Excel

Összegzés

Elem	Leírás
Kiadási állapot	Általános rendelkezésre állás
Termékek	Excel Power BI (Szemantikai modellek) Power BI (Adatfolyamok) Háló (Adatfolyam Gen2) Power Apps (Adatfolyamok) Dynamics 365 Customer Insights Analysis Services
Támogatott hitelesítési típusok	Névtelen (online) Alapszintű (online) Szervezeti fiók (online)
Függvényreferenciák dokumentációja	Excel.Munkafüzet Excel.CurrentWorkbook

Feljegyzés

Egyes képességek egy termékben lehetnek jelen, másokat azonban az üzembehelyezési ütemezések és a gazdagépspecifikus képességek miatt.

Előfeltételek

Ha régi munkafüzethez (például .xls vagy .xlsb) szeretne csatlakozni, az Access-adatbázismotor OLEDB -szolgáltatója (vagy ACE) szükséges. A szolgáltató telepítéséhez nyissa meg a letöltési lapot , és telepítse a megfelelő (32 bites vagy 64 bites) verziót. Ha nincs telepítve, a következő hibaüzenet jelenik meg az örökölt munkafüzetekhez való csatlakozáskor:

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.

Az ACE nem telepíthető felhőszolgáltatás-környezetekben. Ha tehát ezt a hibát egy felhőszolgáltatóban (például a Power Query Online-ban) látja, akkor egy olyan átjárót kell használnia, amelyen telepítve van az ACE az örökölt Excel-fájlokhoz való csatlakozáshoz.

Támogatott képességek

• Importálás

Csatlakozás Excel-munkafüzetbe a Power Query Desktopból

A Kapcsolat létrehozása a Power Query Desktopból:

1. Válassza az Excel-munkafüzet lehetőséget az adatbeolvasási felületen. A Power Query Desktop adatbeolvasási felülete alkalmazásonként eltérő. Ha többet szeretne tudni arról, hogy a Power Query Desktop hogyan szerezhet be adatokat az alkalmazáshoz, lépjen a Hol lehet adatokat lekérni.

2. Keresse meg és jelölje ki a betölteni kívánt Excel-munkafüzetet. Ezután válassza a Megnyitás lehetőséget.

   

   Ha az Excel-munkafüzet online állapotban van, a webes összekötő használatával csatlakozzon a munkafüzethez.

3. A Kezelőben jelölje ki a munkafüzet kívánt adatait, majd válassza a Betöltés lehetőséget az adatok betöltéséhez, vagy az Adatok átalakítása lehetőséget az adatok Power Query-szerkesztő történő átalakításához.

   

Csatlakozás Excel-munkafüzetbe a Power Query Online-ból

A Kapcsolat létrehozása a Power Query Online-ból:

1. Válassza az Excel-munkafüzet lehetőséget az adatbeolvasási felületen. A különböző alkalmazások különböző módokon érhetik el a Power Query Online-t az adatélmény eléréséhez. Ha többet szeretne tudni arról, hogyan juthat el a Power Query Online-hoz, hogyan szerezhet be adatokat az alkalmazásból, lépjen a Hol kérhet le adatokat.

   

2. A megjelenő Excel párbeszédpanelen adja meg az Excel-munkafüzet elérési útját.

   

3. Ha szükséges, válasszon ki egy helyszíni adatátjárót az Excel-munkafüzet eléréséhez.

4. Ha először fér hozzá ehhez az Excel-munkafüzethez, válassza ki a hitelesítési típust, és jelentkezzen be a fiókjába (ha szükséges).

5. A Kezelőben jelölje ki a munkafüzet kívánt adatait, majd az Adatok átalakítása parancsot a Power Query-szerkesztő adatainak további átalakításához.

   

Javasolt táblák

Ha olyan Excel-munkafüzethez csatlakozik, amely nem tartalmaz külön táblát, a Power Query kezelője megpróbál létrehozni egy javasolt táblázatlistát, amelyből választhat. Vegyük például az alábbi munkafüzet-példát, amely az A1 és C5 közötti adatokat, a D8 és az E10 közötti adatokat, valamint a C13 és az F16 közötti adatokat tartalmazza.

