Contents [show]
Op die oomblik het Microsoft Excel byna vyfhonderd werkbladfunksies beskikbaar deur die Function Wizard-venster – knoppie fx in die formulebalk. Dit is 'n baie ordentlike stel, maar desondanks kom byna elke gebruiker vroeër of later 'n situasie teë waar hierdie lys nie die funksie bevat wat hy benodig nie – bloot omdat dit nie in Excel is nie.
Tot nou toe was die enigste manier om hierdie probleem op te los makros, dit wil sê om jou eie gebruikergedefinieerde funksie (UDF = User Defined Function) in Visual Basic te skryf, wat toepaslike programmeringsvaardighede vereis en soms glad nie maklik is nie. Met die jongste Office 365-opdaterings het die situasie egter ten goede verander – 'n spesiale "wrapper"-funksie is by Excel gevoeg LAMBDA. Met sy hulp word die taak om u eie funksies te skep nou maklik en pragtig opgelos.
Kom ons kyk na die beginsel van die gebruik daarvan in die volgende voorbeeld.
Soos u heel waarskynlik weet, het Excel verskeie datumontledingsfunksies waarmee u die nommer van die dag, maand, week en jaar vir 'n gegewe datum kan bepaal. Maar om een of ander rede is daar geen funksie wat die nommer van die kwartaal bepaal nie, wat ook dikwels nodig is, nie waar nie? Kom ons maak hierdie tekortkoming reg en skep met LAMBDA eie nuwe funksie om hierdie probleem op te los.
Stap 1. Skryf die formule
Kom ons begin met die feit dat ons met die hand op die gewone manier 'n formule in 'n bladsel sal skryf wat bereken wat ons benodig. In die geval van die kwartaalnommer kan dit byvoorbeeld so gedoen word:
Stap 2. Omvou in LAMBDA en toets
Nou is dit tyd om die nuwe LAMBDA-funksie toe te pas en ons formule daarin toe te draai. Die funksie sintaksis is soos volg:
=LAMBDA(Veranderlike 1; Veranderlike 2; ... VeranderlikeN ; uitdrukking)
waar die name van een of meer veranderlikes eerste gelys word, en die laaste argument is altyd 'n formule of 'n berekende uitdrukking wat hulle gebruik. Veranderlike name moet nie soos seladresse lyk nie en moet nie kolletjies bevat nie.
In ons geval sal daar net een veranderlike wees – die datum waarvoor ons die kwartaalgetal bereken. Kom ons noem die veranderlike daarvoor, sê, d. Omvou dan ons formule in 'n funksie LAMBDA en deur die adres van die oorspronklike sel A2 met 'n fiktiewe veranderlike naam te vervang, kry ons:
Let asseblief daarop dat na so 'n transformasie ons formule (in werklikheid, korrek!) 'n fout begin produseer het, want nou word die oorspronklike datum van sel A2 nie daarna oorgedra nie. Vir toetsing en selfvertroue, kan jy argumente daaraan oordra deur hulle na die funksie by te voeg LAMBDA tussen hakies:
Stap 3. Skep 'n naam
Nou vir die maklike en prettige deel. Ons maak oop Naam Bestuurder tab formule (Formules — Naambestuurder) en skep 'n nuwe naam met die knoppie Skep (Skep). Kom op en voer 'n naam vir ons toekomstige funksie in (byvoorbeeld, Nomkvartala), en in die veld Link (Verwysing) kopieer versigtig vanaf die formulebalk en plak ons funksie LAMBDA, slegs sonder die laaste argument (A2):
Alles. Nadat u op OK die geskepde funksie kan in enige sel op enige vel van hierdie werkboek gebruik word:
Gebruik in ander boeke
LAMBDA en dinamiese skikkings
Pasgemaakte funksies geskep met 'n funksie LAMBDA ondersteun werk met nuwe dinamiese skikkings en hul funksies suksesvol (FILTER, UNIK, GRAAD) in 2020 by Microsoft Excel gevoeg.
Kom ons sê ons wil 'n nuwe gebruikergedefinieerde funksie skep wat twee lyste sal vergelyk en die verskil tussen hulle sal gee - daardie elemente van die eerste lys wat nie in die tweede is nie. Lewenswerk, is dit nie? Voorheen het hulle hiervoor óf funksies a la gebruik VPR (VERSOEKING), of PivotTables, of Power Query-navrae. Nou kan jy met een formule doen:
In die Engelse weergawe sal dit wees:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Hier is die funksie COUNTIF tel die aantal voorkomste van elke element van die eerste lys in die tweede, en dan die funksie FILTER kies slegs diegene van hulle wat nie hierdie gebeurtenisse gehad het nie. Deur hierdie struktuur in te draai LAMBDA en skep 'n benoemde reeks op grond daarvan met 'n naam, byvoorbeeld, SOEK VERSPREIDING – ons sal 'n gerieflike funksie kry wat die resultaat van die vergelyking van twee lyste in die vorm van 'n dinamiese skikking gee:
As die brondata nie gewone, maar “slim” tabelle is, sal ons funksie ook sonder probleme klaarkom:
Nog 'n voorbeeld is om teks dinamies te verdeel deur dit na XML om te skakel en dit dan sel vir sel te ontleed deur die FILTER.XML-funksie wat ons onlangs ontleed het, te gebruik. Om hierdie komplekse formule nie elke keer met die hand te reproduseer nie, sal dit makliker wees om dit in LAMBDA toe te draai en 'n dinamiese reeks daarop te skep, dws 'n nuwe kompakte en gerieflike funksie, deur dit byvoorbeeld RAZDTEXT te noem:
Die eerste argument van hierdie funksie sal die sel met die bronteks wees, en die tweede - die skeidingskarakter, en dit sal die resultaat in die vorm van 'n horisontale dinamiese skikking terugstuur. Die funksiekode sal soos volg wees:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Die lys voorbeelde is eindeloos – in enige situasie waar jy dikwels dieselfde lang en omslagtige formule moet invoer, sal die LAMBDA-funksie die lewe merkbaar makliker maak.
Rekursiewe opsomming van karakters
Alle vorige voorbeelde het slegs een, die mees voor die hand liggende, kant van die LAMBDA-funksie getoon – die gebruik daarvan as 'n "omhulsel" om lang formules daarin toe te draai en hul insette te vereenvoudig. Trouens, LAMBDA het 'n ander, baie dieper kant wat dit in amper 'n volwaardige programmeertaal verander.
Die feit is dat 'n fundamentele belangrike kenmerk van LAMBDA-funksies die vermoë is om dit in te implementeer rekursie – logika van berekeninge, wanneer in die proses van berekening die funksie homself noem. Van die gewoonte klink dit dalk grillerig, maar in programmering is rekursie 'n algemene ding. Selfs in makro's in Visual Basic, kan jy dit implementeer, en nou, soos jy kan sien, het dit na Excel gekom. Kom ons probeer om hierdie tegniek te verstaan met 'n praktiese voorbeeld.
Gestel ons wil 'n gebruikergedefinieerde funksie skep wat alle gegewe karakters uit die bronteks sal verwyder. Die bruikbaarheid van so 'n funksie, dink ek, hoef jy nie te bewys nie - dit sal baie gerieflik wees om rommel insetdata met die hulp daarvan uit te vee, reg?
In vergelyking met die vorige, nie-rekursiewe voorbeelde, wag daar egter twee probleme op ons.
- Ons sal 'n naam vir ons funksie moet uitdink voordat ons sy kode begin skryf, want daarin sal hierdie naam reeds gebruik word om die funksie self te noem.
- Om so 'n rekursiewe funksie in 'n sel in te voer en dit te ontfout deur argumente tussen hakies na LAMBDA te spesifiseer (soos ons vroeër gedoen het) sal nie werk nie. Jy sal dadelik 'n funksie van nuuts af moet skep Naam Bestuurder (Naambestuurder).
Kom ons noem ons funksie, sê maar, SKOON en ons wil graag hê dat dit twee argumente moet hê – die teks wat skoongemaak moet word en die lys van uitgeslote karakters as 'n teksstring:
Kom ons skep, soos ons vroeër gedoen het, op die blad formule в Naam bestuurder benoemde reeks, noem dit CLEAR en voer in die veld in Range volgende konstruksie:
=LAMBDA(t;d;IF(d=””;t;KLAAR(PLAASVERVANGER(t;LINKS(d);””);MIDDEL(d;2;255))))
Hier is die veranderlike t die oorspronklike teks wat uitgevee moet word, en d is die lys karakters wat uitgevee moet word.
Dit werk alles so:
Iterasie 1
Die fragment PLAASVERVANGER(t;LINKS(d);””), soos jy dalk kan raai, vervang die eerste karakter van die linkerkarakter uit die stel d wat in die bronteks t geskrap moet word met 'n leë teksstring, maw verwyder die " A”. As 'n intermediêre resultaat kry ons:
Vsh zkz n 125 roebels.
Iterasie 2
Dan roep die funksie homself en as invoer (die eerste argument) ontvang dit wat oorbly na skoonmaak in die vorige stap, en die tweede argument is die string uitgeslote karakters wat nie van die eerste af nie, maar vanaf die tweede karakter begin, dws “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” sonder die aanvanklike “A” – dit word deur die MID-funksie gedoen. Soos voorheen, neem die funksie die eerste karakter van die linkerkant van die oorblywendes (B) en vervang dit in die teks wat daaraan gegee is (Zkz n 125 roebels) met 'n leë string - ons kry as 'n intermediêre resultaat:
125 ru.
Iterasie 3
Die funksie roep homself weer, en ontvang as die eerste argument wat oor is van die teks wat by die vorige iterasie uitgevee moet word (Bsh zkz n 125 ru.), En as die tweede argument word die stel uitgeslote karakters afgekap deur nog een karakter om die linkerkant, dws “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” sonder die aanvanklike “B”. Dan neem dit weer die eerste karakter van links (B) uit hierdie stel en verwyder dit uit die teks – ons kry:
sh zkz n 125 ru.
En so aan – ek hoop jy kry die idee. Met elke iterasie sal die lys karakters wat verwyder moet word aan die linkerkant afgekap word, en ons sal die volgende karakter uit die stel soek en vervang met 'n leemte.
Wanneer al die karakters opraak, sal ons die lus moet verlaat - hierdie rol word net deur die funksie uitgevoer IF (AS), waarin ons ontwerp toegedraai is. As daar geen karakters oor is om uit te vee nie (d=””), moet die funksie homself nie meer noem nie, maar moet bloot die teks wat uitgevee moet word (veranderlike t) in sy finale vorm terugstuur.
Rekursiewe iterasie van selle
Net so kan jy 'n rekursiewe opsomming van selle in 'n gegewe reeks implementeer. Gestel ons wil 'n lambda-funksie met die naam skep VERVANGINGSLYS vir groothandelvervanging van fragmente in die bronteks volgens 'n gegewe verwysingslys. Die resultaat moet so lyk:
Dié. by ons funksie VERVANGINGSLYS daar sal drie argumente wees:
- sel met teks om te verwerk (bronadres)
- die eerste sel van 'n kolom met waardes om vanaf die opsoek te soek
- die eerste sel van die kolom met vervangingswaardes van die opsoek
Die funksie moet van bo na onder in die gids gaan en opeenvolgend alle opsies van die linkerkolom vervang Om te vind na die ooreenstemmende opsies uit die regterkolom plaasvervanger. Jy kan dit implementeer met die volgende rekursiewe lambda-funksie:
Skuif af by elke iterasie word geïmplementeer deur 'n standaard Excel-funksie BESKIKKING (OFFSET), wat in hierdie geval drie argumente het – die oorspronklike reeks, ryverskuiwing (1) en kolomverskuiwing (0).
Wel, sodra ons die einde van die gids bereik (n = “”), moet ons die rekursie beëindig – ons hou op om onsself te noem en vertoon wat opgehoop het na al die vervangings in die bronteksveranderlike t.
Dis al. Geen moeilike makro's of Power Query-navrae nie - die hele taak word deur een funksie opgelos.
- Hoe om Excel se nuwe dinamiese skikking funksies te gebruik: FILTER, SORT, UNIC
- Vervang en skoonmaak van teks met die SUBSTITUT-funksie
- Skep makro's en gebruikergedefinieerde funksies (UDF's) in VBA