Excel

Článok
02/15/2024

Súhrn

Položka	Description
Stav vydania	Všeobecná dostupnosť
Produkty	Excel Power BI (sémantické modely) Power BI (toky údajov) Fabric (Tok údajov Gen2) Power Apps (toky údajov) Dynamics 365 Customer Insights Analysis Services
Podporované typy overovania	Anonymné (online) Základné (online) Konto organizácie (online)
Referenčná dokumentácia k funkcii	Excel.Workbook Excel.CurrentWorkbook

Poznámka

Niektoré možnosti môžu byť k dispozícii v jednom produkte, ale nie iné z dôvodu plánov nasadenia a funkcií špecifických pre hostiteľa.

Požiadavky

Na pripojenie k staršiemu zošitu (napríklad .xls alebo .xlsb) sa vyžaduje poskytovateľ databázového stroja Access OLEDB (alebo ACE). Ak chcete nainštalovať tohto poskytovateľa, prejdite na stránku na stiahnutie a nainštalujte príslušnú (32-bitovú alebo 64-bitovú) verziu. Ak ho nemáte nainštalovaný, pri pripájaní k starším zošitom sa zobrazí nasledujúca 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 sa nedá nainštalovať v prostrediach cloudovej služby. Ak sa teda zobrazuje táto chyba v cloudovom hostiteľovi (napríklad Power Query Online), na pripojenie k starším excelovým súborom musíte použiť bránu s nainštalovaným ACE.

Podporované možnosti

Importovať

Pripojenie do excelového zošita z aplikácie Power Query Desktop

Vytvorenie pripojenia z aplikácie Power Query Desktop:

V prostredí na získanie údajov vyberte položku Excelové zošity . Možnosti získania údajov v aplikácii Power Query Desktop sa líšia medzi aplikáciami. Ďalšie informácie o tom, ako Power Query Desktop získať údaje pre aplikáciu, nájdete v téme Kde sa nachádzajú údaje.
Vyhľadajte a vyberte excelové zošity, ktoré chcete načítať. Potom vyberte položku Otvoriť.

Ak je zošit programu Excel online, pripojte sa k zošitu pomocou webového konektora .
V Navigátore vyberte požadované informácie o zošite a potom buď vyberte položku Načítať, aby ste načítali údaje, alebo položku Transformovať údaje a pokračujte v transformácii údajov v Editor Power Query.

Pripojenie do excelového zošita zo služby Power Query Online

Vytvorenie pripojenia z Power Query Online:

V prostredí na získavanie údajov vyberte možnosť excelového zošita . Rôzne aplikácie majú rôzne spôsoby, ako získať údaje pomocou doplnku Power Query Online. Ďalšie informácie o tom, ako získať údaje z aplikácie do služby Power Query Online, nájdete v téme Kde získať údaje.
V zobrazenom dialógovom okne Excelu zadajte cestu k excelovému zošitu.
Ak je to potrebné, vyberte lokálnu bránu údajov na prístup k excelovému zošitu.
Ak ste do tohto excelového zošita pristupovali prvýkrát, vyberte typ overenia a prihláste sa do svojho konta (ak je to potrebné).
V Navigátore vyberte požadované informácie o zošite a potom položku Transformovať údaje a pokračujte v transformácii údajov v Editor Power Query.

Navrhované tabuľky

Ak sa pripojíte k excelovému zošitu, ktorý neobsahuje konkrétne jednu tabuľku, navigátor Power Query sa pokúsi vytvoriť navrhovaný zoznam tabuliek, z nich si môžete vybrať. Pozrite sa napríklad na nasledujúci príklad zošita, ktorý obsahuje údaje od A1 po C5, viac údajov od D8 do E10 a viac od C13 do F16.

Snímka obrazovky excelového zošita s tromi množinami údajov.

Keď sa pripojíte k údajom v doplnku Power Query, navigátor Power Query vytvorí dva zoznamy. Prvý zoznam obsahuje celý hárok zošita a druhý zoznam obsahuje tri navrhované tabuľky.

Ak v navigátore vyberiete celý hárok, zošit sa zobrazí tak, ako sa zobrazuje v Exceli, pričom všetky prázdne bunky sú vyplnené hodnotou null.

Ak vyberiete jednu z navrhovaných tabuliek, každá jednotlivá tabuľka, ktorú mohol Power Query určiť z rozloženia zošita, sa zobrazí v navigátore. Ak napríklad vyberiete tabuľku 3, zobrazia sa údaje, ktoré sa pôvodne objavili v bunkách C13 až F16.

Poznámka

Ak sa hárok dosť zmení, tabuľka sa možno neobnoví správne. Obnovenie možno budete môcť opraviť opätovným importovaním údajov a výberom novej navrhovanej tabuľky.

Riešenie problémov

Presnosť čísel (alebo "Prečo sa zmenili moje čísla?")

