Mój ulubiony sposób budowy raportu w Excelu to tabela przestawna. Znam jednak wielu użytkowników, którzy lubią pracować na danych źródłowych i stawiając na nich filtry, obserwować wyniki. Większość raportów tego typu wykorzystuje funkcje AGREGUJ i SUMY.CZĘŚCIOWE. Zobaczmy, jak skonstruować z ich użyciem raport, który łatwo się będzie filtrował za pomocą fragmentatorów i zwykłego filtru na kolumnie.

[grwebform url="https://app.getresponse.com/view_webform_v2.js?u=N3vX&webforms_id=9648602" css="on" center="off" center_margin="0"/]

Składnia funkcji Excel SUMY.CZĘŚCIOWE

Funkcja SUMY.CZĘŚCIOWE to funkcja dość dobrze znana analitykom i entuzjastom Excela. Jest pomocna przy konieczności dokonania szybkich obliczeń na przefiltrowanych danych. SUMY.CZĘŚCIOWE pojawiły się w Excelu jeszcze przed tabelami przestawnymi i są ich prekursorem. Najłtawiej jest je wstawić z karty Dane > Sumy częściowe. Jedną z podformuł Sumy.częściowej jest SUMA. Podformuła ta różni się od zwykłej sumy tym, że sumuje tylko wyświetlane komórki, a ignoruje ukryte/przefiltrowane wartości. Natomiast zwykła suma sumuje wszystkie wartości (również ukryte).

Poniżej opis z Excela powyższej funkcji:

SUMY.CZĘŚCIOWE posiadają 11 różnych kombinacji z możliwością uwzględniania oraz pomijania wartości ukrytych. Poniżej lista dostępnych kombinacji.

Składnia funkcji Excel AGREGUJ

Kolejną pomocną formułą przy obliczeniach na tabelach źródłowych jest funkcja AGREGUJ. Funkcja ta przedstawia wartość zagregowaną z listy lub bazy danych. Funkcja AGREGUJ umożliwia stosowanie różnych funkcji agregujących. Jest dostępnych 19 podfunkcji – ich szczegółowa lista poniżej:

Ponadto funkcja AGREGUJ pozwala agregować dane w różny sposób, np. jest możliwość ignorowania różnych wartości.

W poniższym artykule zostaną wykorzystane funkcje SUMY.CZĘŚCIOWE i AGREGUJ na zdefiniowanej tabeli źródłowej z filtrami.

Tworzymy SUMY.CZĘŚCIOWE

Na początku zostanie utworzona zdefiniowana tabela. Dane dotyczą sprzedaży poszczególnych produktów w poszczególnych regionach i segmentach.

Aby utworzyć tabelę należy zaznaczyć dane i nacisnąć kombinację klawiszy CTRL+T.

Następnym krokiem będzie wstawienie fragmentatorów na tabeli, analogicznie jak opisywałem przy przepływach na mapie. Fragmentatory będą dotyczyć Segmentu rynku, Regionu oraz Kategorii produktów. Zostaną umieszczone po prawej stronie danych. Dodatkowo każdemu z nich został przyporządkowany inne kolor, aby różniły się od siebie. Fragmentatory są połączone z tabelą i dane zostały przefiltrowane przy pomocy dwóch kliknięć.

Pod przefiltrowanymi danymi zostaną wstawione SUMY.CZĘŚCIOWE. W częściej spotykanej technice umieszcza się te formuły nad tabelą. Pierwsza z nich będzie dotyczyła Sumy (nr 9).

Formuła zostanie przygotowana dla kolumn Sprzedaż oraz Zysk. Składnia formuły wymusza wskazanie działania, jakie ma być wykonane na danych, następnie należy wskazać zakres danych. W związku z tym, że dane są zdefiniowaną tabelą wystarczy wskazać jej nazwę oraz nazwę kolumy.

Kolejną funkcją jest Średnia (nr 1) również dla kolumn Sprzedaż oraz Zysk.

Najważniejsze: obie formuły zliczają przefiltrowane wartości.

Tworzymy AGREGUJ

Te same obliczenia zostaną wykonane przy pomocy funkcji AGREGUJ.

Pierwszą z nich będzie kolejny raz SUMA. Ponadto zostanie wybrana kombinacja, która ignoruje ukryte wiersze, wartości błędne oraz zagnieżdżone funkcje SUMY.CZĘŚCIOWE oraz Agreguj.

Funkcja jest przygotowana dla wartości z kolumn Sprzedaż oraz Zysk.

Jak widać poniżej, obie funkcje zwracają te same wyniki, ale w inny sposób. Funkcja SUMY.CZĘŚCIOWE jest bardziej popularna i częściej wykorzystywana. Natomiast funkcja AGREGUJ umożliwia pominięcie większej ilości rodzajów danych przy obliczeniach (także jeśli po drodze napotka SUMY.CZĘŚCIOWE i AGREGUJ).

Podsumowanie

Funkcje SUMY.CZĘŚCIOWE i AGREGUJ sprawdzają się przy dokonywaniu obliczeń na tabeli z danymi w momencie, gdy użytkownik chce widzieć tabelę źródłową i ją filtrować, wpływając filtrami na wyliczenia. Ten sposób pracy jest bardziej intuicyjny dla części użytkowników niż praca z tabelą przestawną. Obie formuły mają podobne zastosowanie, przy czym funkcja AGREGUJ umożliwia bardziej szczegółowe wykluczenia. Zwykle kalkulacje umieszcza się nad tabelą (czasem pod) i wstawia filtry w postaci fragmentatorów (choć zwykłe filtrowanie będzie działać podobnie).