Formuła ogólna
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Podsumowanie
Aby zsumować wartości w pasujących kolumnach i wierszach, możesz użyć funkcji SUMPRODUCT. W przedstawionym przykładzie formuła w J6 to:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
gdzie dane (C5: G14), dni (B5: B14) i kody (C4: G4) to nazwane zakresy.
Wyjaśnienie
Funkcja SUMPRODUCT może obsługiwać tablice natywnie, bez konieczności wprowadzania klawisza Control Shift.
W tym przypadku mnożymy wszystkie wartości w danych z nazwanego zakresu przez dwa wyrażenia, które odfiltrowują wartości, które nie są interesujące. Pierwsze wyrażenie stosuje filtr oparty na kodach:
(codes=J4)
Ponieważ J4 zawiera „A002”, wyrażenie tworzy tablicę wartości TRUE FALSE w następujący sposób:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Drugie wyrażenie filtruje w dniu:
(days=J5)
Ponieważ J4 zawiera „Wed”, wyrażenie tworzy tablicę wartości TRUE FALSE w następujący sposób:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
W programie Excel wartości PRAWDA FAŁSZ są automatycznie przekształcane do wartości 1 i 0 przez dowolną operację matematyczną, więc operacja mnożenia przekształca powyższe tablice na jedynki i zera oraz tworzy tablicę 2D o takich samych wymiarach, jak oryginalne dane. Proces można zwizualizować, jak pokazano poniżej:
Na koniec SUMPRODUCT zwraca sumę wszystkich elementów w końcowej tablicy, 9.
Licz zamiast sumy
Jeśli chcesz policzyć pasujące wartości zamiast sumować, możesz skrócić formułę do:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Zwróć uwagę, że liczba ta będzie obejmować puste komórki.
Uwagi
- Chociaż przykład pokazuje tylko jedną pasującą kolumnę, ta formuła poprawnie zsumuje wiele dopasowanych kolumn.
- Jeśli chcesz dopasować tylko kolumny (nie wiersze), możesz użyć takiej formuły.
- Aby dopasować tylko wiersze, możesz użyć funkcji LICZ.WARUNKI.
