Tietojen siirtäminen Excelistä Accessiin

Tässä artikkelissa kerrotaan, miten voit siirtää tiedot Excelistä Accessiin ja muuntaa tiedot relaatiotaulukoiksi, jotta voit käyttää Microsoft Exceliä ja Accessia yhdessä. Yhteenvetona Access sopii parhaiten tietojen tallentamiseen, tallentamiseen, kyselyihin ja jakamiseen, ja Excel on paras tapa laskea, analysoida ja visualisoida tietoja.

Kahdessa artikkelissa Accessin tai Excelin avulla voit hallita tietoja ja10 tärkeintä syytä käyttää Accessia Excelin kanssa. Artikkelissa kerrotaan, mikä ohjelma sopii parhaiten tiettyyn tehtävään ja miten voit käyttää Exceliä ja Accessia yhdessä käytännöllisen ratkaisun luomiseksi.

Kun siirrät tietoja Excelistä Accessiin, prosessiin liittyy kolme perusvaihetta.

Vaihe 1: Tietojen tuominen Excelistä Accessiin

Tietojen tuominen on toiminto, joka voi sujua paljon sujuvammin, jos tietojen valmisteleminen ja puhdistaminen kestää jonkin aikaa. Tietojen tuominen on kuin uuteen kotiin siirtymistä. Jos siivoat ja järjestät omaisuutesi ennen muuttoasi, asettuminen uuteen kotiin on paljon helpompaa.

Tietojen puhdistaminen ennen tuontia

Ennen kuin tuot tietoja Accessiin, Excelissä kannattaa:

• Muunna solut, jotka sisältävät muita kuin atomitietoja (eli useita arvoja yhdessä solussa) useiksi sarakkeiksi. Esimerkiksi Taidot-sarakkeen solu, joka sisältää useita osaamisaluearvoja, kuten C#-ohjelmointi, VBA-ohjelmointi ja Verkkosuunnittelu, on jaettava eri sarakkeisiin, jotka sisältävät vain yhden osaamisalueen arvon.
• POISTA VÄLIT-komennolla etu- ja lopussa olevat välilyönnit sekä useita upotettuja välilyöntejä.
• Poista tulostumattomat merkit.
• Etsi ja korjaa oikeinkirjoitus- ja välimerkkivirheet.
• Poista rivien kaksoiskappaleet tai kenttien kaksoiskappaleet.
• Varmista, että tietosarakkeissa ei ole sekamuotoiluja, varsinkaan luvuiksi muotoiltuja lukuja tai numeroiksi muotoiltuja päivämääriä.

Lisätietoja on seuraavissa Excelin ohjeaiheissa:

• Kymmenen tapaa tarkistaa ja korjata tiedot
• Ainutkertaisten arvojen suodattaminen tai kaksoiskappaleiden poistaminen
• Tekstiksi tallennettujen lukujen muuntaminen luvuiksi
• Tekstiksi tallennettujen päivämäärien muuttaminen päivämäärämuotoon

Valitse paras tietotyyppi tuotaessa

Accessin tuontitoiminnon aikana haluat tehdä hyviä valintoja, jotta saat vain vähän (jos lainkaan) muuntovirheitä, jotka edellyttävät manuaalista interventiota. Seuraavassa taulukossa on yhteenveto siitä, miten Excelin lukumuodot ja Access-tietotyypit muunnetaan, kun tuot tietoja Excelistä Accessiin, ja annetaan vinkkejä parhaista tietotyypeistä ohjatussa laskentataulukon tuomisessa.

