Cum se parcursează datele prin Excel

Iată care sunt principalele lucruri pe care le puteți face în timp ce datele se află în stația de tren Excel:

• Import    Puteți importa date din mai multe surse de date externe diferite. Aceste surse de date pot fi pe computerul dvs., în cloud sau în jumătate din lume. Pentru mai multe informații, consultați Importul de date 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 datele într-o varietate de 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 crea un tip de date care să completeze tipurile de date legate. Pentru mai multe informații, consultați Ajutor Power Query pentru Excel.

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

• Reîmprospătare    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/Properties    Fiecare sursă de date externă are o conexiune și informații de proprietate asociate, care uneori necesită modificări în funcție de circumstanțele dvs. Pentru mai multe informații, consultați Gestionareazonelor de date externe și a proprietăților acestora, Crearea, editareași gestionarea conexiunilor la date externe și Proprietăți conexiune.

• Moștenit    Metodele tradiționale, cum ar fi experții import moștenit și MSQuery, sunt disponibile în continuare pentru utilizare. Pentru mai multe informații, consultați Opțiuni de import și analiză a datelor și Utilizarea Microsoft Query pentru a regăsi date externe.

Secțiunile următoare furnizează mai multe detalii despre ce se întâmplă în culisele acestei gări aglomerate Excel.

Există proprietăți pentru o conexiune, o interogare și o 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 intervalului 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 acreditări asociate și reîmprospătează informațiile utilizează caseta de dialog Mai mare Proprietăți date interval extern.

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

Dialog Box or Pane Command Paths	Tabulatori și locutorii	Subiect de Ajutor principal
Surse recente Data > Recent Sources	(Fără file) Loc pentru conectarea>dialog Navigator	Gestionarea setărilor și permisiunilor sursei de date
Proprietăți conexiune OR Data Connection Wizard Interogări > date & Connections > Connections (conexiuni) sau > clic dreapta pe o conexiune) > Properties	Fila Utilizare, Fila Definiție utilizată în fila	Proprietăți conexiune
Proprietăți interogare Data> Existing Connections > (right click a connection) > Edit Connection Properties SAU interogări> date & de| Fila Interogări din > (faceți clic dreapta pe o conexiune) > Proprietăți SAU Query> Properties SAU data>Refresh All>Connections (when positioned on a loaded query worksheet)	Fila Utilizare, Fila Definiție utilizată în fila	Proprietăți conexiune
Interogările & Conexiuni Interogările > de & Conexiunile	Fila Interogări, fila Conexiuni	Proprietăți conexiune
Conexiuni existente Data > Existing Connections	Connections tab Tables tab	Conectarea la date externe
Proprietăți date externe SAU Proprietățile zonelor de date externe OR Data> Properties (Disabled if not positioned on a query worksheet)	Utilizate în filă (din caseta de dialog Proprietăți conexiune)   Butonul Reîmprospătare din butoanele din dreapta pentru proprietățile de interogare	Gestionarea zonelor de date externe și a proprietăților lor
Connection Properties > Definition tab > Export Connection File OR Query> Export Connection File	(Fără file) Locatorii în caseta de dialog Fișier - folderul Surse de date	Crearea, editarea și gestionarea conexiunilor la date externe

