Importowanie i kształtowanie danych w programie Excel dla komputerów Mac (Power Query)

Importuj źródła danych

Dane można importować do programu Excel przy użyciu dodatku Power Query z wielu różnych źródeł danych: skoroszytu programu Excel, formatu tekstowego/CSV, kodu XML, kodu JSON, bazy danych programu SQL Server, listy usługi SharePoint Online, danych OData, pustej tabeli i pustego zapytania.

1. Wybierz pozycję Pobieranie danych>.

   

2. Aby wybrać żądane źródło danych, wybierz pozycję Pobierz dane (Power Query).

3. W oknie dialogowym Wybieranie źródła danych wybierz jedno z dostępnych źródeł danych.

   

4. Połącz ze źródłem danych. Aby dowiedzieć się więcej o sposobie łączenia się z poszczególnymi źródłami danych, zobacz Importowanie danych ze źródeł danych.

5. Wybierz dane, które chcesz zaimportować.

6. Załaduj dane, klikając przycisk Załaduj .

Wynik

Zaimportowane dane pojawią się w nowym arkuszu.

Następne kroki

Aby kształtować i przekształcać dane za pomocą edytora Power Query, wybierz pozycję Przekształć dane. Aby uzyskać więcej informacji, zobacz Kształtuj dane za pomocą edytora Power Query.

Kształtowanie danych za pomocą edytora Power Query

Procedura

1. Wybierz pozycję Pobieranie>danych (Power Query).

2. Aby otworzyć Edytor Power Query, wybierz pozycję Uruchom Edytor Power Query.

   

   Porada

   Możesz również uzyskać dostęp do Edytora zapytań, wybierając pozycję Pobierz dane (Power Query), wybierając źródło danych, a następnie klikając pozycję Dalej.

3. Kształtuj i przekształcaj dane przy użyciu Edytora zapytań tak samo jak w programie Excel dla systemu Windows.

   

   Aby uzyskać więcej informacji, zobacz Pomoc dodatku Power Query dla programu Excel.

4. Po zakończeniu wybierz pozycjęZamknij stronę> główną & Załaduj.

Wynik

Nowo zaimportowane dane pojawią się w nowym arkuszu.

Odśwież źródła danych

Możesz odświeżyć następujące źródła danych: pliki programu SharePoint, listy programu SharePoint, foldery programu SharePoint, dane OData, pliki tekstowe/CSV, skoroszyty programu Excel (.xlsx), pliki XML i JSON, lokalne tabele i zakresy, baza danych i foldery SQL Server firmy Microsoft.

Odśwież po raz pierwszy

Przy pierwszej próbie odświeżenia źródeł danych opartych na plikach w zapytaniach skoroszytu może być konieczne zaktualizowanie ścieżki pliku.

1. Wybierz pozycję Dane, strzałkę obok pozycji Pobierz dane, a następnieUstawienia źródła danych. Zostanie wyświetlone okno dialogowe Ustawień źródła danych.
2. Wybierz połączenie, a następnie wybierz pozycję Zmień ścieżkę pliku.
3. W oknie dialogowym Ścieżka pliku wybierz nową lokalizację, a następnie wybierz pozycję Pobierz dane.
4. Wybierz pozycję Zamknij.

Odśwież kolejne godziny

Aby odświeżyć:

• Wszystkie źródła danych w skoroszycie, wybierz pozycjęOdśwież wszystkiedane>.
• Określone źródło danych, kliknij prawym przyciskiem myszy tabelę zapytań w arkuszu, a następnie wybierz pozycję Odśwież.
• Tabela przestawna, zaznacz komórkę w tabeli przestawnej, a następnie wybierz pozycję Analiza >tabeli przestawnejOdśwież dane.

Wprowadź i wyczyść poświadczenia

Podczas pierwszego uzyskiwania dostępu do programów SharePoint, SQL Server, OData lub innych źródeł danych wymagających uprawnień należy podać odpowiednie poświadczenia. Możesz również wyczyścić poświadczenia, aby wprowadzić nowe.

Wprowadź poświadczenia

Podczas odświeżania zapytania po raz pierwszy może zostać wyświetlony monit o zalogowanie się. Wybierz metodę uwierzytelniania i określ poświadczenia logowania, aby połączyć się ze źródłem danych i kontynuować odświeżanie.

Jeśli wymagane jest logowanie, zostanie wyświetlone okno dialogowe Wprowadzanie poświadczeń .

Na przykład:

• Poświadczenia programu SharePoint:

  

• Poświadczenia programu SQL Server:

  

Wyczyść poświadczenia

1. Wybierz pozycję Pobieranie danych>ustawień źródła danych>.
2. W oknie dialogowym Ustawienia źródła danych wybierz odpowiednie połączenie.
3. U dołu wybierz pozycję Wyczyść uprawnienia.
4. Potwierdź, co chcesz zrobić, a następnie wybierz pozycję Usuń.

