Sådan opretter du en Excel-opslagsformel med flere kriterier

2024 Forfatter: Abigail Brown | [email protected]. Sidst ændret: 2023-12-17 06:43

Hvad du skal vide

• Opret først en INDEX-funktion, og start derefter den indlejrede MATCH-funktion ved at indtaste Lookup_value-argumentet.
• Næste, tilføj Lookup_array-argumentet efterfulgt af Match_type-argumentet, og angiv derefter kolonneintervallet.
• Vend derefter den indlejrede funktion til en matrixformel ved at trykke på Ctrl+ Shift+ Enter. Tilføj endelig søgetermerne til regnearket.

Denne artikel forklarer, hvordan man opretter en opslagsformel, der bruger flere kriterier i Excel til at finde oplysninger i en database eller tabel med data ved at bruge en matrixformel. Matrixformlen involverer at indlejre MATCH-funktionen inde i INDEX-funktionen. Oplysningerne dækker Excel til Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 og Excel til Mac.

Følg med i selvstudiet

For at følge trinene i denne vejledning skal du indtaste eksempeldataene i de følgende celler, som vist på billedet nedenfor. Række 3 og 4 efterlades tomme for at rumme den matrixformel, der blev oprettet under denne øvelse. (Bemærk, at denne vejledning ikke inkluderer formateringen, der ses på billedet.)

• Indtast det øverste dataområde i cellerne D1 til F2.
• Indtast det andet område i cellerne D5 til F11.

Opret en INDEX-funktion i Excel

INDEX-funktionen er en af de få funktioner i Excel, der har flere former. Funktionen har en Array Form og en Reference Form. Array Form returnerer data fra en database eller tabel med data. Referenceformularen angiver cellereferencen eller placeringen af dataene i tabellen.

I dette selvstudium bruges Array-formularen til at finde navnet på leverandøren til titanium-widgets i stedet for cellereferencen til denne leverandør i databasen.

Følg disse trin for at oprette INDEX-funktionen:

1. Vælg celle F3 for at gøre den til den aktive celle. Denne celle er hvor den indlejrede funktion vil blive indtastet.

2. Gå til Formler.

   

3. Vælg Opslag og reference for at åbne rullelisten med funktioner.
4. Vælg INDEX for at åbne Select Arguments dialogboksen.
5. Vælg array, row_num, column_num.
6. Vælg OK for at åbne Function Arguments dialogboksen. I Excel til Mac åbner Formula Builder.
7. Placer markøren i tekstfeltet Array.

8. Fremhæv celler D6 til F11 i regnearket for at indtaste området i dialogboksen.

   Lad dialogboksen Funktionsargumenter være åben. Formlen er ikke færdig. Du udfylder formlen i instruktionerne nedenfor.

   

Start funktionen Nested MATCH

Når en funktion indlejres i en anden, er det ikke muligt at åbne den anden eller indlejrede funktions formelbygger for at indtaste de nødvendige argumenter. Den indlejrede funktion skal indtastes som et af argumenterne for den første funktion.

Når du indtaster funktioner manuelt, er funktionens argumenter adskilt fra hinanden med et komma.

Det første trin til at indtaste den indlejrede MATCH-funktion er at indtaste Lookup_value-argumentet. Lookup_value er placeringen eller cellereferencen for søgetermen, der skal matches i databasen.

The Lookup_value accepterer kun ét søgekriterium eller en term. For at søge efter flere kriterier skal du udvide Lookup_value ved at sammenkæde to eller flere cellereferencer ved at bruge og-tegnet (&).

1. Placer markøren i Row_num i Function Arguments-dialogboksen..