Datele dintr-un registru de lucru Excel pot veni din două locații diferite. Datele pot fi stocate direct în registrul de lucru sau pot fi stocate într-o sursă de date externă, 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, o operațiune care poate consuma mult timp și 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ă, oricâ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 Office Data Connection (ODC) (.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 modul 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: Analysis Services, 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 și 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 Conexiuni existente. (Selectați Date > Conexiuni existente.) Utilizând această casetă de dialog, puteți vedea următoarele tipuri de conexiuni:

• Conexiunile din 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 din Expert conexiune de date sau din conexiuni 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 Surse de date personale 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 dintr-o bibliotecă SharePoint. 

De asemenea, puteți utiliza Excel ca editor de fișier de conexiune pentru a crea și a edita conexiunile 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 pe care o doriți, 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 >date & Conexiuni > fila Conexiuni > (faceți clic dreapta pe o conexiune) > Proprietăți) pentru a controla diverse setări pentru conexiuni la surse de date externe și a utiliza, a reutiliza sau a comuta fișierele de conexiune.

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

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 original de conexiune de date care s-a utilizat pentru a crea conexiunea (indicată de numele de fișier afișat î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 de conexiune este eliminată.

Pentru a vă asigura că fișierul de conexiune este utilizat întotdeauna atunci când se reîmprospătează o sursă de date, faceți clic pe Se încearcă întotdeauna să utilizați acest 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ă această proprietate setată.

Utilizând caseta de dialog Conexiuni, puteți gestiona cu ușurință aceste conexiuni, inclusiv crearea, editarea și ștergerea lor (Selectare interogări > Interogări & Conexiuni > Fila Conexiuni > (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 utilizate în registrul de lucru.

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

• Arată unde se utilizează fiecare conexiune în registrul de lucru curent.

• Diagnosticarea unui mesaj de eroare despre conexiunile la date externe.

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

• Faceți mai simplă crearea și partajarea fișierelor de conexiune cu utilizatorii.

Fișierele de conexiune sunt deosebit de utile pentru partajarea conexiunilor în mod constant, făcând conexiunile mai detectabile, ajutând la îmbunătățirea securității conexiunilor și facilitând administrarea sursei de date. Cea mai bună metodă de a partaja fișiere de conexiune este să le puneți într-o locație sigură și de încredere, cum ar fi un folder de rețea sau o bibliotecă SharePoint, în care 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ă instaleze 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 de pe computer.

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) la un fișier ODC, deschizând fișierul de conexiune și făcând clic pe butonul Export fișier conexiune de pe fila Definiție a casetei de dialog Proprietăți conexiune.

Utilizarea fișierelor interogare

Fișierele 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 un mod tradițional de partajare a interogărilor cu alți utilizatori Excel.

Utilizarea fișierelor de interogare .dqy    Aveți posibilitatea să utilizați Microsoft Query pentru a salva fișierele .dqy care conțin interogări pentru date din baze de date relaționale sau fișiere text. Când deschideți aceste fișiere în Microsoft Query, aveți posibilitatea 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-o fișier cub offline (.cub). Când utilizați Expertul conexiune multidimensională în 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 acceptă driverele sursei de date OLE DB care utilizează acest format. Pentru mai multe informații, consultați documentația pentru driverul dvs.

Utilizarea fișierelor de interogare .qry    Microsoft Query poate să deschidă și să salveze fișierele de interogare în format .qry pentru a le utiliza cu versiunile 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 Ajutor 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 (denumită ș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ă excepție este un raport PivotTable conectat la o sursă de date, care nu creează o zonă de date externe. În Excel, aveți posibilitatea să formatați și să stabiliți o zonă de date externe sau să o utilizați în calcule, la fel ca în cazul altor date.

Excel de nume automat o zonă de date externe, după cum urmează:

• Intervalele de date externe din fișierele de conexiune de date Office (ODC) au același nume ca numele fișierului.

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

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

• Intervalele de date externe din interogările web sunt denumite cu numele paginii web din care s-au regăsit datele.

Dacă foaia de lucru are mai multe zone de date externe din aceeași sursă, intervalele sunt numerotate. De exemplu, Textul Meu, MyText_1, MyText_2 etc.

O zonă de date externe are proprietăți suplimentare (a nu se confunda cu proprietățile de conexiune) pe care le puteți utiliza pentru a controla datele, cum ar fi păstrarea formatării celulei și lățimea coloanei. Puteți modifica aceste proprietăți ale intervalului de date externe făcând clic pe Proprietăți în grupul Conexiuni din fila Date, apoi făcând modificările în casetele de dialog Proprietăți zonă 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. La fel 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ări de autentificare Excel Services.

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

Excel date obiect	Creează Extern date zonă?		OLE DB	ODBC	Text fișier	HTML fișier	XML fișier	SharePoint listă
Expertul 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
Tabel Excel	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

Soluție Excel Services pentru tabelele Excel și intervalele denumite

Dacă doriți să afișați un registru de lucru Excel în Excel Services, puteți să vă conectați la date și să le reîmprospătați, 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 o interogare Microsoft.

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

Să facem o excursie cu banda de memorie a bazei de date.

Despre MDAC, OLE DB și OBC

În primul rând, ne cerem scuze pentru toate acronimele. Microsoft Data Access Components (MDAC) 2.8 este inclus î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 driverele Open Database Connectivity (ODBC) sau furnizorii OLE DB, care sunt fie construiți și livrați de Microsoft, fie dezvoltați de diverși terți. Când instalați Microsoft Office, pe computer sunt adăugate 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.

Puteți utiliza, de asemenea, driverele ODBC și furnizorii OLE DB de la alți producători pentru a obține informații din alte surse, în afară de sursele de date Microsoft, inclusiv alte tipuri de baze de date ODBC și OLE DB. Pentru informații despre 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 conectarea 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 vă 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, faceți următoarele:

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

2. Definiți un nume de sursă de date (DSN) utilizând fie Administrator sursă de date ODBC pentru a stoca informațiile de conexiune din registry sau un fișier DSN, fie un șir de conectare în 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 pe Panou de control. Faceți clic pe Sistem șiîntreținere , apoi faceți clic 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 computer

Sursele de date de computer stochează informațiile de conexiune din registry, pe un anumit computer, cu un nume definit de utilizator. Puteți utiliza sursele de date de computer numai pe computerul pe care sunt definite. Există două tipuri de surse de date de computer: utilizator și 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 pentru toți utilizatorii computerului.

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

Surse de date de fișier

Sursele de date de fișier (numite și fișiere DSN) stochează informațiile de conexiune într-un fișier text, nu în registry și sunt, în general, mai flexibile de utilizat decât sursele de date de computer. De exemplu, puteți copia o sursă de date de fișier pe orice computer cu driverul ODBC corect, astfel încât aplicația dvs. să se poată baza pe informații de conexiune unitare ș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ă mențineț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 computer 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 pe care un consumator de date le utilizează pentru a accesa o sursă de date prin intermediul furnizorului OLE DB al sursei de date. Puteți să creați informațiile de conexiune, alegeți una dintre următoarele variante:

• Î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 extensia nume de fișier .udl, apoi editați fișierul, care afișează caseta de dialog Proprietăți legătură de date.