Excel

Článek
02/07/2024

Shrnutí

Položka	Popis
Stav vydání	Všeobecná dostupnost
Produkty	Excel Power BI (sémantické modely) Power BI (toky dat) Prostředky infrastruktury (Tok dat Gen2) Power Apps (toky dat) Dynamics 365 Customer Insights Analysis Services
Podporované typy ověřování	Anonymní (online) Základní (online) Účet organizace (online)
Referenční dokumentace k funkcím	Excel.Workbook Excel.CurrentWorkbook

Poznámka:

Některé funkce můžou být přítomné v jednom produktu, ale ne jiné kvůli plánům nasazení a možnostem specifickým pro hostitele.

Požadavky

Pokud se chcete připojit ke staršímu sešitu (například .xls nebo .xlsb), vyžaduje se zprostředkovatel OLEDB (nebo ACE) databázového stroje Accessu. Chcete-li nainstalovat tohoto poskytovatele, přejděte na stránku pro stažení a nainstalujte příslušnou (32bitovou nebo 64bitovou) verzi. Pokud ho nemáte nainstalovaný, při připojování ke starším sešitům se zobrazí následující chyba:

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.

ACE nejde nainstalovat v prostředích cloudové služby. Pokud se tedy tato chyba zobrazuje v cloudovém hostiteli (například Power Query Online), budete muset pro připojení ke starším excelovým souborům použít bránu s nainstalovanou službou ACE.

Podporované možnosti

Import

Připojení do excelového sešitu z Power Query Desktopu

Postup připojení z Power Query Desktopu:

Vyberte excelový sešit v prostředí získat data. Možnosti získání dat v Power Query Desktopu se mezi aplikacemi liší. Další informace o tom, jak Power Query Desktop získat data pro vaši aplikaci, najdete v části Kde získat data.
Vyhledejte a vyberte excelový sešit, který chcete načíst. Pak vyberte Otevřít.

Pokud je excelový sešit online, připojte se k němu pomocí webového konektoru .
V Navigátoru vyberte požadované informace o sešitu a pak buď vyberte Načíst, abyste načetli data, nebo transformovat data a pokračujte v transformaci dat v Editor Power Query.

Připojení do excelového sešitu z Power Query Online

Vytvoření připojení z Power Query Online:

V prostředí získat data vyberte možnost excelového sešitu . Různé aplikace mají různé způsoby, jak získat prostředí dat v Power Query Online. Další informace o tom, jak získat data z aplikace do Power Query Online, najdete v části Kde získat data.
V dialogovém okně Aplikace Excel, které se zobrazí, zadejte cestu k excelovém sešitu.
V případě potřeby vyberte místní bránu dat pro přístup k excelovém sešitu.
Pokud jste k tomuto excelovém sešitu přistupovali poprvé, vyberte typ ověřování a v případě potřeby se přihlaste ke svému účtu.
V Navigátoru vyberte požadované informace o sešitu a pak transformujte data v Editor Power Query.

Navrhované tabulky

Pokud se připojíte k excelovém sešitu, který neobsahuje konkrétně jednu tabulku, pokusí se navigátor Power Query vytvořit navrhovaný seznam tabulek, ze které si můžete vybrat. Představte si například následující příklad sešitu, který obsahuje data z A1 do C5, další data z D8 do E10 a další z C13 do F16.

Snímek obrazovky excelového sešitu se třemi sadami dat

Když se připojíte k datům v Power Query, vytvoří navigátor Power Query dva seznamy. První seznam obsahuje celý list sešitu a druhý seznam obsahuje tři navrhované tabulky.

Pokud vyberete celý list v navigátoru, zobrazí se sešit tak, jak se zobrazí v Excelu, se všemi prázdnými buňkami vyplněnými hodnotou null.

Pokud vyberete jednu z navrhovaných tabulek, zobrazí se v navigátoru každá jednotlivá tabulka, kterou Power Query dokázala určit z rozložení sešitu. Pokud například vyberete tabulku 3, zobrazí se data, která se původně objevila v buňkách C13 až F16.

Poznámka:

Pokud se list dostatečně změní, nemusí se tabulka správně aktualizovat. Aktualizaci můžete opravit opětovným importem dat a výběrem nové navrhované tabulky.

Řešení problému

Číselná přesnost (nebo "Proč se moje čísla změnila?")

