Excel

Artykuł
02/21/2024

Podsumowanie

Element	opis
Stan wydania	Ogólna dostępność
Produkty	Excel Power BI (modele semantyczne) Power BI (przepływy danych) Sieć szkieletowa (Dataflow Gen2) Power Apps (przepływy danych) Dynamics 365 Customer Insights Analysis Services
Obsługiwane typy uwierzytelniania	Anonimowe (online) Podstawowa (online) Konto organizacyjne (online)
Dokumentacja referencyjna funkcji	Excel.Workbook Excel.CurrentWorkbook

Uwaga

Niektóre funkcje mogą być obecne w jednym produkcie, ale nie w innych ze względu na harmonogramy wdrażania i możliwości specyficzne dla hosta.

Wymagania wstępne

Aby nawiązać połączenie ze starszym skoroszytem (takim jak .xls lub xlsb), wymagany jest dostawca OLEDB aparatu bazy danych programu Access (lub ACE). Aby zainstalować tego dostawcę, przejdź do strony pobierania i zainstaluj odpowiednią wersję (32-bitowa lub 64-bitowa). Jeśli nie masz zainstalowanego skoroszytu, podczas nawiązywania połączenia ze starszymi skoroszytami zostanie wyświetlony następujący błąd:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

Nie można zainstalować usługi ACE w środowiskach usług w chmurze. Jeśli więc widzisz ten błąd na hoście w chmurze (takim jak Power Query Online), musisz użyć bramy z zainstalowaną usługą ACE w celu nawiązania połączenia ze starszymi plikami programu Excel.

Obsługiwane możliwości

Importuj

Połączenie do skoroszytu programu Excel z programu Power Query Desktop

Aby nawiązać połączenie z programu Power Query Desktop:

Wybierz pozycję Skoroszyt programu Excel w środowisku pobierania danych. Środowisko pobierania danych w programie Power Query Desktop różni się między aplikacjami. Aby uzyskać więcej informacji na temat środowiska pobierania danych dla aplikacji w programie Power Query Desktop, zobacz Gdzie uzyskać dane.
Wyszukaj i wybierz skoroszyt programu Excel, który chcesz załadować. Następnie wybierz pozycję Otwórz.

Jeśli skoroszyt programu Excel jest w trybie online, użyj łącznika sieci Web, aby nawiązać połączenie ze skoroszytem.
W obszarze Nawigator wybierz żądane informacje o skoroszycie, a następnie wybierz pozycję Załaduj, aby załadować dane lub Przekształć dane, aby kontynuować przekształcanie danych w Edytor Power Query.

Połączenie do skoroszytu programu Excel z usługi Power Query Online

Aby nawiązać połączenie z usługi Power Query Online:

Wybierz opcję skoroszytu programu Excel w środowisku pobierania danych. Różne aplikacje mają różne sposoby uzyskiwania danych w usłudze Power Query Online. Aby uzyskać więcej informacji na temat sposobu uzyskiwania danych z aplikacji w usłudze Power Query Online, przejdź do obszaru Gdzie uzyskać dane.
W wyświetlonym oknie dialogowym programu Excel podaj ścieżkę do skoroszytu programu Excel.
W razie potrzeby wybierz lokalną bramę danych, aby uzyskać dostęp do skoroszytu programu Excel.
Jeśli po raz pierwszy uzyskujesz dostęp do tego skoroszytu programu Excel, wybierz rodzaj uwierzytelniania i zaloguj się do swojego konta (w razie potrzeby).
W nawigatorze wybierz żądane informacje o skoroszycie, a następnie przekształć dane, aby kontynuować przekształcanie danych w Edytor Power Query.

Sugerowane tabele

Jeśli połączysz się ze skoroszytem programu Excel, który nie zawiera specjalnie pojedynczej tabeli, nawigator dodatku Power Query podejmie próbę utworzenia sugerowanej listy tabel, z których można wybrać. Rozważmy na przykład poniższy przykład skoroszytu, który zawiera dane z A1 do C5, więcej danych z D8 do E10 i nie tylko z C13 do F16.

