Jaa

Excel

  • Artikkeli

Yhteenveto

Kohde Kuvaus
Julkaisutila Yleinen saatavuus
Tuotteet Excel
Power BI (semanttiset mallit)
Power BI (tietovuot)
Fabric (Tietovuo Gen2)
Power Apps (tietovuot)
Dynamics 365 Customer Insights
Analysis Services
Tuetut todennustyypit Anonyymi (online)
Perus (online)
Organisaation tili (online)
Funktioiden viitedokumentaatio Excel.Workbook
Excel.CurrentWorkbook

Muistiinpano

Jotkin ominaisuudet voivat olla olemassa yhdessä tuotteessa, mutta ei toisissa käyttöönottoaikataulujen ja isäntäkohtaisten ominaisuuksien vuoksi.

Edellytykset

Jotta voit muodostaa yhteyden vanhaan työkirjaan (kuten .xls tai .xlsb:hen), käyttöoikeustietokantamoduulin OLEDB (tai ACE) -palvelu vaaditaan. Jos haluat asentaa tämän palvelun, siirry lataussivulle ja asenna asianmukainen (32- tai 64-bittinen) versio. Jos sitä ei ole asennettu, saat seuraavan virheilmoituksen, kun muodostat yhteyden vanhoihin työkirjoihin:

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:a ei voi asentaa pilvipalveluympäristöihin. Jos siis näet tämän virheen pilvipalveluisännässä (kuten Power Query Onlinessa), sinun on käytettävä yhdyskäytävää, johon on asennettu ACE muodostaaksesi yhteyden vanhoihin Excel-tiedostoihin.

Tuetut toiminnot

  • Tuo

Excel-työkirjaan yhdistäminen Power Query Desktopista

Yhteyden muodostaminen Power Query Desktopista:

  1. Valitse Excel-työkirja Nouda tiedot -toiminnosta. Power Query Desktopin Nouda tiedot -käyttökokemus vaihtelee sovellusten välillä. Lisätietoja Power Query Desktopista saat sovelluksen tietojen käyttökokemuksesta artikkelista Mistä tiedot noudataan.

  2. Etsi ja valitse Excel-työkirja, jonka haluat ladata. Valitse sitten Avaa.

    Valitse Excel-työkirja Resurssienhallinnasta.

    Jos Excel-työkirja on online-tilassa, muodosta yhteys työkirjaan verkkoyhdistimen avulla.

  3. Valitse siirtymistoiminnossa haluamasi työkirjan tiedot ja valitse sitten Lataa ladataksesi tiedot tai Jatka tietojen muuntamista Power Query -editorissa valitsemalla Lataa.

    Power Query Desktopiin tuotu Excel-työkirja Siirtymistoimintoon.

Excel-työkirjaan yhdistäminen Power Query Onlinesta

Yhteyden muodostaminen Power Query Onlinesta:

  1. Valitse Excel-työkirja-vaihtoehto Nouda tiedot -toiminnosta. Eri sovelluksilla on eri tapoja päästä Power Query Onlinen tietojen noutamiseen. Lisätietoja siitä, miten pääset Power Query Onlinen tietojen noutamiseen sovelluksestasi, on kohdassa Mistä tiedot noudataan.

    Näyttökuva Nouda tiedot -ikkunasta, jossa on korostettu Excel-työkirja.

  2. Kirjoita avautuvaan Excel-valintaikkunaan Excel-työkirjan polku.

    Näyttökuva yhteyden tiedoista, joiden avulla voit käyttää Excel-työkirjaa.

  3. Valitse tarvittaessa paikallinen tietoyhdyskäytävä Excel-työkirjan käyttämistä varten.

  4. Jos tämä on ensimmäinen kerta, kun olet käyttänyt tätä Excel-työkirjaa, valitse todennuslaji ja kirjaudu tarvittaessa sisään tilillesi.

  5. Valitse siirtymistoiminnossa haluamasi työkirjan tiedot ja jatka sitten tietojen muuntamista Power Query -editorissa muuntamalla .

    Näyttökuva Excel-työkirjasta, joka on tuotu Power Query Online Navigatoriin.

Ehdotetut taulukot

Jos muodostat yhteyden Excel-työkirjaan, joka ei sisällä erityisesti yksittäistä taulukkoa, Power Query -siirtymistoiminto yrittää luoda ehdotetun luettelon taulukoista, joista voit valita. Otetaan esimerkiksi seuraava työkirjaesimerkki, joka sisältää tietoja A1:stä C5:een, lisää tietoja D8:sta E10:een ja lisää C13:sta F16:een.