Při importu excelových dat si můžete všimnout, že se některé číselné hodnoty při importu do Power Query mírně mění. Pokud například v Excelu vyberete buňku obsahující 0,049, zobrazí se toto číslo v řádku vzorců jako 0,049. Pokud ale naimportujete stejnou buňku do Power Query a vyberete ji, podrobnosti náhledu se zobrazí jako 0,049000000000002 (i když je v tabulce náhledu formátovaná jako 0,049). Co se tu děje?

Odpověď je trochu složitá a musí to udělat s tím, jak Excel ukládá čísla pomocí něčeho, co se nazývá binární zápis s plovoucí desetinou čárkou. Na konci řádku jsou určitá čísla, která Excel nemůže představovat s přesností 100 %. Pokud prolomíte soubor .xlsx a podíváte se na skutečnou hodnotu uloženou, uvidíte, že v .xlsx souboru, 0,049 je ve skutečnosti uložen jako 0,049000000000000002. Toto je hodnota, kterou Power Query načte z .xlsx, a proto se zobrazí hodnota, která se zobrazí při výběru buňky v Power Query. (Další informace o číselné přesnosti v Power Query najdete v částech Desetinné číslo a Pevné desetinné číslo v částiDatové typy v Power Query.)

Připojení do online excelového sešitu

Pokud se chcete připojit k excelovým dokumentům hostovaným na SharePointu, můžete to udělat prostřednictvím webového konektoru v Power BI Desktopu, Excelu a tocích dat a také pomocí konektoru Excelu v tocích dat. Pokud chcete získat odkaz na soubor:

Otevřete dokument v desktopové aplikaci Excel.
Otevřete nabídku Soubor, vyberte kartu Informace a pak vyberte Kopírovat cestu.
Zkopírujte adresu do pole Cesta k souboru nebo adresa URL a odeberte z konce adresy ?web=1 .

Starší konektor ACE

Power Query čte starší sešity (například .xls nebo .xlsb) pomocí zprostředkovatele OLEDB accessového databázového stroje (nebo ACE). Z tohoto důvodu může dojít k neočekávanému chování při importu starších sešitů, ke kterým nedochází při importu sešitů OpenXML (například .xlsx). Tady je několik běžných příkladů.

Neočekávané formátování hodnot

Kvůli ACE se hodnoty ze starší verze excelového sešitu můžou importovat s menší přesností nebo přesností, než očekáváte. Představte si například, že excelový soubor obsahuje číslo 1024.231, které jste naformátovali jako "1 024,23". Při importu do Power Query je tato hodnota reprezentována jako textová hodnota 1 024,23 místo jako základní celé číslo (1024,231). Důvodem je, že ACE v tomto případě nezpřístupní podkladovou hodnotu Power Query, ale jenom hodnotu, která se zobrazí v Excelu.

Neočekávané hodnoty null

Když ACE načte list, podívá se na prvních osm řádků a určí datové typy sloupců. Pokud prvních osm řádků nebude reprezentovat pozdější řádky, může ACE u tohoto sloupce použít nesprávný typ a vrátit hodnoty null pro libovolnou hodnotu, která neodpovídá danému typu. Pokud například sloupec obsahuje čísla v prvních osmi řádcích (například 1000, 1001 atd.), ale obsahuje nečíselná data v pozdějších řádcích (například "100Y" a "100Z"), ACE dospěla k závěru, že sloupec obsahuje čísla a všechny nečíselné hodnoty se vrátí jako null.

Nekonzistentní formátování hodnot

V některých případech vrátí ACE úplně jiné výsledky v rámci aktualizací. Při použití příkladu popsaného v oddílu formátování se může najednou zobrazit hodnota 1024,231 místo "1 024,23". Tento rozdíl může být způsobený otevřením starší verze sešitu v Excelu při importu do Power Query. Chcete-li tento problém vyřešit, zavřete sešit.

Chybějící nebo neúplná excelová data

Power Query někdy nedokáže extrahovat všechna data z excelového listu. Příčinou tohoto selhání je často nesprávná dimenze listu (například dimenzeA1:C200, kdy skutečná data zabírají více než tři sloupce nebo 200 řádků).

Diagnostika nesprávných dimenzí

Zobrazení dimenzí listu:

Přejmenujte soubor xlsx s příponou .zip.
Otevřete soubor v Průzkumník souborů.
Přejděte do xl\worksheets.
Zkopírujte soubor XML pro problematický list (například Sheet1.xml) ze souboru ZIP do jiného umístění.
Zkontrolujte několik prvních řádků souboru. Pokud je soubor dostatečně malý, otevřete ho v textovém editoru. Pokud je soubor příliš velký, aby se otevřel v textovém editoru, spusťte z příkazového řádku následující příkaz: další Sheet1.xml.
<dimension .../> Vyhledejte značku (například<dimension ref="A1:C200" />).

Pokud má soubor atribut dimenze, který odkazuje na jednu buňku (například <dimension ref="A1" />), Použije Power Query tento atribut k vyhledání počátečního řádku a sloupce dat na listu.

Pokud má ale soubor atribut dimenze, který odkazuje na více buněk (například <dimension ref="A1:AJ45000"/>), Použije Power Query tuto oblast k vyhledání počátečního řádku a sloupce i koncového řádku a sloupce. Pokud tato oblast neobsahuje všechna data na listu, některá data se nenačtou.

Oprava nesprávných dimenzí

Problémy způsobené nesprávnými dimenzemi můžete vyřešit provedením jedné z následujících akcí:

Otevřete a znovu uložte dokument v Excelu. Tato akce přepíše nesprávné dimenze uložené v souboru se správnou hodnotou.
Ujistěte se, že je nástroj, který vygeneroval soubor Aplikace Excel, opraven, aby správně vygeneroval výstup dimenzí.
Aktualizujte dotaz M tak, aby ignoroval nesprávné dimenze. Od verze Power Query Excel.Workbook z prosince 2020 teď podporuje možnost InferSheetDimensions . Pokud je pravda, tato možnost způsobí, že funkce ignoruje dimenze uložené v sešitu a místo toho je určí kontrolou dat.

Tady je příklad, jak zadat tuto možnost:

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

Pomalé načítání excelových dat může být způsobeno také nesprávnými rozměry. V tomto případě je však zpomalení způsobeno mnohem většími rozměry, než musí být, a nikoli příliš malou. Příliš velké dimenze způsobí, že Power Query přečte mnohem větší množství dat ze sešitu, než je skutečně potřeba.

Pokud chcete tento problém vyřešit, najdete podrobné pokyny k vyhledání a obnovení poslední buňky na listu .

Při načítání dat z Excelu na počítači nebo ze SharePointu zvažte jak objem zahrnutých dat, tak složitost sešitu.

Při načítání velmi velkých souborů ze SharePointu si všimnete snížení výkonu. Jedná se ale pouze o jednu část problému. Pokud máte v excelovém souboru načteného ze SharePointu významnou obchodní logiku, tato obchodní logika se může při aktualizaci dat spouštět, což může způsobit složité výpočty. Zvažte agregaci a předpočítání dat nebo přesun více obchodní logiky z excelové vrstvy a do vrstvy Power Query.

Chyby při importu souborů CSV pomocí konektoru Excelu

I když se soubory CSV dají otevřít v Excelu, nejsou to excelové soubory. Místo toho použijte konektor Text/CSV.

Chyba při importu sešitů Strict Open XML Spreadsheet

Při importu sešitů uložených ve formátu Strict Open XML Spreadsheet v Excelu se může zobrazit následující chyba:

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

K této chybě dochází, když není ovladač ACE nainstalovaný na hostitelském počítači. Sešity uložené ve formátu Strict Open XML Spreadsheet může číst jenom ACE. Vzhledem k tomu, že takové sešity používají stejnou příponu souboru jako běžné sešity Open XML (.xlsx), nemůžeme příponu použít k zobrazení obvyklé the Access Database Engine OLEDB provider may be required to read this type of file chybové zprávy.

Pokud chcete tuto chybu vyřešit, nainstalujte ovladač ACE. Pokud k chybě dochází v cloudové službě, budete muset použít bránu spuštěnou na počítači s nainstalovaným ovladačem ACE.

Chyby "Soubor obsahuje poškozená data"

Při importu některých excelových sešitů se může zobrazit následující chyba.

DataFormat.Error: File contains corrupted data.

Tato chyba obvykle značí problém s formátem souboru.

K této chybě může někdy dojít, když se zdá, že soubor je soubor Open XML (například .xlsx), ale ovladač ACE je skutečně potřeba ke zpracování souboru. Další informace o zpracování souborů, které vyžadují ovladač ACE, najdete v části Starší verze konektoru ACE.

Známé problémy a omezení

Power Query Online nemá přístup k šifrovaným excelových souborům. Vzhledem k tomu, že excelové soubory označené jinými typy citlivosti než "Veřejné" nebo "Jiné než firmy" jsou šifrované, nejsou přístupné prostřednictvím Power Query Online.
Power Query Online nepodporuje excelové soubory chráněné heslem.