Ved å bruke en matriseformel i Excel kan vi opprette en oppslagsformel som bruker flere kriterier for å finne informasjon i en database eller et datatabell. Matriseformelen innebærer å nest MATCH-funksjonen inne i INDEX-funksjonen.
Denne opplæringen inneholder et trinnvis eksempel på å opprette en oppslagsformel som bruker flere kriterier for å finne en leverandør avtitan widgets i en prøve database. Følg med for å lære hvor enkelt det er å lage dine egne tilpassede oppslagskomponenter.
Klargjør Excel-arbeidsboken din med data
For å følge trinnene i denne opplæringen må du begynne med å skrive inn eksempeldataene i følgende celler, som vist i bildet ovenfor. Rader 3 og 4 er tomt for å imøtekomme matriseformelen opprettet under denne opplæringen.
- Skriv inn det øverste spekteret av data i celler D1 til F2
- Skriv inn det andre området i celler D5 til F11
Opplæringen inneholder ikke formateringen sett i bildet, men dette påvirker ikke hvordan oppslagsmetoden fungerer. Informasjon om formateringsalternativer som ligner på de som er sett over, er tilgjengelige i denne grunnleggende Excel-formateringsveiledningen.
02 av 08Opprett en INDEX-funksjon i Excel
De INDEX funksjon er en av de få i Excel som har flere former. Funksjonen har en Array Form og a Referanseskjema. Array-skjemaet returnerer de faktiske dataene fra en database eller en tabell med data, mens referanseskjemaet gir deg referansen til cellen eller plasseringen av dataene i tabellen.
I denne opplæringen bruker vi Array-skjemaet siden vi vil vite leverandørens navn for titan widgets i stedet for cellehenvisningen til denne leverandøren i vår database. Følg disse trinnene for å opprette INDEX-funksjonen:
- Klikk på cellen F3 for å gjøre det til den aktive cellen - dette er hvor vi skal legge inn den nestede funksjonen.
- Klikk på formler fanen på båndmenyen.
- Velge Oppslag og referanse fra båndstangen for å åpne funksjonen ned.
- Klikk på INDEX i listen for å hente opp Velg Argumenter dialogboks.
- Velg array, row_num, col_num alternativet i dialogboksen.
- Klikk OK for å åpne INDEX-funksjonen dialogboksen; Dette vil åpne Formula Builder i Excel.
- I Formelbygger, Klikk på Array linje.
- Fremhev celler D6 til F11, ved å klikke og dra, i regnearket for å legge inn rekkevidden i byggeren.
I eldre versjoner av Excel Formelbygger vil bli erstattet av Funksjonsargumenter vindu. Påfør samme trinn som nevnt i denne opplæringen i vinduet.
03 av 08Start Nested MATCH-funksjonen
Når du hekker en funksjon i en annen, er det ikke mulig å åpne den andre eller nestede funksjonens formelbygger o Legg inn de nødvendige argumentene. Den nestede funksjonen må skrives inn som en av de sorterte innene i den første funksjonen.
Når du skriver inn funksjoner manuelt, skilles funksjonens argumenter fra hverandre med et komma.
Det første trinnet i å skrive inn den nestede MATCH-funksjonen er å skrive inn søkeverdi argument. Lookup_value vil være plasseringen eller cellehenvisningen for søkeordet vi ønsker å matche i databasen.
Normalt aksepterer Lookup_value bare ett søkekriterium eller en term. For å søke etter flere kriterier, må vi utvide Lookup_value; thans er gjort ved å sammenkoble eller bli med to eller flere cellehenvisninger sammen med ampersand-symbolet - &.
- I Formelbygger, Klikk på radnummer linje.
- Skriv inn funksjonsnavnet KAMP etterfulgt av en åpen rund brakett.
- Klikk på cellen D3 å angi den cellehenvisningen i dialogboksen.
- Skriv en ampersand etter cellehenvisningen D3 for å legge til en andre cellereferanse.
- Klikk på cellen E3 å angi denne andre cellehenvisningen.
- Skriv inn et komma etter cellehenvisningen E3 for å fullføre oppføringen til MATCH-funksjonen søkeverdi argument.
I det siste trinnet i opplæringen, vår Lookup_values vil bli skrevet inn i cellene D3 og E3 i regnearket.
04 av 08Fullfør Nested MATCH-funksjonen
Dette trinnet dekker å legge til søkematrise argument for det nestede KAMP funksjon. Lookup_array er rekkevidde av celler som MATCH-funksjonen vil søke for å finne Lookup_value-argumentet som ble lagt til i forrige trinn i opplæringen.
Siden vi har identifisert to søkefelt i Lookup_array-argumentet, må vi gjøre det samme for Lookup_array. MATCH-funksjonen søker bare ett array for hvert spesifisert term, og dermed å skrive inn flere arrayer, bruker vi igjen ampersand til å sammenkoble arrayene sammen.
Disse trinnene skal oppgis etter kommaene som er angitt i forrige trinn på radnummer linje i INDEX funksjon.
- Klikk på radnummer linje etter kommaet for å plassere innsatspunktet på slutten av gjeldende oppføring.
- Fremhev celler D6 til D11 i regnearket for å gå inn i området - dette er det første feltet som funksjonen er å søke.
- Skriv en ampersand etter cellehenvisninger D6: D11 fordi vi vil at funksjonen skal søke i to arrays.
- Fremhev celler E6 til E11 i regnearket for å gå inn i området - dette er det andre valget funksjonen er å søke.
- Skriv inn et komma etter cellehenvisningen E3 for å fullføre oppføringen til KAMP funksjons søkematrise argument.
- Forlate INDEX funksjonsdialogboksen åpnes for neste trinn i opplæringen.
Legg til MATCH Type Argument
Det tredje og siste argumentet i MATCH-funksjonen er match_type argument; det forteller Excel hvordan du skal matche søkeverdi med verdier i Lookup_array - de tilgjengelige valgene er 1, 0 eller -1.
Dette argumentet er valgfritt. Hvis den utelates, bruker funksjonen standardverdien på 1.
- Hvis Match_type = 1 eller utelates: MATCH finner 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: MATCH finner den første verdien som er nøyaktig lik Lookup_value. Lookup_array-dataene kan sorteres i hvilken som helst rekkefølge.
- Hvis Match_type = -1: MATCH finner den minste verdien som er større enn eller lik Lookup_value. Lookup_array-dataene må sorteres i synkende rekkefølge.
Disse trinnene skal oppgis etter kommaene som er angitt i forrige trinn på radnummer linje i INDEX funksjon.
- Etter kommaen på radnummer linje, skriv en null - 0 - siden vi vil at den nestede funksjonen skal returnere nøyaktige kamper til de vilkårene vi skrev inn i cellene D3 og E3.
- Skriv inn en lukket rundbrakett - ) - å fullføre KAMP funksjon.
- Forlate Formelbygger åpne for neste trinn i opplæringen.
Ferdiggjør INDEX-funksjonen
Nå da KAMP funksjonen er ferdig vil vi flytte til den tredje linjen i Formula Builder og angi det siste argumentet for INDEX funksjon. Dette tredje og siste argumentet er kolonne argument som forteller Excel kolonnenummeret i serien D6 til F11; Her finner du informasjonen vi vil returnere av funksjonen. I dette tilfellet, en leverandør for titan widgets.
- Klikk på kolonne linje.
- Skriv inn nummer tre - 3 - på denne linjen siden vi ser etter data i den tredje kolonnen i serien D6 til F11.
- Igjen, la Formelbygger åpne for neste trinn i opplæringen.
Opprette Array Formula
Før du lukker Formelbygger, vi må slå vår nestede funksjon i en matriseformel; Dette gjør det mulig for vår funksjon å søke etter flere vilkår i datatabellen. I denne opplæringen ser vi for å matche to betingelser: widgets fra kolonne 1 og Titanium fra kolonne 2.
Å lage en matriseformel i Excel gjøres ved å trykke på CTRL, SKIFTE, og TAST INN tastene på tastaturet samtidig. Når en gang er trykket, blir funksjonen omgitt av krøllete braces, noe som indikerer at funksjonen nå er en matrise.
- Lukk Formelbygger ved å klikke på Ferdig knapp.
- Deretter velger du celle F4 og trykk på Tast inn nøkkel for å vise formelen.
- For å konvertere Formelen til en matrise, trykk samtidig CTRL + SKIFT + ENTER på tastaturet ditt.
- Hvis gjort riktig a # N / A Feil vil vises i celle F3 - cellen hvor vi kom inn i funksjonen.
- De # N / A Feil vises i cellen F3 fordi celler D3 og E3 er tomme. D3 og E3 er cellene der vi fortalte funksjonen for å finne Lookup_values. Når data er lagt til i disse to cellene, vil feilen bli erstattet av informasjon fra databasen.
Legge til søkekriteriene
Til slutt vil vi legge til søkeordene i vårt regneark. Som nevnt i det forrige trinnet, ser vi etter å matche vilkårene widgets fra kolonne 1 og Titanium fra kolonne 2. Hvis, og bare hvis vår formel finner en kamp for begge vilkårene i de aktuelle kolonnene i databasen, vil den returnere verdien fra den tredje kolonnen.
- Klikk på cellen D3.
- Type widgets og trykk på Tast inn tast på tastaturet.
- Klikk på cellen E3.
- Type Titanium og trykk på Tast inn tast på tastaturet.
- Leverandørens navn Widgets Inc. skal vises i celle F3 - plasseringen av funksjonen siden det er den eneste leverandøren som listes som selger Titanium Widgets.
- Når du klikker på celle F3, vises den komplette funksjonen i formellinjen over regnearket.
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
I vårt eksempel var det bare én leverandør for titan widgets. Hvis det hadde mer enn én leverandør, blir leverandøren som er oppført først i databasen, returnert av funksjonen.
