Zrozumienie kombinacji SumIF i sumy częściowej

Natknąłem się na to rozwiązanie łączenia sumif i sumy częściowej, ale nie rozumiem tego ..

Łączenie SUMIF () i SUBTOTAL () dla dynamicznej sumy częściowej

Czy ktoś mógłby wyjaśnić, w jaki sposób ten zestaw funkcji działa razem, aby był zrozumiały dla innych niedoświadczonych użytkowników (takich jak ja)? To rozwiązanie oczywiście rozwiązuje pytanie, ale bardzo trudno jest go dostosować do innego zestawu danych, bez większego wglądu. (Wolę nie publikować własnego pytania, ponieważ jeśli tak, to następna osoba z tym zapytaniem będzie miała ten sam problem!)

Dzięki!

microsoft-excel

— Alex S.
źródło

Jak moglibyśmy odpowiedzieć na twoje pytanie bez twoich danych?

— Raystafarian,

Chodzi o to, że jeśli potrafisz wyjaśnić, jak działa formuła, dzieląc obliczenia, wszystko, co muszę zrobić, to dostosować odniesienia. Mam już rozwiązanie (wiem, że jest to właściwe, ponieważ widziałem je na kilku stronach), po prostu nie rozumiem, jak je zastosować.

— Alex S

=SUMPRODUCT(SUBTOTAL(109,OFFSET(AW5,ROW(AW5:AW552)-ROW(AW5),,1)),--(AV5:AV552=AV558))

Sumproduct - mnoży jedną tablicę przez drugą i dodaje je razem.

Subtotal- daje sumę częściową określonej operacji na zakresie (zakresach) - oto argumenty . 109 = suma

Offset - przyjmuje odwołanie i używa przesunięcia do odwołania do komórki, która jest odsunięta od niego.

row - daje numer wiersza odniesienia

--Oznaczenie wymusza [prawdziwe] i [false] powrót w [1] [0] w tablicy. Kiedy operujesz na prawdzie lub fałszu, stają się one numeryczne, więc tutaj działasz, a następnie odwracasz znak. Możesz zamiast tego*1

Więc jeśli czytam to poprawnie:

Wiersz-wiersz zwraca tablicę liczb, które są przesunięte względem AW5 o wysokość 1
Sprawdź AV5: AV552 pod kątem dopasowania AV558, jako tablica 0 i 1
Zsumuj pierwszą tablicę i pomnóż ją przez drugą tablicę.

To pomaga?

— Raystafarian
źródło

Dzięki Ray, to ogromna pomoc. Zrozumiałem, jakich rezultatów mogę się spodziewać po SUMPRODUCT, ale nie wiem, jak działały obliczenia, więc się tam dostaliśmy. Część formuły, która wciąż mnie myli, to PRZESUNIĘCIE - czy możesz wyjaśnić jej cel w ramach tej konkretnej formuły? Lub, mówiąc inaczej, co by się stało, gdyby nie został użyty i zamiast tego został użyty ręcznie wybrany odnośnik?

— Alex S

@AlexS offset działa jak countif, więc jeśli offset działa, to zrób sumę. Więc jeśli nie przesuniesz i nie użyjesz referencji, nie dostosuje się ona podczas przetwarzania tablicy.

— Raystafarian,

Rozumiem, więc moja formuła byłaby taka, =SUMPRODUCT(SUBTOTAL(109,OFFSET($AF$6,ROW($AF$6:$AF$91)-ROW($B$6),,1)),--($B$6:$B$91=$B$95))jeśli chcę obliczyć sumę widocznych (niefiltrowanych) wartości w komórkach AF6: AF91, o ile wartość w odpowiednim wierszu w zakresie B6: B91 odpowiada B95. Czy to wygląda dobrze?

— Alex S

@AlexS nie, pierwszy $B$6powinien byćAF6

— Raystafarian

dzięki, wydaje się, że działa z oryginalnym odniesieniem, ale rozumiem, dlaczego twoja sugestia jest najlepszą praktyką. To rozwiązało moje zapytanie.

— Alex S