Piezīme.: Microsoft Access neatbalsta Excel datu importēšanu ar lietoto jūtīguma etiķeti. Lai novērstu šo problēmu, varat noņemt etiķeti pirms importēšanas un pēc importēšanas atkārtoti lietot šo etiķeti. Papildinformāciju skatiet rakstā Sensitivitātes etiķešu lietošana failiem un e-pastam sistēmā Office.
Šajā rakstā ir paskaidrots, kā pārvietot datus no programmas Excel uz programmu Access un pārvērst datus par relāciju tabulām, lai varētu vienlaikus lietot programmu Microsoft Excel un Access. Summējot Access ir vislabākā datu tveršanai, glabāšanai, vaicājumiem un koplietošanai, un Excel ir vislabākā datu aprēķināšanai, analīzei un vizualizēšanai.
Divi raksti: programmas Access vai Excel izmantošana datu pārvaldīšanai un 10 galvenie iemesli, kāpēc izmantot programmu Access programmā Excel, apspriediet, kura programma ir vislabāk piemērota konkrētam uzdevumam un kā izmantot programmu Excel un Access kopā, lai izveidotu praktisku risinājumu.
Pārvietojot datus no Programmas Excel uz programmu Access, procesam ir jāveic trīs pamatdarbības.
Piezīme.: Papildinformāciju par datu modelēšanu un relācijām programmā Access skatiet rakstā Datu bāzes noformēšanas pamatprincipi.
1. darbība. Datu importēšana no programmas Excel programmā Access
Datu importēšana ir darbība, kas norit daudz vieglāk, ja nepieciešams laiks, lai sagatavotu un notīrītu datus. Datu importēšana ir kā pāriešana uz jaunu māju. Ja pirms pārvietošanas iztīrāt un sakārtosiet sāciet, nostāst jaunajās mājās ir daudz vieglāk.
Datu tīrīšana pirms importēšanas
Pirms importējat datus programmā Access, programmā Excel ieteicams:
-
Šūnas, kurās ir dati, kas nav atomu dati (tas ir, vairākas vērtības vienā šūnā), konvertējiet vairākās kolonnās. Piemēram, šūna kolonnā "Prasmes", kurā ir vairākas prasmju vērtības, piemēram, "C# programmēšana", "VBA programmēšana" un "Tīmekļa noformējums", ir jāsadala atsevišķās kolonnās, kurās ir tikai viena prasmju vērtība.
-
Izmantojiet komandu TRIM, lai noņemtu sākuma, beigu un vairākas iegultās atstarpes.
-
Noņemiet nedrukātās rakstzīmes.
-
Pareizrakstības un pieturzīmju kļūdu atrašana un labošana.
-
Noņemiet rindu dublikātus vai lauku dublikātus.
-
Nodrošiniet, lai datu kolonnās nebūtu jauktu formātu, īpaši skaitļi, kas formatēti kā teksts vai datumi, kas formatēti kā skaitļi.
Papildinformāciju skatiet šajās Excel palīdzības tēmās:
Piezīme.: Ja datu tīrīšana ir nepieciešama sarežģīti vai ja jums nav laika vai resursu, lai automatizētu šo procesu pats, apsveriet iespēju izmantot trešās puses piegādātāju. Lai iegūtu papildinformāciju, tīmekļa pārlūkprogrammā meklējiet "datu tīrīšanas programmatūra" vai "datu kvalitāte".
Labākā datu tipa izvēle importēšanas laikā
Importēšanas operācijas laikā programmā Access jūs vēlaties izdarīt labu izvēli, lai saņemtu dažas (ja rodas) konvertēšanas kļūdas, kam nepieciešama manuāla iejaukšanās. Šajā tabulā apkopots, kā tiek konvertēti Excel skaitļu formāti un Access datu tipi, importējot datus no programmas Excel programmā Access, kā arī sniegti daži padomi par vislabākajiem datu tipiem, ko izvēlēties izklājlapu importēšanas vednī.
|
Excel skaitļu formāts |
Access datu tips |
Komentāri |
Paraugprakse |
|---|---|---|---|
|
Teksts |
Text, Memo |
Datu tips Access teksts saglabā burtciparu datus, līdz 255 rakstzīmēm. Datu tips Access Memo saglabā burtciparu datus līdz 65 535 rakstzīmēm. |
Izvēlieties Memo, lai dati netiktu apcirsti. |
|
Skaitlis, Procenti, Daļskaitlis, Zinātnisks |
Skaitlis |
Programmā Access ir viens datu tips Skaitlis, kas ir atkarīgs no rekvizīta Lauka lielums (Byte, Integer, Long Integer, Single, Double, Decimal). |
Izvēlieties Double, lai izvairītos no datu konvertēšanas kļūdām. |
|
Datums |
Datums |
Gan access, gan Excel izmanto tos pašus sērijas datumu numurus, lai glabātu datumus. Programmā Access datumu diapazons ir lielāks: no -657 434 (100. gada 1. janvāris) līdz 2 958 465 (9999. gada 31. decembris). Tā kā programma Access neatpazīst datumu sistēmu 1904 (programmā Excel Macintosh datoriem), datumi ir jākonvertē programmā Excel vai Access, lai izvairītos no pārpratumiem. Papildinformāciju skatiet rakstā Datumu sistēmas, formāta vai divciparu gada interpretācijas maiņa un Datu importēšana vai saistīšana ar datiem Excel darbgrāmatā. |
Izvēlieties Datums. |
|
Laiks |
Laiks |
Access un Excel saglabā laika vērtības, izmantojot vienu datu tipu. |
Izvēlieties laiks, kas parasti ir noklusējuma iestatījums. |
|
Currency, Accounting |
Valūta |
Programmā Access datu tips Valūta datus glabā kā 8 baitu skaitļus precīzi līdz četrām decimāldaļas vietām, un tas tiek izmantots, lai glabātu finanšu datus un novērstu vērtību noapaļošanu. |
Izvēlieties Valūta, kas parasti ir noklusējuma iestatījums. |
|
Būla izteiksme |
Jā/nē |
Programma Access visām vērtībām Jā izmanto -1, bet visām vērtībām Nē — 0, savukārt programma Excel visām vērtībām TRUE izmanto 1, bet visām vērtībām FALSE — 0. |
Izvēlieties Jā/nē, kas automātiski konvertē pamatā esošās vērtības. |
|
Hipersaite |
Hipersaite |
Hipersaite programmā Excel un Programmā Access ietver vietrādi URL vai tīmekļa adresi, uz kuras varat noklikšķināt un kam sekot. |
Izvēlieties Hipersaite, pretējā gadījumā programma Access pēc noklusējuma var izmantot datu tipu Teksts. |
Kad dati ir programmā Access, varat izdzēst Excel datus. Neaizmirstiet vispirms dublēt sākotnējo Excel darbgrāmatu, pirms to izdzēst.
Papildinformāciju skatiet Access palīdzības tēmā Datu importēšana vai saistīšana ar tiem Excel darbgrāmatā.
Automātiska datu pievienošana vienkāršā veidā
Bieži sastopama problēma, kuras dēļ Excel lietotāji vienā lielā darblapā pievieno datus ar vienām un tajā pašām kolonnām. Piemēram, jums var būt līdzekļu izsekošanas risinājums, kas sācies programmā Excel, bet tagad ir pieaugis, lai iekļautu failus no daudzām darbgrupām un nodaļām. Šie dati var būt dažādās darblapās un darbgrāmatās vai teksta failos, kas ir datu plūsmas no citām sistēmām. Līdzīgus datus programmā Excel nevar pievienot, izmantojot lietotāja interfeisa komandu vai vienkāršu veidu.
Vislabākais risinājums ir izmantot programmu Access, kur var viegli importēt un pievienot datus vienā tabulā, izmantojot izklājlapu importēšanas vedni. Turklāt vienā tabulā var pievienot daudz datu. Varat saglabāt importēšanas darbības, pievienot tās kā ieplānotus Microsoft Outlook uzdevumus un pat izmantot makro, lai automatizētu procesu.
2. darbība. Datu normalizēšana, izmantojot tabulu analīzes vedni
Pirmajā acu uzmetienā datu normalizēšanas process var šķist biedējošs uzdevums. Tomēr tabulu normalizēšana programmā Access ir process, kas ir daudz vienkāršāks, izmantojot tabulu analīzes vedni.
1. Velciet atlasītās kolonnas uz jaunu tabulu un automātiski izveidojiet relācijas
2. Izmantojiet pogu komandas, lai pārdēvētu tabulu, pievienotu primāro atslēgu, esošu kolonnu padarītu par primāro atslēgu un atsauktu pēdējo darbību
Varat izmantot šo vedni, lai rīkojieties šādi:
-
Pārvērtiet tabulu par mazāku tabulu kopu un automātiski izveidojiet tabulu primāro un ārējo atslēgu relāciju.
-
Pievienojiet primāro atslēgu esošam laukam, kurā ir unikālas vērtības, vai izveidojiet jaunu ID lauku, kurā tiek izmantots datu tips AutoNumber.
-
Automātiski izveidojiet relācijas, lai ieviestu attiecinošo integritāti ar kaskadēto atjaunināšanu. Kaskadēto dzēšanu nevar automātiski pievienot, lai novērstu nejaušu datu dzēšanu, bet vēlāk var vienkārši pievienot kaskadēto dzēšanu.
-
Meklējiet jaunās tabulās liekos vai dublētos datus (piemēram, tos pašus klientus ar diviem dažādiem tālruņu numuriem) un atjauniniet to pēc vajadzības.
-
Dublējiet sākotnējo tabulu un pārdēvējiet to, pievienojot "_OLD" tās nosaukumam. Pēc tam tiek izveidots vaicājums, kas rekonstruē sākotnējo tabulu ar sākotnējo tabulas nosaukumu, lai visas esošās formas vai atskaites, kuru pamatā ir sākotnējā tabula, darbotos ar jauno tabulas struktūru.
Papildinformāciju skatiet rakstā Datu normalizācija, izmantojot tabulu analizētāju.
3. darbība. Savienojuma izveide ar Access datiem no programmas Excel
Kad dati ir normalizēti programmā Access un ir izveidots vaicājums vai tabula, kas rekonstruē sākotnējos datus, ir vienkārši izveidot savienojumu ar Access datiem no programmas Excel. Jūsu dati tagad ir programmā Access kā ārējs datu avots, tāpēc tos var savienot ar darbgrāmatu, izmantojot datu savienojumu, kas ir informācijas konteiners, kas tiek izmantots, lai atrastu, pieteiktos un piekļūtu ārējam datu avotam. Savienojuma informācija tiek glabāta darbgrāmatā, un to var glabāt arī savienojuma failā, piemēram, Office datu savienojuma (ODC) failā (.odc faila nosaukuma paplašinājums) vai datu avota nosaukuma failā (.dsn paplašinājums). Kad ir izveidots savienojums ar ārējiem datiem, excel darbgrāmatu var automātiski atsvaidzināt (vai atjaunināt) no programmas Access ikreiz, kad programmā Access tiek atjaunināti dati.
Papildinformāciju skatiet rakstā Datu importēšana no ārējiem datu avotiem (Power Query).
Datu ieešana programmā Access
Šajā sadaļā ir norādīta šāda datu normalizēšanas fāze: vērtību sadalīšana pārdevēja un adreses kolonnās to atomu gabalos, saistīto jautājumu atdalīšana savās tabulās, šo tabulu kopēšana un ielīmēšana no programmas Excel programmā Access, galveno relāciju izveide starp jaunizveidotajām Access tabulām un vienkārša vaicājuma izveide un izpilde programmā Access, lai atgrieztu informāciju.
Example data in non-normalized form
Šajā darblapā kolonnā Pārdevējs un kolonnā Adrese ir vērtības, kas nav atomu vērtības. Abas kolonnas ir jāsadala divās vai vairākās atsevišķās kolonnās. Šajā darblapā ir arī informācija par pārdevējiem, produktiem, klientiem un pasūtījumiem. Šī informācija pēc tēmas ir arī jāsadala atsevišķās tabulās.
|
Pārdevējs |
Order ID |
Pasūtījuma datums |
Produkta ID |
Daudzums |
Cena |
Klienta nosaukums |
Adrese |
Tālrunis |
|---|---|---|---|---|---|---|---|---|
|
Li, Yale |
2349 |
3/4/09 |
C-789 |
3 |
7,00 $ |
Kafejnīca “Viktorija” |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
|
Li, Yale |
2349 |
3/4/09 |
C-795 |
6 |
9,75 $ |
Kafejnīca “Viktorija” |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
|
Andis, Laiens |
2350 |
3/4/09 |
A-2275 |
2 |
16,75 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Andis, Laiens |
2350 |
3/4/09 |
F-198 |
6 |
5,25 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Andis, Laiens |
2350 |
3/4/09 |
B-205 |
1 |
4,50 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Hance, Armi |
2351 |
3/4/09 |
C-795 |
6 |
9,75 $ |
Contoso, Ltd. |
2302 Harvard Ave Bellevue, WA 98227 |
425-555-0222 |
|
Hance, Armi |
2352 |
3/5/09 |
A-2275 |
2 |
16,75 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Hance, Armi |
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 $ |
Kafejnīca “Viktorija” |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
|
Koch, Reed |
2353 |
3/7/09 |
C-789 |
5 |
7,00 $ |
Kafejnīca “Viktorija” |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Informācija to mazākajos daļās: atomu dati
Strādājot ar datiem šajā piemērā, varat izmantot programmas Excel komandu Teksts par kolonnu, lai sadalītu šūnas "atom" daļas (piemēram, adresi, pilsētu, valsti un pasta indeksu) diskrētās kolonnās.
Tālāk esošajā tabulā ir parādītas jaunās kolonnas tajā pašā darblapā pēc to sadalīšanas, lai padarītu visas vērtības atomu. Ņemiet vērā, ka informācija kolonnā Pārdevējs ir sadalīta kolonnā Uzvārds un Vārds un kolonnā Adrese ievadītā informācija ir sadalīta kolonnā Adrese, Pilsēta, Štats un Pasta indekss. Šie dati ir "pirmajā normālajā formā".
|
Uzvārds |
Vārds |
|
Adrese |
Pilsēta |
Štats |
Pasta indekss |
|---|---|---|---|---|---|---|
|
Li |
Yale |
2302 Harvard Ave |
Liepāja |
WA |
98227 |
|
|
Mieriņa |
Ellen |
1025 Kolumbijas aplis |
Olaine |
WA |
98234 |
|
|
Baltiņš |
Jānis |
2302 Harvard Ave |
Liepāja |
WA |
98227 |
|
|
Koch |
Rēts |
7007 Cornell St Redmond |
Rīga |
WA |
98199 |
Datu sadalīšana sakārtotās jomās programmā Excel
Vairākas tālāk norādītās datu paraugu tabulas parāda to pašu informāciju no Excel darblapas pēc tam, kad tā ir sadalīta tabulās pārdevējiem, produktiem, klientiem un pasūtījumiem. Tabulas noformējums nav galīgais variants, bet tas ir uz pareizā ieraksta.
Tabulā Pārdevēji ir tikai informācija par pārdevējiem. Ievērojiet, ka katram ierakstam ir unikāls ID (Pārdevēja ID). Tabulā Pasūtījumi tiks izmantota pārdevēja ID vērtība, lai savienotu pasūtījumus pārdevējiem.
|
Pārdevēji |
||
|---|---|---|
|
Pārdevēja ID |
Uzvārds |
Vārds |
|
101 |
Li |
Yale |
|
103 |
Mieriņa |
Ellen |
|
105 |
Baltiņš |
Jānis |
|
107 |
Koch |
Rēts |
Tabulā Produkti ir tikai informācija par produktiem. Ievērojiet, ka katram ierakstam ir unikāls ID (Produkta ID). Produkta ID vērtība tiks izmantota, lai savienotu produkta informāciju ar tabulu Pasūtījuma dati.
|
Produkti |
|
|---|---|
|
Produkta ID |
Cena |
|
A-2275 |
16.75 |
|
B-205 |
4.50 |
|
C-789 |
7.00 |
|
C-795 |
9.75 |
|
D-4420 |
7.25 |
|
F-198 |
5.25 |
Tabulā Klienti ir tikai informācija par klientiem. Ievērojiet, ka katram ierakstam ir unikāls ID (Klienta ID). Vērtība Klienta ID tiek izmantota, lai savienotu klienta informāciju ar tabulu Pasūtījumi.
|
Customers |
||||||
|---|---|---|---|---|---|---|
|
Klienta ID |
Vārds |
Adrese |
Pilsēta |
Štats |
Pasta indekss |
Tālrunis |
|
1001 |
Contoso, Ltd. |
2302 Harvard Ave |
Liepāja |
WA |
98227 |
425-555-0222 |
|
1003 |
Adventure Works |
1025 Kolumbijas aplis |
Olaine |
WA |
98234 |
425-555-0185 |
|
1005 |
Kafejnīca “Viktorija” |
7007 Cornell St |
Rīga |
WA |
98199 |
425-555-0201 |
Tabulā Pasūtījumi ir informācija par pasūtījumiem, pārdevējiem, klientiem un produktiem. Ievērojiet, ka katram ierakstam ir unikāls ID (Pasūtījuma ID). Daļa šīs tabulas informācijas ir jāsadala papildu tabulā, kurā ir pasūtījumu dati, lai tabulā Pasūtījumi būtu tikai četras kolonnas — unikāls pasūtījuma ID, pasūtījuma datums, pārdevēja ID un klienta ID. Šeit parādītā tabula vēl nav sadalīta tabulā Pasūtījuma dati.
|
Pasūtījumi |
|||||
|---|---|---|---|---|---|
|
Order ID |
Pasūtījuma datums |
Pārdevēja ID |
Klienta ID |
Produkta ID |
Daudzums |
|
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 |
Pasūtījuma dati, piemēram, produkta ID un daudzums, tiek pārvietoti no tabulas Pasūtījumi un saglabāti tabulā ar nosaukumu Pasūtījuma dati. Ņemiet vērā, ka šeit ir 9 pasūtījumi, tāpēc ir prātīgi, ka šajā tabulā ir 9 ieraksti. Ņemiet vērā, ka tabulai Pasūtījumi ir unikāls ID (Pasūtījuma ID), uz kuru ir jābūt norādei tabulā Detalizēta informācija par pasūtījumu.
Tabulas Pasūtījumi beigu noformējumam jāizskatās šādi:
|
Pasūtījumi |
|||
|---|---|---|---|
|
Order ID |
Pasūtījuma datums |
Pārdevēja ID |
Klienta 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 |
Tabulā Pasūtījuma dati nav kolonnu, kurās ir nepieciešamas unikālas vērtības (t.i., nav primārās atslēgas), tāpēc ikviens vai visas kolonnas var ietvert "liekus" datus. Tomēr diviem šīs tabulas ierakstiem jābūt pilnībā identiskiem (šī kārtula attiecas uz jebkuru tabulas datu bāzē). Šajā tabulā jābūt 17 ierakstiem — katram produktam atbilstošā informācija atsevišķā secībā. Piemēram, 2349. secībā trīs C–789 produkti ir viena no visām pasūtījuma divām daļām.
Tāpēc tabulai Pasūtījuma dati jāizskatās šādi:
|
Detalizēta informācija par pasūtījumu |
||
|---|---|---|
|
Order ID |
Produkta ID |
Daudzums |
|
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 |
Datu kopēšana un ielīmēšana no Programmas Excel programmā Access
Tagad, kad informācija par pārdevējiem, klientiem, produktiem, pasūtījumiem un pasūtījumiem ir sadalīta atsevišķās jomās programmā Excel, šos datus varat kopēt tieši programmā Access, kur tie kļūs par tabulām.
Relāciju izveide starp Access tabulām un vaicājuma pa izpilde
Pēc datu pārvietošanas uz Access varat izveidot relācijas starp tabulām un pēc tam izveidot vaicājumus, lai atgrieztu informāciju par dažādām tēmām. Piemēram, varat izveidot vaicājumu, kas atgriež pasūtījuma ID un pārdevēju vārdus pasūtījumiem, kas ievadīti no 05.03.09. līdz 08.09.
Turklāt varat izveidot formas un atskaites, lai atvieglotu datu ievadi un pārdošanas analīzi.
Vai nepieciešama papildu palīdzība?
Vienmēr varat pajautāt speciālistam Excel tech kopienā vai saņemt atbalstu kopienās.
