Cum trec datele prin Excel

Iată principalele lucruri pe care le puteți face în timp ce datele se află în gara Excel:

• Importa    Puteți importa date din mai multe surse de date externe diferite. Aceste surse de date pot fi pe computerul dvs., în cloud sau la jumătatea drumului în întreaga lume. Pentru mai multe informații, consultați Importul datelor din surse de date externe.

• Power Query    Puteți utiliza Power Query (denumit anterior Obțineți & Transformare) pentru a crea interogări pentru a forma, a transforma și a combina date în diverse moduri. Vă puteți exporta lucrul ca șablon Power Query pentru a defini o operațiune de flux de date în Power Apps. Puteți chiar să creați un tip de date pentru a suplimenta tipurile de date legate. Pentru mai multe informații, consultați Power Query pentru Ajutor Excel.

• Securitate    Confidențialitatea datelor, acreditările și autentificarea sunt întotdeauna o preocupare continuă. Pentru mai multe informații, consultați Gestionarea setărilor și permisiunilor sursei de date și Setarea nivelurilor de confidențialitate.

• Împrospăta    Datele importate necesită de obicei o operațiune de reîmprospătare pentru a aduce modificări, cum ar fi adăugiri, actualizări și ștergeri, în Excel. Pentru mai multe informații, consultați Reîmprospătarea unei conexiuni de date externe în Excel.

• Connections/Proprietăți    Fiecare sursă de date externă are asociate informații despre conexiune și proprietate asortate care necesită uneori modificări, în funcție de circumstanțele dvs. Pentru mai multe informații, consultați Gestionarea zonelor de date externe și a proprietăților acestora, Crearea, editarea și gestionarea conexiunilor la date externe și Proprietăți conexiune.

• Moștenire    Metodele tradiționale, cum ar fi Legacy Import Wizards și MSQuery, sunt încă disponibile pentru utilizare. Pentru mai multe informații, consultați Opțiuni de import și analiză a datelor și Utilizați Microsoft Query pentru a regăsi date externe.

Secțiunile următoare oferă mai multe detalii despre ceea ce se întâmplă în culise în această gară ocupată, Excel.

Există proprietăți de conexiune, interogare și zonă de date externe. Atât proprietățile de conexiune, cât și cele de interogare conțin informații de conexiune tradiționale. Într-un titlu de casetă de dialog, Proprietăți conexiune înseamnă că nu există nicio interogare asociată cu aceasta, dar Proprietăți interogare înseamnă că există. Proprietățile zonei de date externe controlează aspectul și formatul datelor. Toate sursele de date au o casetă de dialog Proprietăți date externe , dar sursele de date care au informații asociate despre acreditări și reîmprospătare utilizează caseta de dialog Proprietăți date zonă externă mai mare.

Următoarele informații rezumă cele mai importante casete de dialog, panouri, căi de comandă și subiectele de ajutor corespunzătoare.

Casetă de dialog sau panou Căi de comandă	Tabulatori și tunele	Subiect principal de Ajutor
Surse recente Date > surse recente	(Fără file) Tunnels to Connect > Navigator dialog box	Gestionarea setărilor și permisiunilor sursei de date
Proprietăți conexiune OR Expert conexiune de date Data > Queries & Connections > Connections tab > (faceți clic dreapta pe o conexiune) > Properties	Fila Utilizare , fila Definiție utilizată în	Proprietăți conexiune
Proprietăți interogare Data > Existing Connections > (faceți clic dreapta pe o conexiune) > Edit Connection Properties OR Interogări de>de date & conexiuneas | Fila Interogări > (faceți clic dreapta pe o conexiune) > Proprietăți PROPRIETĂȚI >interogare OR OR Data > Refresh All > Connections (when positioned on a loaded query worksheet)	Fila Utilizare , fila Definiție utilizată în	Proprietăți conexiune
Interogări & Connections & Connections Interogări de> de date	Fila Interogări Connections  tab	Proprietăți conexiune
Connections existent Connections de date > existente	fila Connectionstabele	Conectarea la date externe
Proprietăți date externe OR Proprietăți zonă de date externe OR Data > Properties (Disabled if not positioned on a query worksheet)	Utilizat în fila (din caseta de dialog Proprietăți conexiune )   Refresh button on the right tunnels to Query Properties	Gestionarea zonelor de date externe și a proprietăților lor
Connection Properties > Definition tab > Export Connection File OR Interogare > Export fișier conexiune	(Fără file) Tunnels to File dialog box Data sources folder	Crearea, editarea și gestionarea conexiunilor la date externe

