Duomenų perkėlimas iš "Excel" į "Access"

Šiame straipsnyje paaiškinta, kaip perkelti duomenis iš "Excel" į "Access" ir konvertuoti duomenis į sąryšines lenteles, kad galėtumėte kartu naudoti "Microsoft Excel" ir "Access". Apibendrinant galima pasakyti, kad "Access" geriausiai tinka duomenims fiksuoti, saugoti, užklausti ir bendrinti, o "Excel" – duomenims skaičiuoti, analizuoti ir vizualizuoti.

Dviejuose straipsniuose " Access" arba "Excel" naudojimas duomenims valdyti ir 10 pagrindinių priežasčių, kodėl verta naudoti "Access" su "Excel", aptariama, kuri programa geriausiai tinka konkrečiai užduočiai atlikti ir kaip kartu naudoti "Excel" ir "Access", kad būtų sukurtas praktiškas sprendimas.

Perkeliant duomenis iš "Excel" į "Access", procesą sudaro trys pagrindiniai veiksmai.

1 veiksmas: Duomenų importavimas iš "Excel" į "Access"

Duomenų importavimas yra operacija, kuri gali vykti daug sklandžiau, jei skirsite šiek tiek laiko duomenims paruošti ir išvalyti. Duomenų importavimas yra tarsi persikėlimas į naujus namus. Jei prieš persikeldami išvalysite ir sutvarkysite savo turtą, įsikurti naujuose namuose bus daug lengviau.

Išvalykite duomenis prieš importuodami

Prieš importuojant duomenis į "Access" programoje "Excel" rekomenduojama:

• Konvertuokite langelius, kuriuose yra neatominių duomenų (t. y. kelios reikšmės viename langelyje), į kelis stulpelius. Pvz., stulpelio "Įgūdžiai" langelį, kuriame yra kelios įgūdžių reikšmės, pvz., "C# programavimas", "VBA programavimas" ir "Žiniatinklio dizainas", reikėtų išskaidyti į atskirus stulpelius, kuriuose yra tik viena įgūdžių reikšmė.
• Naudokite komandą TRIM norėdami pašalinti pradžioje, pabaigoje esančius ir kelis įdėtuosius tarpus.
• Šalinti nespausdinamus simbolius.
• Rasti ir ištaisyti rašybos ir skyrybos klaidas.
• Pašalinkite pasikartojančias eilutes arba pasikartojančius laukus.
• Įsitikinkite, kad duomenų stulpeliuose nėra mišrių formatų, ypač skaičių, suformatuotų kaip tekstas, arba datų, suformatuotų kaip skaičiai.

Daugiau informacijos žr. šiose "Excel" žinyno temose:

• Dešimt geriausių būdų išvalyti duomenis
• Unikalių reikšmių filtravimas arba pasikartojančių reikšmių šalinimas
• Teksto pavidalo skaičių konvertavimas į skaitinį formatą
• Datų, saugomų kaip tekstas, konvertavimas į datas

Importuojant pasirinkite tinkamiausią duomenų tipą

Atlikdami importavimo operaciją programoje "Access" norite tinkamai pasirinkti, kad gautumėte kelias konvertavimo klaidas (jei tokių yra) ir joms reikėtų neautomatinio įsikišimo. Šioje lentelėje apibendrinama, kaip konvertuojami "Excel" skaičių formatai ir "Access" duomenų tipai, kai importuojate duomenis iš "Excel" į "Access", ir pateikiama keletas patarimų, kokius duomenų tipus geriausia pasirinkti naudojant skaičiuoklės importavimo vedlį.

