Vzorec aplikace Excel: Vyhledávání a transpozice podle více kritérií -

Obsah

Obecný vzorec

(=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0)))

souhrn

Chcete-li provést vyhledávání podle více kritérií a transponovat výsledky do tabulky, můžete použít maticový vzorec založený na INDEXU a MATCH. V zobrazeném příkladu je vzorec v G5:

(=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0)))

Všimněte si, že tento vzorec je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.

Tento vzorec také používá tři pojmenované rozsahy: umístění = B5: B13, částka = D5: D13, datum = C5: C13

Vysvětlení

Jádrem tohoto vzorce je INDEX, který načítá hodnotu z pojmenovaného rozsahu „amount“ (B5: B13):

=INDEX(amount,row_num)

kde row_num je zpracováno s funkcí MATCH a logickou logikou:

MATCH(1,($F5=location)*(G$4=date),0)

V tomto fragmentu je umístění ve F5 porovnáno se všemi umístěními a datum v G4 je porovnáno se všemi daty. Výsledkem je v každém případě pole TRUE a FALSE hodnot. Když se tato pole násobí společně, matematická operace vynutí hodnoty TRUE a FALSE na něčí a nuly, takže vyhledávací pole vstupující do MATCH vypadá takto:

(1;0;0;0;0;0;0;0;0)

MATCH je nastaven tak, aby odpovídal 1 jako přesná shoda, a vrací pozici na INDEX jako číslo řádku. Číslo 1 funguje pro vyhledávací hodnotu, protože pole nyní obsahuje pouze 1 a 0, jak je uvedeno výše.

F5 a G4 se zadávají jako smíšené odkazy, takže vzorec lze kopírovat přes tabulku bez úprav.