Importul și formarea datelor în Excel pentru Mac (Power Query)

Importul surselor de date

Puteți importa date în Excel utilizând Power Query dintr-o largă varietate de surse de date: Registru de lucru Excel, Text/CSV, XML, JSON, BAZĂ DE DATE SQL Server, Listă SharePoint Online, OData, Tabel necompletat și Interogare necompletată.

1. Selectați Data>Get Data.

   

2. Pentru a selecta sursa de date dorită, selectați Preluare date (Power Query).

3. În caseta de dialog Alegere sursă de date , selectați una dintre sursele de date disponibile.

   

4. Conectați-vă la sursa de date. Pentru a afla mai multe despre cum să vă conectați la fiecare sursă de date, consultați Importul de date din surse de date.

5. Alegeți datele pe care doriți să le importați.

6. Încărcați datele făcând clic pe butonul Încărcare .

Rezultat

Datele importate apar într-o foaie nouă.

Următorii pași

Pentru a forma și a transforma datele utilizând Editorul Power Query, selectați Transformare date. Pentru mai multe informații, consultați Formarea datelor cu Editor Power Query.

Formarea datelor cu Editorul Power Query

Procedură

1. Selectați Preluare>date (Power Query).

2. Pentru a deschide Editor Power Query, selectați Lansați Editor Power Query.

   

   Sfat

   De asemenea, puteți accesa Editor Power Query selectând Preluare date (Power Query), alegând o sursă de date, apoi făcând clic pe Următorul.

3. Formați și transformați datele utilizând Editorul Power Query așa cum ați face în Excel pentru Windows.

   

   Pentru mai multe informații, consultați Ajutorul Power Query pentru Excel.

4. Când ați terminat, selectați Pornire>,Închidere & Încărcare.

Rezultat

Datele nou importate apar într-o foaie nouă.

Reîmprospătarea surselor de date

Puteți reîmprospăta următoarele surse de date: fișiere SharePoint, liste SharePoint, foldere SharePoint, OData, fișiere text/CSV, registre de lucru Excel (.xlsx), fișiere XML și JSON, tabele și zone locale, o bază de date Microsoft SQL Server și foldere.

Reîmprospătare pentru prima dată

Prima dată când încercați să reîmprospătați sursele de date bazate pe fișiere în interogările din registrul de lucru, poate fi necesar să actualizați calea de fișier.

1. Selectați Date, săgeata de lângă Obțineți date, apoi Setări sursă de date. Apare caseta de dialog cu setările sursei de date .
2. Selectați o conexiune și apoi selectați Modificare cale fișier.
3. În caseta de dialog Cale fișier , selectați o locație nouă, apoi selectați Preluare date.
4. Selectați Închidere.

Reîmprospătarea la următoarele ore

Pentru a reîmprospăta:

• Toate sursele de date din registrul de lucru, selectațiReîmprospătare totalădate>.
• Pentru o anumită sursă de date, faceți clic dreapta pe un tabel de interogare dintr-o foaie, apoi selectați Reîmprospătare.
• Un PivotTable, selectați o celulă din raportul PivotTable, apoi selectați PivotTable Analiză>reîmprospătare date.

Introducerea și golirea acreditărilor

Prima dată când accesați SharePoint, SQL Server, OData sau alte surse de date care necesită permisiune, trebuie să furnizați acreditările corespunzătoare. De asemenea, se recomandă să ștergeți acreditările pentru a introduce unele noi.

Introduceți acreditările

Când reîmprospătați o interogare pentru prima dată, este posibil să vi se solicite să vă conectați. Selectați metoda de autentificare și specificați acreditările de conectare pentru a vă conecta la sursa de date și a continua cu reîmprospătarea.

Dacă este necesară conectarea, apare caseta de dialog Introduceți acreditările .

De exemplu:

• Acreditările SharePoint:

  

• Acreditări SQL Server:

  

Ștergeți acreditările

1. Selectați Setări>pentru sursa de datede preluare a datelor>.
2. În caseta de dialog din Setarea surseide date, selectați conexiunea dorită.
3. În partea de jos, selectați Golire permisiuni.
4. Confirmați că aceasta este ceea ce doriți să faceți, apoi selectați Ștergere.

