LAMBDA ndi Ntchito Yatsopano Yatsopano ya Excel

Pakadali pano, Microsoft Excel ili ndi ntchito pafupifupi mazana asanu zamasamba zomwe zimapezeka kudzera pawindo la Function Wizard - batani fx mu bar formula. Iyi ndi seti yabwino kwambiri, komabe, pafupifupi wogwiritsa ntchito aliyense posakhalitsa amakumana ndi vuto lomwe mndandandawu ulibe ntchito yomwe amafunikira - chifukwa chakuti mulibe mu Excel.

Mpaka pano, njira yokhayo yothetsera vutoli inali macros, mwachitsanzo, kulemba ntchito yanu yodziwika ndi wogwiritsa ntchito (UDF = User Defined Function) mu Visual Basic, zomwe zimafuna luso loyenera la mapulogalamu ndipo nthawi zina zimakhala zovuta nkomwe. Komabe, ndi zosintha zaposachedwa za Office 365, zinthu zasintha bwino - ntchito yapadera ya "wrapper" yawonjezedwa ku Excel. LAMBDA. Ndi chithandizo chake, ntchito yopangira ntchito zanu tsopano yathetsedwa mosavuta komanso mokongola.

Tiyeni tione mfundo ya ntchito yake mu chitsanzo chotsatira.

Monga mukudziwira, Excel ili ndi ntchito zingapo zoyika masiku zomwe zimakupatsani mwayi wodziwa kuchuluka kwa tsiku, mwezi, sabata, ndi chaka pa tsiku lomwe mwapatsidwa. Koma pazifukwa zina palibe ntchito yomwe imatsimikizira kuchuluka kwa kotala, komwe kumafunikanso nthawi zambiri, sichoncho? Tiyeni tikonze cholakwika ichi ndikupanga ndi LAMBDA ntchito yanu yatsopano yothetsera vutoli.

Gawo 1. Lembani ndondomekoyi

Tiyeni tiyambe ndi mfundo yakuti pamanja mwachizolowezi tidzalemba fomula mu cell cell yomwe imawerengera zomwe tikufuna. Pankhani ya nambala ya kotala, izi zitha kuchitika, mwachitsanzo, motere:

Gawo 2. Kukulunga mu LAMBDA ndikuyesa

Tsopano ndi nthawi yoti mugwiritse ntchito LAMBDA yatsopano ndikukulunga fomula yathu mmenemo. Syntax ya ntchito ndi motere:

=LAMBDA(Zosintha1; Zosintha2; … ZosinthaN ; mawu)

pomwe mayina amitundu imodzi kapena zingapo adandandalikidwa koyamba, ndipo mtsutso womaliza nthawi zonse umakhala chilinganizo kapena mawu owerengeka omwe amawagwiritsa ntchito. Mayina osinthika asakhale ngati ma adilesi amafoni ndipo asakhale ndi madontho.

Kwa ife, padzakhala kusintha kumodzi kokha - tsiku limene timawerengera nambala ya kotala. Tiyeni tiyitane kusinthika kwake, kunena, d. Ndiye kukulunga chilinganizo chathu mu ntchito LAMBDA ndikulowetsa adilesi ya cell A2 yoyambirira ndi dzina lopeka losinthika, timapeza:

Chonde dziwani kuti pambuyo pa kusinthika kotere, chilinganizo chathu (kwenikweni, cholondola!) chinayamba kutulutsa cholakwika, chifukwa tsopano tsiku loyambirira kuchokera ku selo A2 silinasamutsidwe kwa ilo. Pakuyesa ndi chidaliro, mutha kupereka mikangano powonjezerapo pambuyo pa ntchitoyi LAMBDA m'makolo:

Gawo 3. Pangani dzina

