Frisk information

Informationsplatform viser de seneste nyheder

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

Selvstudiedata til opslagsfunktionen med flere kriterier i Excel

Hvad skal man vide

  • Opret først en INDEX-funktion, og start derefter den indlejrede MATCH-funktion ved at indtaste Lookup_value-argumentet.
  • Tilføj derefter argumentet Lookup_array efterfulgt af Match_type argument, angiv derefter kolonneområdet.
  • Gør derefter den indlejrede funktion til en matrixformel ved at trykke på Ctrl+Flytte+Gå ind. Tilføj endelig søgetermerne til arbejdsarket.

Denne artikel forklarer, hvordan du 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 vejledningen

For at følge trinene i denne vejledning skal du indtaste prøvedataene i 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 den formatering, 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ælge Opslag og reference for at åbne rullelisten med funktioner.

  4. Vælg INDEKS at åbne Vælg Argumenter dialog boks.

  5. Vælge matrix, række_nummer, kolonnenummer.

  6. Vælg Okay at åbne Funktionsargumenter dialog boks. I Excel til Mac åbnes Formula Builder.

  7. Placer markøren i Array tekstboks.

  8. Fremhæv celler D6 igennem 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 funktioner indtastes manuelt, adskilles funktionens argumenter fra hinanden med et komma.

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

Opslagsværdien accepterer kun ét søgekriterium eller et udtryk. For at søge efter flere kriterier skal du udvide Lookup_value ved at sammenkæde, eller sammenføje, to eller flere cellereferencer ved at bruge og-tegnet (&).

  1. I den Funktionsargumenter dialogboksen, placer markøren i Række_nummer tekstboks.

  2. Gå ind MATCH(.

  3. Vælg celle D3 for at indtaste den cellereference i dialogboksen.

  4. Gå ind & (et-tegnet) efter cellereferencen D3 for at tilføje en anden cellereference.

  5. Vælg celle E3 for at indtaste den anden cellereference.

  6. Gå ind ,(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 argumentet Lookup_array for den indlejrede MATCH-funktion. Lookup_array er det celleområde, som MATCH-funktionen søger for at finde argumentet Lookup_value, der er tilføjet i det forrige trin af 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 Række_nummer tekstboks. Markøren vises efter kommaet i slutningen af ​​den aktuelle post.

  2. Fremhæv celler D6 igennem D11 i arbejdsarket for at indtaste intervallet. Dette interval er det første array, som funktionen søger.

  3. Gå ind & (et og-tegn) efter cellereferencerne D6: D11. Dette symbol får funktionen til at søge i to arrays.

  4. Fremhæv celler E6 igennem E11 i arbejdsarket for at indtaste intervallet. Dette interval er det andet array, som funktionen søger.

  5. Gå ind ,(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 argument. 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 enhver 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. Gå ind 0 (et nul) efter kommaet i Række_nummer tekstboks. Dette tal får den indlejrede funktion til at returnere eksakte matches til de termer, der er indtastet i cellerne D3 og E3.

  2. Gå ind ) (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 udført. 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 af titanium widgets.

  1. Placer markøren i Kolonne_nummer tekstboks.

  2. Gå ind 3 (tallet tre). Dette tal fortæller formlen at lede 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.

Opret matrixformlen

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 denne øvelse matches to udtryk: Widgets fra kolonne 1 og Titanium fra kolonne 2.

For at oprette en matrixformel i Excel skal du trykke på CTRL,FLYTTE,og GÅ IND nøgler samtidigt. Når du har trykket på den, er funktionen omgivet af krøllede seler, hvilket indikerer, at funktionen nu er et array.

  1. Vælg Okay for at lukke dialogboksen. I Excel til Mac skal du vælge Færdig.

  2. Vælg celle F3 for at se formlen skal du placere markøren i slutningen af ​​formlen i formellinjen.

  3. For at konvertere formlen til en matrix skal du trykke på CTRL+FLYTTE+GÅ IND.

  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 for at finde Lookup_value. Efter at data er tilfø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 arbejdsarket. Dette trin matcher vilkårene Widgets fra kolonne 1 og Titanium fra kolonne 2.

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

  1. Vælg celle D3.

  2. Gå ind Widgets.

  3. Vælg celle E3.

  4. Type Titaniumog tryk Gå ind.

  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.