Crearea și transferul codului VBA Power Query

Deși crearea în Editor Power Query nu este disponibilă în Excel pentru Mac, VBA acceptă crearea Power Query. Transferul unui modul de cod VBA dintr-un fișier din Excel pentru Windows în Excel pentru Mac este un proces în doi pași. Un exemplu de program vă este furnizat la sfârșitul acestei secțiuni.

Primul pas: Excel pentru Windows

1. În Excel Windows, dezvoltați interogări utilizând VBA. Codul VBA care utilizează următoarele entități din modelul de obiecte Excel funcționează și în Excel pentru Mac: obiectul Interogări, obiectul WorkbookQuery, proprietatea Workbook.Queries. Pentru mai multe informații, consultați Referințe VBA Excel.

2. În Excel, asigurați-vă că Visual Basic Editor este deschis apăsând ALT+F11.

3. Faceți clic dreapta pe modul, apoi selectați Export fișier. Apare caseta de dialog Export .

4. Introduceți un nume de fișier, asigurați-vă că extensia de fișier este .bas, apoi selectați Salvare.

5. Încărcați fișierul VBA într-un serviciu online pentru a-l face accesibil de pe Mac.

   Puteți utiliza Microsoft OneDrive. Pentru mai multe informații, consultați Sincronizarea fișierelor cu OneDrive pe Mac OS X.

Pasul doi: Excel pentru Mac

1. Descărcați fișierul VBA într-un fișier local, fișierul VBA pe care l-ați salvat în "Pasul unu: Excel pentru Windows" și l-ați încărcat într-un serviciu online.
2. În Excel pentru Mac, selectați Instrumente>Macro>Visual Basic Editor. Se afișează fereastra Visual Basic Editor .
3. Faceți clic dreapta pe un obiect din fereastra Project, apoi selectați Import fișier. Apare caseta de dialog Import fișier .
4. Găsiți fișierul VBA, apoi selectați Deschidere.

Exemplu de cod

Iată câteva coduri de bază pe care puteți să le adaptați și să le utilizați. Aceasta este un exemplu de interogare care creează o listă cu valorile de la 1 la 100.

Sub CreateSampleList()
  ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
    "let" & vbCr & vbLf & _
      "Source = {1..100}," & vbCr & vbLf & _
      "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
      "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
    "in" & vbCr & vbLf & _
      "RenamedColumns"
  ActiveWorkbook.Worksheets.Add
  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [SampleList]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "SampleList"
    .Refresh BackgroundQuery:=False
  End With
End Sub

Importul și reîmprospătarea fișierelor TXT sau CSV

1. Deschideți registrul de lucru Excel.

2. Dacă primiți un avertisment de securitate despre dezactivarea conexiunilor de date externe, selectați Activare conținut.

3. Dacă apare caseta de dialog Acordare acces la fișiere , selectați Selectare, apoi selectați Acordați acces la folderul de nivel superior care conține fișierele sursei de date.

4. Selectați date>din text (moștenit). Apare caseta de dialog Instrumentul de găsire .

5. Găsiți fișierul .txt sau .csv și selectați Deschidere.Apare Expertul import text .

   Sfat Verificați în mod repetat panoul Previzualizare a datelor selectate pentru a confirma alegerile.

6. Pe prima pagină, procedați astfel:
   Tip de fișier Pentru a alege tipul de fișier text, selectați Lățime delimitată sau fixă.

   Număr de rând În Porniți importul la, selectați un număr de rând pentru a specifica primul rând de date pe care doriți să le importați.

   Set de caractere În Originea fișierului, selectați setul de caractere utilizat în fișierul text. În majoritatea cazurilor, puteți lăsa această setare la valoarea implicită.

