Inhoud
Op dit moment heeft Microsoft Excel bijna vijfhonderd werkbladfuncties beschikbaar via het venster Functiewizard – knop fx in de formulebalk. Dit is een heel behoorlijke set, maar desalniettemin komt bijna elke gebruiker vroeg of laat in een situatie terecht waarin deze lijst niet de functie bevat die hij nodig heeft - simpelweg omdat het niet in Excel staat.
Tot nu toe was de enige manier om dit probleem op te lossen macro's, dwz het schrijven van uw eigen door de gebruiker gedefinieerde functie (UDF = User Defined Function) in Visual Basic, wat de juiste programmeervaardigheden vereist en soms helemaal niet eenvoudig is. Met de nieuwste Office 365-updates is de situatie echter ten goede veranderd - er is een speciale "wrapper" -functie toegevoegd aan Excel LAMBDA. Met zijn hulp is de taak om uw eigen functies te maken nu eenvoudig en mooi opgelost.
Laten we eens kijken naar het principe van het gebruik ervan in het volgende voorbeeld.
Zoals u waarschijnlijk weet, heeft Excel verschillende datumontledingsfuncties waarmee u het nummer van de dag, de maand, de week en het jaar voor een bepaalde datum kunt bepalen. Maar om de een of andere reden is er geen functie die het nummer van het kwartaal bepaalt, wat ook vaak nodig is, toch? Laten we deze tekortkoming verhelpen en creëren met LAMBDA eigen nieuwe functie om dit probleem op te lossen.
Stap 1. Schrijf de formule
Laten we beginnen met het feit dat we handmatig op de gebruikelijke manier een formule in een bladcel zullen schrijven die berekent wat we nodig hebben. In het geval van het kwartiernummer kan dit bijvoorbeeld als volgt:
Stap 2. Afsluiten in LAMBDA en testen
Nu is het tijd om de nieuwe LAMBDA-functie toe te passen en onze formule erin in te pakken. De functiesyntaxis is als volgt:
=LAMBDA(Variabele1; Variabele2; ... VariabeleN ; Uitdrukking)
waarbij de namen van een of meer variabelen als eerste worden vermeld en het laatste argument altijd een formule of een berekende uitdrukking is die ze gebruikt. Variabelenamen mogen er niet uitzien als celadressen en mogen geen punten bevatten.
In ons geval is er maar één variabele: de datum waarvoor we het kwartaalnummer berekenen. Laten we de variabele ervoor noemen, laten we zeggen, d. Dan verpakken we onze formule in een functie LAMBDA en als we het adres van de oorspronkelijke cel A2 vervangen door een fictieve variabelenaam, krijgen we:
Houd er rekening mee dat na een dergelijke transformatie onze formule (in feite correct!) een fout begon te produceren, omdat nu de oorspronkelijke datum uit cel A2 er niet naar wordt overgebracht. Voor testen en vertrouwen kun je er argumenten aan doorgeven door ze achter de functie toe te voegen LAMBDA tussen haakjes:
Stap 3. Maak een naam
Nu voor het gemakkelijke en leuke gedeelte. We openen Name Manager tab formule (Formules — Naam Manager) en maak een nieuwe naam aan met de knop creëren (Creëren). Bedenk en voer een naam in voor onze toekomstige functie (bijvoorbeeld Nomkvartala), en in het veld Link (Referentie) kopieer voorzichtig uit de formulebalk en plak onze functie LAMBDA, alleen zonder het laatste argument (A2):
Alles. Na het klikken op OK de gemaakte functie kan in elke cel op elk blad van deze werkmap worden gebruikt:
Gebruik in andere boeken
LAMBDA en dynamische arrays
Aangepaste functies gemaakt met een functie LAMBDA met succes het werk met nieuwe dynamische arrays en hun functies ondersteunen (FILTER, UNIEK, GRADE) toegevoegd aan Microsoft Excel in 2020.
Laten we zeggen dat we een nieuwe door de gebruiker gedefinieerde functie willen maken die twee lijsten zou vergelijken en het verschil daartussen zou teruggeven - die elementen uit de eerste lijst die niet in de tweede staan. Levenswerk, nietwaar? Voorheen gebruikten ze hiervoor beide functies a la VPR (VERT.ZOEKEN), draaitabellen of Power Query-query's. Nu kunt u doen met één formule:
In de Engelse versie wordt het:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Hier de functie COUNTIF telt het aantal keren dat elk element van de eerste lijst in de tweede voorkomt, en vervolgens de functie FILTER selecteert alleen die van hen die deze gebeurtenissen niet hadden. Door deze structuur in te wikkelen LAMBDA en op basis daarvan een benoemd bereik maken met een naam, bijvoorbeeld ZOEK DISTRIBUTIE - we krijgen een handige functie die het resultaat retourneert van het vergelijken van twee lijsten in de vorm van een dynamische array:
Als de brongegevens geen gewone, maar "slimme" tabellen zijn, kan onze functie het ook zonder problemen aan:
Een ander voorbeeld is het dynamisch splitsen van tekst door deze naar XML te converteren en vervolgens cel voor cel te ontleden met behulp van de functie FILTER.XML die we onlangs hebben geparseerd. Om deze complexe formule niet elke keer handmatig te reproduceren, zal het gemakkelijker zijn om het in LAMBDA te wikkelen en er een dynamisch bereik op gebaseerd te maken, dat wil zeggen een nieuwe compacte en handige functie, door het bijvoorbeeld RAZDTEXT te noemen:
Het eerste argument van deze functie is de cel met de brontekst en het tweede - het scheidingsteken, en het resultaat wordt geretourneerd in de vorm van een horizontale dynamische array. De functiecode is als volgt:
=LAMBDA(t;d; TRANSPONEREN(FILTER.XML(“
De lijst met voorbeelden is eindeloos - in elke situatie waarin u vaak dezelfde lange en omslachtige formule moet invoeren, zal de LAMBDA-functie het leven merkbaar gemakkelijker maken.
Recursieve opsomming van tekens
Alle voorgaande voorbeelden hebben slechts één, de meest voor de hand liggende kant van de LAMBDA-functie laten zien: het gebruik ervan als een "wrapper" om lange formules erin te verpakken en hun invoer te vereenvoudigen. LAMBDA heeft zelfs een andere, veel diepere kant die het in bijna een volwaardige programmeertaal verandert.
Het feit is dat een fundamenteel belangrijk kenmerk van LAMBDA-functies de mogelijkheid is om ze te implementeren in herhaling – logica van berekeningen, wanneer tijdens het berekenen de functie zichzelf aanroept. Uit gewoonte klinkt het misschien eng, maar bij programmeren is recursie heel gewoon. Zelfs in macro's in Visual Basic kun je het implementeren, en nu, zoals je kunt zien, is het naar Excel gekomen. Laten we proberen deze techniek te begrijpen met een praktisch voorbeeld.
Stel dat we een door de gebruiker gedefinieerde functie willen maken die alle gegeven tekens uit de brontekst zou verwijderen. Het nut van zo'n functie hoef je, denk ik, niet te bewijzen - het zou erg handig zijn om met zijn hulp vervuilende invoergegevens te wissen, toch?
In vergelijking met de vorige, niet-recursieve voorbeelden wachten ons echter twee moeilijkheden.
- We zullen een naam voor onze functie moeten bedenken voordat we de code gaan schrijven, omdat deze naam al wordt gebruikt om de functie zelf aan te roepen.
- Het invoeren van zo'n recursieve functie in een cel en het debuggen door argumenten tussen haakjes op te geven na LAMBDA (zoals we eerder deden) zal niet werken. U moet onmiddellijk een functie "from scratch" maken in Name Manager (Naam Manager).
Laten we onze functie, laten we zeggen, CLEAN noemen en we willen dat deze twee argumenten heeft: de tekst die moet worden opgeschoond en de lijst met uitgesloten tekens als een tekenreeks:
Laten we maken, zoals we eerder deden, op het tabblad formule в Naam manager genoemd bereik, noem het CLEAR en voer het in het veld in RANGE volgende constructie:
=LAMBDA(t;d;IF(d=””;t;CLEAR(VERVANGING(t;LEFT(d);””);MID(d;2;255))))
Hier is de variabele t de originele tekst die moet worden gewist, en is d de lijst met tekens die moeten worden verwijderd.
Het werkt allemaal als volgt:
Iteratie 1
Het fragment SUBSTITUTE(t;LEFT(d);””) vervangt, zoals u wellicht vermoedt, het eerste teken van het linkerteken van de set d dat in de brontekst t moet worden verwijderd door een lege tekenreeks, dwz verwijdert de " EEN". Als tussenresultaat krijgen we:
Vsh zkz n 125 roebel.
Iteratie 2
Vervolgens roept de functie zichzelf aan en ontvangt als invoer (het eerste argument) wat er overblijft na het opschonen in de vorige stap, en het tweede argument is de reeks uitgesloten tekens, niet beginnend bij het eerste, maar bij het tweede teken, dwz “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," zonder de initiaal "A" - dit wordt gedaan door de MID-functie. Zoals eerder neemt de functie het eerste teken van links van de resterende (B) en vervangt het in de tekst die eraan is gegeven (Zkz n 125 roebel) door een lege string - we krijgen als tussenresultaat:
125 roebel.
Iteratie 3
De functie roept zichzelf opnieuw aan en ontvangt als eerste argument wat er over is van de tekst die moet worden gewist bij de vorige iteratie (Bsh zkz n 125 ru.), En als tweede argument wordt de set uitgesloten tekens afgekapt met nog een teken om links, dwz “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.”, zonder de initiaal “B”. Dan neemt het opnieuw het eerste teken van links (B) uit deze set en verwijdert het uit de tekst - we krijgen:
sh zkzn 125 ru.
En ga zo maar door - ik hoop dat je het idee snapt. Bij elke iteratie wordt de lijst met te verwijderen tekens aan de linkerkant afgekapt en zoeken we naar het volgende teken uit de set en vervangen we dit door een leegte.
Wanneer alle tekens op zijn, moeten we de lus verlaten - deze rol wordt alleen uitgevoerd door de functie IF (ALS), waarin ons ontwerp is verpakt. Als er geen tekens meer zijn om te verwijderen (d=””), dan zou de functie zichzelf niet langer moeten aanroepen, maar zou de te wissen tekst (variabele t) in zijn uiteindelijke vorm moeten retourneren.
Recursieve iteratie van cellen
Op dezelfde manier kunt u een recursieve opsomming van cellen in een bepaald bereik implementeren. Stel dat we een lambda-functie willen maken met de naam VERVANGINGSLIJST voor grootschalige vervanging van fragmenten in de brontekst volgens een gegeven referentielijst. Het resultaat zou er als volgt uit moeten zien:
Die. op onze functie VERVANGINGSLIJST er zullen drie argumenten zijn:
- cel met te verwerken tekst (bronadres)
- de eerste cel van een kolom met waarden om te zoeken vanuit de lookup
- de eerste cel van de kolom met vervangende waarden uit de zoekopdracht
De functie moet van boven naar beneden in de map gaan en achtereenvolgens alle opties uit de linkerkolom vervangen Vinden naar de overeenkomstige opties in de rechterkolom Plaatsvervanger. U kunt dit implementeren met de volgende recursieve lambda-functie:
Verschuiving naar beneden bij elke iteratie wordt geïmplementeerd door een standaard Excel-functie VERWIJDEREN (VERSCHUIVING), die in dit geval drie argumenten heeft: het oorspronkelijke bereik, rijverschuiving (1) en kolomverschuiving (0).
Welnu, zodra we het einde van de directory bereiken (n = ""), moeten we de recursie beëindigen - we stoppen met onszelf aan te roepen en laten zien wat er is verzameld na alle vervangingen in de brontekstvariabele t.
Dat is alles. Geen lastige macro's of Power Query-query's - de hele taak wordt opgelost door één functie.
- Hoe de nieuwe dynamische matrixfuncties van Excel te gebruiken: FILTER, SORT, UNIC
- Tekst vervangen en opschonen met de SUBSTITUTE-functie
- Macro's en door de gebruiker gedefinieerde functies (UDF's) maken in VBA