Premikanje podatkov iz Excela v Access

V tem članku je opisano, kako premaknete podatke iz Excela v Access in pretvorite podatke v relacijske tabele, tako da lahko uporabite Microsoft Excel in dostopate skupaj. Če želite povzeti, je Access najboljši za zajemanje, shranjevanje, poizvedovanje in skupno rabo podatkov, Excel pa je najboljši za izračunavanje, analiziranje in vizualizacijo podatkov.

V dveh člankih z Accessom ali Excelom lahko upravljate podatke in 10 razlogov za uporabo Accessa z Excelom, razpravljajte o tem, kateri program je najprimernejši za določeno opravilo in kako lahko s programom Excel in Access vzpostavite praktično rešitev.

Ko premaknete podatke iz Excela v Access, je postopek na voljo v treh osnovnih korakih.

three basic steps

Opomba: Če želite več informacij o modeliranju podatkov in relacijah v Accessu, glejte osnove načrtovanja zbirke podatkov.

1. korak: uvoz podatkov iz Excela v Access

Uvažanje podatkov je postopek, ki je lahko veliko bolj nemoten, če si vzamete čas za pripravo in čiščenje podatkov. Uvažanje podatkov je podobno selitvi v nov dom. Če ste počistili in organizirali svoje imetje, preden ste se preselili, je veliko lažje poravnati v novi dom.

Čiščenje podatkov pred uvozom

Preden uvozite podatke v Access, je v Excelu priporočljivo, da:

  • Pretvorite celice, ki vsebujejo neatomske podatke (več vrednosti v eni celici), v več stolpcev. Na primer celica v stolpcu» Skills «, ki vsebuje več vrednosti znanja, kot so» programiranje C# «,» programiranje VBA «in» Web Design «, je treba razdeliti v ločene stolpce, ki vsebujejo le eno vrednost znanja.

  • Če želite odstraniti vodilne, končne in več vdelanih presledkov, uporabite ukaz Obreži.

  • Odstranite nenatisljive znake.

  • Poiščite in odpravite napake pri črkovanju in ločilih.

  • Odstranite podvojene vrstice ali podvojena polja.

  • Zagotovite, da stolpci s podatki ne vsebujejo mešanih oblik, še posebej števil, oblikovanih kot besedilo ali datumi, ki so oblikovani kot številke.

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

Opomba: Če so vaše potrebe po čiščenju podatkov zapletene ali pa nimate časa ali virov za avtomatizacijo samega postopka, lahko uporabite prodajalca neodvisnih ponudnikov. Če želite več informacij, poiščite» programska oprema za čiščenje podatkov «ali» kakovost podatkov «vašega priljubljenega iskalnika v spletnem brskalniku.

Izberite najboljši podatkovni tip, ko uvozite

Med operacijo uvoza v Accessu želite ustvariti dobre izbire, tako da prejmete le nekaj napak pri pretvorbi, ki bodo zahtevale ročne posege. V tej tabeli je povzeto, kako so Excelove oblike zapisa števil in Accessovi podatkovni tipi pretvorjeni, ko uvozite podatke iz Excela v Access, in ponudi nekaj namigov za najboljše podatkovne tipe, ki jih lahko izberete v čarovniku za uvoz preglednice.

Excelova oblika zapisa števil

Podatkovni tip v Accessu

Pripombe

Najboljša praksa

Besedilo

Besedilo, Zapisek

Podatkovni tip Accessovega besedila shranjuje alfanumerične podatke do 255 znakov. Podatkovni tip Accessovega memoranduma shranjuje alfanumerične podatke do 65.535 znakov.

Izberite Zapisek , da se izognete Odreži podatkov.

Število, odstotek, ulomek, znanstveni

število

Access ima eno številsko vrsto podatkov, ki se razlikuje glede na lastnost velikosti polja (bajt, celo število, dolgo celo število, eno, dvojno, decimalno).

Izberite» podvoji «, da se izognete napakam pri pretvorbi podatkov.

