SUMA.JEŻELI

SUMA.JEŻELI to funkcja arkusza kalkulacyjnego sumująca wartości spełniające jeden zadany warunek.

SUMA.JEŻELI jako narzędzie warunkowego dodawania

SUMA.JEŻELI jest funkcją arkusza kalkulacyjnego używaną wtedy, gdy nie wszystkie liczby z zakresu mają zostać uwzględnione w wyniku. Dodawanie odbywa się wyłącznie dla tych komórek, które odpowiadają wskazanemu kryterium. Dzięki temu można szybko podsumować sprzedaż wybranego produktu, koszty przypisane do konkretnego działu, liczbę godzin poświęconych na określone zadanie albo wartości zapisane przy danym statusie.

W praktyce funkcja pozwala połączyć wyszukiwanie warunku z obliczeniem sumy. Nie trzeba ręcznie zaznaczać pasujących wierszy ani tworzyć wielu pomocniczych działań. Wystarczy określić, gdzie ma być sprawdzany warunek, jaki warunek ma zostać spełniony oraz z którego zakresu powinny zostać pobrane liczby. Jeżeli zakres sumowania nie zostanie podany, arkusz zsumuje komórki z zakresu kryteriów.

Ogólny zapis funkcji wygląda następująco: SUMA.JEŻELI(zakres; kryterium; [suma_zakres]). Zakres oznacza obszar, w którym ma zostać sprawdzony warunek. Kryterium określa, czego funkcja ma szukać. Opcjonalny zakres sumowania wskazuje komórki zawierające wartości przeznaczone do dodania. W polskiej wersji arkusza argumenty zazwyczaj rozdziela się średnikami, chociaż ustawienia regionalne mogą wpływać na sposób zapisu formuły.

SUMA.JEŻELI a zakresy, kryteria i wynik formuły

Najprostszy przykład można oprzeć na tabeli, w której w kolumnie A znajdują się nazwy kategorii, a w kolumnie B kwoty. Formuła =SUMA.JEŻELI(A2:A20;"biuro";B2:B20) doda tylko te wartości z kolumny B, dla których odpowiadająca komórka w kolumnie A zawiera tekst „biuro”. Wiersze dotyczące innych kategorii zostaną pominięte. Kolejność wierszy nie ma znaczenia, ponieważ funkcja analizuje każdy element wskazanego zakresu.

Zakres kryteriów i zakres sumowania powinny mieć zgodny układ. Najbezpieczniej, gdy obejmują taką samą liczbę wierszy i odnoszą się do tych samych rekordów. Jeżeli kryteria znajdują się w przedziale A2:A20, a liczby w B2:B20, powiązanie jest czytelne i łatwe do sprawdzenia. Niezgodne zakresy mogą prowadzić do pomijania części danych, przesunięcia dopasowań albo wyniku, który nie odpowiada logicznej strukturze tabeli.

Kryterium może być zwykłym tekstem, liczbą, odwołaniem do komórki lub wyrażeniem z operatorem porównania. Przykładowo ">100" oznacza wartości większe od 100, "<=50" oznacza wartości mniejsze lub równe 50, a "<>zamknięte" wybiera wpisy różne od słowa „zamknięte”. Gdy operator ma zostać połączony z wartością znajdującą się w innej komórce, stosuje się łączenie tekstu, na przykład ">"&D1. Pozwala to zmieniać próg bez poprawiania samej formuły.

Przy kryteriach tekstowych przydatne są symbole wieloznaczne. Gwiazdka oznacza dowolny ciąg znaków, natomiast znak zapytania zastępuje jeden znak. Formuła =SUMA.JEŻELI(A2:A20;"komp*";B2:B20) może objąć teksty rozpoczynające się od „komp”, niezależnie od dalszej części wpisu. Takie rozwiązanie bywa użyteczne przy nazwach mających wspólny początek, lecz wymaga ostrożności, ponieważ może objąć więcej rekordów, niż pierwotnie zakładano.

Warto odróżnić SUMA.JEŻELI od funkcji SUMA. Zwykłe SUMA dodaje wskazane wartości bez filtrowania warunkiem. SUMA.JEŻELI wykonuje filtrowanie według jednego kryterium, a następnie oblicza wynik. Jeżeli potrzebne jest jednoczesne spełnienie kilku warunków, właściwszym rozwiązaniem jest SUMA.WARUNKÓW. Z kolei JEŻELI służy przede wszystkim do zwracania różnych wyników zależnie od warunku, a nie do bezpośredniego sumowania całego zestawu rekordów.

SUMA.JEŻELI przy typowych problemach z danymi

Jednym z częstych źródeł błędów są liczby zapisane jako tekst. Na ekranie mogą wyglądać jak wartości liczbowe, lecz arkusz nie zawsze potraktuje je jako liczby podczas sumowania. Przyczyną bywają zbędne spacje, nietypowe separatory dziesiętne, apostrof na początku wpisu albo dane skopiowane z zewnętrznego źródła. W takiej sytuacji formuła może zwrócić zaniżony wynik, mimo że zakres i kryterium wydają się poprawne.

Problemy mogą wynikać również z dodatkowych spacji w kryteriach tekstowych. Wpisy „Warszawa” oraz „Warszawa ” mogą być wizualnie trudne do odróżnienia, ale dla arkusza mogą stanowić różne ciągi znaków. Przed zastosowaniem funkcji warto oczyścić dane, usunąć zbędne odstępy i ujednolicić zapis nazw. Pomocne może być także sprawdzenie, czy wszystkie warianty wielkości liter, skrótów oraz znaków diakrytycznych są traktowane zgodnie z założeniem.