Tsopano kwa gawo losavuta komanso losangalatsa. Timatsegula Woyang'anira Dzina tsamba chilinganizo (Mafomula - Woyang'anira Dzina) ndi kupanga dzina latsopano ndi batani Pangani (Pangani). Bwerani ndikuyika dzina la ntchito yathu yamtsogolo (mwachitsanzo, Nomkvartala), ndi m'munda Lumikizani (Buku) koperani mosamala kuchokera pa formula bar ndikumata ntchito yathu LAMBDA, pokhapokha popanda mkangano womaliza (A2):

Chirichonse. Pambuyo kuwonekera pa OK zomwe zidapangidwa zitha kugwiritsidwa ntchito muselo iliyonse patsamba lililonse la bukhuli:

Gwiritsani ntchito m'mabuku ena

LAMBDA ndi ma dynamic arrays

Zochita zokhazikika zomwe zimapangidwa ndi ntchito LAMBDA kuthandizira bwino ntchito ndi magulu atsopano osinthika ndi ntchito zawo (FILTER, UNIK, kalasi) adawonjezeredwa ku Microsoft Excel mu 2020.

Tiyerekeze kuti tikufuna kupanga ntchito yatsopano yofotokozedwa ndi ogwiritsa ntchito yomwe ingafanizire mindandanda iwiri ndikubwezeretsanso kusiyana pakati pawo - zinthu zomwe zili pamndandanda woyamba zomwe sizili wachiwiri. Ntchito ya moyo, sichoncho? M'mbuyomu, adagwiritsa ntchito ntchito za la VPR (VLOOKUP), kapena PivotTables, kapena mafunso a Power Query. Tsopano mutha kuchita ndi fomula imodzi:

M'Chingerezi zikhala:

=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)

Apa ntchito COUNTIF imawerengera kuchuluka kwa zochitika za chinthu chilichonse cha mndandanda woyamba wachiwiri, ndiyeno ntchitoyo FILTER amasankha okhawo omwe sanachite izi. Pokukuta kamangidwe kameneka LAMBDA ndikupanga mndandanda wotchulidwa kutengera dzina, mwachitsanzo, KUGAWANIDWA KWA SEARCH - tipeza ntchito yabwino yomwe imabweretsa zotsatira zofananiza mindandanda iwiri mu mawonekedwe amitundu yosiyanasiyana:

Ngati magwero a data si wamba, koma matebulo "anzeru", ntchito yathu idzatha popanda mavuto:

Chitsanzo china ndikugawa mawu mosinthika powasintha kukhala XML kenako kuwagawa selo ndi selo pogwiritsa ntchito FILTER.XML yomwe tasankha posachedwa. Kuti musapangenso fomu yovutayi pamanja nthawi zonse, zidzakhala zosavuta kuzikulunga mu LAMBDA ndikupanga mawonekedwe osinthika motengera momwemo, mwachitsanzo, ntchito yatsopano komanso yosavuta, kuyitcha, mwachitsanzo, RAZDTEXT:

Mtsutso woyamba wa ntchitoyi udzakhala selo yokhala ndi mawu oyambira, ndipo yachiwiri - mawonekedwe olekanitsa, ndipo idzabwezera zotsatira zake ngati mawonekedwe osakanikirana. Code ntchito idzakhala motere:

=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“"&SUBSTITUTE (t;d? "«)&»";"//Y")))

Mndandanda wa zitsanzo ndi wopanda malire - nthawi iliyonse yomwe nthawi zambiri mumayenera kuyika fomula yayitali komanso yovuta, ntchito ya LAMBDA ipangitsa moyo kukhala wosavuta.

Kubwerezabwereza kwa zilembo

Zitsanzo zonse zam'mbuyomu zawonetsa gawo limodzi lokha, lodziwikiratu kwambiri, la ntchito ya LAMBDA - kugwiritsa ntchito kwake ngati "chokutira" pakukulunga ma formula ataliatali momwemo ndikuchepetsa zomwe alowetsa. M'malo mwake, LAMBDA ili ndi mbali ina, yozama kwambiri, yomwe imasandutsa pafupifupi chilankhulo chokwanira.

