Tietojen siirtäminen Excelistä Accessiin

Tässä artikkelissa kerrotaan, miten voit siirtää tietoja Excelistä käyttämään ja muuntamaan tietoja relaatio taulukoihin, jotta voit käyttää Microsoft Exceliä ja Accessia yhdessä. Yhteenvetona on parasta käyttää tietojen tallentamiseen, tallentamiseen, kyselyyn ja jakamiseen, ja Excel sopii parhaiten tietojen laskemiseen, analysointiin ja visualisointiin.

Kaksi artikkelia, jotka käyttävät Accessia tai Exceliä tietojen hallintaan , ja kymmenen syytä käyttää Accessia Excelissä, keskustella siitä, mikä ohjelma sopii parhaiten tiettyyn tehtävään, ja miten voit käyttää Exceliä ja käyttää yhdessä käytännön ratkaisun luomiseen.

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

kolme perusvaihetta

Huomautus: Lisä tietoja tietojen mallinnuksesta ja yhteyksistä Accessissa on Ohje aiheessa tieto kannan suunnittelun perusteet.

Vaihe 1: tietojen tuominen Excelistä Accessiin

Tietojen tuominen on toiminto, joka voi sujuvammin, jos tietojen valmisteleminen ja puhdistaminen kestää jonkin aikaa. Tietojen tuominen on kuin siirtyminen uuteen kotiin. Jos puhdistat ja järjestät omaisuutensa ennen muuttoa, uuteen kotiin asettuminen on paljon helpompaa.

Tietojen puhdistaminen ennen tuontia

Ennen kuin tuot tietoja Accessiin, Excel kannattaa:

  • Muunna solut, jotka sisältävät muita kuin Atomi tietoja (eli useita arvoja yhdessä solussa) useisiin sarakkeisiin. Esimerkiksi osaamis alueet-sarakkeessa oleva solu, joka sisältää useita osaamis alue arvoja, kuten C#-ohjelmointi, VBA-ohjelmointi ja verkko suunnittelu, pitäisi jakaa erillisiin sarakkeisiin, jotka sisältävät vain yhden osaamis arvo arvon.

  • TRIM-komennolla voit poistaa rivivälistys-, perään-ja useita upotettuja väli lyöntejä.

  • Poistaa tulostumattomat merkit.

  • Etsi ja korjaa oikeinkirjoitus-ja väli merkki virheet.

  • Poista rivien kaksoiskappaleet tai kenttien kaksoiskappaleet.

  • Varmista, että tieto sarakkeet eivät sisällä sekamuotoisia muotoiluja, erityisesti lukuja, jotka on muotoiltuna tekstiksi tai luvuiksi muotoiltuina.

Saat lisä tietoja seuraavista Excelin ohje aiheista:

Huomautus: Jos tietojen puhdistus tarpeet ovat monimutkaisia tai sinulla ei ole aikaa tai resursseja automatisoida prosessia itse, kannattaa ehkä käyttää kolmannen osapuolen toimittajaa. Jos haluat lisä tietoja, Etsi suosikki haku koneesi verkko selaimessa haku "tietojen puhdistus ohjelmisto" tai "tietojen laatu".

Parhaan tieto tyypin valitseminen tuotaessa

Accessissa tapahtuvan tuonti toiminnon aikana haluat tehdä hyviä vaihto ehtoja, jotta saat joitakin (mahdollisia) muunto virheitä, jotka edellyttävät manuaalista puuttumista. Seuraavassa taulukossa on yhteenveto siitä, miten Excelin luku muotoilut ja Access-tieto tyypit muunnetaan, kun tuot tietoja Excelistä Accessiin, ja antaa vinkkejä parhaista tieto tyypeistä, joita voit valita ohjatussa laskenta taulukon tuonnissa.

Excel-luku muoto

Accessin tietotyyppi

Kommentit

Parhaat käytännöt

Teksti

Teksti, memo

Käytä teksti-tieto tyyppiä tallentaa aakkosnumeeriset tiedot enintään 255 merkkiä. Käytä memo-tieto tyyppiä, joka tallentaa aakkosnumeerisia tietoja enintään 65 535 merkkiä.

Valitse Muistio , jos haluat välttää tietojen katkaisemisen.

Luku, osuus, murto-osa, tieteelliset

Numero

Accessissa on yksi numero-tieto tyyppi, joka vaihtelee kentän koko-ominaisuuden mukaan (tavu, kokonaisluku, Pitkä kokonaisluku, yksittäinen, kaksinkertainen, Desi maali).

