I-Parameterizing Izindlela Zedatha Embuzweni Wamandla

Uma usuqalile ukusebenzisa amathuluzi we-add-in ye-Power Query ku-Microsoft Excel, khona-ke maduze nje uzobhekana nenkinga eyodwa ekhethekile kakhulu, kodwa evame kakhulu futhi ecasulayo ehambisana nokuphulwa njalo kwezixhumanisi zomthombo wedatha. Ingqikithi yenkinga ukuthi uma embuzweni wakho ubhekisela kumafayela angaphandle noma amafolda, i-Power Query ifaka amakhodi eqinile indlela eya kuwo embhalweni wombuzo. Konke kusebenza kahle kukhompyutha yakho, kodwa uma unquma ukuthumela ifayela ngesicelo kozakwenu, khona-ke bayodumazeka, ngoba. banendlela ehlukile yomthombo wedatha kukhompuyutha yabo, futhi umbuzo wethu ngeke usebenze.

Yini okufanele uyenze esimweni esinjalo? Ake sibheke leli cala ngokuningiliziwe ngesibonelo esilandelayo.

Ukwakhiwa kwenkinga

Ake sithi sinaso kufolda E:Imibiko yokuthengisa lilele ifayela Imikhiqizo engu-100 ephezulu.xls, okulayishwe kusuka kusizindalwazi sethu sezinkampani noma uhlelo lwe-ERP (1C, SAP, njll.) Leli fayela liqukethe ulwazi mayelana nezinto ezithengiswa kakhulu futhi libukeka kanje ngaphakathi:

Cishe kucace zisuka nje ku-bat ukuthi cishe akunakwenzeka ukusebenza nayo ku-Excel kuleli fomu: imigqa engenalutho kweyodwa enedatha, amaseli ahlanganisiwe, amakholomu engeziwe, unhlokweni onamazinga amaningi, njll. kuzophazamisa.

Ngakho-ke, eduze kwaleli fayela kufolda efanayo, sakha elinye ifayela elisha I-Handler.xlsx, lapho sizodala umbuzo Wombuzo Wamandla ozolayisha idatha embi esuka kufayela elilayishiwe elingumthombo Imikhiqizo engu-100 ephezulu.xls, futhi uzihlele kahle:

Ukwenza isicelo kufayela langaphandle

Ivula ifayela I-Handler.xlsx, khetha kuthebhu Idatha Umyalo Thola Idatha - Kusuka Efayeleni - Kusuka ku-Excel Workbook (Idatha - Thola Idatha - Kusuka kufayela - Kusuka ku-Excel), bese ucacisa indawo yefayela elingumthombo neshidi esilidingayo. Idatha ekhethiwe izolayishwa kusihleli Sombuzo Wamandla:

Masizibuyisele kokujwayelekile:

1. Susa imigqa engenalutho nge Ikhaya — Susa imigqa — Susa imigqa engenalutho (Ikhaya - Khipha Imigqa - Khipha Imigqa Engenalutho).
2. Susa imigqa emi-4 ephezulu ngokungadingekile Ikhaya - Susa Imigqa - Susa Imigqa Ephezulu (Ikhaya - Susa Imigqa - Susa Imigqa Ephezulu).
3. Phakamisa umugqa wokuqala kunhlokweni yethebula ngenkinobho Sebenzisa umugqa wokuqala njengezihloko ithebhu Ikhaya (Ekhaya - Sebenzisa umugqa wokuqala njengesihloko).
4. Hlukanisa i-athikili enezinhlamvu ezinhlanu egameni lomkhiqizo kukholamu yesibili usebenzisa umyalo ikholomu ehlukanisayo ithebhu Uguquko (Guqula — Hlukanisa Ikholomu).
5. Susa amakholomu angadingekile futhi uqambe kabusha izihloko zalawo asele ukuze abonakale kangcono.

Ngenxa yalokho, kufanele sithole okulandelayo, isithombe esimnandi kakhulu:

Kusele ukulayisha leli thebula le-ennobled emuva eshidini efayelini lethu I-Handler.xlsx iqembu vala futhi ulande (Ekhaya — Vala&Layisha) ithebhu Ikhaya:

Ukuthola indlela eya efayeleni esicelweni

Manje ake sibone ukuthi umbuzo wethu ubukeka kanjani “ngaphansi kwe-hood”, ngolimi lwangaphakathi olwakhelwe kokuthi Umbuzo Wamandla ngegama elifushane elithi “M”. Ukuze wenze lokhu, buyela emuva embuzweni wethu ngokuchofoza kabili kuwo kufasitelana elingakwesokudla Izicelo nokuxhumana nakuthebhu Buyekeza khetha Umhleli Othuthukile (Buka — Isihleli Esithuthukile):

Efasiteleni elivulekayo, umugqa wesibili uveza ngokushesha indlela enekhodi eqinile eya kufayela lethu loqobo lokulayisha. Uma singashintsha lolu chungechunge lombhalo ngepharamitha, okuguquguqukayo, noma isixhumanisi seseli yeshidi le-Excel lapho le ndlela ibhalwe khona ngaphambili, khona-ke singayishintsha kalula kamuva.

