Datu pārvietošana no programmas Excel uz programmu Access

Šajā rakstā ir paskaidrots, kā pārvietot datus no programmas Excel un pārvērtiet datus par relāciju tabulām, lai varētu izmantot programmu Microsoft Excel un piekļūt kopā. Lai summētu, Access ir vislabākā datu fiksēšanai, glabāšanai, vaicājumiem un koplietošanai, un Excel ir vispiemērotākā datu aprēķināšanai, analizēšanai un vizualizēšanai.

Divi raksti, izmantojot Access vai Excel, lai pārvaldītu savus datus un 10 galvenos iemeslus, kāpēc izmantot programmu Access ar Excel, apspriest, kura programma ir vispiemērotākā konkrētam uzdevumam un kā izmantot programmu Excel un piekļūt kopā, lai izveidotu praktisku risinājumu.

Kad pārvietojat datus no programmas Excel uz programmu Access, procesam ir trīs vienkāršas darbības.

three basic steps

Piezīme.: Informāciju par datu modelēšanu un relācijām programmā Access skatiet rakstā datu bāzu izveides pamati.

1. darbība: datu importēšana no programmas Excel uz programmu Access

Datu importēšana ir darbība, kas var būt daudz mierīgāka, ja aizņemt kādu laiku datu sagatavošanai un tīrīšanai. Datu importēšana ir kā pāreja uz jaunu mājas lietošanai. Ja iztīrāt un organizējat savus īpašumus, pirms pāriešanas uz jauno mājokli, ir daudz vieglāk.

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

Pirms importējat datus programmā Access, programmā Excel ir ieteicams:

  • Konvertēt šūnas, kurās ir dati, kas nav atomi (tas ir, vairākas vērtības vienā šūnā) uz vairākām kolonnām. Piemēram, šūnas "prasmes" kolonnā, kurā ir vairākas prasmju vērtības, piemēram, "C# programmēšana", "VBA programmēšana" un "tīmekļa noformējums" ir jāizlauž, lai atdalītu kolonnas, kurās katrs 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ājamās rakstzīmes.

  • Atrodiet un izlabojiet pareizrakstības un interpunkcijas kļūdas.

  • Noņemt dublētās rindas vai dublētos laukus.

  • Pārliecinieties, vai datu kolonnās nav jauktu formātu, jo īpaši skaitļi, kas formatēti kā teksts vai datumi, kas formatēti kā skaitļi.

Lai iegūtu papildinformāciju, skatiet šīs Excel palīdzības tēmas:

Piezīme.: Ja jūsu datu tīrīšanas vajadzības ir sarežģītas vai jums nav laika vai resursu, lai automatizētu procesu patstāvīgi, varat apsvērt iespēju izmantot trešās puses piegādātāju. Lai iegūtu papildinformāciju, savā tīmekļa pārlūkprogrammā meklējiet "datu tīrīšanas programmatūra" vai "datu kvalitāte".

Vislabākā datu tipa izvēle importēšanas laikā

Importēšanas darbības laikā programmā Access jūs vēlaties izdarīt labu izvēli, lai saņemtu dažas (ja tādas ir) konvertēšanas kļūdas, kurām būs nepieciešama manuāla iejaukšanās. Šajā tabulā apkopots, kā Excel skaitļu formāti un Access datu tipi tiek konvertēti, kad importējat datus no programmas Excel uz programmu Access, un tiek sniegti daži padomi par labākajiem datu tipiem, kurus izvēlēties izklājlapas importēšanas vednī.

Excel skaitļu formāts

Access datu tips

Komentāri

Paraugprakse

Teksts

Text, Memo

Access teksta datu tips 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 izvairītos no datu apciršanas.

Skaitlis, procentuālā vērtība, daļskaitlis, zinātnisks

Skaitlis

Programmā Access ir viens datu tips Number, kas atšķiras atkarībā no lauka lieluma rekvizīta (baits, vesels skaitlis, garš vesels skaitlis; viens, dubults, decimāldaļa).

Izvēlieties dubults , lai nepieļautu datu konvertēšanas kļūdas.

Datums

Datums

