Datu pārvietošana no Excel uz Access

Šajā rakstā aprakstīts, kā pārvietot datus no Excel uz Access un pārvērst datus par relāciju tabulām, lai varētu izmantot Microsoft Excel un Access. Summējot Access ir vislabākā datu tveršanai, glabāšanai, vaicājumiem un koplietošanai, Excel vislabāk ir aprēķināt, analizēt un vizualizēt datus.

Divi raksti: Programmas Access vai Excel izmantošana datu pārvaldīšanai un 10galvenie iemesli, kāpēc izmantot programmu Access kopā ar Excel , apspriediet, kura programma ir vislabāk piemērota noteiktam uzdevumam un kā izmantot programmu Excel un programmu Access kopā, lai izveidotu praktisku risinājumu.

Pārvietojot datus no programmas Excel uz Access, procesam ir jāveic trīs pamata darbības.

three basic steps

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 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 iztīrāt un pirms pārvietošanas sakārtosiet savus sāņus, daudz vieglāk ir ieiet jaunajās mājās.

Datu tīrīšana pirms importēšanas

Pirms importējat datus programmā Access, 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 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ā ir apkopots Excel kā tiek konvertēti skaitļu formāti un Access datu tipi, importējot datus no programmas Excel uz Access, un sniegti daži padomi par vislabākajiem datu tipiem, ko izvēlēties Izklājlapas importēšanas vednī.

Excel 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 nesacirstu datus.

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 programmā Access, Excel arī tiek izmantots viens un tas pats sērijas datuma numurs, 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ā Access neatpazīst datumu sistēmu 1904 (tiek izmantota 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 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 Yes izmanto -1, bet visām vērtībām No — 0, bet programma Excel 1 visām vērtībām TRUE un 0 visām vērtībām FALSE.

Izvēlieties Jā/nē,kas automātiski konvertē pamatā esošās vērtības.

Hipersaite

Hiper​​​saite

Hipersaite lietojumprogrammā Excel Access satur 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 tās dzēšanas.

Papildinformāciju skatiet Access palīdzības tēmā Datu importēšana vai saistīšana ar datiem Excel darbgrāmatā.

Automātiska datu pievienošana vienkāršā veidā

Biežākā problēma Excel lietotājiem ir datu ar vienādām kolonnām pievienošana vienā lielā darblapā. 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. Nav lietotāja interfeisa komandas vai ērta veida, kā pievienot līdzīgus datus Excel.

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 operācijas, 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. Savienošana piekļuve Access datiem no 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 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, var arī automātiski atsvaidzināt (vai atjaunināt) savu Excel no Programmas Access ikreiz, kad dati tiek atjaunināti programmā Access.

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 vissavienkāršākajā atomu daļā, saistīto jautājumu atdalīšana savās tabulās, šo tabulu kopēšana un ielīmēšana no Excel programmā Access, jaunu Access tabulu galveno relāciju izveide 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 ne 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 vārds

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ā, var izmantot komandu Teksts par kolonnu programmā Excel, lai atdalī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ākās tālāk redzamajās parauga datu tabulās redzama tā pati informācija no Excel darblapā pēc tā sadalīšanas 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ījumu 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 visas 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 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, varat kopēt šos datus 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ālistiem Excel Tech kopienā, saņemt atbalstu Answers kopienā vai ieteikt jaunu līdzekli vai uzlabojumu vietnē Excel User Voice.

Vai nepieciešama papildu palīdzība?

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

×