Excelin lukumuotoilu	Accessin tietotyyppi	Kommentit	Parhaat käytännöt
Teksti	Teksti, Muistio	Access-teksti-tietotyyppi tallentaa enintään 255 merkkiä aakkosnumeerisia tietoja. Access Memo -tietotyyppi tallentaa enintään 65 535 merkkiä aakkosnumeerisia tietoja.	Valitse Muistio , jos haluat välttää tietojen katkaisemisen.
Luku, Prosentti, Murtoluku, Funktiolaskin	Numero	Accessissa on yksi Luku-tietotyyppi, joka vaihtelee Kentän koko -ominaisuuden mukaan (Tavu, Kokonaisluku, Pitkä kokonaisluku, Yksittäinen, Kaksinkertainen, Desimaali).	Voit välttää tietojen muuntovirheet valitsemalla Kaksoisnapsauta .
Päivämäärä	Päivämäärä	Access ja Excel käyttävät samaa päivämäärän sarjanumeroa päivämäärien tallentamiseen. Accessissa päivämääräalue on suurempi: -657 434 (1. tammikuuta 100 a.d.) ja 2 958 465 (31. joulukuuta 9999). Koska Access ei tunnista vuoden 1904 päivämääräjärjestelmää (jota käytetään Excel for Macintoshissa), sinun on muunnettava päivämäärät joko Excelissä tai Accessissa sekaannuksen välttämiseksi. Lisätietoja on artikkelissa Päivämääräjärjestelmän, muodon tai kaksinumeroisen vuositulkinnan muuttaminen ja Excel-työkirjan tietojen tuominen tai linkittäminen.	Valitse Päivämäärä.
Time	Aika	Access ja Excel tallentavat molemmat aika-arvot käyttämällä samaa tietotyyppiä.	Valitse Aika, joka on yleensä oletus.
Valuutta, Kirjanpito	Valuutta	Accessissa Valuutta-tietotyyppi tallentaa tiedot kahdeksan tavun lukuina tarkkuudella neljään desimaaliin, ja sitä käytetään taloustietojen tallentamiseen ja arvojen pyöristämisen estämiseen.	Valitse Valuutta, joka on yleensä oletusvaluutta.
totuusarvo	Kyllä/Ei	Access käyttää arvoa -1 kaikille Kyllä-arvoille ja 0 kaikille Ei-arvoille, kun taas Excel käyttää arvoa 1 kaikille TOSI-arvoille ja 0 kaikille EPÄTOSI-arvoille.	Valitse Kyllä/Ei, joka muuntaa pohjana olevat arvot automaattisesti.
Hyperlinkki	Hyperlinkki	Excelin ja Accessin hyperlinkki sisältää URL-osoitteen tai verkko-osoitteen, jota voit napsauttaa ja seurata.	Valitse Hyperlinkki, muuten Access voi käyttää oletusarvoisesti Teksti-tietotyyppiä.

Kun tiedot ovat Accessissa, voit poistaa Excel-tiedot. Muista varmuuskopioida alkuperäinen Excel-työkirja ensin ennen sen poistamista.

Lisätietoja on Accessin ohjeaiheessa Excel-työkirjan tietojen tuominen tai linkittäminen.

Tietojen liittäminen automaattisesti helposti

Yleinen ongelma, joka Excelin käyttäjillä on, on samojen sarakkeiden tietojen liittäminen yhteen suureen laskentataulukkoon. Käytössäsi voi esimerkiksi olla Excelistä alkanut kohteiden seurantaratkaisu, joka on nyt kasvanut sisältämään useiden työryhmän ja osaston tiedostoja. Nämä tiedot voivat olla eri laskentataulukoissa ja työkirjoissa tai tekstitiedostoissa, jotka ovat muiden järjestelmien tietosyötteitä. Excelissä ei ole käyttöliittymäkomentoa tai helppoa tapaa liittää samankaltaisia tietoja.

Paras ratkaisu on käyttää Accessia, jossa voit helposti tuoda ja liittää tietoja yhteen taulukkoon ohjatun laskentataulukon tuomisen avulla. Lisäksi voit liittää paljon tietoja yhteen taulukkoon. Voit tallentaa tuontitoiminnot, lisätä ne ajoitettuina Microsoft Outlook -tehtävinä ja jopa automatisoida prosessin makrojen avulla.

Vaihe 2: Tietojen normalisointi ohjatulla Taulukon analysoiminen -toiminnolla

Ensi silmäyksellä tietojen normalisointiprosessi voi tuntua pelottavalta tehtävältä. Onneksi taulukoiden normalisointi Accessissa on paljon helpompaa ohjatun taulukon analysoinnin ansiosta.

1. Vedä valitut sarakkeet uuteen taulukkoon ja luo yhteydet automaattisesti

Kaksi. Painikekomentojen avulla voit nimetä taulukon uudelleen, lisätä perusavaimen, tehdä aiemmin luodusta sarakkeesta perusavaimen ja kumota viimeisen toiminnon

Tämän ohjatun toiminnon avulla voit tehdä seuraavat toimet:

• Muunna taulukko pienempien taulukoiden joukoksi ja luo automaattisesti perus- ja viiteavainyhteys taulukoiden välille.
• Lisää perusavain aiemmin luotuun kenttään, joka sisältää yksilöllisiä arvoja, tai luo uusi tunnuskenttä, joka käyttää Laskuri-tietotyyppiä.
• Luo automaattisesti yhteyksiä viite-eheyden valvomiseksi johdannaispäivitysten avulla. Johdannaispoistoja ei lisätä automaattisesti, jotta tietoja ei poisteta vahingossa, mutta voit helposti lisätä johdannaispoistoja myöhemmin.
• Etsi uusista taulukoista tarpeettomia tai päällekkäisiä tietoja (kuten sama asiakas, jolla on kaksi eri puhelinnumeroa) ja päivitä se haluamallasi tavalla.
• Varmuuskopioi alkuperäinen taulukko ja nimeä se uudelleen liittämällä sen nimeen "_OLD". Luot sitten kyselyn, joka rekonstruoi alkuperäisen taulukon alkuperäisen taulukon nimellä, jotta kaikki alkuperäiseen taulukkoon perustuvat aiemmin luodut lomakkeet tai raportit toimivat uuden taulukkorakenteen kanssa.

