Ukuhlanganisa amatafula avela kumafayela e-Excel ahlukene nge-Power Query

Ukwakhiwa kwenkinga

Ake sibheke isisombululo esihle kwesinye sezimo ezijwayelekile abasebenzisi abaningi be-Excel ababhekana nazo maduze noma kamuva: udinga ukuqoqa ngokushesha nangokuzenzakalelayo idatha enanini elikhulu lamafayela uye kuthebula lokugcina elilodwa. 

Ake sithi sinefolda elandelayo, equkethe amafayela amaningana anedatha evela emadolobheni amagatsha:

Inani lamafayela alinandaba futhi lingashintsha esikhathini esizayo. Ifayela ngalinye lineshidi elinegama Saleslapho itafula ledatha likhona:

Inombolo yemigqa (ama-oda) ematafuleni, yebo, ihlukile, kodwa isethi yamakholomu ijwayelekile yonke indawo.

Umsebenzi: ukuqoqa idatha kusuka kuwo wonke amafayela encwadini eyodwa enokubuyekeza okuzenzakalelayo okulandelayo lapho wengeza noma ususa amafayela edolobha noma imigqa kumathebula. Ngokwethebula lokugcina elihlanganisiwe, khona-ke kuzokwazi ukwakha noma yimiphi imibiko, amathebula e-pivot, idatha yokuhlunga isihlungi, njll. Into eyinhloko ukwazi ukuqoqa.

Sikhetha izikhali

Ukuze uthole isixazululo, sidinga inguqulo yakamuva ye-Excel 2016 (ukusebenza okudingekayo sekuvele kwakhiwe kuyo ngokuzenzakalelayo) noma izinguqulo zangaphambilini ze-Excel 2010-2013 ezinesengezo samahhala esifakiwe. Umbuzo Wamandla kusuka kuMicrosoft (yilande lapha). I-Power Query iyithuluzi eliguquguquka kakhulu nelinamandla kakhulu lokulayisha idatha ku-Excel kusuka emhlabeni wangaphandle, bese uyayihlubula futhi uyicubungule. Umbuzo Wamandla usekela cishe yonke imithombo yedatha ekhona - kusukela kumafayela ombhalo kuya ku-SQL ngisho ne-Facebook 🙂

Uma ungenayo i-Excel 2013 noma i-2016, awukwazi ukufunda ngokuqhubekayo (ukudlala nje). Ezinguqulweni ezindala ze-Excel, umsebenzi onjalo ungenziwa kuphela ngokuhlela i-macro ku-Visual Basic (okunzima kakhulu kwabaqalayo) noma ngokukopisha okwenziwa ngesandla (okuthatha isikhathi eside futhi kukhiqize amaphutha).

Isinyathelo 1. Ngenisa ifayela elilodwa njengesampula

Okokuqala, ake singenise idatha encwadini eyodwa yokusebenzela njengesibonelo, ukuze i-Excel “icoshe umbono”. Ukwenza lokhu, dala ibhuku lokusebenza elisha elingenalutho futhi...

• uma une-Excel 2016, bese uvula ithebhu Idatha bese Dala Umbuzo - Kusuka Efayeleni - Kusuka Encwadini (Idatha - Umbuzo Omusha- Kusuka kufayela - Kusuka ku-Excel)
• uma une-Excel 2010-2013 enesengezo se-Power Query efakiwe, bese uvula ithebhu Umbuzo Wamandla bese ukhetha kuyo Kusuka kufayela - Kusuka encwadini (Kusuka kufayela - Kusuka ku-Excel)

Khona-ke, efasiteleni elivulayo, hamba kufolda yethu ngemibiko bese ukhetha noma yimaphi amafayela wedolobha (akunandaba ukuthi yiliphi, ngoba wonke ajwayelekile). Ngemuva kwemizuzwana embalwa, iwindi le-Navigator kufanele livele, lapho udinga ukukhetha ishidi esilidingayo (Ukuthengisa) ngakwesokunxele, futhi okuqukethwe kwalo kuzovezwa ngakwesokudla:

Uma uchofoza inkinobho ekhoneni elingezansi kwesokudla laleli windi Thwebula (Layisha), bese ithebula lizongeniswa ngokushesha eshidini ngendlela yalo yoqobo. Ngefayela elilodwa, lokhu kuhle, kodwa sidinga ukulayisha amafayela amaningi anjalo, ngakho-ke sizohamba ngokuhlukile bese sichofoza inkinobho. Ukulungiswa (Hlela). Ngemva kwalokho, umhleli wombuzo wamandla kufanele uboniswe efasiteleni elihlukile nedatha yethu evela encwadini:

Leli ithuluzi elinamandla kakhulu elikuvumela ukuthi "uqedele" ithebula ekubukeni esikudingayo. Ngisho nencazelo ekha phezulu yayo yonke imisebenzi yayo ingathatha cishe amakhasi ayikhulu, kodwa, uma kafushane kakhulu, usebenzisa leli windi ungakwazi:

• hlunga idatha engadingekile, imigqa engenalutho, imigqa enamaphutha
• hlunga idatha ngekholomu eyodwa noma ngaphezulu
• susa ukuphindaphinda
• hlukanisa umbhalo onamathelayo ngamakholomu (ngama-delimiters, inombolo yezinhlamvu, njll.)
• beka umbhalo ngokulandelana (susa izikhala ezengeziwe, uhlobo olulungile, njll.)
• guqula izinhlobo zedatha ngayo yonke indlela (guqula izinombolo ezinjengombhalo zibe izinombolo ezivamile futhi ngokuphambene nalokho)
• guqula (phendukisa) amathebula futhi wandise amathebula anezinhlangothi ezimbili abe ayisicaba
• engeza amakholomu engeziwe kuthebula futhi usebenzise amafomula nemisebenzi kuwo usebenzisa ulimi lwe-M olwakhelwe kokuthi Umbuzo Wamandla.
• ...

Isibonelo, ake sengeze ikholomu enegama lombhalo lenyanga etafuleni lethu, ukuze kamuva kube lula ukwakha imibiko yethebula le-pivot. Ukuze wenze lokhu, chofoza kwesokudla esihlokweni sekholomu Usukubese ukhetha umyalo Phinda ikholomu (Ikholomu Eyimpinda), bese uchofoza kwesokudla enhlokweni yekholomu eyimpinda evelayo bese ukhetha Imiyalo Uguquko - Inyanga - Igama Lenyanga:

Kufanele kwakhiwe ikholomu entsha namagama ombhalo wenyanga omugqa ngamunye. Ngokuchofoza kabili esihlokweni sekholomu, ungasiqamba kabusha usuka kuso Kopisha Usuku ukuze ukhululeke kakhudlwana izinyanga, isib.

Uma kwamanye amakholomu uhlelo lungazange lubone kahle uhlobo lwedatha, ungakusiza ngokuchofoza isithonjana sefomethi ohlangothini lwesobunxele lwekholomu ngayinye:

Ungakhipha imigqa enamaphutha noma imigqa engenalutho, kanye nabaphathi abangadingekile noma amakhasimende, usebenzisa isihlungi esilula:

Ngaphezu kwalokho, zonke izinguquko ezenziwe zilungiswa kuphaneli engakwesokudla, lapho zingahle zihlehliswe khona (isiphambano) noma ziguqule amapharamitha azo (igiya):

Ilula futhi inhle, akunjalo?

Isinyathelo sesi-2. Masiguqule isicelo sethu sibe umsebenzi

Ukuze ngokulandelayo siphinde zonke izinguquko zedatha ezenziwe encwadini ngayinye engenisiwe, sidinga ukuguqula isicelo sethu esidaliwe sisenze umsebenzi, ozobe ususetshenziswa kuwo wonke amafayela ethu. Ukwenza lokhu empeleni kulula kakhulu.

Kusihleli Sombuzo, hamba kuthebhu Buka bese uchofoza inkinobho Umhleli Othuthukile (Buka — Isihleli Esithuthukile). Iwindi kufanele livuleke lapho zonke izenzo zethu zangaphambilini zizobhalwa khona ngendlela yekhodi ngolimi luka-M. Sicela uqaphele ukuthi indlela eya efayelini esiyingenise njengesibonelo inekhodi eqinile:

Manje ake senze izinguquko ezimbalwa:

Incazelo yabo ilula: umugqa wokuqala (indlela yefayela)=> uphendulela inqubo yethu ibe umsebenzi onokuphikisana indlela yefayela, futhi ngezansi sishintsha indlela egxilile enanini lalokhu okuguquguqukayo. 

Konke. Chofoza ku- Qeda futhi kufanele ubone lokhu:

Ungesabi ukuthi idatha ilahlekile - empeleni, konke kulungile, konke kufanele kubukeke kanje 🙂 Sidale ngempumelelo umsebenzi wethu wangokwezifiso, lapho yonke i-algorithm yokungenisa nokucubungula idatha ikhunjulwa ngaphandle kokuboshelwa efayeleni elithile. . Kusele ukuyinikeza igama eliqondakala kakhudlwana (ngokwesibonelo getData) kuphaneli kwesokudla ebaleni Igama futhi ungavuna Ikhaya — Vala bese ulanda (Ekhaya - Vala futhi Ulayishe). Sicela uqaphele ukuthi indlela eya efayelini esiyingenise njengesibonelo inekhodi eqinile. Uzobuyela efasiteleni eliyinhloko le-Microsoft Excel, kodwa iphaneli elinoxhumano oludaliwe kumsebenzi wethu kufanele livele ngakwesokudla:

Isinyathelo 3. Ukuqoqa wonke amafayela

