Andmete teisaldamine Excelist Accessi

Selles artiklis kirjeldatakse, kuidas teisaldada andmed Excelist Accessi ja teisendada andmed relatsioontabeliteks, et saaksite kasutada koos Microsoft Excelit ja Accessi. Kokkuvõtte tegemiseks sobib Access kõige paremini andmete talletamiseks, talletamiseks, päringute tegemiseks ja ühiskasutusse andmiseks ning Excel sobib kõige paremini andmete arvutamiseks, analüüsimiseks ja visualiseerimiseks.

Kaks artiklit Accessi või Exceli kasutamine andmete haldamiseks ja Kümme peamist põhjust Accessi kasutamiseks Koos Exceliga arutavad, milline programm sobib konkreetse ülesande jaoks kõige paremini ning kuidas kasutada Excelit ja Accessi koos praktilise lahenduse loomiseks.

Andmete teisaldamisel Excelist Accessi on protsessis kolm põhitoimingut.

1. toiming: andmete importimine Excelist Accessi

Andmete importimine on toiming, mis võib andmete ettevalmistamiseks ja puhastamiseks veidi aega võtta. Andmete importimine sarnaneb uude koju kolimisega. Kui puhastate ja korraldate oma vara enne kolimist, on palju lihtsam uude koju kolida.

Andmete puhastamine enne importimist

Enne andmete Accessi importimist tasub Excelis teha järgmist.

• Teisendage mitteatomiandmeid sisaldavad lahtrid (st ühes lahtris mitu väärtust) mitmeks veeruks. Näiteks veeru "Oskused" lahter, mis sisaldab mitut oskuse väärtust (nt "C# programmeerimine", "VBA programmeerimine", ja "Veebikujundus"), tuleks jagada eraldi veergudeks, kus igaüks sisaldab ainult ühte oskuse väärtust.
• Käsu TRIM abil saate eemaldada algus-, lõpu- ja mitu manustatud tühikut.
• Eemaldage mitteprinditavad märgid.
• Saate otsida ja parandada õigekirja- ja kirjavahemärke.
• Eemaldage duplikaatread või duplikaatväljad.
• Veenduge, et andmeveerud ei sisaldaks segavorminguid, eriti tekstina vormindatud arve või arvudena vormindatud kuupäevi.

Lisateavet leiate järgmistest Exceli spikriteemadest.

• Kümme parimat võimalust andmete puhastamiseks
• Kordumatute väärtuste filtreerimine või duplikaatväärtuste eemaldamine
• Tekstina talletatud arvude teisendamine arvudeks
• Tekstina salvestatud kuupäevade teisendamine kuupäevadeks

Valige importimisel parim andmetüüp

Accessi imporditoimingu ajal soovite teha head valikud, et saaksite vähe (kui on) teisendusvigu, mis nõuavad käsitsi sekkumist. Järgmises tabelis on kokkuvõte Exceli arvuvormingute ja Accessi andmetüüpide teisendamisest andmete importimisel Excelist Accessi ja pakub näpunäiteid arvutustabelite importimise viisardis sobivate parimate andmetüüpide kohta.

Exceli arvuvorming	Accessi andmetüüp	Kommentaarid	Head tavad
Text (Tekst)	Tekst, memo	Andmetüüp Access Text talletab tärkandmeid kuni 255 märki. Andmetüüp Access Memo talletab tärkandmeid kuni 65 535 märki.	Andmete kärpimise vältimiseks valige Memo .
Arv, protsent, murd, teaduslik	Arv	Accessis on üks andmetüüp Arv, mis sõltub atribuudist Välja suurus (Bait, Täisarv, Pikk täisarv, Ühekordne, Kahekordne, Kümnendarv).	Andmeteisendustõrgete vältimiseks valige Topelttäpsusega arv.
Date	Kuupäev	Nii Access kui ka Excel kasutavad kuupäevade talletamiseks sama kuupäeva järjenumbrit. Accessis on kuupäevavahemik suurem: –657 434 (1. jaanuar 100 A.D.) kuni 2 958 465 (31. detsember 9999 A.D.). Kuna Access ei tuvasta 1904-kuupäevasüsteemi (seda kasutatakse Excel for the Macintoshis), peate segaduse vältimiseks kuupäevad Excelis või Accessis teisendama. Lisateavet leiate teemadest Kuupäevasüsteemi, vormingu või kahekohalise aasta tõlgendamise muutmine ja Exceli töövihiku andmete importimine või linkimine.	Valige Date (Kuupäev).
Time	Time	Accessis ja Excelis talletatakse nii ajaväärtused kui ka andmetüübid.	Valige Time (Aeg), mis on tavaliselt vaikeväärtus.
Valuuta, raamatupidamine	Valuuta	Accessi andmetüüp Valuuta talletab andmeid 8-baitide arvudena, mille täpsus on neli kümnendkohta, ning seda kasutatakse finantsandmete talletamiseks ja väärtuste ümardamise vältimiseks.	Valige Valuuta, mis on tavaliselt vaikeväärtus.
kahendmuutuja	Jah/ei	Access kasutab kõigi jah-väärtuste puhul väärtust -1 ja kõigi ei-väärtuste puhul väärtust 0, Kuid Excel kasutab kõigi VÄÄRTUSTE TRUE puhul väärtust 1 ja kõigi väärtuste FALSE puhul väärtust 0.	Valige Jah/ei, mis teisendab alusväärtused automaatselt.
Hüperlink	Hüperlink	Exceli ja Accessi hüperlink sisaldab URL-i või veebiaadressi, mida saate klõpsata ja jälgida.	Valige Hüperlink, vastasel juhul võib Access kasutada vaikimisi andmetüüpi Tekst.

