SzkoleniaMi.pl - Szkolenia WordPress, MS Excel, MS Project, Adobe Photoshop, SEO/SEM

Praktyczne wykorzystanie funkcji AGREGUJ w celu wyeliminowania błędów obliczeniowych.

Tworząc zestawienia (sprzedaży, wyniku finansowego, podsumowania poniesionych kosztów oraz uzyskanych zysków) w rozbiciu na grupy i podgrupy (regiony, oddziały, sklepy, przedziały czasowe) z jednoczesnym zastosowaniem funkcjonalności narzędzia Suma częściowa, otrzymujemy jako wynik: grupy wykorzystujące w formułach funkcję SUMY.CZĘŚCIOWE().

Jest to oczywiście bardzo dobre rozwiązanie, o wiele lepsze niż stosowanie funkcji SUMA(). Jednak – tak jak w przypadku większości funkcji w MS Excel – błędy występujące w komórkach, które są sumowane powodują generowanie błędu przez funkcję sumującą, a naszym przypadku przez funkcję SUMY.CZĘŚCIOWE().

Przykład znajdujący się na rys. powyżej pokazuje, jak błąd wynikający z dzielenia przez pustą komórkę T22 wpłynął na powstanie błędu #DZIEL/0! w całym zestawieniu. Możemy tego uniknąć dodając do wszystkich komórek funkcję sprawdzającą czy występuje błąd w formule, np.: dla komórki K22 musielibyśmy wprowadzić formułę: =JEŻELI.BŁĄD(S22/T22;0). Wynikiem byłoby ’0′ w sytuacji wystąpienia błędu lub wynik dzielenia: S22/T22.

Jest to zbyt czasochłonne rozwiązanie, aby je zastosować do wszystkich komórek w których znajdują się formuły, np.: dla kolumny ‘K’ są one następujące:

Lepszym i z pewnością bardziej eleganckim rozwiązaniem będzie zastosowanie w podsumowaniach zamiast funkcji SUMY.CZĘŚCIOWE(), funkcji AGREGUJ() – dostępnej w MS Excel od wersji 2010.

 
Funkcja AGREGUJ()

Zgodnie z pomocą do programu:

Funkcja AGREGUJ oferuje metodę radzenia sobie z ograniczeniem formatowania warunkowego. Paski danych, zestawy ikon i skale kolorów nie mogą wyświetlać formatowania warunkowego, gdy zakres zawiera błędy. Jest to spowodowane tym, że funkcje MIN, MAX i PERCENTYL nie są obliczane, gdy zakres obliczeń zawiera błąd. Z podobnych powodów funkcje MAX.K, MIN.K i ODCH.STANDARD.POPUL również mają wpływ na prawidłowe działanie niektórych reguł formatowania warunkowego. Te funkcje można stosować za pośrednictwem funkcji AGREGUJ, ponieważ pozwala ona ignorować błędy. Funkcja AGREGUJ może stosować różne funkcje agregujące do listy lub bazy danych, oferując przy tym opcję ignorowania ukrytych wierszy i wartości błędów.

Jednym słowem funkcja AGREGUJ() pozwala podobnie jak SUMY.CZĘŚCIOWE() wykonać jedną z kilku funkcji  matematycznych na zakresie, ale dodatkowo pozwala ignorować błędne i ukryte komórki.

Składnia funkcji SUMY.CZĘŚCIOWE() to: =SUMY.CZĘŚCIOWE(funkcja_nr; adres1; …), natomiast AGREGUJ() to: =AGREGUJ(nr_funkcji; opcje; odw1; …). Różnią się one nieznacznie składnią i działaniem oraz ilością obsługiwanych funkcji.

AGREGUJ oprócz takich funkcji jak:

• ŚREDNIA
• ILE.LICZB
• ILE.NIEPUSTYCH
• MAX
• MIN
• ILOCZYN
• ODCH.STANDARD.PRÓBKI
• ODCH.STAND.POPUL
• SUMA
• WARIANCJA.PRÓBKI
• WARIANCJA.POP

obsługuje dodatkowo funkcje:

• MEDIANA
• WYST.NAJCZĘŚCIEJ.WART
• MAX.K
• MIN.K
• PERCENTYL.PRZEDZ.ZAMK
• KWARTYL.PRZEDZ.ZAMK
• PERCENTYL.PRZEDZ.OTW
• KWARTYL.PRZEDZ.OTW.

Ponadto mamy do wyboru następujące opcje:

Mając do dyspozycji możliwość ignorowania wartości błędów możemy poprawić formuły wykorzystane do stworzenia zestawienia zamieniając funkcję SUMY.CZĘŚCIOWE() na AGREGUJ().

Przykładowo, formuła w komórce K19, zamiast: =SUMY.CZĘŚCIOWE(9;K20:K22) będzie następująca: =AGREGUJ(9;2;K20:K22), a zestawienie mimo błędu dzielenia przez zero w komórce K22 będzie prawidłowo obliczone.

To oczywiście tylko prosty przykład zastosowania nowej funkcji dostępnej w MS Excel 2010, ale wydaje mi się, że będzie ona stanowiła duże ułatwienie podczas tworzenia zestawień odpornych na błędy – do czego serdecznie zachęcam.