Tietojen siirtäminen Excelistä Accessiin

Tässä artikkelissa kerrotaan, miten voit siirtää tiedot Excelistä Accessiin ja muuntaa tiedot relaatiotaulukoiksi niin, että voit käyttää Microsoft Exceliä ja Accessia yhdessä. Yhteenvetona voidaan todeta, että Access sopii parhaiten tietojen keräämiseen, tallentamiseen, kyselyjen suorittamiseen ja jakamiseen ja Excel tietojen laskemiseen, analysointiin ja visualisointiin.

Kahdessa artikkelissa, Tietojen hallinta Accessin tai Excelin avulla ja 10 tärkeintä syytä käyttää Accessia Excelissä, keskustellaan siitä, mikä ohjelma sopii parhaiten mihinkin tehtävään ja miten Excelin ja Accessin avulla voidaan yhdessä luoda käytännöllinen ratkaisu.

Kun siirrät tietoja Excelistä Accessiin, prosessissa on kolme perusvaihetta.

Vaihe 1: Tietojen tuominen Excelistä Accessiin

Tietojen tuominen voi olla paljon sujuvampaa, jos käytät aikaa tietojen valmistelemiseen ja siistimiseen. Tietojen tuominen vastaa muuttoa uuteen kotiin. Jos siivoat ja järjestät omaisuutesi ennen muuttoa, uuteen kotiin asettuminen on paljon helpompaa.

Tietojen puhdistaminen ennen niiden tuomista

Ennen tietojen tuomista Accessiin Excelissä on hyvä toimia seuraavasti:

• Muunna solut, jotka sisältävät ei-atomisia tietoja (eli useita arvoja yhdessä solussa) useiksi sarakkeiksi. Esimerkiksi Taidot-sarakkeen solu, joka sisältää useita taitoarvoja, kuten "C#-ohjelmointi", "VBA-ohjelmointi" ja "WWW-suunnittelu", tulisi jakaa omiksi sarakkeiksi, joista jokainen sisältää vain yhden osaamisarvon.
• POISTA.VÄLIT-komennolla voit poistaa alussa ja lopussa olevat välilyönnit sekä useita upotettuja välilyöntejä.
• Poista tulostumattomat merkit.
• Etsi ja korjaa oikeinkirjoitus- ja välimerkkivirheitä.
• Poista päällekkäiset rivit tai kenttien kaksoiskappaleet.
• Varmista, että tietosarakkeet eivät sisällä sekamuotoiluja, erityisesti tekstiksi muotoiltuja lukuja tai 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

Parhaan tietotyypin valitseminen tuotaessa

Accessin tuontitoiminnon aikana kannattaa tehdä hyviä valintoja, jotta saat vain vähän (jos ollenkaan) muunnosvirheitä, jotka vaativat manuaalisia toimia. Seuraavassa taulukossa on yhteenveto siitä, miten Excelin lukumuotoilut ja Access-tietotyypit muunnetaan tuotaessa tietoja Excelistä Accessiin. Taulukossa on myös vihjeitä parhaista tietotyypeistä ohjatun laskentataulukon tuomisen avulla.

Excelin lukumuoto	Accessin tietotyyppi	Kommentit	Parhaat käytännöt
Teksti	Teksti, muistio	Accessin Teksti-tietotyyppi tallentaa aakkosnumeerista tietoa, jossa on enintään 255 merkkiä. Accessin Muistio-tietotyyppi tallentaa aakkosnumeerista tietoa, jossa on enintään 65 535 merkkiä.	Valitse Muistio , jotta tietoja ei katkea.
Luku, prosenttiluku, murtoluku, tieteellinen	Numero	Accessissa on yksi luku-tietotyyppi, joka vaihtelee kentän koon ominaisuuden (tavu, kokonaisluku, pitkä kokonaisluku, yksittäinen, kaksinkertainen, desimaali) mukaan.	Vältä tietojen muuntovirheet valitsemalla Double .
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 laajempi: -657 434 (1. tammikuuta 100 jKr.) - 2 958 465 (31. joulukuuta 9999 jKr.). Koska Access ei tunnista 1904-päivämääräjärjestelmää (jota käytetään Excel for Macintoshissa), päivämäärät on muunnettava joko Excelissä tai Accessissa sekaannusten välttämiseksi. Lisätietoja on artikkeleissa Päivämääräjärjestelmän, muodon tai kaksinumeroisen vuosiluvun tulkinnan muuttaminen ja Tietojen tuominen tai linkittäminen Excel-työkirjaan.	Valitse Päivämäärä.
Time	Aika	Access ja Excel tallentavat aika-arvot samalla tietotyypillä.	Valitse aika, joka on yleensä oletusarvo.
Valuutta, kirjanpito	Valuutta	Accessin Valuutta-tietotyyppi tallentaa tiedot 8-tavuisina lukuina neljän desimaalin tarkkuudella. Sitä käytetään taloustietojen tallentamiseen ja arvojen pyöristämisen estämiseen.	Valitse valuutta, joka on yleensä oletusarvo.
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	Excelissä ja Accessissa oleva hyperlinkki sisältää URL- tai WWW-osoitteen, jota voit napsauttaa.	Valitse Hyperlinkki, muuten Access saattaa käyttää oletusarvoisesti Teksti-tietotyyppiä.

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

