XOPPSLAG() Litt avansert bruk, og hva i all verden er EgR()??
XOPPSLAG() erstatter funksjonen FINN.RAD(), og er vesentlig råere i bruk. Her skal vi se hvordan vi kan bruke matrisefunksjonalitet i XOPPSLAG() – altså få formelen til å spre resultatet over flere rader, og også litt på mitt lille hjertebarn EgR() som refererer til siste skrevne rad i en kolonne – og fyller et hull i Excel for meg.
XOPPSLAG() finner du i Microsoft365, ikke i Office 16/19
Les også: Aldri mer FINN.RAD(). Nå er det XOPPSLAG() som gjelder
Les også: XOPPSLAG() med flere søkekriterier – dette er utklassing!
Lenke til eksempelfilen finner du nederst i artikkelen
(Artikkelen fortsetter under videoruten. ) |
Formelen i B2
Formelen i B2 er den mest alminnelige bruken av XOPPSLAG:
=XOPPSLAG(A2;Årslønn!$C:$C;Årslønn!$D:$D;"-";0)
Den søker etter verdien i A2 (som i dette tilfellet er 1) i kolonne C i fanen Årslønn og returner verdien i kolonne D hvis den finner verdien. Finnes ikke verdien, returneres «-«.
Formelen i C2
Denne formelen er spenstigere ved at den angir hvilke rader nedover formelresultatet skal spre seg over.
=XOPPSLAG(A2:A11;Årslønn!$C:$C;Årslønn!$A:$A;"-";0)
Her ser du at A2 i B2-formelen er utvidet til A2:A11. Dette gjør at formelen automatisk settes inn på radene C2 til C11. Bare pass på at cellene under C2 er tomme, ellers vil du få feilmeldingen #Overflyt:
Formelen i D2
Her erstatter vi A2:A11 med funksjonen INDIREKTE(«A2:A» & H2).
INDIREKTE() leser innholdet fra en tekst, og vil i dette returnere verdien i radene A2:A100. Fordelen med dette er at hvis alle formlene i kolonne B-F er skrevet på denne måten, kan vi utvide området formelen sprer seg over ved å endre antallet i H2.
=XOPPSLAG(INDIREKTE("A2:A" & H2);Adresse!$A:$A;Adresse!$B:$B;"Ikke funnet";0)
Formelen i E2 (og forsåvidt F2)
Det ultimate ville jo være om formelen selv kunne finne ut hva som er siste rad i kolonnen hvor søkeverdien står, i dette tilfellet A-kolonnen. Excel har ikke noen slik funksjon, så jeg har skrevet en som heter EgR() – ExcelGuruRader – som gjør akkurat dette.
Her kan jeg sette 1000 eller 100000 i H2, men formelresultatet vil ikke spre seg lenger ned enn til siste rad med innhold i A-kolonnen.
=XOPPSLAG(EgR(A2;H2);Adresse!$A:$A;Adresse!$C:$C;"Ikke funnet";0)
Hvorfor ha med H2 med antall rader som skal undersøkes? Det har med hastighet å gjøre. Hvis jeg skulle sjekke alle radene i kolonnen, er det 1 048 576 rader. Hvis jeg bruker funksjonen i fem kolonner, blir det 5 242 880 rader å sjekke, og det tar tid. Hvis det holder å sjekke 500 rader, er det gjort på et blunk.
EgR()??
Dette er en funksjon du enkelt kan ta med deg i dine egne Excelbøker. Kopier Celle J2 fra eksempelboka og lim inn i din egen Excelbok. Da kopieres EgR() automatisk inn, og er klar til bruk.
EgR()!
EgR() er en LAMBDA-funksjon, og du finner den under Formler->Navnbehandling
EgR() Formelen
For de mest interesserte:
= LAMBDA(
TableStart;
CheckRows;
LET(
FirstColumn;
TEXTBEFORE(TEXTAFTER(CELL("address"; TableStart); "$"); "$");
FirstRow;
CELL("row";TableStart);
CheckRows1;
CheckRows;
Rng;
FirstColumn & "1:" & FirstColumn & CheckRows1;
INDIRECT( FirstColumn
& FirstRow
& ":"
& FirstColumn
& MAX(ROW(INDIRECT(Rng)) * NOT(ISBLANK(INDIRECT(Rng)))))))(A2; H2)
Last ned Excelboka her: XOPPSLAG() Litt avansert bruk, og hva i all verden er EgR()?? (29 downloads )