Hvordan lage en Excel-oppslagsformel med flere kriterier

2024 Forfatter: Abigail Brown | [email protected]. Sist endret: 2023-12-17 06:57

What to Know

• Lag først en INDEX-funksjon, og start deretter den nestede MATCH-funksjonen ved å skrive inn Lookup_value-argumentet.
• Deretter legger du til Lookup_array-argumentet etterfulgt av Match_type-argumentet, og spesifiser deretter kolonneområdet.
• Deretter gjør du den nestede funksjonen til en matriseformel ved å trykke Ctrl+ Shift+ Enter. Til slutt legger du til søkeordene i regnearket.

Denne artikkelen forklarer hvordan du lager en oppslagsformel som bruker flere kriterier i Excel for å finne informasjon i en database eller tabell med data ved å bruke en matriseformel. Matriseformelen innebærer å neste MATCH-funksjonen inne i INDEX-funksjonen. Informasjonen dekker Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 og Excel for Mac.

Følg med veiledningen

For å følge trinnene i denne opplæringen, skriv inn eksempeldataene i de følgende cellene, som vist i bildet nedenfor. Rad 3 og 4 er tomme for å imøtekomme matriseformelen som ble opprettet under denne opplæringen. (Merk at denne opplæringen ikke inkluderer formateringen som vises på bildet.)

• Skriv inn det øverste dataområdet i cellene D1 til F2.
• Skriv inn det andre området i cellene D5 til F11.

Opprett en INDEX-funksjon i Excel

INDEX-funksjonen er en av få funksjoner i Excel som har flere former. Funksjonen har et matriseskjema og et referanseskjema. Array Form returnerer data fra en database eller tabell med data. Referanseskjemaet gir cellereferansen eller plasseringen av dataene i tabellen.

I denne opplæringen brukes Array Form for å finne navnet på leverandøren for titan widgets, i stedet for cellereferansen til denne leverandøren i databasen.

Følg disse trinnene for å lage INDEX-funksjonen:

1. Velg celle F3 for å gjøre den til den aktive cellen. Denne cellen er der den nestede funksjonen legges inn.

2. Gå til Formulas.

   

3. Velg Oppslag og referanse for å åpne rullegardinlisten for funksjoner.
4. Velg INDEX for å åpne Select Arguments-dialogboksen.
5. Velg array, row_num, column_num.
6. Velg OK for å åpne Function Arguments dialogboksen. I Excel for Mac åpnes Formelbyggeren.
7. Plasser markøren i tekstboksen Array.

8. Uthev celler D6 til F11 i regnearket for å legge inn området i dialogboksen.

   La funksjonsargument-dialogboksen være åpen. Formelen er ikke ferdig. Du fullfører formelen i instruksjonene nedenfor.

   

Start Nested MATCH-funksjonen

Når en funksjon legges inn i en annen, er det ikke mulig å åpne den andre, eller nestede, funksjonens formelbygger for å legge inn de nødvendige argumentene. Den nestede funksjonen må angis som et av argumentene til den første funksjonen.

Når funksjoner legges inn manuelt, skilles funksjonens argumenter fra hverandre med komma.

Det første trinnet for å angi den nestede MATCH-funksjonen er å angi Lookup_value-argumentet. Lookup_value er plasseringen eller cellereferansen for søkeordet som skal matches i databasen.

The Lookup_value godtar bare ett søkekriterium eller en term. For å søke etter flere kriterier utvider du Lookup_value ved å sette sammen, eller slå sammen, to eller flere cellereferanser ved å bruke og-tegnet (&).

