Sådan slår du flere værdier op i Excel: Trin-for-trin

Når du arbejder med store datasæt i Microsoft Excel, kan det være udfordrende at finde flere værdier, der matcher flere forhold. Nogle indbyggede funktioner, f.eks. VLOOKUP, blev oprindeligt designet til at arbejde med enkelte værdier.

Flere kan kombineres for at slå flere værdier op. De inkluderer funktionerne VLOOKUP, INDEX, MATCH og IF. Nuværende versioner af Excel kan bruge dynamiske arrays, mens ældre versioner bruger array-formler.

Denne artikel viser dig præcis, hvordan du bruger disse funktioner i formler, der finder flere værdier i dine data.

Lad os gå!

Indholdsfortegnelse

• Sådan bruges VLOOKUP med flere værdier
  • Hvad er matrixformler?
  • VLOOKUP eksempel
  • Seneste versioner af Excel
  • Ældre versioner
  • Præcis match vs. omtrentlig match
  • Mere om VLOOKUP
• Sådan bruges INDEX og MATCH til at slå flere værdier op
  • Sådan bruges INDEX og MATCH sammen i Excel 365
  • Arbejde med ældre versioner af Excel
• Sådan bruges IF-funktionen til at finde flere værdier
  • Aktuelle versioner af Excel
  • Ældre versioner af Excel
• Sådan bruger du SUMPRODUCT til flere kriterier
• 3 almindelige fejl med opslagsfunktioner
  • 1. #N/A fejl
  • 2. #REF! Fejl
  • 3. Cirkulære fejl
• Sådan bruges IFERROR med opslagsfunktioner
• 7 avancerede opslagsteknikker
  • 1. Relativ positionsopslag
  • 2. LILLE Funktion
  • 3. RÆKKE Funktion
  • 4. FILTER Funktion
  • 5. Hjælpersøjler
  • 6. Dynamiske arrays
  • 7. Strømforespørgsel
• Afsluttende tanker

Sådan bruges VLOOKUP med flere værdier

Funktionen bruges ofte til at finde enkelte værdier i en række data. Du kan dog også slå flere match op i Excel med denne opslagsformel.

Som standard returnerer den kun den første matchende værdi, den finder. Du kan dog ændre funktionen til at returnere flere værdier ved at bruge en matrixformel.

Hvad er matrixformler?

En matrixformel er en formel, der kan udføre beregninger på matrixer af data. Det kaldes en matrixformel, fordi den kan returnere en matrix af resultater i stedet for en enkelt værdi.

Der er flere nemme trin, når du opretter en matrixformel:

1. Vælg et celleområde for at søge.

2. Indtast formlen i formellinjen.

3. Tryk på Ctrl + Shift + Enter for at fuldføre det.

Syntaksen for en matrixformel ligner den for en almindelig formel, men den inkluderer krøllede klammer {} omkring formlen. De krøllede klammeparenteser angiver, at formlen er en matrixformel, og at den vil returnere en matrix af værdier.

Eksemplerne i denne artikel viser dig, hvordan du bruger matrixformler korrekt.

VLOOKUP eksempel

Vores eksempel har fem elementer i kolonne A i et regneark:

• Æble

• Banan

• Brød

• Gulerod

• Kirsebær

Opgaven er at kontrollere, om tre specifikke frugter er på denne liste: æble, banan og kirsebær.

Seneste versioner af Excel

VLOOKUP-syntaksen afhænger af, hvilken version af Excel du bruger.

De seneste versioner ( Excel 365 eller Excel 2021 ) understøtter dynamiske arrays. Denne funktion gør det muligt for formler at returnere flere resultater, der "spilder" ind i tilstødende celler.

Dette er syntaksen (lighedstegnet starter formlen):

=VOPSLAG(opslagsværdi, tabelmatrix, kolonneindekstal, [områdeopslag])

• lookup_value: Den værdi, du vil slå op.

• table_array: Hele den datatabel, du vil søge efter.

• col_index_num: Tabellens kolonnenummer i table_array, der indeholder de data, du vil returnere.

• range_lookup: Valgfrit. Angiver, om du ønsker et nøjagtigt match eller et omtrentligt match.

