LAMBDA ir Excel jaunā superfunkcija

Pašlaik programmā Microsoft Excel ir gandrīz pieci simti darblapu funkciju, kas pieejamas, izmantojot funkciju vedņa logu – pogu fx formulas joslā. Šis ir ļoti pieklājīgs komplekts, taču, neskatoties uz to, gandrīz katrs lietotājs agrāk vai vēlāk saskaras ar situāciju, kad šajā sarakstā nav vajadzīgās funkcijas - vienkārši tāpēc, ka tā nav programmā Excel.

Līdz šim vienīgais veids, kā atrisināt šo problēmu, bija makro, proti, savas lietotāja definētas funkcijas (UDF = User Defined Function) rakstīšana programmā Visual Basic, kas prasa atbilstošas ​​programmēšanas prasmes un dažkārt nemaz nav viegli. Taču līdz ar jaunākajiem Office 365 atjauninājumiem situācija ir mainījusies uz labo pusi – programmai Excel ir pievienota īpaša “wrapper” funkcija LAMBDA. Ar tās palīdzību tagad viegli un skaisti tiek atrisināts uzdevums izveidot savas funkcijas.

Apskatīsim tā izmantošanas principu nākamajā piemērā.

Kā jūs, visticamāk, zināt, programmā Excel ir vairākas datuma parsēšanas funkcijas, kas ļauj noteikt dienas, mēneša, nedēļas un gada skaitu konkrētajam datumam. Bet nez kāpēc nav funkcijas, kas nosaka ceturkšņa numuru, kas arī bieži ir vajadzīgs, vai ne? Novērsīsim šo trūkumu un veidosim ar LAMBDA savu jaunu funkciju, lai atrisinātu šo problēmu.

1. solis. Uzrakstiet formulu

Sāksim ar to, ka manuāli parastajā veidā mēs lapas šūnā ierakstīsim formulu, kas aprēķina mums nepieciešamo. Ceturkšņa numura gadījumā to var izdarīt, piemēram, šādi:

2. solis. Iesaiņošana LAMBDA un testēšana

Tagad ir pienācis laiks lietot jauno LAMBDA funkciju un iekļaut tajā mūsu formulu. Funkcijas sintakse ir šāda:

=LAMBDA(Mainīgais1; Mainīgais2; … MainīgaisN ; izteiksme)

kur vispirms ir norādīti viena vai vairāku mainīgo nosaukumi, bet pēdējais arguments vienmēr ir formula vai aprēķināta izteiksme, kas tos izmanto. Mainīgo nosaukumi nedrīkst izskatīties kā šūnu adreses, un tajos nedrīkst būt punkti.

Mūsu gadījumā būs tikai viens mainīgais – datums, kuram mēs aprēķinām ceturkšņa numuru. Sauksim mainīgo, teiksim, d. Pēc tam mūsu formulas iesaiņošana funkcijā LAMBDA un aizstājot sākotnējās šūnas A2 adresi ar fiktīvu mainīgā nosaukumu, mēs iegūstam:

Lūdzu, ņemiet vērā, ka pēc šādas transformācijas mūsu formula (patiesībā pareizi!) sāka radīt kļūdu, jo tagad sākotnējais datums no šūnas A2 uz to netiek pārsūtīts. Pārbaudei un pārliecībai varat tam nodot argumentus, pievienojot tos pēc funkcijas LAMBDA iekavās:

3. darbība. Izveidojiet nosaukumu

Tagad par vieglo un jautro daļu. Atveram Nosaukuma vadītājs tab formula (Formulas — vārdu pārvaldnieks) un izveidojiet jaunu nosaukumu ar pogu izveidot (Izveidot). Izdomājiet un ievadiet mūsu turpmākās funkcijas nosaukumu (piemēram, Nomkvartala), un laukā saite (Atsauce) uzmanīgi nokopējiet no formulas joslas un ielīmējiet mūsu funkciju LAMBDA, tikai bez pēdējā argumenta (A2):

Viss. Pēc noklikšķināšanas uz OK izveidoto funkciju var izmantot jebkurā šūnā jebkurā šīs darbgrāmatas lapā:

Izmanto citās grāmatās

LAMBDA un dinamiskie masīvi

Pielāgotas funkcijas, kas izveidotas ar funkciju LAMBDA veiksmīgi atbalstīt darbu ar jauniem dinamiskiem masīviem un to funkcijām (FILTER, UNIK, GRADE) tika pievienots programmai Microsoft Excel 2020. gadā.