Zrzut ekranu przedstawiający skoroszyt programu Excel z trzema zestawami danych.

Po nawiązaniu połączenia z danymi w dodatku Power Query nawigator Power Query tworzy dwie listy. Pierwsza lista zawiera cały arkusz skoroszytu, a druga lista zawiera trzy sugerowane tabele.

Jeśli wybierzesz cały arkusz w nawigatorze, skoroszyt zostanie wyświetlony tak, jak pokazano w programie Excel, ze wszystkimi pustymi komórkami wypełnionymi wartością null.

Jeśli wybierzesz jedną z sugerowanych tabel, każda pojedyncza tabela, którą program Power Query mógł określić z układu skoroszytu, jest wyświetlany w nawigatorze. Jeśli na przykład wybierzesz tabelę 3, zostaną wyświetlone dane, które pierwotnie pojawiły się w komórkach C13 do F16.

Uwaga

Jeśli arkusz zmieni się wystarczająco dużo, tabela może nie zostać prawidłowo odświeżona. Może być możliwe naprawienie odświeżania przez ponowne zaimportowanie danych i wybranie nowej sugerowanej tabeli.

Rozwiązywanie problemów

Precyzja liczbowa (lub "Dlaczego moje liczby się zmieniły?")

Podczas importowania danych programu Excel można zauważyć, że niektóre wartości liczbowe wydają się nieco zmieniać podczas importowania do dodatku Power Query. Jeśli na przykład wybierzesz komórkę zawierającą 0,049 w programie Excel, ta liczba zostanie wyświetlona na pasku formuły jako 0.049. Jeśli jednak zaimportujesz tę samą komórkę do dodatku Power Query i wybierzesz ją, szczegóły podglądu będą wyświetlane jako 0.049000000000002 (mimo że w tabeli podglądu jest sformatowana jako 0,049). Co się tu dzieje?

Odpowiedź jest nieco skomplikowana i ma związek ze sposobem przechowywania liczb przez program Excel przy użyciu notacji binarnej zmiennoprzecinkowa. Najważniejsze jest to, że istnieją pewne liczby, których program Excel nie może reprezentować z dokładnością 100%. Jeśli otworzysz plik .xlsx i przyjrzysz się rzeczywistej wartości przechowywanej, zobaczysz, że w pliku .xlsx wartość 0.049 jest rzeczywiście przechowywana jako 0.0490000000000000000002. Jest to wartość odczytana przez dodatek Power Query z .xlsx, a tym samym wartość wyświetlana po wybraniu komórki w dodatku Power Query. (Aby uzyskać więcej informacji na temat dokładności liczbowej w dodatku Power Query, przejdź do sekcji "Liczba dziesiętna" i "Stała liczba dziesiętna"Typy danych w dodatku Power Query).

Połączenie do skoroszytu programu Excel w trybie online

Jeśli chcesz nawiązać połączenie z dokumentem programu Excel hostowanym w programie SharePoint, możesz to zrobić za pośrednictwem łącznika sieci Web w programie Power BI Desktop, programie Excel i przepływach danych, a także za pomocą łącznika programu Excel w przepływach danych. Aby uzyskać link do pliku:

Otwórz dokument w programie Excel Desktop.
Otwórz menu Plik, wybierz kartę Informacje, a następnie wybierz pozycję Kopiuj ścieżkę.
Skopiuj adres do pola Ścieżka pliku lub adres URL i usuń ciąg ?web=1 na końcu adresu.

Starszy łącznik ACE

Dodatek Power Query odczytuje starsze skoroszyty (takie jak .xls lub xlsb) przy użyciu dostawcy OLEDB aparatu bazy danych programu Access (lub ACE). W związku z tym mogą wystąpić nieoczekiwane zachowania podczas importowania starszych skoroszytów, które nie występują podczas importowania skoroszytów OpenXML (takich jak .xlsx). Oto kilka typowych przykładów.

Nieoczekiwane formatowanie wartości

