Applies ToExcel za Microsoft 365 Excel 2024 Access 2024 Excel 2021 Access 2021 Excel 2019 Access 2019 Excel 2016 Access 2016

Opomba: Microsoft Access ne podpira uvažanja Excelovih podatkov z uporabljeno oznako občutljivosti. Kot nadomestno rešitev lahko odstranite oznako pred uvozom in nato znova uporabite oznako po uvozu. Če želite več informacij, glejte Uporaba oznak občutljivosti za datoteke in e-pošto v Officeu.

V tem članku je opisano, kako premaknete podatke iz Excela v Access in pretvorite podatke v relacijske tabele, tako da lahko uporabljate Microsoft Excel in Access skupaj. Če povzamemo, je Access najprimernejši za zajem, shranjevanje, poizvedovanje in skupno rabo podatkov, Excel pa je najprimernejši za izračun, analiziranje in ponazoritev podatkov.

V dveh člankih z Accessom ali Excelom lahko upravljate podatke in 10 najpogostejših razlogov za uporabo Accessa z Excelom, razpravljate o tem, kateri program je najprimernejši za določeno opravilo, in kako uporabiti Excel in Access skupaj, da ustvarite praktično rešitev.

Ko premaknete podatke iz Excela v Access, so v postopku trije osnovni koraki.

three basic steps

Opomba: Če želite informacije o modelih podatkov in relacijah v Accessu, glejte Osnove načrtovanja zbirke podatkov.

1. korak: Uvoz podatkov iz Excela v Access

Uvoz podatkov je operacija, ki lahko traja veliko bolj nemoteno, če si v nekaj časa za pripravo in čiščenje podatkov. Uvažanje podatkov je podobno premikanju na nov dom. Če pred selitvijo počistite in organizirate svoje premoženje, je lažje, če se preselite v nov dom.

Čiščenje podatkov pred uvozom

Preden uvozite podatke v Access, priporočamo, da v Excelu naredite to:

  • Pretvorite celice, ki vsebujejo ne-atomske podatke (to je več vrednosti v eni celici) v več stolpcev. Celica v stolpcu »Znanja«, ki vsebuje več vrednosti znanja, na primer »Programiranje C#«, »Programiranje VBA« in »Spletni načrt«, mora biti razdeljena tako, da ločite stolpce, v katerih je le ena vrednost znanja.

  • Z ukazom TRIM odstranite vodilne, končne in več vdelanih prostorov.

  • Odstranite nenatisljive znake.

  • Iskanje in odpravljanje napak v črkovanju in ločilih.

  • Odstranite podvojene vrstice ali podvojena polja.

  • Prepričajte se, da stolpci s podatki ne vsebujejo mešanih oblik, še posebej števil, oblikovanih kot besedilo ali datumi, oblikovani kot števila.

Če želite več informacij, glejte te teme pomoči za Excel:

Opomba: Če so vaše potrebe po čiščenju podatkov zapletene ali če nimate časa ali virov za avtomatizacijo postopka sami, razmislite o uporabi neodvisnega dobavitelja. Če želite več informacij, v svojem spletnem brskalniku poiščite »programsko opremo za čiščenje podatkov« ali »kakovost podatkov« svojega priljubljenega iskalnika.

Izbiranje najboljše vrste podatkov ob uvozu

Med operacijo uvoza v Accessu želite sprejeti dobre odločitve, da boste prejeli nekaj (če obstajajo) napak pri pretvorbi, ki bodo zahtevale ročno posredovanje. V spodnji tabeli je povzetek, kako so oblike zapisa števil v Excelu in Accessovi podatkovni tipi pretvorjeni, ko uvozite podatke iz Excela v Access, in ponuja nekaj namigov o najboljših podatkovnih tipih, ki jih lahko izberete v čarovniku za uvoz preglednic.

Excelova oblika zapisa števil

Podatkovni tip v Accessu

Pripombe

Najboljša praksa

Text (Besedilo)

Besedilo, zapisek

