XOPPSLAG() Litt avansert bruk, og hva i all verden er EgR()??

|
AvKetil Øyesvold Melhus Publisert Redigert

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()?? (12 downloads )

Similar Posts