Mutarea datelor din Excel în Access

Acest articol vă arată cum să mutați datele din Excel în Access și cum să faceți conversia datelor în tabele relaționale, astfel încât să utilizați Microsoft Excel și Access împreună. Pe scurt, Access este ideal pentru capturarea, stocarea, interogarea și partajarea datelor, iar Excel este ideal pentru calcularea, analiza și vizualizarea datelor.

Două articole, Utilizarea Access sau Excel pentru a gestiona datele și Principalele 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ă.

Când mutați date din Excel în Access, există trei pași de bază în proces.

Pasul 1: Importul datelor din Excel în Access

Importul de date este o operațiune care se poate desfășura mult mai ușor dacă dedicați un timp pentru a pregăti și a curăța datele. Importul datelor este ca și cum m-ați muta într-o casă nouă. Dacă vă curățați și vă organizați bunurile înainte de a vă muta, este mult mai ușor să vă acomodați în noua casă.

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 singură celulă) în mai multe coloane. De exemplu, o celulă dintr-o coloană "Competențe" care conține mai multe valori de competențe, cum ar fi "Programare C#", "Programare VBA" și "Design web" ar trebui să fie împărțită în coloane separate care conțin fiecare o singură valoare de competență.
• Utilizați comanda TRIM pentru a elimina spațiile încorporate la început, la sfârșit și mai multe.
• Eliminați caracterele neimprimabile.
• Găsiți și corectați erorile de ortografie și de punctuație.
• Eliminați rândurile 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 Excel:

• Cele mai eficiente zece metode de curățire a datelor
• Filtrarea pentru valori unice sau eliminarea valorilor dublate
• Conversia numerelor memorate ca text în numere
• Conversia datelor stocate ca text în date

Alegeți cel mai bun tip de date atunci când importați

În timpul operațiunii de import în Access, doriți să faceți alegeri bune, astfel încât să primiți câteva (dacă există) erori de conversie care vor necesita intervenție manuală. Următorul tabel rezumă modul în care formatele de numere Excel și tipurile de date Access sunt convertite atunci când importați date din Excel în Access și oferă câteva sfaturi privind cele mai bune tipuri de date de ales în expertul Import foaie de calcul.

Formatul numerelor 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 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ă pe baza unei proprietăți Dimensiune câmp (Byte, Integer, Întreg lung, Simplu, Dublu, Zecimal).	Alegeți La două opțiuni pentru a evita erorile de conversie a datelor.
Data	Dată	Atât Access, cât și Excel utilizează același număr serial de dată pentru a stoca datele. Î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 date 1904 (utilizat în Excel pentru Macintosh), trebuie să efectuați conversia datelor în Excel sau în Access, pentru a evita confuzia. Pentru mai multe informații, consultați Modificarea sistemului de date calendaristice, a formatului sau a interpretării anului din două cifre și Importul sau legarea la datele dintr-un registru de lucru Excel.	Alegeți Data.
Timp	Ora	Atât Access, cât și Excel stochează valori de timp utilizând același tip de date.	Alegeți Oră, care este de obicei setarea implicită.
Monedă, Contabilitate	Monedă	În Access, tipul de date Monedă stochează datele sub formă de numere de 8 byți cu o precizie de 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 efectuează 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 din Excel. Nu uitați să faceți backup mai întâi 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ăugați automat date folosind modalitatea simplă

O problemă comună pe care o au utilizatorii Excel este adăugarea de date cu aceleași coloane într-o singură 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 ajuns să includă 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ă pentru interfața de 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 cu ușurință să importați și să adăugați date într-un tabel utilizând expertul Import foaie de calcul. În plus, puteți să adăugați 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 planificate Microsoft Outlook ș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 sarcină grea. 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 de buton pentru a redenumi un tabel, a adăuga o cheie primară, a transforma o coloană existentă în cheie primară și a anula ultima acțiune

Puteți utiliza 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 între tabelele primare și chei externe.
• Adăugați o cheie primară la un câmp existent care conține valori unice sau creați un nou câmp ID 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 puteți adăuga cu ușurință ștergeri în cascadă mai târziu.
• Căutați în tabele 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.
• Creați copii 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 Analizorul de tabel.

Pasul 3: Conectarea la datele Access din Excel

