Applies To„Excel“, skirta „Microsoft 365“ „Excel 2024“ „Access 2024“ Excel 2021 Access 2021 Excel 2019 Access 2019 Excel 2016 Access 2016

Pastaba: "Microsoft Access" nepalaiko "Excel" duomenų importavimo naudojant pritaikytą slaptumo žymą. Norėdami išspręsti šią problemą, prieš importuodami galite pašalinti žymą ir importavę iš naujo pritaikyti etiketę. Daugiau informacijos žr. Slaptumo žymų taikymas "Office" failams ir el. paštui.

Šiame straipsnyje aiškinama, kaip perkelti duomenis iš "Excel" į "Access" ir konvertuoti duomenis į sąryšinių lentelių, kad galėtumėte naudoti "Microsoft Excel" ir "Access" kartu. Norint apibendrinti, "Access" geriausiai tinka duomenims užfiksuoti, saugoti, teikti užklausas ir bendrinti, o "Excel" geriausiai tinka duomenims skaičiuoti, analizuoti ir vizualizuoti.

Du straipsniai: "Access" arba "Excel" naudojimas duomenims valdyti ir 10 priežasčių, kodėl "Access" naudojama su "Excel", aptarkite, kuri programa geriausiai tinka konkrečiai užduočiai, ir kaip kartu naudoti "Excel" ir "Access", kad sukurtumėte praktišką sprendimą.

Perkėlus duomenis iš "Excel" į "Access", procesą galima atlikti trimis pagrindiniais veiksmais.

three basic steps

Pastaba: Informacijos apie duomenų modeliavimą ir ryšius programoje "Access" žr. Duomenų bazės kūrimo pagrindai.

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

Duomenų importavimas – tai operacija, kuri gali daug sklandžiau atlikti, jei šiek tiek užtruksite duomenų paruošimui ir valymui. Duomenų importavimas yra tarsi perėjimas į naują namą. Jei prieš persikeldami valote ir tvarkote savo turtą, daug lengviau atsisėsti į naujus namus.

Duomenų valymas prieš importuojant

Prieš importuojant duomenis į "Access", programoje "Excel" naudinga:

  • Konvertuoti langelius, kuriuose yra ne atominės reikšmės (t. y. kelios reikšmės viename langelyje) į kelis stulpelius. Pavyzdžiui, langelis stulpelyje "Įgūdžiai", kuriame yra kelios įgūdžių reikšmės, pvz., "C# programavimas", "VBA programavimas" ir "žiniatinklio dizainas", turėtų būti suskirstytas į atskirus stulpelius, kuriuose yra tik viena įgūdžių reikšmė.

  • Naudokite komandą TRIM, kad pašalintumėte pradžioje, pabaigoje ir kelis įdėtuosius tarpus.

  • Pašalinkite nespausdinamus simbolius.

  • Raskite ir ištaisykite rašybos ir skyrybos klaidas.

  • Pašalinkite pasikartojančias eilutes arba pasikartojančius laukus.

  • Įsitikinkite, kad duomenų stulpeliuose nėra mišraus formato, ypač skaičių, suformatuotų kaip tekstas arba datos, suformatuotos kaip skaičiai.

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

Pastaba: Jei duomenų valymo poreikiai yra sudėtingi arba neturite laiko ar išteklių procesui automatizuoti patys, galite naudoti trečiosios šalies tiekėją. Norėdami gauti daugiau informacijos, savo žiniatinklio naršyklėje ieškokite "duomenų valymo programinė įranga" arba "duomenų kokybė".

Importuodami pasirinkite geriausią duomenų tipą

"Access" importavimo operacijos metu norite priimti gerus sprendimus, kad gautumėte kelias (jei tokių yra) konvertavimo klaidas, kurioms atlikti reikės neautomatinio įsikišimo. Šioje lentelėje apibendrinama, kaip "Excel" skaičių formatai ir "Access" duomenų tipai konvertuojami importuojant duomenis iš "Excel" į "Access", ir pateikiama patarimų, kokius geriausius duomenų tipus pasirinkti skaičiuoklės importavimo vediklyje.

"Excel" skaičių formatas

„Access“ duomenų tipas