Kui andmed on Accessis, saate Exceli andmed kustutada. Ärge unustage enne kustutamist exceli algne töövihik varundada.

Lisateavet leiate Accessi spikriteemast Exceli töövihiku andmete importimine või linkimine.

Andmete automaatne lisamine hõlpsalt

Levinud probleem Exceli kasutajatel on andmete lisamine samade veergudega ühele suurele töölehele. Näiteks võib teil olla varajälituslahendus, mis on Excelis käivitatud, kuid nüüdsest on see kasvanud paljude töörühmade ja osakondade failide kaasamiseks. Need andmed võivad olla erinevatel töölehtedel ja töövihikutes või tekstifailides, mis on muudest süsteemidest pärit andmekanalid. Excelis pole kasutajaliidese käsku ega lihtsat võimalust sarnaste andmete lisamiseks.

Parim lahendus on kasutada Accessi, kus saate arvutustabeli importimise viisardi abil hõlpsalt andmeid ühte tabelisse importida ja lisada. Lisaks saate ühte tabelisse lisada palju andmeid. Saate imporditoimingud salvestada, lisada need ajastatud Microsoft Outlooki ülesannete hulka ja isegi makrode abil protsessi automatiseerida.

2. juhis: andmete normaliseerimine tabelianalüsaatori viisardi abil

Esmapilgul võib andmete normaliseerimise protsessi läbimine tunduda heidutav ülesanne. Õnneks on tabelite normaliseerimine Accessis tänu tabelianalüsaatori viisardile palju lihtsam.

1. Valitud veergude lohistamine uude tabelisse ja seoste automaatne loomine

2. 2. Nupukäskude kasutamine tabeli ümbernimetamiseks, primaarvõtme lisamiseks, olemasolevaks veeruks primaarvõtmeks muutmiseks ja viimase toimingu tagasivõtmiseks

Selle viisardi abil saate teha järgmist.

• Teisendage tabel väiksemate tabelite komplektiks ja looge tabelite vahel automaatselt primaar- ja võõrvõtme seos.
• Lisage primaarvõti olemasolevale väljale, mis sisaldab kordumatuid väärtusi, või looge uus ID-väli, mis kasutab andmetüüpi Automaatnumber.
• Kaskaadvärskendamisega viitamistervikluse jõustamiseks looge seosed automaatselt. Kaskaadkustutusi ei lisata automaatselt, et vältida andmete kogemata kustutamist, kuid saate hiljem hõlpsalt kaskaadkustutusi lisada.
• Otsige uutest tabelitest liigseid või duplikaatandmeid (nt sama klient kahe erineva telefoninumbriga) ja värskendage seda vastavalt soovile.
• Varundage algne tabel ja nimetage see ümber, lisades selle nimele teksti "_OLD". Seejärel loote päringu, mis rekonstrueerib algse tabeli algse tabeli nimega nii, et kõik algsel tabelil põhinevad olemasolevad vormid või aruanded töötaksid uue tabelistruktuuriga.

Lisateavet leiate teemast Andmete normaliseerimine tabelianalüsaatori abil.

3. toiming: Excelist Accessi andmetega ühenduse loomine