Datele dintr-un registru de lucru Excel pot proveni din două locații diferite. Datele pot fi stocate direct în registrul de lucru sau pot fi stocate într-o sursă externă de date, cum ar fi un fișier text, o bază de date sau un cub OLAP (Online Analytical Processing). Această sursă de date externă este conectată la registrul de lucru printr-o conexiune de date, care este un set de informații care descrie cum să găsiți, să vă conectați și să accesați sursa de date externă.

Principalul avantaj al conectării la date externe este că puteți analiza periodic aceste date fără a copia în mod repetat datele în registrul de lucru, care este o operațiune care poate consuma mult timp și care este predispusă la erori. După conectarea la date externe, puteți, de asemenea, să reîmprospătați (sau să actualizați) automat registrele de lucru Excel din sursa de date originală de fiecare dată când sursa de date este actualizată cu informații noi.

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) sau un fișier nume sursă de date (.dsn).

Pentru a aduce date externe în Excel, aveți nevoie de acces la date. Dacă sursa de date externă pe care doriți să o accesați nu se află pe computerul local, poate fi necesar să contactați administratorul bazei de date pentru o parolă, permisiuni de utilizator sau alte informații de conexiune. Dacă sursa de date este o bază de date, asigurați-vă că baza de date nu este deschisă în mod exclusiv. Dacă sursa de date este un fișier text sau o foaie de calcul, asigurați-vă că un alt utilizator nu o are deschisă pentru acces exclusiv.

Multe surse de date necesită, de asemenea, un driver ODBC sau un furnizor OLE DB pentru a coordona fluxul de date între Excel, fișierul de conexiune și sursa de date.

Diagrama următoare rezumă punctele cheie despre conexiunile de date.

1. Există o varietate de surse de date la care vă puteți conecta: Servicii de analiză, SQL Server, Microsoft Access, alte baze de date OLAP și relaționale, foi de calcul și fișiere text.

2. Multe surse de date au un driver ODBC sau un furnizor OLE DB asociat.

3. Un fișier de conexiune definește toate informațiile necesare pentru a accesa și a regăsi date dintr-o sursă de date.

4. Informațiile de conexiune sunt copiate dintr-un fișier de conexiune într-un registru de lucru, iar informațiile de conexiune pot fi editate cu ușurință.

5. Datele sunt copiate într-un registru de lucru, astfel încât să le puteți utiliza la fel cum utilizați datele stocate direct în registrul de lucru.

Pentru a găsi fișierele de conexiune, utilizați caseta de dialog Connections existent. (Selectați Date > Connections existent.) Utilizând această casetă de dialog, puteți vedea următoarele tipuri de conexiuni:

• Connections în registrul de lucru    

  Această listă afișează toate conexiunile curente din registrul de lucru. Lista este creată din conexiuni pe care le-ați definit deja, pe care le-ați creat utilizând caseta de dialog Selectare sursă de date a Expertului conexiune de date sau din conexiunile pe care le-ați selectat anterior ca conexiune din această casetă de dialog.

• Fișierele de conexiune de pe computer    

  Această listă este creată din folderul Sursele mele de date , care este stocat de obicei în folderul Documente.

• Fișiere de conexiune în rețea    

  Această listă poate fi creată dintr-un set de foldere din rețeaua locală, a căror locație poate fi implementată în rețea ca parte a implementării politicilor de grup Microsoft Office sau a unei biblioteci SharePoint. 

De asemenea, puteți utiliza Excel ca editor de fișiere de conexiune pentru a crea și a edita conexiuni la surse de date externe care sunt stocate într-un registru de lucru sau într-un fișier de conexiune. Dacă nu găsiți conexiunea dorită, puteți crea o conexiune făcând clic pe Răsfoire pentru mai multe pentru a afișa caseta de dialog Selectare sursă de date , apoi făcând clic pe Sursă nouă pentru a porni Expertul conexiune de date.