Pieņemsim, ka mēs vēlamies izveidot jaunu lietotāja definētu funkciju, kas salīdzinātu divus sarakstus un atgrieztu starpību starp tiem – tos elementus no pirmā saraksta, kas nav otrajā sarakstā. Dzīves darbs, vai ne? Iepriekš šim nolūkam viņi izmantoja vai nu funkcijas a la VPR (MEKLĒT), vai Rakurstabulas vai Power Query vaicājumi. Tagad jūs varat darīt ar vienu formulu:

Angļu valodas versijā tas būs:

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

Šeit ir funkcija COUNTIF saskaita katra pirmā saraksta elementa atkārtojumu skaitu otrajā un pēc tam funkcijas FILTER atlasa tikai tos, kuriem šie gadījumi nav bijuši. Ietinot šo struktūru LAMBDA un izveidot nosauktu diapazonu, pamatojoties uz to ar nosaukumu, piemēram, MEKLĒŠANAS IZPLATĪŠANA - mēs iegūsim ērtu funkciju, kas atgriež divu sarakstu salīdzināšanas rezultātu dinamiska masīva veidā:

Ja avota dati nav parastas, bet “gudras” tabulas, arī mūsu funkcija tiks galā bez problēmām:

Vēl viens piemērs ir teksta dinamiska sadalīšana, pārvēršot to XML formātā un pēc tam parsējot to šūnā pa šūnai, izmantojot funkciju FILTER.XML, ko nesen parsējām. Lai šo sarežģīto formulu katru reizi neatveidotu manuāli, to būs vieglāk ietīt LAMBDA un uz tās bāzes izveidot dinamisko diapazonu, proti, jaunu kompaktu un ērtu funkciju, nosaucot to, piemēram, RAZDTEXT:

Šīs funkcijas pirmais arguments būs šūna ar avota tekstu, bet otrais – atdalīšanas rakstzīme, un tas atgriezīs rezultātu horizontāla dinamiska masīva formā. Funkcijas kods būs šāds:

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

Piemēru saraksts ir bezgalīgs – jebkurā situācijā, kad bieži nākas ievadīt vienu un to pašu garo un smagnējo formulu, LAMBDA funkcija dzīvi ievērojami atvieglos.

Rekursīvs rakstzīmju uzskaitījums

Visi iepriekšējie piemēri ir parādījuši tikai vienu, visredzamāko, LAMBDA funkcijas pusi – tās izmantošanu kā “iesaiņojumu”, lai tajā ietītu garas formulas un vienkāršotu to ievadi. Faktiski LAMBDA ir vēl viena, daudz dziļāka puse, kas to pārvērš gandrīz pilnvērtīgā programmēšanas valodā.

Fakts ir tāds, ka LAMBDA funkciju būtiska iezīme ir iespēja tās ieviest rekursija – aprēķinu loģika, kad aprēķina procesā funkcija izsauc sevi. No ieraduma tas var izklausīties rāpojoši, taču programmēšanā rekursija ir izplatīta lieta. Pat Visual Basic makro var to ieviest, un tagad, kā redzat, tas ir nonācis programmā Excel. Mēģināsim izprast šo tehniku ​​ar praktisku piemēru.

Pieņemsim, ka mēs vēlamies izveidot lietotāja definētu funkciju, kas noņemtu visas norādītās rakstzīmes no avota teksta. Šādas funkcijas lietderība, manuprāt, nav jāpierāda – ar tās palīdzību būtu ļoti ērti notīrīt piegružotus ievades datus, vai ne?

Tomēr, salīdzinot ar iepriekšējiem, nerekursīviem piemēriem, mūs sagaida divas grūtības.

1. Mums būs jāizdomā nosaukums savai funkcijai, pirms sākam rakstīt tās kodu, jo tajā šis nosaukums jau tiks izmantots pašas funkcijas izsaukšanai.
   
2. Šādas rekursīvas funkcijas ievadīšana šūnā un atkļūdošana, norādot argumentus iekavās aiz LAMBDA (kā mēs to darījām iepriekš), nedarbosies. Jums nekavējoties būs jāizveido funkcija “no nulles”. Nosaukuma vadītājs (Vārdu pārvaldnieks).
   

Sauksim mūsu funkciju, teiksim, CLEAN, un mēs vēlamies, lai tai būtu divi argumenti – tīrāmais teksts un izslēgto rakstzīmju saraksts kā teksta virkne:

Izveidosim cilnē tāpat kā iepriekš formula в Vārda pārvaldnieks nosaukts diapazons, nosauciet to CLEAR un ievadiet laukā Diapazons sekojoša konstrukcija:

=LAMBDA(t;d;IF(d="";t;CLEAR(AIZSTĀTĀJS(t;LEFT(d);"");VIDĒJS(d;2;255))))