Ze względu na ACE wartości ze starszego skoroszytu programu Excel mogą być importowane z mniejszą precyzją lub wiernością niż oczekiwano. Załóżmy na przykład, że plik programu Excel zawiera liczbę 1024.231, która została sformatowana do wyświetlenia jako "1024.23". Podczas importowania do dodatku Power Query ta wartość jest reprezentowana jako wartość tekstowa "1024.23" zamiast jako podstawowa liczba pełnej wierności (1024.231). Jest to spowodowane tym, że w tym przypadku ACE nie udostępnia bazowej wartości dodatku Power Query, ale tylko wartości wyświetlanej w programie Excel.

Nieoczekiwane wartości null

Gdy usługa ACE ładuje arkusz, analizuje pierwsze osiem wierszy w celu określenia typów danych kolumn. Jeśli pierwsze osiem wierszy nie jest reprezentatywne dla późniejszych wierszy, ACE może zastosować niepoprawny typ do tej kolumny i zwracać wartości null dla dowolnej wartości, która nie jest zgodna z typem. Jeśli na przykład kolumna zawiera liczby w pierwszych ośmiu wierszach (takich jak 1000, 1001 itd.), ale zawiera dane nieliczbowe w późniejszych wierszach (takich jak "100Y" i "100Z"), ACE stwierdza, że kolumna zawiera liczby, a wszystkie wartości nieliczbowe są zwracane jako null.

Niespójne formatowanie wartości

W niektórych przypadkach usługa ACE zwraca zupełnie inne wyniki między odświeżeniami. Korzystając z przykładu opisanego w sekcji formatowania, możesz nagle zobaczyć wartość 1024.231 zamiast "1024.23". Ta różnica może być spowodowana otwarciem starszego skoroszytu w programie Excel podczas importowania go do dodatku Power Query. Aby rozwiązać ten problem, zamknij skoroszyt.

Brakujące lub niekompletne dane programu Excel

Czasami program Power Query nie może wyodrębnić wszystkich danych z arkusza programu Excel. Ta awaria jest często spowodowana nieprawidłowymi wymiarami arkusza (na przykład wymiaramiA1:C200, gdy rzeczywiste dane zajmują więcej niż trzy kolumny lub 200 wierszy).

Jak zdiagnozować nieprawidłowe wymiary

Aby wyświetlić wymiary arkusza:

Zmień nazwę pliku xlsx na rozszerzenie .zip.
Otwórz plik w Eksplorator plików.
Przejdź do pliku xl\arkusze.
Skopiuj plik XML problematycznego arkusza (na przykład Sheet1.xml) z pliku zip do innej lokalizacji.
Sprawdź kilka pierwszych wierszy pliku. Jeśli plik jest wystarczająco mały, otwórz go w edytorze tekstów. Jeśli plik jest za duży, aby został otwarty w edytorze tekstów, uruchom następujące polecenie w wierszu polecenia: więcej Sheet1.xml.
<dimension .../> Wyszukaj tag (na przykład <dimension ref="A1:C200" />).

Jeśli plik ma atrybut wymiaru wskazujący pojedynczą komórkę (na przykład <dimension ref="A1" />), dodatek Power Query używa tego atrybutu do znalezienia wiersza początkowego i kolumny danych w arkuszu.

Jeśli jednak plik ma atrybut wymiaru wskazujący wiele komórek (takich jak <dimension ref="A1:AJ45000"/>), dodatek Power Query używa tego zakresu do znalezienia wiersza początkowego i kolumny, a także końcowego wiersza i kolumny. Jeśli ten zakres nie zawiera wszystkich danych w arkuszu, niektóre dane nie zostaną załadowane.

Jak naprawić nieprawidłowe wymiary

Problemy spowodowane nieprawidłowymi wymiarami można rozwiązać, wykonując jedną z następujących akcji:

Otwórz i zapisz ponownie dokument w programie Excel. Ta akcja spowoduje zastąpienie nieprawidłowych wymiarów przechowywanych w pliku z poprawną wartością.
Upewnij się, że narzędzie, które wygenerowało plik programu Excel, jest stałe w celu poprawnego wyprowadzenia wymiarów.
Zaktualizuj zapytanie języka M, aby zignorować nieprawidłowe wymiary. Od grudnia 2020 r. wersja dodatku Power Query Excel.Workbook obsługuje InferSheetDimensions teraz opcję. Jeśli to prawda, ta opcja spowoduje, że funkcja zignoruje wymiary przechowywane w skoroszycie, a zamiast tego określi je, sprawdzając dane.

