Sisu
Probleemi sõnastamine
Vaatame ilusat lahendust ühe väga standardse olukorra jaoks, millega enamik Exceli kasutajaid varem või hiljem kokku puutub: peate kiiresti ja automaatselt koguma andmed suurest hulgast failidest ühte lõpptabelisse.
Oletame, et meil on järgmine kaust, mis sisaldab mitut faili harulinnade andmetega:
Failide arv ei oma tähtsust ja võib tulevikus muutuda. Igal failil on leht nimega Müükkus andmetabel asub:
Tabelite ridade (tellimuste) arv on muidugi erinev, kuid veergude komplekt on kõikjal standardne.
Ülesanne: koguda andmed kõigist failidest ühte raamatusse koos järgneva automaatse värskendamisega linnafailide või tabeliridade lisamisel või kustutamisel. Lõpliku koondtabeli järgi on siis võimalik koostada mistahes aruandeid, pivot-tabeleid, andmeid filtreerida-sorteerida jne. Peaasi, et saaks koguda.
Valime relvad
Lahenduseks vajame Excel 2016 uusimat versiooni (vajalik funktsionaalsus on vaikimisi juba sisse ehitatud) või Exceli varasemaid versioone 2010-2013 koos tasuta lisandmooduliga. Toite päring Microsoftilt (laadige see alla siit). Power Query on ülipaindlik ja ülivõimas tööriist andmete laadimiseks Excelisse välismaailmast, seejärel eemaldamiseks ja töötlemiseks. Power Query toetab peaaegu kõiki olemasolevaid andmeallikaid – tekstifailidest SQL-i ja isegi Facebookini 🙂
Kui teil pole Excel 2013 või 2016, siis te ei saa edasi lugeda (nali naljaks). Exceli vanemates versioonides saab sellist ülesannet täita ainult Visual Basicus makro programmeerimisega (mis on algajatele väga raske) või monotoonse käsitsi kopeerimisega (mis võtab kaua aega ja tekitab vigu).
Samm 1. Importige üks fail näidisena
Esmalt impordime näitena andmed ühest töövihikust, et Excel “idee üles võtaks”. Selleks looge uus tühi töövihik ja…
- kui teil on Excel 2016, avage vahekaart kuupäev ja siis Loo päring – failist – raamatust (Andmed – uus päring – failist – Excelist)
- kui teil on installitud Power Query lisandmooduliga Excel 2010–2013, avage vahekaart Toite päring ja valige sellel Failist – raamatust (Failist – Excelist)
Seejärel minge avanevas aknas meie kausta aruannetega ja valige mis tahes linnafailid (pole vahet, milline, sest need on kõik tüüpilised). Mõne sekundi pärast peaks ilmuma Navigatori aken, kus peate vasakult valima meile vajaliku lehe (Müük) ja selle sisu kuvatakse paremal pool:
Kui klõpsate selle akna paremas alanurgas oleval nupul Lae (laadimine), siis imporditakse tabel kohe algsel kujul lehele. Ühe faili puhul on see hea, kuid me peame laadima palju selliseid faile, nii et läheme veidi teisiti ja klõpsame nuppu Parandus (Edit). Pärast seda tuleks Power Query päringuredaktor kuvada eraldi aknas meie andmetega raamatust:
See on väga võimas tööriist, mis võimaldab teil "viimistleda" tabeli meile vajaliku vaate järgi. Isegi kõigi selle funktsioonide pealiskaudne kirjeldus võtaks umbes sada lehekülge, kuid kui väga lühidalt, siis seda akent kasutades saate:
- filtreerige välja mittevajalikud andmed, tühjad read, vigadega read
- sortida andmeid ühe või mitme veeru järgi
- kordusest vabaneda
- jagage kleepuv tekst veergude kaupa (eraldajate, märkide arvu jne järgi)
- pane tekst järjekorda (eemaldage lisatühikud, parandage suurtähti jne)
- teisendada andmetüüpe igal võimalikul viisil (muuta numbrid nagu tekst tavalisteks numbriteks ja vastupidi)
- transponeerida (pöörata) tabeleid ja laiendada kahemõõtmelisi risttabeleid tasapinnalisteks
- lisage tabelisse täiendavaid veerge ning kasutage Power Query sisseehitatud M-keele abil neis valemeid ja funktsioone.
- ...
Näiteks lisame oma tabelisse veeru kuu tekstinimetusega, et hiljem oleks lihtsam pivot tabeli aruandeid koostada. Selleks paremklõpsake veeru pealkirja andmedja valige käsk Dubleeri veerg (Duplikaat veerg)ja seejärel paremklõpsake ilmuva duplikaadi veeru päisel ja valige käsud Teisendus – kuu – kuu nimi:
Iga rea jaoks tuleks moodustada uus veerg kuu tekstinimedega. Topeltklõpsates veeru pealkirja, saate selle ümber nimetada Kopeeri kuupäev mugavamaks kuu, nt.
Kui mõnes veerus ei tuvastanud programm andmetüüpi päris õigesti, saate seda aidata, klõpsates iga veeru vasakus servas vorminguikooni:
Lihtsa filtri abil saate välistada vigade või tühjade ridadega read, aga ka mittevajalikud juhid või kliendid:
Lisaks on kõik teostatud teisendused fikseeritud parempoolses paneelis, kus neid saab alati tagasi kerida (rist) või muuta nende parameetreid (käik):
Kerge ja elegantne, kas pole?
2. samm. Teisendame oma taotluse funktsiooniks
Selleks, et hiljem korrata kõiki iga imporditud raamatu jaoks tehtud andmete teisendusi, peame oma loodud päringu teisendama funktsiooniks, mida seejärel rakendatakse omakorda kõikidele meie failidele. Selle tegemine on tegelikult väga lihtne.
Minge päringuredaktoris vahekaardile Vaade ja klõpsake nuppu Täiustatud redaktor (Vaade – täiustatud redaktor). Peaks avanema aken, kus kõik meie eelmised toimingud kirjutatakse koodi kujul M-keeles. Pange tähele, et näite jaoks imporditud faili tee on koodis kõvakoodiga:
Teeme nüüd paar kohandust:
Nende tähendus on lihtne: esimene rida (failitee)=> muudab meie protseduuri argumendiga funktsiooniks faili teekond, ja allpool muudame fikseeritud tee selle muutuja väärtusele.
Kõik. Kliki lõpp ja peaks nägema seda:
Ärge kartke, et andmed on kadunud – tegelikult on kõik korras, kõik peaks välja nägema nii . Jääb üle anda sellele arusaadavam nimi (näiteks getData) väljal paremal asuval paneelil Eesnimi ja saate lõigata Avaleht — sulgege ja laadige alla (Kodu – sulgege ja laadige). Pange tähele, et näite jaoks imporditud faili tee on koodis kõvakoodiga kodeeritud. Naaseme Microsoft Exceli põhiaknasse, kuid paremale peaks ilmuma paneel, millel on meie funktsiooniga loodud ühendus:
Samm 3. Kõigi failide kogumine
Kõik raskem on seljataga, meeldiv ja kerge osa jääb. Minge vahekaardile Andmed – Loo päring – failist – kaustast (Andmed — uus päring — failist — kaustast) või kui teil on Excel 2010–2013, sarnaselt vahekaardile Toite päring. Ilmuvas aknas määrake kaust, kus asuvad kõik meie lähtelinna failid, ja klõpsake nuppu OK. Järgmine samm peaks avama akna, kus loetletakse kõik selles kaustas (ja selle alamkaustades) leitud Exceli failid ja nende üksikasjad:
Click Muutma (Edit) ja jälle jõuame tuttavasse päringuredaktori aknasse.
Nüüd peame oma tabelisse lisama uue veeru koos meie loodud funktsiooniga, mis "tõmbab" andmed igast failist. Selleks minge vahekaardile Lisa veerg – kohandatud veerg (Lisa veerg – lisa kohandatud veerg) ja ilmuvas aknas sisestage meie funktsioon getData, määrates selle argumendina iga faili täieliku tee:
Pärast klõpsamist nupul OK loodud veerg tuleks lisada meie parempoolsesse tabelisse.
Nüüd kustutame kõik mittevajalikud veerud (nagu Excelis, kasutades hiire paremat nuppu – eemalda), jättes alles ainult lisatud veeru ja failinimega veeru, sest see nimi (täpsemalt linn) on kasulik iga rea koguandmetes.
Ja nüüd “vau hetk” – klõpsake meie funktsiooniga lisatud veeru paremas ülanurgas oma nooltega ikooni:
… eemalda märge Kasutage eesliitena algset veeru nime (Kasutage eesliitena algset veeru nime)ja kliki OK. Ja meie funktsioon laadib ja töötleb andmeid igast failist, järgides salvestatud algoritmi ja kogudes kõik ühisesse tabelisse:
Täieliku ilu huvides saate ka esimesest failinimedega veerust eemaldada laiendid .xlsx – standardselt asendades sõnaga "mitte midagi" (paremklõps veeru päisel – Asendaja) ja nimetage see veerg ümber Linn. Ja korrigeerige ka andmevormingut kuupäevaga veerus.
Kõik! Kliki Avaleht – sulgege ja laadige (Kodu – sulge ja laadi). Kõik päringuga kogutud andmed kõigi linnade kohta laaditakse praegusele Exceli lehele üles „nutika tabeli“ vormingus:
Loodud ühendust ja meie montaažifunktsiooni ei pea kuidagi eraldi salvestama – need salvestatakse tavapärasel viisil koos aktiivse failiga.
Edaspidi piisab kaustas (linnade lisamine või eemaldamine) või failides (ridade arvu muutmine) tehtavate muudatuste korral paremklõpsake otse tabelis või päringul parempoolses paneelis ja valige käsk Värskenda ja salvesta (Värskenda) - Power Query "ehitab" kõik andmed uuesti mõne sekundi pärast.
PS
Muudatus. Pärast 2017. aasta jaanuari uuendusi õppis Power Query ise Exceli töövihikuid koguma, st ei pea enam eraldi funktsiooni tegema – see toimub automaatselt. Seega pole selle artikli teist sammu enam vaja ja kogu protsess muutub märgatavalt lihtsamaks:
- Vali Loo taotlus - failist - kaustast - valige kaust - OK
- Pärast failide loendi kuvamist vajutage Muutma
- Laiendage päringuredaktori aknas topeltnoolega veergu Binaarne ja valige igast failist võetava lehe nimi
Ja see on kõik! Laul!
- Risttala ümberkujundamine tasaseks, mis sobib pöördelaudade ehitamiseks
- Animeeritud mulldiagrammi koostamine Power View's
- Makro erinevatest Exceli failidest lehtede üheks koondamiseks