Pri importovaní excelových údajov si môžete všimnúť, že pri importovaní do Power Query sa zdá, že určité číselné hodnoty sa mierne menia. Ak napríklad vyberiete bunku obsahujúcu 0,049 v Exceli, toto číslo sa zobrazí v riadku vzorcov ako 0,049. Ak však importujete rovnakú bunku do doplnku Power Query a vyberiete ju, podrobnosti ukážky ju zobrazia ako 0,0490000000000002 (aj keď v tabuľke ukážky je formátovaná ako 0,049). Čo sa tu deje?

Odpoveď je trochu zložitá a súvisí s tým, ako excel ukladá čísla pomocou niečoho, čo sa nazýva binárny zápis s pohyblivou desatinnou čiarkou. Spodnom riadku je, že existujú určité čísla, ktoré Excel nemôže predstavovať s presnosťou na 100 %. Ak ste crack otvoriť .xlsx súbor a pozrite sa na skutočnú hodnotu, ktorá je uložená, uvidíte, že v súbore .xlsx, je 0,049 skutočne uložené ako 0,04900000000002. Toto je hodnota, ktorú Power Query prečíta z .xlsx, a teda hodnota, ktorá sa zobrazí po výbere bunky v doplnku Power Query. (Ďalšie informácie o presnosti čísel v Doplnku Power Query nájdete v sekciách "Desatinné číslo" a "Pevné desatinné číslo" tabuľky Typy údajov v doplnku Power Query.)

Pripojenie do online excelového zošita

Ak sa chcete pripojiť k excelovému dokumentu hosťovanému v SharePointe, môžete to urobiť prostredníctvom webového konektora v aplikácii Power BI Desktop, Exceli a tokoch údajov, ako aj pomocou konektora Excelu v tokoch údajov. Získanie prepojenia na súbor:

Otvorte dokument v aplikácii Excel Desktop.
Otvorte ponuku Súbor , vyberte kartu Informácie a potom vyberte položku Kopírovať cestu.
Skopírujte adresu do poľa Cesta k súboru alebo URL adresa a odstráňte reťazec ?web=1 z konca adresy.

Starší konektor ACE

Power Query prečíta staršie zošity (napríklad .xls alebo .xlsb) pomocou poskytovateľa OLEDB databázového stroja Access (alebo ACE). Z tohto dôvodu sa pri importovaní starších zošitov, ktoré sa nevyskytujú pri importovaní zošitov OpenXML, môže vyskytnúť neočakávané správanie (napríklad .xlsx). Tu je niekoľko bežných príkladov.

Neočakávané formátovanie hodnoty

Z dôvodu ACE je možné importovať hodnoty zo staršieho excelového zošita s menšou presnosťou alebo vernosťou, než by ste očakávali. Predstavte si napríklad, že excelový súbor obsahuje číslo 1024.231, ktoré ste formátovali na zobrazenie ako 1 024,23. Po importovaní do doplnku Power Query je táto hodnota znázornená ako textová hodnota 1 024,23 namiesto základného čísla s úplnou vernosťou (1024,231). Je to preto, lebo ACE v tomto prípade nevynorí základnú hodnotu do Power Query, ale len hodnotu, ktorá sa zobrazuje v Exceli.

Neočakávané hodnoty null

Keď ACE načíta hárok, znázorní prvých osem riadkov na určenie typov údajov stĺpcov. Ak prvých osem riadkov nie je reprezentatívnych pre neskoršie riadky, ACE môže použiť nesprávny typ na tento stĺpec a vrátiť hodnoty null pre akúkoľvek hodnotu, ktorá sa nezhoduje s typom. Ak napríklad stĺpec obsahuje čísla v prvých ôsmich riadkoch (napríklad 1 000, 1 001 atď.), ale obsahuje nečíselné údaje v neskorších riadkoch (napríklad "100Y" a "100Z"), ACE končí, že stĺpec obsahuje čísla a všetky nečíselné hodnoty sa vrátia ako null.

Nekonzistentné formátovanie hodnôt

V niektorých prípadoch vráti ACE úplne odlišné výsledky v rámci obnovení. Na základe príkladu opísaného v časti formátovania môžete náhle vidieť hodnotu 1024,231 namiesto hodnoty 1 024,23. Tento rozdiel môže spôsobovať otvorenie staršieho zošita v Exceli pri importovaní do Power Query. Ak chcete vyriešiť tento problém, zavrite zošit.

Chýbajúce alebo neúplné excelové údaje

Niekedy sa v Power Query nepodarí extrahovať všetky údaje z excelového hárka. Toto zlyhanie je často spôsobené tým, že hárok má nesprávne dimenzie (napríklad má dimenzie A1:C200 , keď skutočné údaje zaberajú viac ako tri stĺpce alebo 200 riadkov).

Ako diagnostikovať nesprávne dimenzie

Zobrazenie dimenzií hárka:

Súbor xlsx premenujte na .zip rozšírenie.
Otvorte súbor v Prieskumník.
Prejdite do xl\worksheets.
Skopírujte súbor XML pre problematický hárok (napríklad Sheet1.xml) zo súboru zip do iného umiestnenia.
Skontrolujte niekoľko prvých riadkov súboru. Ak je súbor dostatočne malý, otvorte ho v textovom editore. Ak je súbor príliš veľký na to, aby sa otvoril v textovom editore, spustite nasledujúci príkaz z príkazového riadka: viac Sheet1.xml.
Vyhľadajte <dimension .../> značku (napríklad <dimension ref="A1:C200" />).