Vores specifikke eksempel bruger denne formel:

=OPSLAG({“æble”,,”banan”,,”kirsebær”},A1:A5,1,FALSK)

Når formlen indtastes i celle B1, spredes resultaterne ind i cellerne C1 og D1. Dette billede viser eksemplet i aktion:

Ældre versioner

Hvis du bruger en ældre version af Excel, der ikke understøtter dynamiske arrays (f.eks. Excel 2019 eller tidligere), skal du bruge en lidt anden tilgang med en arrayformel.

Følg disse trin:

1. Klik på den celle, hvor du vil vise resultaterne for det første element (f.eks. kolonne B).

2. Indtast følgende formel uden at trykke på Enter endnu: =VLOOKUP({“æble”,,”banan”,,”kirsebær”}, A1:A5, 1, FALSK)

3. Tryk på Ctrl + Shift + Enter for at gøre dette til en matrixformel.

4. Kopier celle B1 og indsæt den i cellen nedenfor (eller brug udfyldningshåndtagene).

Når du bruger Ctrl + Shift + Enter, tilføjer Excel krøllede klammeparenteser omkring formlen. Dette indikerer, at det er en matrixformel.

Præcis match vs. omtrentlig match

Som standard bruger VLOOKUP-funktionen et omtrentligt match. Det betyder, at det vil returnere det nærmeste match, det kan finde, selvom celleværdierne ikke er et nøjagtigt match.

Hvis du vil udføre et eksakt match, kan du indstille range_lookup-argumentet til FALSE.

Husk, at omtrentlige overensstemmelser fungerer bedst med ordnede numeriske værdier. Det er normalt ikke passende, når celleværdien er tekst.

Mere om VLOOKUP

Hvis du vil lære mere om denne alsidige funktion, så tjek disse artikler:

Nu hvor du er indstillet med VLOOKUP-funktionen, lad os tage et kig på to andre funktioner, der kan gøre, hvad den gør på en anden måde: INDEX og MATCH.

Sådan bruges INDEX og MATCH til at slå flere værdier op

Du kan kombinere funktionerne INDEX og MATCH sammen for at finde flere værdier i flere rækker.

INDEX-funktionen i Excel returnerer en værdi eller reference til en celle inden for et specificeret område.

=INDEKS(matrix; rækkenummer; [kolonne_nummer])

• matrix: Området af celler, der skal søges efter værdien.

• row_num: Rækkenummeret i arrayet, hvorfra en værdi skal returneres.

• column_num: (Valgfrit) Kolonnenummeret i arrayet, hvorfra en værdi skal returneres. Hvis den udelades, returnerer funktionen hele rækken.

MATCH-funktionen i Excel returnerer positionen af ​​en værdi inden for et specificeret interval.

=MATCH(opslagsværdi, opslagsmatrix, [matchtype])

• lookup_value: Værdien, der skal søges efter i lookup_arrayet.

• lookup_array: Området af celler, der skal søges efter opslagsværdien.

• match_type: (Valgfrit) Den type match, der skal udføres. Hvis den udelades, udfører funktionen et nøjagtigt match.

Sådan bruges INDEX og MATCH sammen i Excel 365

For at bruge INDEX og MATCH sammen til at slå flere værdier op i Excel, skal du bruge en matrixformel.

Når du arbejder med de tidligere eksempeldata, er dette formlen i Excel 365:

=INDEX(A1:A5, MATCH({"æble","banan","kirsebær"}, A1:A5, 0))

Ovenstående eksempel opdeles således:

• INDEX: dette returnerer værdien af ​​en celle i et specificeret område baseret på et givet række- og kolonnenummer. I dette tilfælde returnerer den værdien fra området A1:A5.

• A1:A5: Dette er det definerede tabelinterval, hvor du søger efter værdien, og hvorfra resultatet vil blive returneret.

• MATCH: dette søger efter et specificeret element i et celleområde og returnerer den relative position for det pågældende element i området.

• {“apple”,,”banana”,,”cherry”}: dette er matrixkonstanten, der indeholder de værdier, du vil slå op.