Datum

Datum

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

Ker Access ne prepozna sistema 1904 za datum (ki je uporabljen v Excelu za Macintosh), morate datume pretvoriti v Excel ali Access, da se izognete zmedi.

Če želite več informacij, glejte Spreminjanje sistema datuma, oblike zapisa ali dvomestne razlage letnice ter uvoz ali povezava do podatkov v Excelovem delovnem zvezku.

Izberite datum.

Čas

Ura

Access in Excel oba shranita časovne vrednosti tako, da uporabita isti podatkovni tip.

Izberite čas, ki je običajno privzet.

Valuta, računovodstvo

Valuta

V Accessu podatkovni tip Valuta shranjuje podatke kot 8-bajtne številke z natančnostjo na štiri decimalna mesta in se uporablja za shranjevanje finančnih podatkov in preprečevanje zaokroževanja vrednosti.

Izberite valuta, ki je običajno privzeta.

Logičen

Da/ne

Access uporabi – 1 za vse vrednosti» da «in 0 za vse vrednosti brez, medtem ko Excel uporabi 1 za vse vrednosti TRUE in 0 za vse napačne vrednosti.

Izberite da/ne, ki samodejno pretvori temeljne vrednosti.

Hiperpovezava

Hiperpovezava

Hiperpovezava v Excelu in Access vsebuje URL ali spletni naslov, ki ga lahko kliknete in upoštevate.

Izberite Hiperpovezava, sicer Access lahko privzeto uporabi besedilni podatkovni tip.

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

Če želite več informacij, glejte uvoz ali povezava do podatkov v Excelovem delovnem zvezku.

Samodejno dodajanje podatkov na preprost način

Pogosta težava, ki jo imajo uporabniki v Excelu, je dodajanje podatkov z istimi stolpci na en velik delovni list. Morda imate na primer rešitev za sledenje sredstev, ki je bila zagnana v Excelu, zdaj pa je zrasla za vključitev datotek iz številnih delovnih skupin in oddelkov. Ti podatki so morda na različnih delovnih listih in v delovnih zvezkih ali v besedilnih datotekah, ki so podatkovni viri iz drugih sistemov. Ni ukaza uporabniškega vmesnika ali enostavnega načina za dodajanje podobnih podatkov v Excelu.

Najboljša rešitev je, da uporabite Access, kjer lahko preprosto uvozite in dodajate podatke v eno tabelo z uporabo čarovnika za uvoz preglednice. Poleg tega lahko dodate veliko podatkov v eno tabelo. Postopke uvoza lahko shranite, jih dodate kot načrtovana Microsoft Outlookova opravila in celo uporabite makre, da avtomatizirate postopek.

2. korak: Normalizirajte podatke z uporabo čarovnika za analizator tabele

Na prvi pogled se lahko pomikate po postopku normalizacije podatkov, ki se morda zdijo zastrašujoče opravilo. Na srečo je normaliziranje tabel v Accessu proces, ki je veliko lažje, zahvaljujoč čarovniku 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, dodajte primarni ključ, naredite obstoječi stolpec primarni ključ in razveljavite zadnje dejanje.

S tem čarovnikom lahko naredite to:

  • Pretvorite tabelo v niz manjših tabel in samodejno ustvarite relacijo primarnega in tujega ključa med tabelami.

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

  • Samodejno ustvarjanje relacij za uveljavitev referenčne integritete s kaskadnimi posodobitvami. Kaskadne brisanja niso samodejno dodane, da preprečite nenamerno brisanje podatkov, lahko pa preprosto dodate kaskadne brisanja pozneje.

  • Iskanje novih tabel za odveč ali podvojene podatke (kot je na primer ista stranka z dvema različnima telefonskimi številkami) in posodobite to po želji.

  • Varnostno kopirate izvirno tabelo in jo preimenujete tako, da dodate» _OLD «v njeno ime. Nato ustvarite poizvedbo, ki rekonstruira izvirno tabelo, z izvirno ime 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 analizatorjem tabele.