Kui andmed on Accessis normaliseeritud ja loodud on päring või tabel, mis taastab algsed andmed, on exceli Accessi andmetega ühenduse loomine lihtne. Teie andmed on nüüd Accessis välise andmeallikana ja seega saab neid töövihikuga ühendada andmeühenduse kaudu, mis on teabemahuti, mida kasutatakse välise andmeallika otsimiseks, sisselogimiseks ja sellele juurdepääsemiseks. Ühenduseteave talletatakse töövihikus ja seda saab talletada ka ühendusfailis (nt Office'i andmeühendusfailis (ODC-faili laiend) või andmeallika nimelaiendis (.dsn). Pärast välisandmetega ühenduse loomist saate exceli töövihikut Accessis automaatselt värskendada (või värskendada) ka siis, kui andmeid Accessis värskendatakse.

Lisateavet leiate teemast Andmete importimine välistest andmeallikatest (Power Query)..

Andmete accessi toomine

Selles jaotises tutvustatakse järgmisi andmete normaliseerimise etappe: veergude Müügiesindaja ja Aadress väärtuste tükeldamine kõige aatomitumateks osadeks, seotud teemade eraldamine oma tabeliteks, nende tabelite kopeerimine ja kleepimine Excelist Accessi, uute Accessi tabelite vahel võtmesuhete loomine ning lihtsa päringu loomine ja käitamine Accessis teabe tagastamiseks.

Näidisandmed normaalandmeteta kujul

Järgmine tööleht sisaldab veerus Müügiesindaja ja Veerus Aadress mitteatomaarseid väärtusi. Mõlemad veerud tuleb tükeldada kaheks või enamaks eraldi veeruks. See tööleht sisaldab ka teavet müügiesindajate, toodete, klientide ja tellimuste kohta. Samuti tuleks see teave teema järgi eraldi tabeliteks jaotada.

Müüja	Tellimuse ID	Tellimuse kuupäev	Toote ID	Kogus	Hind	Kliendi nimi	Address (Aadress)	Telefon
Li, Yale	2349	3/4/09	C-789	3	7,00 $	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201
Li, Yale	2349	3/4/09	C-795	6	9,75 $	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201
Adams, Ellen	2350	3/4/09	A-2275	2	16,75 $	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	F-198	6	5,25 $	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	B-205	1	4,50 $	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2351	3/4/09	C-795	6	9,75 $	Contoso, Ltd	2302 Harvard Ave Bellevue, WA 98227	425-555-0222
Hance, Jim	2352	3/5/09	A-2275	2	16,75 $	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2352	3/5/09	D-4420	3	7,25 $	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Koch, Reed	2353	3/7/09	A-2275	6	16,75 $	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201
Koch, Reed	2353	3/7/09	C-789	5	7,00 $	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201

Teave väikseimates osades: aatomiandmed

Selles näites andmetega töötades saate Exceli käsu Tekst veergu abil eraldada lahtri aatomiosad (nt tänava aadress, linn, maakond ja sihtnumber) eraldi veergudesse.

Järgmises tabelis on toodud sama töölehe uued veerud pärast tükeldamist, et muuta kõik väärtused aatomiks. Pange tähele, et veerus Müügiesindaja olev teave on tükeldatud veergudeks Perekonnanimi ja Eesnimi ning veeru Aadress teave on tükeldatud veergudesse Aadress, Linn, Maakond ja Sihtnumber. Need andmed on "esimesel normaalvormil".

Last Name (Perekonnanimi)	First Name (Eesnimi)	Tänava aadress	Linn	Osariik	Sihtkood
Li	Yale	2302 Harvard Ave	Pärnu	WA	98227
Adams	Ellen	1025 Columbia ring	Kirkland	WA	98234
Hance	Joonas	2302 Harvard Ave	Pärnu	WA	98227
Koch	Reed	7007 Cornell St Redmond	Redmond	WA	98199

Andmete tükeldamine Korraldatud teemadeks Excelis

Järgnevas mitmes näidisandmete tabelis kuvatakse sama teave Exceli töölehelt pärast seda, kui see on tükeldatud müügiesindajate, toodete, klientide ja tellimuste tabeliteks. Tabeli kujundus pole lõplik, kuid see on õigel teel.

Tabel Müügiesindajad sisaldab ainult teavet müügipersonali kohta. Pange tähele, et igal kirjel on kordumatu ID (müügiesindaja ID). Väärtust Müügiesindaja ID kasutatakse tabelis Tellimused tellimuste ühendamiseks müügiesindajatega.

Müügiesindajad
Müügiesindaja ID	Last Name (Perekonnanimi)	First Name (Eesnimi)
101	Li	Yale
103	Adams	Ellen
105	Hance	Joonas
107	Koch	Reed

Tabel Tooted sisaldab ainult teavet toodete kohta. Pange tähele, et igal kirjel on kordumatu ID (toote ID). Toote ID väärtust kasutatakse tooteteabe ühendamiseks tabeliga Tellimuse üksikasjad.

Tooted
Toote ID	Hind
A-2275	16.75
B-205	4.50
C-789	7.00
C-795	9.75
D-4420	7.25
F-198	5.25

Tabel Kliendid sisaldab ainult teavet klientide kohta. Pange tähele, et igal kirjel on kordumatu ID (kliendi ID). Väärtust Kliendi ID kasutatakse klienditeabe ühendamiseks tabeliga Tellimused.

Kliendid
TellijaID	Nimi	Tänava aadress	Linn	Osariik	Sihtkood	Telefon
1001	Contoso, Ltd	2302 Harvard Ave	Pärnu	WA	98227	425-555-0222
1003	Adventure Works	1025 Columbia ring	Kirkland	WA	98234	425-555-0185
1005	Fourth Coffee	7007 Cornell St	Redmond	WA	98199	425-555-0201

Tabel Tellimused sisaldab teavet tellimuste, müügiesindajate, klientide ja toodete kohta. Pange tähele, et igal kirjel on kordumatu ID (Tellimuse ID). Osa selle tabeli teabest tuleb tükeldada täiendavaks tabeliks, mis sisaldab tellimuse üksikasju, nii et tabel Tellimused sisaldab ainult nelja veergu – tellimuse kordumatu ID, tellimuse kuupäev, müügiesindaja ID ja kliendi ID. Siin esitatud tabelit pole veel tabelisse Tellimuse üksikasjad tükeldatud.

Tellimused
Tellimuse ID	Tellimuse kuupäev	Müügiesindaja ID	Kliendi ID	Toote ID	Kogus
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

Tellimuse üksikasjad (nt toote ID ja kogus) teisaldatakse tabelist Tellimused välja ja talletatakse tabelis Tellimuse üksikasjad. Pidage meeles, et tellimusi on 9, seega on selles tabelis 9 kirjet. Pange tähele, et tabelil Tellimused on kordumatu ID (Tellimuse ID), millele viidatakse tabelist Tellimuse üksikasjad.

Tabeli Tellimused lõplik kujundus peaks välja nägema selline:

Tellimused
Tellimuse ID	Tellimuse kuupäev	Müügiesindaja ID	Kliendi ID
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

Tabel Tellimuse üksikasjad ei sisalda veerge, mis nõuaksid kordumatuid väärtusi (st primaarvõtit pole), seega pole üheski või kõigis veergudes lubatud liigseid andmeid sisaldada. Selle tabeli kaks kirjet ei tohiks siiski olla täiesti identsed (see reegel kehtib andmebaasi suvalise tabeli kohta). Selles tabelis peaks olema 17 kirjet, mis vastavad konkreetses järjestuses olevale tootele. Näiteks tellimuses 2349 koosnevad kolm C-789 toodet kogu tellimuse kahest osast.

Seetõttu peaks tabel Tellimuse üksikasjad välja nägema selline:

Tellimuse üksikasjad
Tellimuse ID	Toote ID	Kogus
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

Andmete kopeerimine ja kleepimine Excelist Accessi

Nüüd, kui teave müügiesindajate, klientide, toodete, tellimuste ja tellimuse üksikasjade kohta on Excelis jaotatud eraldi teemadeks, saate need andmed kopeerida otse Accessi, kus neist saavad tabelid.

Accessi tabelite vaheliste seoste loomine ja päringu käivitamine

Kui olete andmed Accessi teisaldanud, saate luua tabelite vahel seoseid ja seejärel luua päringuid erinevate teemade kohta teabe saamiseks. Näiteks saate luua päringu, mis tagastab tellimuse ID ja müügiesindajate nimed vahemikus 05.03.09 kuni 08.09.09.

Lisaks saate luua vorme ja aruandeid, et lihtsustada andmete sisestamist ja müügianalüüsi.

Kas vajate rohkem abi?

Võite alati küsida Exceli tehnikakogukonna eksperdilt või kogukonnafoorumites tuge.