Podatkovni tip »Accessovo besedilo« shranjuje alfanumerične podatke do 255 znakov. Podatkovni tip »Accessov zapisek« shranjuje alfanumerične podatke do 65.535 znakov.

Izberite Zapisek , če se želite izogniti prirezokom podatkov.

Število, odstotek, ulomek, znanstveno

število

Access ima en podatkovni tip Število, ki se razlikuje glede na lastnost Velikost polja (Bajt, Celo število, Dolgo celo število, Enojno, Dvojno, Decimalno).

Izberite Dvojno , da se izognete morebitnim napakam pri pretvorbi podatkov.

Datum

Datum

Access in Excel za shranjevanje datumov uporabljata isto zaporedno številko datuma. V Accessu je datumski obseg večji: od -657.434 (1. januar 100 AD) do 2.958.465 (31. december 9999 A.D.).

Ker Access ne prepozna datumskega sistema 1904 (ki se uporablja v Excelu za Macintosh), morate pretvoriti datume v Excelu ali Accessu, da se izognete zmedi.

Če želite več informacij, glejte Spreminjanje sistema datumov, oblike zapisa ali dvomestne letne interpretacije in Uvoz ali povezovanje s podatki v Excelovem delovnem zvezku.

Izberite Datum.

Čas

Ura

Access in Excel shranjujeta časovne vrednosti z istim podatkovom.

Izberite Ura, ki je po navadi privzeta nastavitev.

Currency, Accounting

Valuta

V Accessu podatkovni tip »Valuta« shranjuje podatke kot 8-bajtna števila natančno na štiri decimalna mesta, uporablja pa se za shranjevanje finančnih podatkov in preprečevanje zaokroževanja vrednosti.

Izberite Valuta, ki je po navadi privzeta nastavitev.

Logičen

Da/ne

Access uporablja -1 za vse vrednosti »Da« in 0 za vse vrednosti »Ne«, Medtem ko Excel uporablja 1 za vse vrednosti TRUE in 0 za vse vrednosti FALSE.

Izberite Da/ne, ki samodejno pretvori temeljne vrednosti.

Hiperpovezava

Hiperpovezava

Hiperpovezava v Excelu in Accessu vsebuje URL ali spletni naslov, ki ga lahko kliknete in spremljate.

Izberite Hiperpovezava, sicer bo Access privzeto uporabil podatkovni tip »Besedilo«.

Ko so podatki v Accessu, lahko izbrišete Excelove podatke. Ne pozabite najprej varnostno kopirati izvirnega Excelovega delovnega zvezka, preden ga izbrišete.

Če želite več informacij, glejte temo pomoči za Access Uvoz ali povezovanje podatkov v Excelovem delovnem zvezku.

Samodejno prilaganje podatkov na preprost način

Pogosto se težava, ki jo imajo uporabniki Excela, je dodajanje podatkov z istimi stolpci na en velik delovni list. Morda imate na primer rešitev za evidenco osnovnih sredstev, ki se je začela v Excelu, vendar je zdaj na voljo za vključevanje datotek iz številnih delovnih skupin in oddelkov. Ti podatki so lahko na različnih delovnih listih in v delovnih zvezkih ali v besedilnih datotekah, ki so viri podatkov iz drugih sistemov. V Excelu ni ukaza uporabniškega vmesnika ali preprostega načina za dodajanje podobnih podatkov.

Najboljša rešitev je, da uporabite Access, kjer lahko preprosto uvozite podatke in jih dodate v eno tabelo s čarovnikom za uvoz preglednic. Poleg tega lahko v eno tabelo dodate veliko podatkov. Operacije uvoza lahko shranite, jih dodate kot načrtovana Microsoft Outlookova opravila in celo z makri avtomatizirate postopek.

2. korak: Normalizacija podatkov s čarovnikom za analizo tabel

Na prvi pogled se vam lahko zdi, da je premikanje po postopku normaliziranja podatkov zastrašujoče opravilo. Na srečo je normaliziranje tabel v Accessu postopek, ki je veliko preprostejši zaradi čarovnika za analizo tabel.

