Formuła ogólna
=SUMPRODUCT(SUMIF(codes,lookups,values))
Podsumowanie
Aby zsumować wartości pobrane przez operację wyszukiwania, można użyć SUMPRODUCT z funkcją SUMA.JEŻELI.
W pokazanym przykładzie formuła w H5 to:
=SUMPRODUCT(SUMIF(codes,C5:G5,values))
Gdzie kody to nazwany zakres J4: J5, a wartości to nazwany zakres K4: K5.
Kontekst
Czasami możesz chcieć zsumować wiele wartości pobranych przez operację wyszukiwania. W tym przykładzie chcemy zsumować czas wolny w każdym tygodniu w oparciu o system kodowy, w którym F = cały dzień, a H = pół dnia. Jeśli dzień jest pusty, nie ma czasu.
Wyzwaniem jest znalezienie wzoru, który zarówno wyszukuje, jak i sumuje wartości związane z F i H.
Wyjaśnienie
Rdzeniem tej formuły jest SUMA.JEŻELI, która służy do wyszukiwania poprawnych wartości F i H. Używanie funkcji SUMA.JEŻELI do wyszukiwania wartości jest bardziej zaawansowaną techniką, która działa dobrze, gdy wartości są liczbowe i nie ma duplikatów w „tabeli przeglądowej ”.
Sztuczka w tym przypadku polega na tym, że kryterium SUMA.JEŻELI nie jest pojedynczą wartością, ale raczej tablicą wartości z zakresu C5: G5:
=SUMPRODUCT(SUMIF(codes,C5:G5,values))
Ponieważ podajemy SUMA.JEŻELI więcej niż jedno kryterium, SUMA.JEŻELI zwróci więcej niż jeden wynik. W przedstawionym przykładzie wynik funkcji SUMA.JEŻELI jest następującą tablicą:
(1, 0, 5, 0, 0, 0)
Zauważ, że poprawnie otrzymujemy 1 dla każdego „F” i 0,5 dla każdego „H”, a puste wartości w tygodniu generują zero.
Na koniec używamy SUMPRODUCT, aby zsumować wartości w tablicy zwróconej przez SUMA.JEŻELI. Ponieważ istnieje tylko jedna tablica, SUMPRODUCT po prostu zwraca sumę wszystkich wartości.
