Rakenna taulukoita eri otsikoilla useista kirjoista

Ongelman muotoilu

Meillä on useita tiedostoja (esimerkissämme - 4 kpl, yleensä - niin monta kuin haluat) yhdessä kansiossa Raportit:

Sisällä nämä tiedostot näyttävät tältä:

Jossa:

• Tarvittava tietolehti kutsutaan aina Valokuvat, mutta se voi olla missä tahansa työkirjan kohdassa.
  
• Arkin ulkopuolella Valokuvat Jokaisessa kirjassa voi olla muita arkkeja.
• Tietoja sisältävissä taulukoissa on eri määrä rivejä, ja ne voivat alkaa eri rivillä laskentataulukossa.
• Eri taulukoiden samojen sarakkeiden nimet voivat vaihdella (esim. Määrä = määrä = määrä).
• Taulukoiden sarakkeet voidaan järjestää eri järjestykseen.

Tehtävä: kerää myyntitiedot kaikista taulukon tiedostoista Valokuvat yhdeksi yhteiseksi taulukoksi, jotta siihen voidaan myöhemmin rakentaa yhteenveto tai muuta analytiikkaa.

Vaihe 1. Sarakkeiden nimien hakemiston valmistelu

Ensimmäinen asia on valmistella hakuteos, jossa on kaikki mahdolliset sarakkeiden nimien vaihtoehdot ja niiden oikea tulkinta:

Muunnamme tämän luettelon dynaamiseksi "älykkääksi" taulukoksi käyttämällä välilehden Muotoile taulukkona -painiketta Koti (Etusivu — Muotoile taulukkona) tai pikanäppäimellä Ctrl+T ja lataa se Power Queryyn komennolla Tiedot – taulukosta/alueesta (Tiedot – taulukosta/alueesta). Excelin uusimmissa versioissa se on nimetty uudelleen muotoon Lehtien kanssa (Arkista).

Poistamme vaiheen perinteisesti Power Queryn kyselyeditori-ikkunassa Vaihtunut tyyppi ja lisää uusi vaihe sen sijaan napsauttamalla painiketta f_xkaavapalkissa (jos se ei ole näkyvissä, voit ottaa sen käyttöön välilehdellä Arvostelu) ja kirjoita sinne kaava sisäänrakennetulla Power Queryn kielellä M:

=Table.ToRows(Lähde)

Tämä komento muuntaa edellisessä vaiheessa ladatun lähde viitetaulukko listaksi, joka koostuu sisäkkäisistä listoista (List), joista jokainen on puolestaan ​​arvopari Siitä tuli - tuli yhdeltä riviltä:

Tarvitsemme tämän tyyppisiä tietoja hieman myöhemmin, kun nimeämme uudelleen kaikkien ladattujen taulukoiden otsikot.

Kun muunnos on valmis, valitse komennot Etusivu — Sulje ja lataa — Sulje ja lataa… ja tuontityyppi Luo vain yhteys (Koti — Sulje&Lataa — Sulje&Lataa kohteeseen... — Luo vain yhteys) ja palaa Exceliin.

Vaihe 2. Lataamme kaiken kaikista tiedostoista sellaisenaan

Ladataan nyt kaikkien tiedostojemme sisältö kansiosta – toistaiseksi sellaisenaan. Joukkueiden valinta Tiedot – Hae tiedot – Tiedostosta – Kansiosta (Data — Hae tiedot — Tiedostosta — Kansiosta) ja sitten kansio, jossa lähdekirjamme ovat.

Napsauta esikatseluikkunassa Muuntaa (Muuttaa) or Muutos (Muokata):

Laajenna sitten kaikkien ladattujen tiedostojen sisältö (Binääri) painiketta, jossa on kaksoisnuolet sarakkeen otsikossa Sisältö:

Power Query ensimmäisen tiedoston esimerkissä (Vostok.xlsx) kysyy meiltä sen arkin nimeä, jonka haluamme ottaa kustakin työkirjasta – valitse Valokuvat ja paina OK:

Sen jälkeen (itse asiassa) tapahtuu useita tapahtumia, jotka eivät ole ilmeisiä käyttäjälle, joiden seuraukset näkyvät selvästi vasemmassa paneelissa:

