Pastaba: "Microsoft Access" nepalaiko "Excel" duomenų importavimo naudojant pritaikytą slaptumo žymą. Norėdami išspręsti šią problemą, prieš importuodami galite pašalinti žymą ir importavę iš naujo pritaikyti etiketę. Daugiau informacijos žr. Slaptumo žymų taikymas "Office" failams ir el. paštui.
Šiame straipsnyje aiškinama, kaip perkelti duomenis iš "Excel" į "Access" ir konvertuoti duomenis į sąryšinių lentelių, kad galėtumėte naudoti "Microsoft Excel" ir "Access" kartu. Norint apibendrinti, "Access" geriausiai tinka duomenims užfiksuoti, saugoti, teikti užklausas ir bendrinti, o "Excel" geriausiai tinka duomenims skaičiuoti, analizuoti ir vizualizuoti.
Du straipsniai: "Access" arba "Excel" naudojimas duomenims valdyti ir 10 priežasčių, kodėl "Access" naudojama su "Excel", aptarkite, kuri programa geriausiai tinka konkrečiai užduočiai, ir kaip kartu naudoti "Excel" ir "Access", kad sukurtumėte praktišką sprendimą.
Perkėlus duomenis iš "Excel" į "Access", procesą galima atlikti trimis pagrindiniais veiksmais.
Pastaba: Informacijos apie duomenų modeliavimą ir ryšius programoje "Access" žr. Duomenų bazės kūrimo pagrindai.
1 veiksmas: duomenų importavimas iš "Excel" į "Access"
Duomenų importavimas – tai operacija, kuri gali daug sklandžiau atlikti, jei šiek tiek užtruksite duomenų paruošimui ir valymui. Duomenų importavimas yra tarsi perėjimas į naują namą. Jei prieš persikeldami valote ir tvarkote savo turtą, daug lengviau atsisėsti į naujus namus.
Duomenų valymas prieš importuojant
Prieš importuojant duomenis į "Access", programoje "Excel" naudinga:
-
Konvertuoti langelius, kuriuose yra ne atominės reikšmės (t. y. kelios reikšmės viename langelyje) į kelis stulpelius. Pavyzdžiui, langelis stulpelyje "Įgūdžiai", kuriame yra kelios įgūdžių reikšmės, pvz., "C# programavimas", "VBA programavimas" ir "žiniatinklio dizainas", turėtų būti suskirstytas į atskirus stulpelius, kuriuose yra tik viena įgūdžių reikšmė.
-
Naudokite komandą TRIM, kad pašalintumėte pradžioje, pabaigoje ir kelis įdėtuosius tarpus.
-
Pašalinkite nespausdinamus simbolius.
-
Raskite ir ištaisykite rašybos ir skyrybos klaidas.
-
Pašalinkite pasikartojančias eilutes arba pasikartojančius laukus.
-
Įsitikinkite, kad duomenų stulpeliuose nėra mišraus formato, ypač skaičių, suformatuotų kaip tekstas arba datos, suformatuotos kaip skaičiai.
Daugiau informacijos žr. šiose "Excel" žinyno temose:
Pastaba: Jei duomenų valymo poreikiai yra sudėtingi arba neturite laiko ar išteklių procesui automatizuoti patys, galite naudoti trečiosios šalies tiekėją. Norėdami gauti daugiau informacijos, savo žiniatinklio naršyklėje ieškokite "duomenų valymo programinė įranga" arba "duomenų kokybė".
Importuodami pasirinkite geriausią duomenų tipą
"Access" importavimo operacijos metu norite priimti gerus sprendimus, kad gautumėte kelias (jei tokių yra) konvertavimo klaidas, kurioms atlikti reikės neautomatinio įsikišimo. Šioje lentelėje apibendrinama, kaip "Excel" skaičių formatai ir "Access" duomenų tipai konvertuojami importuojant duomenis iš "Excel" į "Access", ir pateikiama patarimų, kokius geriausius duomenų tipus pasirinkti skaičiuoklės importavimo vediklyje.
"Excel" skaičių formatas |
„Access“ duomenų tipas |
Komentarai |
Geriausia praktika |
---|---|---|---|
Tekstas |
Tekstas, atmintinė |
"Access" teksto duomenų tipe saugomi raidiniai ir skaitiniai duomenys iki 255 simbolių. "Access Memo" duomenų tipe saugomi raidiniai ir skaitiniai duomenys iki 65 535 simbolių. |
Pasirinkite Atmintinė , kad nepraeitumėte jokių duomenų. |
Skaičius, procentas, trupmena, mokslinis |
Skaičius |
"Access" turi vieną skaičių duomenų tipą, kuris priklauso nuo ypatybės Lauko dydis (Baitas, Sveikasis skaičius, Ilgasis sveikasis skaičius, Vienas, Dvigubas, Dešimtainis). |
Pasirinkite Dvigubas , kad išvengtumėte duomenų konvertavimo klaidų. |
Data |
Data |
Abi programos "Access" ir "Excel" naudoja tą patį datų sekos numerį datoms saugoti. Programoje "Access" datos diapazonas yra didesnis: nuo -657 434 (100 m. sausio 1 d.) iki 2 958 465 (9999 m. gruodžio 31 d.). Programa Access neatpažįsta 1904 m. datos sistemos (naudojamos "Excel", skirtoje Macintosh versijai), todėl turite konvertuoti datas naudodami "Excel" arba "Access", kad išvengtumėte painiavos. Daugiau informacijos žr . Datos sistemos, formato arba dviejų skaitmenų metų interpretavimo keitimas ir Duomenų importavimas arba susiejimas "Excel" darbaknygėje. |
Pasirinkite Data. |
Laikas |
Laikas |
"Access" ir "Excel" laiko reikšmes saugo naudodamos tą patį duomenų tipą. |
Pasirinkite Laikas, kuris paprastai yra numatytasis. |
Valiuta, apskaita |
Valiuta |
Programoje "Access" duomenų tipas Valiuta saugo duomenis kaip 8 baitų skaičius tiksliai iki keturių dešimtainių skilčių ir naudojamas finansiniams duomenims saugoti ir reikšmių apvalinimo prevencijai. |
Pasirinkite Valiuta, kuri paprastai yra numatytoji. |
bulio logika |
Taip / Ne |
Programa "Access" naudoja -1 visoms Taip reikšmėms ir 0 visoms reikšmėms Ne, o "Excel" naudoja 1 visoms REIKŠMĖMS TRUE, o 0 – visoms FALSE reikšmėms. |
Pasirinkite Taip/ne, kuri automatiškai konvertuos esamas reikšmes. |
Hipersaitas |
Hipersaitas |
"Excel" ir "Access" hipersaite yra URL arba žiniatinklio adresas, kurį galite spustelėti ir stebėti. |
Pasirinkite Hipersaitas, kitu atveju pagal numatytuosius parametrus "Access" gali naudoti teksto duomenų tipą. |
Kai duomenys yra "Access", galite panaikinti "Excel" duomenis. Nepamirškite prieš panaikindami pradinę "Excel" darbaknygę sukurti atsarginę kopiją.
Daugiau informacijos ieškokite "Access" žinyno temoje Duomenų importavimas arba susiejimas su "Excel" darbaknygės duomenimis.
Automatinis duomenų papildymas paprastu būdu
Dažnai "Excel" vartotojai prideda duomenis su tais pačiais stulpeliais viename dideliame darbalapyje. Pavyzdžiui, gali būti, kad turite turto sekimo sprendimą, kuris buvo pradėtas programoje "Excel", bet dabar tapo įtraukiant failus iš daugelio darbo grupių ir skyrių. Šie duomenys gali būti skirtinguose darbalapiuose ir darbaknygėse arba tekstiniuose failuose, kurie yra duomenų informacijos santraukos iš kitų sistemų. Nėra vartotojo sąsajos komandos ar paprasto būdo pridėti panašių duomenų programoje "Excel".
Geriausias sprendimas yra naudoti "Access", kurioje galite lengvai importuoti ir papildyti duomenis į vieną lentelę naudodami skaičiuoklės importavimo vediklį. Be to, į vieną lentelę galite įtraukti daug duomenų. Galite įrašyti importavimo operacijas, įtraukti jas kaip suplanuotas "Microsoft Outlook" užduotis ir net naudoti makrokomandas, kad automatizuotumėte procesą.
2 veiksmas: duomenų normalizavimas naudojant lentelių analizatoriaus vediklį
Iš pirmo žvilgsnio tai, kaip normalizuoti savo duomenis, gali atrodyti bauginanti užduotis. Laimei, "Access" lentelių normalizavimas yra daug paprastesnis procesas dėl lentelių analizatoriaus vediklio.
1. Vilkite pasirinktus stulpelius į naują lentelę ir automatiškai sukurkite ryšius
2. Naudokite mygtukų komandas norėdami pervardyti lentelę, įtraukti pirminį raktą, padaryti esamą stulpelį pirminiu raktu ir anuliuoti paskutinį veiksmą
Naudodami šį vediklį galite atlikti šiuos veiksmus:
-
Konvertuokite lentelę į mažesnių lentelių rinkinį ir automatiškai sukurkite pirminio ir išorinio rakto ryšį tarp lentelių.
-
Į esamą lauką, kuriame yra unikalių reikšmių, įtraukite pirminį raktą arba sukurkite naują ID lauką, kuriame naudojamas duomenų tipas "AutoNumber".
-
Automatiškai kurkite ryšius, kad įgalintumėte nuorodų vientisumą naudodami pakopinius naujinimus. Pakopiniai naikinimai nėra automatiškai įtraukiami, kad nebūtų galima netyčia panaikinti duomenų, bet vėliau galite lengvai įtraukti pakopinį naikinimą.
-
Naujose lentelėse ieškokite nereikalingų arba besidubliuojančių duomenų (pvz., to paties kliento su dviem skirtingais telefono numeriais) ir atnaujinkite tai, kaip norite.
-
Sukurkite pradinės lentelės atsarginę kopiją ir pervardykite ją prie jos pavadinimo pridėdami "_OLD". Tada galite sukurti užklausą, kuri rekonstruoja pradinę lentelę, su pradiniu lentelės pavadinimu, kad visos esamos formos ar ataskaitos, pagrįstos pradine lentele, veiktų su nauja lentelės struktūra.
Daugiau informacijos žr. Duomenų normalizavimas naudojant lentelių analizatorių.
3 veiksmas: prisijungimas prie "Access" duomenų iš "Excel"
Normalizavus duomenis programoje "Access" ir sukūrus užklausą ar lentelę, kuri atkurs pradinius duomenis, paprasčiausia prisijungti prie "Access" duomenų iš "Excel". Jūsų duomenys dabar yra "Access" kaip išorinis duomenų šaltinis, todėl juos galima prijungti prie darbaknygės naudojant duomenų ryšį, kuris yra informacijos konteineris, naudojamas išoriniams duomenų šaltiniams rasti, prisijungti ir pasiekti. Ryšio informacija saugoma darbaknygėje ir gali būti saugoma ryšio faile, pvz., "Office" duomenų ryšio (ODC) faile (.odc failo vardo plėtinys) arba duomenų šaltinio pavadinimo faile (.dsn plėtinys). Prisijungę prie išorinių duomenų, taip pat galite automatiškai atnaujinti (arba atnaujinti) "Excel" darbaknygę iš "Access", kai duomenys atnaujinami programoje "Access".
Daugiau informacijos žr. Duomenų importavimas iš išorinių duomenų šaltinių ("Power Query").
Duomenų gavimas į "Access"
Šiame skyriuje supažindinama su šiais duomenų normalizavimo etapais: Stulpelių Pardavėjas ir Adresas reikšmių skaidymas į labiausiai atomines dalis, susijusių subjektų atskyrimas į atskiras lenteles, tų lentelių kopijavimas ir įklijavimas iš "Excel" į "Access", pagrindinių ryšių tarp naujai sukurtų "Access" lentelių kūrimas ir paprastos užklausos kūrimas ir vykdymas programoje "Access", kad būtų pateikta informacija.
Ne normalizuotos formos duomenų pavyzdys
Šiame darbalapyje stulpelyje Pardavėjas ir stulpelyje Adresas yra neatominių reikšmių. Abu stulpeliai turi būti išskaidyti į du ar daugiau atskirų stulpelių. Šiame darbalapyje taip pat yra informacijos apie pardavėjus, produktus, klientus ir užsakymus. Ši informacija taip pat turėtų būti padalyta į atskiras lenteles pagal temą.
Pardavėjas |
Užsakymo ID |
Užsakymo data |
Produkto ID |
Kiekis |
Kaina |
Kliento vardas ir pavardė |
Adresas |
Telefonas |
---|---|---|---|---|---|---|---|---|
Li, Jalė |
2349 |
3/4/09 |
C-789 |
3 |
7,00 EUR |
Fourth kava |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Li, Jalė |
2349 |
3/4/09 |
C-795 |
6 |
9,75 JAV dol. |
Fourth kava |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Adomas, Linas |
2350 |
3/4/09 |
A-2275 |
2 |
16,75 JAV dol. |
Nuotykių darbai |
1025 Kolumbijos žiedas Kirkland, WA 98234 |
425-555-0185 |
Adomas, Linas |
2350 |
3/4/09 |
F-198 |
6 |
5,25 JAV dol. |
Nuotykių darbai |
1025 Kolumbijos žiedas Kirkland, WA 98234 |
425-555-0185 |
Adomas, Linas |
2350 |
3/4/09 |
B–205 m. |
1 |
4,50 JAV dol. |
Nuotykių darbai |
1025 Kolumbijos žiedas Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2351 |
3/4/09 |
C-795 |
6 |
9,75 JAV dol. |
UAB Contoso. |
2302 Harvard Ave Bellevue, WA 98227 |
425-555-0222 |
Hance, Jim |
2352 |
3/5/09 |
A-2275 |
2 |
16,75 JAV dol. |
Nuotykių darbai |
1025 Kolumbijos žiedas Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2352 |
3/5/09 |
D-4420 |
3 |
7,25 JAV dol. |
Nuotykių darbai |
1025 Kolumbijos žiedas Kirkland, WA 98234 |
425-555-0185 |
Koch, Reed |
2353 |
3/7/09 |
A-2275 |
6 |
16,75 JAV dol. |
Fourth kava |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Koch, Reed |
2353 |
3/7/09 |
C-789 |
5 |
7,00 EUR |
Fourth kava |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Informacija mažiausiose jos dalyse: atominiai duomenys
Dirbdami su šio pavyzdžio duomenimis, programoje "Excel" galite naudoti komandą Tekstą į stulpelį , kad atskirtumėte "atomines" langelio dalis (pvz., adresą, miestą, apskritį ir pašto kodą) į atskirus stulpelius.
Šioje lentelėje rodomi nauji stulpeliai tame pačiame darbalapyje po to, kai jie buvo suskaidyti, kad visos reikšmės taptų atominėmis. Atkreipkite dėmesį, kad stulpelyje Pardavėjas esanti informacija buvo suskaidyta į stulpelius Pavardė ir Vardas, o informacija stulpelyje Adresas suskaidyta į stulpelius Adresas, Miestas, Valstija ir Pašto indeksas. Šie duomenys yra "pirmosios normalinės formos".
Pavardė |
Vardas |
|
Adresas |
Miestas |
Valstybė |
Pašto indeksas |
---|---|---|---|---|---|---|
Li |
Yale |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
Kenteris |
Ellen |
1025 Kolumbijos ratas |
Kaunas |
WA |
98234 |
|
Petkus |
Jim |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
Koch |
Reed |
7007 Cornell St Redmond |
Redmond |
WA |
98199 |
Duomenų skaidymas į sutvarkytas temas programoje "Excel"
Kelios stebimų duomenų pavyzdžių lentelės rodo tą pačią informaciją iš "Excel" darbalapio, kai ji suskaidyta į lenteles, skirtas pardavėjams, produktams, klientams ir užsakymams. Lentelės dizainas nėra galutinis, bet yra tinkamame kelyje.
Lentelėje Pardavėjas yra tik informacija apie pardavėjus. Atkreipkite dėmesį, kad kiekvienas įrašas turi unikalų ID (pardavėjo ID). Pardavėjo ID reikšmė bus naudojama lentelėje Užsakymai, kad būtų galima sujungti užsakymus su pardavėjais.
Pardavėjai |
||
---|---|---|
Pardavėjo ID |
Pavardė |
Vardas |
101 |
Li |
Yale |
103 |
Kenteris |
Ellen |
105 |
Petkus |
Jim |
107 |
Koch |
Reed |
Lentelėje Produktai yra tik informacija apie produktus. Atkreipkite dėmesį, kad kiekvienas įrašas turi unikalų ID (produkto ID). Produkto ID reikšmė bus naudojama produkto informacijai sujungti su lentele Užsakymo išsami informacija.
Produktai |
|
---|---|
Produkto ID |
Kaina |
A-2275 |
16.75 |
B–205 m. |
4.50 |
C-789 |
7.00 |
C-795 |
9.75 |
D-4420 |
7.25 |
F-198 |
5.25 |
Lentelėje Klientai yra tik informacija apie klientus. Atkreipkite dėmesį, kad kiekvienas įrašas turi unikalų ID (kliento ID). Kliento ID reikšmė bus naudojama kliento informacijai sujungti su lentele Užsakymai.
Customers |
||||||
---|---|---|---|---|---|---|
Kliento ID |
Pavadinimas |
Adresas |
Miestas |
Valstybė |
Pašto indeksas |
Telefonas |
1001 |
UAB Contoso. |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
425-555-0222 |
1003 |
Nuotykių darbai |
1025 Kolumbijos ratas |
Kaunas |
WA |
98234 |
425-555-0185 |
1005 |
Fourth kava |
7007 Cornell St |
Redmond |
WA |
98199 |
425-555-0201 |
Lentelėje Užsakymai yra informacija apie užsakymus, pardavėjus, klientus ir produktus. Atkreipkite dėmesį, kad kiekvienas įrašas turi unikalų ID (užsakymo ID). Tam tikrą šios lentelės informaciją reikia išskaidyti į papildomą lentelę, kurioje yra užsakymo išsami informacija, kad lentelėje Užsakymai būtų tik keturi stulpeliai: unikalus užsakymo ID, užsakymo data, pardavėjo ID ir kliento ID. Čia rodoma lentelė dar nebuvo suskaidyta į lentelę Užsakymų išsami informacija.
Užsakymai |
|||||
---|---|---|---|---|---|
Užsakymo ID |
Užsakymo data |
Pardavėjo ID |
Kliento ID |
Produkto ID |
Kiekis |
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 m. |
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 |
Užsakymo informacija, pvz., produkto ID ir kiekis, perkeliama iš lentelės Užsakymai ir saugoma lentelėje Užsakymo išsami informacija. Turėkite omenyje, kad yra 9 užsakymai, todėl prasminga, kad šioje lentelėje yra 9 įrašai. Atkreipkite dėmesį, kad lentelėje Užsakymai yra unikalus ID (Užsakymo ID), kuris nurodomas lentelėje Užsakymo išsami informacija.
Galutinis lentelės Užsakymai dizainas turėtų atrodyti taip:
Užsakymai |
|||
---|---|---|---|
Užsakymo ID |
Užsakymo data |
Pardavėjo ID |
Kliento 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 |
Lentelėje Užsakymų išsami informacija nėra stulpelių, kuriems reikia unikalių reikšmių (t. y. nėra pirminio rakto), todėl yra gerai, kad bet kuriame arba visuose stulpeliuose būtų perteklinių duomenų. Tačiau jokie du įrašai šioje lentelėje neturėtų būti visiškai identiški (ši taisyklė taikoma bet kokiai duomenų bazės lentelei). Šioje lentelėje turi būti 17 įrašų, kurių kiekvienas atitinka atskirą užsakymą. Pavyzdžiui, 2349 eilės tvarka tris C–789 produktus sudaro viena iš dviejų viso užsakymo dalių.
Todėl lentelė Užsakymų išsami informacija turėtų atrodyti taip:
Išsami užsakymo informacija |
||
---|---|---|
Užsakymo ID |
Produkto ID |
Kiekis |
2349 |
C-789 |
3 |
2349 |
C-795 |
6 |
2350 |
A-2275 |
2 |
2350 |
F-198 |
6 |
2350 |
B–205 m. |
1 |
2351 |
C-795 |
6 |
2352 |
A-2275 |
2 |
2352 |
D-4420 |
3 |
2353 |
A-2275 |
6 |
2353 |
C-789 |
5 |
Duomenų kopijavimas ir įklijavimas iš "Excel" į programą "Access"
Dabar, kai informacija apie pardavėjus, klientus, produktus, užsakymus ir užsakymo informaciją buvo suskirstyta į atskiras temas programoje "Excel", galite nukopijuoti duomenis tiesiai į "Access", kur jie taps lentelėmis.
Ryšių tarp "Access" lentelių kūrimas ir užklausos vykdymas
Perkėlę duomenis į "Access", galite kurti ryšius tarp lentelių ir kurti užklausas, kad būtų pateikta informacija apie įvairias temas. Pavyzdžiui, galite sukurti užklausą, kuri pateikia užsakymo ID ir užsakymų, įvestų nuo 2009-03-05 iki 2009-03-08, pardavėjų vardus.
Be to, galite kurti formas ir ataskaitas, kad būtų lengviau įvesti ir analizuoti duomenis.
Reikia daugiau pagalbos?
Visada galite paklausti „Excel“ technologijų bendruomenės specialisto arba gauti palaikymo bendruomenėse.