the table analyzer wizard

1. Povlecite izbrane stolpce v novo tabelo in samodejno ustvarite relacije

2. Uporabite ukaze gumbov za preimenovanje tabele, dodajanje primarnega ključa, spreminjanje obstoječega stolpca v primarni ključ in razveljavitev zadnjega dejanja

S tem čarovnikom lahko naredite to:

  • Pretvorite tabelo v nabor manjših tabel in samodejno ustvarite relacijo med tabelami s primarnim in tujim ključem.

  • Dodajte primarni ključ obstoječemu polju, ki vsebuje enolične vrednosti, ali ustvarite novo polje z ID-jem, ki uporablja podatkovni tip »Samoštevilo«.

  • Samodejno ustvarite relacije, da vzpostavite referenčno integriteto s kaskadnim posodabljanjem. Kaskadno brisanje ni samodejno dodano, da bi preprečili nenamerno brisanje podatkov, vendar lahko kaskadno brisanje preprosto dodate pozneje.

  • V novih tabelah poiščite odvečne ali podvojene podatke (na primer isto stranko z dvema različnima telefonskima številkama) in jih po želji posodobite.

  • Varnostno kopirajte izvirno tabelo in jo preimenujte tako, da imenu _OLD »ime«. Nato ustvarite poizvedbo, ki znova ustvari izvirno tabelo z imenom izvirne tabele, tako da bodo vsi obstoječi obrazci ali poročila, ki temeljijo na izvirni tabeli, delovali z novo strukturo tabele.

Če želite več informacij, glejte Normaliziranje podatkov z analizatorju tabel.

3. korak: Vzpostavljanje povezave z Accessovimi podatki iz Excela

Ko so podatki v Accessu normalizirani in je ustvarjena poizvedba ali tabela, ki ponaroča izvirne podatke, je preprosto vzpostaviti povezavo z Accessovimi podatki iz Excela. Vaši podatki so zdaj v Accessu kot zunanji vir podatkov in tako lahko vzpostavite povezavo z delovnim zvezkom prek podatkovne povezave, ki je vsebnik informacij, ki se uporabljajo za iskanje, prijavo in dostop do zunanjega vira podatkov. Podatki o povezavi so shranjeni v delovnem zvezku in jih je mogoče shraniti tudi v datoteko za povezavo, na primer Officeovo datoteko za povezavo podatkov (ODC) (datotečno pripono .odc) ali datoteko z imenom vira podatkov (pripona .dsn). Ko vzpostavite povezavo z zunanjimi podatki, lahko samodejno osvežite (ali posodobite) Excelov delovni zvezek v Accessu vsakič, ko so podatki posodobljeni v Accessu.

Če želite več informacij, glejte Uvoz podatkov iz zunanjih virov podatkov (Power Query).

Prenos podatkov v Access

V tem razdelku so navedene te faze normaliziranja podatkov: prelom vrednosti v stolpcih »Prodajalec« in »Naslov« v najbolj atomske dele, ločevanje sorodnih predmetov v njihove tabele, kopiranje in lepljenje teh tabel iz Excela v Access, ustvarjanje ključnih odnosov med novo ustvarjenimi Accessovimi tabelami in ustvarjanje in zagon preproste poizvedbe v Accessu za prikaz informacij.

Vzorčni podatki v ne normalizirani obliki

Na tem delovnem listu so neamične vrednosti v stolpcu Prodajalec in Stolpec Naslov. Oba stolpca razdelite v dva ali več ločenih stolpcev. Na tem delovnem listu so tudi informacije o prodajalcih, izdelkih, strankah in naročilih. Te informacije je treba po zadevi dodatno razdeliti v ločene tabele.

Prodajalec

ID naročila

Datum naročila

ID izdelka

Količina

Cena

ime stranke

Address (Naslov)

Telefon

Li, Yale

2349

3/4/09

C-789

3

7,00 USD

Kavarna Četrta kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Li, Yale

