Sisällys
Ongelman muotoilu
Syöttötietona meillä on Excel-tiedosto, jossa yksi taulukoista sisältää useita taulukoita seuraavan muodon myyntitiedoilla:
Ota huomioon, että:
- Erikokoisia taulukoita ja eri tuotesarjoja ja alueita riveissä ja sarakkeissa ilman lajittelua.
- Taulukoiden väliin voidaan lisätä tyhjiä rivejä.
- Pöytien lukumäärä voi olla mikä tahansa.
Kaksi tärkeää oletusta. Oletetaan, että:
- Jokaisen taulukon yläpuolella, ensimmäisessä sarakkeessa, on johtajan nimi, jonka myyntiä taulukko kuvaa (Ivanov, Petrov, Sidorov jne.)
- Tavaroiden ja alueiden nimet kaikissa taulukoissa kirjoitetaan samalla tavalla – tapaustarkkuudella.
Lopullisena tavoitteena on kerätä tiedot kaikista taulukoista yhdeksi tasaiseksi normalisoiduksi taulukoksi, joka on kätevä myöhempää analysointia ja yhteenvedon laatimista varten, eli tähän:
Vaihe 1. Muodosta yhteys tiedostoon
Luodaan uusi tyhjä Excel-tiedosto ja valitse se välilehdeltä Päiväys Komento Hae tiedot – tiedostosta – kirjasta (Data — Tiedostosta — Työkirjasta). Määritä myyntitietojen lähdetiedoston sijainti ja valitse sitten navigaattori-ikkunassa tarvitsemamme arkki ja napsauta painiketta Muunna tiedot (Muunna tiedot):
Tämän seurauksena kaikki sen tiedot tulisi ladata Power Query -editoriin:
Vaihe 2. Siivoa roskat
Poista automaattisesti luodut vaiheet muokattu tyyppi (Muutettu tyyppi) и Korotetut otsikot (Kohotetut otsikot) ja päästä eroon tyhjistä riveistä ja viivoista, joissa on summia suodattimen avulla nolla и YHTEENSÄ ensimmäisellä sarakkeella. Tuloksena saamme seuraavan kuvan:
Vaihe 3. Esimiesten lisääminen
Ymmärtääksesi myöhemmin, missä kenen myynti on, on tarpeen lisätä taulukkoomme sarake, jossa jokaisella rivillä on vastaava sukunimi. Tätä varten:
1. Lisätään apusarake rivinumeroilla komennolla Lisää sarake – hakemistosarake – 0:sta (Lisää sarake — Hakemistosarake — 0:sta).
2. Lisää sarake kaavan kanssa komennolla Sarakkeen lisääminen – Mukautettu sarake (Lisää sarake — Muokattu sarake) ja esittele siellä seuraava rakenne:
Tämän kaavan logiikka on yksinkertainen – jos ensimmäisen sarakkeen seuraavan solun arvo on "Tuote", niin tämä tarkoittaa, että olemme törmänneet uuden taulukon alkuun, joten näytämme edellisen solun arvon johtajan nimi. Muuten emme näytä mitään, eli nollaa.
Saadaksesi emosolun sukunimellä, katsomme ensin edellisen vaiheen taulukkoa #"Hakemisto lisätty"ja määritä sitten tarvitsemamme sarakkeen nimi [Sarake1] hakasulkeissa ja solun numero kyseisessä sarakkeessa hakasulkeissa. Solun numero on yksi pienempi kuin nykyinen, jonka otamme sarakkeesta indeksiVastaavasti.
3. Tyhjät solut on vielä täytettävä nolla nimet korkeammista soluista komennolla Muunna – Täytä – Alas (Muunna - Täytä - Alas) ja poista ensimmäisen sarakkeen tarpeeton sarake indekseillä ja rivit, joissa on sukunimet. Tuloksena saamme:
Vaihe 4. Ryhmittely erillisiin taulukoihin johtajien mukaan
Seuraava vaihe on ryhmitellä kunkin johtajan rivit erillisiin taulukoihin. Käytä tätä varten Transformation-välilehden Group by -komentoa (Transform – Group By) ja valitse avautuvasta ikkunasta Hallinnoija-sarake ja toiminto Kaikki rivit (Kaikki rivit), jotta voit yksinkertaisesti kerätä tietoja käyttämättä mitään koontitoimintoa ne (summa, keskiarvo jne.). P.):
Tämän seurauksena saamme erilliset taulukot jokaiselle johtajalle:
Vaihe 5: Muunna sisäkkäiset taulukot
Nyt annamme taulukot, jotka sijaitsevat tuloksena olevan sarakkeen jokaisessa solussa Kaikki tiedot kunnollisessa kunnossa.
Poista ensin sarake, jota ei enää tarvita kustakin taulukosta Johtaja. Käytämme taas Muokattu sarake kieleke Muutos (Muunna — Muokattu sarake) ja seuraava kaava:
Sitten toisella lasketulla sarakkeella nostamme kunkin taulukon ensimmäisen rivin otsikoiksi:
Ja lopuksi suoritamme päämuunnoksen – jokaisen pöydän avaamisen M-funktiolla Taulukko.UnpivotOtherColumns:
Otsikon alueiden nimet siirtyvät uuteen sarakkeeseen ja saamme kapeamman, mutta samalla pidemmän normalisoidun taulukon. Tyhjennä solut nolla jätetään huomioimatta.
Päästäksemme eroon tarpeettomista välisarakkeista, meillä on:
Vaihe 6 Laajenna sisäkkäiset taulukot
Kaikki normalisoidut sisäkkäiset taulukot on laajennettava yhdeksi luetteloksi käyttämällä sarakeotsikossa olevaa painiketta, jossa on kaksoisnuolet:
… ja vihdoin saamme mitä halusimme:
Voit viedä tuloksena olevan taulukon takaisin Exceliin komennolla Etusivu — Sulje ja lataa — Sulje ja lataa… (Etusivu — Sulje&Lataa — Sulje&Lataa...).
- Rakenna taulukoita eri otsikoilla useista kirjoista
- Tietojen kerääminen kaikista tietyn kansion tiedostoista
- Tietojen kerääminen kirjan kaikista lehdistä yhteen taulukkoon