1. I Function Arguments-dialogboksen plasserer du markøren i tekstboksen Row_num..
2. Enter MATCH(.
3. Velg celle D3 for å legge inn cellereferansen i dialogboksen.
4. Skriv inn & (et-tegnet) etter cellereferansen D3 for å legge til en andre cellereferanse.

5. Velg celle E3 for å angi den andre cellereferansen.

6. Enter , (et komma) etter cellereferansen E3 for å fullføre inntastingen av MATCH-funksjonens Lookup_value-argument.

   

   I det siste trinnet i opplæringen vil Lookup_values bli lagt inn i celle D3 og E3 i regnearket.

Fullfør Nested MATCH-funksjonen

Dette trinnet dekker å legge til Lookup_array-argumentet for den nestede MATCH-funksjonen. Lookup_array er celleområdet som MATCH-funksjonen søker for å finne Lookup_value-argumentet lagt til i forrige trinn i opplæringen.

Fordi to søkefelt ble identifisert i Lookup_array-argumentet, må det samme gjøres for Lookup_array. MATCH-funksjonen søker bare i én matrise for hvert spesifisert begrep. For å angi flere matriser, bruk og-tegnet for å sette sammen matrisene.

1. Plasser markøren på slutten av dataene i tekstboksen Row_num. Markøren vises etter kommaet på slutten av gjeldende oppføring.

2. Uthev celler D6 til D11 i regnearket for å gå inn i området. Dette området er den første matrisen funksjonen søker etter.
3. Skriv inn & (et og-tegn) etter cellereferansene D6:D11. Dette symbolet får funksjonen til å søke i to arrays.
4. Uthev celler E6 til E11 i regnearket for å gå inn i området. Dette området er den andre matrisen funksjonen søker etter.

5. Skriv inn , (et komma) etter cellereferansen E3 for å fullføre oppføringen av MATCH-funksjonens Lookup_array-argument.

   

6. La dialogboksen være åpen for neste trinn i veiledningen.

Legg til MATCH Type-argumentet

Det tredje og siste argumentet til MATCH-funksjonen er Match_type-argumentet. Dette argumentet forteller Excel hvordan man matcher Lookup_value med verdier i Lookup_array. De tilgjengelige valgene er 1, 0 eller -1.

Dette argumentet er valgfritt. Hvis den utelates, bruker funksjonen standardverdien 1.

• Hvis Match_type=1 eller er utelatt, finner MATCH den største verdien som er mindre enn eller lik Lookup_value. Lookup_array-dataene må sorteres i stigende rekkefølge.
• Hvis Match_type=0, finner MATCH den første verdien som er lik Lookup_value. Lookup_array-dataene kan sorteres i hvilken som helst rekkefølge.
• Hvis Match_type=-1, finner MATCH den minste verdien som er større enn eller lik Lookup_value. Lookup_array-dataene må sorteres i synkende rekkefølge.

Angi disse trinnene etter kommaet som ble angitt i forrige trinn på Row_num-linjen i INDEX-funksjonen:

1. Skriv inn 0 (en null) etter kommaet i tekstboksen Row_num. Dette tallet får den nestede funksjonen til å returnere eksakte treff til termene som er angitt i cellene D3 og E3.

2. Skriv inn ) (en avsluttende runde-parentes) for å fullføre MATCH-funksjonen.

   

3. La dialogboksen være åpen for neste trinn i veiledningen.

Fullfør INDEX-funksjonen

MATCH-funksjonen er ferdig. Det er på tide å gå til tekstboksen Column_num i dialogboksen og skrive inn det siste argumentet for INDEX-funksjonen. Dette argumentet forteller Excel at kolonnenummeret er i området D6 til F11. Dette området er der den finner informasjonen som returneres av funksjonen. I dette tilfellet en leverandør for titan widgets.

1. Plasser markøren i tekstboksen Column_num.

2. Skriv inn 3 (tall tre). Dette tallet forteller at formelen skal se etter data i den tredje kolonnen i området D6 til F11.

   

3. La dialogboksen være åpen for neste trinn i veiledningen.

Create the Array Formula

Før du lukker dialogboksen, gjør du den nestede funksjonen til en matriseformel. Denne matrisen lar funksjonen søke etter flere termer i datatabellen. I denne opplæringen er to termer matchet: widgets fra kolonne 1 og titan fra kolonne 2.

For å lage en matriseformel i Excel, trykk CTRL, SHIFT og ENTERtaster samtidig. Når den er trykket, er funksjonen omgitt av krøllete klammeparenteser, noe som indikerer at funksjonen nå er en matrise.

1. Velg OK for å lukke dialogboksen. I Excel for Mac velger du Done.
2. Velg celle F3 for å se formelen, og plasser deretter markøren på slutten av formelen i formellinjen.
3. For å konvertere formelen til en matrise, trykk CTRL+ SHIFT+ ENTER.
4. A N/A feil vises i celle F3. Dette er cellen der funksjonen ble lagt inn.

5. N/A-feilen vises i celle F3 fordi cellene D3 og E3 er tomme. D3 og E3 er cellene der funksjonen ser for å finne Lookup_value. Etter at data er lagt til disse to cellene, erstattes feilen med informasjon fra databasen.

   

Legg til søkekriteriene

Det siste trinnet er å legge til søkeordene i regnearket. Dette trinnet samsvarer med begrepene Widgets fra kolonne 1 og Titanium fra kolonne 2.

Hvis formelen finner samsvar for begge ledd i de aktuelle kolonnene i databasen, returnerer den verdien fra den tredje kolonnen.

1. Velg celle D3.
2. Enter Widgets.
3. Velg celle E3.
4. Skriv Titanium, og trykk Enter.
5. Leverandørens navn, Widgets Inc., vises i celle F3. Dette er den eneste leverandøren som er oppført som selger Titanium Widgets.

6. Velg celle F3. Funksjonen vises i formellinjen over regnearket.

   {=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
   I dette eksemplet er det bare én leverandør for titan-widgets. Hvis det hadde vært mer enn én leverandør, returneres leverandøren som er oppført først i databasen av funksjonen.