Kusonkhanitsa matebulo kuchokera kumafayilo osiyanasiyana a Excel ndi Power Query

Kupanga vuto

Tiyeni tiwone yankho lokongola pamikhalidwe yokhazikika yomwe ogwiritsa ntchito ambiri a Excel amakumana nayo posachedwa: muyenera kusonkhanitsa mwachangu komanso mwachangu deta kuchokera pamafayilo ambiri kukhala tebulo limodzi lomaliza. 

Tiyerekeze kuti tili ndi chikwatu chotsatirachi, chomwe chili ndi mafayilo angapo okhala ndi data kuchokera kumizinda yanthambi:

Chiwerengero cha mafayilo chilibe kanthu ndipo chingasinthe mtsogolo. Fayilo iliyonse ili ndi pepala lotchedwa Saleskomwe data table ili:

Chiwerengero cha mizere (madongosolo) mu matebulo, ndithudi, ndi osiyana, koma mizati ndi muyezo kulikonse.

Ntchito: kusonkhanitsa zidziwitso kuchokera kumafayilo onse kukhala buku limodzi ndikuzisintha zokha mukawonjezera kapena kufufuta mafayilo amzinda kapena mizere pamatebulo. Malingana ndi tebulo lomaliza lophatikizidwa, ndiye kuti kudzakhala kotheka kupanga malipoti aliwonse, matebulo a pivot, deta yamtundu wa fyuluta, etc. Chinthu chachikulu ndikutha kusonkhanitsa.

Timasankha zida

Kuti tipeze yankho, tifunika mtundu waposachedwa wa Excel 2016 (machitidwe ofunikira adamangidwa kale momwemo mwachisawawa) kapena mitundu yam'mbuyomu ya Excel 2010-2013 yokhala ndi zowonjezera zaulere. Kufunsa Mphamvu kuchokera ku Microsoft (tsitsani apa). Power Query ndi chida chosinthika kwambiri komanso champhamvu kwambiri pakukweza deta mu Excel kuchokera kunja, kenako ndikuvula ndikukonza. Power Query imathandizira pafupifupi magwero onse omwe alipo - kuchokera pamafayilo amawu kupita ku SQL komanso Facebook 🙂

Ngati mulibe Excel 2013 kapena 2016, ndiye kuti simungathe kuwerenga (kungosewera). M'matembenuzidwe akale a Excel, ntchito yotereyi imatha kutheka pokhapokha pokonza macro mu Visual Basic (omwe ndi ovuta kwambiri kwa oyamba kumene) kapena kukopera pamanja (komwe kumatenga nthawi yayitali ndikupanga zolakwika).

Gawo 1. Tengani fayilo imodzi ngati chitsanzo

Choyamba, tiyeni titenge zambiri kuchokera ku buku limodzi lantchito monga chitsanzo, kuti Excel "itenge lingaliro". Kuti muchite izi, pangani buku latsopano lopanda kanthu ndi…

• ngati muli ndi Excel 2016, ndiye tsegulani tabu Deta Kenako Pangani Mafunso - Kuchokera Fayilo - Kuchokera M'buku (Deta - Funso Latsopano- Kuchokera pafayilo - Kuchokera ku Excel)
• ngati muli ndi Excel 2010-2013 ndi Power Query add-in, ndiye tsegulani tabu. Kufunsa Mphamvu ndi kusankhapo Kuchokera ku fayilo - Kuchokera m'buku (Kuchokera ku fayilo - Kuchokera ku Excel)

Kenako, pawindo lomwe limatsegulidwa, pitani ku chikwatu chathu ndi malipoti ndikusankha mafayilo aliwonse amzindawu (zilibe kanthu kuti ndi iti, chifukwa onse ndi ofanana). Pambuyo pamasekondi angapo, zenera la Navigator liyenera kuwoneka, pomwe muyenera kusankha pepala lomwe tikufuna (Zogulitsa) kumanzere, ndipo zomwe zili mkati mwake zidzawonetsedwa kumanja:

Ngati inu alemba pa batani m'munsi pomwe ngodya pa zenera Download (Katundu), ndiye tebulo lidzatumizidwa mwamsanga ku pepala mu mawonekedwe ake oyambirira. Pa fayilo imodzi, izi ndizabwino, koma tifunika kutsitsa mafayilo ambiri, kotero tipita mosiyana ndikudina batani. kudzudzulidwa (Sinthani). Pambuyo pake, Power Query query editor iyenera kuwonetsedwa pawindo losiyana ndi deta yathu kuchokera m'bukuli:

Ichi ndi chida champhamvu kwambiri chomwe chimakulolani "kumaliza" tebulo kuti muwone zomwe tikufuna. Ngakhale kufotokozera mwachidule za ntchito zake zonse kungatenge masamba pafupifupi zana, koma, ngati mwachidule, pogwiritsa ntchito zenerali mutha:

