Formuła ogólna
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Podsumowanie
Aby policzyć widoczne wiersze tylko za pomocą kryteriów, możesz użyć dość złożonej formuły opartej na SUMPRODUCT, SUBTOTAL i OFFSET. W przedstawionym przykładzie formuła w C12 to:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Przedmowa
Funkcja SUMA CZĘŚCIOWA może łatwo generować sumy i liczniki dla ukrytych i nieukrytych wierszy. Jednak bez pomocy nie jest w stanie obsłużyć kryteriów takich jak LICZ.JEŻELI lub SUMA.JEŻELI. Jednym z rozwiązań jest użycie funkcji SUMPRODUCT do zastosowania zarówno funkcji SUMA CZĘŚCIOWA (poprzez PRZESUNIĘCIE) i kryteriów. Szczegóły tego podejścia opisano poniżej.
Wyjaśnienie
Zasadniczo ta formuła działa poprzez skonfigurowanie dwóch tablic w SUMPRODUCT. Pierwsza tablica stosuje kryteria, a druga obsługuje widoczność:
=SUMPRODUCT(criteria*visibility)
Kryterium jest stosowane z częścią formuły:
=(C5:C8=C10)
Który generuje taką tablicę:
(FALSE;TRUE;FALSE;TRUE)
Gdzie PRAWDA oznacza „spełnia kryteria”. Uwaga, ponieważ używamy mnożenia (*) w tej tablicy, wartości TRUE FALSE zostaną automatycznie przekonwertowane na 1 i 0 przez operację matematyczną, więc otrzymamy:
(0;1;0;1)
Filtr widoczności jest stosowany przy użyciu SUMY CZĘŚCIOWEJ z funkcją numer 103.
SUBTOTAL jest w stanie wykluczyć ukryte wiersze podczas wykonywania obliczeń, więc możemy go użyć w tym przypadku do wygenerowania „filtru” w celu wykluczenia ukrytych wierszy w SUMPRODUCT. Problem polega jednak na tym, że SUBTOTAL zwraca pojedynczą liczbę, podczas gdy potrzebujemy tablicy wyników, aby pomyślnie użyć jej w SUMPRODUCT. Sztuczka polega na użyciu funkcji OFFSET, aby podać SUBTOTAL jedno odwołanie na wiersz, tak aby funkcja OFFSET zwróciła jeden wynik na wiersz.
Oczywiście wymaga to kolejnej sztuczki, która polega na podaniu OFFSET-u tablicy zawierającej jedną liczbę na wiersz, zaczynając od zera. Robimy to za pomocą wyrażenia zbudowanego na funkcji WIERSZ:
=ROW(C5:C8)-MIN(ROW(C5:C8)
który wygeneruje tablicę taką jak ta:
(0;1;2;3)
Podsumowując, druga tablica (która obsługuje widoczność za pomocą SUBTOTAL) jest generowana w następujący sposób:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
I wreszcie mamy:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Co zwraca 1.
Wiele kryteriów
Możesz rozszerzyć formułę, aby obsługiwała wiele kryteriów, takich jak to:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Podsumowanie wyników
Aby zamiast liczby zwracać sumę wartości, możesz dostosować formułę, tak aby obejmowała zakres sumy:
=SUMPRODUCT(criteria*visibility*sumrange)
Kryteria i tablice widoczności działają tak samo, jak wyjaśniono powyżej, z wyłączeniem komórek, które nie są widoczne. Jeśli potrzebujesz częściowego dopasowania, możesz skonstruować wyrażenie za pomocą ISNUMBER + SEARCH, jak wyjaśniono tutaj.