2349

3/4/09

C-795

6

9,75 USD

Kavarna Četrta kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Adams, Ellen

2350

3/4/09

A-2275

2

16,75 USD

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

F-198

6

5,25 USD

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

B-205

1

4,50 USD

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2351

3/4/09

C-795

6

9,75 USD

Contoso, d.o.o.

2302 Harvard Ave Bellevue, WA 98227

425-555-0222

Hance, Jim

2352

3/5/09

A-2275

2

16,75 USD

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2352

3/5/09

D-4420

3

7,25 USD

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Koch, Reed

2353

3/7/09

A-2275

6

16,75 USD

Kavarna Četrta kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Koch, Reed

2353

3/7/09

C-789

5

7,00 USD

Kavarna Četrta kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Informacije v najmanjših delih: atomski podatki

Pri delu s podatki v tem primeru lahko z ukazom Besedilo v stolpec v Excelu ločite »atomske« dele celice (na primer naslov, mesto, državo in poštno številko) v ločene stolpce.

V spodnji tabeli so prikazani novi stolpci na istem delovnem listu, potem ko so bili razdeljeni, tako da so vse vrednosti atomske. Informacije v stolpcu Prodajalec so bile razdeljene v stolpca Priimek in Ime ter da so bili podatki v stolpcu Naslov razdeljeni v stolpce Naslov, Mesto, Država in Poštna številka. Ti podatki so v »prvi običajni obliki«.

Priimek

Ime

 

Naslov

Mesto

Zvezna država

Poštna številka

Li

Yale

2302 Harvard Ave

Portorož

WA

98227

Potočnik

Ellen

1025 Columbia Circle

Maribor

WA

98234

Hace

Janez

2302 Harvard Ave

Portorož

WA

98227

Koch

Reed

7007 Cornell St Redmond

Redmond

WA

98199

Razdvajajte podatke v organiziranih temah v Excelu

V več tabelah vzorčnih podatkov, ki sledijo, so prikazane iste informacije iz Excelovega delovnega lista, ko jih razdelite v tabele za prodajalce, izdelke, stranke in naročila. Načrt tabele ni končni, vendar je na pravi poti.

V tabeli Prodajalci so le podatki o prodajnem osebju. Upoštevajte, da ima vsak zapis enoličen ID (ID prodajalca). Vrednost ID-ja prodajalca bo uporabljena v tabeli »Naročila« za povezovanje naročil s prodajalci.

Prodajalci

ID prodajalca

Priimek

Ime

101

Li

Yale

103

Potočnik

Ellen

105

Hace

Janez

107

Koch

Reed

V tabeli »Izdelki« so samo podatki o izdelkih. Upoštevajte, da ima vsak zapis enoličen ID (ID izdelka). Vrednost ID-ja izdelka bo uporabljena za povezovanje podatkov o izdelku s tabelo »Podrobnosti o naročilu«.

Izdelki

ID izdelka

Cena

A-2275

16.75

B-205

4.50

C-789

7.00

C-795

9.75

D-4420

7.25

F-198

5.25

V tabeli »Stranke« so le podatki o strankah. Upoštevajte, da ima vsak zapis enoličen ID (ID stranke). Vrednost »ID stranke« bo uporabljena za povezovanje podatkov o stranki s tabelo »Naročila«.

Stranke

ID stranke

Ime

Naslov

Mesto

Zvezna država

Poštna številka

Telefon

1001

Contoso, d.o.o.

2302 Harvard Ave

Portorož

WA

98227

425-555-0222

1003

Adventure Works

1025 Columbia Circle

Maribor

WA

98234

425-555-0185

1005

Kavarna Četrta kava

7007 Cornell St

Redmond

WA

98199

425-555-0201

