Jaa

Excel

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, näyttöön tulee seuraava virhe, kun muodostetaan yhteys 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 pilvipalvelussa (kuten Power Query Onlinessa), sinun on käytettävä yhdyskäytävää, johon on asennettu ACE, jotta voit muodostaa 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.

    Näyttökuva Resurssienhallinnasta, jossa Excel-työkirja on valittuna.

    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.

    Näyttökuva Power Query työpöytäsiirtymistoiminnosta, johon on tuotu Excel-työkirja.

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 käytät tätä Excel-työkirjaa ensimmäistä kertaa, valitse todennustyyppi ja kirjaudu tilillesi (tarvittaessa).

  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-siirtymistoimintoon.

Ehdotetut taulukot

Jos muodostat yhteyden Excel-työkirjaan, joka ei sisällä 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 yksi taulukko näkyy tyhjäarvoilla tyhjissä soluissa.

Jos valitset jonkin ehdotetuista taulukoista, jokainen yksittäinen taulukko, jonka Power Query pystyi määrittämään työkirjan asettelusta, näkyy siirtymistoiminnossa. Jos esimerkiksi valitset taulukon 3, soluissa C13–F16 alun perin näytetyt tiedot näytetään.

Näyttökuva siirtymistoiminnosta, jossa taulukko 3 on valittu Ehdotetut taulukot -kohdassa ja taulukon 3 sisältö näytetään.

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 Query. 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 katsot todellista tallennettavaa arvoa, huomaat, että .xlsx tiedostossa 0,049 on itse asiassa tallennettu muodossa 0,04900000000000000002. Tämä luku on arvo, jonka Power Query lukee .xlsxja siten arvo, joka tulee näkyviin, kun valitset solun Power Query. (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-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 riippuvuuden vuoksi saatat kohdata odottamattomia toimintoja tuotaessa vanhoja työkirjoja, joita ei esiinny tuotaessa OpenXML-työkirjoja (kuten .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. Oletetaan 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ä ongelma ilmenee, koska tässä tapauksessa ACE näyttää Power Queryyn vain Excelissä näkyvän arvon, ei pohjana olevaa lukua.

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 edusta myöhempiä rivejä, ACE saattaa käyttää väärää tyyppiä kyseisessä sarakkeessa ja palauttaa tyhjäarvoja kaikille arvoille, jotka eivät 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 taulukon tietoja, osaa tiedoista 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 saa funktion ohittamaan työkirjaan tallennetut dimensiot ja määrittämään ne sen sijaan tarkistamalla 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 mitat voivat myös hidastaa Excel-tietojen lataamista. Tässä tapauksessa mitat, jotka ovat paljon suurempia kuin niiden pitäisi olla, aiheuttavat kuitenkin hitautta sen sijaan, että ne olisivat liian pieniä. Liian suuret dimensiot saavat Power Query lukemaan työkirjasta paljon suuremman määrän tietoja kuin 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 noudat tietoja Excelistä tietokoneellasi tai SharePointista, ota huomioon sekä tietojen määrä että työkirjan monimutkaisuus.

Saatat huomata suorituskyvyn heikkenemistä, kun haet erittäin suuria tiedostoja SharePointista. Tiedostokoko on kuitenkin vain yksi osa ongelmaa. Jos SharePointista noudettavassa Excel-tiedostossa on merkittävää liiketoimintalogiikkaa, tämä liiketoimintalogiikka on ehkä suoritettava, kun päivität tietojasi, mikä voi aiheuttaa monimutkaisia laskutoimituksia. Harkitse tietojen koostamista ja esilaskemista tai liiketoimintalogiikan siirtämistä Excel-tasolta Power Query -kerrokseen.

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 "Strict Open XML Spreadsheet" -muodossa tallennettuja työkirjoja. 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 ilmenee pilvipalvelussa, sinun on käytettävä yhdyskäytävää, joka toimii 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 osoittaa, että tiedoston muodossa on ongelma.

Joskus tämä virhe voi kuitenkin tapahtua, kun tiedosto näyttää olevan Open XML -tiedosto (kuten .xlsx), mutta tiedoston käsittelyyn tarvitaan ACE-ohjainta. 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-asetususeHeaders 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ä.
  • Last updated on