Lisätietoja on artikkelissa Tietojen normalisointi Taulukon analysointi -toiminnon avulla.

Vaihe 3: Yhteyden muodostaminen Access-tietoihin Excelistä

Kun tiedot on normalisoitu Accessissa ja on luotu kysely tai taulukko, joka rekonstruoi alkuperäiset tiedot, on helppo muodostaa yhteys Access-tietoihin Excelistä. Tietosi ovat nyt Accessissa ulkoisena tietolähteenä, joten ne voidaan yhdistää työkirjaan tietoyhteyden kautta, joka on tietosäilö, jota käytetään ulkoisen tietolähteen etsimiseen, kirjautumiseen ja käyttämiseen. Yhteystiedot tallennetaan työkirjaan, ja ne voidaan tallentaa myös yhteystiedostoon, kuten Office Data Connection (ODC) -tiedostoon (.odc-tiedostotunniste) tai tietolähteen nimitiedostoon (.dsn-tunniste). Kun olet muodostanut yhteyden ulkoisiin tietoihin, voit myös päivittää Excel-työkirjan Automaattisesti Accessista aina, kun tiedot päivitetään Accessissa.

Lisätietoja on artikkelissa Tietojen tuominen ulkoisista tietolähteistä (Power Query).

Tietojen vieminen Accessiin

Tässä osassa esitellään seuraavat vaiheet tietojen normalisoimisessa: Myyjä- ja Osoite-sarakkeiden arvojen jakaminen niiden atomiosiisi, toisiinsa liittyvien aiheiden erottaminen omiin taulukoihin, taulukoiden kopioiminen ja liittäminen Excelistä Accessiin, uusien luotujen Access-taulukoiden välisten avainsuhteiden luominen ja yksinkertaisen kyselyn luominen ja suorittaminen Accessissa tietojen palauttamiseksi.

Esimerkkitiedot normalisoimattomassa muodossa

Seuraava laskentataulukko sisältää muut kuin atomiarvot Myyjä-sarakkeessa ja Osoite-sarakkeessa. Molemmat sarakkeet on jaettava kahteen tai useampaan erilliseen sarakkeeseen. Tässä laskentataulukossa on myös tietoja myyjistä, tuotteista, asiakkaista ja tilauksista. Nämä tiedot olisi myös jaettava aiheittain erillisiin taulukoihin.

Myyjä	Tilauksen tunnus	Tilauksen päivämäärä	Tuotetunnus	Määrä	Hinta	Asiakkaan nimi	Osoite	Puhelin
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

Pienimmissä osissa olevat tiedot: atomitiedot

Kun käsittelet tässä esimerkissä olevia tietoja, excelin Teksti sarakkeeseen - komennolla voit erottaa solun atomiosat (kuten katuosoitteen, kaupungin, osavaltion ja postinumeron) erillisiin sarakkeisiin.

Seuraavassa taulukossa näkyvät saman laskentataulukon uudet sarakkeet sen jälkeen, kun ne on jaettu atomiarvojen lisääminen. Huomaa, että Myyjä-sarakkeen tiedot on jaettu Sukunimi- ja Etunimi-sarakkeisiin ja että Osoite-sarakkeen tiedot on jaettu Katuosoite-, Kaupunki-, Osavaltio- ja Postinumero-sarakkeisiin. Nämä tiedot ovat "ensimmäisessä normaalimuodossa".

Sukunimi	Etunimi	Katuosoite	Kaupunki	Tila	Postinumero
Li	Yale	2302 Harvard Ave	Kotka	WA	98227
Adams	Ellen	1025 Columbia Circle	Tampere	WA	98234
Hance	Jim	2302 Harvard Ave	Kotka	WA	98227
Koch	Reed	7007 Cornell St Redmond	Redmond	WA	98199

Tietojen jako järjesteltyihin aiheisiin Excelissä

Seuraavissa esimerkkitietojen taulukoissa näkyvät samat tiedot Excel-laskentataulukosta sen jälkeen, kun ne on jaettu taulukoihin myyjille, tuotteille, asiakkaille ja tilauksille. Taulukon rakenne ei ole lopullinen, mutta se on oikealla tiellä.

Myyjät-taulukko sisältää vain tietoja myyntihenkilöstöstä. Huomaa, että kullakin tietueella on yksilöllinen tunnus (Myyjätunnus). Myyjätunnus-arvoa käytetään Tilaukset-taulukossa tilausten yhdistämiseen myyjiin.