• A1:A5: dette er det område, hvor MATCH vil søge efter værdierne fra matrixkonstanten.

• 0: dette er matchtypen for MATCH-funktionen. I dette tilfælde er det 0, hvilket betyder, at du leder efter et nøjagtigt match i stedet for et tæt match.

 Dette billede viser formlen i aktion:

Arbejde med ældre versioner af Excel

Hvis du bruger en ældre Excel-fil, der ikke understøtter dynamiske arrays (f.eks. Excel 2019 eller tidligere), skal du bruge en anden tilgang.

Fordi ældre versioner ikke understøtter, at formlerne "spilder" ind i tilstødende celler, bliver du nødt til at opdele brugen i tre separate formler.

Følg disse trin:

1. Klik på den celle, hvor du vil have resultatet for det første element (f.eks. celle B1)

2. Indtast nedenstående formel:

3. =INDEKS(A1:A5, MATCH("æble", A1:A5, 0))

4. Tryk på Enter for at udføre formlen.

5. Indtast denne formel i celle B2: =INDEX(A1:A5, MATCH("banan", A1:A5, 0))

6. Indtast denne formel i celle B3: =INDEX(A1:A5, MATCH("kirsebær", A1:A5, 0))

Dette billede viser cellereference B3:

INDEX- og MATCH-funktionerne er ikke de eneste, der kan gøre dig i stand til at finde flere værdier. I næste afsnit ser vi på, hvordan du kan bruge IF-funktionen som et alternativ.

Sådan bruges IF-funktionen til at finde flere værdier

En anden måde at slå flere celleværdier på baseret på bestemte kriterier er at bruge IF-funktionen med andre funktioner.

IF-funktionen giver dig mulighed for at teste flere forhold og returnere forskellige resultater afhængigt af resultatet af disse tests.

Lad os f.eks. sige, at du har en tabel med salgsdata med kolonner for Produkt og Salg. Du ønsker at slå op og sammenlægge salgsbeløbet for to af de tre produkter.

Aktuelle versioner af Excel

For at finde summen af ​​kolonnen Salg, hvor produktet enten er "Æble" eller "Banan" ved hjælp af IF-funktionen, kan du bruge en matrixformel med funktionerne HVIS, SUM og ELLER.

Forudsat at dine data starter i celle A1, skal du bruge følgende formel:

=SUM(HVIS((A2:A4=”Æble”)+(A2:A4=”Banan”), B2:B4, 0))

Sektionen (A2:A4=”Apple”)+(A2:A4=”Banana”) opretter en matrix, der har en værdi på 1, hvis cellen i området A2:A4 indeholder “Apple” eller “Banana”, og 0 ellers .

IF-sætningen kontrollerer hvert element i array-argumentet. Hvis værdien er 1 (dvs. produktet er enten "Apple" eller "Banana"), tager det den tilsvarende værdi i kolonnen Salg (område B2:B4); ellers tager det 0.

SUM-funktionen summerer værdierne fra IF-funktionen og summerer effektivt salgsværdierne for både "Apple" og "Banana".

Dette billede viser formlen i aktion på søgeområdet:

Ældre versioner af Excel

I Excel 2019 eller tidligere skal du bruge en matrixformel. Følg disse trin:

1. Indtast formlen, men tryk ikke på Enter.

2. Tryk på Ctrl + Shift + Enter for at gøre det til en matrixformel.

Excel tilføjer krøllede parenteser {} omkring formlen, hvilket angiver, at det er en matrixformel.

Dernæst ser vi på, hvordan du kan bruge SUMPRODUCT til at slå flere værdier op baseret på dine kriterier. Lad os gå!

Sådan bruger du SUMPRODUCT til flere kriterier

SUMPRODUCT-funktionen giver dig også mulighed for at slå flere værdier op baseret på flere kriterier.

Fordi det ikke kræver brug af en matrixformel, er syntaksen den samme uanset versionen af ​​Excel.

Ved at bruge de samme data som i det foregående eksempel ser formlen sådan ud:

=SUMPRODUKT((A2:A4=”Æble”)+(A2:A4=”Banan”), B2:B4)

