Notă: Microsoft Access nu acceptă importul de date Excel cu o etichetă de confidențialitate aplicată. Ca soluție de evitare, puteți să eliminați eticheta înainte de a o importa, apoi să o aplicați din nou după import. Pentru mai multe informații, consultați Aplicarea etichetelor de confidențialitate la fișiere și mesaje de e-mail în Office.
Acest articol vă arată cum să mutați datele din Excel în Access și cum să efectuați conversia datelor în tabele relaționale, astfel încât să puteți utiliza Microsoft Excel și Access împreună. Pentru a rezuma, Access este ideal pentru capturarea, stocarea, interogarea și partajarea datelor, iar Excel este ideal pentru calcularea, analizarea și vizualizarea datelor.
Două articole, Utilizarea Access sau Excel pentru a vă gestiona datele și Primele 10 motive pentru a utiliza Access cu Excel, discută ce program este cel mai potrivit pentru o anumită activitate și cum să utilizați Excel și Access împreună pentru a crea o soluție practică.
Atunci când mutați date din Excel în Access, există trei pași de bază pentru proces.
Notă: Pentru informații despre modelarea datelor și relațiile din Access, consultați Noțiuni de bază despre proiectarea bazelor de date.
Pasul 1: Importul datelor din Excel în Access
Importul datelor este o operațiune care poate merge mult mai bine dacă vă luați un timp pentru a pregăti și a curăța datele. Importul datelor este ca și cum vă mutați într-o casă nouă. Dacă vă curățați și vă organizați bunurile înainte de a vă muta, stabilirea în casa ta nouă este mult mai ușor.
Curățați datele înainte de a importa
Înainte de a importa date în Access, în Excel este o idee bună să:
-
Efectuați conversia celulelor care conțin date non-atomice (adică mai multe valori într-o celulă) în mai multe coloane. De exemplu, o celulă dintr-o coloană "Abilități" care conține mai multe valori de competențe, cum ar fi programarea "C#", "programare VBA" și "Proiectare web" ar trebui să fie scindată pentru a separa coloanele care conțin fiecare o singură valoare de competență.
-
Utilizați comanda TRIM pentru a elimina spațiile încorporate inițiale, finale și multiple.
-
Eliminați caracterele care nu se imprimă.
-
Găsiți și remediați erorile de ortografie și de punctuație.
-
Eliminați rândurile dublate sau câmpurile dublate.
-
Asigurați-vă că coloanele de date nu conțin formate mixte, în special numere formatate ca text sau date formatate ca numere.
Pentru mai multe informații, consultați următoarele subiecte de ajutor pentru Excel:
Notă: Dacă necesitățile dvs. de curățare a datelor sunt complexe sau nu aveți timp sau resurse pentru a automatiza procesul pe cont propriu, luați în considerare utilizarea unui distribuitor terț. Pentru mai multe informații, căutați "software de curățare a datelor" sau "calitatea datelor" după motorul de căutare preferat în browserul web.
Alegeți cel mai bun tip de date atunci când importați
În timpul operațiunii de import din Access, doriți să luați decizii bune, astfel încât să primiți puține erori de conversie (dacă există) care vor necesita intervenție manuală. Următorul tabel rezumă modul în care sunt convertite formatele de numere Excel și tipurile de date Access atunci când importați date din Excel în Access și oferă câteva sfaturi despre cele mai bune tipuri de date pe care să le alegeți în Expertul Import foaie de calcul.
Format de număr Excel |
Tipul de date Access |
Comentarii |
Exemplu de bună practică |
---|---|---|---|
Text |
Text, Memo |
Tipul de date Text Access stochează date alfanumerice de până la 255 de caractere. Tipul de date Memo Access stochează date alfanumerice de până la 65.535 de caractere. |
Alegeți Memo pentru a evita trunchierea datelor. |
Număr, Procent, Fracție, Științific |
Număr |
Access are un tip de date Număr care variază în funcție de o proprietate Dimensiune câmp (Octet, Întreg, Întreg lung, Simplu, Dublu, Zecimal). |
Alegeți Dublă precizie pentru a evita erorile de conversie a datelor. |
Data |
Dată |
Access și Excel utilizează același număr serial pentru a stoca date. În Access, intervalul de date este mai mare: de la -657.434 (1 ianuarie 100 d.Hr.) la 2.958.465 (31 decembrie 9999 d.Hr.). Deoarece Access nu recunoaște sistemul de dată 1904 (utilizat în Excel pentru Macintosh), trebuie să efectuați conversia datelor în Excel sau Access pentru a evita confuzia. Pentru mai multe informații, consultați Modificarea sistemului de date calendaristice, a formatului sau a interpretării anului cu două cifre și Importul sau legarea la datele dintr-un registru de lucru Excel. |
Alegeți Dată. |
Timp |
Ora |
Access și Excel stochează valori de timp utilizând același tip de date. |
Alegeți Oră, care este de obicei setarea implicită. |
Monedă, Contabil |
Monedă |
În Access, tipul de date Monedă stochează datele ca numere de 8 octeți cu precizie la patru zecimale și este utilizat pentru a stoca date financiare și a împiedica rotunjirea valorilor. |
Alegeți Monedă, care este de obicei valoarea implicită. |
Boolean |
Da/Nu |
Access utilizează -1 pentru toate valorile Da și 0 pentru toate valorile Nu, în timp ce Excel utilizează 1 pentru toate valorile TRUE și 0 pentru toate valorile FALSE. |
Alegeți Da/Nu, care face automat conversia valorilor subiacente. |
Hyperlink |
Hyperlink |
Un hyperlink din Excel și Access conține un URL sau o adresă web pe care puteți să faceți clic și să o urmăriți. |
Alegeți Hyperlink, altfel, Access poate utiliza tipul de date Text în mod implicit. |
După ce datele sunt în Access, puteți șterge datele Excel. Nu uitați să faceți backup registrului de lucru Excel original înainte de a-l șterge.
Pentru mai multe informații, consultați subiectul de ajutor Access Importul sau legarea la datele dintr-un registru de lucru Excel.
Adăugarea automată a datelor într-un mod simplu
O problemă comună pe care o au utilizatorii Excel este adăugarea de date cu aceleași coloane într-o foaie de lucru mare. De exemplu, este posibil să aveți o soluție de urmărire a activelor care a început în Excel, dar acum a crescut pentru a include fișiere din mai multe grupuri de lucru și departamente. Aceste date pot fi în foi de lucru și registre de lucru diferite sau în fișiere text care sunt fluxuri de date din alte sisteme. Nu există nicio comandă de interfață utilizator sau o modalitate simplă de a adăuga date similare în Excel.
Cea mai bună soluție este să utilizați Access, unde puteți să importați și să adăugați cu ușurință date într-un singur tabel, utilizând Expertul Import foaie de calcul. În plus, puteți adăuga o mulțime de date într-un singur tabel. Puteți să salvați operațiunile de import, să le adăugați ca activități Microsoft Outlook planificate și chiar să utilizați macrocomenzi pentru a automatiza procesul.
Pasul 2: Normalizarea datelor utilizând Expertul Analizor de tabel
La prima vedere, parcurgerea procesului de normalizare a datelor poate părea o activitate descurajatoare. Din fericire, normalizarea tabelelor în Access este un proces mult mai ușor, datorită Expertului Analizor de tabel.
1. Glisați coloanele selectate într-un tabel nou și creați automat relații
2. Utilizați comenzile butonului pentru a redenumi un tabel, a adăuga o cheie primară, a transforma o coloană existentă într-o cheie primară și a anula ultima acțiune
Aveți posibilitatea să utilizați acest expert pentru a efectua următoarele:
-
Efectuați conversia unui tabel într-un set de tabele mai mici și creați automat o relație cheie primară și una străină între tabele.
-
Adăugați o cheie primară la un câmp existent care conține valori unice sau creați un câmp ID nou care utilizează tipul de date Numerotare automată.
-
Creați automat relații pentru a impune integritatea referențială cu actualizări în cascadă. Ștergerile în cascadă nu sunt adăugate automat pentru a împiedica ștergerea accidentală a datelor, dar ulterior puteți adăuga cu ușurință ștergeri în cascadă.
-
Căutați în tabelele noi date redundante sau dublate (cum ar fi același client cu două numere de telefon diferite) și actualizați-le după cum doriți.
-
Faceți backup tabelului original și redenumiți-l adăugând "_OLD" la numele său. Apoi creați o interogare care reconstruiește tabelul original, cu numele tabelului original, astfel încât toate formularele sau rapoartele existente bazate pe tabelul original să funcționeze cu noua structură de tabel.
Pentru mai multe informații, consultați Normalizarea datelor utilizând Analizor de tabel.
Pasul 3: Conectarea la datele Access din Excel
După ce datele au fost normalizate în Access și a fost creată o interogare sau un tabel care reconstruiește datele originale, este o chestiune simplă de conectare la datele Access din Excel. Datele dvs. se află acum în Access ca sursă de date externă și, prin urmare, pot fi conectate la registrul de lucru printr-o conexiune de date, care este un container de informații utilizat pentru a găsi, a face log on și a accesa sursa de date externă. Informațiile de conexiune sunt stocate în registrul de lucru și pot fi stocate, de asemenea, într-un fișier de conexiune, cum ar fi un fișier Conexiune de date Office (ODC) (extensia numelui de fișier .odc) sau un fișier nume sursă de date (extensie .dsn). După ce vă conectați la date externe, puteți, de asemenea, să reîmprospătați (sau să actualizați) automat registrul de lucru Excel din Access de fiecare dată când datele se actualizează în Access.
Pentru mai multe informații, consultați Importul datelor din surse de date externe (Power Query).
Accesați-vă datele în Access
Această secțiune vă ajută să parcurgeți următoarele faze ale normalizării datelor: Împărțirea valorilor din coloanele Vânzător și Adresă în părțile lor cele mai atomice, separarea subiectelor asociate în propriile lor tabele, copierea și lipirea tabelelor din Excel în Access, crearea de relații cheie între tabelele Access nou create și crearea și rularea unei interogări simple în Access pentru a returna informații.
Example data in non-normalized form
Următoarea foaie de lucru conține valori non-atomice în coloana Vânzător și în coloana Adresă. Ambele coloane ar trebui să fie împărțite în două sau mai multe coloane separate. Această foaie de lucru conține, de asemenea, informații despre vânzători, produse, clienți și comenzi. Aceste informații ar trebui, de asemenea, să fie împărțite mai departe, în funcție de subiect, în tabele separate.
Vânzător |
ID comandă |
Data comenzii |
ID produs |
Cantitate |
Preț |
Nume client |
Address |
Telefon |
---|---|---|---|---|---|---|---|---|
Li, Yale |
2349 |
3/4/09 |
C-789 |
3 |
7,00 USD |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Li, Yale |
2349 |
3/4/09 |
C-795 |
6 |
9,75 USD |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Adams, Ellen |
2350 |
3/4/09 |
A-2275 |
2 |
16,75 USD |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
F-198 |
6 |
5,25 USD |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
B-205 |
1 |
4,50 USD |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2351 |
3/4/09 |
C-795 |
6 |
9,75 USD |
Contoso, Ltd. |
2302 Harvard Ave Bellevue, WA 98227 |
425-555-0222 |
Hance, Jim |
2352 |
3/5/09 |
A-2275 |
2 |
16,75 USD |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2352 |
3/5/09 |
D-4420 |
3 |
7,25 USD |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Koch, Reed |
2353 |
3/7/09 |
A-2275 |
6 |
16,75 USD |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Koch, Reed |
2353 |
3/7/09 |
C-789 |
5 |
7,00 USD |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Informații în părțile sale cele mai mici: date atomice
Lucrul cu datele din acest exemplu, puteți utiliza comanda Text în coloană din Excel pentru a separa părțile "atomice" ale unei celule (cum ar fi adresa străzii, localitatea, județul și codul poștal) în coloane discrete.
Următorul tabel afișează noile coloane din aceeași foaie de lucru după ce au fost scindate pentru a face toate valorile atomice. Rețineți că informațiile din coloana Vânzător au fost împărțite în coloanele Nume și Prenume și că informațiile din coloana Adresă au fost împărțite în coloanele Adresă poștală, Localitate, Județ și Cod poștal. Aceste date sunt în "prima formă normală".
Nume |
Prenume |
|
Adresă |
Localitate |
Stat |
Cod poştal |
---|---|---|---|---|---|---|
Li |
Yale |
2302 Harvard Ave |
Sinaia |
SB |
98227 |
|
Adams |
Ellen |
Cerc Columbia 1025 |
Cluj |
SB |
98234 |
|
Hance |
Daniel |
2302 Harvard Ave |
Sinaia |
SB |
98227 |
|
Koch |
Stuf |
7007 Cornell St Redmond |
Redmond |
SB |
98199 |
Împărțirea datelor în subiecte organizate în Excel
Mai multe tabele de date din exemplele de date care urmează afișează aceleași informații din foaia de lucru Excel după ce aceasta a fost împărțită în tabele pentru vânzători, produse, clienți și comenzi. Proiectul tabelului nu este final, dar este pe drumul cel bun.
Tabelul Vânzători conține numai informații despre personalul de vânzări. Rețineți că fiecare înregistrare are un ID unic (ID vânzător). Valoarea ID vânzător va fi utilizată în tabelul Comenzi pentru a conecta comenzile la agenți de vânzări.
Vânzători |
||
---|---|---|
ID agent de vânzări |
Nume |
Prenume |
101 |
Li |
Yale |
103 |
Adams |
Ellen |
105 |
Hance |
Daniel |
107 |
Koch |
Stuf |
Tabelul Produse conține doar informații despre produse. Rețineți că fiecare înregistrare are un ID unic (ID produs). Valoarea ID produs va fi utilizată pentru a conecta informațiile despre produs la tabelul Detalii comandă.
Produse |
|
---|---|
ID produs |
Preț |
A-2275 |
16.75 |
B-205 |
4.50 |
C-789 |
7.00 |
C-795 |
9.75 |
D-4420 |
7.25 |
F-198 |
5.25 |
Tabelul Clienți conține doar informații despre clienți. Rețineți că fiecare înregistrare are un ID unic (ID client). Valoarea ID client va fi utilizată pentru a conecta informațiile despre client la tabelul Comenzi.
Customers |
||||||
---|---|---|---|---|---|---|
ID client |
Nume |
Adresă |
Localitate |
Stat |
Cod poştal |
Telefon |
1001 |
Contoso, Ltd. |
2302 Harvard Ave |
Sinaia |
SB |
98227 |
425-555-0222 |
1003 |
Adventure Works |
Cerc Columbia 1025 |
Cluj |
SB |
98234 |
425-555-0185 |
1005 |
Fourth Coffee |
7007 Cornell St |
Redmond |
SB |
98199 |
425-555-0201 |
Tabelul Comenzi conține informații despre comenzi, agenți de vânzări, clienți și produse. Rețineți că fiecare înregistrare are un ID unic (ID comandă). Unele dintre informațiile din acest tabel trebuie să fie împărțite într-un tabel suplimentar care conține detaliile comenzii, astfel încât tabelul Comenzi să conțină doar patru coloane: ID-ul unic al comenzii, data comenzii, ID-ul agentului de vânzări și ID-ul de client. Tabelul afișat aici nu a fost încă împărțit în tabelul Detalii comandă.
Comenzi |
|||||
---|---|---|---|---|---|
ID comandă |
Data comenzii |
ID vânzător |
ID client |
ID produs |
Cantitate |
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 |
Detaliile comenzii, cum ar fi ID-ul de produs și cantitatea, sunt mutate din tabelul Comenzi și stocate într-un tabel denumit Detalii comandă. Rețineți că există 9 comenzi, deci are sens să existe 9 înregistrări în acest tabel. Rețineți că tabelul Comenzi are un ID unic (ID comandă), la care se va face referire din tabelul Detalii comandă.
Proiectarea finală a tabelului Comenzi ar trebui să arate astfel:
Comenzi |
|||
---|---|---|---|
ID comandă |
Data comenzii |
ID vânzător |
ID client |
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 |
Tabelul Detalii comandă nu conține coloane care necesită valori unice (adică nu există o cheie primară), deci este în regulă ca oricare coloană sau toate coloanele să conțină date "redundante". Cu toate acestea, două înregistrări din acest tabel nu ar trebui să fie complet identice (această regulă se aplică la orice tabel dintr-o bază de date). În acest tabel ar trebui să existe 17 înregistrări , fiecare corespunzătoare unui produs într-o comandă individuală. De exemplu, în comanda 2349, trei produse C-789 compun una dintre cele două părți ale întregii comenzi.
Prin urmare, tabelul Detalii comandă ar trebui să arate astfel:
Detalii comandă |
||
---|---|---|
ID comandă |
ID produs |
Cantitate |
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 |
Copierea și lipirea datelor din Excel în Access
Acum că informațiile despre vânzători, clienți, produse, comenzi și detalii despre comenzi au fost împărțite în subiecte separate în Excel, puteți copia datele respective direct în Access, unde vor deveni tabele.
Crearea relațiilor între tabelele Access și rularea unei interogări
După ce ați mutat datele în Access, puteți să creați relații între tabele, apoi să creați interogări pentru a returna informații despre diverse subiecte. De exemplu, puteți crea o interogare care returnează ID comandă și numele agenților de vânzări pentru comenzile introduse între 05.03.2009 și 08.03.09.
În plus, puteți crea formulare și rapoarte pentru a face introducerea datelor și analiza vânzărilor mai simple.
Aveți nevoie de ajutor suplimentar?
Puteți oricând să întrebați un expert din Comunitatea tehnică Excel sau să obțineți asistență în Comunități.