Engeza ithebula elihlakaniphile elinendlela yefayela

Masivale Umbuzo Wamandla okwamanje futhi sibuyele kufayela lethu I-Handler.xlsx. Ake sengeze ishidi elisha elingenalutho futhi senze ithebula elincane “elihlakaniphile” kulo, kuseli okuwukuphela kwalo okuzobhalwa kulo indlela egcwele eya kufayela lomthombo wethu wedatha:

Ukuze udale ithebula elihlakaniphile ukusuka ebangeni elivamile, ungasebenzisa isinqamuleli sekhibhodi Ctrl+T noma inkinobho Fometha njengetafula ithebhu Ikhaya (Ekhaya — Fometha Njengethebula). Isihloko sekholomu (iseli A1) singaba yinoma yini ngokuphelele. Qaphela futhi ukuthi ukuze kucace ngilinike ithebula igama Amapharamitha ithebhu umakhi (Umklamo).

Ukukopisha indlela evela ku-Explorer noma ngisho nokuyifaka ngesandla, yiqiniso, akunzima kakhulu, kodwa kungcono kakhulu ukunciphisa isici somuntu futhi unqume indlela, uma kungenzeka, ngokuzenzakalelayo. Lokhu kungenziwa kusetshenziswa umsebenzi ojwayelekile weshidi le-Excel ISELULEKILE (ISELI), enganikeza inqwaba yolwazi oluwusizo mayelana neseli elicaciswe njengempikiswano - okuhlanganisa indlela eya kufayela lamanje:

Uma sicabanga ukuthi ifayela ledatha yomthombo lihlala likufolda efanayo ne-Prosesa yethu, khona-ke indlela esiyidingayo ingakhiwa ngefomula elandelayo:

=LEFT(ISELI(“igama lefayela”);THOLA(“[“;ISELI(“igama lefayela”))-1)&”Imikhiqizo eyi-100 ephezulu.xls”

noma ngenguqulo yesiNgisi:

=KUSELE(ISELI(«igama lefayela»); THOLA(«[«;ISELI(«igama lefayela»))-1)&»Топ-100 товаров.xls»

… uphi umsebenzi I-LEVSIMV (KUSELE) ithatha ucezu lombhalo kusuka kusixhumanisi esigcwele kuya kubakaki abayisikwele abavulayo (okungukuthi indlela eya kufolda yamanje), bese igama nesandiso sefayela lomthombo wethu wedatha sinamathiselwe kuso.

Yenza ipharamitha indlela embuzweni

Ukuthinta kokugcina nokubaluleke kakhulu kusasele - ukubhala indlela eya efayeleni lomthombo esicelweni Imikhiqizo engu-100 ephezulu.xls, ibhekisela kuseli A2 lethebula lethu “elihlakaniphile” elidaliwe Amapharamitha.

Ukuze senze lokhu, ake sibuyele embuzweni Wombuzo Wamandla bese siwuvula futhi Umhleli Othuthukile ithebhu Buyekeza (Buka — Isihleli Esithuthukile). Esikhundleni somugqa weyunithi yezinhlamvu ezicashuniwe “E:Imibiko yokuthengisaImikhiqizo eyi-100 ephezulu.xlsx” Ake sethule isakhiwo esilandelayo:

I-Excel.CurrentWorkbook(){[Igama="Izilungiselelo"][Okuqukethwe]0 {}[Indlela eya emthonjeni wedatha]

Ake sibone ukuthi ihlanganisani:

• I-Excel.CurrentWorkbook() kuwumsebenzi wolimi lwe-M lokufinyelela okuqukethwe kwefayela lamanje
• {[Igama="Izilungiselelo"][Okuqukethwe] - lena ipharamitha yokucwengisiswa komsebenzi wangaphambilini, okubonisa ukuthi sifuna ukuthola okuqukethwe kwethebula elithi "smart" Amapharamitha
• [Indlela eya emthonjeni wedatha] yigama lekholomu kuthebula Amapharamithaesibhekisela kukho
  
• 0 {} inombolo yomugqa etafuleni Amapharamithalapho sifuna ukuthatha khona idatha. I-cap ayibali futhi izinombolo ziqala ziro, hhayi kweyodwa.

Yilokho kuphela, eqinisweni.

Kusele ukuchofoza Qeda futhi uhlole ukuthi isicelo sethu sisebenza kanjani. Manje, lapho uthumela yonke ifolda ngawo womabili amafayela ngaphakathi kwenye i-PC, isicelo sizohlala sisebenza futhi sinquma indlela eya kudatha ngokuzenzakalelayo.

• Uyini Umbuzo Wamandla futhi kungani udingeka lapho usebenza ku-Microsoft Excel
• Uwangenisa kanjani amazwibela ombhalo antantayo kokuthi Umbuzo Wamandla
• Ukudizayina kabusha i-XNUMXD Crosstab ibe yi-Flat Table enombuzo wamandla