Access un Excel izmanto vienu un to pašu sērijas datuma numuru, lai glabātu datumus. Programmā Access datumu diapazons ir lielāks: no-657 434 (1. janvāris, 100 ad) līdz 2 958 465 (31. decembris, 9999 AD).

Tā kā programma Access neatpazīst 1904 datumu sistēmu (tiek izmantota programmā Excel for Macintosh), ir jākonvertē datumi programmā Excel vai Access, lai izvairītos no neskaidrības.

Lai iegūtu papildinformāciju, skatiet rakstu datuma sistēmas, formāta vai divciparu gada interpretācijas mainīšana un datu importēšana no Excel darbgrāmatas vai saistīšana arto.

Izvēlieties datums.

Laiks

Laiks

Access un Excel glabā laika vērtības, izmantojot vienus un tos pašus datu tipus.

Izvēlieties laiku, kas parasti ir noklusējuma vērtība.

Valūta, grāmatvedība

Valūta

Programmā Access datu tips valūta saglabā datus kā 8 baitu skaitļus ar precizitāti līdz četrām decimāldaļas vietām, un tiek izmantots, lai glabātu finanšu datus un nepieļautu vērtību noapaļošanu.

Izvēlieties valūtu, kas parasti ir noklusējuma vērtība.

Būla izteiksme

Jā/nē

Programma Access izmanto-1 visām vērtībām Jā vērtības un 0 visām bez vērtību, bet Excel izmanto 1 visām patiesājām vērtībām un 0 visām APLAMajām vērtībām.

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

Hipersaite

Hiper​​​saite

Hipersaite programmā Excel un Access satur vietrādi URL vai tīmekļa adresi, uz kuras varat noklikšķināt un sekot.

Izvēlieties hipersaite, pretējā gadījumā Access var izmantot teksta datu tipu pēc noklusējuma.

Kad dati ir programmā Access, varat izdzēst Excel datus. Neaizmirstiet vispirms dublēt oriģinālo Excel darbgrāmatu, pirms tās izdzēšanas.

Lai iegūtu papildinformāciju, skatiet rakstu Access palīdzības tēma datu importēšana vai saistīšana ar Excel darbgrāmatas datiem.

Automātiska datu pievienošana vienkāršs veids

Biežāk sastopamās problēmas programmas Excel lietotāji var pievienot datus ar vienu un to pašu kolonnu vienā lielā darblapā. Piemēram, jums var būt līdzekļu izsekošanas risinājums, kas sākts programmā Excel, bet tagad ir pieaudzis, iekļaujot 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 nevienas lietotāja interfeisa komandas vai vienkāršs veids, kā programmā Excel pievienot līdzīgus datus.

Labākais risinājums ir izmantot programmu Access, kur varat viegli importēt un pievienot datus vienā tabulā, izmantojot izklājlapas importēšanas vedni. Turklāt vienā tabulā varat pievienot daudz datu. Varat saglabāt importēšanas darbības, pievienot tās kā plānotos Microsoft Outlook uzdevumus, kā arī izmantot makro, lai automatizētu procesu.

2. darbība: datu normalizēšana, izmantojot tabulu analīzes vedni

No pirmā acu uzmetiena, kas notiek ar datu normalizēšanas procesu, var šķist biedējošs uzdevums. Par laimi, tabulu normalizēšana programmā Access ir daudz vienkāršāka darbība, pateicoties tabulas analīzes vednim.

.

1. Velciet atlasītās kolonnas uz jaunu tabulu un automātiski izveidojiet relācijas

2. Izmantojiet pogas komandas, lai pārdēvētu tabulu, pievienotu primāro atslēgu, padarītu esošo kolonnu par primāro atslēgu un atsauktu pēdējo darbību.

