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.
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.
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.