Funkcje LICZ.WARUNKI i SUMA.WARUNKÓW

Funkcja LICZ.JEŻELI

Licz jeżeli to jedna z najpopularniejszych funkcji w programie Excel. Wiele osób najpierw pracuje na tabelach z nałożonym filtrami i tam próbuje zliczać wyszczególnione rekordy, jednak ta metoda jest dosyć doraźna, gdy każda zmiana parametrów filtrów powoduje przemodelowanie tabeli, a nasz zliczony wykaz automatycznie znika. Jak więc zatrzymać wynik na dłużej, a najlepiej pozyskać go do komórki arkusza? Naturalnie funkcja =LICZ.JEŻELI(). Jest to prosta funkcja, która zwraca nam z zaznaczonego zakresu liczbę wystąpień danej frazy lub wartości liczbowej np. „3” w zbiorze miast, gdzie szukamy wyrażenia „Wrocław”:

Funkcja oczywiście wygląda następująco:

Warto tutaj zwrócić uwagę, że funkcja jest „ślepa” na wielkość liter, napisaliśmy Wrocław z małej litery „W”, a jednak odszukał 3 rekordy.

Jednak dzisiaj nie rozmawiamy o funkcji licz jeżeli, a o nieco bardziej rozbudowanej jej siostrach.

Jak zauważyliśmy powyższa funkcja pokazuje 1 wynik dla jednego zakresu, przy jednym tylko kryterium. Jeśli pracujemy na filtrach tabel, to zazwyczaj tych kryteriów zakładamy znacznie więcej, np. chcemy wyszczególnić dane miast (kryterium 1), następnie daną grupę produktów (kryterium 2) i do tego w określonym czasie sprzedaży (kryterium 3). Co jeśli, chcemy zrobić podobne zapytanie do naszej tabeli, lecz w postaci formuły? Wtedy z pomocą przychodzą dwie funkcje - =LICZ.WARUNKI() oraz =SUMA.WARUNKÓW().

Funkcja LICZ.WARUNKI

Najpierw zajmijmy się tą łatwiejszą, czyli licz warunki. Aby dobrze zrozumieć jej działanie, wyobraźmy sobie, że posiadamy nie jeden, a kilka komisów samochodów używanych w całym kraju. Jako właściciel lub manager, chcemy podejmować lepsze decyzje co do importu kolejnych aut, tak by zarobić jak najwięcej. Kluczowa dla nas będzie informacja, jakiego typu auta sprzedają się najchętniej. Logicznie analizując sytuację, to zależy pewnie też od miasta, może osoby we Wrocławiu preferują inne auta niż osoby w stolicy czy Krakowie, dajmy na to Wrocław i Karków mają stosunkowo blisko w góry, a nad może daleko – więc ludzie mogą woleć duże rodzinne auta, natomiast stolica jest dość zurbanizowania i zatłoczona, więc tutaj preferencje były by do małych zwinnych aut miejskich? To nasze hipotetyczne założenia, ale szanujący się biznesmen nie opiera się na przypuszczeniach, a na analizie danych – więc zapytajmy o to Excela, a dokładnie obejrzyjmy dane z naszej sprzedaży.

Tak naprawdę tutaj można by liczyć korelacje aut do różnych cech, jak wielkość, kolor, silnik – ale należałoby dysponować tabelą z takimi danymi.

My skupimy się na 2 kryteriach – lokalizacji i cenie auta. Dla potrzeb ćwiczenia przyjmujemy, że „drogie auto” zaczyna się powyżej 40 000 zł, a tanie analogicznie.

Pytanie (hipoteza) jakie stawiamy to:  Czy w danym mieście chętniej sprzedają się auta tanie, czy drogie?

Jak widzimy, funkcja LICZ.JEZELI – potrafi policzyć tylko jedno kryterium, albo cena – albo miasto, a my chcemy odszukać połączenie w jednej linii rekordu tych dwóch cech.

Ustawiamy pilotażowe kryterium – lokalizacja WROCŁAW; cena <40000

Tabela wygląda następująco:

Używając formatowania warunkowego, jesteśmy w stanie zrobić to „na oko” – naszą odpowiedzią są wyniki, gdzie mamy wskazane na zielono miasto Wrocław i pasuje ono w poziomie z żółtym polem ceny

Niebieskie zakresy to kolumna MIASTO – SALON, czerwony zakres to CENA.

Funkcja składa się z duetów – pakietów w którym występuje ZAKRES nr 1 oraz Kryterium – do tego zakresu i następny duet – ZAKRES nr 2 i analogiczne jego kryterium. Funkcja nie ma końca i możemy takich pakietów wymieniać więcej, jeśli pozwala nam na to baza danych, tzn. tabela. Przykładowo zakres A1:A100 (zawiera kolory karoserii) i kryterium „Czerwony metalik”, następnie zakres B1:B100 (zawiera przykładowo silnik) i kryterium „Diesel”.

Zmieniając teraz tylko przedział < > możemy szybko przeliczyć drogie auta, a tanie auta.

Funkcja SUMA.WARUNKÓW

Druga funkcja z tej rodziny poleceń to SUMA.WARUNKÓW

Jest to w zasadzie bliźniacza funkcja, lecz z małym dodatkiem. Analizując powyższą tabelę poznajemy ilości dopasowanych kryteriów do zakresu, ale jako manager chcielibyśmy poznać tak naprawdę wartość sprzedaży. Co z tego, że sprzedaliśmy 100 aut za grosze, skoro zarobek był śladowy, może jednak lepiej sprzedawać mniej, a droższych?

Funkcja SUMA.WARUNKÓW jest w sanie odpowiedzieć nam na powyższe pytanie, bo robi to co jej poprzedniczka, czyli odszukuje te „zestawy” ale dodatkowo oblicza sumę ze wskazanych wyników w wybranej przez n

as kolumnie. Sprawdźmy w praktyce na tym samym przykładzie:

W tej formule jako pierwszy składnik musimy wskazać zakres, gdzie obliczymy sumę, a dalsza część odbywa się jak w poprzedniej funkcji, czyli duet zakres i kryterium, kolejny duet zakres i kryterium itd.

UWAGA! W tym wyjątkowym przykładzie musimy zaznaczyć 2x ten sam zakres z cenami, raz on jest traktowany jako miejsce do obliczeń sumy, a drugim razem jako obszar szukania kryterium dla ceny.