Formuła ogólna
=IF(A2=A1,"",SUMIF(A:A,A2,B:B))
Podsumowanie
Aby podsumować dane według grupy lub etykiety bezpośrednio w tabeli, można użyć formuły opartej na funkcji SUMA.JEŻELI.
W przedstawionym przykładzie formuła w D5 to:
=IF(B5=B4,"",SUMIF(B:B,B5,C:C))
Uwaga: dane muszą być sortowane według kolumny grupującej, aby uzyskać rozsądne wyniki.
Wyjaśnienie
Struktura tej formuły jest oparta na funkcji JEŻELI, która testuje każdą wartość w kolumnie B, aby sprawdzić, czy jest taka sama jak wartość w „komórce powyżej”. Gdy wartości są zgodne, formuła nie zwraca niczego („”). Gdy wartości są różne, funkcja JEŻELI wywołuje SUMA.JEŻELI:
SUMIF(B:B,B5,C:C)
W każdym wierszu, w którym funkcja SUMA.JEŻELI jest wyzwalana przez JEŻELI, funkcja SUMA.JEŻELI oblicza sumę wszystkich pasujących wierszy w kolumnie C (C: C). Kryteria używane przez funkcję SUMA.JEŻELI to bieżąca wartość wiersza w kolumnie B (B5), sprawdzana względem całej kolumny B (B: B).
Pełne odwołania do kolumn, takie jak ta, są fajne i eleganckie, ponieważ nie musisz się martwić o to, gdzie zaczynają się i kończą dane, ale musisz się upewnić, że nad lub pod tabelą nie ma dodatkowych danych, które mogą zostać przechwycone przez SUMIF.
Występ
Może się wydawać, że użycie odwołania do pełnej kolumny jest złym pomysłem, ponieważ obecne wersje programu Excel zawierają więcej niż 1 mln wierszy. Jednak testy wykazały, że program Excel ocenia tylko dane z „używanego zakresu” (A1 do adresu przecięcia ostatniej używanej kolumny i ostatnio używanego wiersza) za pomocą tego typu formuły.
Charles Williams z Fast Excel ma dobry artykuł na ten temat, z pełnym zestawem wyników pomiaru czasu.
Dlaczego o tabelach przestawnych?
Ten przykład ma na celu pokazanie, jak działają odwołania do pełnych kolumn i jak można ich używać bezpośrednio w tabeli danych. Tabele przestawne pozostają doskonałym sposobem grupowania i podsumowywania danych.
