Obecný vzorec
=SUMPRODUCT(--(range1=criteria),range2)
souhrn
Chcete-li součet nebo mezisoučet sloupců na základě kritérií v sousedních sloupcích, můžete použít vzorec založený na funkci SUMPRODUCT. V zobrazeném příkladu je vzorec v J5:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Vysvětlení
Tento vzorec v jádru používá SUMPRODUCT k vynásobení součtových produktů dvou polí: array1 a array2 . První pole, pole1 je nastaveno tak, aby fungovalo jako „filtr“, který umožňuje pouze hodnoty, které splňují kritéria.
Array1 používá rozsah, který začíná na prvním sloupci, který obsahuje hodnoty, které musí splňovat kritéria. Tyto „hodnoty kritérií“ jsou umístěny ve sloupci nalevo od „datových hodnot“ a bezprostředně vedle nich.
Kritéria se používají jako jednoduchý test, který vytvoří pole TRUE a FALSE hodnot:
--($B5:$H5=J$4)
Tento bit vzorce „testuje“ každou hodnotu v prvním poli pomocí dodaných kritérií, poté použije dvojitý zápor (-) k vynucení výsledných hodnot PRAVDA a NEPRAVDA na 1 a 0. Výsledek vypadá takto:
(1,0,0,0,1,0,1)
Všimněte si, že 1 odpovídá sloupcům 1,5 a 7, které splňují kritéria „A“.
Pro pole2 uvnitř SUMPRODUCT používáme rozsah, který je „posunut“ o jeden sloupec doprava. Tento rozsah začíná prvním sloupcem obsahujícím hodnoty součtu a končí posledním sloupcem, který obsahuje hodnoty součtu.
Takže v ukázkovém vzorci v J5, po naplnění polí, máme:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Vzhledem k tomu, že SUMPRODUCT je naprogramován speciálně tak, aby ignoroval chyby, které jsou výsledkem vynásobení textových hodnot, vypadá výsledné pole takto:
(1,0,0,0,1,0,1)
Jediné hodnoty, které „přežijí“ násobení, jsou ty, které odpovídají 1 s uvnitř pole1 . Můžete myslet na logiku v poli1 "filtrující" hodnoty v poli2 .