După ce creați conexiunea, puteți utiliza caseta de dialog Proprietăți conexiune (Selectare interogări de > de date& Connections> fila Connections > (faceți clic dreapta pe o conexiune) > Proprietăți) pentru a controla diverse setări pentru conexiunile la sursele de date externe și pentru a utiliza, a reutiliza sau a comuta fișierele de conexiune.

Notă    Uneori, caseta de dialog Proprietăți conexiune este denumită caseta de dialog Proprietăți interogare atunci când există o interogare creată în Power Query (denumită anterior Obțineți & Transformare) asociată cu aceasta.

Dacă utilizați un fișier de conexiune pentru a vă conecta la o sursă de date, Excel copiază informațiile de conexiune din fișierul de conexiune în registrul de lucru Excel. Când efectuați modificări utilizând caseta de dialog Proprietăți conexiune , editați informațiile despre conexiunea de date stocate în registrul de lucru Excel curent și nu fișierul de conexiune de date original care este posibil să fi fost utilizat pentru a crea conexiunea (indicat de numele de fișier care se afișează în proprietatea Fișier conexiune de pe fila Definiție ). După ce editați informațiile de conexiune (cu excepția proprietăților Nume conexiune și Descriere conexiune ), linkul la fișierul de conexiune este eliminat și proprietatea Fișier conexiune este debifată.

Pentru a vă asigura că fișierul de conexiune este utilizat întotdeauna atunci când o sursă de date este reîmprospătată, faceți clic pe Se încearcă întotdeauna utilizarea acestui fișier pentru a reîmprospăta aceste date pe fila Definiție . Bifarea acestei casete de selectare asigură că actualizările fișierului de conexiune vor fi utilizate întotdeauna de toate registrele de lucru care utilizează acel fișier de conexiune, care trebuie să aibă și această proprietate setată.

Utilizând caseta de dialog Connections, puteți gestiona cu ușurință aceste conexiuni, inclusiv crearea, editarea și ștergerea acestora (Selectați interogările de> de date & Connections> Connections filă > (faceți clic dreapta pe o conexiune) > Proprietăți.) Puteți utiliza această casetă de dialog pentru a face următoarele: 

• Creați, editați, reîmprospătați și ștergeți conexiunile care se utilizează în registrul de lucru.

• Verificați sursa de date externe. Se recomandă să faceți acest lucru în cazul în care conexiunea a fost definită de alt utilizator.

• Afișați unde se utilizează fiecare conexiune în registrul de lucru curent.

• Diagnosticați un mesaj de eroare despre conexiunile la date externe.

• Redirecționați o conexiune la alt server sau la altă sursă de date sau înlocuiți fișierul de conexiune pentru o conexiune existentă.

• Facilitați crearea și partajarea fișierelor de conexiune cu utilizatorii.

Fișierele de conexiune sunt utile în special pentru partajarea conexiunilor în mod unitar, făcând conexiunile mai ușor de descoperit, ajutând la îmbunătățirea securității conexiunilor și facilitând administrarea surselor de date. Cea mai bună modalitate de a partaja fișiere de conexiune este să le amplasați într-o locație sigură și de încredere, cum ar fi un folder de rețea sau o bibliotecă SharePoint, unde utilizatorii pot citi fișierul, dar numai utilizatorii desemnați pot modifica fișierul. Pentru mai multe informații, consultați Partajarea datelor cu ODC.

Utilizarea fișierelor ODC

Puteți crea fișiere conexiune de date Office (.odc) conectându-vă la date externe prin caseta de dialog Selectare sursă de date sau utilizând Expertul conexiune de date pentru a vă conecta la surse de date noi. Un fișier ODC utilizează etichete HTML și XML particularizate pentru a stoca informațiile de conexiune. Puteți să vizualizați sau să editați cu ușurință conținutul fișierului în Excel.

Puteți partaja fișiere de conexiune cu alte persoane pentru a le oferi același acces pe care îl aveți la o sursă de date externă. Alți utilizatori nu trebuie să configureze o sursă de date pentru a deschide fișierul de conexiune, dar poate fi necesar să instaleze driverul ODBC sau furnizorul OLE DB necesar pentru a accesa datele externe pe computerul lor.

