Genom att använda en matrisformel i Excel kan vi skapa en uppslagsformel som använder flera kriterier för att hitta information i en databas eller ett databas. Matrisformeln innebär att MATCH-funktionen är inuti INDEX-funktionen.
Denna handledning innehåller ett steg för steg exempel på att skapa en lookup-formel som använder flera kriterier för att hitta en leverantör avtitan widgets i en provdatabas. Följ med för att lära dig hur lätt det är att skapa egna skräddarsydda formulär.
Förbered din Excel-arbetsbok med data
För att följa stegen i den här handledningen måste du börja med att ange provdata i följande celler, som visas i bilden ovan. Rader 3 och 4 lämnas tomma för att rymma den matrisformel som skapats under denna handledning.
- Ange det högsta utbudet av data i celler D1 till F2
- Ange det andra intervallet i celler D5 till F11
Handledningen innehåller inte formateringen som ses i bilden, men det påverkar inte hur uppslagningsformeln fungerar. Information om formateringsalternativ som liknar dem som ses ovan finns i denna grundläggande Excel-formateringstutorial.
02 av 08Skapa en INDEX-funktion i Excel
De INDEX funktionen är en av de få i Excel som har flera former. Funktionen har en Array Form och a Referensform. Array-formuläret returnerar de faktiska data från en databas eller en tabell med data, medan referensformuläret ger dig cellens referens eller plats för data i tabellen.
I denna handledning använder vi Array-formuläret eftersom vi vill veta leverantörens namn för titand widgets snarare än cellreferensen till denna leverantör i vår databas. Följ dessa steg för att skapa din INDEX-funktion:
- Klicka på cellen F3 för att göra det till den aktiva cellen - det är här vi kommer att skriva in den kapslade funktionen.
- Klicka på formler fliken på bandmenyn.
- Välja Uppslag och referens från ribban för att öppna funktionen ned.
- Klicka på INDEX i listan för att hämta upp Välj Argument dialog ruta.
- Välj den array, row_num, col_num alternativet i dialogrutan.
- Klick ok för att öppna dialogrutan INDEX-funktion Detta öppnar Formula Builder i Excel.
- I Formelbyggare, Klicka på Array linje.
- Markera celler D6 till F11, genom att klicka och dra i kalkylbladet för att ange intervallet i byggaren.
I äldre versioner av Excel Formelbyggare kommer att ersättas av Funktionsargument fönster. Applicera samma steg som nämns i denna handledning i fönstret.
03 av 08Starta Nested MATCH-funktionen
När man nestar en funktion inuti en annan är det inte möjligt att öppna den andra eller nestade funktionens formelbyggare o Ange de nödvändiga argumenten. Den kapslade funktionen måste skrivas in som en av den sorterade in i den första funktionen.
När man matar in funktioner manuellt, separeras funktionens argument från varandra med ett kommatecken.
Det första steget att skriva in den nestlade MATCH-funktionen är att ange letauppvärde argument. Lookup_value kommer att vara plats eller cellreferens för söktermen vi vill matcha i databasen.
Normalt accepterar Lookup_value bara ett sökkriterium eller term. För att söka efter flera kriterier måste vi utöka Lookup_value; thans görs genom att sammanfoga eller ansluta två eller flera cellreferenser tillsammans med ampersand-symbolen - &.
- I Formelbyggare, Klicka på rad_num linje.
- Skriv funktionsnamnet MATCH följt av en öppen rund konsol.
- Klicka på cellen D3 att ange den cellreferensen i dialogrutan.
- Skriv en ampersand efter cellreferensen D3 för att lägga till en andra cellreferens.
- Klicka på cellen E3 att ange denna andra cellreferens.
- Skriv ett komma efter cellreferensen E3 för att slutföra posten för MATCH-funktionens letauppvärde argument.
I det sista steget i handledningen, vår Lookup_values kommer att matas in i cellerna D3 och E3 i arbetsbladet.
04 av 08Slutför den Nested MATCH-funktionen
Detta steg omfattar att lägga till uppslagnings argument för den nestade MATCH fungera. Lookup_array är det antal celler som MATCH-funktionen ska söka för att hitta det Lookup_value-argument som lagts till i föregående steg i handledningen.
Eftersom vi har identifierat två sökfält i Lookup_array-argumentet måste vi göra detsamma för Lookup_array. MATCH-funktionen söker bara en array för varje angiven term, vilket innebär att du anger flera arrayer, vi använder igen ampersand för att sammanfoga arraysna tillsammans.
Dessa steg ska anges efter kommatecken som har angetts i föregående steg på rad_num linje i INDEX fungera.
- Klicka på rad_num rad efter kommatecken för att placera infogningspunkten i slutet av den aktuella posten.
- Markera celler D6 till D11 i arbetsbladet för att komma in i intervallet - det här är den första matrisen som funktionen ska söka.
- Skriv en ampersand efter cellreferenserna D6: D11 eftersom vi vill att funktionen ska söka i två arrays.
- Markera celler E6 till E11 i arbetsbladet för att komma in i intervallet - det här är den andra gruppen som funktionen ska söka.
- Skriv ett komma efter cellreferensen E3 för att slutföra posten av MATCH funktionens uppslagnings argument.
- Lämna INDEX Funktionsdialogrutan öppnas för nästa steg i handledningen.
Lägg till MATCH Type Argument
Det tredje och sista argumentet för MATCH-funktionen är match_type argument; det berättar Excel hur man matchar letauppvärde med värden i Lookup_array - tillgängliga val är 1, 0 eller -1.
Detta argument är valfritt. Om den utelämnas använder funktionen standardvärdet på 1.
- Om Match_type = 1 eller utelämnas: MATCH finner det största värdet som är mindre än eller lika med Lookup_value. Lookup_array-data måste sorteras i stigande ordning.
- Om Match_type = 0: MATCH hittar det första värdet som är exakt lika med Lookup_value. Lookup_array-data kan sorteras i vilken ordning som helst.
- Om Match_type = -1: MATCH finner det minsta värdet som är större än eller lika med Lookup_value. Lookup_array-data måste sorteras i fallande ordning.
Dessa steg ska anges efter kommatecken som har angetts i föregående steg på rad_num linje i INDEX fungera.
- Efter kommatecken på rad_num linje, skriv en noll - 0 - eftersom vi vill att den kapslade funktionen ska returnera exakta matchningar till de villkor som vi angav i celler D3 och E3.
- Skriv en stängningsrunda fäst - ) - för att slutföra MATCH fungera.
- Lämna Formelbyggare öppna för nästa steg i handledningen.
Avsluta INDEX-funktionen
Nu när MATCH funktionen är klar kommer vi att flytta till den tredje raden av Formula Builder och ange det sista argumentet för INDEX fungera. Det tredje och sista argumentet är kolumn argument som berättar Excel kolumnnummer i intervallet D6 till F11; Här hittar du den information vi vill returnera av funktionen. I detta fall, en leverantör för titan widgets.
- Klicka på kolumn linje.
- Ange nummer tre - 3 - På den här raden eftersom vi letar efter data i den tredje kolumnen i sortimentet D6 till F11.
- Återgå, lämna Formelbyggare öppna för nästa steg i handledningen.
Skapa Array Formel
Innan du stänger Formelbyggare, vi måste vrida vår kapslade funktion i en matrisformel; Det här gör det möjligt för vår funktion att söka efter flera villkor i datatabellen. I denna handledning ser vi ut att matcha två termer: widgets från kolumn 1 och Titanium från kolumn 2.
Skapa en matrisformel i Excel görs genom att trycka på CTRL, FLYTTA, och STIGA PÅ tangenterna på tangentbordet samtidigt. När en gång har tryckts, kommer funktionen att vara omgiven av lockiga hängslen, vilket indikerar att funktionen nu är en matris.
- Stäng Formelbyggare genom att klicka på Gjort knapp.
- Välj sedan cell F4 och tryck på Stiga på nyckeln för att visa formeln.
- För att konvertera formeln till en array trycker du samtidigt på CTRL + SKIFT + RETUR på ditt tangentbord.
- Om det görs korrekt a # N / A fel kommer att visas i cell F3 - cellen där vi kom in i funktionen.
- De # N / A fel visas i cellen F3 eftersom celler D3 och E3 är tomma. D3 och E3 är cellerna där vi berättade för funktionen att hitta Lookup_values. När data har lagts till i dessa två celler kommer felet att ersättas med information från databasen.
Lägga till sökkriterierna
Slutligen lägger vi till sökvillkoren i vårt arbetsblad. Som nämnts i föregående steg ser vi ut att matcha villkoren widgets från kolumn 1 och Titanium från kolumn 2. Om och endast om vår formel hittar en matchning för båda termerna i de lämpliga kolumnerna i databasen, kommer den att returnera värdet från den tredje kolumnen.
- Klicka på cellen D3.
- Typ widgets och tryck på Stiga på tangent på tangentbordet.
- Klicka på cellen E3.
- Typ Titanium och tryck på Stiga på tangent på tangentbordet.
- Leverantörens namn Widgets Inc. ska visas i cell F3 - Funktionsplatsen eftersom den är den enda leverantören som listar vem säljer Titanium Widgets.
- När du klickar på cellen F3 visas den fullständiga funktionen i formulärfältet ovanför arbetsbladet.
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
I vårt exempel var det bara en leverantör för titand widgets. Om det fanns mer än en leverantör returneras den leverantör som listas först i databasen av funktionen.
