Contents [show]
Formulering van die probleem
Kom ons kyk na 'n pragtige oplossing vir een van die baie standaard situasies wat die meeste Excel-gebruikers vroeër of later in die gesig staar: jy moet vinnig en outomaties data van 'n groot aantal lêers in een finale tabel insamel.
Gestel ons het die volgende gids, wat verskeie lêers bevat met data van takstede:
Die aantal lêers maak nie saak nie en kan in die toekoms verander. Elke lêer het 'n vel met die naam Verkopewaar die datatabel geleë is:
Die aantal rye (bestellings) in die tabelle is natuurlik anders, maar die stel kolomme is oral standaard.
Taak: om data van alle lêers in een boek te versamel met daaropvolgende outomatiese opdatering wanneer stadslêers of rye in tabelle bygevoeg of uitgevee word. Volgens die finale gekonsolideerde tabel sal dit moontlik wees om enige verslae, spilpunttabelle, filter-sorteer data, ens. te bou. Die belangrikste ding is om te kan versamel.
Ons kies wapens
Vir die oplossing benodig ons die nuutste weergawe van Excel 2016 (die nodige funksionaliteit is reeds by verstek daarin ingebou) of vorige weergawes van Excel 2010-2013 met die gratis byvoeging geïnstalleer Kragnavraag van Microsoft (laai dit hier af). Power Query is 'n super buigsame en super kragtige instrument om data van die buitewêreld in Excel te laai, en dan te stroop en te verwerk. Power Query ondersteun byna alle bestaande databronne – van tekslêers tot SQL en selfs Facebook 🙂
As jy nie Excel 2013 of 2016 het nie, kan jy nie verder lees nie (net 'n grap). In ouer weergawes van Excel kan so 'n taak slegs bewerkstellig word deur 'n makro in Visual Basic te programmeer (wat baie moeilik is vir beginners) of deur eentonige manuele kopiëring (wat lank neem en foute genereer).
Stap 1. Voer een lêer as 'n voorbeeld in
Kom ons voer eers data uit een werkboek in as 'n voorbeeld, sodat Excel "die idee optel". Om dit te doen, skep 'n nuwe leë werkboek en ...
- As jy Excel 2016 het, maak dan die oortjie oop data en dan Skep navraag - Van Lêer - Van Boek (Data - Nuwe navraag - Van lêer - Van Excel)
- as jy Excel 2010-2013 het met die Power Query-byvoeging geïnstalleer, maak dan die oortjie oop Kragnavraag en kies daarop Uit lêer – Uit boek (Vanaf lêer - Van Excel)
Gaan dan in die venster wat oopmaak na ons gids met verslae en kies enige van die stadlêers (dit maak nie saak watter een nie, want hulle is almal tipies). Na 'n paar sekondes moet die Navigator-venster verskyn, waar u die blad wat ons benodig (Verkope) aan die linkerkant moet kies, en die inhoud daarvan sal aan die regterkant vertoon word:
As jy op die knoppie in die onderste regterhoek van hierdie venster klik Aflaai (Laai), dan sal die tabel onmiddellik in sy oorspronklike vorm na die blad ingevoer word. Vir 'n enkele lêer is dit goed, maar ons moet baie sulke lêers laai, so ons sal 'n bietjie anders gaan en op die knoppie klik Regstelling (Edit). Daarna moet die Power Query-navraagredigeerder in 'n aparte venster vertoon word met ons data uit die boek:
Dit is 'n baie kragtige instrument wat jou toelaat om die tafel te "klaar" tot die uitsig wat ons nodig het. Selfs 'n oppervlakkige beskrywing van al sy funksies sal ongeveer honderd bladsye neem, maar as dit baie kortliks is, kan u hierdie venster gebruik:
- filter onnodige data, leë lyne, lyne met foute uit
- sorteer data volgens een of meer kolomme
- ontslae te raak van herhaling
- verdeel taai teks deur kolomme (deur afbakenings, aantal karakters, ens.)
- plaas teks in volgorde (verwyder ekstra spasies, korrigeer hoofletters, ens.)
- omskep datatipes op elke moontlike manier (verander getalle soos teks in normale getalle en omgekeerd)
- transponeer (roteer) tabelle en brei tweedimensionele kruistabelle uit na plat tabelle
- voeg bykomende kolomme by die tabel en gebruik formules en funksies daarin met die M-taal wat in Power Query ingebou is.
- ...
Kom ons voeg byvoorbeeld 'n kolom met die teksnaam van die maand by ons tabel, sodat dit later makliker sal wees om spiltabelverslae te bou. Om dit te doen, regskliek op die kolomopskrif datumen kies die opdrag Duplikaat kolom (Duplikaatkolom), en regskliek dan op die kopskrif van die duplikaatkolom wat verskyn en kies Opdragte Transformeer – Maand – Maand Naam:
'n Nuwe kolom moet gevorm word met die teksname van die maand vir elke ry. Deur op 'n kolomopskrif te dubbelklik, kan jy dit hernoem van Kopieer datum na 'n meer gemaklike Maand, bv.
As die program in sommige kolomme nie die datatipe heeltemal korrek herken het nie, kan u dit help deur op die formaatikoon aan die linkerkant van elke kolom te klik:
U kan reëls met foute of leë reëls, sowel as onnodige bestuurders of kliënte uitsluit, deur 'n eenvoudige filter te gebruik:
Boonop word alle uitgevoerde transformasies in die regte paneel vasgestel, waar hulle altyd teruggerol (kruis) of hul parameters (rat) kan verander:
Lig en elegant, is dit nie?
Stap 2. Kom ons verander ons versoek in 'n funksie
Om vervolgens al die datatransformasies wat vir elke ingevoerde boek gemaak is, te herhaal, moet ons ons geskepte versoek omskakel in 'n funksie, wat dan op sy beurt op al ons lêers toegepas sal word. Om dit te doen is eintlik baie eenvoudig.
In die navraagredigeerder, gaan na die View-oortjie en klik op die knoppie Gevorderde redakteur (Bekyk - Gevorderde Redigeerder). 'n Venster moet oopmaak waar al ons vorige aksies in die vorm van kode in die M-taal geskryf sal word. Neem asseblief kennis dat die pad na die lêer wat ons vir die voorbeeld ingevoer het, hardkodeer is in die kode:
Kom ons maak nou 'n paar aanpassings:
Hulle betekenis is eenvoudig: die eerste reël (lêerpad)=> verander ons prosedure in 'n funksie met 'n argument Lêer pad, en hieronder verander ons die vaste pad na die waarde van hierdie veranderlike.
Almal. Klik op Voltooi en moet dit sien:
Moenie bang wees dat die data verdwyn het nie – om die waarheid te sê, alles is reg, alles moet so lyk 🙂 Ons het suksesvol ons persoonlike funksie geskep, waar die hele algoritme vir die invoer en verwerking van data onthou word sonder om aan 'n spesifieke lêer gekoppel te wees . Dit bly om dit 'n meer verstaanbare naam te gee (byvoorbeeld kry Data) in die paneel aan die regterkant in die veld Naam en jy kan maai Tuis — Maak toe en laai af (Tuis - Maak toe en laai). Neem asseblief kennis dat die pad na die lêer wat ons vir die voorbeeld ingevoer het, hardkodeer is in die kode. Jy sal terugkeer na die hoof Microsoft Excel-venster, maar 'n paneel met die geskepde verbinding met ons funksie moet aan die regterkant verskyn:
Stap 3. Versamel al die lêers
Al die moeilikste deel is agter, die aangename en maklike deel bly. Gaan na die oortjie Data - Skep navraag - Van lêer - Van gids (Data - Nuwe navraag - Van lêer - Van gids) of, as jy Excel 2010-2013 het, soortgelyk aan die oortjie Kragnavraag. In die venster wat verskyn, spesifiseer die gids waar al ons bronstadlêers geleë is en klik OK. Die volgende stap moet 'n venster oopmaak waar al die Excel-lêers wat in hierdie vouer (en sy subgidse) gevind word en besonderhede vir elkeen van hulle gelys sal word:
Klik verandering (Edit) en weer kom ons in die bekende navraagredigeerder venster.
Nou moet ons nog 'n kolom by ons tabel voeg met ons geskepde funksie, wat die data van elke lêer sal "trek". Om dit te doen, gaan na die blad Voeg kolom by – pasgemaakte kolom (Voeg kolom by – Voeg pasgemaakte kolom by) en in die venster wat verskyn, voer ons funksie in kry Data, en spesifiseer daarvoor as 'n argument die volle pad na elke lêer:
Na kliek op OK die geskepde kolom moet by ons tabel aan die regterkant gevoeg word.
Laat ons nou alle onnodige kolomme uitvee (soos in Excel, met die regtermuisknoppie – Verwyder), en laat slegs die bygevoegde kolom en die kolom met die lêernaam, want hierdie naam (meer presies, die stad) sal nuttig wees om in die totale data vir elke ry te hê.
En nou die “wow-oomblik” – klik op die ikoon met sy eie pyltjies in die regter boonste hoek van die bygevoegde kolom met ons funksie:
… ontmerk Gebruik oorspronklike kolomnaam as voorvoegsel (Gebruik oorspronklike kolomnaam as voorvoegsel)en klik op OK. En ons funksie sal die data van elke lêer laai en verwerk, volgens die aangetekende algoritme en alles in 'n gemeenskaplike tabel versamel:
Vir volledige skoonheid kan jy ook die .xlsx-uitbreidings uit die eerste kolom met lêername verwyder – deur standaardvervanging met “niks” (regskliek op die kolomopskrif – plaasvervanger) en hernoem hierdie kolom na Stad. En korrigeer ook die dataformaat in die kolom met die datum.
Almal! Klik op Tuis – Maak toe en laai (Tuis — Maak toe en laai). Alle data wat deur die navraag vir alle stede ingesamel word, sal in die "slim tabel"-formaat na die huidige Excel-blad opgelaai word:
Die geskepde verbinding en ons monteerfunksie hoef nie op enige manier apart gestoor te word nie – dit word op die gewone manier saam met die huidige lêer gestoor.
In die toekoms, met enige veranderinge in die gids (byvoeging of verwydering van stede) of in lêers (verandering van die aantal reëls), sal dit genoeg wees om regs te klik op die tabel of op die navraag in die regter paneel en kies die bevel Dateer op en stoor (Verfris) – Power Query sal al die data weer binne 'n paar sekondes "herbou".
PS
Wysiging. Na die Januarie 2017-opdaterings het Power Query geleer hoe om Excel-werkboeke op sigself te versamel, dws dit is nie nodig om meer 'n aparte funksie te maak nie – dit gebeur outomaties. Die tweede stap uit hierdie artikel is dus nie meer nodig nie en die hele proses word merkbaar eenvoudiger:
- Kies Skep versoek - Van lêer - Van gids - Kies gids - OK
- Nadat die lys lêers verskyn het, druk verandering
- Brei die Binêre kolom uit met 'n dubbelpyl in die Navraagredigeerder-venster en kies die bladnaam wat uit elke lêer geneem moet word
En dit is al! Liedjie!
- Herontwerp van die kruistabel in 'n plat een wat geskik is vir die bou van spiltafels
- Bou 'n geanimeerde borrelgrafiek in Power View
- Makro om velle van verskillende Excel-lêers in een saam te stel