• sefa deta yosafunikira, mizere yopanda kanthu, mizere yokhala ndi zolakwika
• sinthani data potengera ndime imodzi kapena zingapo
• chotsani kubwerezabwereza
• gawani zolemba zomata ndi mizati (potengera malire, kuchuluka kwa zilembo, ndi zina zotero)
• khazikitsani mawu (chotsani mipata yowonjezera, cholondola, ndi zina zotero)
• sinthani mitundu ya data mwanjira iliyonse (sinthani manambala ngati mawu kukhala manambala wamba ndi mosemphanitsa)
• tembenuzani (tembenuzani) matebulo ndikukulitsa matebulo amitundu iwiri kukhala athyathyathya
• onjezani zigawo zina patebulo ndikugwiritsa ntchito mafomu ndi ntchito momwemo pogwiritsa ntchito chilankhulo cha M chopangidwa mu Power Query.
• ...

Mwachitsanzo, tiyeni tiwonjezere gawo lokhala ndi dzina la mwezi patebulo lathu, kuti pambuyo pake zikhale zosavuta kupanga malipoti a pivot table. Kuti muchite izi, dinani kumanja pamutu wamutu tsikundikusankha lamulo Mzere wobwereza (Chigawo Chobwereza), ndiyeno dinani kumanja pamutu wa gawo lobwereza lomwe likuwonekera ndikusankha Malamulo Kusintha - Mwezi - Dzina la Mwezi:

Danga latsopano liyenera kupangidwa ndi mayina a mwezi pa mzere uliwonse. Mwa kuwonekera pawiri pamutu wagawo, mutha kuyitchanso kuchokera Copy Date kumasuka kwambiri mwezi, mwachitsanzo.

Ngati m'magawo ena pulogalamuyo sinazindikire molondola mtundu wa data, ndiye kuti mutha kuthandizira podina chizindikiro chakumanzere kwa gawo lililonse:

Mutha kusiya mizere yokhala ndi zolakwika kapena mizere yopanda kanthu, komanso mamenejala osafunikira kapena makasitomala, pogwiritsa ntchito fyuluta yosavuta:

Kuphatikiza apo, masinthidwe onse opangidwa amakhazikika pagawo lakumanja, pomwe amatha kubwezeredwa (mtanda) kapena kusintha magawo awo (giya):

Kuwala ndi kaso, sichoncho?

Gawo 2. Tiyeni tisinthe pempho lathu kukhala ntchito

Kuti kenako tibwerezenso zosintha zonse zomwe zidapangidwa m'buku lililonse lotumizidwa kunja, tiyenera kusintha zomwe tapempha kuti zikhale ntchito, zomwe zidzagwiritsidwanso ntchito pamafayilo athu onse. Kuchita izi ndikosavuta kwambiri.

Mu Query Editor, pitani ku View tabu ndikudina batani Advanced Editor (Onani - Advanced Editor). Zenera liyenera kutsegulidwa pomwe zochita zathu zonse zam'mbuyomu zidzalembedwe mumtundu wa chilankhulo cha M. Chonde dziwani kuti njira yopita ku fayilo yomwe tidaitanitsa mwachitsanzo ili ndi code code:

Tsopano tiyeni tikonze zingapo:

Tanthauzo lawo ndi losavuta: mzere woyamba (njira yamafayilo)=> amasintha ndondomeko yathu kukhala ntchito yokhala ndi mkangano filepath, ndipo pansipa timasintha njira yokhazikika ku mtengo wa kusinthaku. 

Zonse. Dinani pa chitsiriziro ndipo ayenera kuwona izi:

Osawopa kuti deta yasowa - kwenikweni, zonse ziri bwino, chirichonse chiyenera kuwoneka chonchi 🙂 Tapanga bwino ntchito yathu yachizolowezi, pomwe ndondomeko yonse ya kuitanitsa ndi kukonza deta imakumbukiridwa popanda kumangidwa ku fayilo yeniyeni. . Zimatsalira kuti zipatse dzina lomveka bwino (mwachitsanzo getData) mu gulu kumanja kumunda dzina loyamba ndipo mukhoza kukolola Kunyumba - Tsekani ndikutsitsa (Kunyumba - Tsekani ndi Katundu). Chonde dziwani kuti njira yopita ku fayilo yomwe tidaitanitsa mwachitsanzo ndi hardcoded mu code. Mubwerera ku zenera lalikulu la Microsoft Excel, koma gulu lomwe lili ndi cholumikizira chopangidwa ndi ntchito yathu liyenera kuwonekera kumanja:

Gawo 3. Kusonkhanitsa owona onse

