detta är en mer avancerad formel. För grunderna, se hur du använder INDEX och MATCH.
normalt är en INDEXMATCHNINGSFORMEL konfigurerad med MATCHUPPSÄTTNING för att titta igenom ett kolumnområde och tillhandahålla en matchning baserat på givna kriterier. Utan att sammanfoga värden i en hjälparkolumn, eller i själva formeln, finns det inget sätt att leverera mer än ett kriterier.,
denna formel fungerar kring denna begränsning genom att använda boolesk logik för att skapa en rad av ettor och nollor för att representera rader som matchar alla 3 kriterier och sedan använda MATCH för att matcha den första 1 hittades. Den tillfälliga uppsättningen av ettor och nollor genereras med detta utdrag:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)här jämför vi objektet i H5 mot alla objekt, storleken i H6 mot alla storlekar och färgen i H7 mot alla färger. Det ursprungliga resultatet är tre matriser av sant / falskt resultat så här:
{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}*{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}*{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}Tips: Använd F9 för att se dessa resultat., Välj bara ett uttryck i formelfältet och tryck på F9.
matematikoperationen (multiplikation) omvandlar de sanna falska värdena till 1s och 0s:
{1;1;1;0;0;0;1}*{0;0;1;0;0;1;0}*{1;0;1;0;0;0;1}efter multiplikation har vi en enda matris så här:
{0;0;1;0;0;0;0}som matas in i MATCHFUNKTIONEN som uppslagningsmatrisen, med ett uppslagsvärde på 1:
MATCH(1,{0;0;1;0;0;0;0})vid denna tidpunkt är formeln en standardindexmatchningsformel., Matchningsfunktionen returnerar 3 till INDEX:
=INDEX(E5:E11,3)och INDEX returnerar ett slutresultat på $17.00.
array visualization
arrayerna som förklaras ovan kan vara svåra att visualisera. Bilden nedan visar grundtanken. Kolumnerna B, C och D motsvarar data i exemplet. Kolumn F skapas genom att multiplicera de tre kolumnerna tillsammans. Det är matrisen överlämnas för att matcha.,
icke-array version
det är möjligt att lägga till ett annat INDEX till denna formel, för att undvika att behöva ange som en array formel med control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))INDEXFUNKTIONEN kan hantera arrays natively, så det andra indexet läggs bara till ”catch” the array skapad med den booleska logikoperationen och returnera samma array igen för att matcha. För att göra detta konfigureras INDEX med nollrader och en kolumn. Nollradstricket gör att INDEX returnerar kolumn 1 från matrisen (som redan är en kolumn ändå).,
Varför vill du ha icke-array-versionen? Ibland glömmer folk att ange en matrisformel med control + shift + enter, och formeln returnerar ett felaktigt resultat. Så, en icke-array formel är mer ”skottsäker”. Tradeoff är dock en mer komplex formel.
Obs! i Excel 365 är det inte nödvändigt att ange matrisformler på ett speciellt sätt.