I dette emne beskrives de mest almindelige årsager til LOPSLAG til et forkert resultat af funktionen, og der gives forslag til brug af INDEKS og SAMMENLIGN i stedet.
Tip!: Se også oversigtskortet: Tip til fejlfinding af LOPSLAG, som viser de almindelige årsager til #NA problemer i en praktisk PDF-fil. Du kan dele PDF-filen med andre eller udskrive den til eget brug.
Problem: Opslagsværdien er ikke i den første kolonne i argumentet tabelmatrix
En af begrænsningerne for LOPSLAG er, at den kun kan søge efter værdier i kolonnen yderst til venstre i tabelmatrixen. Hvis opslagsværdien ikke er i den første kolonne i matrixen, vises fejlen #N/A.
I følgende tabel vil vi hente antallet af solgte enheder af Kale (kål).
Fejlen #N/A opstår, fordi opslagsværdien “Kale” vises i den anden kolonne (Produce) i table_array argument A2:C10. I dette tilfælde leder Excel efter den i kolonne A, ikke kolonne B.
Løsning: Du kan forsøge at løse dette problem ved at justere LOPSLAG til at henvise til den korrekte kolonne. Hvis det ikke er muligt, kan du prøve at flytte dine kolonner. Det kan også være meget upraktisk, hvis du har store eller komplekse regneark, hvor celleværdier er resultater af andre beregninger— eller måske er der andre logiske årsager til, at du ikke kan flytte rundt på kolonnerne. Løsningen er at bruge en kombination af funktionerne INDEKS og SAMMENLIGN, som kan søge efter en værdi i en kolonne, uanset dens placering i opslagstabellen. Se næste afsnit.
Overvej at bruge INDEKS/SAMMENLIGN i stedet
INDEKS og MATCH er gode indstillinger i mange tilfælde, hvor LOPSLAG ikke opfylder dine behov. Den største fordel ved INDEKS/MATCH er, at du kan slå en værdi op i en kolonne på et hvilket som helst sted i opslagstabellen. INDEKS returnerer en værdi fra en angivet tabel/område—i henhold til dens placering. SAMMENLIGN returnerer den relative placering af en værdi i en tabel/et område. Brug INDEKS og MATCH sammen i en formel til at slå en værdi op i en tabel/matrix ved at angive den relative placering af værdien i tabellen/matrixen.
Der er flere fordele ved at bruge INDEKS/SAMMENLIGN i stedet for LOPSLAG:
-
Med INDEKS og MATCH behøver den returnerede værdi ikke at være i samme kolonne som opslagskolonnen. Dette er forskelligt fra LOPSLAG, hvor returværdien skal være i det angivne område. Hvordan har det nogen betydning? Med LOPSLAG skal du kende det kolonnenummer, der indeholder den returnerede værdi. Selvom det ikke kan virke udfordrende, kan det være besværligt, når du har en stor tabel og er nødt til at tælle antallet af kolonner. Hvis du tilføjer/fjerner en kolonne i tabellen, skal du også gentælle og opdatere argumentet col_index_num. Med INDEKS OG SAMMENLIGN kræves der ingen optælling, da opslagskolonnen adskiller sig fra den kolonne, der indeholder den returnerede værdi.
-
Med INDEKS og MATCH kan du angive enten en række eller kolonne i en matrix – eller angive begge dele. Det betyder, at du kan slå værdier op både lodret og vandret.
-
INDEKS og SAMMENLIGN kan bruges til at slå værdier op i en hvilken som helst kolonne. I modsætning til LOPSLAG, hvor du kun kan slå en værdi op i den første kolonne i en tabel, fungerer INDEKS og MATCH, hvis din opslagsværdi er i den første kolonne, den sidste eller hvor som helst der imellem.
-
INDEKS og MATCH giver fleksibilitet i forbindelse med dynamisk henvisning til den kolonne, der indeholder den returnerede værdi.Det betyder, at du kan føje kolonner til tabellen uden at bryde INDEKS og MATCH. LOPSLAG brydes derimod, hvis du vil tilføje en kolonne i tabellen – da det giver en statisk reference til tabellen.
-
INDEKS og SAMMENLIGN giver større fleksibilitet med match.INDEKS og MATCH kan finde et nøjagtigt match eller en værdi, der er større eller mindre end opslagsværdien. LOPSLAG vil kun søge efter det nærmeste match til en værdi (som standard) eller en nøjagtig værdi. LOPSLAG antager også som standard, at den første kolonne i tabelmatricen er sorteret i alfabetisk rækkefølge, og hvis din tabel ikke er konfigureret på denne måde, returnerer LOPSLAG det nærmeste match i tabellen, som muligvis ikke er de data, du leder efter.
Syntaks
For at opbygge syntaksen for INDEKS/MATCH skal du bruge matrix-/referenceargumentet fra funktionen INDEKS og indlejre MATCH-syntaksen i den. Dette har form:
= INDEKS(matrix eller reference; SAMMENLIGN(opslagsværdi;opslagsmatrix;[sammenligningstype])
Lad os bruge INDEKS/MATCH til at erstatte LOPSLAG fra eksemplet ovenfor. Syntaksen kommer til at se således ud:
=INDEKS(C2:C10;SAMMENLIGN(B13;B2:B10;0))
I almindeligt sprog betyder det:
=INDEKS(returner en værdi fra C2:C10, der matcher(Kale, som findes et sted i matrixen B2:B10, hvor den returnerede værdi er den første værdi, der svarer til Kale))
Formlen søger efter den første værdi i C2:C10, der svarer til Kale (i B7) og returnerer værdien i celle C7 (100), som er den første værdi, der svarer til Kale.
Problem: Der blev ikke fundet et nøjagtigt match
Når argumentet range_lookup er FALSK – og LOPSLAG ikke kan finde et nøjagtigt match i dine data – returneres fejlen #N/A.
Løsning: Hvis du er sikker på, at de relevante data findes i dit regneark, og LOPSLAG ikke fanger dem, skal du bruge tid på at kontrollere, at de refererede celler ikke har skjulte mellemrum eller tegn, der ikke udskrives. Sørg også for, at cellerne følger den korrekte datatype. Eksempelvis skal celler med tal formateres som Talog ikke Tekst.
Du kan også overveje at bruge enten funktionen RENS eller TRIM til at rydde op i data i celler.
Problem: Opslagsværdien er mindre end den mindste værdi i matricen
Hvis argumentet range_lookup er angivet til SAND, og opslagsværdien er mindre end den mindste værdi i matrixen, vises fejlen #N/A. SAND søger efter et nærliggende match i matricen og returnerer den nærmeste værdi, der er mindre end opslagsværdien.
I det følgende eksempel er opslagsværdien 100, men der er ingen værdier inden for området B2:C10, der er mindre end 100; derfor opstår fejlen.
Løsning:
-
Ret opslagsværdien efter behov.
-
Hvis du ikke kan ændre opslagsværdien og har brug for større fleksibilitet med at matche værdier, kan du overveje at bruge INDEKS/SAMMENLIGNING i stedet for LOPSLAG – se afsnittet ovenfor i denne artikel. Med INDEKS/SAMMENLIGN kan du søge efter værdier, der er større end, mindre end eller lig med opslagsværdien. Hvis du vil have mere at vide om at bruge INDEKS/SAMMENLIGN i stedet for LOPSLAG, skal du se det forrige afsnit i dette emne.
Problem: Opslagskolonnen er ikke sorteret i stigende rækkefølge
Hvis argumentet range_lookup er angivet til SAND—og en af dine opslagskolonner ikke er sorteret i stigende rækkefølge (A-Å), vises fejlen #N/A.
Løsning:
-
Rediger funktionen LOPSLAG til at søge efter et nøjagtigt match. For gøre dette skal du angive argumentet søgeinterval til FALSK. Ingen sortering er nødvendig for FALSK.
-
Du kan bruge funktionen INDEKS/SAMMENLIGN til at søge efter en værdi i en usorteret tabel.
Problem: Værdien er et stort flydende tal
Hvis du har klokkeslætsværdier eller store decimaltal i celler, returnerer Excel fejlen #I/T på grund af præcisionen af flydende tal. Flydende tal er tal, der følger efter et decimaltegn. (Excel gemmer klokkeslætsværdier som flydende tal). Excel kan ikke gemme tal med meget store flydende punkter, så for at funktionen kan fungere korrekt, skal de flydende tal afrundes til 5 decimaler.
Løsning: Afkort tallene ved at runde dem op til fem decimaler med funktionen AFRUND.
Har du brug for mere hjælp?
Du kan altid spørge en ekspert i Excel Tech Community eller få support i community'er.