3. korak: vzpostavljanje povezave z Accessovimi podatki iz Excela

Ko so podatki v Accessu normalizirani in je bila ustvarjena poizvedba ali tabela, ki rekonstruira prvotne podatke, je to preprosta stvar vzpostavljanja povezave z Accessovimi podatki iz Excela. Vaši podatki so zdaj v Accessu kot zunanji vir podatkov, zato so lahko povezani z delovnim zvezkom prek podatkovne povezave, ki je vsebnik podatkov, ki se uporabljajo za iskanje, prijavo v in dostop do zunanjega vira podatkov. Informacije o povezavi so shranjene v delovnem zvezku in so lahko shranjene tudi v datoteki za povezavo, kot je datoteka ODC (Officeove podatkovne povezave) (datotečna pripona. odc) ali datoteka z imenom vira podatkov (pripona. DSN). Ko vzpostavite povezavo z zunanjimi podatki, lahko tudi samodejno osvežite (ali posodobite) Excelov delovni zvezek iz Accessa, kadar koli se podatki posodobijo v Accessu.

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

Pridobivanje podatkov v Access

V tem razdelku so navedene faze normalizacije podatkov: prelom vrednosti v stolpcih prodajalca in naslovov v najbolj atomske dele, ki ločujejo sorodne predmete v svoje tabele, kopirate in prilepite te tabele iz Excela v Access, ustvarjanje ključnih relacij med novo ustvarjenimi Accessovimi tabelami in ustvarjanje in zagon preproste poizvedbe v Accessu za vrnitev informacij.

Vzorčni podatki v obliki, ki ni normalizirana

V tem delovnem listu so neatomske vrednosti v stolpcu» prodajalec «in stolpcu» naslov «. Oba stolpca je treba razdeliti v dva ali več ločenih stolpcev. V tem delovnem listu so tudi informacije o prodajalcih, izdelkih, kupcih in naročilih. Te informacije bi morale biti tudi razdeljene po zadevi v ločene tabele.

Prodajalec

ID naročila

Datum naročila

ID izdelka

Qty

Cena

Ime stranke

Naslov

Telefon

Li, Yale

2349

3/4/09

C-789

3

$7,00

Kavarna Četrta kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Li, Yale

2349

3/4/09

C-795

6

$9,75

Kavarna Četrta kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Adams, Ellen

2350

3/4/09

A-2275

2

$16,75

Avanturistične gradnje

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

F-198

6

$5,25

Avanturistične gradnje

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

B-205

1

$4,50

Avanturistične gradnje

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, jim

2351

3/4/09

C-795

6

$9,75

Contoso, d.o.o.

2302 Harvard ave Bellevue, WA 98227

425-555-0222

Hance, jim

2352

3/5/09

A-2275

2

$16,75

Avanturistične gradnje

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, jim

2352

3/5/09

D-4420

3

$7,25

Avanturistične gradnje

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Koch, Trst

2353

3/7/09

A-2275

6

$16,75

Kavarna Četrta kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Koch, Trst

2353

3/7/09

C-789

5

$7,00

Kavarna Četrta kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Informacije v najmanjših delih: atomski podatki

Če delate s podatki v tem primeru, lahko z ukazom» besedilo v stolpec «v Excelu razdelite» atomske «dele celice (na primer naslov ulice, mesto, država in poštna številka) v diskretne stolpce.

V spodnji tabeli so prikazani novi stolpci na istem delovnem listu, potem ko so bili razdeljeni, da bi naredili vse vrednosti za atomsko. Upoštevajte, da so bile informacije v stolpcu» prodajalec «razdeljene na priimek in stolpce s prvim imenom ter da so bile informacije v stolpcu» naslov «razdeljene na stolpce» naslov «,» mesto «,» država «in» POŠTNa koda «. Ti podatki so v razdelku» prva normalna oblika «.

Priimek

Ime

 

Naslov

Mesto

Zvezna država