Lisätietoja on Accessin ohjeaiheessa : Tuo tai linkitä Excel-työkirjan tietoihin.

Tietojen helppo liittäminen automaattisesti

Excel-käyttäjien yleinen ongelma on samojen sarakkeiden tietojen liittäminen yhteen suureen laskentataulukkoon. Käytössäsi saattaa esimerkiksi olla kohteiden seurantaratkaisu, joka sai alkunsa Excelistä mutta joka sisältää nyt useiden työryhmien ja osastojen tiedostot. 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 samankaltaisten tietojen liittämiseen.

Paras ratkaisu on käyttää Accessia, jossa voit helposti tuoda ja liittää tietoja yhteen taulukkoon käyttämällä ohjattua laskentataulukon tuomista. Voit lisäksi 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 analysoinnilla

Ensi silmäyksellä tietojen normalisointi voi tuntua pelottavalta tehtävältä. Taulukoiden normalisointi Accessissa on onneksi paljon helpompaa ohjatun taulukon analysoimisen ansiosta.

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

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

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

• Voit muuntaa taulukon pienemmiksi taulukoiksi ja luoda automaattisesti perusavaimen ja viiteavaimen yhteyden taulukoiden välille.
• Lisää perusavain aiemmin luotuun kenttään, joka sisältää yksilöllisiä arvoja, tai luo uusi tunnuskenttä, joka käyttää Laskuri-tietotyyppiä.
• luoda automaattisesti yhteyksiä viite-eheyden säilyttämiseksi johdannaispäivitysten avulla. Johdannaispoistoja ei lisätä automaattisesti estämään tietojen poistaminen vahingossa, mutta voit helposti lisätä johdannaispoistoja myöhemmin.
• Etsi uusista taulukoista tarpeettomia tietoja tai tietojen kaksoiskappaleita (kuten sama asiakas, jolla on kaksi eri puhelinnumeroa) ja päivitä tämä haluamallasi tavalla.
• Varmuuskopioi alkuperäinen taulukko ja nimeä se uudelleen lisäämällä sen nimeen "_OLD". Luo sen jälkeen kysely, joka muodostaa alkuperäisestä taulukosta alkuperäisen taulukon nimen niin, että kaikki alkuperäiseen taulukkoon perustuvat lomakkeet ja raportit toimivat uudessa taulukkorakenteessa.

Lisätietoja on artikkelissa Tietojen normalisointi taulukon analysoinnilla.

Vaihe 3: Muodosta yhteys Excelin tietojen käyttämiseksi