7. Pe pagina a doua, procedați astfel:

   Delimitat

   Dacă alegeți Delimitat pe prima pagină, sub Delimitatori, bifați caracterul delimitator sau utilizați caseta de selectare Altele pentru a introduce unul care nu este listat.

   Selectați Se tratează delimitatorii consecutivi ca unul dacă datele conțin un delimitator de mai mult de un caracter între câmpurile de date sau dacă datele conțin mai mulți delimitatori particularizați.

   În Calificator text, selectați caracterul care încadrează valorile în fișierul text, care este de cele mai multe ori caracterul ghilimele (").
   Lățime fixă

   Dacă ați ales Lățime fixă pe prima pagină, urmați instrucțiunile pentru a crea, a șterge sau a muta o linie de sfârșit în caseta Previzualizare date selectate .

8. Pe pagina a treia, procedați astfel:

   Pentru fiecare coloană de sub Previzualizarea datelor selectate, selectați-o și modificați-o la un alt format de coloană, dacă doriți. Puteți să setați în continuare formatul de dată și să selectați Complex pentru a modifica setările datelor numerice. De asemenea, puteți efectua conversia datelor după ce le importați.

   Selectați Finalizați. Apare caseta de dialog Import date .

9. Alegeți unde doriți să fie adăugate datele: pe foaia existentă, pe o foaie nouă sau într-un raport PivotTable.

10. Selectați OK.
    Pentru a vă asigura că funcționează conexiunea, introduceți câteva date, apoi selectațiReîmprospătareconexiuni>.

Conectarea la o Bază de date SQL (ODBC)

1. Selectați date>din SQL Server ODBC. Apare caseta de dialog Conectare la sursa de date ODBC SQL Server.

   

2. Introduceți serverul în caseta Nume server și, opțional, introduceți baza de date în caseta Nume bază de date .

   Obțineți aceste informații de la administratorul bazei de date.

3. Sub Autentificare, selectați o metodă din listă: Nume de utilizator/Parolă, Kerberos sau NTLM.

4. Introduceți acreditările în casetele Nume utilizator și Parola .

5. Selectați Conectare. Apare caseta de dialog Navigator .

6. În panoul din stânga, navigați la tabelul dorit și selectați-l.

7. Confirmați instrucțiunea SQL din panoul din dreapta. Puteți modifica instrucțiunea SQL după cum considerați necesar.

8. Pentru a previzualiza datele, selectați Rulare.

9. Când sunteți gata, selectați Date returnate. Apare caseta de dialog Import date .

   

10. Alegeți unde doriți să fie adăugate datele: pe foaia existentă, pe o foaie nouă sau într-un raport PivotTable.

11. Pentru a seta proprietățile de conexiune pe filele Utilizare și Definiție din caseta de dialog Proprietăți , selectați Proprietăți. După ce importați datele, puteți selecta, de asemenea,Conexiuni de date>, apoi, în caseta de dialog Proprietăți conexiune, selectați Proprietăți.

12. Selectați OK.

13. Pentru a vă asigura că funcționează conexiunea, introduceți câteva date, apoi selectațiReîmprospătare totalădate>.

Conectarea la altă bază de date (Microsoft Query)

Dacă doriți să utilizați o sursă externă care nu este o Bază de date SQL (de exemplu, FileMaker Pro), puteți utiliza un driver Open Database Connectivity (ODBC) instalat pe Mac. Informații despre drivere sunt disponibile pe această pagină web. După instalarea driverului pentru sursa de date, urmați acești pași:

1. Selectați date>din baza de date (Microsoft Query).

2. Adăugați sursa de date pentru baza de date, apoi selectați OK.

3. La solicitarea de acreditări SQL Server, introduceți metoda de autentificare, numele de utilizator și parola.

   

4. În partea stângă, selectați săgeata de lângă server pentru a vedea bazele de date.

5. Selectați săgeata de lângă baza de date dorită.

6. Selectați tabelul dorit.

7. Pentru a previzualiza datele, selectați Rulare.

8. Când sunteți gata, selectați Date returnate.

9. În caseta de dialog Import date , alegeți unde doriți să se afle datele: pe foaia existentă, pe o foaie nouă sau într-un PivotTable.

10. Selectați OK.

11. Pentru a vă asigura că funcționează conexiunea, introduceți câteva date, apoi selectațiReîmprospătare totalădate>.

Ștergeți acreditările de conectare

Dacă permisiunile nu funcționează, este recomandat să nu le goliți mai întâi, apoi să vă conectați.

1. SelectațiConexiuni dedate>. Apare caseta de dialog Conexiuni registru de lucru .

2. Selectați conexiunea dorită din listă, apoi selectați Golire permisiuni.