poštna številka

Li

Yale

2302 Harvard ave

Portorož

WA

98227

Potočnik

Marka

Krog za 1025 Columbia

Maribor

WA

98234

Hace

Janez

2302 Harvard ave

Portorož

WA

98227

Koch

Reed

7007 Cornell St Redmond

Redmond

WA

98199

Prekinitev podatkov v organiziranih temah v Excelu

Več tabel vzorčnih podatkov, ki sledijo prikazanim istim podatkom iz Excelovega delovnega lista, potem ko je bil razdeljen v tabele za prodajalce, izdelke, stranke in naročila. Načrt tabele ni dokončen, vendar je na pravi strani.

V tabeli» prodajalci «so le informacije o prodajnem osebju. Upoštevajte, da ima vsak zapis enoličen ID (ID prodajalca). Vrednost ID-ja prodajalca bo uporabljena v tabeli» naročila «, da poveže naročila za prodajalce.

Prodajalci

ID prodajalca

Priimek

Ime

101

Li

Yale

103

Potočnik

Marka

105

Hace

Janez

107

Koch

Reed

V tabeli» izdelki «so le informacije 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 naročila «.

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 informacije o strankah. Upoštevajte, da ima vsak zapis enoličen ID (ID stranke). Vrednost ID-ja stranke bo uporabljena za povezovanje podatkov o strankah v 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

Avanturistične gradnje

Krog za 1025 Columbia

Maribor

WA

98234

425-555-0185

1005

Kavarna Četrta kava

7007 Cornell St

Redmond

WA

98199

425-555-0201

V tabeli» naročila «so informacije o naročilih, prodajalcih, kupcih in izdelkih. Upoštevajte, da ima vsak zapis enoličen ID (ID naročila). Nekatere informacije v tej tabeli je treba razdeliti v dodatno tabelo, ki vsebuje podrobnosti naročila, tako da tabela» naročila «vsebuje le štiri stolpce – Enolični ID naročila, datum naročila, ID prodajalca in ID stranke. Tabela, prikazana tukaj, še ni bila razdeljena v tabelo s podrobnostmi o naročilu.

Naročila

ID naročila

Datum naročila

ID prodajalca

ID stranke

ID izdelka

Qty

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 o naročilu, kot sta ID izdelka in količina, so premaknjene iz tabele naročila in shranjene v tabeli z imenom podrobnosti naročila. Upoštevajte, da je na voljo 9 naročil, zato je smiselno, da je v tej tabeli 9 zapisov. Tabela» naročila «ima enoličen ID (ID naročila), ki bo naveden v tabeli» podrobnosti naročila «.

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 zahtevajo enolične vrednosti (to pomeni, da ni primarnega ključa), zato je v redu za vse ali vse stolpce, ki vsebujejo» odvečne «podatke. Vendar pa v tej tabeli ni nobenega dva zapisa, ki bi moral biti popolnoma identičen (to pravilo velja za katero koli tabelo v zbirki podatkov). V tej tabeli mora biti 17 zapisov – vsak, ki ustreza izdelku v posameznem vrstnem redu. V vrstnem redu 2349 so na primer trije izdelki C-789 sestavljeni iz enega od dveh delov celotnega naročila.

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

Podrobnosti o naročilu

ID naročila

ID izdelka

Qty

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 informacije o prodajalcih, kupcih, izdelkih, naročilih in podrobnostih o naročilu razdeljene v ločene predmete v Excelu, lahko te podatke kopirate neposredno v Access, kjer bodo postale tabele.

Ustvarjanje relacij med Accessovimi tabelami in zagonom poizvedbe

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

Poleg tega lahko ustvarite obrazce in poročila, s katerimi olajšate vnos podatkov in analizo prodaje.

Potrebujete dodatno pomoč?

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel, pridobite podporo skupnosti Answers ali predlagate novo funkcijo oziroma izboljšavo na spletnem mestu Excel User Voice.

Razširite poznavanje Officea
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×