Pobierz przykładowy skoroszyt
Ten samouczek pokaże, jak obliczyć „podsumę, jeśli”, licząc tylko widoczne wiersze z kryteriami.
SUMA CZĘŚCIOWA Funkcja
Funkcja SUMY.CZĘŚCIOWE może wykonywać różne obliczenia na zakresie danych (liczba, suma, średnia itp.). Co najważniejsze, można go używać do obliczania tylko widocznych (przefiltrowanych) wierszy. W tym przykładzie użyjemy funkcji do zliczania widocznych wierszy (LICZBA) przez ustawienie argumentu NUMER_CZĘŚCIOWY funkcji na 3 (pełną listę możliwych funkcji można znaleźć tutaj).
= SUMA CZĘŚCIOWA(3, $ D $ 2: $ D $ 14)
Zwróć uwagę, jak zmieniają się wyniki, gdy ręcznie filtrujemy wiersze.
SUMA CZĘŚCIOWA JEŻELI
Aby utworzyć „Suma częściowa If”, użyjemy kombinacji SUMPRODUCT, SUBTOTAL, OFFSET, ROW i MIN w formule tablicowej. Korzystając z tej kombinacji, możemy zasadniczo utworzyć ogólną funkcję „SUM.CZĘŚCIOWA JEŻELI”. Przejdźmy przez przykład.
Mamy listę członków i ich status obecności dla każdego wydarzenia:
Załóżmy, że jesteśmy proszeni o dynamiczne policzenie liczby członków, którzy wzięli udział w wydarzeniu, ponieważ ręcznie filtrujemy listę w następujący sposób:
Aby to osiągnąć, możemy skorzystać z następującej formuły:
=SUMPRODUCT((=)*(SUMA CZĘŚCIOWA(3,PRZESUNIĘCIE(,WIERSZ()-MIN(WIERSZ()),0)))))
=SUMPRODUCT((D2:D14="Nadzorowany")*(SUM.CZĘŚCIOWA(3,OFFSET(D2,ROW(D2:D14))-MIN(ROW(D2:D14)),0))))
W przypadku korzystania z programu Excel 2022 lub starszego należy wprowadzić formułę tablicową, naciskając CTRL + SHIFT + ENTER aby poinformować program Excel, że wprowadzasz formułę tablicową. Będziesz wiedział, że formuła została poprawnie wprowadzona jako formuła tablicowa, gdy wokół formuły pojawią się nawiasy klamrowe (patrz obrazek powyżej).
Jak działa formuła?
Formuła działa poprzez pomnożenie dwóch tablic wewnątrz SUMPRODUCT, gdzie pierwsza tablica dotyczy naszych kryteriów, a druga tablica filtruje tylko widoczne wiersze:
=PRODUKCJASUM(*)
Tablica kryteriów
Tablica kryteriów ocenia każdy wiersz w naszym zakresie wartości (w tym przykładzie status „Nadzorowany”) i generuje tablicę podobną do tej:
=(=)
=(D2:D14="Uczestniczy")
Wyjście:
{PRAWDA; FAŁSZYWE; FAŁSZYWE; PRAWDA; FAŁSZYWE; TURKA; TURKA; TURKA; FAŁSZYWE; FAŁSZYWE; PRAWDA; FAŁSZYWE; PRAWDA}
Zwróć uwagę, że dane wyjściowe w pierwszej tablicy w naszej formule ignorują to, czy wiersz jest widoczny, czy nie, w czym pomaga nasza druga tablica.
Tablica widoczności
Używając SUBTOTAL, aby wykluczyć niewidoczne wiersze z naszego zakresu, możemy wygenerować naszą tablicę widoczności. Jednak sama suma częściowa zwróci pojedynczą wartość, podczas gdy SUMPRODUCT oczekuje tablicy wartości. Aby obejść ten problem, używamy PRZESUNIĘCIA, aby przekazać jeden wiersz na raz. Ta technika wymaga podania PRZESUNIĘCIA tablicy, która zawiera jedną liczbę na raz. Druga tablica wygląda tak:
= SUMA CZĘŚCIOWA(3, PRZESUNIĘCIE(, WIERSZ()-MIN(WIERSZ()),0))
=SUMA CZĘŚCIOWA(3;PRZESUNIĘCIE(D2,WIERSZ(D2:D14)-MIN(WIERSZ(D2:D14));0))
Wyjście:
{1;1;0;0;1;1}
Łączenie dwóch razem:
=SUMPRODUCT({PRAWDA; PRAWDA; FAŁSZ; FAŁSZ; PRAWDA; PRAWDA} * {1; 1; 0; 0; 1; 1})
= 4
SUMA CZĘŚCIOWA JEŻELI z wieloma kryteriami
Aby dodać wiele kryteriów, po prostu wiele więcej kryteriów razem w SUMPRODUCT, jak na przykład:
=SUMPRODUCT((=)*(=)*(SUMA CZĘŚCIOWA(3,PRZESUNIĘCIE(,ROW()-MIN(ROW()),0)))))
=SUMPRODUCT((E2:E14="Nadzorowany")*(B2:B14=2019)*(SUM.CZĘŚCIOWA(3,OFFSET(E2,ROW(E2:E14))-MIN(ROW(E2:E14)),0)) ))
SUMA CZĘŚCIOWA JEŻELI w Arkuszach Google
Funkcja JEŻELI SUMA CZĘŚCIOWA działa dokładnie tak samo w Arkuszach Google, jak w programie Excel:
