Innehåll
För närvarande har Microsoft Excel nästan femhundra kalkylbladsfunktioner tillgängliga via funktionsguidens fönster – knappen fx i formelfältet. Detta är en mycket anständig uppsättning, men ändå stöter nästan varje användare förr eller senare på en situation där den här listan inte innehåller den funktion han behöver – helt enkelt för att den inte är i Excel.
Hittills har det enda sättet att lösa detta problem varit makron, dvs att skriva en egen användardefinierad funktion (UDF = User Defined Function) i Visual Basic, vilket kräver lämpliga programmeringskunskaper och ibland inte är lätt alls. Men med de senaste Office 365-uppdateringarna har situationen förändrats till det bättre - en speciell "wrapper"-funktion har lagts till i Excel LAMBDA. Med dess hjälp är uppgiften att skapa dina egna funktioner nu löst enkelt och vackert.
Låt oss titta på principen för dess användning i följande exempel.
Som du med största sannolikhet vet har Excel flera datumanalysfunktioner som låter dig bestämma numret på dagen, månaden, veckan och året för ett givet datum. Men av någon anledning finns det ingen funktion som bestämmer kvartalets nummer, vilket också ofta behövs, eller hur? Låt oss åtgärda denna brist och skapa med LAMBDA egen ny funktion för att lösa detta problem.
Steg 1. Skriv formeln
Låt oss börja med att vi manuellt på vanligt sätt kommer att skriva en formel i en arkcell som beräknar vad vi behöver. När det gäller kvartalsnumret kan detta till exempel göras så här:
Steg 2. Slå in i LAMBDA och testa
Nu är det dags att använda den nya LAMBDA-funktionen och slå in vår formel i den. Funktionens syntax är följande:
=LAMBDA(Variabel1; Variabel2; ... VariabelN ; Uttryck)
där namnen på en eller flera variabler listas först, och det sista argumentet alltid är en formel eller ett beräknat uttryck som använder dem. Variabelnamn ska inte se ut som celladresser och får inte innehålla punkter.
I vårt fall kommer det bara att finnas en variabel – det datum för vilket vi beräknar kvartalstalet. Låt oss kalla variabeln för det, säg d. Sedan sveper vi in vår formel i en funktion LAMBDA och genom att ersätta adressen för den ursprungliga cellen A2 med ett fiktivt variabelnamn får vi:
Observera att efter en sådan omvandling började vår formel (faktiskt korrekt!) ge ett fel, för nu överförs inte det ursprungliga datumet från cell A2 till det. För testning och förtroende kan du skicka argument till den genom att lägga till dem efter funktionen LAMBDA inom parantes:
Steg 3. Skapa ett namn
Nu till den enkla och roliga delen. Vi öppnar Namnhanterare fliken formeln (Formler – Namnhanterare) och skapa ett nytt namn med knappen Skapa (Skapa). Kom på och ange ett namn för vår framtida funktion (t.ex. Nomkvartala), och i fält Länk (Referens) kopiera försiktigt från formelfältet och klistra in vår funktion LAMBDA, endast utan det sista argumentet (A2):
Allt. Efter att ha klickat på OK den skapade funktionen kan användas i vilken cell som helst på vilket ark som helst i denna arbetsbok:
Använd i andra böcker
LAMBDA och dynamiska arrayer
Anpassade funktioner skapade med en funktion LAMBDA framgångsrikt stödja arbete med nya dynamiska arrayer och deras funktioner (FILTER, UNIK, BETYG) lades till i Microsoft Excel 2020.
Låt oss säga att vi vill skapa en ny användardefinierad funktion som skulle jämföra två listor och returnera skillnaden mellan dem – de element från den första listan som inte finns i den andra. Livets arbete, eller hur? Tidigare använde de antingen funktioner a la för detta VPR (VISNING), eller Pivottabeller, eller Power Query-frågor. Nu kan du göra med en formel:
I den engelska versionen blir det:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Här är funktionen COUNTIF räknar antalet förekomster av varje element i den första listan i den andra, och sedan funktionen FILTER väljer bara de av dem som inte hade dessa händelser. Genom att linda in denna struktur LAMBDA och skapa ett namngivet intervall baserat på det med ett namn, till exempel, SÖK DISTRIBUTION – vi kommer att få en bekväm funktion som returnerar resultatet av att jämföra två listor i form av en dynamisk array:
Om källdata inte är vanliga, utan "smarta" tabeller, kommer vår funktion också att klara sig utan problem:
Ett annat exempel är att dynamiskt dela text genom att konvertera den till XML och sedan analysera den cell för cell med funktionen FILTER.XML som vi nyligen analyserade. För att inte reproducera denna komplexa formel manuellt varje gång, blir det lättare att linda in den i LAMBDA och skapa ett dynamiskt omfång baserat på det, dvs en ny kompakt och bekväm funktion, som till exempel namnger den RAZDTEXT:
Det första argumentet för den här funktionen kommer att vara cellen med källtexten, och det andra - separatortecknet, och det kommer att returnera resultatet i form av en horisontell dynamisk array. Funktionskoden blir som följer:
=LAMBDA(t;d; TRANSPOSERA(FILTER.XML(“
Listan med exempel är oändlig – i alla situationer där du ofta måste ange samma långa och krångliga formel, kommer LAMBDA-funktionen att göra livet märkbart enklare.
Rekursiv uppräkning av tecken
Alla tidigare exempel har bara visat en, den mest uppenbara, sidan av LAMBDA-funktionen - dess användning som en "omslag" för att slå in långa formler i den och förenkla deras inmatning. Faktum är att LAMBDA har en annan, mycket djupare, sida som gör det till nästan ett fullfjädrat programmeringsspråk.
Faktum är att en fundamentalt viktig egenskap hos LAMBDA-funktioner är förmågan att implementera dem i rekursion – logik för beräkningar, när funktionen anropar sig själv i beräkningsprocessen. Av vana kan det låta läskigt, men i programmering är rekursion en vanlig sak. Även i makron i Visual Basic kan du implementera det, och nu har det som du kan se kommit till Excel. Låt oss försöka förstå denna teknik med ett praktiskt exempel.
Anta att vi vill skapa en användardefinierad funktion som tar bort alla givna tecken från källtexten. Användbarheten av en sådan funktion tror jag att du inte behöver bevisa – det skulle vara väldigt bekvämt att rensa nedskräpade indata med dess hjälp, eller hur?
Men jämfört med de tidigare, icke-rekursiva exemplen, väntar oss två svårigheter.
- Vi måste komma på ett namn för vår funktion innan vi börjar skriva dess kod, för i den kommer detta namn redan att användas för att anropa själva funktionen.
- Att skriva in en sådan rekursiv funktion i en cell och felsöka den genom att ange argument inom parentes efter LAMBDA (som vi gjorde tidigare) kommer inte att fungera. Du måste skapa en funktion direkt "från början". Namnhanterare (namnhanterare).
Låt oss kalla vår funktion, säg, CLEAN och vi vill att den ska ha två argument – texten som ska rengöras och listan över uteslutna tecken som en textsträng:
Låt oss skapa, som vi gjorde tidigare, på fliken formeln в Namnansvarig namngett område, namnge det CLEAR och ange i fältet Mätområde följande konstruktion:
=LAMBDA(t;d;OM(d=””;t;CLEAR(SUBSTITUTER(t;VÄNSTER(d);””);MIDDEN(d;2;255))))
Här är variabeln t den ursprungliga texten som ska raderas, och d är listan över tecken som ska raderas.
Det hela fungerar så här:
iteration 1
Fragmentet SUBSTITUTE(t;LEFT(d);””), som du kanske kan gissa, ersätter det första tecknet från det vänstra tecknet från uppsättningen d som ska raderas i källtexten t med en tom textsträng, dvs tar bort " A”. Som ett mellanresultat får vi:
Vsh zkz n 125 rubel.
iteration 2
Då anropar funktionen sig själv och som input (det första argumentet) tar emot det som är kvar efter rengöring i föregående steg, och det andra argumentet är strängen av uteslutna tecken som börjar inte från det första utan från det andra tecknet, dvs ”BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," utan det initiala "A" - detta görs av MID-funktionen. Som tidigare tar funktionen det första tecknet från vänster om de återstående (B) och ersätter det i texten som ges till den (Zkz n 125 rubel) med en tom sträng - vi får som ett mellanresultat:
125 ru.
iteration 3
Funktionen anropar sig själv igen och tar emot som det första argumentet det som är kvar av texten som ska rensas vid föregående iteration (Bsh zkz n 125 ru.), Och som det andra argumentet trunkeras uppsättningen av uteslutna tecken med ytterligare ett tecken till till vänster, dvs "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.," utan initialt "B". Sedan tar den igen det första tecknet från vänster (B) från denna uppsättning och tar bort det från texten – vi får:
sh zkz n 125 ru.
Och så vidare – jag hoppas att du förstår idén. Med varje iteration kommer listan över tecken som ska tas bort att trunkeras till vänster, och vi kommer att söka efter och ersätta nästa tecken från uppsättningen med ett tomrum.
När alla karaktärer tar slut måste vi lämna loopen - den här rollen utförs bara av funktionen IF (OM), där vår design är insvept. Om det inte finns några tecken kvar att radera (d=””) ska funktionen inte längre anropa sig själv, utan ska helt enkelt returnera texten som ska raderas (variabel t) i sin slutliga form.
Rekursiv iteration av celler
På samma sätt kan du implementera en rekursiv uppräkning av celler i ett givet intervall. Anta att vi vill skapa en lambdafunktion med namnet ERSÄTTNINGSLISTA för partihandelsersättning av fragment i källtexten enligt en given referenslista. Resultatet ska se ut så här:
De där. på vår funktion ERSÄTTNINGSLISTA det kommer att finnas tre argument:
- cell med text att bearbeta (källadress)
- den första cellen i en kolumn med värden att söka från uppslagningen
- den första cellen i kolumnen med ersättningsvärden från uppslagningen
Funktionen ska gå uppifrån och ner i katalogen och ersätta alla alternativ i den vänstra kolumnen i följd Att hitta till motsvarande alternativ från den högra kolumnen Ersättning. Du kan implementera detta med följande rekursiva lambdafunktion:
Skift ned vid varje iteration implementeras av en standard excel-funktion FÖRFOGANDE (OFFSET), som i det här fallet har tre argument – det ursprungliga intervallet, radförskjutning (1) och kolumnförskjutning (0).
Tja, så fort vi når slutet av katalogen (n = “”), måste vi avsluta rekursionen – vi slutar kalla oss själva och visar vad som har ackumulerats efter alla ersättningar i källtextvariabeln t.
Det är allt. Inga knepiga makron eller Power Query-frågor – hela uppgiften löses av en funktion.
- Så här använder du Excels nya dynamiska array-funktioner: FILTER, SORT, UNIC
- Ersätta och rensa text med ERSÄTTNINGSfunktionen
- Skapa makron och användardefinierade funktioner (UDF) i VBA