Ikholomu Yesibonelo - Ubuhlakani Obungenziwanga Embuzweni Wamandla

Enye yamavidiyo abukwe kakhulu esiteshini sami se-YouTube yividiyo emayelana nokugcwalisa i-Flash ku-Microsoft Excel. Ingqikithi yaleli thuluzi ukuthi uma udinga ukuguqula idatha yakho yomthombo ngandlela thize, udinga nje ukuqala ukuthayipha umphumela ofuna ukuwuthola kukholamu eseduze. Ngemuva kokuthi amaseli amaningana athayiphiwe ngesandla (imvamisa u-2-3 anele), i-Excel "izoqonda" ingqondo yezinguquko ozidingayo futhi iqhubeke ngokuzenzakalelayo lokho okuthayiphile, iqedele wonke umsebenzi onzima kuwe:

I-quintessence yokusebenza kahle. Inkinobho yomlingo "yenza kahle" sonke esiyithanda kakhulu, akunjalo?

Eqinisweni, kukhona i-analogue yethuluzi elinjalo ku-Power Query - lapho ibizwa khona Ikholomu evela ezibonelweni (Ikholomu evela Ezibonelweni). Eqinisweni, lobu ubuhlakani bokwenziwa obuncane obakhelwe kokuthi Umbuzo Wamandla ongafunda ngokushesha kudatha yakho bese uyiguqula. Ake sibhekisise amakhono ayo ezimeni ezithile ezisebenzayo ukuze siqonde lapho engaba usizo kithi emisebenzini yangempela.

Isibonelo 1. Umbhalo wokunamathisela/ukusika

Ake sithi sinethebula “elihlakaniphile” ku-Excel elinedatha yabasebenzi:

Ilayishe kokuthi Umbuzo Wamandla ngendlela evamile - ngenkinobho Kusukela kuThebula/Ububanzi ithebhu Idatha (Idatha - Kusuka Kuthebula/Ububanzi).

Ake sithi sidinga ukwengeza ikholomu enezibongo nama-initials kumsebenzi ngamunye (I-Ivanov SV yesisebenzi sokuqala, njll.). Ukuxazulula le nkinga, ungasebenzisa enye yezindlela ezimbili:

• chofoza kwesokudla kukholomu yesihloko ngedatha yomthombo bese ukhetha umyalo Engeza ikholomu ezibonelweni (Engeza ikholomu evela ezibonelweni);

• khetha ikholomu eyodwa noma ngaphezulu enedatha nakuthebhu Yengeza ikholomu khetha iqembu Ikholomu evela ezibonelweni. Lapha, kuhlu lokudonsela phansi, ungacacisa ukuthi wonke noma amakholomu akhethiwe kuphela adinga ukuhlaziywa.

Khona-ke yonke into ilula - kukholamu evela ngakwesokudla, siqala ukufaka izibonelo zemiphumela oyifunayo, futhi ubuhlakani bokwenziwa okwakhelwe ku-Power Query buzama ukuqonda umqondo wethu wokuguqula futhi uqhubeke ngokuqhubekayo ngokwakho:

Kodwa-ke, ungafaka izinketho ezilungile kunoma yimaphi amaseli ale kholomu, okungukuthi hhayi phezulu-phansi kanye nomugqa. Futhi, ungakwazi ukwengeza noma ukukhipha kalula amakholomu ekuhlaziyeni kamuva usebenzisa amabhokisi okuhlola kubha yesihloko.

Naka ifomula ephezulu efasiteleni - yilokhu okudalwa yi-smart Power Query ukuze uthole imiphumela esiyidingayo. Lokhu, ngendlela, umehluko oyisisekelo phakathi kwaleli thuluzi kanye Ukugcwalisa ngokushesha ku-Excel. Ukugcwalisa ngokushesha kusebenza njengokuthi “ibhokisi elimnyama” – akusibonisi umqondo wezinguquko, kodwa kumane kunikeze imiphumela esele yenziwe futhi siyithathe kalula. Lapha yonke into isobala futhi ungahlala uqonda ngokucacile ukuthi yini ngempela eyenzekayo ngedatha.