Chowonadi ndi chakuti chinthu chofunikira kwambiri pazantchito za LAMBDA ndikutha kuzigwiritsa ntchito kubwereza - kulingalira kwa mawerengedwe, pamene mukuwerengera ntchitoyo imadzitcha yokha. Kuchokera pachizoloŵezicho, zikhoza kumveka ngati zowopsya, koma mu mapulogalamu, kubwereza ndi chinthu chofala. Ngakhale mu macros mu Visual Basic, mutha kuyigwiritsa ntchito, ndipo tsopano, monga mukuwonera, yabwera ku Excel. Tiyeni tiyese kumvetsetsa njira iyi ndi chitsanzo chothandiza.

Tiyerekeze kuti tikufuna kupanga ntchito yofotokozedwa ndi wogwiritsa ntchito yomwe ingachotse zilembo zonse zomwe zaperekedwa kuchokera muzolemba. Kufunika kwa ntchito yotereyi, ndikuganiza, simuyenera kutsimikizira - zingakhale zosavuta kuchotsa deta yotayika ndi chithandizo chake, sichoncho?

Komabe, poyerekeza ndi zitsanzo zam'mbuyo, zosabwerezabwereza, zovuta ziwiri zikutiyembekezera.

1. Tidzayenera kubwera ndi dzina la ntchito yathu tisanayambe kulemba code yake, chifukwa m'menemo, dzinali lidzagwiritsidwa ntchito kale kutchula ntchitoyo.
   
2. Kulowetsa ntchito yobwerezabwereza yotereyi mu selo ndikuyisintha mwa kufotokoza zotsutsana m'mabulaketi pambuyo pa LAMBDA (monga momwe tinachitira poyamba) sizingagwire ntchito. Muyenera kupanga ntchito nthawi yomweyo "kuyambira poyambira" mkati Woyang'anira Dzina (Name Manager).
   

Tiyeni tiyimbe ntchito yathu, tinene, CLEAN ndipo tikufuna kuti ikhale ndi mikangano iwiri - mawuwo ayeretsedwe ndi mndandanda wa zilembo zosaphatikizidwa ngati chingwe chalemba:

Tiyeni tipange, monga tinachitira kale, pa tabu chilinganizo в Woyang'anira dzina dzina lake, litchuleni CLEAR ndikulowa m'munda zosiyanasiyana zomanga zotsatirazi:

=LAMBDA(t;d;IF(d=””;t;CHONSE(MALOWA(t;KULEFT(d);)”);MID(d;2;255))))

Apa zosintha t ndizolemba zoyambirira zomwe ziyenera kuchotsedwa, ndipo d ndiye mndandanda wa zilembo zomwe ziyenera kuchotsedwa.

Zonse zimagwira ntchito motere:

Kusintha 1

Chidutswa SUBSTITUTE(t;LEFT(d);””), monga momwe mungaganizire, chimalowa m'malo mwa munthu woyamba kuchokera kumanzere kuchokera pa set d kuti achotsedwe m'mawu oyambira t ndi chingwe chopanda kanthu, mwachitsanzo, kuchotsa " A”. Monga chotsatira chapakati, timapeza:

Vsh zkz n 125 rubles.

Kusintha 2

Ndiye ntchitoyo imadzitcha yokha ndipo monga chothandizira (mkangano woyamba) imalandira zomwe zatsala pambuyo poyeretsa mu sitepe yapitayi, ndipo mkangano wachiwiri ndi mndandanda wa zilembo zosaphatikizidwa kuyambira pachiyambi, koma kuchokera ku khalidwe lachiwiri, mwachitsanzo, "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," popanda "A" yoyambirira - izi zimachitika ndi ntchito ya MID. Monga kale, ntchitoyi imatenga munthu woyamba kumanzere kwa otsalawo (B) ndikulowetsa m'mawu omwe adapatsidwa (Zkz n 125 rubles) ndi chingwe chopanda kanthu - timapeza ngati zotsatira zapakatikati:

125 ru.

Kusintha 3

Ntchitoyi imadzitchanso kachiwiri, kulandira ngati mtsutso woyamba zomwe zatsala m'malembawo kuti zithetsedwe pa nthawi yapitayi (Bsh zkz n 125 ru.), Ndipo monga mtsutso wachiwiri, gulu la zilembo zosaphatikizidwa zimadulidwa ndi munthu wina kumanzere, ie “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” popanda “B” woyambirira. Kenako zimatenganso munthu woyamba kumanzere (B) kuchokera pagawoli ndikuchichotsa palemba - timapeza:

sh zkz n 125 ru.

Ndi zina zotero - ndikuyembekeza kuti mupeza lingaliro. Kubwereza kulikonse, mndandanda wa zilembo zomwe zichotsedwe zimadulidwa kumanzere, ndipo tidzafufuza ndikusintha zilembo zina kuchokera pagululo ndi zopanda kanthu.

Pamene zilembo zonse zatha, tidzafunika kuchoka pamzerewu - udindowu umangochitidwa ndi ntchitoyi IF (NGATI), momwe mapangidwe athu adakulungidwa. Ngati palibe zilembo zomwe zatsala kuti zichotsedwe (d=””), ndiye kuti ntchitoyo isadziyitanenso, koma ingobweza mawuwo kuti achotsedwe (kusinthika t) m'mawonekedwe ake omaliza.

Kubwerezabwereza kwa ma cell

Mofananamo, mutha kugwiritsa ntchito kuwerengera mobwerezabwereza kwa ma cell mumndandanda womwe waperekedwa. Tiyerekeze kuti tikufuna kupanga ntchito ya lambda yotchedwa ZINTHU ZOSINTHA kuti muthe kusintha magawo ang'onoang'ono m'mawu oyambira malinga ndi mndandanda womwe waperekedwa. Chotsatiracho chiyenera kuwoneka motere:

Iwo. pa ntchito yathu ZINTHU ZOSINTHA padzakhala mikangano itatu:

1. cell yokhala ndi mawu oti asinthe (adilesi yoyambira)
2. cell yoyamba ya mzati yokhala ndi mfundo zoti mufufuze poyang'ana
3. cell yoyamba ya mzati yokhala ndi zikhalidwe zosinthira kuchokera pakuwunika

Ntchitoyi iyenera kuchoka pamwamba mpaka pansi mu bukhu ndikusintha motsatizana zosankha zonse kuchokera kumanzere Kuti mupeze ku zosankha zofananira kuchokera kugawo lakumanja M'malo. Mutha kugwiritsa ntchito izi ndi ntchito yobwerezabwereza ya lambda:

Kusinthana pansi pa kubwereza kulikonse kumayendetsedwa ndi ntchito yokhazikika ya Excel Kutaya (ZOCHITIKA), yomwe ili ndi mfundo zitatu - mzere woyambirira, kusintha kwa mizere (1) ndi kusintha kwa magawo (0).

Chabwino, tikangofika kumapeto kwa chikwatu (n = ""), tiyenera kuthetsa kubwereza - timasiya kudzitcha tokha ndikuwonetsa zomwe zasonkhanitsidwa pambuyo pa kusintha kwa malemba t.

Ndizomwezo. Palibe ma macros ovuta kapena mafunso a Power Query - ntchito yonseyo imathetsedwa ndi ntchito imodzi.

• Momwe mungagwiritsire ntchito magwiridwe antchito atsopano a Excel: FILTER, SORT, UNIC
• Kusintha ndi kukonza malemba ndi ntchito ya SUBSTITUTE
  
• Kupanga ma macros and user-defined function (UDFs) mu VBA