Vältä tietojen muunto virheitä valitsemalla kaksinkertainen .

Päivämäärä

Päivämäärä

Access ja Excel molemmat käyttävät samaa järjestys päivämäärä numeroa päivä määrien tallentamiseen. Accessissa päivämäärä väli on suurempi: From-657 434 (1. tammi kuuta 100 jKr) – 2 958 465 (31. joulu kuuta, 9999 jKr).

Koska Access ei tunnista 1904-päivämäärä järjestelmää (jota käytetään Macintosh Excelissä), sinun on muunnettava päivä määrät joko Excelissä tai Accessissa sekaannusten välttämiseksi.

Lisä tietoja on Ohje aiheissa päivämäärä järjestelmän, muodon tai kaksinumeroisen vuosi luvun 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 tieto tyyppiä.

Valitse aika, joka on yleensä oletus arvo.

Valuutta, kirjan pito

Valuutta

Accessissa valuutta-tieto tyyppi tallentaa tiedot 8-tavuisina numeroina neljän Desi maalin tarkkuudella, ja niitä käytetään taloudellisten tietojen tallentamiseen ja arvojen pyöristämiseen.

Valitse Valuutta, joka on yleensä oletus arvo.

totuusarvo

Kyllä/Ei

Access käyttää-1 kaikille kyllä-arvoille ja 0 kaikille ei-arvoille, kun taas Excel käyttää arvoa 1 kaikissa TODELLISINA arvoina ja Nollaa kaikki väärät arvot.

Valitse Kyllä/ei, joka muuntaa pohjana olevat arvot automaattisesti.

Hyperlinkki

Hyperlinkki

Excelissä ja Accessissa oleva hyperlinkki sisältää URL-osoitteen tai verkko-osoite, jota voi napsauttaa ja seurata.

Valitse hyperlinkki, muuten Accessissa voi käyttää teksti-tieto tyyppiä oletusarvoisesti.

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

Lisä tietoja on artikkelissa Access-Ohje aiheen tuominen tai linkittäminen Excel-työkirjan tietoihin.

Tietojen automaattinen liittäminen helpolla tavalla

Yleinen ongelma, jossa Excel-käyttäjät ovat liittämässä tietoja samoihin sarakkeisiin yhdeksi suureksi laskenta taulukoksi. Sinulla voi esimerkiksi olla resurssien seuranta ratkaisu, joka on käynnistynyt Excelissä, mutta on nyt kasvanut sisältämään tiedostoja useista työryhmistä ja osastoista. Nämä tiedot voivat olla eri laskenta taulukoissa ja työkirjoissa tai teksti tiedostoissa, jotka ovat tieto syötteitä muista järjestelmistä. Vastaavia tietoja ei voi liittää Excelissä mitään käyttö liittymä komentoa tai helppoa tapaa.

Paras ratkaisu on käyttää Accessia, jossa voit helposti tuoda ja liittää tietoja yhteen taulukkoon ohjatulla laskenta taulukon tuonti toiminnolla. Lisäksi voit liittää useita tietoja yhteen taulukkoon. Voit tallentaa tuonti toiminnot, lisätä ne ajoitettuna Microsoft Outlook-tehtävinä ja automatisoida prosessin myös makrojen avulla.

Vaihe 2: tietojen normalisointi ohjatun taulukon analysoinnin avulla

Ensi silmäyksellä tietojen normalisointi prosessi saattaa vaikuttaa pelottavalta tehtävältä. Onneksi Accessissa olevien taulu koiden normalisointi on paljon helpompaa, kun Ohjattu taulukon analysoiminen-toiminto on käynnissä.

ohjattu taulukon analysoiminen

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