Ak má súbor atribút dimenzie, ktorý odkazuje na jednu bunku (napríklad <dimension ref="A1" />), Power Query používa tento atribút na vyhľadanie počiatočného riadka a stĺpca údajov v hárku.

Ak má však súbor atribút dimenzie, ktorý odkazuje na viaceré bunky (napríklad <dimension ref="A1:AJ45000"/>), Power Query používa tento rozsah na vyhľadanie počiatočného riadka a stĺpca , ako aj koncového riadka a stĺpca. Ak tento rozsah neobsahuje všetky údaje na hárku, niektoré údaje sa nenačítajú.

Oprava nesprávnych dimenzií

Problémy spôsobené nesprávnymi dimenziami môžete vyriešiť vykonaním jednej z nasledujúcich akcií:

Otvorte a znova uložte dokument v Exceli. Táto akcia prepíše nesprávne dimenzie uložené v súbore správnou hodnotou.
Skontrolujte, či je nástroj vygenerovaný excelovým súborom fixný tak, aby bol výstupom dimenzií správne.
Aktualizujte dotaz jazyka M a ignorujte nesprávne dimenzie. Od vydania doplnku Power Query Excel.Workbook z decembra 2020 teraz podporuje možnosť InferSheetDimensions . Ak je hodnota True, táto možnosť spôsobí, že funkcia bude ignorovať dimenzie uložené v zošite a namiesto toho ich určiť na základe kontroly údajov.

Tu je príklad, ako poskytnúť túto možnosť:

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

Pomalé načítavanie excelových údajov môže byť spôsobené aj nesprávnymi dimenziami. V tomto prípade je však spomalenie spôsobené tým, že dimenzie sú oveľa väčšie, než je potrebné, a nie sú príliš malé. Príliš veľké dimenzie spôsobia, že doplnok Power Query načíta oveľa väčšie množstvo údajov zo zošita, ako je v skutočnosti potrebné.

Ak chcete tento problém vyriešiť, podrobné pokyny nájdete v téme Vyhľadanie a obnovenie poslednej bunky v hárku .

Pri načítavaní údajov z Excelu v počítači alebo zo SharePointu zvážte objem s tým spojených údajov, ako aj zložitosť zošita.

Pri načítavaní veľmi veľkých súborov zo SharePointu si všimnete zníženie výkonu. Ide však len o jednu časť problému. Ak máte v excelovom súbore načítanom zo SharePointu významnú obchodnú logiku, táto obchodná logika sa môže vykonať pri obnovení údajov, čo môže spôsobiť zložité výpočty. Zvážte agregáciu a predbežné výpočet údajov alebo presunutie väčšej časti obchodnej logiky z vrstvy Excelu do vrstvy Power Query.

Chyby pri použití excelového konektora na import súborov CSV

Aj keď je možné otvoriť súbory CSV v Exceli, nejde o excelové súbory. Namiesto toho použite konektor Text/CSV.

Chyba pri importovaní zošitov striktných hárkov XML s prísnym otvorením

Pri importovaní zošitov uložených vo formáte Striktný otvorený tabuľkový hárok XML v Exceli sa môže zobraziť nasledujúca chyba:

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

Táto chyba sa stane, keď ovládač ACE nie je nainštalovaný v hostiteľskom počítači. Zošity uložené vo formáte striktného otvoreného tabuľkového hárka XML môže čítať iba ACE. Keďže však takéto zošity používajú rovnakú príponu súboru ako bežné zošity Open XML (.xlsx), nemôžeme použiť rozšírenie na zobrazenie obvyklého the Access Database Engine OLEDB provider may be required to read this type of file chybového hlásenia.

Ak chcete vyriešiť chybu, nainštalujte ovládač ACE. Ak sa chyba vyskytne v cloudovej službe, budete musieť použiť bránu spustenú v počítači, ktorý má nainštalovaný ovládač ACE.

Chyby v súbore obsahujú poškodené údaje

Pri importovaní určitých excelových zošitov sa môže zobraziť nasledujúca chyba.

DataFormat.Error: File contains corrupted data.

Táto chyba zvyčajne naznačí, že sa vyskytol problém s formátom súboru.

Niekedy sa však táto chyba môže vyskytnúť, keď sa zdá, že súbor je súborom Open XML (napríklad .xlsx), ale ovládač ACE je skutočne potrebný na spracovanie súboru. Prejdite do časti Staršie ace konektora , kde nájdete ďalšie informácie o tom, ako spracovať súbory vyžadujúce ovládač ACE.

Známe problémy a obmedzenia

Power Query Online nemá prístup k šifrovaným excelovým súborom. Keďže excelové súbory označené inými typmi citlivosti ako "Verejné" alebo "Non-Business" sú šifrované, nie sú prístupné prostredníctvom nástroja Power Query Online.
Power Query Online nepodporuje excelové súbory chránené heslom.