Sisällys
Ongelman muotoilu
Katsotaanpa kaunista ratkaisua yhteen niistä hyvin tavallisista tilanteista, joita useimmat Excel-käyttäjät kohtaavat ennemmin tai myöhemmin: sinun on kerättävä nopeasti ja automaattisesti tiedot suuresta määrästä tiedostoja yhteen lopputaulukkoon.
Oletetaan, että meillä on seuraava kansio, joka sisältää useita tiedostoja, joissa on tietoja haarakaupungeista:
Tiedostojen määrällä ei ole väliä, ja se voi muuttua tulevaisuudessa. Jokaisella tiedostolla on taulukko nimeltä Myyntimissä datataulukko sijaitsee:
Taulukoiden rivien (tilausten) määrä on tietysti erilainen, mutta sarakejoukko on vakio kaikkialla.
Tehtävä: kerätä tiedot kaikista tiedostoista yhdeksi kirjaksi, jonka jälkeen päivitetään automaattisesti, kun lisäät tai poistat kaupunkitiedostoja tai taulukoiden rivejä. Lopullisen konsolidoidun taulukon mukaan sitten on mahdollista rakentaa mitä tahansa raportteja, pivot-taulukoita, suodatinlajittelutietoja jne. Pääasia, että kerätään.
Valitsemme aseet
Ratkaisua varten tarvitsemme Excel 2016:n uusimman version (tarvittavat toiminnot on jo oletuksena sisäänrakennettu) tai Excel 2010-2013:n aiempia versioita, joihin on asennettu ilmainen apuohjelma Tehokysely Microsoftilta (lataa se täältä). Power Query on erittäin joustava ja erittäin tehokas työkalu tietojen lataamiseen ulkomaailmasta Exceliin, sen poistamiseen ja käsittelyyn. Power Query tukee lähes kaikkia olemassa olevia tietolähteitä – tekstitiedostoista SQL:ään ja jopa Facebookiin 🙂
Jos sinulla ei ole Excel 2013:ta tai 2016:ta, et voi lukea enempää (vain vitsi). Excelin vanhemmissa versioissa tällainen tehtävä voidaan suorittaa vain ohjelmoimalla makro Visual Basicissa (mikä on erittäin vaikeaa aloittelijoille) tai monotonisella manuaalisella kopioinnilla (joka kestää kauan ja aiheuttaa virheitä).
Vaihe 1. Tuo yksi tiedosto näytteeksi
Tuodaan ensin tietoja yhdestä työkirjasta esimerkkinä, jotta Excel "poimii idean". Voit tehdä tämän luomalla uuden tyhjän työkirjan ja…
- jos sinulla on Excel 2016, avaa välilehti Päiväys ja sitten Luo kysely – tiedostosta – kirjasta (Data - Uusi kysely - Tiedostosta - Excelistä)
- jos sinulla on Excel 2010–2013, johon on asennettu Power Query -apuohjelma, avaa välilehti Tehokysely ja valitse siitä Tiedostosta – Kirjasta (Tiedostosta - Excelistä)
Siirry sitten avautuvassa ikkunassa raporttikansioomme ja valitse mikä tahansa kaupunkitiedostoista (ei väliä mikä, koska ne ovat kaikki tyypillisiä). Muutaman sekunnin kuluttua ilmestyy Navigator-ikkuna, josta sinun on valittava vasemmalla puolella tarvitsemamme arkki (Myynti) ja sen sisältö näkyy oikealla puolella:
Jos napsautat tämän ikkunan oikeassa alakulmassa olevaa painiketta Lataa (Ladata), taulukko tuodaan välittömästi taulukkoon alkuperäisessä muodossaan. Yksittäiselle tiedostolle tämä on hyvä, mutta meidän on ladattava useita tällaisia tiedostoja, joten toimimme hieman eri tavalla ja napsautamme painiketta Korjaus (Muokata). Sen jälkeen Power Queryn kyselyeditori tulee näyttää erillisessä ikkunassa, jossa on kirjasta saadut tiedot:
Tämä on erittäin tehokas työkalu, jonka avulla voit "viimeistää" taulukon tarvitsemamme näkymään. Jopa pintapuolinen kuvaus kaikista sen toiminnoista vie noin sata sivua, mutta jos hyvin lyhyesti, tämän ikkunan avulla voit:
- suodattaa pois tarpeettomat tiedot, tyhjät rivit ja rivit, joissa on virheitä
- lajitella tiedot yhden tai useamman sarakkeen mukaan
- päästä eroon toistosta
- jaa tarrateksti sarakkeilla (erottimilla, merkkien lukumäärällä jne.)
- laita teksti järjestykseen (poista ylimääräiset välilyönnit, korjaa kirjainkoko jne.)
- muuntaa tietotyyppejä kaikin mahdollisin tavoin (muuntaa numeroita, kuten tekstiä, normaaleiksi numeroiksi ja päinvastoin)
- Transponoi (käännä) taulukoita ja laajenna kaksiulotteiset ristikkäiset taulukot litteiksi
- lisää sarakkeita taulukkoon ja käytä niissä kaavoja ja funktioita Power Queryn sisäänrakennetulla M-kielellä.
- ...
Lisätään taulukkoomme esimerkiksi sarake, jossa on kuukauden tekstinimi, jotta myöhemmin pivot-taulukkoraporttien rakentaminen olisi helpompaa. Voit tehdä tämän napsauttamalla hiiren kakkospainikkeella sarakkeen otsikkoa dataja valitse komento Kopioi sarake (Kaksois sarake)ja napsauta sitten hiiren kakkospainikkeella näkyviin tulevan kopiosarakkeen otsikkoa ja valitse Komennot Muutos – Kuukausi – Kuukauden nimi:
Jokaiselle riville tulee muodostaa uusi sarake, jossa on kuukauden tekstinimet. Kaksoisnapsauttamalla sarakkeen otsikkoa voit nimetä sen uudelleen Kopioi päivämäärä mukavampaan Kuukausi, esim.
Jos joissain sarakkeissa ohjelma ei tunnistanut tietotyyppiä aivan oikein, voit auttaa sitä napsauttamalla kunkin sarakkeen vasemmalla puolella olevaa muotokuvaketta:
Voit sulkea pois rivit, joissa on virheitä tai tyhjiä rivejä, sekä tarpeettomat johtajat tai asiakkaat yksinkertaisella suodattimella:
Lisäksi kaikki suoritetut muunnokset on kiinnitetty oikeaan paneeliin, jossa ne voidaan aina rullata taaksepäin (ristittää) tai muuttaa niiden parametreja (vaihde):
Kevyt ja tyylikäs, eikö?
Vaihe 2. Muunnetaan pyyntömme funktioksi
Jotta voimme toistaa myöhemmin kaikki kullekin tuodulle kirjalle tehdyt tietomuunnokset, meidän on muutettava luotu pyyntömme funktioksi, jota sitten käytetään vuorostaan kaikkiin tiedostoihimme. Tämän tekeminen on itse asiassa hyvin yksinkertaista.
Siirry Kyselyeditorissa Näytä-välilehteen ja napsauta painiketta Edistynyt editori (Näytä — Advanced Editor). Ikkunan pitäisi avautua, johon kaikki aiemmat toiminnomme kirjoitetaan koodin muodossa M-kielellä. Huomaa, että esimerkkiä varten tuomamme tiedoston polku on kovakoodattu koodiin:
Tehdään nyt pari säätöä:
Niiden merkitys on yksinkertainen: ensimmäinen rivi (tiedostopolku)=> muuttaa menettelymme argumentin sisältäväksi funktioksi tiedostopolku, ja alla muutamme kiinteän polun tämän muuttujan arvoon.
Kaikki. Klikkaa Suorittaa loppuun ja pitäisi nähdä tämä:
Älä pelkää, että tiedot ovat kadonneet – itse asiassa kaikki on kunnossa, kaiken pitäisi näyttää tältä 🙂 Olemme onnistuneesti luoneet mukautetun toiminnon, jossa koko tietojen tuonti- ja käsittelyalgoritmi muistaa ilman, että se on sidottu tiettyyn tiedostoon . On vielä annettava sille ymmärrettävämpi nimi (esim getData) kentän oikealla puolella olevassa paneelissa Etunimi ja voit niittää Etusivu — Sulje ja lataa (Koti - Sulje ja lataa). Huomaa, että esimerkkiä varten tuomamme tiedoston polku on kovakoodattu koodiin. Palaat Microsoft Excelin pääikkunaan, mutta oikealle tulee näkyviin paneeli, jossa on luotu yhteys toimintoomme:
Vaihe 3. Kerää kaikki tiedostot
Vaikein osa on takana, miellyttävä ja helppo osa jää. Siirry välilehdelle Tiedot – Luo kysely – Tiedostosta – Kansiosta (Data — Uusi kysely — Tiedostosta — Kansiosta) tai jos sinulla on Excel 2010-2013, välilehden tapaan Tehokysely. Määritä näkyviin tulevassa ikkunassa kansio, jossa kaikki lähdekaupunkitiedostomme sijaitsevat, ja napsauta OK. Seuraavassa vaiheessa pitäisi avata ikkuna, jossa luetellaan kaikki tästä kansiosta (ja sen alikansioista) löytyvät Excel-tiedostot ja kunkin tiedot:
Napauta Muutos (Muokata) ja taas pääsemme tuttuun kyselyn muokkausikkunaan.
Nyt meidän on lisättävä toinen sarake taulukkoomme luomallamme toiminnolla, joka "vetää" tiedot jokaisesta tiedostosta. Voit tehdä tämän siirtymällä välilehdelle Lisää sarake – Mukautettu sarake (Lisää sarake - Lisää mukautettu sarake) ja kirjoita funktiomme näkyviin tulevassa ikkunassa getData, määrittämällä sille argumentiksi jokaisen tiedoston koko polun:
Napsauttamisen jälkeen OK luotu sarake tulee lisätä oikealla olevaan taulukkoomme.
Nyt poistetaan kaikki tarpeettomat sarakkeet (kuten Excelissä, hiiren oikealla painikkeella – poista), jättämällä vain lisätty sarake ja sarake, jossa on tiedostonimi, koska tämä nimi (tarkemmin kaupunki) on hyödyllinen jokaisen rivin kokonaistiedoissa.
Ja nyt "vau hetki" - napsauta toiminnollamme lisätyn sarakkeen oikeassa yläkulmassa olevaa kuvaketta omilla nuoleillaan:
… poista valinta Käytä etuliitteenä alkuperäistä sarakkeen nimeä (Käytä alkuperäistä sarakkeen nimeä etuliitteenä)ja valitse OK. Ja toimintomme lataa ja käsittelee tiedot jokaisesta tiedostosta noudattaen tallennettua algoritmia ja keräämällä kaiken yhteiseen taulukkoon:
Täydellisen kauneuden vuoksi voit myös poistaa .xlsx-laajennukset ensimmäisestä sarakkeesta tiedostonimien kanssa – korvaamalla tavallisesti sanalla "ei mitään" (napsauta sarakkeen otsikkoa hiiren kakkospainikkeella - korvike) ja nimeä tämä sarake uudelleen muotoon Kaupunki. Korjaa myös päivämäärän sarakkeen tietomuoto.
Kaikki! Klikkaa Etusivu – Sulje ja lataa (Koti - Sulje ja lataa). Kaikki kyselyn keräämät tiedot kaikista kaupungeista ladataan nykyiseen Excel-taulukkoon "älykäs taulukko" -muodossa:
Luotua yhteyttä ja kokoonpanotoimintoamme ei tarvitse tallentaa erikseen millään tavalla – ne tallennetaan yhdessä nykyisen tiedoston kanssa tavalliseen tapaan.
Jatkossa kansion (kaupunkien lisääminen tai poistaminen) tai tiedostojen (rivien lukumäärän muuttaminen) muutoksissa riittää, kun napsautat hiiren kakkospainikkeella suoraan taulukkoa tai oikeanpuoleisessa paneelissa olevaa kyselyä ja valitset komento Päivitä & Tallenna (Virkistää) – Power Query "rakentaa" kaikki tiedot uudelleen muutamassa sekunnissa.
PS
Muutos. Tammikuun 2017 päivitysten jälkeen Power Query oppi itse keräämään Excel-työkirjoja, eli erillistä funktiota ei enää tarvitse tehdä – se tapahtuu automaattisesti. Näin ollen tämän artikkelin toista vaihetta ei enää tarvita ja koko prosessi yksinkertaistuu huomattavasti:
- Valita Luo pyyntö – Tiedostosta – Kansiosta – Valitse kansio – OK
- Kun tiedostoluettelo tulee näkyviin, paina Muutos
- Laajenna Kyselyeditori-ikkunassa Binaarisarake kaksoisnuolella ja valitse jokaisesta tiedostosta otettava arkin nimi
Ja siinä kaikki! Song!
- Crosstab uudelleen suunnittelu litteäksi, joka sopii kääntöpöytien rakentamiseen
- Animoidun kuplakaavion luominen Power View'ssa
- Makro koota arkit eri Excel-tiedostoista yhdeksi