Edukiak
Arazoaren formulazioa
Ikus dezagun irtenbide eder bat Excel-eko erabiltzaile gehienek lehenago edo beranduago aurre egiten duten egoera estandarretako baterako: fitxategi kopuru handietako datuak azkar eta automatikoki bildu behar dituzu azken taula batean.
Demagun honako karpeta hau dugula, adar-hirietako datuak dituzten hainbat fitxategi dituena:
Fitxategi kopuruak ez du axola eta etorkizunean alda daiteke. Fitxategi bakoitzak fitxa bat du izena salmentaknon dagoen datu-taula:
Tauletako errenkada (agindu) kopurua, noski, ezberdina da, baina zutabeen multzoa estandarra da nonahi.
Zeregin: fitxategi guztietako datuak liburu bakarrean biltzea eta gero automatikoki eguneratzearekin, hiriko fitxategiak edo errenkadak tauletan gehitzean edo ezabatzean. Azken taula bateratuaren arabera, orduan posible izango da edozein txosten eraikitzeko, taula dinamikoak, iragazki-ordenatzeko datuak, etab. Nagusia biltzeko gai izatea da.
Guk aukeratzen ditugu armak
Irtenbiderako, Excel 2016-ren azken bertsioa behar dugu (beharrezko funtzionalitateak lehenespenez sartuta daude jada) edo Excel 2010-2013-ren aurreko bertsioak doako gehigarria instalatuta. Power kontsulta Microsoft-etik (deskargatu hemen). Power Query tresna oso malgua eta oso indartsua da datuak Excel-era kanpotik kargatzeko, gero kendu eta prozesatzeko. Power Query-k lehendik dauden datu-iturri ia guztiak onartzen ditu: testu-fitxategietatik hasi eta SQLra eta baita Facebook-era ere 🙂
Excel 2013 edo 2016 ez baduzu, ezin duzu gehiago irakurri (txantxa besterik ez). Excel-en bertsio zaharretan, zeregin hori Visual Basic-en makro bat programatuz (hasiberrientzat oso zaila da) edo eskuzko kopia monotonoaren bidez (denbora luzea hartzen du eta akatsak sortzen ditu).
1. urratsa. Inportatu fitxategi bat lagin gisa
Lehenik eta behin, inporta ditzagun lan-liburu bateko datuak adibide gisa, Excel-ek "ideia jaso dezan". Horretarako, sortu lan-koaderno berri bat eta...
- Excel 2016 baduzu, ireki fitxa Data eta gero Sortu kontsulta - Fitxategitik - Liburutik (Datuak - Kontsulta berria - Fitxategitik - Excel-etik)
- Power Query gehigarria instalatuta Excel 2010-2013 baduzu, ireki fitxa Power kontsulta eta hautatu gainean Fitxategitik – Liburutik (Fitxategitik — Exceletik)
Ondoren, irekitzen den leihoan, joan txostenak dituen gure karpetara eta hautatu hiriko edozein fitxategi (berdin du zein den, denak tipikoak baitira). Segundo batzuk igaro ondoren, Nabigatzailearen leihoa agertu behar da, non behar dugun orria (Salmentak) hautatu behar duzun ezkerreko aldean, eta bere edukia eskuinaldean bistaratuko da:
Leiho honen beheko eskuineko izkinan dagoen botoian klik egiten baduzu Deskargatu (Kargatu), orduan taula berehala inportatuko da orrira jatorrizko forman. Fitxategi bakarrerako, hau ona da, baina horrelako fitxategi asko kargatu behar ditugu, beraz, apur bat ezberdin joango gara eta botoia sakatuko dugu. Zuzenketa (Editatu). Horren ondoren, Power Query kontsulta-editorea beste leiho batean bistaratu beharko litzateke liburuko gure datuekin:
Hau oso tresna indartsua da, mahaia behar dugun ikuspegira "bukatzeko" aukera ematen duena. Funtzio guztien azaleko deskribapenak ere ehun orrialde inguru beharko lituzke, baina, oso labur bada, leiho hau erabiliz egin dezakezu:
- iragazi behar ez diren datuak, lerro hutsak, akatsak dituzten lerroak
- ordenatu datuak zutabe baten edo gehiagoren arabera
- errepikapena kendu
- zatitu testu itsaskorra zutabeka (mugatzaileen, karaktere kopuruaren arabera, etab.)
- ordenatu testua (kendu zuriune gehigarriak, zuzen maiuskulak, etab.)
- bihurtu datu motak modu posible guztietan (bihurtu testua bezalako zenbakiak zenbaki arruntetan eta alderantziz)
- taulak transposatu (biratu) eta bi dimentsioko gurutze-taulak lauetara zabaldu
- gehitu zutabe gehigarriak taulari eta erabili formulak eta funtzioak horietan Power Query-n integratutako M hizkuntza erabiliz.
- ...
Esaterako, gehi diezaiogun zutabe bat hilabeteko testu-izena duen gure taulari, gerora errazagoa izan dadin taula dinamikoen txostenak sortzea. Horretarako, egin klik eskuineko botoiarekin zutabearen goiburuan dataeta hautatu komandoa Zutabe bikoiztua (Ztabe bikoiztua), eta gero egin klik eskuineko botoiarekin agertzen den zutabe bikoiztuaren goiburuan eta hautatu Komandoak Transformatu – Hilabetea – Hilabetearen izena:
Zutabe berri bat osatu behar da ilara bakoitzeko hilabeteko testu-izenekin. Zutabe baten goiburuan klik bikoitza eginez gero, izena aldatu diezaiokezu Kopiatu data erosoago batera HilabeteaAdib.
Zutabe batzuetan programak datu-mota ondo ezagutzen ez badu, orduan lagundu dezakezu zutabe bakoitzaren ezkerraldeko formatuaren ikonoan klik eginez:
Erroreak edo lerro hutsak dituzten lerroak bazter ditzakezu, baita beharrezkoak ez diren kudeatzaileak edo bezeroak ere, iragazki sinple bat erabiliz:
Gainera, egindako eraldaketa guztiak eskuineko panelean finkatzen dira, non beti atzera bota daitezke (gurutzatu) edo parametroak alda daitezke (engranajea):
Arina eta dotorea, ezta?
2. urratsa. Eraldatu dezagun gure eskaera funtzio batean
Gerora inportatutako liburu bakoitzeko egindako datu-eraldaketa guztiak errepikatzeko, sortutako eskaera funtzio batean bihurtu behar dugu, eta gero, aldi berean, gure fitxategi guztietan aplikatuko da. Hori egitea benetan oso erraza da.
Kontsulten editorean, joan Ikusi fitxara eta egin klik botoian Editore aurreratua (Ikusi — Editore aurreratua). Leiho bat ireki beharko litzateke, non gure aurreko ekintza guztiak M hizkuntzan kode moduan idatziko diren. Kontuan izan adibiderako inportatu dugun fitxategiaren bidea kodean gogor kodetuta dagoela:
Orain egin ditzagun doikuntza pare bat:
Haien esanahia sinplea da: lehen lerroa (fitxategi-bidea) => argumentu batekin funtzio bihurtzen du gure prozedura fitxategi-bidea, eta azpian aldagai honen baliora bide finkoa aldatzen dugu.
Denak. Egin klik Amaitu eta hau ikusi beharko litzateke:
Ez izan beldurrik datuak desagertu direla; izan ere, dena ondo dago, dena horrela izan beharko litzateke 🙂 Gure funtzio pertsonalizatua arrakastaz sortu dugu, non datuak inportatzeko eta prozesatzeko algoritmo osoa fitxategi zehatz bati lotuta egon gabe gogoratzen den. . Izen ulergarriagoa ematea geratzen da (adibidez getData) eremuko eskuineko panelean Lehenengoa izena eta jaso dezakezu Hasiera — Itxi eta deskargatu (Hasiera — Itxi eta Kargatu). Kontuan izan adibiderako inportatu dugun fitxategirako bidea kodean gogor kodetuta dagoela. Microsoft Excel leiho nagusira itzuliko zara, baina gure funtzioarekin sortutako konexioa duen panel bat agertu beharko litzateke eskuinaldean:
3. urratsa. Fitxategi guztiak biltzea
Zailena guztia atzean dago, zati atsegina eta erraza geratzen da. Joan fitxara Datuak – Sortu kontsulta – Fitxategitik – Karpetatik (Datuak — Kontsulta berria — Fitxategitik — Karpetatik) edo, Excel 2010-2013 baduzu, fitxaren antzera Power kontsulta. Agertzen den leihoan, zehaztu gure iturburu-hiriaren fitxategi guztiak dauden karpeta eta egin klik OK. Hurrengo urratsak leiho bat ireki beharko luke non karpeta honetan (eta bere azpikarpetetan) aurkitutako Excel fitxategi guztiak eta horietako bakoitzaren xehetasunak zerrendatuko diren:
Klikatu Aldatu (Editatu) eta berriro kontsulta-editorearen leiho ezagunean sartzen gara.
Orain beste zutabe bat gehitu behar diogu gure taulari sortu dugun funtzioarekin, fitxategi bakoitzeko datuak "erabaki" dituena. Horretarako, joan fitxara Gehitu zutabea - Zutabe pertsonalizatua (Gehitu zutabea — Gehitu pertsonalizatutako zutabea) eta agertzen den leihoan, sartu gure funtzioa getData, argumentu gisa fitxategi bakoitzaren bide osoa zehaztuz:
Klik egin ondoren OK sortutako zutabea gure eskuineko taulan gehitu behar da.
Orain ezabatu ditzagun beharrezkoak ez diren zutabe guztiak (Excel-en bezala, saguaren eskuineko botoia erabiliz - Kendu), gehitutako zutabea eta zutabea fitxategi-izenarekin soilik utziz, izen hori (zehazkiago, hiria) errenkada bakoitzeko datu osoetan edukitzeko baliagarria izango delako.
Eta orain "wow momentua" - egin klik gehitutako zutabearen goiko eskuineko izkinan dituen geziak dituen ikonoan gure funtzioarekin:
… desmarkatu Erabili jatorrizko zutabearen izena aurrizki gisa (Erabili jatorrizko zutabearen izena aurrizki gisa)eta sakatu OK. Eta gure funtzioak fitxategi bakoitzeko datuak kargatu eta prozesatuko ditu, grabatutako algoritmoari jarraituz eta dena taula komun batean bilduz:
Edertasun osoa lortzeko, lehen zutabeko .xlsx luzapenak ere kendu ditzakezu fitxategi-izenekin - "ezer"-rekin ordezkatuz (egin klik eskuineko botoiarekin zutabearen goiburuan - Ordezko) eta aldatu izena zutabe honi hiria. Eta zutabeko datu-formatua ere zuzendu datarekin.
Denak! Egin klik Hasiera – Itxi eta kargatu (Hasiera — Itxi eta Kargatu). Kontsultak hiri guztietarako bildutako datu guztiak uneko Excel orrira igoko dira "taula adimenduna" formatuan:
Sortutako konexioa eta gure muntaketa funtzioa ez dira inola ere bereizita gorde behar - uneko fitxategiarekin batera gordetzen dira ohiko moduan.
Etorkizunean, karpetan (hiriak gehitu edo kendu) edo fitxategietan (lerro kopurua aldatuz) egiten diren aldaketak eginez gero, nahikoa izango da eskuineko botoiarekin zuzenean klik egitea taulan edo eskuineko paneleko kontsultan eta hautatu agindua Eguneratu eta Gorde (Freskatu) - Power Query-k datu guztiak berriro "berreraiki" egingo ditu segundo gutxitan.
PS
Zuzenketa. 2017ko urtarrileko eguneraketen ondoren, Power Query-k Excel-eko liburuak nola biltzen ikasi zuen, hau da, ez da gehiago funtzio bereizirik egin behar; automatikoki gertatzen da. Horrela, artikulu honetako bigarren urratsa ez da beharrezkoa eta prozesu osoa nabarmen errazagoa da:
- Aukeratu Sortu eskaera - Fitxategitik - Karpetatik - Hautatu karpeta - Ados
- Fitxategien zerrenda agertu ondoren, sakatu Aldatu
- Kontsulta editorearen leihoan, zabaldu Binary zutabea gezi bikoitz batekin eta hautatu fitxategi bakoitzetik hartu beharreko orri-izena.
Eta hori da dena! Abestia!
- Gurutze-taula birdiseinatu taula pivoteak eraikitzeko egokia den lau batean
- Power View-n burbuila-diagrama animatu bat eraikitzea
- Makroa Excel fitxategi ezberdinetako orriak bakar batean biltzeko