Šo vedni var izmantot, lai veiktu tālāk norādītās darbības.

  • Tabulas pārvēršana par mazāku tabulu kopu un automātiski izveido primārās un ārējās atslēgas relāciju starp tabulām.

  • Pievienojiet esošu lauku, kurā ir unikālas vērtības, vai izveidojiet jaunu ID lauku, kas izmanto datu tipu AutoNumber.

  • Automātiski izveidot relācijas, lai ieviestu attiecinošo integritāti ar kaskadētajiem atjauninājumiem. Kaskadētā dzēšana netiek automātiski pievienota, lai neļautu nejauši izdzēst datus, taču varat vienkārši pievienot kaskadēto dzēšanu vēlāk.

  • Meklējiet jaunās tabulās datus par liekajiem vai dublikātiem (piemēram, to pašu klientu ar diviem dažādiem tālruņa numuriem) un atjauniniet tos pēc vajadzības.

  • Dublējiet sākotnējo tabulu un pārdēvējiet to, pievienojot "_OLD" tās nosaukumam. Pēc tam izveidojiet vaicājumu, kas rekonstruē sākotnējo tabulu ar sākotnējo tabulas nosaukumu, lai visas esošās veidlapas vai atskaites, kuru pamatā ir sākotnējā tabula, darbotos ar jauno tabulas struktūru.

Papildinformāciju skatiet rakstā datu normalizēšana, izmantojot tabuluanalīzi.

3. darbība: savienojuma izveide ar Access datiem no programmas Excel

Pēc tam, 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. Tagad jūsu dati ir programmā Access kā ārējs datu avots, un to var pievienot darbgrāmatā, 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 arī saglabāt savienojuma failā, piemēram, Office datu savienojuma (. odc faila nosaukuma paplašinājumā) vai datu avota nosaukuma failā (. DSN paplašinājums). Kad ir izveidots savienojums ar ārējiem datiem, varat arī automātiski atsvaidzināt (vai atjaunināt) Excel darbgrāmatu no Access, tiklīdz dati tiek atjaunināti programmā Access.

Papildinformāciju skatiet rakstā datu importēšana no ārējiem datu avotiem (Power Query).

Datu iegūšana programmā Access

Šajā sadaļā ir aprakstītas tālāk norādītās datu normalizēšanas fāzes: vērtību sadalīšana pārdevēja un adreses kolonnās to lielākajā daļā atomu, ar kuriem saistītās tēmas tiek sadalītas savās tabulās, kopējot un ielīmējot šīs tabulas no programmas Excel Piekļuve, veidojot galvenās relācijas starp jaunizveidotajām Access tabulām un izveidojot un palaižot vienkāršu vaicājumu programmā Access, lai atgrieztu informāciju.

Datu piemērs nenormalizētā formā

Tālāk redzamajā darblapā ir ietvertas neatomu vērtības kolonnā Pārdevējs un adreses kolonna. Abas kolonnas ir jādala divās vai vairāk atsevišķās kolonnās. Šajā darblapā ir iekļauta arī informācija par pārdevējiem, produktiem, klientiem un pasūtījumiem. Šī informācija pēc tēmas ir arī jādala atsevišķās tabulās.

Pārdevējs

Order ID

Pasūtījuma datums

Produkta ID

Qty

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

Adamss, Inga

2350

3/4/09

A-2275

2

$16,75

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adamss, Inga

2350

3/4/09

F-198

6

$5,25

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adamss, Inga

2350

3/4/09

B-205

1

$4,50

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Īklu, Džim

2351

3/4/09

C-795

6

$9,75

Contoso, Ltd.

2302 Harvard Ave Bellevue, WA 98227

425-555-0222

Īklu, Džim

2352

3/5/09

A-2275

2

$16,75

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Īklu, Džim

2352

3/5/09

D-4420

3

$7,25

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Kohs, Rīds

2353

3/7/09

A-2275

6

$16,75

Kafejnīca “Viktorija”

7007 Cornell St Redmond, WA 98199

425-555-0201

Kohs, Rīds

2353

3/7/09

C-789

5

$7,00

Kafejnīca “Viktorija”

7007 Cornell St Redmond, WA 98199

425-555-0201

Informācija mazākās daļās: atomu dati

Darbs ar datiem šajā piemērā varat izmantot programmas Excel komandu teksts par kolonnu , lai atdalītu šūnas "Atomic" (piemēram, iela, pilsēta, novads un pasta indekss).

Tālāk esošajā tabulā ir norādītas jaunās kolonnas tajā pašā darblapā pēc tam, kad tās ir sakārtotas, lai visas vērtības būtu atoms. Ņemiet vērā, ka kolonnā Pārdevējs ietvertā informācija ir sadalīta ar vārdu un uzvārdu kolonnām, un informācija kolonnā adrese ir sadalīta uz pasta adreses, pilsētas, valsts un pasta indeksa kolonnām. Šie dati ir pirmajā parastajā formā.