Uma ubona lowo Umbuzo Wamandla "ubambe umbono", ungacindezela ngokuphephile inkinobho OK noma isinqamuleli sekhibhodi Ctrl+Faka - ikholomu yangokwezifiso enefomula esungulwe i-Power Query izokwakhiwa. Ngendlela, kamuva ingahlelwa kalula njengekholomu evamile eyenziwe ngesandla (ngomyalo Ukwengeza Ikholomu – Ikholomu Ngokwezifiso) ngokuchofoza isithonjana segiya kwesokudla segama lesinyathelo:

Isibonelo sesi-2: Icala njengasemishweni

Uma uchofoza kwesokudla kukholomu yesihloko esinombhalo bese ukhetha umyalo Uguquko (Guqula), bese ungabona imiyalo emithathu enesibopho sokushintsha irejista:

Ilula futhi ipholile, kodwa kulolu hlu, ngokwesibonelo, mina ngokwami ​​bengilokhu ngintula enye inketho - icala njengasemishweni, lapho capitalization (capital) ingabi uhlamvu lokuqala egameni ngalinye, kodwa kuphela uhlamvu lokuqala esitokisini, futhi wonke umbhalo uma Lokhu kuvezwe ngezinhlamvu ezincane (amancane).

Lesi sici esingekho kulula ukusisebenzisa ngobuhlakani bokwenziwa Amakholomu avela ezibonelweni - vele ufake izinketho ezimbalwa ze-Power Query ukuze uqhubeke ngomoya ofanayo:

Njengefomula lapha, Umbuzo Wamandla usebenzisa inqwaba yemisebenzi Umbhalo.Phezulu и Umbhalo.Ngezansi, ukuguqula umbhalo ube ngosonhlamvukazi nabancane, ngokulandelana, kanye nemisebenzi Umbhalo.Qala и Umbhalo.Maphakathi - ama-analogue emisebenzi ye-Excel LEFT kanye ne-PSTR, ekwazi ukukhipha umucu ongaphansi embhalweni ukusuka kwesokunxele naphakathi.

Isibonelo 3. Ukuvunyelwa kwamagama

Kwesinye isikhathi, lapho kusetshenzwa idatha etholiwe, kuba nesidingo sokuhlela kabusha amagama kumaseli ngokulandelana okunikiwe. Yiqiniso, ungakwazi ukuhlukanisa ikholomu ibe amakholomu egama ahlukene ngesihlukanisi bese uyinamathisela futhi ngokulandelana okucacisiwe (ungakhohlwa ukwengeza izikhala), kodwa ngosizo lwethuluzi. Ikholomu evela ezibonelweni konke kuzoba lula kakhulu:

Isibonelo sesi-4: Izinombolo kuphela

Omunye umsebenzi obaluleke kakhulu ukukhipha izinombolo (izinombolo) kuphela kokuqukethwe kweseli. Njengangaphambili, ngemva kokulayisha idatha kokuthi Umbuzo Wamandla, hamba kuthebhu Ukwengeza ikholomu – Ikholomu evela ezibonelweni futhi ugcwalise amaseli ambalwa mathupha ukuze uhlelo luqonde ukuthi yini ngempela esifuna ukuyithola:

Bingo!

Futhi, kufanelekile ukubheka phezulu efasiteleni ukuze uqiniseke ukuthi Umbuzo ukhiqize ifomula ngendlela efanele - kulokhu uqukethe umsebenzi. Umbhalo. Khetha, okuthi, njengoba ungase uqagele, ikhiphe izinhlamvu ezinikeziwe embhalweni ongumthombo ngokohlu. Ngokulandelayo, lolu hlu, kunjalo, lungahlelwa kalula kubha yefomula uma kunesidingo.

Isibonelo sesi-5: Thumela umbhalo kuphela

Ngokufanayo nesibonelo sangaphambilini, ungakhipha futhi ngokuphambene nalokho - umbhalo kuphela, ukususa zonke izinombolo, izimpawu zokubhala, njll.