Amikor a Power Queryben csatlakozik az adatokhoz, a Power Query kezelője két listát hoz létre. Az első lista a teljes munkafüzetlapot, a második pedig három javasolt táblát tartalmaz.

Ha kijelöli a teljes munkalapot a kezelőben, a munkafüzet az Excelben megjelenő módon jelenik meg, és az összes üres cella null értékű.

Ha kiválasztja a javasolt táblák egyikét, a kezelőben minden egyes tábla, amelyet a Power Query a munkafüzet elrendezéséből tudott meghatározni, megjelenik. Ha például a 3. táblázatot választja, az eredetileg a C13–F16 cellákban megjelenő adatok jelennek meg.

Feljegyzés

Ha a lap megfelelően változik, előfordulhat, hogy a táblázat nem frissül megfelelően. Előfordulhat, hogy az adatok ismételt importálásával és egy új javasolt tábla kiválasztásával kijavíthatja a frissítést.

Hibaelhárítás

Numerikus pontosság (vagy "Miért változtak a számok?")

Az Excel-adatok importálásakor előfordulhat, hogy bizonyos számértékek kissé megváltoznak a Power Querybe való importáláskor. Ha például egy 0,049-et tartalmazó cellát jelöl ki az Excelben, ez a szám a szerkesztőlécen 0,049-ként jelenik meg. Ha azonban ugyanazt a cellát importálja a Power Querybe, és kijelöli, az előnézet részletei 0,049000000000002 formátumban jelennek meg (annak ellenére, hogy az előnézeti táblázatban 0,049-ként van formázva). Mi folyik itt?

A válasz egy kicsit bonyolult, és ahhoz van köze, hogy az Excel hogyan tárolja a számokat egy bináris lebegőpontos jelöléssel. A lényeg az, hogy vannak olyan számok, amelyeket az Excel nem tud 100%-os pontossággal ábrázolni. Ha feltöri a .xlsx fájlt, és megtekinti a ténylegesen tárolt értéket, látni fogja, hogy a .xlsx fájlban a 0.049 valójában 0.0490000000000000002 formátumban van tárolva. Ez az érték, amelyet a Power Query a .xlsx olvas be, és így az az érték, amely akkor jelenik meg, amikor kijelöli a cellát a Power Queryben. (A Power Query numerikus pontosságával kapcsolatos további információkért lépjen a "Tizedesszám" és a "Rögzített tizedesszám" szakaszra. Adattípusok a Power Queryben.)

online Excel-munkafüzet Csatlakozás

Ha a SharePointban üzemeltetett Excel-dokumentumhoz szeretne csatlakozni, ezt megteheti a Power BI Desktop, az Excel és az Adatfolyamok webes összekötőjével, valamint az Adatfolyamok Excel-összekötőjével is. A fájlra mutató hivatkozás lekérése:

1. Nyissa meg a dokumentumot az Excel Desktopban.
2. Nyissa meg a Fájl menüt, válassza az Információ lapot, majd válassza az Elérési út másolása lehetőséget.
3. Másolja a címet a Fájl elérési útja vagy URL-cím mezőjébe, és távolítsa el a ?web=1 fájlt a cím végéről.

Örökölt ACE-összekötő

A Power Query régebbi munkafüzeteket (például .xls vagy .xlsb) olvas be az Access-adatbázismotor (vagy ACE) OLEDB-szolgáltatójának használatával. Emiatt előfordulhat, hogy váratlan viselkedés tapasztalható az OpenXML-munkafüzetek (például .xlsx) importálásakor nem előforduló örökölt munkafüzetek importálásakor. Íme néhány gyakori példa.

Váratlan értékformázás

