Formler i Excel är redan ett programmeringsspråk. När du bygger en modell i Excel skriver du i huvudsak ett program för att beräkna en uppsättning utdata från en uppsättning ingångar. Calc-teamet Redmond har arbetat med ett par förbättringar av Excel-formelspråket för att göra Excel lite mer som ett programmeringsspråk. Den första av dessa, LET-funktionen är nu ute i beta. Den som väljer Insider Fast-kanalen i Office 365 bör ha tillgång till LET.
Ibland bygger du en formel som måste referera till samma delberäkning om och om igen. Med LET-funktionen kan du definiera en variabel och beräkningen för den variabeln. Din beräkning kan innehålla upp till 126 variabler. Varje variabel kan återanvända beräkningarna i de föregående variablerna. Det sista argumentet i LET-funktionen är en formel som returnerar ett värde (eller en matris) till cellen. Den slutliga formeln hänvisar till variabler som definierats tidigare i LET-funktionen.
Det är lättast att se om jag visar ett exempel. Jag hittade slumpmässigt en formel som publicerades på anslagstavlan 2010. Denna formel, från medlem Special-K99, är utformad för att hitta det näst sista ordet i en fras.
Om jag skulle bygga den ursprungliga formeln steg för steg skulle jag bygga den i steg.
-
Steg 1: I B4, ta TRIM för originalfrasen för att bli av med upprepade mellanslag.
TRIM-funktion för att bli av med upprepade utrymmen. -
Steg 2: Ta reda på hur många ord som finns i B4 genom att jämföra LIM för den trimmade texten med längden på den trimmade texten efter att du har tagit bort mellanslag med SUBSTITUTE. I en fras på fyra ord finns det tre mellanslag. I det aktuella problemet vill du hitta det andra ordet, därav minus ett i slutet av denna formel.
LEN och SUBSTITUT fungerar för att räkna ord -
Steg 3: Lägg till en karat (^) före önskat ord. Detta använder igen SUBSTITUTE men använder det tredje argumentet i SUBSTITUTE för att hitta det andra mellanslaget. Det kommer inte alltid att vara det andra utrymmet. Du måste använda resultatet från steg 2 som det tredje argumentet i steg 3.
Använda karat i SUBSTITUTE -
Steg 4: Isolera alla orden efter karat med MID och FIND.
Isolera alla orden efter karat med MID och FIND -
Steg 5: Isolera det sista ordet med MID och FIND igen.
Isolera det sista ordet med MID och FIND
När de delas upp i små beräkningar som visas ovan kan många följa beräkningen. Jag bygger ofta formler med metoden som visas ovan.
Men jag vill inte ta upp fem kolumner för en formel, så jag börjar konsolidera dessa fem formler i en enda formel. Formeln i F4 använder E4 två gånger. Kopiera allt i formelfältet för E4 efter likhetstecknet. Använd Klistra in för att ersätta E4 på båda platserna. Fortsätt att ersätta cellreferenser med deras formler tills det enda som den slutliga formeln refererar till är cell A4. Vid den här tiden har du en vansinnigt lång formel:
Varför är det så förvirrande? Hur blev fem formler med en genomsnittlig längd på 24 tecken till en formel på 370 tecken? Det beror på att den enkla formeln i B4 refereras totalt 12 gånger i den slutliga formeln. Om du inte har lagrat = TRIM (A4) i cell B4, slutar du skriva TRIM (A4) tolv gånger i den slutliga formeln.
Här är hur många gånger var och en av underformlerna används i den slutliga formeln.
LET to the Rescue
Med LET-funktionen kan du definiera variabler en gång i formeln och återanvända dessa variabler senare i formeln. I figuren nedan definieras fyra variabler i variabeldefinitionerna innan en slutlig beräkning returnerar det sista ordet.
Det verkar som att den bästa metoden här är att använda Alt + Enter efter varje variabeldefinition i formeln. Medan dina formler kan vara A, B, C och D, skadar det inte att använda meningsfulla variabla namn, precis som du skulle göra i något programmeringsspråk.
Observera i figuren ovan att efter att TRIMTEXT har definierats som = TRIM (A4), används TRIMTEXT-variabeln i definitionen för WhoSpace och CaratText.
Kolla på video
Du kan se stegen för att kombinera delformlerna i megaformeln och till LET-funktionen här:
I andra tester beräknar LET-formeln cirka 65% snabbare än liknande megaformel.
