SUMA.JEŻELI w Excelu

SUMA.JEŻELI służy w Excelu do tego, aby sumować wartości dla komórek, które zawierają specyficzne wartości.

SPIS TREŚCI

1 Krótkie wprowadzenie do funkcji SUMA.JEŻELI
2 SUMA.JEŻELI dla kolejnych wystąpień tej samej wartości
3 SUMA.JEŻELI dla przedziałów liczbowych
4 SUMA.JEŻELI z tylko dwoma argumentami
5 Sumowanie za pomocą SUMA.JEŻELI według fragmentu tekstu

Krótkie wprowadzenie do funkcji SUMA.JEŻELI

Wyobraź sobie tabelę Excela z listą pracowników, gdzie znajduje się kolumna ze stanowiskiem oraz kolumna z wynagrodzeniem. Chcesz sprawdzić ile zarabiają w sumie pracownicy na stanowisku managera. Chcesz więc znaleźć wszystkich managerów i dodać ich wynagrodzenia do siebie.

Funkcje Excela używają argumentów. W przypadku SUMA.JEŻELI jest ich 3:

- Argument 1 – podaj zakres komórek, w których chcesz szukać określonych wartości. Dla tych wartości będą sumowane liczby z argumentu nr 3. Zazwyczaj jest to kolumna tabeli.
- Argument 2 – podaj warunek, określający jakich komórek szukasz (np. zawierające konkretny tekst, liczby, daty).
- Argument 3 – podaj zakres komórek, w których znajdują się liczby do sumowania.

SUMA.JEŻELI dla kolejnych wystąpień tej samej wartości

Kiedy w Excelu stosować funkcję SUMA.JEŻELI

Bardzo często pracujemy na tabelach danych w Excelu. Najczęściej poszczególne kolumny tabel (czasem wiersze) zawierają kolejne rodzaje informacji, np. nazwa, imię, kwota, ilość, data.

Oprócz tego, że dane te można przeglądać, to dobrze jest też umieć je podsumować. Jeśli mamy 1.000 wierszy w tabeli, to przeglądanie wszystkich informacji jest trudne. Jedną z funkcji Excela, służących do takiego podsumowania, jest właśnie SUMA.JEŻELI.

Służy ona przede wszystkim do tego, żeby zsumować jakieś liczby/kwoty. Należy więc mieć w swoim Excelu jakąś kolumnę z liczbami (kwoty sprzedaży, kwoty kosztów, wynagrodzenia, liczba osób, ilość zgłoszeń, itp.). Jeśli chcemy zsumować wszystkie wartości z tej kolumny, to wystarczy prosta funkcja SUMA. SUMA.JEŻELI okazuje się wartościowa, jeśli chcemy zsumować tylko część tych danych – tylko te, które odpowiadają ustalonemu przez nas warunkowi.

Przykład zastosowania SUMA.JEŻELI w Excelu

W zadaniu widać informacje o sprzedaży produktów do różnych państw. Interesować nas będzie policzenie kwoty netto. Ale chcemy obliczyć kwotę netto dla każdego z państw z osobna – więc warunkiem będzie to, aby w kolumnie „Kierunek sprzedaży” znajdowała się nazwa właściwego państwa.

Rysunek 1. Kolumny użyte w funkcji SUMA.JEŻELI.

- Argument 1 – kolumna ze wszystkimi państwami. Zazwyczaj, blokuje się ten argument dolarami. Jeśli formuła będzie przeciągana do kolejnych państw, to te kolejne państwa wyszukiwane są dalej w tej samej tabeli, więc aby tabela się nie zmieniła (nie przesunęła), zablokuj ją.
- Argument 2 – nazwa tego państwa, którego szukamy w kolumnie z argumentu nr 1 i dla którego sumowane będą kwoty z argumentu nr 3.
- Argument 3 – kolumna z liczbami do sumowania, czyli „Kwota netto”. Excel sam się nie domyśli, które kwoty chcemy sumować, dlatego należy to wskazać wprost. Zazwyczaj blokuje się ten argument dolarami, z tego samego powodu co argument nr 1.

=SUMA.JEŻELI($I$3:$I$2621;Q3;$M$1:$M$2619)

Rysunek 2. SUMA.JEŻELI oblicza sumę dla każdego z Państw.

Ważne zasady stosowania SUMA.JEŻELI w Excelu

Stosując tę funkcję, pamiętaj o kilku zasadach:

- Argument nr 1 i argument nr 3 muszą mieć taką samą długość. Excel musi umieć „sparować” odpowiednie wartości z argumentu nr 1 z właściwymi liczbami z argumentu nr 3.
- Argument nr 1 i nr 3 mogą być całymi kolumnami arkusza. Pamiętaj jednak, żeby nad, ani pod Twoją tabelą nie było innych danych, które mogą zostać pomylone z właściwymi danymi – no bo jeśli zaznaczysz całą kolumnę, to wskazana wartość jest szukana od samej góry, do samego dołu.
- Pamiętaj o blokowaniu argumentów nr 1 i nr 3. Nie zawsze, ale zazwyczaj trzeba to zrobić.