Näyttökuva Excel-työkirjasta, jossa on kolme tietojoukkoa.

Kun muodostat yhteyden Power Queryn tietoihin, Power Query -siirtymistoiminto luo kaksi luetteloa. Ensimmäinen luettelo sisältää koko työkirjataulukon, ja toinen luettelo sisältää kolme ehdotettua taulukkoa.

Jos valitset siirtymistoiminnossa koko laskentataulukon, työkirja näytetään excelissä näkyvällä tavalla, ja kaikki tyhjät solut on täytetty tyhjäarvoilla.

Näyttökuva siirtymistoiminnosta, jossa yksittäinen taulukko näkyy tyhjäarvoina tyhjissä soluissa. Jos valitset jonkin ehdotetuista taulukoista, siirtymistoiminnossa näytetään jokainen yksittäinen taulukko, jonka Power Query pystyi selvittämään työkirjan asettelusta. Jos esimerkiksi valitset taulukon 3, soluissa C13–F16 alun perin näytetyt tiedot näytetään.

Näyttökuva siirtymistoiminnosta, jossa taulukko 3 on ehdotettujen taulukoiden alla ja taulukon 3 sisältö näkyvissä.

Muistiinpano

Jos taulukko muuttuu tarpeeksi, taulukko ei ehkä päivity oikein. Voit ehkä korjata päivityksen tuomalla tiedot uudelleen ja valitsemalla uuden ehdotetun taulukon.

Vianmääritys

Numeerinen tarkkuus (tai "Miksi numeroni muuttuivat?")

Kun tuot Excel-tietoja, saatat huomata, että tietyt lukuarvot näyttävät muuttuvan hieman, kun ne tuodaan Power Queryun. Jos esimerkiksi valitset Excelissä solun, joka sisältää arvon 0,049, tämä luku näkyy kaavarivillä muodossa 0,049. Mutta jos tuot saman solun Power Queryyn ja valitset sen, esikatselun tiedot näyttävät sen muodossa 0.049000000002 (vaikka esikatselutaulukossa se on muotoiltu muodossa 0,049). Mitä tapahtui?

Vastaus on hieman monimutkainen, ja se liittyy siihen, miten Excel tallentaa numerot käyttämällä niin sanottua binaarista liukulukumerkintää. Tärkeintä on, että on tiettyjä lukuja, joita Excel ei voi edustaa 100 prosentin tarkkuudella. Jos avaat .xlsx -tiedoston ja tarkastelet todellista tallennettua arvoa, huomaat, että .xlsx-tiedostossa 0,049 tallennetaan itse asiassa muodossa 0.04900000000000000000000. Tämä on arvo, jonka Power Query lukee .xlsx ja siten arvo, joka tulee näkyviin, kun valitset solun Power Queryssä. (Jos haluat lisätietoja Power Queryn numeerisesta tarkkuudesta , siirry kohteen "Desimaaliluku" ja "Kiinteä desimaaliluku" -osioihin.Tietotyypit Power Queryssa.)

Excelin online-työkirjaan yhdistäminen

Jos haluat muodostaa yhteyden SharePointissa isännöitävään Excel-asiakirjaan, voit tehdä sen Power BI Desktopissa, Excelissä ja tietovoissa olevan verkkoyhdistimen sekä tietovoiden Excel-liittimen avulla. Saat linkin tiedostoon seuraavasti:

  1. Avaa tiedosto Excel Desktopissa.
  2. Avaa Tiedosto-valikko, valitse Tiedot-välilehti ja valitse sitten Kopioi polku.
  3. Kopioi osoite Tiedostopolku- tai URL-osoite-kenttään ja poista ?web=1 osoitteen lopusta.

Vanha ACE-yhdistin

Power Query lukee vanhoja työkirjoja (kuten .xls tai .xlsb) Access-tietokantamoduulin (tai ACE) OLEDB-palvelun avulla. Tämän vuoksi saatat kohdata odottamattomia toimintoja tuoessasi vanhoja työkirjoja, joita ei tapahdu tuotaessa OpenXML-työkirjoja (esimerkiksi .xlsx). Seuraavassa on joitakin yleisiä esimerkkejä.

Arvon odottamaton muotoilu

ACE-toiminnon vuoksi vanhan Excel-työkirjan arvot saatetaan tuoda liian tarkasti tai tarkasti kuin oletit. Kuvitellaan esimerkiksi, että Excel-tiedostosi sisältää numeron 1024.231, jonka olet muotoillut näytettäväksi muodossa "1,024.23". Kun tämä arvo tuodaan Power Queryyn, se esitetään tekstiarvona "1,024.23" taustalla olevan täyden uskollisuusluvun (1024.231) sijaan. Tämä johtuu siitä, että tässä tapauksessa ACE ei näytä pohjana olevaa arvoa Power Querylle, vaan vain arvon sellaisena kuin se näkyy Excelissä.

