Obecný vzorec
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
souhrn
Chcete-li sečíst hodnoty v odpovídajících sloupcích a řádcích, můžete použít funkci SUMPRODUCT. V zobrazeném příkladu je vzorec v J6:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
kde data (C5: G14), dny (B5: B14) a kódy (C4: G4) jsou pojmenovány rozsahy.
Vysvětlení
Funkce SUMPRODUCT dokáže nativně zpracovat pole, aniž by bylo nutné zadávat řízení.
V tomto případě vynásobíme všechny hodnoty v pojmenovaném rozsahu dat dvěma výrazy, které odfiltrují hodnoty, které nás nezajímají. První výraz použije filtr založený na kódech:
(codes=J4)
Protože J4 obsahuje „A002“, výraz vytvoří pole TRUE FALSE hodnot takto:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Druhý filtr filtrů dne:
(days=J5)
Vzhledem k tomu, že J4 obsahuje „St“, vytvoří výraz řadu TRUE FALSE hodnot, jako je tato:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
V aplikaci Excel jsou TRUE FALSE hodnoty automaticky vynuceny na hodnoty 1 a 0 libovolnou matematickou operací, takže operace násobení vynuluje výše uvedená pole na jedničky a nuly a vytvoří 2D pole se stejnými rozměry jako původní data. Proces lze vizualizovat, jak je znázorněno níže:
Nakonec SUMPRODUCT vrátí součet všech prvků v konečném poli 9.
Počítat místo součtu
Pokud chcete místo sčítání počítat odpovídající hodnoty, můžete vzorec zkrátit na:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Tento počet bude zahrnovat prázdné buňky.
Poznámky
- Ačkoli příklad ukazuje pouze jeden shodný sloupec, tento vzorec správně sečte více shodných sloupců.
- Pokud potřebujete porovnávat pouze sloupce (ne řádky), můžete použít vzorec jako je tento.
- Chcete-li odpovídat pouze řádkům, můžete použít funkci COUNTIFS.