Fișierele ODC sunt metoda recomandată pentru conectarea la date și partajarea datelor. Puteți efectua cu ușurință conversia altor fișiere de conexiune tradiționale (DSN, UDL și fișiere de interogare) într-un fișier ODC, deschizând fișierul de conexiune, apoi făcând clic pe butonul Export fișier conexiune din fila Definiție a casetei de dialog Proprietăți conexiune .

Utilizarea fișierelor de interogare

Fișierele de interogare sunt fișiere text care conțin informații despre sursa de date, inclusiv numele serverului unde se află datele și informațiile de conexiune pe care le furnizați atunci când creați o sursă de date. Fișierele de interogare sunt o modalitate tradițională de partajare a interogărilor cu alți utilizatori Excel.

Utilizarea fișierelor de interogare .dqy    Puteți utiliza Microsoft Query pentru a salva fișiere .dqy care conțin interogări de date din baze de date relaționale sau fișiere text. Când deschideți aceste fișiere în Microsoft Query, puteți să vizualizați datele returnate de interogare și să modificați interogarea pentru a regăsi rezultate diferite. Puteți salva un fișier .dqy pentru orice interogare pe care o creați, fie utilizând Expertul interogare, fie direct în Microsoft Query.

Utilizarea fișierelor de interogare .oqy    Puteți salva fișiere .oqy pentru a vă conecta la datele dintr-o bază de date OLAP, fie pe un server, fie într-un fișier cub offline (.cub). Atunci când utilizați Expertul conexiune multidimensională din Microsoft Query pentru a crea o sursă de date pentru o bază de date sau un cub OLAP, se creează automat un fișier .oqy. Deoarece bazele de date OLAP nu sunt organizate în înregistrări sau tabele, nu puteți crea interogări sau fișiere .dqy pentru a accesa aceste baze de date.

Utilizarea fișierelor de interogare .rqy    Excel poate deschide fișiere de interogare în format .rqy pentru a accepta driverele sursei de date OLE DB care utilizează acest format. Pentru mai multe informații, consultați documentația pentru driver.

Utilizarea fișierelor de interogare .qry    Microsoft Query poate să deschidă și să salveze fișierele de interogare în format .qry pentru a fi utilizate cu versiuni anterioare de Microsoft Query care nu pot deschide fișiere .dqy. Dacă aveți un fișier de interogare în format .qry pe care doriți să-l utilizați în Excel, deschideți fișierul în Microsoft Query, apoi salvați-l ca fișier .dqy. Pentru informații despre salvarea fișierelor .dqy, consultați Ajutorul Microsoft Query.

Utilizarea fișierelor de interogare Web .iqy    Excel poate deschide fișiere de interogare Web .iqy pentru a regăsi date de pe Web. Pentru mai multe informații, consultați Exportul în Excel din SharePoint.

O zonă de date externe (numită și tabel de interogare) este un nume definit sau un nume de tabel care definește locația datelor aduse într-o foaie de lucru. Atunci când vă conectați la date externe, Excel creează automat o zonă de date externe. Singura excepție de la această regulă este un raport PivotTable conectat la o sursă de date, care nu creează o zonă de date externă. În Excel, puteți să formatați și să aranjați o zonă de date externe sau să o utilizați în calcule, la fel ca în cazul altor date.

Excel denumește automat o zonă de date externe după cum urmează:

• Zonele de date externe din fișierele Conexiune de date Office (ODC) primesc același nume ca numele de fișier.

• Zonele de date externe din bazele de date sunt denumite cu numele interogării. În mod implicit, Query_from_sursă este numele sursei de date pe care ați utilizat-o pentru a crea interogarea.

• Zonele de date externe din fișierele text sunt denumite cu numele fișierului text.

• Zonele de date externe din interogările Web sunt denumite cu numele paginii Web de la care au fost regăsite datele.

Dacă foaia de lucru are mai multe zone de date externe din aceeași sursă, zonele sunt numerotate. De exemplu, TextMelem, MyText_1, MyText_2 și așa mai departe.