2. nimeä taulukko uudelleen käyttämällä painike komentoja, lisää perusavain, luo aiemmin luotu sarake perusavaimeksi ja Kumoa edellinen toiminto.

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

  • Muunna taulukko pienten taulu koiden joukkoon ja luo automaattisesti taulu koiden välille perusavain-ja viite avain yhteys.

  • Lisää perusavain aiemmin luotuun kenttään, joka sisältää yksilöllisiä arvoja, tai luo uusi tunnus kenttä, joka käyttää laskuri-tieto tyyppiä.

  • Luo suhteet automaattisesti, jos haluat pakottaa viite-eheyden johdannais päivityksillä. Johdannais poistoja ei lisätä automaattisesti, jos haluat estää tietojen tahattoman poistamisen, mutta voit helposti lisätä johdannais poistoja myöhemmin.

  • Hae uusista tauluista tarpeettomia tai päällekkäisiä tietoja (kuten sama asiakas, jossa on kaksi eri Puhelin numeroa) ja päivitä se haluamallasi tavalla.

  • Varmuuskopioi alkuperäinen taulukko ja nimeä se uudelleen liittämällä sen nimeen "_OLD". Sen jälkeen voit luoda kyselyn, joka luo alkuperäisen taulukon uudelleen alkuperäisen taulukon nimen kanssa, jotta alkuperäiseen taulukkoon perustuvat aiemmin luodut lomakkeet ja raportit toimivat uuden taulukon rakenteen kanssa.

Lisä tietoja on Ohje aiheessa tietojen normalisointi taulukon analysoinnin avulla.

Vaihe 3: yhteyden muodostaminen Access-tietoihin Excelistä

Kun tiedot on normalisoitu Accessissa ja on luotu kysely tai taulukko, joka luo alkuperäiset tiedot uudelleen, se on yksinkertainen tapa muodostaa yhteys Access-tietoihin Excelistä. Tiedot ovat nyt Accessissa ulkoisessa tieto lähteessä, joten ne voidaan yhdistää työkirjaan tieto yhteyden kautta, joka on tieto säilö, jota käytetään ulkoisen tieto lähteen etsimiseen, kirjautumiseen ja käyttämiseen. Yhteys tiedot tallennetaan työkirjaan, ja ne voidaan tallentaa myös yhteys tiedostoon, kuten Office-tieto yhteys tiedostoon (. odc-tiedosto nimen tunniste) tai tieto lähteen nimi tiedostoon (. DSN-tunniste). Kun olet saanut yhteyden ulkoisiin tietoihin, voit päivittää Excel-työkirjan (tai päivittää sen) automaattisesti Accessista aina, kun tiedot päivitetään Accessissa.

Lisä tietoja on kohdassa tietojen tuominen ulkoisista tieto lähteistä (Power Query).

Tietojen hakeminen Accessiin

Tässä osiossa käydään läpi seuraavat tietojen normalisointi vaiheet: myynti-ja osoite-sarakkeiden arvojen jakaminen kaikkein Atomi kappaleiksi, toisiinsa liittyvien aiheiden erottaminen omiin taulu koihin, näiden taulu koiden kopioiminen ja liittäminen Excelistä Voit käyttää, luoda avain yhteyksiä juuri luotujen käyttö oikeus taulukoiden välille sekä luoda ja suorittaa yksinkertaisen kyselyn tietojen palauttamiseen Accessissa.

Esimerkki tiedot normalisoitumattoman lomakkeen muodossa

Seuraava laskenta taulukko sisältää muita kuin Atomi arvoja myyjä-sarakkeessa ja osoite-sarakkeessa. Molemmat sarakkeet on jaettava kahteen tai useampaan erilliseen sarakkeeseen. Tämä laskenta taulukko sisältää myös tietoja myynti henkilöistä, tuotteista, asiakkaista ja tila uksista. Nämä tiedot on myös jaettava edelleen aiheen mukaan erillisiin taulukoihin.

Myyjä

Tilauksen tunnus

Tilauksen päivämäärä

Tuotetunnus

QTY

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

Aatami, Ellen

2350

3/4/09

A-2275

2

$16,75

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Aatami, Ellen

2350

3/4/09

F-198

6

$5,25

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Aatami, 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

Tiedot pienin-osissa: atomi tiedot

Jos käytät tässä esimerkissä tietoja, voit käyttää Excelin teksti-sarake -komentoa erottamaan solun "atomi" osat (kuten katu osoite, kaupunki, osavaltio ja posti numero) erillisiin sarakkeisiin.

Seuraava taulukko sisältää saman laskenta taulukon uudet sarakkeet, kun ne on jaettu niin, että kaikki arvot ovat atomisia. Huomaa, että myyjä-sarakkeen tiedot on jaettu suku nimi-ja etunimi-sarakkeisiin ja että osoite-sarakkeen tiedot on jaettu katu osoite-, kaupunki-, osavaltio-ja posti numero-sarakkeisiin. Tämä tieto on "ensimmäinen normaalimuoto".

Sukunimi

Etunimi

 

Katuosoite

Kaupunki