Šeit mainīgais t ir sākotnējais teksts, kas jādzēš, un d ir dzēšamo rakstzīmju saraksts.

Tas viss darbojas šādi:

1. atkārtojums

Fragments SUBSTITUTE(t;LEFT(d);""), kā jūs varētu nojaust, avota tekstā t aizstāj pirmo rakstzīmi no kreisās rakstzīmes no kopas d, kas dzēš avota tekstā t ar tukšu teksta virkni, ti, noņem " A”. Kā starprezultātu mēs iegūstam:

Vsh zkz n 125 rubļi.

2. atkārtojums

Pēc tam funkcija izsauc sevi un kā ievadi (pirmais arguments) saņem to, kas palicis pāri pēc tīrīšanas iepriekšējā solī, bet otrais arguments ir izslēgto rakstzīmju virkne, kas sākas nevis no pirmās, bet otrās rakstzīmes, ti, “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ”, bez sākuma burta “A” – to dara MID funkcija. Tāpat kā iepriekš, funkcija paņem pirmo rakstzīmi no kreisās puses no atlikušajām (B) un aizstāj to tai dotajā tekstā (Zkz n 125 rubļi) ar tukšu virkni - mēs iegūstam kā starprezultātu:

125 ru.

3. atkārtojums

Funkcija izsauc sevi vēlreiz, kā pirmo argumentu saņemot to, kas ir palicis pāri no teksta, kas jādzēš iepriekšējā iterācijā (Bsh zkz n 125 ru.), un kā otro argumentu izslēgto rakstzīmju kopa, kas saīsināta ar vēl vienu rakstzīmi. pa kreisi, ti, “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.”, bez sākuma burta “B”. Pēc tam tas atkal paņem pirmo rakstzīmi no šīs kopas no kreisās puses (B) un noņem to no teksta - mēs iegūstam:

sh zkz n 125 ru.

Un tā tālāk – ceru, ka sapratāt ideju. Katrā iterācijā noņemamo rakstzīmju saraksts tiks saīsināts kreisajā pusē, un mēs meklēsim un aizstāsim nākamo rakstzīmi no kopas ar tukšumu.

Kad visas rakstzīmes beigsies, mums būs jāiziet no cilpas – šo lomu tikai pilda funkcija IF (JA), kurā ir ietīts mūsu dizains. Ja nav atlicis dzēšamās rakstzīmes (d=””), funkcijai vairs nevajadzētu sevi izsaukt, bet tai vienkārši jāatgriež dzēšamais teksts (mainīgais t) tā galīgajā formā.

Šūnu rekursīva iterācija

Līdzīgi varat ieviest rekursīvu šūnu uzskaitīšanu noteiktā diapazonā. Pieņemsim, ka mēs vēlamies izveidot lambda funkciju ar nosaukumu AIZMAIŅAS SARAKSTS vairumtirdzniecības fragmentu nomaiņai avota tekstā saskaņā ar doto uzziņu sarakstu. Rezultātam vajadzētu izskatīties šādi:

Tie. mūsu pasākumā AIZMAIŅAS SARAKSTS būs trīs argumenti:

1. šūna ar apstrādājamo tekstu (avota adrese)
2. kolonnas pirmā šūna ar vērtībām, kas jāmeklē no uzmeklēšanas
3. kolonnas pirmā šūna ar aizstāšanas vērtībām no uzmeklēšanas

Funkcijai direktorijā vajadzētu virzīties no augšas uz leju un secīgi aizstāt visas opcijas no kreisās kolonnas Atrast uz attiecīgajām opcijām no labās kolonnas Aizstājējs. To var ieviest ar šādu rekursīvo lambda funkciju:

Pārslēgšanu uz leju katrā iterācijā īsteno standarta Excel funkcija APGLABĀŠANA (OFFSET), kam šajā gadījumā ir trīs argumenti – sākotnējais diapazons, rindas maiņa (1) un kolonnas maiņa (0).

Nu, tiklīdz sasniedzam direktorija beigas (n = “”), jābeidz rekursija – beidzam zvanīt paši un avota teksta mainīgajā t attēlojam, kas ir sakrājies pēc visām aizstāšanām.

Tas ir viss. Nav sarežģītu makro vai Power Query vaicājumu — visu uzdevumu atrisina viena funkcija.

• Kā izmantot programmas Excel jaunās dinamiskā masīva funkcijas: FILTER, SORT, UNIC
• Teksta aizstāšana un tīrīšana ar funkciju SUBSTITUTE
  
• Makro un lietotāja definētu funkciju (UDF) izveide VBA