Formler i Excel er allerede et programmeringsspråk. Når du bygger en modell i Excel, skriver du i hovedsak et program for å beregne et sett med utganger fra et sett med innganger. Calc-teamet Redmond har jobbet med et par forbedringer av Excel-formelspråket for å gjøre Excel litt mer som et programmeringsspråk. Den første av disse, LET-funksjonen er nå ute i beta. Alle som velger Insider Fast-kanalen til Office 365, bør ha tilgang til LET.
Noen ganger bygger du en formel som må referere til den samme delberegningen om og om igjen. LET-funksjonen lar deg definere en variabel og beregningen for den variabelen. Beregningen din kan ha opptil 126 variabler. Hver variabel kan bruke beregningene i de foregående variablene på nytt. Det siste argumentet i LET-funksjonen er en formel som returnerer en verdi (eller en matrise) til cellen. Den endelige formelen vil referere til variabler som er definert tidligere i LET-funksjonen.
Dette er lettest å se om jeg viser deg et eksempel. Jeg fant tilfeldigvis en formel som ble lagt ut på Meldingstavlen i 2010. Denne formelen, fra medlem Special-K99, er utformet for å finne det nest siste ordet i en setning.
Hvis jeg skulle bygge den opprinnelige formelen trinnvis, ville jeg bygge den trinnvis.
-
Trinn 1: I B4, ta TRIM av den originale setningen for å kvitte deg med gjentatte mellomrom.
TRIM-funksjon for å bli kvitt gjentatte mellomrom. -
Trinn 2: Finn ut hvor mange ord det er i B4 ved å sammenligne LEN av den trimmede teksten med lengden på den trimmede teksten etter å ha fjernet mellomrom med SUBSTITUTE. I en setning på fire ord er det tre mellomrom. I det nåværende problemet vil du finne det andre ordet, derav minus ett på slutten av denne formelen.
LENN og SUBSTITUTE fungerer for å telle ord -
Trinn 3: Legg til en karat (^) før ønsket ord. Dette bruker igjen SUBSTITUTE, men bruker det tredje argumentet i SUBSTITUTE for å finne det andre rommet. Det vil ikke alltid være 2. plass. Du må bruke resultatet fra trinn 2 som det tredje argumentet i trinn 3.
Bruke karat i SUBSTITUTE -
Trinn 4: Isoler alle ordene etter karat ved hjelp av MID og FIND.
Isoler alle ordene etter karat ved hjelp av MID og FIND -
Trinn 5: Isoler det nest siste ordet ved hjelp av MID og FIND igjen.
Isoler det nest siste ordet ved hjelp av MID og FIND
Når de er delt inn i små beregninger som vist ovenfor, kan mange følge logikken i beregningen. Jeg bygger ofte formler ved hjelp av metoden vist ovenfor.
Men jeg vil ikke ta opp fem kolonner for en formel, så jeg begynner å konsolidere de fem formlene i en enkelt formel. Formelen i F4 bruker E4 to ganger. Kopier alt i formellinjen for E4 etter likhetstegnet. Bruk Lim inn for å erstatte E4 begge steder. Fortsett å erstatte cellereferanser med formlene til det eneste det refereres til av den endelige formelen er celle A4. På dette punktet har du en sinnsykt lang formel:
Hvorfor er dette så forvirrende? Hvordan ble fem formler med en gjennomsnittlig lengde på 24 tegn til en formel på 370 tegn? Det er fordi den enkle formelen i B4 er referert totalt 12 ganger i den endelige formelen. Hvis du ikke har lagret = TRIM (A4) i celle B4, ender du opp med å skrive TRIM (A4) tolv ganger i den endelige formelen.
Her er hvor mange ganger hver av underformlene brukes i den endelige formelen.
LET til redning
LET-funksjonen lar deg definere variabler en gang i formelen og bruke de variablene på nytt senere i formelen. I figuren nedenfor er fire variabler definert i variabeldefinisjonene før en endelig beregning returnerer det siste ordet.
Det ser ut til at den beste fremgangsmåten her er å bruke Alt + Enter etter hver variabeldefinisjon i formelen. Mens formlene dine kan være A, B, C og D, skader det ikke å bruke meningsfulle variabelnavn, akkurat som du ville gjort i ethvert programmeringsspråk.
Legg merke til i figuren over at etter å ha definert TRIMTEXT som = TRIM (A4), blir TRIMTEXT-variabelen gjenbrukt i definisjonen for WhichSpace og CaratText.
Se på video
Du kan se trinnene for å kombinere delformlene i megaformelen og til LET-funksjonen her:
I andre tester beregner LET-formelen omtrent 65% raskere enn den lignende megaformelen.