Komentarai

Geriausia praktika

Tekstas

Tekstas, atmintinė

"Access" teksto duomenų tipe saugomi raidiniai ir skaitiniai duomenys iki 255 simbolių. "Access Memo" duomenų tipe saugomi raidiniai ir skaitiniai duomenys iki 65 535 simbolių.

Pasirinkite Atmintinė , kad nepraeitumėte jokių duomenų.

Skaičius, procentas, trupmena, mokslinis

Skaičius

"Access" turi vieną skaičių duomenų tipą, kuris priklauso nuo ypatybės Lauko dydis (Baitas, Sveikasis skaičius, Ilgasis sveikasis skaičius, Vienas, Dvigubas, Dešimtainis).

Pasirinkite Dvigubas , kad išvengtumėte duomenų konvertavimo klaidų.

Data

Data

Abi programos "Access" ir "Excel" naudoja tą patį datų sekos numerį datoms saugoti. Programoje "Access" datos diapazonas yra didesnis: nuo -657 434 (100 m. sausio 1 d.) iki 2 958 465 (9999 m. gruodžio 31 d.).

Programa Access neatpažįsta 1904 m. datos sistemos (naudojamos "Excel", skirtoje Macintosh versijai), todėl turite konvertuoti datas naudodami "Excel" arba "Access", kad išvengtumėte painiavos.

Daugiau informacijos žr . Datos sistemos, formato arba dviejų skaitmenų metų interpretavimo keitimas ir Duomenų importavimas arba susiejimas "Excel" darbaknygėje.

Pasirinkite Data.

Laikas

Laikas

"Access" ir "Excel" laiko reikšmes saugo naudodamos tą patį duomenų tipą.

Pasirinkite Laikas, kuris paprastai yra numatytasis.

Valiuta, apskaita

Valiuta

Programoje "Access" duomenų tipas Valiuta saugo duomenis kaip 8 baitų skaičius tiksliai iki keturių dešimtainių skilčių ir naudojamas finansiniams duomenims saugoti ir reikšmių apvalinimo prevencijai.

Pasirinkite Valiuta, kuri paprastai yra numatytoji.

bulio logika

Taip / Ne

Programa "Access" naudoja -1 visoms Taip reikšmėms ir 0 visoms reikšmėms Ne, o "Excel" naudoja 1 visoms REIKŠMĖMS TRUE, o 0 – visoms FALSE reikšmėms.

Pasirinkite Taip/ne, kuri automatiškai konvertuos esamas reikšmes.

Hipersaitas

Hipersaitas

"Excel" ir "Access" hipersaite yra URL arba žiniatinklio adresas, kurį galite spustelėti ir stebėti.

Pasirinkite Hipersaitas, kitu atveju pagal numatytuosius parametrus "Access" gali naudoti teksto duomenų tipą.

Kai duomenys yra "Access", galite panaikinti "Excel" duomenis. Nepamirškite prieš panaikindami pradinę "Excel" darbaknygę sukurti atsarginę kopiją.

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

Automatinis duomenų papildymas paprastu būdu

Dažnai "Excel" vartotojai prideda duomenis su tais pačiais stulpeliais viename dideliame darbalapyje. Pavyzdžiui, gali būti, kad turite turto sekimo sprendimą, kuris buvo pradėtas programoje "Excel", bet dabar tapo įtraukiant failus iš daugelio darbo grupių ir skyrių. Š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šių duomenų programoje "Excel".

Geriausias sprendimas yra naudoti "Access", kurioje galite lengvai importuoti ir papildyti duomenis į vieną lentelę naudodami skaičiuoklės importavimo vediklį. 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, kad automatizuotumėte procesą.

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

Iš pirmo žvilgsnio tai, kaip normalizuoti savo duomenis, gali atrodyti bauginanti užduotis. Laimei, "Access" lentelių normalizavimas yra daug paprastesnis procesas dėl lentelių analizatoriaus vediklio.

the table analyzer wizard

1. Vilkite pasirinktus stulpelius į naują lentelę ir automatiškai sukurkite ryšius

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

