Wyobraźmy sobie sytuację, w której mamy dużą tabelę w Excelu podzieloną na 4 kolumny. W pierwszej mamy liczbę egzemplarzy, w drugiej tytuł książki, w trzeciej kategorię oraz w ostatniej cenę za sztukę. Naszym zadaniem będzie np. policzenie ile jest wszystkich artykułów, których liczba jest większa od 15 albo ile jest wszystkich egzemplarzy w danej kategorii. Jak się w ogóle do tego zabrać? Otóż, jest na to kilka sposobów. Jednym z nich jest wykorzystanie funkcji SUMA.JEŻELI() bądź SUMA.WARUNKÓW().
Plik Excela wykorzystywany w przykładach możesz pobrać tutaj
Pierwszą funkcją, która zostanie omówiona jest funkcja SUMA.JEŻELI(). Doprecyzowując to co zostało wcześniej napisane, jest to funkcja sumująca wartości, ale tylko i wyłącznie w chwili, w której spełnione jest odpowiednie kryteria.
SUMA.JEŻELI(zakres;kryteria;[suma_zakres])
gdzie:
Dobrze, wiemy już jak jest zbudowana funkcja SUMA.JEŻELI(), teraz czas na praktyczne podejście. Wróćmy do sytuacji z początku artykułu. Mamy tabelę składającą się z czterech kolumn, w których mamy podane kolejno: liczbę egzemplarzy, tytuł, kategorię i cenę za sztukę.
Przykład 1:
=SUMA.JEŻELI(A2:A17;”>15”)
Interesuje nas sprawdzenie, ile jest wszystkich egzemplarzy, których liczba jest większa od 15. W tym celu wykorzystamy funkcję SUMA.JEŻELI(). Jako zakres podamy kolumnę z liczbą artykułów, natomiast jako kryterium podamy formułę >20. Warto zauważyć, że argument opcjonalny [suma_zakres] został pusty, dzięki czemu funkcja sumowała wartości znajdujące się w zakresie z pierwszego argumentu.
Przykład 2:
=SUMA.JEŻELI(C2:C17;”=Historia”;A2;A17)
W drugim przykładzie będziemy chcieli policzyć wszystkie artykuły będące w kategorii historia. Ponownie wykorzystamy funkcję SUMA.JEŻELI(), ale tym razem jako zakres podamy kolumnę z nazwami kategorii, jako kryterium weźmiemy wyrażenie “=Historia”. Interesuje nas sumowanie z innej kolumny, niż to co podaliśmy w argumencie pierwszym. W tym celu wykorzystamy opcjonalny argument, czyli suma_zakres, za który podanym kolumnę z liczbą artykułów.
Przykład 3:
Jeżeli chcielibyśmy obliczyć ile jest artykułów w każdej z kategorii, możemy do tego wykorzystać komórkę, w której zapiszemy wartość sprawdzaną jako kryterium. W takiej sytuacji trzeba pamiętać o zablokowaniu zakresu podanego jako pierwszy i trzeci argument funkcji. Dodatkowo dodana została komórka, która liczy sumę artykułów z kolumny A i sumę wszystkich książek, które uzyskaliśmy przy użyciu funkcji SUMA.JEŻELI(). Widzimy, że w jednym i drugim przypadku wartości są sobie równe.
Jeżeli chcielibyśmy policzyć wszystkie książki z danej kategorii, których cena przekracza 20zł funkcja SUMA.JEŻELI() niestety nie zda egzaminu. Możemy w niej podać tylko jedno kryterium. Jeśli mamy do czynienia z sytuacją, w której należy sprawdzić więcej niż jeden warunek z pomocą przychodzi funkcja SUMA.WARUNKÓW().
Jest to bardzo podobna funkcja do wcześniej opisywanej SUMA.JEŻELI() z tą różnicą, że mamy w niej możliwość wpisania więcej niż jednego kryterium. Składnia jest następująca:
SUMA.WARUNKÓW(suma_zakres;kryteria_zakres1;kryteria,[kryteria_zakres2;kryteria2…]
gdzie:
W przypadku funkcji SUMA.WARUNKÓW(), dokładnie tak jak w przypadku funkcji SUMA.JEŻELI(), kształt i rozmiar argumentu dla suma_zakres i kryteria_zakres muszą być dokładnie takie same.
Przykład 4:
Zajmijmy się naszym przypadkiem, do którego zostaliśmy wprowadzeni we wcześniejszej części. Mianowicie, chcemy policzyć ile jest wszystkich artykułów, z kategorii historia, których cena za sztukę przekracza 20zł.
W tym celu skorzystamy z funkcji SUMA.WARUNKÓW() i zapiszemy w niej dwa kryteria.
Pierwsze kryterium będzie dotyczyło kategorii, drugie natomiast ceny za sztukę.
=SUMA.WARUNKÓW(A2:A17;C2:C17;”=Historia”;D2:D17;”>20″)
Pierwszym argumentem jest zakres, w którym mamy liczbę artykułów. Drugim argumentem, jest zakres, w którym sprawdzamy czy książka należy do kategorii historia. Kryterium to, jest podane jako argument trzeci. Kolejne dwa argumenty są analogiczne do dwóch wcześniejszych, mianowicie – zakres, w którym sprawdzamy cenę za sztukę i kryterium, że cena ma być wyższa niż 20.
Przykład 5:
Jeśli chcielibyśmy sprawdzić łączną liczbę artykułów, dla wszystkich kategorii możemy, tak jak w przypadku funkcji SUMA.JEŻELI(), wykorzystać komórkę, w której zapiszemy odpowiednie kryterium. Zawsze należy pamiętać o zablokowaniu komórek z zakresami, które mają pozostać stałe. W przeciwnym razie funkcja nie będzie pracowała na odpowiednim zestawie danych, co w konsekwencji spowoduje niepoprawne obliczenia.
Funkcje SUMA.JEŻELI() i SUMA.WARUNKÓW() są bardzo użytecznymi funkcjami, w trakcie pracy z narzędziem jakim jest excel. Dzięki nim, jesteśmy w stanie sumować wartości w zależności od kryteriów, których może być więcej niż jedno. Ponadto, kryteria mogą dotyczyć nie tylko sumowanego zakresu, ale też innych zakresów. Ważne jest to, żeby rozmiar i kształt zakresów dotyczących kryteriów i sum był dokładnie taki sam! Korzystając z funkcji SUMA.JEŻELI() oraz SUMA.WARUNKÓW() do określenia kryteriów, możemy skorzystać z odwołań do innych komórek. Trzeba jedynie pamiętać o blokowaniu zakresów, jeśli jest to konieczne!
Chcesz uczyć się Excela na poziomie średniozaawansowanym? Sprawdź nasze kursy informatyczne, oferujemy w nich między innymi kurs Excel oraz kurs Excel dla średniozaawansowanych.