Osavaltio

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 jakaminen Excelin järjestäytyneeseen aiheeseen

Useiden esimerkki tietojen taulu koissa näkyy samat tiedot Excel-laskenta taulukosta sen jälkeen, kun ne on jaettu taulu koihin myyjille, tuotteille, asiakkaille ja tila uksille. Taulukon rakenne ei ole lopullinen, mutta se on oikeilla jäljillä.

Myyjätaulukko sisältää vain myynti henkilöstöä koskevia tietoja. Huomaa, että kullakin tietueella on yksilöivä tunniste (myyjä ID). Tila ukset-taulukon MYYJÄTUNNUS-arvoa käytetään tilausten yhdistämiseen myyjille.

Myyjiä

Myyjän tunniste

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 tunniste (tuote tunniste). Tuote tunnuksen arvoa käytetään tuote tietojen yhdistämiseen tilaus tiedot-taulukkoon.

Tuotteiden

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öivä tunniste (asiakas numero). Asiakas tunnus-arvoa käytetään asiakas tietojen yhdistämiseen tila ukset-taulukkoon.

Asiakkaat

Asiakastunnus

Nimi

Katuosoite

Kaupunki

Osavaltio

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

Tila ukset-taulukko sisältää tietoja tila uksista, myynti henkilöistä, asiakkaista ja tuotteista. Huomaa, että kullakin tietueella on yksilöivä tunniste (tilaus numero). Osa tämän taulukon tiedoista on jaettava lisä taulukkoon, joka sisältää tilaus tiedot niin, että tila ukset-taulukossa on vain neljä saraketta – yksilöivä tilaus tunnus, tilaus päivämäärä, myyjätunnus ja asiakas tunnus. Tässä näytettävää taulukkoa ei ole vielä jaettu tilaus tiedot-taulukkoon.

Tilaukset

Tilauksen tunnus

Tilauksen päivämäärä

Myyjän tunniste

Asiakastunnus

Tuotetunnus

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

Tilaus tiedot, kuten tuote tunnus ja määrä siirretään tila ukset-taulukosta ja tallennetaan tilaus tiedot-taulukkoon. Muista, että on olemassa 9 tilausta, joten tässä taulukossa on 9 tietuetta. Huomaa, että tila ukset-taulukossa on yksilöivä tunnus (tilaus tunnus), johon viitataan tilaus tiedot-taulukossa.

Tila ukset-taulukon lopullisen rakenteen pitäisi näyttää seuraavalta:

Tilaukset

Tilauksen tunnus

Tilauksen päivämäärä

Myyjän tunniste

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

Tilaus tiedot-taulukko ei sisällä sarakkeita, jotka vaativat 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 identtisiä (tämä sääntö koskee mitä tahansa tieto kannan taulukkoa). Tässä taulukossa pitäisi olla 17 tietuetta, jotka kukin vastaavat yksittäistä tilausta. Esimerkiksi järjestys 2349-kohdassa kolme C-789-tuotetta sisältävät yhden koko tila uksen kahdesta osasta.

Tilaus tiedot-taulukon pitäisi näin ollen näyttää seuraavanlaiselta:

Tilaus tiedot

Tilaustunnus

Tuotetunnus

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

Tietojen kopioiminen ja liittäminen Excelistä Accessiin

Nyt kun myyjät, Asiakkaat, tuotteet, tila ukset ja tilaus tiedot on jaettu eri aihe isiin Excelissä, voit kopioida tiedot suoraan Accessiin, jolloin niistä tulee tauluja.

Yhteyksien luominen käyttö oikeus taulukoiden välille ja kyselyn suorittaminen

Kun olet siirtänyt tiedot Accessiin, voit luoda yhteyksiä taulukon välille ja luoda kyselyitä, jotka palauttavat tietoja eri aiheista. Voit esimerkiksi luoda kyselyn, joka palauttaa tilaus tunnuksen sekä niiden myynti henkilöiden nimet, jotka on syötetty 3/05/09 ja 3/08/09 välillä.

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

Tarvitsetko lisätietoja?

Voit aina kysyä neuvoa Excel Tech Community -yhteisön asiantuntijalta, saada tukea yhteisön vastauksista tai ehdottaa uutta ominaisuutta tai parannusta Excel User Voice -sivustolla.

Kehitä Office-taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

Kiitos palautteesta!

Kiitos palautteestasi! Näyttää siltä, että Office-tukiedustajamme avusta voi olla sinulle hyötyä.

×