Odottamattomat tyhjäarvot

Kun ACE lataa laskentataulukon, se tarkastelee kahdeksaa ensimmäistä riviä sarakkeiden tietotyyppien määrittämiseksi. Jos kahdeksan ensimmäistä riviä eivät vastaa myöhempiä rivejä, ACE voi käyttää kyseiseen sarakkeeseen virheellistä tyyppiä ja palauttaa tyhjäarvot mille tahansa arvolle, joka ei vastaa tyyppiä. Jos sarake sisältää esimerkiksi lukuja kahdeksan ensimmäisen rivin sisällä (kuten 1000, 1001 jne.), mutta sen myöhemmillä riveillä on ei-numeerisia tietoja (kuten "100Y" ja "100Z"), ACE päättelee, että sarake sisältää lukuja ja että kaikki muut kuin numeeriset arvot palautetaan tyhjäarvoina.

Epäyhtenäinen arvon muotoilu

Joissakin tapauksissa ACE palauttaa täysin erilaiset tulokset päivitysten välillä. Muotoilu-osiossa kuvatun esimerkin avulla saatat yhtäkkiä nähdä arvon 1024.231 arvon "1,024.23" sijaan. Tämä ero voi johtua siitä, että vanha työkirja avataan Excelissä tuotaessa sitä Power Queryen. Voit ratkaista tämän ongelman sulkemalla työkirjan.

Puuttuvat tai epätäydelliset Excel-tiedot

Joskus Power Query ei onnistu poimimaan kaikkia tietoja Excel-laskentataulukosta. Tämä virhe johtuu usein siitä, että laskentataulukon dimensiot ovat virheelliset (esimerkiksi silloinA1:C200, kun todellisissa tiedoissa on yli kolme saraketta tai 200 riviä).

Virheellisten dimensioiden vianmääritys

Voit tarkastella laskentataulukon dimensioita seuraavasti:

  1. Nimeä xlsx-tiedosto uudelleen .zip tunnisteella.
  2. Avaa tiedosto Resurssienhallinnassa.
  3. Siirry xl\laskentataulukoihin.
  4. Kopioi ongelmallisen laskentataulukon XML-tiedosto (esimerkiksi Sheet1.xml) zip-tiedostosta toiseen sijaintiin.
  5. Tarkasta tiedoston ensimmäiset rivit. Jos tiedosto on tarpeeksi pieni, avaa se tekstieditorissa. Jos tiedosto on liian suuri avattavaksi tekstieditorissa, suorita seuraava komento komentokehotteesta: lisää Sheet1.xml.
  6. Etsi <dimension .../> tunniste (esimerkiksi <dimension ref="A1:C200" />).

Jos tiedostossa on dimensiomäärite, joka osoittaa yhteen soluun (esimerkiksi <dimension ref="A1" />), Power Query käyttää tätä määritettä löytääkseen laskentataulukon tietojen aloitusrivin ja sarakkeen.

Jos tiedostossa on dimensiomäärite, joka osoittaa useisiin soluihin (esimerkiksi <dimension ref="A1:AJ45000"/>), Power Query käyttää tätä aluetta etsiäkseen aloitusrivin ja -sarakkeen sekä loppurivin ja -sarakkeen. Jos tämä alue ei sisällä kaikkia laskentataulukossa olevia tietoja, joitakin tietoja ei ladata.

Virheellisten dimensioiden korjaaminen

Voit korjata virheellisten dimensioiden aiheuttamat ongelmat tekemällä jonkin seuraavista toimista:

  • Avaa tiedosto ja tallenna se uudelleen Excelissä. Tämä toiminto korvaa tiedostoon tallennetut virheelliset dimensiot oikealla arvolla.

  • Varmista, että Excel-tiedoston luonut työkalu on kiinteä, jotta dimensiot voidaan tulostaa oikein.

  • Päivitä M-kysely virheellisten dimensioiden ohittamiseksi. Power Queryn Excel.Workbook joulukuun 2020 julkaisuversiosta alkaen tukee nyt vaihtoehtoa InferSheetDimensions . Kun arvo on tosi, tämä asetus aiheuttaa sen, että funktio ohittaa työkirjaan tallennetut dimensiot ja sen sijaan määrittää ne tarkastelemalla tietoja.

    Tässä on esimerkki tämän asetuksen määrittämisestä:

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