O zonă de date externe are proprietăți suplimentare (a nu se confunda cu proprietățile conexiunii) pe care le puteți utiliza pentru a controla datele, cum ar fi păstrarea formatării celulelor și lățimea coloanei. Puteți modifica aceste proprietăți de zonă de date externe făcând clic pe Proprietăți în grupul Connections de pe fila Date, apoi efectuând modificările în casetele de dialog Proprietăți zonă de date externe sau Proprietăți date externe.

Există mai multe obiecte de date (cum ar fi o zonă de date externe și un raport PivotTable) pe care le puteți utiliza pentru a vă conecta la surse de date diferite. Cu toate acestea, tipul de sursă de date la care vă puteți conecta este diferit între fiecare obiect de date.

Puteți să utilizați și să reîmprospătați datele conectate în Excel Services. Ca în cazul oricărei surse de date externe, poate fi necesar să vă autentificați accesul. Pentru mai multe informații, consultați Reîmprospătarea unei conexiuni de date externe în Excel. Fsau mai multe informații despre acreditări, consultați Setările de autentificare Excel Services.

Următorul tabel rezumă sursele de date acceptate pentru fiecare obiect de date din Excel.

Excela date obiect	Creează Extern date Gama?		OLE DB	ODBC	Text fișier	COD HTML fișier	COD XML fișier	SharePoint listă
Expert Import text	Da		Nu	Nu	Da	Nu	Nu	Nu
Raport PivotTable (non-OLAP)	Nu		Da	Da	Da	Nu	Nu	Da
Raport PivotTable (OLAP)	Nu		Da	Nu	Nu	Nu	Nu	Nu
Excel Table	Da		Da	Da	Nu	Nu	Da	Da
Asociere XML	Da		Nu	Nu	Nu	Nu	Da	Nu
Interogare Web	Da		Nu	Nu	Nu	Da	Da	Nu
Expert conexiune de date	Da		Da	Da	Da	Da	Da	Da
Microsoft Query	Da		Nu	Da	Da	Nu	Nu	Nu

Excel Services soluție pentru tabele și zone denumite Excel

Dacă doriți să afișați un registru de lucru Excel în Excel Services, puteți să vă conectați și să reîmprospătați datele, dar trebuie să utilizați un raport PivotTable. Excel Services nu acceptă zone de date externe, ceea ce înseamnă că Excel Services nu acceptă un tabel Excel conectat la o sursă de date, la o interogare web, la o asociere XML sau la Microsoft Query.

Totuși, puteți evita această limitare utilizând un PivotTable pentru a vă conecta la sursa de date, apoi proiectați și aranjați raportul PivotTable ca tabel bidimensional fără niveluri, grupuri sau subtotaluri, astfel încât să se afișeze toate valorile dorite pentru rânduri și coloane. 

Să mergem într-o excursie pe banda memoriei bazei de date.

Despre MDAC, OLE DB și OBC

În primul rând, scuze pentru toate acronimele. Componentele Microsoft Data Access (MDAC) 2.8 sunt incluse în Microsoft Windows. Cu MDAC, puteți să vă conectați și să utilizați date dintr-o largă varietate de surse de date relaționale și nerelaționale. Vă puteți conecta la mai multe surse de date diferite utilizând drivere Open Database Connectivity (ODBC) sau furnizori OLE DB, care sunt fie construiți și livrați de Microsoft, fie dezvoltați de diverși terți. Atunci când instalați Microsoft Office, sunt adăugate la computer drivere ODBC suplimentare și furnizori OLE DB suplimentari.

Pentru a vedea o listă completă de furnizori OLE DB instalați pe computer, afișați caseta de dialog Proprietăți legătură de date dintr-un fișier legătură de date, apoi faceți clic pe fila Furnizor .

Pentru a vedea o listă completă de furnizori ODBC instalați pe computer, afișați caseta de dialog Administrator bază de date ODBC , apoi faceți clic pe fila Drivere .

De asemenea, puteți utiliza drivere ODBC și furnizori OLE DB de la alți producători pentru a obține informații de la alte surse decât sursele de date Microsoft, inclusiv alte tipuri de baze de date ODBC și OLE DB. Pentru informații cu privire la instalarea acestor drivere ODBC sau furnizori OLE DB, consultați documentația pentru baza de date sau contactați distribuitorul bazei de date.