Utwórz i przełącz kod VBA dodatku Power Query

Chociaż tworzenie w edytorze Power Query nie jest dostępne w programie Excel dla komputerów Mac, język VBA obsługuje tworzenie dodatku Power Query. Transferowanie modułu kodu VBA w pliku z programu Excel dla systemu Windows do programu Excel dla komputerów Mac jest procesem dwuetapowym. Na końcu tej sekcji udostępniono przykładowy program.

Krok pierwszy: Excel dla systemu Windows

1. W systemie Windows programu Excel twórz zapytania przy użyciu aplikacji VBA. Kod języka VBA używający następujących jednostek w modelu obiektowym programu Excel działa również w Excel dla komputerów Mac: obiekt Zapytania, obiekt WorkbookQuery, właściwość Workbook.Queries. Aby uzyskać więcej informacji, zobacz Dokumentacja języka VBA w programie Excel.

2. W programie Excel upewnij się, że edytor Visual Basic jest otwarty, naciskając klawisze ALT+F11.

3. Kliknij prawym przyciskiem myszy moduł, a następnie wybierz pozycję Eksportuj plik. Zostanie wyświetlone okno dialogowe Eksportowanie .

4. Wprowadź nazwę pliku, upewnij się, że rozszerzenie pliku to .bas, a następnie wybierz pozycję Zapisz.

5. Przekaż plik VBA do usługi online, aby udostępnić plik z komputera Mac.

   Możesz korzystać z usługi Microsoft OneDrive. Aby uzyskać więcej informacji, zobacz Synchronizuj pliki z usługą OneDrive w systemie Mac OS X.

Krok drugi: Excel dla komputerów Mac

1. Pobierz plik VBA do pliku lokalnego, zapisany plik VBA w „Krok pierwszy: Excel dla systemu Windows” i przekazany do usługi online.
2. W Excel dla komputerów Mac wybierz pozycję Narzędzia —Edytor Visual Basic dlamakr>>. Zostanie wyświetlone okno edytora Visual Basic.
3. Kliknij prawym przyciskiem myszy obiekt w oknie programu Project, a następnie wybierz pozycję Importuj plik. Zostanie wyświetlone okno dialogowe Importuj plik.
4. Znajdź plik VBA, a następnie wybierz pozycję Otwórz.

Przykładowy kod

Oto podstawowy kod, który można dostosować i użyć. To jest przykładowe zapytanie, które tworzy listę z wartościami od 1 do 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

Importowanie i odświeżanie plików TXT lub CSV

1. Otwórz skoroszyt programu Excel.

2. Jeśli zostanie wyświetlone ostrzeżenie o zabezpieczeniach dotyczące wyłączania połączeń danych zewnętrznych, wybierz pozycję Włącz zawartość.

3. Jeśli zostanie wyświetlone okno dialogowe Udziel dostępu do plików, wybierz pozycję Wybierz, a następnie wybierz pozycję Udziel dostępu do folderu najwyższego poziomu zawierającego pliki źródła danych.

4. Wybierz pozycję Dane>z tekstu (starsza wersja). Zostanie wyświetlone okno dialogowe Wyszukiwanie .

5. Znajdź plik .txt lub .csv i wybierz pozycję Otwórz. Zostanie wyświetlony Kreator importu tekstu .

   Wskazówka Wielokrotnie sprawdzaj okienko Podgląd wybranych danych , aby potwierdzić swoje wybory.

6. Na pierwszej stronie wykonaj następujące czynności:
   Typ pliku Aby wybrać typ pliku tekstowego, wybierz pozycję Rozdzielany lub Stała szerokość.

   Numer wiersza w opcji Rozpocznij importowanie w wierszu, wybierz numer wiersza, aby określić pierwszy wiersz danych, które chcesz zaimportować.

   Zestaw znaków W polu Pochodzenie pliku wybierz zestaw znaków używany w pliku tekstowym. W większości przypadków możesz pozostawić to ustawienie jako domyślne.

