Applies ToExcel pakalpojumam Microsoft 365 Excel 2024 Access 2024 Excel 2021 Access 2021 Excel 2019 Access 2019 Excel 2016 Access 2016

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.

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

Hiper​​​saite

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

Reed

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

Reed

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.

Nepieciešama papildu palīdzība?

Vēlaties vairāk opciju?

Izpētiet abonementa priekšrocības, pārlūkojiet apmācības kursus, uzziniet, kā aizsargāt ierīci un veikt citas darbības.

Kopienas palīdz uzdot jautājumus un atbildēt uz tiem, sniegt atsauksmes, kā arī saņemt informāciju no ekspertiem ar bagātīgām zināšanām.