Zonse zovuta kwambiri zili kumbuyo, gawo losangalatsa komanso losavuta limakhalabe. Pitani ku tabu Deta - Pangani Mafunso - Kuchokera Fayilo - Kuchokera ku Foda (Deta - Funso Latsopano - Kuchokera pafayilo - Kuchokera ku foda) kapena, ngati muli ndi Excel 2010-2013, mofanana ndi tabu Kufunsa Mphamvu. Pazenera lomwe likuwoneka, tchulani chikwatu chomwe mafayilo athu onse amzindawu ali ndipo dinani OK. Gawo lotsatira liyenera kutsegula zenera pomwe mafayilo onse a Excel opezeka mufoda iyi (ndi mafoda ake ang'onoang'ono) ndi tsatanetsatane wa iliyonse yaiwo alembedwa:

Dinani Change (Sinthani) ndipo kachiwiri timalowa mu zenera lodziwika bwino lazosintha.

Tsopano tifunika kuwonjezera ndime ina patebulo lathu ndi ntchito yathu yopangidwa, yomwe "idzakoka" deta kuchokera ku fayilo iliyonse. Kuti muchite izi, pitani ku tabu Onjezani Mzere - Mzere Wamakonda (Onjezani Mgawo - Onjezani Mzere Wamakonda) ndipo pawindo lomwe likuwonekera, lowetsani ntchito yathu getData, kufotokozera ngati mkangano njira yonse ya fayilo iliyonse:

Pambuyo pang'anani OK gawo lopangidwa liyenera kuwonjezeredwa patebulo lathu kumanja.

Tsopano tiyeni tichotse zipilala zonse zosafunikira (monga mu Excel, pogwiritsa ntchito batani lakumanja la mbewa - Chotsani), kusiya gawo lowonjezera ndi gawo lokhala ndi dzina la fayilo, chifukwa dzinali (momwemonso, mzinda) lidzakhala lothandiza kukhala ndi data yonse pamzere uliwonse.

Ndipo tsopano "wow mphindi" - dinani chizindikirocho ndi mivi yake pakona yakumanja kwa gawo lowonjezera ndi ntchito yathu:

… sankhani Gwiritsani ntchito dzina lazagawo loyambirira ngati choyambirira (Gwiritsani ntchito dzina lazachigawo choyambirira ngati choyambirira)ndipo dinani OK. Ndipo ntchito yathu idzatsegula ndikukonza deta kuchokera pa fayilo iliyonse, kutsatira ndondomeko yojambulidwa ndikusonkhanitsa zonse patebulo lofanana:

Kuti muthe kukongola kotheratu, mutha kuchotsanso zowonjezera za .xlsx pamzati woyamba wokhala ndi mayina a mafayilo - posinthana ndi "palibe" (dinani kumanja pamutu wandalama - M'malo) ndikusinthanso gawoli kukhala maganizo. Komanso konzani mtundu wa data womwe uli pagawo lokhala ndi tsiku.

Zonse! Dinani pa Kunyumba - Tsekani ndikunyamula (Kunyumba - Tsekani & Katundu). Zonse zomwe zasonkhanitsidwa ndi funso la mizinda yonse zidzakwezedwa pa pepala lamakono la Excel mumtundu wa "smart table":

Kugwirizana komwe kunapangidwa ndi ntchito yathu ya msonkhano siziyenera kupulumutsidwa padera mwanjira iliyonse - zimasungidwa pamodzi ndi fayilo yamakono mwachizolowezi.

M'tsogolomu, ndi zosintha zilizonse mufoda (kuwonjezera kapena kuchotsa mizinda) kapena mafayilo (kusintha kuchuluka kwa mizere), kudzakhala kokwanira kudina kumanja patebulo kapena pafunso lomwe lili patsamba lakumanja ndikusankha lamula Sinthani & Sungani (Bwezeretsani) - Power Query "idzamanganso" deta yonse mumasekondi pang'ono.

PS

Kusintha. Pambuyo pa zosintha za Januware 2017, Power Query idaphunzira momwe mungasonkhanitsire mabuku a Excel pawokha, mwachitsanzo, palibe chifukwa chopangiranso ntchito ina - zimachitika zokha. Chifukwa chake, gawo lachiwiri kuchokera m'nkhaniyi silikufunikanso ndipo njira yonseyo imakhala yosavuta:

1. Sankhani Pangani Pempho - Kuchokera Fayilo - Kuchokera Foda - Sankhani Foda - Chabwino
2. Mndandanda wa mafayilo ukawoneka, dinani Change
3. Pazenera la Query Editor, onjezerani gawo la Binary ndi mivi iwiri ndikusankha dzina latsamba lomwe liyenera kutengedwa pafayilo iliyonse.

Ndipo ndizo zonse! Nyimbo!

• Konzaninso crosstab kukhala yosalala yoyenera kumanga matebulo a pivot
• Kupanga tchati chojambulidwa mu Power View
• Macro kuti asonkhanitse mapepala kuchokera ku mafayilo osiyanasiyana a Excel kukhala amodzi