Naudodami šį vediklį galite atlikti šiuos veiksmus:

  • Konvertuokite lentelę į mažesnių lentelių rinkinį ir automatiškai sukurkite pirminio ir išorinio rakto ryšį tarp lentelių.

  • Į esamą lauką, kuriame yra unikalių reikšmių, įtraukite pirminį raktą arba sukurkite naują ID lauką, kuriame naudojamas duomenų tipas "AutoNumber".

  • Automatiškai kurkite ryšius, kad įgalintumėte nuorodų vientisumą naudodami pakopinius naujinimus. Pakopiniai naikinimai nėra automatiškai įtraukiami, kad nebūtų galima netyčia panaikinti duomenų, bet vėliau galite lengvai įtraukti pakopinį naikinimą.

  • Naujose lentelėse ieškokite nereikalingų arba besidubliuojančių duomenų (pvz., to paties kliento su dviem skirtingais telefono numeriais) ir atnaujinkite tai, kaip norite.

  • Sukurkite pradinės lentelės atsarginę kopiją ir pervardykite ją prie jos pavadinimo pridėdami "_OLD". Tada galite sukurti užklausą, kuri rekonstruoja 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 žr. Duomenų normalizavimas naudojant lentelių analizatorių.

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

Normalizavus duomenis programoje "Access" ir sukūrus užklausą ar lentelę, kuri atkurs pradinius duomenis, paprasčiausia 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 konteineris, naudojamas išoriniams duomenų šaltiniams 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 pavadinimo 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 žr. Duomenų importavimas iš išorinių duomenų šaltinių ("Power Query").

Duomenų gavimas į "Access"

Šiame skyriuje supažindinama su šiais duomenų normalizavimo etapais: Stulpelių Pardavėjas ir Adresas reikšmių skaidymas į labiausiai atomines dalis, susijusių subjektų atskyrimas į atskiras lenteles, tų lentelių kopijavimas ir įklijavimas iš "Excel" į "Access", pagrindinių ryšių tarp naujai sukurtų "Access" lentelių kūrimas ir paprastos užklausos kūrimas ir vykdymas programoje "Access", kad būtų pateikta informacija.

Ne normalizuotos formos duomenų pavyzdys

Šiame darbalapyje stulpelyje Pardavėjas ir stulpelyje Adresas yra neatominių reikšmių. Abu stulpeliai turi būti išskaidyti į du ar daugiau atskirų stulpelių. Šiame darbalapyje taip pat yra informacijos apie pardavėjus, produktus, klientus ir užsakymus. Ši informacija taip pat turėtų būti padalyta į atskiras lenteles pagal temą.

Pardavėjas

Užsakymo ID

Užsakymo data

Produkto ID

Kiekis

Kaina

Kliento vardas ir pavardė

Adresas

Telefonas

Li, Jalė

2349

3/4/09

C-789

3

7,00 EUR

Fourth kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Li, Jalė

2349

3/4/09

C-795

6

9,75 JAV dol.

Fourth kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Adomas, Linas

2350

3/4/09

A-2275

2

16,75 JAV dol.

Nuotykių darbai

1025 Kolumbijos žiedas Kirkland, WA 98234

425-555-0185

Adomas, Linas

2350

3/4/09

F-198

6

5,25 JAV dol.

Nuotykių darbai

1025 Kolumbijos žiedas Kirkland, WA 98234

425-555-0185

Adomas, Linas

2350

3/4/09

B–205 m.

1

4,50 JAV dol.

Nuotykių darbai

1025 Kolumbijos žiedas Kirkland, WA 98234

425-555-0185

Hance, Jim

2351

3/4/09

C-795

6

9,75 JAV dol.

UAB Contoso.

2302 Harvard Ave Bellevue, WA 98227

425-555-0222

Hance, Jim

2352

3/5/09

A-2275

2

16,75 JAV dol.

Nuotykių darbai

1025 Kolumbijos žiedas Kirkland, WA 98234

425-555-0185

Hance, Jim

2352

3/5/09

D-4420

3

7,25 JAV dol.

Nuotykių darbai

1025 Kolumbijos žiedas Kirkland, WA 98234

425-555-0185

Koch, Reed

2353

3/7/09

A-2275

6

16,75 JAV dol.