1. Power Query ottaa ensimmäisen tiedoston kansiosta (meillä on se Vostok.xlsx — nähdä Tiedostoesimerkki) esimerkkinä ja tuo sen sisällön luomalla kyselyn Muunna näytetiedosto. Tämä kysely sisältää joitain yksinkertaisia ​​vaiheita, kuten lähde (tiedostojen käyttöoikeus) suunnistus (arkkien valinta) ja mahdollisesti otsikoiden nostaminen. Tämä pyyntö voi ladata tietoja vain yhdestä tietystä tiedostosta Vostok.xlsx.
   
2. Tämän pyynnön perusteella luodaan siihen liittyvä toiminto Muunna tiedosto (merkitty tyypillisellä kuvakkeella f_x), jossa lähdetiedosto ei ole enää vakio, vaan muuttujaarvo – parametri. Siten tämä funktio voi poimia tietoja mistä tahansa kirjasta, jonka liukastamme siihen argumenttina.
   
3. Toimintoa sovelletaan vuorotellen jokaiseen sarakkeen tiedostoon (binaariin). Sisältö – Step on vastuussa tästä Kutsu mukautettua toimintoa kyselyssämme, joka lisää sarakkeen tiedostoluetteloon Muunna tiedosto tuontitulokset kustakin työkirjasta:

   

4. Ylimääräiset sarakkeet poistetaan.
5. Sisäkkäisten taulukoiden sisältö on laajennettu (vaihe Laajennettu taulukon sarake) – ja näemme kaikkien kirjojen tiedonkeruun lopulliset tulokset:

   
   

Vaihe 3. Hionta

Edellinen kuvakaappaus osoittaa selvästi, että suora kokoonpano "sellaisenaan" osoittautui huonolaatuiseksi:

• Sarakkeet ovat käänteisiä.
• Useita ylimääräisiä rivejä (tyhjiä eikä vain).
• Taulukon otsikoita ei pidetä otsikoina, ja ne sekoitetaan tietojen kanssa.
  

Voit korjata kaikki nämä ongelmat erittäin helposti – vain säädä Muunna näytetiedosto -kyselyä. Kaikki siihen tekemämme säädöt kuuluvat automaattisesti siihen liittyvään Muunna tiedosto -toimintoon, mikä tarkoittaa, että niitä käytetään myöhemmin, kun tuodaan tietoja kustakin tiedostosta.

Avaamalla pyynnön Muunna näytetiedosto, lisää vaiheita tarpeettomien rivien suodattamiseksi (esimerkiksi sarakkeen mukaan Column2) ja nostamalla otsikot painikkeella Käytä ensimmäistä riviä otsikoina (Käytä ensimmäistä riviä otsikoina). Pöytä näyttää paljon paremmalta.

Jotta eri tiedostojen sarakkeet mahtuisivat myöhemmin automaattisesti toistensa alle, ne on nimettävä samalla tavalla. Voit suorittaa tällaisen massa uudelleennimeämisen aiemmin luodun hakemiston mukaan yhdellä M-koodirivillä. Painamme painiketta uudelleen f_x kaavapalkissa ja lisää muutettava funktio:

= Taulukko.RenameColumns(#”Kohotetut otsikot”, Otsikot, Puuttuva kenttä.Ohita)

Tämä toiminto ottaa taulukon edellisestä vaiheesta Korotetut otsikot ja nimeää kaikki sen sarakkeet uudelleen sisäkkäisen hakuluettelon mukaan Pääotsikot. Kolmas argumentti Puuttuva kenttä. Ohita tarvitaan, jotta niissä otsikoissa, jotka ovat hakemistossa, mutta jotka eivät ole taulukossa, ei tapahdu virhettä.

Itse asiassa siinä kaikki.

Paluu pyyntöön Raportit näemme täysin erilaisen kuvan – paljon hienomman kuin edellinen:

• Mitä ovat Power Query, Power Pivot, Power BI ja miksi Excel-käyttäjä tarvitsee niitä
  
• Tietojen kerääminen kaikista tietyn kansion tiedostoista
• Tietojen kerääminen kirjan kaikista lehdistä yhteen taulukkoon