"Excel" skaičių formatas	„Access“ duomenų tipas	Komentarai	Geriausia praktika
Tekstas	Tekstas, atmintinė	"Access" teksto duomenų tipas saugo iki 255 simbolių raidinius skaitinius duomenis. "Access Memo" duomenų tipas saugo iki 65 535 simbolių raidinius skaitinius duomenis.	Norėdami išvengti duomenų trumpinimo, pasirinkite Atmintinę .
Skaičius, procentai, trupmena, mokslinis	Skaičius	"Access" turi vieną skaičiaus duomenų tipą, kuris skiriasi priklausomai nuo lauko dydžio ypatybės (baitas, sveikasis skaičius, ilgasis sveikasis skaičius, viengubas, dvigubas, dešimtainis).	Pasirinkite Dvigubai, kad išvengtumėte duomenų konvertavimo klaidų.
Data	Data	"Access" ir "Excel" datoms saugoti naudoja tą patį datos sekos numerį. Programoje "Access" datų diapazonas yra didesnis: nuo -657.434 (100 m. sausio 1 d.) iki 2.958.465 (9999 m. gruodžio 31 d.). Kadangi programa Access neatpažįsta datos sistemos 1904 (kuri buvo naudojama programoje "Excel", skirtoje "Macintosh"), norint išvengti painiavos, datas reikia konvertuoti programoje "Excel" arba programoje "Access". Daugiau informacijos rasite Datų sistemos, formato arba dviejų skaitmenų metų aiškinimo keitimas ir Duomenų importavimas į "Excel" darbaknygę arba susiejimas su jais.	Pasirinkite datą.
Laikas	Laikas	Ir "Access", ir "Excel" saugo laiko reikšmes, naudodamos tą patį duomenų tipą.	Pasirinkite laiką, kuris paprastai yra numatytasis.
Valiuta, Apskaita	Valiuta	Programoje "Access" valiutos duomenų tipas saugo duomenis kaip 8 baitų skaičius, tiksliai nurodančius keturis skaitmenis po kablelio, ir yra naudojamas finansiniams duomenims saugoti ir verčių apvalinimui išvengti.	Pasirinkite valiutą, kuri paprastai yra numatytoji valiuta.
bulio logika	Taip / Ne	"Access" naudoja -1 visoms reikšmėms Taip ir 0 visoms reikšmėms Ne, o "Excel" naudoja 1 visoms reikšmėms TRUE ir 0 visoms reikšmėms FALSE.	Pasirinkite Taip / Ne, jei bus automatiškai konvertuotos esamos reikšmės.
Hipersaitas	Hipersaitas	"Excel" ir "Access" hipersaitas turi URL arba žiniatinklio adresą, kurį galite spustelėti ir sekti.	Pasirinkite Hipersaitas, nes priešingu atveju "Access" gali naudoti teksto duomenų tipą pagal numatytuosius parametrus.

Kai duomenys bus programoje "Access", juos galėsite panaikinti. Nepamirškite sukurti atsarginės pradinės "Excel" darbaknygės prieš ją panaikindami.

Daugiau informacijos ieškokite "Access" žinyno temoje Duomenų importavimas iš "Excel" darbaknygės arba susiejimas su jais.

Automatinis duomenų papildymas paprastu būdu

Dažna "Excel" vartotojų problema – duomenų pridėjimas tais pačiais stulpeliais į vieną didelį darbalapį. Pavyzdžiui, galite turėti turto sekimo sprendimą, kuris prasidėjo "Excel", bet dabar išplėstas iki daugelio darbo grupių ir skyrių failų. Š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šius duomenis programoje "Excel".

Geriausias sprendimas yra naudoti "Access", kur galite lengvai importuoti ir įtraukti duomenis į vieną lentelę naudodami skaičiuoklės importavimo vedlį. 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 procesui automatizuoti.

2 veiksmas: duomenų normalizavimas naudojant lentelių analizatoriaus vedlį

Iš pirmo žvilgsnio atlikti duomenų normalizavimo procesą gali atrodyti nelengva užduotis. Laimei, lentelių normalizavimas programoje "Access" yra procesas, kuris daug lengvesnis dėl lentelių analizatoriaus vediklio.

1. Nuvilkite pažymėtus stulpelius į naują lentelę ir automatiškai kurkite ryšius

2. Naudokite mygtukų komandas norėdami pervardyti lentelę, įtraukti pirminį raktą, esamą stulpelį padaryti pirminiu raktu ir anuliuoti paskutinį veiksmą

