souhrn
Chcete-li přeložit textové hodnoty na čísla a sečíst výsledek, můžete použít vzorec INDEX a MATCH a funkci SUMA. V zobrazeném příkladu je vzorec v H5:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
kde „kód“ je pojmenovaný rozsah K5: K9 a „hodnota“ je pojmenovaný rozsah L5: L9.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Srdcem tohoto vzorce je základní vzorec INDEX a MATCH, který se používá k překladu textových hodnot do čísel definovaných ve vyhledávací tabulce. Například k překladu výrazu „EX“ na odpovídající číslo použijeme:
=INDEX(value,MATCH("EX",code,0))
který by se vrátil 4.
Zvrat v tomto problému však spočívá v tom, že chceme přeložit a sečíst rozsah textových hodnot ve sloupcích C až G na čísla. To znamená, že musíme poskytnout více než jednu vyhledávací hodnotu a potřebujeme INDEX, abychom vrátili více než jeden výsledek. Standardní přístup je vzorec jako tento:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Po spuštění MATCH máme pole s 5 položkami:
=SUM(INDEX(value,(2,2,3,2,5)))
Zdá se tedy, že INDEX by měl SUMU vrátit 5 výsledků. Pokud to však zkusíte, funkce INDEX vrátí pouze jeden součet výsledků. Chcete-li, aby INDEX vrátil více výsledků, musíme použít poměrně nejasný trik a zabalit MATCH do N a IF takto:
N(IF(1,MATCH(C5:G5,code,0)))
To účinně donutí INDEX poskytnout funkci SUM více než jednu hodnotu. Po spuštění INDEXu máme:
=SUM((3,3,2,3,-1))
A funkce SUM vrací součet položek v poli, 10. Chcete-li o tomto chování dobře psát, přečtěte si tento zajímavý článek na webu EXCELXOR.