Az ACE miatt előfordulhat, hogy egy régi Excel-munkafüzet értékei a vártnál kisebb pontossággal vagy hűséggel importálhatók. Tegyük fel például, hogy az Excel-fájl az 1024.231-es számot tartalmazza, amelyet "1024.23" formátumban formázott. A Power Querybe importálva ez az érték "1024.23" szöveges értékként jelenik meg a mögöttes teljes hűségszám (1024.231) helyett. Ennek az az oka, hogy ebben az esetben az ACE nem jeleníti meg a mögöttes értéket a Power Queryben, hanem csak az Excelben megjelenített értéket.

Váratlan null értékek

Amikor az ACE betölt egy lapot, az első nyolc sort tekinti meg az oszlopok adattípusainak meghatározásához. Ha az első nyolc sor nem felel meg a későbbi soroknak, előfordulhat, hogy az ACE helytelen típust alkalmaz az adott oszlopra, és null értékeket ad vissza minden olyan értékhez, amely nem felel meg a típusnak. Ha például egy oszlop az első nyolc sorban (például 1000, 1001 stb.) tartalmaz számokat, de a későbbi sorokban (például "100Y" és "100Z") nem numerikus adatokat tartalmaz, az ACE arra a következtetésre jut, hogy az oszlop számokat tartalmaz, és a nem numerikus értékek null értéket adnak vissza.

Inkonzisztens értékformázás

Bizonyos esetekben az ACE teljesen eltérő eredményeket ad vissza a frissítések között. A formázási szakaszban ismertetett példában hirtelen megjelenhet az 1024.231 érték az "1024.23" helyett. Ezt a különbséget az okozhatja, hogy az örökölt munkafüzet meg van nyitva az Excelben, miközben importálja a Power Querybe. A probléma megoldásához zárja be a munkafüzetet.

Hiányzó vagy hiányos Excel-adatok

Előfordulhat, hogy a Power Query nem tudja kinyerni az összes adatot egy Excel-munkalapról. Ezt a hibát gyakran a munkalap helytelen dimenziói okozzák (például ha A1:C200 a tényleges adatok háromnál több oszlopot vagy 200 sort foglalnak el).

Helytelen dimenziók diagnosztizálása

Munkalap dimenzióinak megtekintése:

1. Nevezze át az xlsx-fájlt egy .zip kiterjesztéssel.
2. Nyissa meg a fájlt a Fájlkezelő.
3. Navigáljon az xl\munkalapokra.
4. Másolja ki a problémás lap xml-fájlját (például Sheet1.xml) a zip-fájlból egy másik helyre.
5. Vizsgálja meg a fájl első néhány sorát. Ha a fájl elég kicsi, nyissa meg egy szövegszerkesztőben. Ha a fájl túl nagy a szövegszerkesztőben való megnyitáshoz, futtassa a következő parancsot egy parancssorból: több Sheet1.xml.
6. Keressen egy címkét <dimension .../> (például <dimension ref="A1:C200" />).

Ha a fájl dimenzióattribútuma egyetlen cellára mutat (például <dimension ref="A1" />), a Power Query ezzel az attribútummal keresi meg a munkalapon lévő adatok kezdősorát és oszlopát.

Ha azonban a fájl olyan dimenzióattribútummal rendelkezik, amely több cellára mutat (például <dimension ref="A1:AJ45000"/>), a Power Query ezzel a tartománnyal keresi meg a kezdő sort és oszlopot , valamint a záró sort és oszlopot. Ha ez a tartomány nem tartalmazza az összes adatot a lapon, az adatok egy része nem töltődik be.

Helytelen dimenziók javítása

A helytelen dimenziók által okozott problémákat az alábbi műveletek egyikével háríthatja el:

• Nyissa meg és mentse újra a dokumentumot az Excelben. Ez a művelet felülírja a fájlban tárolt helytelen dimenziókat a megfelelő értékkel.

• Győződjön meg arról, hogy az Excel-fájlt létrehozó eszköz ki van javítva a dimenziók helyes kimenetéhez.