Kun tiedot on normalisoitu Accessissa ja alkuperäiset tiedot rekonstruoiva kysely tai taulukko on luotu, sinun tarvitsee vain muodostaa yhteys Excelistä Access-tietoihin. Tietosi ovat nyt Accessissa ulkoisena tietolähteenä, joten ne voidaan yhdistää työkirjaan tietoyhteydellä. Tietolähteenä on tietosäilö, jota käytetään ulkoisen tietolähteen etsimiseen, siihen kirjautumiseen ja siihen kirjautumiseen. Yhteystiedot tallennetaan työkirjaan, ja ne voidaan tallentaa myös yhteystiedostoon, kuten Officen tietoyhteystiedostoon (ODC) tai tietolähteen nimitiedostoon (.dsn-tunniste). Kun olet muodostanut yhteyden ulkoisiin tietoihin, voit myös päivittää (tai 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 tuominen Accessiin

Tässä osassa käydään läpi seuraavat tietojen normalisoinnin vaiheet: Myyjä- ja Osoite-sarakkeiden arvojen pilkkominen atomisimpiin osiin, toisiinsa liittyvien aiheiden erottaminen omiin taulukoihinsa, näiden taulukoiden kopioiminen ja liittäminen Excelistä Accessiin, avainsuhteiden luominen juuri luotujen Access-taulukoiden välille sekä tietojen palauttamiseen tarkoitetun yksinkertaisen kyselyn luominen ja suorittaminen Accessissa.

Esimerkkitiedot normalisoimattomassa muodossa

Seuraava laskentataulukko sisältää ei-atomisia arvoja Myyjä- ja Osoite-sarakkeissa. Molemmat sarakkeet tulisi jakaa kahdeksi tai useammaksi erilliseksi sarakkeeksi. Tämä laskentataulukko sisältää myös tietoja myyjistä, tuotteista, asiakkaista ja tilauksista. Nämä tiedot tulisi myös jakaa aiheittain erillisiksi taulukoiksi.

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 dollaria	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 dollaria	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 dollaria	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2352	3/5/09	D-4420	3	7,25 dollaria	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Koch, Reed	2353	3/7/09	A-2275	6	16,75 dollaria	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

Tietoa pienimmissä osissaan: atomidata

Kun käsittelet tämän esimerkin tietoja, voit käyttää Excelin Teksti sarakkeeseen -komentoa ja erottaa solun "atomiset" osat (kuten katuosoite, postitoimipaikka, osavaltio ja postinumero) erillisiksi sarakkeiksi.

Seuraavassa taulukossa näkyvät samassa laskentataulukossa olevat uudet sarakkeet sen jälkeen, kun ne on jaettu siten, että kaikista arvoista tulee atomisia. Huomaa, että Myyjä-sarakkeen tiedot on jaettu Sukunimi- ja Etunimi-sarakkeisiin ja että Osoite-sarakkeen tiedot on jaettu Katuosoite-, Postitoimipaikka-, 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 pilkkominen järjestettyihin aiheisiin Excelissä

Seuraavat esimerkkitiedot sisältävät taulukot näyttävät samat tiedot Excel-laskentataulukosta, kun laskentataulukko on jaettu taulukoiksi myyjiä, tuotteita, asiakkaita ja tilauksia varten. Taulukon rakenne ei ole lopullinen, mutta se on oikealla tiedoilla.

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

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ämäärä, 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-taulukkoon. Pidä mielessä, että tilauksia on yhdeksän, joten on järkevää, että taulukossa on yhdeksän tietuetta. Huomaa, että Tilaukset-taulukolla on yksilöllinen tunnus (Tilaustunnus), johon viitataan Tilaustiedot-taulukosta.

Tilaukset-taulukon lopullisen rakenteen pitäisi näyttää tältä:

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-taulukko ei sisällä yksilöllisiä arvoja edellyttäviä sarakkeita (eli perusavainta ei ole), joten missään tai kaikissa sarakkeissa ei saa olla "päällekkäisiä" tietoja. Tässä taulukossa ei kuitenkaan saa olla kahta täysin samanlaista tietuetta (tämä sääntö koskee tietokannan kaikkia taulukoita). Tässä taulukossa pitäisi olla 17 tietuetta, joista jokainen vastaa yksittäisen tilauksen tuotetta. Esimerkiksi tilauksessa 2349 kolme C-789-tuotetta muodostavat yhden koko tilauksen kahdesta osasta.

Tilaustiedot-taulukon pitäisi sen vuoksi näyttää tältä:

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 myyjät, asiakkaat, tuotteet, tilaukset ja tilaustiedot on jaettu erillisiin aiheisiin Excelissä, voit kopioida nämä tiedot suoraan Accessiin, jossa ne muuttuvat taulukoiksi.

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 myyjien nimet tilauksista, jotka on syötetty välisenä aikana 5.3.2009–8.3.2009.

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

Tarvitsetko lisätietoja?

Voit aina kysyä neuvoa Excel Tech Community -yhteisön asiantuntijalta tai saada tukea yhteisöistä.