Hidas tai hidas suorituskyky Excel-tietoja ladattaessa

Virheelliset dimensiot voivat aiheuttaa myös Excel-tietojen hitaan lataamisen. Tässä tapauksessa hitauden aiheuttaa kuitenkin se, että dimensiot ovat paljon suurempia kuin niiden pitäisi olla, sen sijaan, että ne olisivat liian pieniä. Liian suuret dimensiot aiheuttavat sen, että Power Query lukee työkirjasta paljon suuremman määrän tietoja kuin mitä todellisuudessa tarvitaan.

Voit korjata tämän ongelman katsomalla etsimällä ja nollaamalla laskentataulukon viimeisen solun ja katsomalla tarkat ohjeet.

Huono suorituskyky ladattaessa tietoja SharePointista

Kun noutat tietoja Excelistä koneellasi tai SharePointista, ota huomioon sekä kyseessä olevien tietojen määrä että työkirjan monimutkaisuus.

Huomaat suorituskyvyn heikentyvän, kun noudetaan erittäin suuria tiedostoja SharePointista. Tämä on kuitenkin vain yksi osa ongelmaa. Jos sinulla on merkittävää liiketoimintalogiikkaa Excel-tiedostossa, joka noudetaan SharePointista, tämä liiketoimintalogiikka on ehkä suoritettava, kun päivität tiedot, mikä voi aiheuttaa monimutkaisia laskutoimituksia. Harkitse tietojen koostamista ja esilaskemista tai liiketoimintalogiikan siirtämistä Excel-tasosta Power Query -tasoon.

Virheet käytettäessä Excel-yhdistintä CSV-tiedostojen tuomiseen

VAIKKA CSV-tiedostoja voi avata Excelissä, ne eivät ole Excel-tiedostoja. Käytä sen sijaan Teksti- /CSV-liitintä .

Virhe tuotaessa "tiukkaa avointa XML-laskentataulukkoa" -työkirjoja

Saatat saada seuraavan virheilmoituksen, kun tuot Excelin tiukassa avoimessa XML-laskentataulukossa tallennettuja työkirjoja:

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

Tämä virhe ilmenee, kun ACE-ohjainta ei ole asennettu isäntätietokoneeseen. Vain ACE voi lukea työkirjat, jotka on tallennettu tiukassa avoimessa XML-laskentataulukossa. Koska tällaisilla työkirjoilla käytetään kuitenkin samaa tiedostotunnistetta kuin tavallisilla Open XML -työkirjoilla (.xlsx), emme voi käyttää laajennusta tavallisen the Access Database Engine OLEDB provider may be required to read this type of file virhesanoman näyttämiseen.

Ratkaise virhe asentamalla ACE-ohjain. Jos virhe esiintyy pilvipalvelussa, sinun on käytettävä yhdyskäytävää, joka suoritetaan tietokoneessa, johon on asennettu ACE-ohjain.

"Tiedosto sisältää vioittuneita tietoja" -virheet

Saatat saada seuraavan virheilmoituksen, kun tuot tiettyjä Excel-työkirjoja.

DataFormat.Error: File contains corrupted data.

Yleensä tämä virhe ilmaisee, että tiedoston muotoilussa on ongelma.

Joskus tämä virhe voi kuitenkin tapahtua, kun tiedosto näyttää olevan Avoin XML-tiedosto (kuten .xlsx), mutta ace-ohjainta tarvitaan todellisuudessa tiedoston käsittelyyn. Siirry Vanha ACE -yhdistin -osioon, jossa on lisätietoja siitä, miten voit käsitellä tiedostoja, jotka edellyttävät ACE-ohjainta.

Tunnetut ongelmat ja rajoitukset

  • Power Query Online ei pysty käyttämään salattuja Excel-tiedostoja. Koska excel-tiedostot, joiden luottamuksellisuustyyppi on muu kuin "julkinen" tai "muu kuin yritys", salataan, ne eivät ole käytettävissä Power Query Onlinen kautta.
  • Power Query Online ei tue salasanalla suojattuja Excel-tiedostoja.
  • Excel.Workbook-asetus useHeaders muuntaa luvut ja päivämäärät tekstiksi käyttäen nykyistä maa-asetusta ja käyttäytyy siten eri tavalla, kun niitä suoritetaan ympäristöissä, joissa on määritetty eri käyttöjärjestelmän maa-asetus. Suosittelemme sen sijaan table.PromoteHeaders-toiminnon käyttämistä.