Sektionen (A2:A4=”Apple”)+(A2:A4=”Banana”) opretter en matrix, der har en værdi på 1, hvis cellen i området A2:A4 indeholder “Apple” eller “Banana”, og 0 ellers .

Funktionen SUMPRODUKT multiplicerer elementerne i arrayet med de tilsvarende elementer i kolonnen Salg (område B2:B4). Den lægger derefter de resulterende værdier sammen, og summerer effektivt salgsværdierne for både "Apple" og "Banana".

Nedenstående formel viser det i aktion:

Excel-funktioner er fantastiske, når de fungerer som forventet, men nogle gange kan du løbe ind i fejl. I næste afsnit dækker vi nogle af de almindelige fejl, og hvordan du kan håndtere dem.

3 almindelige fejl med opslagsfunktioner

Opslagsfunktioner kan nogle gange returnere fejl, der kan være frustrerende og tidskrævende at fejlfinde. De tre mest almindelige fejl, du vil støde på, er:

1. #N/A fejl

2. #REF! Fejl

3. Cirkulære fejl

1. #N/A fejl

#N/A-fejlen opstår, når opslagsværdien ikke kan findes i opslagsarrayet.

Der er flere grunde til, at denne fejl kan opstå, herunder:

• opslagsværdien er stavet forkert eller forkert.

• opslagsarrayet er ikke sorteret i stigende rækkefølge.

• opslagsværdien er ikke i datasættet.

Når opslagsværdien ikke er i datasættet, er dette nyttig information. Men uerfarne Excel-brugere tror måske, at #N/A betyder, at noget er gået galt med formlen. Det næste afsnit viser, hvordan du gør dette mere brugervenligt.

2. #REF! Fejl

#REF! fejl opstår, når opslagsmatrixen eller returarrayet slettes eller flyttes.

Denne fejl kan rettes ved at opdatere cellereferencerne i opslagsfunktionen.

3. Cirkulære fejl

Når du kombinerer funktioner i komplekse formler, kan Excel fortælle dig, at du har en cirkulær reference.

Du vil finde disse lettere at undersøge ved at bruge vores guide til .

Sådan bruges IFERROR med opslagsfunktioner

IFERROR-funktionen er et nyttigt værktøj til håndtering af fejl i opslagsfunktioner. Det giver dig mulighed for at angive en værdi eller formel, der skal returneres, hvis opslagsfunktionen returnerer en fejl.

Syntaksen for IFERROR-funktionen er som følger:

=IFERROR(værdi; værdi_hvis_fejl)

· værdi: værdi eller formel, du vil evaluere.

· Værdi_hvis_fejl: værdien eller formlen, der skal returneres, hvis det første argument returnerer en fejl.

Lad os f.eks. sige, at du har en VLOOKUP-funktion, der slår flere værdier op i en tabel. På billedet nedenfor findes en af ​​værdierne ikke i det søgte dataområde.

Som du kan se, vises #N/A-fejlen, hvilket kan være forvirrende for uerfarne Excel-brugere.

I stedet kan du bruge IFERROR til at vise en tom celle eller en besked, der siger "Ikke fundet" med denne syntaks:

=IFERROR(VLOOKUP(opslagsværdi, tabelmatrix, kolonneindeks, FALSK), "Ikke fundet")

Hvis funktionen VOPSLAG i dette eksempel returnerer en fejl, returnerer IFERROR-funktionen meddelelsen "Ikke fundet" i stedet.

Dette billede viser formlen i aktion. Kolonne B har den manglende værdi, mens kolonne C og kolonne D har fundet matcher.

Vi har dækket meget af jorden indtil videre, og du er endelig klar til at lære mere avancerede teknikker til opslag, som er emnet for næste afsnit.

7 avancerede opslagsteknikker

At slå flere værdier op i Excel kan være en udfordrende opgave, især når man har at gøre med store datasæt. Du kan støde på ydeevneproblemer med langsom behandling.

Der er syv avancerede opslagsteknikker, du kan bruge til at gøre processen nemmere og mere effektiv.

• Relativ stillingsopslag

• LILLE funktion

• ROW funktion

• FILTER Funktion

• Hjælperkolonner

• Dynamiske arrays