Yonke ingxenye enzima kakhulu ingemuva, ingxenye ejabulisayo nelula isala. Iya kuthebhu Idatha - Dala Umbuzo - Kusuka Kufayela - Kusuka Kufolda (Idatha - Umbuzo Omusha - Kusuka kufayela - Kusuka kufolda) noma, uma une-Excel 2010-2013, ngokufanayo kuthebhu Umbuzo Wamandla. Efasiteleni elivelayo, cacisa ifolda lapho wonke amafayela wedolobha eliwumthombo atholakala khona bese uchofoza OK. Isinyathelo esilandelayo kufanele sivule iwindi lapho wonke amafayela e-Excel atholakala kule folda (namafolda ayo angaphansi) kanye nemininingwane yawo ngayinye kuzofakwa ohlwini:

Chofoza Guqula (Hlela) futhi futhi singena efasiteleni lomhleli wemibuzo elijwayelekile.

Manje sidinga ukwengeza enye ikholomu etafuleni lethu ngomsebenzi wethu owenziwe, "ozodonsa" idatha kusuka kufayela ngalinye. Ukuze wenze lokhu, yiya kuthebhu Engeza Ikholomu – Ikholomu Ngokwezifiso (Engeza Ikholomu — Engeza Ikholomu Ngokwezifiso) futhi efasiteleni elivelayo, faka umsebenzi wethu getData, eyicacisa njengempikiswano indlela egcwele yefayela ngalinye:

Ngemuva kokuchofoza OK ikholomu edaliwe kufanele yengezwe etafuleni lethu kwesokudla.

Manje ake sisuse wonke amakholomu angadingekile (njengaku-Excel, sisebenzisa inkinobho yegundane engakwesokudla - Susa), kushiya kuphela ikholomu eyengeziwe kanye nekholomu enegama lefayela, ngoba leli gama (ngokunembile, idolobha) lizoba usizo ukuba nedatha ephelele yomugqa ngamunye.

Futhi manje "wow moment" - chofoza isithonjana ngemicibisholo yaso ekhoneni eliphezulu kwesokudla sekholomu eyengeziwe ngomsebenzi wethu:

… susa ukumaka Sebenzisa igama lekholomu yoqobo njengesiqalo (Sebenzisa igama lekholomu yoqobo njengesiqalo)bese uchofoza OK. Futhi umsebenzi wethu uzolayisha futhi ucubungule idatha evela kufayela ngalinye, ngokulandela i-algorithm erekhodiwe futhi iqoqe yonke into kuthebula elifanayo:

Ukuze uthole ubuhle obuphelele, ungakwazi futhi ukususa izandiso ze-.xlsx kukholamu yokuqala enamagama amafayela – ngokufaka “lutho” esikhundleni esivamile (chofoza kwesokudla kunhlokweni yekholomu – I-Substitute) bese uqambe kabusha le kholomu ukuze imizwa. Futhi ulungise ifomethi yedatha kukholamu enedethi.

Konke! Chofoza ku- Ikhaya - Vala futhi Ulayishe (Ekhaya — Vala & Layisha). Yonke idatha eqoqwe ngumbuzo wawo wonke amadolobha izolayishwa eshidini lamanje le-Excel ngefomethi “yetafula elihlakaniphile”:

Ukuxhumeka okudaliwe kanye nomsebenzi wethu womhlangano awudingi ukugcinwa ngokwehlukana nganoma iyiphi indlela - zigcinwa kanye nefayela lamanje ngendlela evamile.

Ngokuzayo, nanoma yiziphi izinguquko kufolda (ukwengeza noma ukususa amadolobha) noma kumafayela (ukushintsha inombolo yemigqa), kuyokwanela ukuchofoza kwesokudla etafuleni noma embuzweni osephaneli elingakwesokudla bese ukhetha umyalo Buyekeza & Londoloza (Vuselela) - Umbuzo Wamandla "uzokwakha kabusha" yonke idatha futhi ngemizuzwana embalwa.

PS

Ukuchitshiyelwa. Ngemuva kwezibuyekezo zangoJanuwari 2017, i-Power Query yafunda ukuqoqa izincwadi zokusebenza ze-Excel iyodwa, okungukuthi asikho isidingo sokwenza umsebenzi ohlukile - kuyenzeka ngokuzenzakalelayo. Ngakho-ke, isinyathelo sesibili esivela kulesi sihloko asisadingeki futhi yonke inqubo iba lula ngokuphawulekayo:

1. Khetha Dala Isicelo - Kusuka Kufayela - Kusuka Kufolda - Khetha Ifolda - KULUNGILE
2. Ngemva kokuvela kohlu lwamafayela, cindezela Guqula
3. Ewindini Lomhleli Wombuzo, nweba ikholomu kanambambili ngomcibisholo okabili bese ukhetha igama leshidi elizothathwa kufayela ngalinye.

Futhi yilokho kuphela! Ingoma!

• Ukudizayina kabusha kwe-crosstab ibe isicaba elungele ukwakha amathebula e-pivot
• Ukwakha ishadi lebhamuza eligqwayizayo ku-Power View
• Imakhro yokuhlanganisa amashidi asuka kumafayela e-Excel ahlukene abe elilodwa