• Frissítse az M-lekérdezést, hogy figyelmen kívül hagyja a helytelen dimenziókat. A Power Query Excel.Workbook 2020. decemberi kiadása már támogatja a InferSheetDimensions lehetőséget. Ha igaz, ez a beállítás azt eredményezi, hogy a függvény figyelmen kívül hagyja a munkafüzetben tárolt dimenziókat, és ehelyett az adatok vizsgálatával határozza meg őket.

  Íme egy példa a beállítás megadására:

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

Lassú vagy lassú teljesítmény az Excel-adatok betöltésekor

Az Excel-adatok lassú betöltését a helytelen dimenziók is okozhatják. Ebben az esetben azonban a lassúságot az okozza, hogy a méretek sokkal nagyobbak, mint amennyire szükség van, és nem túl kicsik. A túl nagy méretek miatt a Power Query a ténylegesen szükségesnél sokkal nagyobb mennyiségű adatot olvas be a munkafüzetből.

A probléma megoldásához részletes útmutatásért tekintse meg a munkalap utolsó cellájának megkeresését és alaphelyzetbe állítását.

Gyenge teljesítmény az adatok SharePointból való betöltésekor

Az Excelből a gépről vagy a SharePointból való lekéréskor vegye figyelembe az érintett adatok mennyiségét és a munkafüzet összetettségét is.

A sharepointból nagy méretű fájlok lekérésekor teljesítménycsökkenést fog tapasztalni. Ez azonban csak egy része a problémának. Ha jelentős üzleti logikája van egy Excel-fájlban, amelyet a SharePointból kér le, előfordulhat, hogy ezt az üzleti logikát végre kell hajtania az adatok frissítésekor, ami bonyolult számításokat okozhat. Fontolja meg az adatok összesítését és előzetes kiszámítását, vagy az üzleti logika nagyobb részének áthelyezését az Excel-rétegből a Power Query-rétegbe.

Hibák az Excel-összekötő CSV-fájlok importálásakor

Annak ellenére, hogy a CSV-fájlok megnyithatók az Excelben, nem Excel-fájlok. Használja inkább a Text/CSV összekötőt .

Hiba a "Szigorú xml-számolótábla megnyitása" munkafüzetek importálásakor

A következő hibaüzenet jelenhet meg az Excel "Szigorú xml-számolótábla megnyitása" formátumban mentett munkafüzetek importálásakor:

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

Ez a hiba akkor fordul elő, ha az ACE-illesztő nincs telepítve a gazdaszámítógépen. A "Szigorú nyílt XML-számolótábla" formátumban mentett munkafüzeteket csak az ACE tudja olvasni. Mivel azonban az ilyen munkafüzetek ugyanazt a fájlkiterjesztést használják, mint a hagyományos Open XML-munkafüzetek (.xlsx), a bővítmény nem használható a szokásos the Access Database Engine OLEDB provider may be required to read this type of file hibaüzenet megjelenítésére.

A hiba megoldásához telepítse az ACE-illesztőt. Ha a hiba egy felhőszolgáltatásban jelentkezik, egy olyan számítógépen futó átjárót kell használnia, amelyen telepítve van az ACE-illesztőprogram.

"A fájl sérült adatokat tartalmaz" hibaüzenetek

Bizonyos Excel-munkafüzetek importálásakor a következő hibaüzenet jelenhet meg.

DataFormat.Error: File contains corrupted data.

Ez a hiba általában azt jelzi, hogy probléma van a fájl formátumával.

Előfordulhat azonban, hogy ez a hiba akkor fordulhat elő, ha egy fájl open XML-fájlnak tűnik (például .xlsx), de a fájl feldolgozásához valójában az ACE-illesztőre van szükség. Az ACE-illesztőt igénylő fájlok feldolgozásával kapcsolatos további információkért lépjen az örökölt ACE-összekötő szakaszra.

Ismert problémák és korlátozások

• A Power Query Online nem tud hozzáférni a titkosított Excel-fájlokhoz. Mivel a bizalmassági típusok nem "Nyilvános" vagy "Nem üzleti" címkével ellátott Excel-fájlok titkosítva vannak, nem érhetők el a Power Query Online-on keresztül.
• A Power Query Online nem támogatja a jelszóval védett Excel-fájlokat.