Myyjät
Myyjän tunnus	Sukunimi	Etunimi
101	Li	Yale
103	Adams	Ellen
105	Hance	Jim
107	Koch	Reed

Tuotteet-taulukko sisältää vain tietoja tuotteista. Huomaa, että kullakin tietueella on yksilöllinen tunnus (tuotetunnus). Tuotetunnus-arvoa käytetään yhdistämään tuotetiedot Tilaustiedot-taulukkoon.

Tuotteet
Tuotetunnus	Hinta
A-2275	16.75
B-205	4.50
C-789	7.00
C-795	9.75
D-4420	7.25
F-198	5,25

Asiakkaat-taulukko sisältää vain tietoja asiakkaista. Huomaa, että kullakin tietueella on yksilöllinen tunnus (asiakastunnus). Asiakastunnus-arvoa käytetään asiakastietojen yhdistämiseen Tilaukset-taulukkoon.

Asiakkaat
Asiakastunnus	Nimi	Katuosoite	Kaupunki	Tila	Postinumero	Puhelin
1001	Contoso, Ltd.	2302 Harvard Ave	Kotka	WA	98227	425-555-0222
1003	Adventure Works	1025 Columbia Circle	Tampere	WA	98234	425-555-0185
1005	Fourth Coffee	7007 Cornell St	Redmond	WA	98199	425-555-0201

Tilaukset-taulukko sisältää tietoja tilauksista, myyjistä, asiakkaista ja tuotteista. Huomaa, että kullakin tietueella on yksilöllinen tunnus (tilaustunnus). Osa tämän taulukon tiedoista on jaettava lisätaulukkoon, joka sisältää tilaustiedot, jotta Tilaukset-taulukossa on vain neljä saraketta: yksilöllinen tilaustunnus, tilauspäivä, myyjän tunnus ja asiakastunnus. Tässä näkyvää taulukkoa ei ole vielä jaettu Tilaustiedot-taulukkoon.

Tilaukset
Tilauksen tunnus	Tilauksen päivämäärä	Myyjän tunnus	Asiakastunnus	Tuotetunnus	Määrä
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

Tilaustiedot, kuten tuotetunnus ja määrä, siirretään pois Tilaukset-taulukosta ja tallennetaan Tilaustiedot-nimiseen taulukkoon. Muista, että tilauksia on 9, joten taulukossa on 9 tietuetta. Huomaa, että Tilaukset-taulukossa on yksilöllinen tunnus (Tilaustunnus), johon viitataan Tilaustiedot-taulukosta.

Tilaukset-taulukon lopullisen rakenteen pitäisi näyttää seuraavalta:

Tilaukset
Tilauksen tunnus	Tilauksen päivämäärä	Myyjän tunnus	Asiakastunnus
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

Tilaustiedot-taulukossa ei ole sarakkeita, jotka edellyttävät yksilöllisiä arvoja (eli perusavainta ei ole), joten kaikki tai kaikki sarakkeet voivat sisältää tarpeettomia tietoja. Tämän taulukon kahden tietueen ei kuitenkaan pitäisi olla täysin samanlaisia (tämä sääntö koskee mitä tahansa tietokannan taulukkoa). Tässä taulukossa pitäisi olla 17 tietuetta , joista jokainen vastaa yksittäistä tilausta. Esimerkiksi järjestyksessä 2349 kolme C-789-tuotetta muodostavat yhden koko tilauksen kahdesta osasta.

Tilauksen tiedot -taulukon pitäisi siksi näyttää seuraavalta:

Tilauksen tiedot
Tilauksen tunnus	Tuotetunnus	Määrä
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

Tietojen kopioiminen ja liittäminen Excelistä Accessiin

Nyt kun tiedot myyjistä, asiakkaista, tuotteista, tilauksista ja tilaustiedoista on jaettu erillisiin aiheisiin Excelissä, voit kopioida nämä tiedot suoraan Accessiin, jossa niistä tulee taulukoita.

Yhteyksien luominen Access-taulukoiden välille ja kyselyn suorittaminen

Kun olet siirtänyt tiedot Accessiin, voit luoda yhteyksiä taulukoiden välille ja luoda sitten kyselyjä, jotka palauttavat tietoja eri aiheista. Voit esimerkiksi luoda kyselyn, joka palauttaa tilaustunnuksen ja 3.5.2009–08.3.2009 annettujen tilausten myyjien nimet.

Lisäksi voit luoda lomakkeita ja raportteja, jotka helpottavat tietojen syöttämistä ja myynnin analysointia.

Tarvitsetko lisätietoja?

Voit aina pyytää apua Excel Tech Communityn asiantuntijalta tai saada tukea yhteisöissä.