După ce datele au fost normalizate în Access și s-a 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ă, astfel încât pot fi conectate la registrul de lucru printr-o conexiune de date, care este un container de informații utilizat pentru a localiza, a vă conecta și a accesa sursa externă de date. Informațiile de conexiune sunt stocate în registrul de lucru și pot fi, de asemenea, stocate într-un fișier de conexiune, cum ar fi un fișier Office Data Connection (ODC) (extensie nume de fișier .odc) sau un fișier nume sursă de date (extensia .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 oricând datele se actualizează în Access.

Pentru mai multe informații, consultați Importul de date din surse de date externe (Power Query).

Aduceți datele în Access

Această secțiune vă ajută să parcurgeți următoarele etape 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 acelor tabele din Excel în Access, crearea relațiilor 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 neatomice în coloana Vânzător și în coloana Adresă. Ambele coloane trebuie să fie scindate în două sau mai multe coloane separate. Această foaie de lucru conține și informații despre vânzători, produse, clienți și comenzi. De asemenea, aceste informații ar trebui scindate mai departe, după 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	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	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	F-198	6	$5.25	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	B-205	1	$4.50	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	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	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201
Koch, Reed	2353	3/7/09	C-789	5	$7.00	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201

Informații în părțile sale cele mai mici: date atomice

Lucrând cu datele din acest exemplu, puteți utiliza comanda Text în coloane din Excel pentru a separa părțile "atomice" ale unei celule (cum ar fi adresa poștală, localitatea, județul și codul poștal) în coloane distincte.

Următorul tabel afișează noile coloane în 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 de familie și Prenume și că informațiile din coloana Adresă au fost împărțite în coloane Adresă, Localitate, Județ și Cod poștal. Aceste date sunt în "prima formă normală".

Nume	Prenume	Adresă poștală	Localitate	Stat	Cod ZIP
Li	Yale	2302 Harvard Ave	Sinaia	SB	98227
Adams	Ellen	1025 Columbia Circle	Cluj	SB	98234
Hance	Daniel	2302 Harvard Ave	Sinaia	SB	98227
Koch	Trestie	7007 Cornell St Redmond	Redmond	SB	98199

Împărțirea datelor în subiecte organizate în Excel

Cele câteva tabele cu date exemplu care urmează afișează aceleași informații din foaia de lucru Excel după ce a fost scindată în tabele pentru vânzători, produse, clienți și comenzi. Designul mesei nu este final, dar este pe drumul cel bun.

Tabelul Vânzători conține doar 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ții de vânzări.

Agenți de vânzări
ID vânzător	Nume	Prenume
101	Li	Yale
103	Adams	Ellen
105	Hance	Daniel
107	Koch	Trestie

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ă poștală	Localitate	Stat	Cod ZIP	Telefon
1001	Contoso, Ltd.	2302 Harvard Ave	Sinaia	SB	98227	425-555-0222
1003	Adventure Works	1025 Columbia Circle	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, vânzători, clienți și produse. Rețineți că fiecare înregistrare are un ID unic (ID comandă). Unele informații 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ă numai patru coloane: ID-ul unic al comenzii, data comenzii, ID-ul agentului de vânzări și ID-ul clientului. 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 produsului și cantitatea, sunt mutate din tabelul Comenzi și stocate într-un tabel denumit Detalii comenzi. Rețineți că există 9 comenzi, deci este logic 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 comenzi.

Proiectarea finală a tabelului Comenzi trebuie 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 (mai exact, nu există o cheie primară), deci este în regulă ca una sau toate coloanele să conțină date "redundante". Totuși, două înregistrări din acest tabel nu trebuie să fie complet identice (această regulă se aplică oricărui tabel dintr-o bază de date). În acest tabel, ar trebui să existe 17 înregistrări - fiecare corespunzând unui produs, într-o comandă individuală. De exemplu, în comanda 2349, trei produse C-789 cuprind una din cele două părți ale întregii comenzi.

Prin urmare, tabelul Detalii comandă trebuie să arate astfel:

Detaliile comenzii
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 detaliile comenzilor au fost scindate în subiecte separate în Excel, puteți copia acele date 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 diferite subiecte. De exemplu, puteți crea o interogare care returnează ID-ul comenzii și numele agenților de vânzări pentru comenzile introduse între 05.03.2009 și 08.03.2009.

În plus, puteți crea formulare și rapoarte pentru a simplifica introducerea datelor și analiza vânzărilor.

Aveți nevoie de ajutor suplimentar?

Puteți oricând să întrebați un expert de la Excel Tech Community sau să obțineți asistență de la Comunități.