Slå værdier op ved hjælp af LOPSLAG, INDEKS OG SAMMENLIGN

Antag, at du har en liste over kontorplaceringsnumre, og du har brug for at vide, hvilke medarbejdere der befinder sig på hvert kontor. Regnearket er enormt, så du synes måske, det er en udfordrende opgave. Det er faktisk ret nemt at gøre med en opslagsfunktion.

Funktionerne LOPSLAG og VOPSLAG sammen med INDEKS og SAMMENLIGN er nogle af de mest nyttige funktioner 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 element af data, som funktionen skal bruge for at fungere. 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. "smith" eller 21.000. Det andet argument er det celleområde, C2-:E7, hvor der skal søges efter den værdi, du vil finde. Det tredje argument er den kolonne 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. Hvis du med andre ord lader det fjerde argument være tomt – eller skriver SAND – får du 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 (2. argument) og returnerer det nærmeste match fra den tredje kolonne i området, kolonne E (3. argument).

Det fjerde argument er tomt, så funktionen returnerer et omtrentligt 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 så nem at bruge. Du angiver de samme argumenter, men der søges i rækker i stedet for kolonner.

Brug af INDEKS og SAMMENLIGN i stedet for LOPSLAG

Der er visse begrænsninger ved at bruge 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 slår op, altid skal være placeret til venstre for den kolonne, der indeholder returværdien. Hvis dit regneark ikke er bygget på denne måde, skal du ikke bruge LOPSLAG. Brug i stedet kombinationen af funktionerne INDEKS og SAMMENLIGN.

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.

Prøv det

Hvis du vil eksperimentere med opslagsfunktioner, før du prøver dem med dine egne data, er her nogle eksempeldata.

Eksempel på LOPSLAG på arbejde

Kopiér følgende data til et tomt regneark.

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

Eksempel på VOPSLAG

Kopiér alle cellerne i denne tabel, og indsæt det kopierede i celle A1 i et tomt regneark i Excel.

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 sammen for 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.

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

Se Også

Oversigtskort: opdatering af LOPSLAG-funktionen

Opslags- og referencefunktioner (reference)

Brug af argumentet tabelmatrix i en LOPSLAG-funktion

Kom gratis i gang med Excel på internettet