Tip!: Prøv at bruge de nye XLOOKUP -og XMATCH -funktioner, forbedrede versioner af de funktioner, der er beskrevet i denne artikel. Disse nye funktioner fungerer i en hvilken som helst retning og giver dem som standard et nøjagtigt resultat, hvilket gør det nemmere og mere praktisk at bruge dem i deres forgængere.
Lad os antage, at du har en liste over Office location-numre, og at du har brug for at vide, hvilke medarbejdere der er i hver enkelt Office. Regnearket er meget stort, så du kan overveje, at det er en udfordrende opgave. Det er faktisk meget nemt at gøre med en slå. op-funktion.
Funktionerne LOPSLAG og VOPSLAG sammen med index og matcher nogle af de mest nyttige funktioner i Excel.
Bemærk!: Funktionen opslag er ikke længere tilgængelig i Excel.
Her er et eksempel på, hvordan du bruger LOPSLAG.
=LOPSLAG(B2,C2:E7,3,SAND)
I dette eksempel er B2 det første argument– et dataelement, som funktionen skal arbejde. For LOPSLAG er dette første argument den værdi, du vil finde. Dette argument kan være en cellereference eller en fast værdi, f. eks "Sørensen" eller 21.000. Det andet argument er området af celler, C2-: E7, hvor du kan søge efter den værdi, du vil finde. Det tredje argument er kolonnen i det celleområde, der indeholder den værdi, du søger.
Det fjerde argument er valgfrit. Angiv enten sand eller falsk. Hvis du skriver SAND, eller lader argumentet være tomt, returnerer funktionen et tilnærmet match af den værdi, du angav i det første argument. Hvis du skriver FALSK, matcher funktionen den værdi, der er angivet i det første argument. Med andre ord kan du lade det fjerde argument stå tomt – eller du kan angive sand – giver dig større fleksibilitet.
Dette eksempel illustrerer, hvordan funktionen virker. Når du angiver en værdi i celle B2 (det første argument), søger LOPSLAG i cellerne i området C2: E7 (andet argument) og returnerer den nærmeste omtrentlige match fra den tredje kolonne i området, kolonne E (tredje argument).
Det fjerde argument er tomt, så funktionen returnerer et tilnærmet match. Hvis den ikke gjorde det, ville du være nødt til at skrive én af værdierne i kolonne C eller D for overhovedet at få noget resultat.
Når du er fortrolig med LOPSLAG, er funktionen VOPSLAG lige let at bruge. Du angiver de samme argumenter, men det søger i rækker i stedet for kolonner.
Brug af indeks og Sammenlign i stedet for LOPSLAG
Der er visse begrænsninger ved brug af LOPSLAG – Funktionen LOPSLAG kan kun slå en værdi op fra venstre mod højre. Det betyder, at den kolonne, der indeholder den værdi, du søger efter, altid skal være placeret til venstre for den kolonne, der indeholder returværdien. Nu, hvis regnearket ikke er bygget på denne måde, skal du ikke bruge LOPSLAG. Brug kombinationen af indekser og MATCH-funktioner i stedet.
I dette eksempel vises en lille liste, hvor den værdi, vi vil søge på, Chicago, ikke findes i kolonnen længst til venstre. Derfor kan vi ikke bruge LOPSLAG. I stedet bruger vi funktionen SAMMENLIGN til at finde Chicago i området B1:B11. Den blev fundet i række 4. Derefter anvendes denne værdi af INDEKS som opslagsargument, og Chicagos indbyggertal findes i den fjerde kolonne (kolonne D). Den anvendte formel vises i celle A14.
Hvis du vil have mere at vide om at bruge indeks og Sammenlign i stedet for LOPSLAG, skal du se artiklen https://www.MrExcel.com/Excel-tips/Excel-VLOOKUP-index-match/ fra Bill Jelen, Microsoft MVP.
Prøv det
Hvis du vil eksperimentere med opslagsfunktioner, før du prøver dem med dine egne data, er her nogle eksempeldata.
LOPSLAG-eksempel på arbejde
Kopiér følgende data til et tomt regneark.
Tip!: Inden du indsætter dataene i Excel, skal du angive kolonnebredderne for kolonnerne A til og med C til 250 pixel og klikke på Ombryd tekst (fanen Hjem, gruppen Justering).
Tæthed | Viskositet | Temperatur |
|---|---|---|
|
0,457 |
3,55 |
500 |
|
0,525 |
3,25 |
400 |
|
0,606 |
2,93 |
300 |
|
0,675 |
2,75 |
250 |
|
0,746 |
2,57 |
200 |
|
0,835 |
2,38 |
150 |
|
0,946 |
2,17 |
100 |
|
1,09 |
1,95 |
50 |
|
1,29 |
1,71 |
0 |
|
Formel |
Beskrivelse |
Resultat |
|
=LOPSLAG(1;A2:C10;2) |
Søger efter den omtrentlige værdi 1 i kolonne A, finder den største værdi, der er mindre end eller lig med 1 i kolonne A, som er 0,946, og returnerer derefter værdien fra kolonne B i samme række. |
2,17 |
|
=LOPSLAG(1;A2:C10;3;SAND) |
Søger efter den omtrentlige værdi 1 i kolonne A, finder den største værdi, der er mindre end eller lig med 1 i kolonne A, som er 0,946, og returnerer derefter værdien fra kolonne C i samme række. |
100 |
|
=LOPSLAG(0,7;A2:C10;3;FALSK) |
Søger efter den præcise værdi 0,7 i kolonne A. Da der ikke findes en tilsvarende værdi i kolonne A, returneres en fejl. |
#I/T |
|
=LOPSLAG(0,1;A2:C10;2;SAND) |
Søger efter den præcise værdi 0,1 i kolonne A. Da 0,1 er mindre end den mindste værdi i kolonne A, returneres en fejl. |
#I/T |
|
=LOPSLAG(2;A2:C10;2;SAND) |
Søger efter den omtrentlige værdi 2 i kolonne A, finder den største værdi, der er mindre end eller lig med 2 i kolonne A, som er 1,29, og returnerer derefter værdien fra kolonne B i samme række. |
1,71 |
VOPSLAG-eksempel
Kopiér alle cellerne i denne tabel, og indsæt det kopierede i celle A1 i et tomt regneark i Excel.
Tip!: Inden du indsætter dataene i Excel, skal du angive kolonnebredderne for kolonnerne A til og med C til 250 pixel og klikke på Ombryd tekst (fanen Hjem, gruppen Justering).
Aksler | Lejer | Bolte |
|---|---|---|
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Formel |
Beskrivelse |
Resultat |
|
=VOPSLAG("Aksler";A1:C4; 2; SAND) |
Søger efter "Aksler" i række 1 og returnerer den værdi fra række 2, der er i den samme kolonne (kolonne A). |
4 |
|
=VOPSLAG("Lejer";A1:C4; 3; FALSK) |
Søger efter "Lejer" i række 1 og returnerer den værdi fra række 3, der er i den samme kolonne (kolonne B). |
7 |
|
=VOPSLAG(";B";A1:C4;3;SAND) |
Søger efter "B" i række 1 og returnerer den værdi fra række 3, der er i den samme kolonne. Fordi der ikke blev fundet en nøjagtig match for "B", bruges den største værdi i række 1, der er mindre end "B": "Aksler" i kolonne A. |
5 |
|
=VOPSLAG("Bolte";A1:C4;4) |
Søger efter "Bolte" i række 1 og returnerer den værdi fra række 4, der er i den samme kolonne (kolonne C). |
11 |
|
=VOPSLAG(3; {1;2;3;"a";"b";"c";"d";"e";"f"}; 2; SAND) |
Søger efter tallet 3 i matrixkonstanten med tre rækker og returnerer værdien fra række 2 i den samme (i dette tilfælde den tredje) kolonne. Der findes tre rækker af værdier i matrixkonstanten, og rækkerne er adskilt med semikolon (;). Fordi "c" findes i række 2 og i den samme kolonne som 3, returneres "c". |
c |
Eksempler på indeks og Sammenlign
I dette sidste eksempel anvendes funktionerne indeks og Sammenlign til at returnere det tidligste fakturanummer og den tilsvarende dato for hver af de fem byer. Da datoen returneres som et tal, bruges funktionen TEKST til at formatere tallet som en dato. Funktionen INDEKS bruger resultatet af funktionen SAMMENLIGN som argument. Kombinationen af funktionerne INDEKS og SAMMENLIGN bruges to gange i hver formel – første gang til at returnere fakturanummeret og derefter til at returnere datoen.
Kopiér alle cellerne i denne tabel, og indsæt det kopierede i celle A1 i et tomt regneark i Excel.
Tip!: Inden du indsætter dataene i Excel, skal du angive kolonnebredderne for kolonnerne A til og med D til 250 pixel og klikke på Ombryd tekst (fanen Hjem, gruppen Justering).
Faktura | By | Fakturadato | Tidligste faktura pr. by, med dato |
|---|---|---|---|
|
3115 |
Atlanta |
07-04-2012 |
="Atlanta = "&INDEKS($A$2:$C$33;SAMMENLIGN("Atlanta";$B$2:$B$33;0);1)& "; Fakturadato: " & TEKST(INDEKS($A$2:$C$33;SAMMENLIGN("Atlanta";$B$2:$B$33;0);3);"d/m/åå") |
|
3137 |
Atlanta |
09-04-2012 |
="Austin= "&INDEKS($A$2:$C$33;SAMMENLIGN("Austin";$B$2:$B$33;0);1)& "; Fakturadato: " & TEKST(INDEKS($A$2:$C$33;SAMMENLIGN("Austin";$B$2:$B$33;0);3);"d/m/åå") |
|
3154 |
Atlanta |
11-04-2012 |
="Dallas= "&INDEKS($A$2:$C$33;SAMMENLIGN("Dallas";$B$2:$B$33;0);1)& "; Fakturadato: " & TEKST(INDEKS($A$2:$C$33;SAMMENLIGN("Dallas";$B$2:$B$33;0);3);"d/m/åå") |
|
3191 |
Atlanta |
21-04-2012 |
="New Orleans = "&INDEKS($A$2:$C$33;SAMMENLIGN("New Orleans";$B$2:$B$33;0);1)& "; Fakturadato: " & TEKST(INDEKS($A$2:$C$33;SAMMENLIGN("New Orleans";$B$2:$B$33;0);3);"d/m/åå") |
|
3293 |
Atlanta |
25-04-2012 |
="Tampa = "&INDEKS($A$2:$C$33;SAMMENLIGN("Tampa";$B$2:$B$33;0);1)& "; Fakturadato: " & TEKST(INDEKS($A$2:$C$33;SAMMENLIGN("Tampa";$B$2:$B$33;0);3);"d/m/åå") |
|
3331 |
Atlanta |
27-04-2012 |
|
|
3350 |
Atlanta |
28-04-2012 |
|
|
3390 |
Atlanta |
01-05-2012 |
|
|
3441 |
Atlanta |
02-05-2012 |
|
|
3517 |
Atlanta |
08-05-2012 |
|
|
3124 |
Austin |
09-04-2012 |
|
|
3155 |
Austin |
11-04-2012 |
|
|
3177 |
Austin |
19-04-2012 |
|
|
3357 |
Austin |
28-04-2012 |
|
|
3492 |
Austin |
06-05-2012 |
|
|
3316 |
Dallas |
25-04-2012 |
|
|
3346 |
Dallas |
28-04-2012 |
|
|
3372 |
Dallas |
01-05-2012 |
|
|
3414 |
Dallas |
01-05-2012 |
|
|
3451 |
Dallas |
02-05-2012 |
|
|
3467 |
Dallas |
02-05-2012 |
|
|
3474 |
Dallas |
04-05-2012 |
|
|
3490 |
Dallas |
05-05-2012 |
|
|
3503 |
Dallas |
08-05-2012 |
|
|
3151 |
New Orleans |
09-04-2012 |
|
|
3438 |
New Orleans |
02-05-2012 |
|
|
3471 |
New Orleans |
04-05-2012 |
|
|
3160 |
Tampa |
18-04-2012 |
|
|
3328 |
Tampa |
26-04-2012 |
|
|
3368 |
Tampa |
29-04-2012 |
|
|
3420 |
Tampa |
01-05-2012 |
|
|
3501 |
Tampa |
06-05-2012 |