Nieprawidłowy rezultat może być skutkiem błędnego zakresu. Jeżeli formuła sprawdza A2:A20, ale sumuje B2:B19, ostatni rekord nie zostanie właściwie powiązany z kryterium. Przy rozbudowanych tabelach dobrze jest zaznaczać zakresy świadomie i kontrolować ich początek oraz koniec. Warto również sprawdzić, czy pomiędzy nagłówkiem a danymi nie znajdują się puste wiersze albo dodatkowe podsumowania, które mogą zmienić interpretację tabeli.

Jeżeli kryterium jest pobierane z komórki, należy zweryfikować jej zawartość. Komórka może zawierać niewidoczną spację, liczbę przechowywaną jako tekst albo inny format niż pozostałe dane. Przy operatorach porównania często zapomina się o połączeniu operatora z odwołaniem do komórki. Sam zapis odwołujący się do progu nie zawsze wystarczy, ponieważ warunek powinien zostać zbudowany jako tekst zawierający operator i wartość.

Przy dużych zestawach danych warto zadbać o jednolitą strukturę. Kolumna kryteriów powinna przechowywać podobne typy informacji, a kolumna sumowania przede wszystkim wartości liczbowe. Łączenie dat, opisów i kwot w jednym polu utrudnia analizę i zwiększa ryzyko niejednoznacznego wyniku. Czytelna tabela ogranicza liczbę poprawek oraz ułatwia późniejsze sprawdzanie formuł.

SUMA.JEŻELI w codziennej pracy z arkuszem

Funkcja może służyć do tworzenia zestawień według osoby, rodzaju wydatku, etapu realizacji, miesiąca albo oznaczenia wprowadzonego w tabeli. Przykładowo, jeśli w jednej kolumnie zapisano statusy „nowe”, „w toku” i „zamknięte”, można obliczyć łączną wartość wyłącznie dla jednego z tych statusów. Podobnie można sumować kwoty przypisane do wybranego kontrahenta, pod warunkiem że nazwy są zapisane spójnie.

Przy datach kryterium może opierać się na granicach czasowych. Możliwe jest zsumowanie wartości wcześniejszych niż określona data, późniejszych od niej albo równych wskazanemu dniowi. Trzeba jednak pamiętać, że data może być przechowywana jako prawidłowa wartość daty lub jako tekst. W drugim przypadku porównanie może nie działać zgodnie z oczekiwaniem. Dobrą praktyką jest sprawdzenie formatu dat w kilku przykładowych komórkach przed przygotowaniem całego raportu.

Odwołanie do komórki z kryterium zwiększa elastyczność zestawienia. Zamiast tworzyć osobną formułę dla każdej kategorii, można umieścić nazwę kategorii w komórce i odwołać się do niej w formule. Po zmianie nazwy wynik zostanie przeliczony automatycznie. Przy kopiowaniu formuły w dół lub w bok należy rozważyć użycie odwołań względnych i bezwzględnych, aby zakres danych nie przesuwał się przypadkowo.

W tabelach, które mają być regularnie uzupełniane, dobrze jest przewidzieć miejsce na nowe wiersze. Można korzystać z tabel arkuszowych, nazwanych zakresów albo odpowiednio dobranych odwołań. Istotne pozostaje to, aby nowo dodane rekordy były objęte zakresem analizowanym przez SUMA.JEŻELI. Formuła działająca na stałym, zbyt krótkim zakresie może po pewnym czasie przestać uwzględniać najnowsze wpisy.

Przed użyciem wyniku w raporcie warto wykonać prostą kontrolę. Należy policzyć kilka pasujących pozycji ręcznie, porównać rezultat z filtrem tabeli oraz sprawdzić rekordy graniczne, na przykład dokładnie równe progowi. Jeżeli wynik ma służyć do decyzji lub rozliczenia, przydatne jest także zapisanie informacji o tym, jaki zakres i jakie kryterium zostały użyte. Ułatwia to późniejszą weryfikację bez konieczności odtwarzania całej logiki arkusza.

Kiedy sprawdzić SUMA.JEŻELI i zgłosić problem do serwisu

Do serwisu warto zgłosić się wtedy, gdy arkusz działa niestabilnie, formuły zwracają niespójne wyniki, plik nie otwiera się poprawnie albo dane znikają po zapisaniu. Wcześniej można sprawdzić kopię dokumentu, nazwę pliku, lokalizację danych oraz to, czy problem występuje także w innym arkuszu. Gdy błąd pojawia się tylko w jednym pliku, przyczyną może być jego struktura, uszkodzony zakres lub niezgodność formatu. Jeżeli podobne zachowanie występuje w wielu dokumentach, warto uwzględnić również stan programu i komputera.

Przydatne jest przygotowanie krótkiego opisu: jaki wynik powinien zostać uzyskany, jaki wynik pojawia się obecnie, na jakim zakresie działa formuła oraz po jakiej operacji wystąpiła nieprawidłowość. Nie należy udostępniać danych poufnych, jeśli nie jest to konieczne. Można przygotować kopię z przykładowymi wartościami, zachowując układ tabeli i formułę. Taki materiał ułatwia odtworzenie problemu oraz rozdzielenie błędu arkusza od problemu z oprogramowaniem.

W przypadku kłopotów ze sprzętem lub oprogramowaniem pomoc może zostać zgłoszona do firmy Warszawskie Pogotowie Komputerowe 24/7. Przed przekazaniem urządzenia warto zanotować komunikaty pojawiające się na ekranie, zabezpieczyć ważne pliki i opisać, czy problem dotyczy jednego dokumentu, konkretnego programu czy całego systemu. Dzięki temu łatwiej ocenić, czy potrzebna jest korekta formuły, uporządkowanie danych, naprawa programu, czy szersza diagnostyka komputera.