Rysunek 3. Na rysunku widać błędne zaznaczenie kolumn. Jedna z nich zaczyna się w pierwszej komórce z danymi, a druga w pierwszym wierszu całego arkusza.

SUMA.JEŻELI dla przedziałów liczbowych

Warunkiem, pod którym sumuje się wartości, często jest pewien przedział – np. zsumuj wynagrodzenia osób w wieku powyżej 40 lat. Albo policz, ile produktów sprzedano po 4 marca 2013 roku. Przedziały te mogą dotyczyć zarówno liczb, jak i dat.

W zadaniu poniżej, znajduje się lista pracowników, wraz z ich wynagrodzeniami. Celem zadania, jest policzyć ile zarabiają w sumie osoby, które mają więcej niż 40 lat.

- Argument 1 – kolumna z wiekiem, który będzie można sprawdzić. Nie musi być zablokowana, bo formuła nie będzie nigdzie przeciągana.
- Argument 2 – warunek, który będzie przez Excela sprawdzany w argumencie nr 1 – czyli więcej niż 40 lat. Możesz to być zapisane na kilka sposobów – więcej informacji tutaj.
- Argument 3 – kolumna z liczbami do sumowania, czyli „Wynagrodzenie”. Nie musi być zablokowana, bo formuła nie będzie nigdzie przeciągana.

=SUMA.JEŻELI(D3:D499;G3;E3:E499)

Rysunek 4. Funkcja SUMA.JEŻELI, podsumowuje wynagrodzenia osób, mających więcej niż 40 lat.

SUMA.JEŻELI z tylko dwoma argumentami

Możesz pominąć używanie argumentu nr 3 w jednej sytuacji – gdy w argumencie nr 1 zaznaczona jest kolumna z liczbami i w tej kolumnie chcesz zarówno sprawdzać warunek, jak i z niej sumować wartości. Można tę samą kolumnę zaznaczyć i w argumencie nr 1 i w argumencie nr 3, ale twórcy Excela przygotowali ułatwienie polegające na tym, że argument nr 3 można już pominąć.

W zadaniu znajduje się lista pracowników wraz z ich wynagrodzeniami. Celem zadania jest policzyć, ile zarabiają w sumie osoby, których wynagrodzenie jest mniejsze od 3.000 zł.

Argument 1 – kolumna z wynagrodzeniami. Nie musi być zablokowana, bo formuła nie będzie nigdzie przeciągana. Są to jednocześnie kwoty sumowane, jeśli spełniony będzie warunek w argumencie nr 2.
Argument 2 – wskazanie warunku, sprawdzanego w argumencie nr 1.

=SUMA.JEŻELI(D:D;$E$2)

Rysunek 5. SUMA.JEŻELI bez argumentu nr 3.

Sumowanie za pomocą SUMA.JEŻELI według fragmentu tekstu

Czasami pojawia się potrzeba policzyć sumę dla wartości w jakiś sposób podobnych do siebie. Co to oznacza? Oznacza to, że mają one jakiś element wspólny w swojej nazwie. Można użyć wtedy znaków „*” lub „?” – służą one w Excelu do stworzenia mniej restrykcyjnej reguły.

W zadaniu widać kody produktów oraz ilość zamówionych sztuk. Produkty są określonych rodzajów. Należy policzyć, ile zamówiono sztuk każdego z rodzajów. Nie chcemy więc szukać szczegółowo – ile zamówiono określonych, konkretnych produktów, ale ile zamówiono sztuk jakiegoś rodzaju. Nazwy rodzaju są podane obok. Znajdują się ona także zawsze w nazwie produktu, na samym końcu. Należy więc zsumować wartości dla produktów kończących się określonym literami.

- Argument 1 – kolumna z kodami produktów. Formuła będzie przeciągana do czterech rodzajów (czterech komórek), więc należy zablokować ten argument dolarami.
- Argument 2 – warunek, który będzie sprawdzany w argumencie nr 1 – nie można wskazać po prostu komórki z nazwą rodzaju – tej nazwy, samej w sobie nie znajdziemy. Znajdziemy natomiast produkty, które ten rodzaj mają zapisany na końcu. Należy więc wskazać, że szukamy produktów, które na początku mają cokolwiek, a na końcu konkretny rodzaj.
- Argument 3 – kolumna z liczbami do sumowania, czyli „Wielkość zamówienia”. Formuła będzie przeciągana do czterech rodzajów (czterech komórek), więc należy zablokować ten argument dolarami.

=SUMA.JEŻELI($B$3:$B$494;"*"&F3;$C$3:$C$494)

Rysunek 6. SUMA.JEŻELI sprawdzająca zgodność tylko fragmentu tekstu.