Utilizarea ODBC pentru a vă conecta la surse de date

În arhitectura ODBC, o aplicație (cum ar fi Excel) se conectează la Managerul de drivere ODBC, care la rândul său utilizează un anumit driver ODBC (cum ar fi driverul Microsoft SQL ODBC) pentru a se conecta la o sursă de date (cum ar fi o bază de date Microsoft SQL Server).

Pentru a vă conecta la surse de date ODBC, procedați astfel:

1. Asigurați-vă că driverul ODBC corespunzător este instalat pe computerul care conține sursa de date.

2. Definiți un nume de sursă de date (DSN) utilizând fie Administratorul sursei de date ODBC pentru a stoca informațiile de conexiune din registry sau un fișier DSN, fie un șir de conectare din codul Microsoft Visual Basic pentru a transmite informațiile de conexiune direct la Managerul de drivere ODBC.

   Pentru a defini o sursă de date, în Windows faceți clic pe butonul Start, apoi faceți clic pe Panou de control. Faceți clic pe Sistem și întreținere, apoi pe Instrumente de administrare. Faceți clic pe Performanță și întreținere, faceți clic pe Instrumente de administrare. apoi faceți clic pe Surse de date (ODBC)." Pentru mai multe informații despre diferitele opțiuni, faceți clic pe butonul Ajutor din fiecare casetă de dialog.

Surse de date de dispozitiv

Sursele de date de dispozitiv stochează informațiile de conexiune în registry, pe un anumit computer, cu un nume definit de utilizator. Puteți utiliza sursele de date de dispozitiv numai pe computerul pe care sunt definite. Există două tipuri de surse de date de dispozitiv: de utilizator și de sistem. Sursele de date de utilizator pot fi utilizate numai de către utilizatorul curent și sunt vizibile doar pentru acel utilizator. Sursele de date de sistem pot fi utilizate de toți utilizatorii unui computer și sunt vizibile tuturor utilizatorilor de pe computer.

O sursă de date de computer este utilă mai ales atunci când doriți să oferiți securitate suplimentară, deoarece vă ajută să vă asigurați că numai utilizatorii conectați pot vizualiza o sursă de date de computer, iar o sursă de date de computer nu poate fi copiată de un utilizator de la distanță pe alt computer.

Surse de date de fișier

Sursele de date de fișier (denumite și fișiere DSN) stochează informațiile de conexiune într-un fișier text, nu în registry și sunt, în general, mai flexibile decât sursele de date de dispozitiv. De exemplu, puteți copia o sursă de date de fișier pe orice computer cu driverul ODBC corect, astfel încât aplicația să se poată baza pe informații de conexiune consistente și precise pe toate computerele pe care le utilizează. Sau puteți să plasați sursa de date a fișierului pe un singur server, să o partajați între mai multe computere din rețea și să păstrați cu ușurință informațiile de conexiune într-o singură locație.

O sursă de date de fișier poate fi și nepartajabilă. O sursă de date de fișier nepartajabilă se află pe un singur computer și indică spre o sursă de date de computer. Puteți utiliza surse de date de fișier nepartajabile pentru a accesa surse de date de dispozitiv existente din sursele de date de fișier.

Utilizarea OLE DB pentru conectarea la surse de date

În arhitectura OLE DB, aplicația care accesează datele se numește consumator de date (cum ar fi Excel), iar programul care permite accesul nativ la date se numește furnizor de baze de date (cum ar fi Microsoft OLE DB Provider pentru SQL Server).

Un fișier Universal Data Link (.udl) conține informațiile de conexiune utilizate de un consumator de date pentru a accesa o sursă de date prin intermediul furnizorului OLE DB al acelei surse de date. Puteți crea informațiile de conexiune efectuând una dintre următoarele acțiuni:

• În Expert conexiune de date, utilizați caseta de dialog Proprietăți legătură de date pentru a defini o legătură de date pentru un furnizor OLE DB. 

• Creați un fișier text necompletat cu o extensie de nume de fișier .udl, apoi editați fișierul, care afișează caseta de dialog Proprietăți link de date .