Oto przykład sposobu podawania tej opcji:

Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

Powolne ładowanie danych programu Excel może być również spowodowane nieprawidłowymi wymiarami. Jednak w tym przypadku spowolnienie jest spowodowane tym, że wymiary są znacznie większe niż muszą być, a nie zbyt małe. Zbyt duże wymiary spowodują, że dodatek Power Query odczytuje znacznie większą ilość danych ze skoroszytu, niż jest to rzeczywiście potrzebne.

Aby rozwiązać ten problem, zapoznaj się z artykułem Lokalizowanie i resetowanie ostatniej komórki w arkuszu , aby uzyskać szczegółowe instrukcje.

Podczas pobierania danych z programu Excel na komputerze lub z programu SharePoint należy wziąć pod uwagę zarówno ilość zaangażowanych danych, jak i złożoność skoroszytu.

Podczas pobierania bardzo dużych plików z programu SharePoint zauważysz spadek wydajności. Jednak jest to tylko jedna część problemu. Jeśli masz znaczącą logikę biznesową w pliku programu Excel pobieranym z programu SharePoint, ta logika biznesowa może wymagać wykonania podczas odświeżania danych, co może spowodować skomplikowane obliczenia. Rozważ agregowanie i wstępne obliczanie danych lub przenoszenie większej liczby logiki biznesowej z warstwy programu Excel i do warstwy Dodatku Power Query.

Błędy podczas importowania plików CSV przy użyciu łącznika programu Excel

Mimo że pliki CSV można otwierać w programie Excel, nie są to pliki programu Excel. Zamiast tego użyj łącznika Text/CSV.

Błąd podczas importowania skoroszytów "Strict Open XML Spreadsheet"

Podczas importowania skoroszytów zapisanych w formacie "Ściśle open XML" programu Excel może zostać wyświetlony następujący błąd:

DataFormat.Error: The specified package is invalid. The main part is missing.

Ten błąd występuje, gdy sterownik ACE nie jest zainstalowany na komputerze hosta. Skoroszyty zapisane w formacie "Strict Open XML Spreadsheet" mogą być odczytywane tylko przez usługę ACE. Jednak ponieważ takie skoroszyty używają tego samego rozszerzenia pliku co zwykłe skoroszyty Open XML (.xlsx), nie możemy użyć rozszerzenia, aby wyświetlić zwykły the Access Database Engine OLEDB provider may be required to read this type of file komunikat o błędzie.

Aby rozwiązać ten problem, zainstaluj sterownik ACE. Jeśli błąd występuje w usłudze w chmurze, musisz użyć bramy uruchomionej na komputerze z zainstalowanym sterownikiem ACE.

Błędy "Plik zawiera uszkodzone dane"

Podczas importowania niektórych skoroszytów programu Excel może zostać wyświetlony następujący błąd.

DataFormat.Error: File contains corrupted data.

Zazwyczaj ten błąd wskazuje, że występuje problem z formatem pliku.

Jednak czasami ten błąd może wystąpić, gdy plik wydaje się być plikiem Open XML (takim jak .xlsx), ale sterownik ACE jest rzeczywiście potrzebny do przetworzenia pliku. Przejdź do sekcji Starszy łącznik ACE, aby uzyskać więcej informacji na temat przetwarzania plików, które wymagają sterownika ACE.

Znane problemy i ograniczenia

Usługa Power Query Online nie może uzyskać dostępu do zaszyfrowanych plików programu Excel. Ponieważ pliki programu Excel oznaczone typami poufności innymi niż "Publiczne" lub "Inne niż biznesowe" są szyfrowane, nie są dostępne za pośrednictwem usługi Power Query Online.
Usługa Power Query Online nie obsługuje plików programu Excel chronionych hasłem.