Serier: Lises funksjon med LET() og LAMDA()
Løsningen på en serie som Excel ikke skjønner bæret av.
Serier er fantastisk. Skriver du Januar, klyper tak i nedre høyre hjørne og drar, kommer de neste månedene som perler på en snor. Men det fungerer bare når Excel tolker serien riktig. Lises serie begynner slik:
01.09.2022 - 00:00-01:00
01.09.2022 - 01:00-02:00
01.09.2022 - 02:00-03:00Altså Dato – Fra time-Til Time. Dette er et ukjent format både for meg og Excel, og serier er utelukket. Utfordringen er at når Lise skal skrive inn sine data, må hun manuelt skrive inn 720 eller 744 rader for å dekke alle timene i måneden = senebetennelse.
I denne artikkelen løser vi oppgaven med formler og drar det litt videre med LET() og LAMBDA(). Jeg ville normalt snekret en makro, men siden Excelboken skal brukes i Excel Online (som ikke kan kjøre makroer), blir det formler.
Tanken er at Lise skriver den første raden, og så skal formelen løse resten når den dras nedover. Da må vi plukke fra hverandre tekststrengen «01.09.2022 – 00:00-01:00» i celle B2, plukke ut dato og klokkeslett og lage en ny streng.
Celle D3, Dato, fra de 10 første tegnene i strengen
=DATO(ÅR(DELTEKST(B2;1;10));MÅNED(DELTEKST(B2;1;10));DAG(DELTEKST(B2;1;10)))Celle E3, Time, tallverdien fra posisjon 14 og 15 i strengen
=VERDI(DELTEKST(B2;14;2))OK, da har vi utgangspunktet for å kunne beregne resultatet av formelen.
F3, Ny Dato
(Se bort i fra formelen som benyttes i videoen. Sukk…)
F3, datoen, vil tippe over til neste dag når vi legger til 1 (1 = 1 dag når vi jobber i Excel). I og med at klokkeslettet 24 ikke finnes, trenger formelen et lite dytt når Fra-tiden er 23. Da er Til-tiden på raden over 0, og vi legger til 1. (E3=23) kan være enten USANN, som gir verdien 0, eller SANN, som gir verdien 1, og det er kun i dette ene tilfellet da E3=23 at vi skal øke datoverdien.
=TEKST(D3+(E3=23));"dd.mm.åååå")G3, Fra Time. » – » legger til bindestreken mellom dato og klokkelsett. Hvis Fra Time på raden over + 1 er større enn 23 (HVIS(E3+1>23;0;E3+1), begynner vi på null igjen. Hvis ikke, øker vi med en (HVIS(E3+1>23;0;E3+1) . TEKST sørger for at vi får null foran tal som er lavere enn 10, f.eks 1 blir 01. Til slutt legger vi på minuttene med &»:00″ side de alltid er null.
=" - "&TEKST(HVIS(E3+1>23;0;E3+1);"00")&":00"H3, Til Time. «-» Legger til bindestreken mellom tidspunktene Fra Time og Til Time. HVIS.SETT gir resultatet av tre tester: Hvis Fra Tid på raden over er mindre enn 22 blir verdien Fra Tid+2. Hvis Fra Time=22, settes verdien til 0, og hvis Fra Time=23, settes Til Time til 1. Til slutt legger vi på minuttene «:00»
="-"&TEKST(HVIS.SETT(E3<22;E3+2;E3=22;0;E3=23;1);"00")&":00"I3, Resultatet. Her slår vi sammen den nye Datoen, ny Fra Time og ny Til Time.
=F3 & G3 & H3Resultatet av denne sammenslåingen blir
01.09.2022 - 01:00-02:00
… som har samme format som B2, og vi er klare for neste rad 🙂
Alt i En formel
Det er ikke så elegant å bruke 5 hjelpe-kolonner for å komme frem til resultatet. I dette tilfellet refererer vi gjentatte ganger til Dato og Fra Time, og formelen vil bli lang. Hvis vi skal skippe referansen til kolonne E i formelen for Til Time, må den endres fra
="-"&TEKST(HVIS.SETT(E3<22;E3+2;E3=22;0;E3=23;1);"00")&":00"til
="-"&TEKST(HVIS.SETT(VERDI(DELTEKST(B2;14;2))<22;VERDI(DELTEKST(B2;14;2))+2;VERDI(DELTEKST(B2;14;2))=22;0;VERDI(DELTEKST(B2;14;2))=23;1);"00")&":00"LET()
Hvor alle referanser til E3 er byttet ut med VERDI(DELTEKST(B2;14;2)). Her er det LET() kommer til sin rett. Ved å definere Timen som en variabel, Hr i dette eksemplet, kan formelen skrives slik:
=LET(Hr;VERDI(DELTEKST(B2;14;2));"-"&TEKST(HVIS.SETT(Hr<22;Hr+2;Hr=22;0;Hr=23;1);"00")&":00")=LET(Hr;VERDI(DELTEKST(B2;14;2)) Tildeler variabelen Hr resultatet av formelen VERDI(DELTEKST(B2;14;2)). I stedet for å oppbevare resultatet i E3, oppbevarer vi det nå i Hr. Forskjellen er at vi kan referere til E3 fra hvor som helst, mens Hr kun har verdi i denne ene formelen.
Hvis vi drar til og samler alle formlene i en eneste formel:
=LET(Dt;DATO(ÅR(DELTEKST(B2;1;10));MÅNED(DELTEKST(B2;1;10));DAG(DELTEKST(B2;1;10)));Hr;VERDI(DELTEKST(B2;14;2));TEKST(Dt+HVIS(Hr<23;Hr/24;(Hr+1)/24);"dd.mm.åååå")&" - "&TEKST(HVIS(Hr+1>23;0;Hr+1);"00")&":00"&"-"&TEKST(HVIS.SETT(Hr<22;Hr+2;Hr=22;0;Hr=23;1);"00")&":00")Her kan du se at det er to variabler, Dt (Dato) og Hr (Time)
LAMBDA()
Og så – hvis vi vil dra det enda lenger og definere formelen vår som en funksjon, kan vi benytte LAMBDA
=LAMBDA(ForrigeTime;LET(ForrigeTime1;ForrigeTime;Dt;DATO(ÅR(DELTEKST(ForrigeTime1;1;10));MÅNED(DELTEKST(ForrigeTime1;1;10));DAG(DELTEKST(ForrigeTime1;1;10)));Hr;VERDI(DELTEKST(ForrigeTime1;14;2));TEKST(Dt+HVIS(Hr<23;Hr/24;(Hr+1)/24);"dd.mm.åååå")&" - "&TEKST(HVIS(Hr+1>23;0;Hr+1);"00")&":00"&"-"&TEKST(HVIS.SETT(Hr<22;Hr+2;Hr=22;0;Hr=23;1);"00")&":00"))(B2)For at en funksjon skal bli generell, må vi kunne sende med et parameter, altså gjøre den uavhengig av hvilken celle verdien står i. I formelen over, er Forrigetime parameteret, og til slutt står det (B2) som er referansen vi tester med. I LET-delen tildeler vi Forrigetime til ForrigeTime1, og så tildeler vi verdier til Dt og Hr fra ForrigeTime1 i stedet fra celle B2.
Fra celle L3 i eksempelfilen kopierer jeg alt unntatt (B2).
Så velger jeg Formler->Navne-behandling og klikker Ny
Så skriver jeg inn navn på funksjonen i feltet Navn: og limer inn formelen i feltet Refererer til: og klikker OK og Lukk
Nå har du en funksjon du kan benytte hvor som helst i denne Excelboken. Hvis du gav opp før du så slutten på videoen, kan du se sekvensen der funksjonen defineres her:
Last ned Excelboken med LET() og Lambda() uten makro her: Lises funksjon med Let og Lambda (2 downloads )
Last ned Excelboken med LET(), Lambda() pluss makro her: Lises funksjon med Let, Lambda og makro (4 downloads )