Galite naudoti šį vediklį norėdami atlikti šiuos veiksmus:

• Konvertuokite lentelę į mažesnių lentelių rinkinį ir automatiškai sukurkite lentelėse pirminio ir išorinio rakto ryšius.
• Įtraukite pirminį raktą į esamą lauką, kuriame yra unikalių reikšmių, arba sukurkite naują ID lauką, kuriame naudojamas automatinio numeravimo duomenų tipas.
• Automatiškai kurkite ryšius, kad įgalintumėte nuorodų vientisumą naudodami pakopinius naujinimus. Pakopinis naikinimas nėra automatiškai įtraukiamas, kad netyčia nebūtų panaikinti duomenys, tačiau pakopinius naikinimus galite lengvai įtraukti vėliau.
• Ieškokite naujose lentelėse pasikartojančių arba pasikartojančių duomenų (pvz., to paties kliento su dviem skirtingais telefono numeriais) ir atnaujinkite juos pagal pageidavimą.
• Sukurkite atsarginę pradinės lentelės kopiją ir pervardykite ją pridėdami "_OLD" prie pavadinimo. Tada sukuriate užklausą, kuri atkuria 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 rasite Duomenų normalizavimas naudojant lentelių analizatorių.

3 veiksmas: prisijungimas prie "Access" duomenų iš "Excel"

Normalizavus duomenis programoje "Access" ir sukūrus užklausą arba lentelę, kuri atkuria pradinius duomenis, tereikia 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 talpykla, naudojama išoriniam duomenų šaltiniui 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 vardo 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 rasite Duomenų importavimas iš išorinių duomenų šaltinių ("„Power Query“").

Duomenų gavimas į "Access"

Šiame skyriuje apžvelgiami šie duomenų normalizavimo etapai: Stulpelių Pardavėjas ir Adresas reikšmių skaidymas į pačias atomines dalis, susijusių subjektų skaidymas į jų lenteles, tų lentelių kopijavimas ir įklijavimas iš "Excel" į "Access", pagrindinių ryšių kūrimas tarp naujai sukurtų "Access" lentelių ir paprastos užklausos kūrimas ir vykdymas programoje "Access" informacijai pateikti.

Nenormalizuotos formos duomenų pavyzdžiai

Tolesnio darbalapio stulpeliuose Pardavėjas ir Adresas yra neatominės reikšmės. Abu stulpeliai turi būti suskaidyti į du ar daugiau atskirų stulpelių. Šiame darbalapyje taip pat yra informacijos apie pardavėjus, produktus, pirkėjus ir užsakymus. Ši informacija taip pat turėtų būti toliau suskirstyta pagal temas į atskiras lenteles.

Pardavėjas	Užsakymo ID	Užsakymo data	Produkto ID	Kiekis	Kaina	Kliento vardas ir pavardė	Adresas	Telefonas
Li, Jeilis	2349	3/4/09	C-789	3	$7.00	Fourth kava	7007 Cornell St Redmond, WA 98199	425-555-0201
Li, Jeilis	2349	3/4/09	C-795	6	9,75 USD	Fourth kava	7007 Cornell St Redmond, WA 98199	425-555-0201
Adamsas, Ellen	2350	3/4/09	A-2275	2	16,75 JAV dolerio	Nuotykių darbai	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adamsas, Ellen	2350	3/4/09	F-198	6	$ 5.25	Nuotykių darbai	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adamsas, Ellen	2350	3/4/09	B-205	1	4,50 JAV dolerio	Nuotykių darbai	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2351	3/4/09	C-795	6	9,75 USD	UAB Contoso.	2302 Harvard Ave Bellevue, WA 98227	425-555-0222
Hance, Jim	2352	3/5/09	A-2275	2	16,75 JAV dolerio	Nuotykių darbai	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2352	3/5/09	D-4420	3	7,25 JAV dolerių	Nuotykių darbai	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Kochas, Nendrė	2353	3/7/09	A-2275	6	16,75 JAV dolerio	Fourth kava	7007 Cornell St Redmond, WA 98199	425-555-0201
Kochas, Nendrė	2353	3/7/09	C-789	5	$7.00	Fourth kava	7007 Cornell St Redmond, WA 98199	425-555-0201