Kulokhu, umsebenzi osuvele uphambene ngokwencazelo uyasetshenziswa - Umbhalo.Susa, okhipha izinhlamvu entanjeni yokuqala ngokohlu olunikeziwe.

Isibonelo sesi-6: Ukukhipha idatha ephalishini lezinhlamvu nezinombolo

Umbuzo Wamandla ungasiza futhi ezimeni ezinzima kakhulu, lapho udinga ukukhipha ulwazi oluwusizo kuphalishi lezinhlamvu nezinombolo kuseli, isibonelo, thola inombolo ye-akhawunti encazelweni yenjongo yokukhokha esitatimendeni sasebhange:

Qaphela ukuthi ifomula yokuguqula ekhiqizwe Umbuzo Wamandla ingaba yinkimbinkimbi kakhulu:

Ukuze kube lula ukufunda nokuqonda, kungaguqulelwa kufomu elihlakaniphe kakhulu kusetshenziswa isevisi ye-inthanethi yamahhala. Ifomethi Yombuzo Wamandla:

Into ewusizo kakhulu - inhlonipho kubadali!

Isibonelo sesi-7: Ukuguqula izinsuku

Ithuluzi Ikholomu evela ezibonelweni ingasetshenziswa kumakholomu wedethi noma wedethi futhi. Uma ufaka amadijithi okuqala edethi, Umbuzo Wamandla uzobonisa ngokuwusizo uhlu lwazo zonke izinketho zokuguqulwa okungenzeka:

Ngakho-ke ungakwazi ukuguqula kalula idethi yokuqala ibe yinoma iyiphi ifomethi yangaphandle, njengokuthi "usuku lwenyanga":

Isibonelo sesi-8: Ukuhlukaniswa ngezigaba

Uma sisebenzisa ithuluzi Ikholomu evela ezibonelweni kukholomu enedatha yezinombolo, isebenza ngendlela ehlukile. Ake sithi sinemiphumela yokuhlolwa kwezisebenzi elayishwe kokuthi Umbuzo Wamandla (amaphuzu anemibandela ebangeni elingu-0-100) futhi sisebenzisa ukugreda okunemibandela okulandelayo:

• Amakhosi - labo abathole amaphuzu angaphezu kuka-90
• Ochwepheshe - bathola amaphuzu kusuka ku-70 kuya ku-90
• Abasebenzisi - kusuka ku-30 ​​kuye ku-70
• Abasaqalayo - labo abathole amaphuzu angaphansi kwama-30

Uma sengeza ikholomu kusuka ezibonelweni ohlwini bese siqala ukuhlela lezi zigaba mathupha, khona-ke maduze i-Power Query izothatha umbono wethu futhi yengeze ikholomu enefomula, lapho o-opharetha bahlangana khona komunye nomunye. if logic izosetshenziswa, efana kakhulu nalokhu esikudingayo:

Futhi, awukwazi ukucindezela isimo kuze kube sekupheleni, kodwa chofoza OK bese ulungisa amavelu e-threshold asekufomula - kuyashesha ngale ndlela:

iziphetho

Impela ithuluzi Ikholomu evela ezibonelweni akulona "iphilisi lomlingo" futhi, ngokushesha noma kamuva, kuzoba nezimo ezingezona ezijwayelekile noma izimo ezinganakwa ikakhulukazi "zepulazi elihlangene" kudatha, lapho Umbuzo Wamandla uzohluleka futhi ngeke ukwazi ukwenza lokho esikufunayo. kahle kithi. Nokho, njengethuluzi elisizayo, lihle kakhulu. Futhi, ngokutadisha amafomula awakhiqizile, ungakwazi ukwandisa ulwazi lwakho lwemisebenzi yolimi lwe-M, oluzohlala luwusizo esikhathini esizayo.

• Ukuhlaziya Umbhalo Ngezinkulumo Ezivamile (RegExp) Kumbuzo Wamandla
• Ukusesha umbhalo okungaqondakali kokuthi Umbuzo Wamandla
• I-Flash Fill ku-Microsoft Excel