Fourth kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Koch, Reed

2353

3/7/09

C-789

5

7,00 EUR

Fourth kava

7007 Cornell St Redmond, WA 98199

425-555-0201

Informacija mažiausiose jos dalyse: atominiai duomenys

Dirbdami su šio pavyzdžio duomenimis, programoje "Excel" galite naudoti komandą Tekstą į stulpelį , kad atskirtumėte "atomines" langelio dalis (pvz., adresą, miestą, apskritį ir pašto kodą) į atskirus stulpelius.

Šioje lentelėje rodomi nauji stulpeliai tame pačiame darbalapyje po to, kai jie buvo suskaidyti, kad visos reikšmės taptų atominėmis. Atkreipkite dėmesį, kad stulpelyje Pardavėjas esanti informacija buvo suskaidyta į stulpelius Pavardė ir Vardas, o informacija stulpelyje Adresas suskaidyta į stulpelius Adresas, Miestas, Valstija ir Pašto indeksas. Šie duomenys yra "pirmosios normalinės formos".

Pavardė

Vardas

 

Adresas

Miestas

Valstybė

Pašto indeksas

Li

Yale

2302 Harvard Ave

Bellevue

WA

98227

Kenteris

Ellen

1025 Kolumbijos ratas

Kaunas

WA

98234

Petkus

Jim

2302 Harvard Ave

Bellevue

WA

98227

Koch

Reed

7007 Cornell St Redmond

Redmond

WA

98199

Duomenų skaidymas į sutvarkytas temas programoje "Excel"

Kelios stebimų duomenų pavyzdžių lentelės rodo tą pačią informaciją iš "Excel" darbalapio, kai ji suskaidyta į lenteles, skirtas pardavėjams, produktams, klientams ir užsakymams. Lentelės dizainas nėra galutinis, bet yra tinkamame kelyje.

Lentelėje Pardavėjas 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, kad būtų galima sujungti užsakymus su pardavėjais.

Pardavėjai

Pardavėjo ID

Pavardė

Vardas

101

Li

Yale

103

Kenteris

Ellen

105

Petkus

Jim

107

Koch

Reed

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 sujungti su lentele Užsakymo išsami informacija.

Produktai

Produkto ID

Kaina

A-2275

16.75

B–205 m.

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 sujungti su lentele Užsakymai.

Customers

Kliento ID

Pavadinimas

Adresas

Miestas

Valstybė

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ą šios lentelės informaciją reikia išskaidyti į papildomą lentelę, kurioje yra užsakymo išsami 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 nebuvo suskaidyta į lentelę Užsakymų išsami 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 m.

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 Užsakymo išsami informacija. Turėkite omenyje, kad yra 9 užsakymai, todėl prasminga, kad šioje lentelėje yra 9 įrašai. Atkreipkite dėmesį, kad lentelėje Užsakymai yra unikalus ID (Užsakymo ID), kuris nurodomas 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 reikia unikalių reikšmių (t. y. nėra pirminio rakto), todėl yra gerai, kad bet kuriame arba visuose stulpeliuose būtų perteklinių duomenų. Tačiau jokie du įrašai šioje lentelėje neturėtų būti visiškai identiški (ši taisyklė taikoma bet kokiai duomenų bazės lentelei). Šioje lentelėje turi būti 17 įrašų, kurių kiekvienas atitinka atskirą užsakymą. Pavyzdžiui, 2349 eilės tvarka tris C–789 produktus sudaro viena 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 m.

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" į programą "Access"

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

Be to, galite kurti formas ir ataskaitas, kad būtų lengviau įvesti ir analizuoti duomenis.

Reikia daugiau pagalbos?

Visada galite paklausti „Excel“ technologijų bendruomenės specialisto arba gauti palaikymo bendruomenėse.

Reikia daugiau pagalbos?

Norite daugiau parinkčių?

Sužinokite apie prenumeratos pranašumus, peržiūrėkite mokymo kursus, sužinokite, kaip apsaugoti savo įrenginį ir kt.

Bendruomenės padeda užduoti klausimus ir į juos atsakyti, pateikti atsiliepimų ir išgirsti iš ekspertų, turinčių daug žinių.