Informacija mažiausiomis dalimis: atominiai duomenys

Dirbdami su šiame pavyzdyje pateiktais duomenimis, galite naudoti "Excel" komandą Tekstą į stulpelį , kad atskirtumėte "atomines" langelio dalis (pvz., adresą, miestą, apskritį ir pašto indeksą) į atskirus stulpelius.

Toliau pateikiamoje lentelėje rodomi nauji stulpeliai tame pačiame darbalapyje, kai jie išskaidomi, kad visos reikšmės būtų atominės. Atkreipkite dėmesį, kad stulpelio Pardavėjas informacija išskaidyta į stulpelius Pavardė ir Vardas, o stulpelio Adresas informacija išskaidyta į stulpelius Adresas, Miestas, Apskritis ir Pašto indeksas. Šie duomenys yra "pirmos normalios formos".

Pavardė	Vardas	Gatvės adresas	Miestas	Valstija	Pašto indeksas
Li	Jeilis	2302 Harvard Ave	Bellevue	WA	98227
Kenteris	Elena	1025 Kolumbijos ratas	Kaunas	WA	98234
Petkus	Jim	2302 Harvard Ave	Bellevue	WA	98227
Kochas	Nendrė	7007 Cornell St Redmond	Redmond	WA	98199

Duomenų skaidymas į sutvarkytus dalykus programoje "Excel"

Keliose toliau pateiktose lentelėse su pavyzdiniais duomenimis rodoma ta pati informacija iš "Excel" darbalapio, išskaidyta į pardavėjų, produktų, klientų ir užsakymų lenteles. Lentelės dizainas nėra galutinis, bet jis eina teisingu keliu.

Lentelėje Pardavėjai 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 norint sujungti užsakymus su pardavėjais.

Pardavėjai
Pardavėjo ID	Pavardė	Vardas
101	Li	Jeilis
103	Kenteris	Elena
105	Petkus	Jim
107	Kochas	Nendrė

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 susieti su lentele Užsakymo informacija.

Produktai
Produkto ID	Kaina
A-2275	16.75
B-205	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 susieti su lentele Užsakymai.

Customers
Kliento ID	Vardas	Gatvės adresas	Miestas	Valstija	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ą informaciją šioje lentelėje reikia išskaidyti į papildomą lentelę, kurioje yra užsakymo 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 nėra išskaidyta į lentelę Užsakymų 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	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, pavadintoje Užsakymo informacija. Turėkite omenyje, kad yra 9 užsakymai, todėl logiška, kad šioje lentelėje yra 9 įrašai. Atkreipkite dėmesį, kad lentelė Užsakymai turi unikalų ID (Užsakymo ID), kuris nurodytas 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 būtų reikalingos unikalios reikšmės (t. y. nėra pirminio rakto), todėl gali būti, kad kuriame nors arba visuose stulpeliuose bus "perteklinių" duomenų. Tačiau šioje lentelėje neturi būti du visiškai identiški įrašai (ši taisyklė taikoma bet kuriai duomenų bazės lentelei). Šioje lentelėje turėtų būti 17 įrašų, kurių kiekvienas atitinka atskirą produktą pagal atskirą užsakymą. Pavyzdžiui, užsakyme 2349 trys C-789 produktai sudaro vieną 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	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" į "Access"

Dabar, kai informacija apie pardavėjus, klientus, produktus, užsakymus ir užsakymo informacija programoje "Excel" suskirstyta į atskiras temas, galite nukopijuoti šiuos 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žsakymų, įvestų nuo 209-03-05 iki 209-03-08, užsakymo ID ir pardavėjų vardus.

Be to, norėdami palengvinti duomenų įvedimą ir pardavimo analizę, galite kurti formas ir ataskaitas.

Reikia daugiau pagalbos?

Visada galite kreiptis eksperto į "Excel" technologijų bendruomenę arba gauti pagalbos bendruomenėse.