7. Na drugiej stronie wykonaj następujące czynności:

   Rozdzielany

   Jeśli na pierwszej stronie została wybrana opcja Rozdzielany , w obszarze Ograniczniki zaznacz znak ogranicznika lub użyj pola wyboru Inne, aby wprowadzić znak, którego nie ma na liście.

   Wybierz pozycję Traktuj kolejne ograniczniki jako jeden, jeśli dane zawierają ogranicznik zawierający więcej niż jeden znak między polami danych lub jeśli dane zawierają wiele ograniczników niestandardowych.

   W opcji Kwalifikator tekstu, zaznacz znak, który zawiera wartości w pliku tekstowym, który jest najczęściej znakiem cudzysłowu (").
   Stała szerokość

   Jeśli na pierwszej stronie została wybrana stała szerokość , postępuj zgodnie z instrukcjami, aby utworzyć, usunąć lub przenieść wiersz podziału w polu Podgląd zaznaczonych danych .

8. Na trzeciej stronie wykonaj następujące czynności:

   Dla każdej kolumny w obszarze Podgląd zaznaczonych danych zaznacz ją, a następnie w razie potrzeby zmień format kolumny na inny. Możesz dodatkowo ustawić format daty i wybrać pozycję Zaawansowane , aby zmienić ustawienia danych liczbowych. Możesz również przekonwertować dane po ich zaimportowaniu.

   Wybierz pozycję Zakończ. Zostanie wyświetlone okno dialogowe Importuj dane.

9. Wybierz miejsce do dodania danych: w istniejącym arkuszu, w nowym arkuszu lub w tabeli przestawnej.

10. Wybierz przycisk OK.
    Aby upewnić się, że połączenie działa, wprowadź dane, a następnie wybierz pozycję Odśwież połączenia>.

Połącz z bazą danych SQL Database (ODBC)

1. Wybierz pozycję Dane>z SQL Server ODBC. Zostanie wyświetlone okno dialogowe Łączenie ze źródłem danych ODBC programu SQL Server.

   

2. Wprowadź serwer w polu Nazwa serwera i opcjonalnie wprowadź bazę danych w polu Nazwa bazy danych .

   Uzyskaj te informacje od administratora bazy danych.

3. W obszarzeUwierzytelnianie wybierz metodę z listy: Nazwa użytkownika/hasło, Kerberos lub NTLM.

4. Wprowadź poświadczenia w polach Nazwa użytkownika i Hasło .

5. Wybierz pozycję Połącz. Zostanie wyświetlone okno dialogowe Nawigator .

6. W okienku po lewej stronie przejdź do wybranej tabeli, a następnie wybierz ją.

7. Potwierdź instrukcję SQL w okienku po prawej stronie. Instrukcję SQL można zmienić według własnego uznania.

8. Aby wyświetlić podgląd danych, wybierz pozycję Uruchom.

9. Gdy wszystko będzie gotowe, wybierz pozycję Zwróć dane. Zostanie wyświetlone okno dialogowe Importuj dane.

   

10. Wybierz miejsce do dodania danych: w istniejącym arkuszu, w nowym arkuszu lub w tabeli przestawnej.

11. Aby ustawić właściwości połączenia na kartach Użycie i definicje w oknie dialogowym Właściwości , wybierz pozycję Właściwości. Po zaimportowaniu danych możesz również wybrać pozycjęPołączeniadanych>, a następnie w oknie dialogowym Właściwości połączenia wybrać pozycję Właściwości.

12. Wybierz przycisk OK.

13. Aby upewnić się, że połączenie działa, wprowadź dane, a następnie wybierz pozycję>Odśwież dane wszystko.

Połącz z inną bazą danych (Microsoft Query)

Jeśli chcesz użyć źródła zewnętrznego, które nie jest bazą danych SQL Database (na przykład FileMaker Pro), możesz użyć sterownika Open Database Connectivity (ODBC) zainstalowanego na komputerze Mac. Informacje o sterownikach są dostępne na tej stronie internetowej. Po zainstalowaniu sterownika źródła danych wykonaj następujące kroki:

1. Wybierz pozycję Dane>z bazy danych (Microsoft Query).

2. Dodaj źródło danych dla bazy danych, a następnie wybierz pozycję OK.

3. W wierszu polecenia poświadczeń programu SQL Server wprowadź metodę uwierzytelniania, nazwę użytkownika i hasło.

   

4. Po lewej stronie wybierz strzałkę obok serwera, aby wyświetlić bazy danych.

5. Wybierz strzałkę obok żądanej bazy danych.

6. Wybierz odpowiednią tabelę.

7. Aby wyświetlić podgląd danych, wybierz pozycję Uruchom.

8. Gdy wszystko będzie gotowe, wybierz pozycję Zwróć dane.

9. W oknie dialogowym Importuj dane wybierz lokalizację danych: w istniejącym arkuszu, w nowym arkuszu lub w tabeli przestawnej.

10. Wybierz przycisk OK.

11. Aby upewnić się, że połączenie działa, wprowadź dane, a następnie wybierz pozycję>Odśwież dane wszystko.

Wyczyść poświadczenia logowania

Jeśli Twoje uprawnienia nie działają, może będzie je trzeba najpierw wyczyścić, a potem zalogować się.

1. Wybierz pozycję Połączenia danych>. Zostanie wyświetlone okno dialogowe Połączenia skoroszytu.

2. Wybierz odpowiednie połączenie z listy, a następnie wybierz pozycję Wyczyść uprawnienia.