Mamy prostą bazę z pracownikami.
W tej bazie danych, będziemy chcieli wyciągnąć kilka informacji statystycznych takich jak np. suma lub średnia zarobków osób z wybranego działu, albo suma zarobków mężczyzn z wybranego działu lub najwyższa i najniższa pensja z wybranego działu.
Pobierz przykładowy plik do ćwiczeń
W tym wpisie poznamy dość sporo nowych funkcji. Wszystkie te funkcje są w pewnym sensie dość podobne do funkcji LICZ.JEŻELI lub LICZ.WARUNKI. Jeśli nie znasz tych funkcji, odsyłam do następujących notek:
- Opis funkcji LICZ.JEŻELI
- Opis funkcji LICZ.WARUNKI
- LICZ.JEŻELI i symbole wieloznaczne
- Łączenie funkcji LICZ.JEŻELI z innymi funkcjami
Znajomość funkcji LICZ.JEŻELI ułatwi przyswojenie informacji zawartych w tym wpisie. Dwa pierwsze wpisy są obowiązkowe, znajomość zagadnień z dwóch ostatnich wpisów nie jest potrzebna do łatwiejszego zrozumienia tego artykułu, ale pomoże w przyszłości budować ciekawsze formuły.
No więc zaczynamy. Aby policzyć ile mamy osób w księgowości należy sprawdzić ile razy w zakresie komórek D2:D16 jest wpisane słowo księgowość. W tym celu użyjemy funkcji LICZ.JEŻELI. Pierwszy argument tej funkcji to zakres komórek, które będziemy analizować a drugi argument to kryteria (interesuje nas słowo księgowość):
=LICZ.JEŻELI(D2:D16; "księgowość")Dla danych z powyższego rysunku słowo księgowość występuje 5 razy. Następnie policzmy sumę zarobków wszystkich osób z działu księgowość. Tutaj skorzystamy z funkcji SUMA.JEŻELI. Dwa pierwsze argumenty tej funkcji są takie same, jak w przypadku funkcji LICZ.JEŻELI. Trzeci argument to zakres komórek w których są owe pensje czyli E2:E16. Oba obszary tj. obszar kryteriów i obszar danych do sumowania powinny być podobne tj. składać się z takiej samej liczby komórek i mieć „ten sam kształt” (w naszym wypadku jest to pionowy zakres 15 kolejnych komórek).
=SUMA.JEŻELI(D2:D16; "księgowość"; E2:E16)Aby policzyć średnią pensję w dziale księgowość można podzielić wcześniej obliczoną sumę zarobków przez liczbę księgowych (czyli można wpisać formułę =D20/D19). Ale tak się składa, że istnieje podobna funkcja tj. ŚREDNIA.JEŻELI, która ma takie same argumenty jak nasza funkcja SUMA.JEŻELI:
= ŚREDNIA.JEŻELI(D2:D16; "księgowość"; E2:E16)Pewną ciekawostką w przypadku funkcji ŚREDNIA.JEŻELI (jak również SUMA.JEŻELI) jest fakt, że jeśli zakres kryteriów i zakres komórek do zliczania to jest ten sam zakres, to można pominąć trzeci argument funkcji. Przykładowo: aby policzyć jaka jest średnia pensja dla osób, których zarobki są niższe niż 4000 zł możemy użyć następującej funkcji:
=ŚREDNIA.JEŻELI(E2:E16; "<4000")Oczywiście nic nie stoi na przeszkodzie, aby wpisać trzeci argument (wynik będzie ten sam)
=ŚREDNIA.JEŻELI(E2:E16; "<4000"; E2:E16)Teraz rozpatrzmy trudniejsze zagadnienie. Otóż interesuje nas suma zarobków mężczyzn z działu IT. Tutaj nie możemy użyć funkcji SUMA.JEŻELI, gdyż ta funkcja sprawdza tylko jeden warunek (czyli za pomocą tej funkcji moglibyśmy albo policzyć sumę zarobków dla mężczyzn albo sumę zarobków dla osób z działu IT). Jeśli chcemy zliczyć sumę zarobków dla osób, które spełniają jednocześnie kilka warunków, należy skorzystać z funkcji SUMA.WARUNKÓW. Funkcja ta nieco się różni od funkcji SUMA.JEŻELI. Otóż pierwszy argument to zakres komórek do sumowania, następnie należy podać w parach po 2 argumenty: zakres kryteriów i kryterium. Według dokumentacji możemy podać nawet 127 par argumentów zakres kryteriów i kryterium, ale tutaj będą tylko dwie pary. Tak wygląda nasza funkcja:
=SUMA.WARUNKÓW(E2:E16; B2:B16; "<>*a"; D2:D16; "IT")Gdzie:
- E2:E16 – w tych komórkach znajdują się pensje
- B2:B16 – w tych komórkach znajdują się imiona pracowników
"<>*a" – to kryterium mówi, że interesują nas (w komórkach B2:B16) tylko te komórki w których imiona nie kończą się na literę a (w języku polskim damskie imiona kończą się na literę a, męskie kończą się na każdą inną, tylko nie na a). Znaki <> oznaczają „różne od” a symbol wieloznaczny *a mówi, że szukamy pewnego wzorca: interesuje nas dowolny ciąg znaków (gwiazdka), który jest zakończony literą a. O symbolach wieloznacznych możesz poczytać w notce: Funkcja LICZ.JEŻELI i symbole wieloznaczne. - D2:D16 – w tych komórkach znajdują się działy do jakich są przypisani pracownicy
- "IT" – interesują nas tylko te komórki, które zawierają tekst IT.
Funkcja ta znajduje 2 pracowników, którzy spełniają te reguły (ogólnie mamy 3 pracowników w dziale IT, ale Dorota nie jest mężczyzną bo jej imię kończy się na literę a).
Tutaj mała dygresja. Można używać funkcji SUMA.WARUNKÓW w zastepstwie do funkcji SUMA.JEŻELI gdy mamy tylko jeden warunek do sprawdzenia. Przykładowo jeśli chcemy sumę zarobków w księgowości to zamiast:
=SUMA.JEŻELI(D2:D16;"księgowość"; E2:E16)Możemy wpisać:
=SUMA.WARUNKÓW(E2:E16; D2:D16; "księgowość")(Tutaj nieco myląca jest kolejność argumentów otóż w funkcji SUMA.WARUNKÓW zawsze na początku podajemy zakres do sumowania a w przypadku funkcji SUMA.JEŻELI zakres do sumowania podajemy na końcu)
Analogicznie możemy policzyć średnią zarobków dla osób z działu IT. Istnieje bliźniacza funkcja o nazwie ŚREDNIA.WARUNKÓW, która przyjmuje takie same argumenty jak funkcja SUMA.WARUNKÓW:
=ŚREDNIA.WARUNKÓW(E2:E16;B2:B16;"<>*a";D2:D16;"IT")Na koniec zostało nam sprawdzenie jaka jest najniższa i najwyższa pensja w dziale IT. Zapewne znasz funkcje MIN i MAX. Niestety, ale te funkcji zwracają najmniejszą bądź największą liczbę dla wszystkich pracowników. Na szczęście istnieją bliźniacze funkcje MIN.WARUNKÓW i MAKS.WARUNKÓW (te funkcje pojawiły się dopiero w Excelu 2019 lub też są dostępne jeśli korzystasz z pakietu Office 365). Mają one podobną budowę do funkcji SUMA.WARUNKÓW i ŚREDNIA WARUNKÓW (tutaj sprawdzamy tylko jedno kryterium, nie ma w Excelu funkcji o nazwach min.jeżeli czy max.jeżeli)
=MIN.WARUNKÓW(E2:E16; D2:D16; "IT")
=MAKS.WARUNKÓW(E2:E16; D2:D16; "IT")
Zwróć uwagę na to, że istnieje pewna niekonsekwencja w nazewnictwie funkcji tj. funkcja zwracająca największą liczbę to MAX a funkcja zwracająca największą liczbę dla komórek spełniających podane warunki to MAKS.WARUNKÓW.
Jeśli posiadasz starszą wersję pakietu MS Office, to zamiast funkcji MIN.WARUNKÓW i MAKS.WARUNKÓW możemy użyć sprytne formuły tablicowe (po ich wpisaniu zamiast Entera należy nacisnąć kombinację klawiszy CTRL+SHIFT+ENTER). O funkcjach tablicowych być może w przyszłości popełnię jakiś wpis gdyż ten temat jest dość rozbudowany.
Alternatywa dla MIN.WARUNKÓW:
=MIN(JEŻELI(D2:D16="IT";E2:E16))Alternatywa dla MAKS.JEŻELI:
=MAX(JEŻELI(D2:D16="IT";E2:E16))Kończąc ten przydługawy wpis napisz w komentarzu czy nie pominąłem jakiejś ciekawej funkcji, która mogłaby się znaleźć w tym zestawieniu.