2. Enter MATCH(.

3. Vælg celle D3 for at indtaste denne cellereference i dialogboksen.
4. Indtast & (et-tegnet) efter cellereferencen D3 for at tilføje en anden cellereference.
5. Vælg celle E3 for at indtaste den anden cellereference.

6. Enter , (et komma) efter cellereferencen E3 for at fuldføre indtastningen af MATCH-funktionens Lookup_value-argument.

   

   I det sidste trin af selvstudiet vil Lookup_values blive indtastet i cellerne D3 og E3 i regnearket.

Fuldfør funktionen Nested MATCH

Dette trin dækker tilføjelse af Lookup_array-argumentet for den indlejrede MATCH-funktion. Lookup_arrayet er det celleområde, som MATCH-funktionen søger for at finde argumentet Lookup_value, der er tilføjet i det foregående trin i selvstudiet.

Fordi to søgefelter blev identificeret i Lookup_array-argumentet, skal det samme gøres for Lookup_array. MATCH-funktionen søger kun i et array for hvert angivet udtryk. For at indtaste flere arrays skal du bruge og-tegnet til at sammenkæde arrays.

1. Placer markøren i slutningen af dataene i tekstboksen Row_num. Markøren vises efter kommaet i slutningen af den aktuelle post.
2. Fremhæv celler D6 til D11 i regnearket for at indtaste området. Dette område er det første array, som funktionen søger.
3. Indtast & (et og-tegn) efter cellereferencerne D6:D11. Dette symbol får funktionen til at søge i to arrays.
4. Fremhæv celler E6 til E11 i regnearket for at indtaste området. Dette område er det andet array, som funktionen søger.

5. Indtast , (et komma) efter cellereferencen E3 for at fuldføre indtastningen af MATCH-funktionens Lookup_array-argument.

   

6. Lad dialogboksen være åben for det næste trin i selvstudiet.

Tilføj MATCH Type-argumentet

Det tredje og sidste argument i MATCH-funktionen er Match_type-argumentet. Dette argument fortæller Excel, hvordan man matcher Lookup_value med værdier i Lookup_array. De tilgængelige valg er 1, 0 eller -1.

Dette argument er valgfrit. Hvis den udelades, bruger funktionen standardværdien 1.

• Hvis Match_type=1 eller udelades, finder MATCH den største værdi, der er mindre end eller lig med Lookup_value. Lookup_array-dataene skal sorteres i stigende rækkefølge.
• Hvis Match_type=0, finder MATCH den første værdi, der er lig med Lookup_value. Lookup_array-dataene kan sorteres i vilkårlig rækkefølge.
• Hvis Match_type=-1, finder MATCH den mindste værdi, der er større end eller lig med Lookup_value. Lookup_array-dataene skal sorteres i faldende rækkefølge.

Indtast disse trin efter kommaet, der blev indtastet i det foregående trin på Row_num-linjen i INDEX-funktionen:

1. Indtast 0 (et nul) efter kommaet i tekstfeltet Row_num. Dette tal får den indlejrede funktion til at returnere eksakte overensstemmelser til de termer, der er indtastet i cellerne D3 og E3.

2. Indtast ) (en afsluttende runde parentes) for at fuldføre MATCH-funktionen.

   

3. Lad dialogboksen være åben for det næste trin i selvstudiet.

Afslut INDEX-funktionen

MATCH-funktionen er færdig. Det er tid til at flytte til tekstboksen Kolonne_nummer i dialogboksen og indtaste det sidste argument for INDEX-funktionen. Dette argument fortæller Excel, at kolonnenummeret er i området D6 til F11. Dette interval er det sted, hvor den finder informationen returneret af funktionen. I dette tilfælde en leverandør til titanium-widgets.

1. Placer markøren i tekstfeltet Column_num.

2. Indtast 3 (tallet tre). Dette tal fortæller formlen at søge efter data i den tredje kolonne i området D6 til F11.

   

3. Lad dialogboksen være åben for det næste trin i selvstudiet.

Create the Array Formula

Før du lukker dialogboksen, skal du omdanne den indlejrede funktion til en matrixformel. Dette array tillader funktionen at søge efter flere termer i datatabellen. I dette selvstudie er to udtryk matchet: Widgets fra kolonne 1 og Titanium fra kolonne 2.

For at oprette en matrixformel i Excel skal du trykke på CTRL, SHIFT og ENTERnøgler samtidigt. Når der er trykket på funktionen, er funktionen omgivet af krøllede seler, hvilket indikerer, at funktionen nu er et array.

1. Vælg OK for at lukke dialogboksen. I Excel til Mac skal du vælge Done.
2. Vælg celle F3 for at se formlen, og placer derefter markøren i slutningen af formlen i formellinjen.
3. For at konvertere formlen til en matrix skal du trykke på CTRL+ SHIFT+ ENTER.
4. A N/A fejl vises i celle F3. Dette er cellen, hvor funktionen blev indtastet.

5. N/A-fejlen vises i celle F3, fordi celler D3 og E3 er tomme. D3 og E3 er de celler, hvor funktionen søger at finde Lookup_value. Efter data er føjet til disse to celler, erstattes fejlen af information fra databasen.

   

Tilføj søgekriterierne

Det sidste trin er at tilføje søgetermerne til regnearket. Dette trin matcher termerne Widgets fra kolonne 1 og Titanium fra kolonne 2.

Hvis formlen finder en match for begge led i de relevante kolonner i databasen, returnerer den værdien fra den tredje kolonne.

1. Vælg celle D3.
2. Enter Widgets.
3. Vælg celle E3.
4. Indtast Titanium, og tryk på Enter.
5. Leverandørens navn, Widgets Inc., vises i celle F3. Dette er den eneste leverandør på listen, der sælger Titanium Widgets.

6. Vælg celle F3. Funktionen vises i formellinjen over regnearket.

   {=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
   I dette eksempel er der kun én leverandør til titanium-widgets. Hvis der havde været mere end én leverandør, returneres den leverandør, der står først i databasen, af funktionen.