Uzvārds

Vārds

 

Adrese

Pilsēta

Štats

pasta indekss

Li

Yale

2302 Harvard Ave

Liepāja

WA

98227

Mieriņa

Inga

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 organizētos tēmās programmā Excel

Vairākas piemēra datu tabulas, kas seko, parāda to pašu informāciju no Excel darblapas pēc tam, kad tās ir sadalītas tabulās pārdevējiem, produktiem, klientiem un pasūtījumiem. Tabulas noformējums nav galīgais variants, taču tas atrodas pareizajā trasē.

Tabulā Pārdevējs ir iekļauta tikai informācija par pārdošanas personālu. Ņemiet vērā, ka katram ierakstam ir unikāls ID (pārdevēja ID). Pārdevēja ID vērtība tiks izmantota tabulā pasūtījumi, lai izveidotu pasūtījumus pārdevējiem.

Pārdevēji

Pārdevēja ID

Uzvārds

Vārds

101

Li

Yale

103

Mieriņa

Inga

105

Baltiņš

Jānis

107

Koch

Reed

Tabulā Products ir iekļauta tikai informācija par produktiem. Ņemiet vērā, ka katram ierakstam ir unikāls ID (produkta ID). Produkta ID vērtība tiks izmantota, lai pievienotu informāciju par produktu tabulā 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 iekļauta tikai informācija par klientiem. Ņemiet vērā, ka katram ierakstam ir unikāls ID (klienta ID). Klienta ID vērtība tiks izmantota, lai tabulā pasūtījumi savienotu klienta informāciju.

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 iekļauta informācija par pasūtījumiem, pārdevējiem, klientiem un produktiem. Ņemiet vērā, ka katram ierakstam ir unikāls ID (pasūtījuma ID). Daļa šīs tabulas informācijas ir jādala papildu tabulā, kurā ir detalizēta informācija par pasūtījumu, lai tabulā pasūtījumi būtu tikai četras kolonnas — unikālā pasūtījuma ID, pasūtījuma datums, pārdevēja ID un klienta ID. Šeit redzamā tabula vēl nav sadalīta tabulā pasūtījuma dati.

Orders

Order ID

Pasūtījuma datums

Pārdevēja ID

Klienta ID

Produkta ID

Qty

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

Detalizēta informācija par pasūtījumu, piemēram, produkta ID un daudzums, tiek pārvietota ārpus tabulas Pasūtījumi un glabāta tabulā ar nosaukumu Order Details. Atcerieties, ka ir 9 pasūtījumi, tāpēc ir vērts, ka šajā tabulā ir 9 ieraksti. Ņemiet vērā, ka tabulā pasūtījumi ir unikāls ID (pasūtījuma ID), kas no tabulas Order Details tiek norādīts.

Tabulas Pasūtījumi galīgais noformējums izskatās šādi:

Orders

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 nevienas kolonnas, kurām ir nepieciešamas unikālas vērtības (tas ir, nav primārās atslēgas), tāpēc ir labi, ja visas vai visas kolonnas satur "liekās" datus. Tomēr šiem diviem ierakstiem šajā tabulā jābūt pilnīgi identiskiem (šī kārtula attiecas uz jebkuru tabulu datu bāzē). Šajā tabulā ir jābūt 17 ierakstiem — katrs atbilst produktam atsevišķā pasūtījumā. Piemēram, 2349 ar trim C-789 produktiem ir viena no divām visu pasūtījumu daļām.

Tāpēc tabulā pasūtījuma dati ir jāizskatās šādi:

Detalizēta informācija par pasūtījumu

Order ID

Produkta ID

Qty

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ījumu detaļām ir izdalīta atsevišķos tēmas programmā Excel, varat kopēt šos datus tieši programmā Access, kur tie kļūst par tabulām.

Relāciju izveide starp Access tabulām un vaicājuma izpildi

Pēc tam, kad esat pārvietojis datus uz programmu 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 to pārdevēju vārdus, kuri ir ievadīti starp 3/05/09 un 3/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.

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?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×