• Strømforespørgsel

1. Relativ positionsopslag

En af de enkleste er ved at bruge relativ positionsopslag. Dette involverer specificering af række- og kolonneforskydninger fra en given celle for at finde den ønskede værdi.

For eksempel, hvis du har en datatabel og ønsker at slå en værdi op, der er to rækker nede og tre kolonner til højre for en given celle, kan du bruge følgende formel:

=OFFSET(celle; 2; 3)

2. LILLE Funktion

En anden nyttig teknik til flere værdiopslag er at bruge SMALL-funktionen. Denne funktion returnerer den n. mindste værdi i et celleområde.

Ved at kombinere denne funktion med andre opslagsfunktioner som INDEX og MATCH, kan du slå flere værdier op baseret på specifikke kriterier.

For eksempel slår følgende formel den næstmindste værdi op i et celleområde, der opfylder en bestemt betingelse:

=INDEKS(data; SAMMENLIGN(SMÅ(HVIS(kriterier; område); 2); område; 0))

3. RÆKKE Funktion

ROW-funktionen kan også bruges til flere værdiopslag i Excel. Denne funktion returnerer rækkenummeret for en given celle, som kan bruges til at referere til celler i en datatabel.

For eksempel slår følgende formel en værdi op i en tabel baseret på en unik identifikator:

=INDEKS(data; MATCH(entydig_id; data[entydig_id], 0); kolonneindeksnummer)

4. FILTER Funktion

FILTER-funktionen er ikke tilgængelig i ældre versioner af Excel.

I Excel 365 kan du bruge det til at filtrere en række celler baseret på bestemte kriterier og kun returnere de værdier, der opfylder disse kriterier. Dette er syntaksen og tre argumenter:

=FILTER(matrix, inkludere, [hvis_tom])

• array: De specifikke data, du vil filtrere.

• include: Inkluderingsargumentet er de kriterier eller betingelser, du vil anvende på arrayet.

• [if_empty] (valgfrit): Værdien, der skal returneres, hvis ingen rækker eller kolonner opfylder kriterierne angivet i inkluderingsargumentet.

For eksempel fungerer følgende formel på eksempeldataene for at finde match for to af de tre elementer i den første kolonne og summere deres tilsvarende værdier i den anden kolonne.

=SUM(FILTER(B2:B4, (A2:A4=”Æble”)+(A2:A4=”Banan”)))

Dette billede viser, hvor mange rækker der matches og summen af ​​de forskellige værdier:

5. Hjælpersøjler

Du kan bruge en hjælpekolonne til at samle flere felter i en funktion som VLOOKUP.

Antag, at du arbejder med for- og efternavne i et separat ark. Du kan sammenkæde dem i en hjælpekolonne, der henvises til i den endelige formel.

6. Dynamiske arrays

Som du har lært i tidligere eksempler, kan Microsoft 365-brugere drage fordel af dynamiske arrays til flere værdiopslag i Excel.

Dynamiske arrays giver dig mulighed for at returnere flere værdier fra en enkelt formel, hvilket gør det nemmere at slå store mængder data op.

7. Strømforespørgsel

Power Query er et kraftfuldt værktøj i Excel, der kan bruges til at returnere værdier baseret på flere kriterier.

For eksempel finder denne video rodet data i et regneark og rydder op i det.

Afsluttende tanker

Det afslutter vores dybe dyk i kunsten at slå flere værdier op i Excel! Når du først har mestret formlerne VLOOKUP, INDEX, MATCH og array, vil du finde dig selv at komme igennem komplekse datasæt som en varm kniv gennem smør.

Nøglen er at forstå syntaksen og logikken bag hver formel. Husk på, at disse formler kan være komplekse, så det er vigtigt at tage dig god tid og teste dine formler grundigt, før du stoler på dem til vigtig dataanalyse.

Excel er et kraftfuldt værktøj, men det er kun så godt som brugeren. Så fortsæt med at finpudse disse færdigheder, fortsæt med at eksperimentere, og du vil snart blive mester i opslag med flere værdier. Indtil næste gang, fortsæt med at knuse disse tal og få Excel til at gøre det hårde arbejde for dig!