V tabeli »Naročila« so podatki o naročilih, prodajalcih, strankah in izdelkih. Upoštevajte, da ima vsak zapis enoličen ID (ID naročila). Nekatere podatke v tej tabeli je treba razdeliti v dodatno tabelo s podrobnostmi o naročilu, tako da so v tabeli »Naročila« le štirje stolpci – enolični ID naročila, datum naročila, ID prodajalca in ID stranke. Tabela, prikazana tukaj, še ni bila razdeljena v tabelo »Podrobnosti o naročilu«.

Naročila

ID naročila

Datum naročila

ID prodajalca

ID stranke

ID izdelka

Količina

2349

3/4/09

101

1005

C-789

3

2349

3/4/09

101

1005

C-795

6

2350

3/4/09

103

1003

A-2275

2

2350

3/4/09

103

1003

F-198

6

2350

3/4/09

103

1003

B-205

1

2351

3/4/09

105

1001

C-795

6

2352

3/5/09

105

1003

A-2275

2

2352

3/5/09

105

1003

D-4420

3

2353

3/7/09

107

1005

A-2275

6

2353

3/7/09

107

1005

C-789

5

Podrobnosti naročila, kot sta ID izdelka in količina, so premaknjene iz tabele »Naročila« in shranjene v tabeli z imenom »Podrobnosti o naročilu«. Ne pozabite, da je v tej tabeli 9 naročil, zato je smiselno, da je v tej tabeli 9 zapisov. Upoštevajte, da ima tabela »Naročila« enolični ID (ID naročila), na katerega se nanaša tabela »Podrobnosti o naročilu«.

Končni načrt tabele »Naročila« bi moral biti podoben temu:

Naročila

ID naročila

Datum naročila

ID prodajalca

ID stranke

2349

3/4/09

101

1005

2350

3/4/09

103

1003

2351

3/4/09

105

1001

2352

3/5/09

105

1003

2353

3/7/09

107

1005

V tabeli »Podrobnosti naročila« ni stolpcev, ki bi zahtevali enolične vrednosti (torej ni primarnega ključa), zato lahko kateri koli ali vsi stolpci vsebujejo »odvečne« podatke. Vendar pa nobena dva zapisa v tej tabeli ne bi morala biti popolnoma enaka (to pravilo velja za vse tabele v zbirki podatkov). V tej tabeli mora biti 17 zapisov – vsak ustreza izdelku v posameznem naročilu. Za 2349 so na primer trije izdelki C-789 sestavljeni iz enega od dveh delov celotnega naročila.

Tabela »Podrobnosti naročila« bi morala biti videti tako:

Podrobnosti naročila

ID naročila

ID izdelka

Količina

2349

C-789

3

2349

C-795

6

2350

A-2275

2

2350

F-198

6

2350

B-205

1

2351

C-795

6

2352

A-2275

2

2352

D-4420

3

2353

A-2275

6

2353

C-789

5

Kopiranje in lepljenje podatkov iz Excela v Access

Zdaj, ko so bili podatki o prodajalcih, strankah, izdelkih, naročilih in podrobnostih naročil razdeljeni na ločene zadeve v Excelu, lahko te podatke kopirate neposredno v Access, kjer bodo postali tabele.

Ustvarjanje relacij med Accessovimi tabelami in zagon poizvedbe

Ko premaknete podatke v Access, lahko ustvarite relacije med tabelami in nato ustvarite poizvedbe, da vrnete informacije o različnih temah. Ustvarite lahko na primer poizvedbo, ki vrne ID naročila in imena prodajalcev za naročila, vnesena med 05. 3. 2009 in 08. 3. 2009.

Poleg tega lahko ustvarite obrazce in poročila, s tem pa poenostavite vnos podatkov in analizo prodaje.

Potrebujete dodatno pomoč?

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel ali pa pridobite podporo v skupnostih.

Ali potrebujete dodatno pomoč?

Ali želite več možnosti?

Raziščite ugodnosti naročnine, prebrskajte izobraževalne tečaje, preberite, kako zaščitite svojo napravo in še več.

Skupnosti vam pomagajo postaviti vprašanja in odgovoriti nanje, posredovati povratne informacije in prisluhniti strokovnjakom z bogatim znanjem.