Ved at bruge en matrixformel i Excel kan vi oprette en opsummeringsformel, der bruger flere kriterier til at finde oplysninger i en database eller en tabel med data. Opsætningsformlen indebærer at nest MATCH-funktionen inde i INDEX-funktionen.
Denne vejledning indeholder et trin for trin eksempel på at oprette en opslagskode, der bruger flere kriterier for at finde en leverandør aftitanium widgets i en prøve database. Følg med for at lære, hvor nemt det er at oprette dine egne brugerdefinerede opslagsformler.
Forbered din Excel-arbejdsbog med data
For at følge trinene i denne vejledning skal du begynde med at indtaste prøvedataene i følgende celler, som vist på billedet ovenfor. Række 3 og 4 er efterladt tomme for at rumme den matrixformel, der er oprettet under denne vejledning.
- Indtast det øverste område af data i celler D1 til F2
- Indtast det andet område i celler D5 til F11
Selvstudiet indeholder ikke formateringen set i billedet, men dette påvirker ikke, hvordan opslagsmetoden fungerer. Oplysninger om formateringsindstillinger svarende til ovenstående er tilgængelige i denne grundlæggende Excel Formatering Tutorial.
02 af 08Opret en INDEX-funktion i Excel
Det INDEKS funktion er et af de få i Excel, der har flere former. Funktionen har en Array Form og a Reference Formular. Array-formularen returnerer de faktiske data fra en database eller en tabel med data, mens referenceskemaet giver dig cellehenvisningen eller placeringen af dataene i tabellen.
I denne vejledning bruger vi Array-formularen, da vi ønsker at kende leverandørens navn til titanium widgets frem for cellehenvisningen til denne leverandør i vores database. Følg disse trin for at oprette din INDEX-funktion:
- Klik på cellen F3 for at gøre det til den aktive celle - det er her, vi vil indtaste den indlejrede funktion.
- Klik på formler fanebladet på båndmenuen.
- Vælge Opslag og reference fra båndstangen for at åbne funktionen, fald ned.
- Klik på INDEKS i listen for at hente den Vælg Argumenter dialog boks.
- Vælg array, row_num, col_num mulighed i dialogboksen.
- Klik Okay for at åbne dialogboksen INDEX-funktion Dette åbner Formula Builder i Excel.
- I Formelbygger, klik på Array linje.
- Fremhæv celler D6 til F11ved at klikke og trække i regnearket for at indtaste området i bygherren.
I ældre versioner af Excel den Formelbygger vil blive erstattet af Funktionsargumenter vindue. Anvend de samme trin som nævnt i denne vejledning i vinduet.
03 af 08Start den Nested MATCH-funktion
Når man nester en funktion i en anden, er det ikke muligt at åbne den anden eller nestede funktionens formelbygger o Indtast de nødvendige argumenter. Den indlejrede funktion skal indtastes som en af den sorterede ind i den første funktion.
Når man indtaster funktioner manuelt, skilles funktionens argumenter fra hinanden med et komma.
Det første trin i at indtaste den indlejrede MATCH-funktion er at indtaste opslagsværdi argument. Lookup_value er placeringen eller cellehenvisningen for det søgeord, vi ønsker at matche i databasen.
Normalt accepterer Lookup_value kun ét søgekriterium eller term. For at søge efter flere kriterier skal vi udvide Lookup_value; thans er gjort ved at sammenkæde eller tilslutte to eller flere cellehenvisninger sammen med ampersand symbolet - &.
- I Formelbygger, klik på ROW_NUM linje.
- Indtast funktionsnavnet MATCH efterfulgt af en åben rund beslag.
- Klik på cellen D3 at indtaste den cellehenvisning i dialogboksen.
- Skriv en ampersand efter cellehenvisningen D3 for at tilføje en anden cellehenvisning.
- Klik på cellen E3 at indtaste denne anden celle reference.
- Skriv et komma efter cellehenvisningen E3 for at afslutte indtastningen af MATCH-funktionens opslagsværdi argument.
I det sidste trin i tutorialen, vores Lookup_values vil blive indtastet i cellerne D3 og E3 i regnearket.
04 af 08Udfør den indlejrede MATCH-funktion
Dette trin dækker tilføjelsen af opslagsmatrixen argument for den indlejrede MATCH fungere. Lookup_array er det udvalg af celler, som MATCH-funktionen vil søge for at finde det Lookup_value-argument, der blev tilføjet i det foregående trin i vejledningen.
Da vi har identificeret to søgefelter i Lookup_array-argumentet, skal vi gøre det samme for Lookup_array. MATCH-funktionen søger kun ét array for hvert angivet term, således at du indtaster flere arrayer, vi igen bruger ampersand til at sammenkoble arraysne sammen.
Disse trin skal indtastes efter kommaet, der er indtastet i det foregående trin på ROW_NUM linje i INDEKS fungere.
- Klik på ROW_NUM linje efter kommaet for at placere indsættelsespunktet i slutningen af den aktuelle indgang.
- Fremhæv celler D6 til D11 i regnearket for at komme ind i området - dette er det første array, funktionen er at søge.
- Skriv en ampersand efter cellehenvisningerne D6: D11 fordi vi vil have funktionen til at søge to arrays.
- Fremhæv celler E6 til E11 i regnearket for at komme ind i området - dette er det andet array, som funktionen skal søge.
- Skriv et komma efter cellehenvisningen E3 for at fuldføre indgangen til MATCH funktionens opslagsmatrixen argument.
- Forlad INDEKS funktionsdialogboksen åbnes til næste trin i vejledningen.
Tilføj MATCH Type Argument
Det tredje og sidste argument i MATCH-funktionen er MATCH_TYPE argument; det fortæller Excel hvordan man kan matche opslagsværdi 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 på 1.
- Hvis Match_type = 1 eller udelades: MATCH finder den største værdi, der er mindre end eller lig med Lookup_value. Lookup_array data skal sorteres i stigende rækkefølge.
- Hvis Match_type = 0: MATCH finder den første værdi, der svarer til Lookup_value. Lookup_array-data kan sorteres i en hvilken som helst rækkefølge.
- Hvis Match_type = -1: MATCH finder den mindste værdi, der er større end eller lig med Lookup_value. Lookup_array data skal sorteres i faldende rækkefølge.
Disse trin skal indtastes efter kommaet, der er indtastet i det foregående trin på ROW_NUM linje i INDEKS fungere.
- Følgende komma på ROW_NUM linje, skriv en nul - 0 - da vi ønsker, at den nestede funktion skal returnere nøjagtige matches til de vilkår, vi indtaster i celler D3 og E3.
- Indtast en lukkede runde beslag - ) - at fuldføre MATCH fungere.
- Forlad Formelbygger Åben til næste trin i vejledningen.
Afslutning af INDEX-funktionen
Nu da MATCH funktionen er færdig, vil vi flytte til tredje linje i Formula Builder og indtaste det sidste argument for INDEKS fungere. Dette tredje og sidste argument er COLUMN_NUM argument, der fortæller Excel kolonne nummer i området D6 til F11; Det er her, hvor den finder de oplysninger, vi ønsker at blive returneret af funktionen. I dette tilfælde, en leverandør for titanium widgets.
- Klik på COLUMN_NUM linje.
- Indtast nummer tre - 3 - på denne linje, da vi leder efter data i tredje kolonne af sortimentet D6 til F11.
- Igen forlader Formelbygger Åben til næste trin i vejledningen.
Oprettelse af array-formlen
Inden du lukker Formelbygger, vi skal vende vores indlejrede funktion til en matrixformel; Dette gør det muligt for vores funktion at søge efter flere udtryk i datatabellen. I denne øvelse søger vi at matche to vilkår: Widgets fra kolonne 1 og Titanium fra kolonne 2.
Oprettelse af en matrixformel i Excel sker ved at trykke på CTRL, FLYTTE, og GÅ IND taster på tastaturet på samme tid. Når en gang er trykket, vil funktionen blive omgivet af krøllede seler, hvilket indikerer, at funktionen nu er en matrix.
- Luk Formelbygger ved at klikke på Færdig knap.
- Vælg derefter celle F4 og tryk på Gå ind nøgle for at se formlen.
- For at konvertere formlen til en matrix, tryk samtidigt CTRL + SKIFT + ENTER på dit tastatur.
- Hvis det gøres korrekt a # N / A fejl vises i celle F3 - cellen hvor vi trådte ind i funktionen.
- Det # N / A Fejl vises i celle F3 fordi celler D3 og E3 er tomme. D3 og E3 er de celler, hvor vi fortalte funktionen at finde Lookup_values. Når data er tilføjet til disse to celler, vil fejlen blive erstattet af oplysninger fra databasen.
Tilføjelse af søgekriterierne
Endelig vil vi tilføje søgeordene til vores regneark. Som nævnt i det foregående trin søger vi at matche vilkårene Widgets fra kolonne 1 og Titanium fra kolonne 2. Hvis og kun hvis vores formel finder en match for begge udtryk i de relevante kolonner i databasen, vil den returnere værdien fra den tredje kolonne.
- Klik på cellen D3.
- Type Widgets og tryk på Gå ind tast på tastaturet.
- Klik på cellen E3.
- Type Titanium og tryk på Gå ind tast på tastaturet.
- Leverandørens navn Widgets Inc. skal vises i celle F3 - placeringen af funktionen, da den er den eneste leverandør, der er opført, der sælger titanium widgets.
- Når du klikker på celle F3, vises den komplette funktion i formellelinjen over regnearket.
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
I vores eksempel var der kun en leverandør til titanium widgets. Hvis der var mere end en leverandør, returneres den leverandør, der er opført først i databasen, af funktionen.
