Funkcja SUMA.JEŻELI w Excelu i sumuje według wielu warunków

Excel to niesamowicie funkcjonalny program. Nawet wbudowany zestaw funkcji wystarcza do wykonania niemal każdego zadania. A poza standardowymi, znanymi wielu, są też takie, o których mało kto słyszał. Ale jednocześnie nie przestają być przydatne. Mają węższą specjalizację i nie zawsze są potrzebne. Ale jeśli o nich wiesz, to w krytycznym momencie mogą być bardzo przydatne.

Dzisiaj porozmawiamy o jednej z takich funkcji − SUMMESLIMN.

Jeśli użytkownik staje przed zadaniem zsumowania kilku wartości, koncentrując się na określonych kryteriach, konieczne jest skorzystanie z funkcji SUMMESLIMN. Formuła korzystająca z tej funkcji przyjmuje te warunki jako argumenty, następnie sumuje wartości, które je spełniają, a następnie znaleziona wartość jest wprowadzana do komórki, w której jest zapisana. 

SUMIFS Szczegółowy opis funkcji

Przed rozważeniem funkcji SUMMESLIMN, musisz najpierw zrozumieć, jaka jest prostsza wersja – SUMMESLI, ponieważ na nim opiera się rozważana przez nas funkcja. Każdy z nas prawdopodobnie zna już dwie funkcje, które są często używane – SUMA (dokonuje sumowania wartości) i JEŚLI (testuje wartość pod kątem określonego warunku).

Jeśli je połączysz, otrzymasz kolejną funkcję – SUMMESLI, który sprawdza dane pod kątem kryteriów określonych przez użytkownika i sumuje tylko te liczby, które spełniają te kryteria. Jeśli mówimy o angielskiej wersji programu Excel, ta funkcja nazywa się SUMA.JEŻELI. W prostych słowach nazwa -językowa jest bezpośrednim tłumaczeniem anglojęzycznej. Ta funkcja może być używana do różnych celów. W szczególności może być stosowany jako alternatywa VPRczyli zapisz

Główna różnica między funkcją SUMMESLIMN  ze zwykłej funkcji SUMMESLI jest to, że stosuje się kilka kryteriów. Jego składnia jest na pierwszy rzut oka dość złożona, ale po bliższym przyjrzeniu się okazuje się, że logika tej funkcji jest bardzo prosta. W pierwszej kolejności należy wybrać zakres, w którym dane będą sprawdzane, a następnie ustalić warunki zgodności z którymi zostanie przeprowadzona analiza. I taką operację można wykonać w dość dużej liczbie warunków.

Sama składnia to:

SUMA.WARUNKI(suma_zakres; warunek_zakres1, warunek1, [zakres_warunków2, warunek2], …)

W odpowiednich miejscach należy umieścić odpowiednie w konkretnym przypadku tablice komórek. 

Przyjrzyjmy się argumentom bardziej szczegółowo:

1. Sum_zakres. Ten argument, jak również zakres warunku 1 i warunku 1, jest wymagany. Jest to zestaw komórek, które należy zsumować.
2. Condition_range1. Jest to zakres, w którym sprawdzany będzie warunek. Jest sparowany z kolejnym argumentem – Warunek1. Sumowanie wartości odpowiadających kryterium odbywa się w ramach komórek określonych w poprzednim argumencie.
3. Warunek1. Ten argument określa kryteria, które mają być sprawdzane. Można to ustawić np. w ten sposób: „>32”.
4. Zakres warunku 2, warunek 2… Tutaj następujące warunki są ustawiane w ten sam sposób. Jeśli trzeba określić więcej niż kilka warunków, dodawane są argumenty Zakres warunków 3 i Warunek 3. Składnia jest taka sama dla następujących argumentów.

Funkcja umożliwia maksymalne przetwarzanie do 127 par warunków i zakresów. 

Możesz go używać w kilku obszarach naraz (podamy tylko kilka, lista jest właściwie jeszcze dłuższa):

1. Księgowość. Na przykład dobrze jest skorzystać z funkcji SUMMESLIMN do tworzenia raportów podsumowujących, na przykład w ujęciu kwartalnym za wydatki powyżej określonej kwoty. Lub utwórz raport dotyczący jednego produktu z określonej kategorii cenowej.
2. Zarządzanie sprzedażą. Tutaj również funkcja może być bardzo przydatna. Na przykład mamy do czynienia z zadaniem zsumowania tylko kosztów towarów, które zostały sprzedane określonemu klientowi w określonym czasie. A w takiej sytuacji funkcja SUMMESLIMN może być bardzo pomocne.
3. Edukacja. Podamy dziś więcej praktycznych przykładów z tego obszaru. W szczególności możesz go użyć, aby uzyskać podsumowanie ocen uczniów. Możesz wybrać dla pojedynczego przedmiotu lub dla poszczególnych ocen. Osoba może od razu ustalić kilka kryteriów, według których zostanie wybrana ocena, co jest bardzo wygodne i pozwala zaoszczędzić sporo czasu.

Jak widać, wachlarz zastosowań tej funkcji jest bardzo szeroki. Ale to nie jedyna zaleta. Przyjrzyjmy się jeszcze kilku zaletom tej funkcji:

1. Możliwość ustawienia wielu kryteriów. Dlaczego jest to zaleta? Możesz użyć normalnej funkcji SUMMESLI! A wszystko dlatego, że jest wygodny. Nie ma potrzeby przeprowadzania oddzielnych obliczeń dla każdego z kryteriów. Wszystkie akcje można zaprogramować z wyprzedzeniem, nawet wcześniej. jak zostanie utworzona tabela danych. To świetna oszczędność czasu.
2. Automatyzacja. Współczesność to wiek automatyzacji. Tylko osoba, która wie, jak prawidłowo zautomatyzować swoją pracę, może dużo zarobić. Dlatego umiejętność opanowania Excela i funkcji SUMMESLIMN w szczególności jest tak ważna dla każdej osoby, która chce budować karierę. Znajomość jednej funkcji pozwala na wykonywanie kilku czynności jednocześnie, jako jedną. I tutaj przechodzimy do następnej korzyści tej funkcji.
3. Oszczędzanie czasu. Właśnie dlatego, że jedna funkcja wykonuje kilka zadań jednocześnie.
4. Prostota. Pomimo tego, że składnia jest na pierwszy rzut oka dość ciężka ze względu na jej nieporęczność, w rzeczywistości logika tej funkcji jest bardzo prosta. Najpierw wybierany jest zakres danych, a następnie zakres wartości, które zostaną sprawdzone pod kątem zgodności z określonym warunkiem. I oczywiście sam warunek musi być również sprecyzowany. I tak kilka razy. W rzeczywistości funkcja ta opiera się tylko na jednej konstrukcji logicznej, co czyni ją prostszą niż dobrze znana VPR pomimo tego, że może być używany do tych samych celów, również biorąc pod uwagę większą liczbę kryteriów. 

Funkcje korzystania z funkcji SUMIFS

Istnieje kilka funkcji korzystania z tej funkcji, na które należy zwrócić uwagę. Przede wszystkim ta funkcja ignoruje zakresy z ciągami tekstowymi lub wartościami null, ponieważ te typy danych nie mogą być dodawane razem we wzorcu arytmetycznym, a jedynie łączone jak ciągi. Ta funkcja nie może tego zrobić. Musisz również zwrócić uwagę na następujące warunki:

1. Możesz użyć tego typu wartości jako warunków zaznaczania komórek, aby dalej dodawać zawarte w nich wartości: wartości liczbowe, wyrażenia logiczne, odwołania do komórek i tak dalej. 
2. Jeżeli sprawdzany jest tekst, wyrażenia logiczne lub znaki matematyczne, to takie kryteria określa się w cudzysłowie.
3. Nie można używać terminów dłuższych niż 255 znaków.
4. Możliwe jest zastosowanie przybliżonych kryteriów doboru wartości za pomocą symboli wieloznacznych. Znak zapytania służy do zastąpienia pojedynczego znaku, a znak mnożenia (gwiazdka) jest potrzebny do zastąpienia wielu znaków. 
5. Wartości logiczne znajdujące się w zakresie sumowania są automatycznie konwertowane na wartości liczbowe zgodnie z ich typem. Tak więc wartość „PRAWDA” zamienia się w jeden, a „FAŁSZ” – w zero. 
6. Jeśli #ARG! błąd pojawia się w komórce, oznacza to, że liczba komórek w zakresach warunku i sumy jest różna. Musisz upewnić się, że rozmiary tych argumentów są takie same. 

Przykłady użycia funkcji SUMA

Funkcjonować SUMMESLIMN nie tak skomplikowane, jak się wydaje na pierwszy rzut oka, okazuje się. Ale dla większej jasności spójrzmy na kilka praktycznych przykładów wykorzystania tej funkcji SUMMESLIMN. To znacznie ułatwi zagłębienie się w temat.

Zakres dynamiki sumowania warunków

Zacznijmy więc od pierwszego przykładu. Załóżmy, że mamy tabelę zawierającą informacje o tym, jak uczniowie radzą sobie z programem nauczania danego przedmiotu. Istnieje zestaw ocen, wykonanie oceniane jest w 10-stopniowej skali. Zadanie polega na znalezieniu oceny z egzaminu tych uczniów, których nazwisko zaczyna się na literę A, a ich minimalna liczba punktów to 5.

Stół wygląda tak.

Abyśmy mogli obliczyć łączny wynik w oparciu o opisane powyżej kryteria, musimy zastosować następujący wzór.

Opiszmy bardziej szczegółowo argumenty:

1. C3:C14 to nasz zakres sumowania. W naszym przypadku pokrywa się to z zakresem warunków. Z niego zostaną wybrane punkty wykorzystywane do obliczenia kwoty, ale tylko te, które spełniają nasze kryteria.
2. „>5” to nasz pierwszy warunek.
3. B3:B14 to drugi zakres sumowania, który jest przetwarzany w celu spełnienia drugiego kryterium. Widzimy, że nie ma zbieżności z zakresem sumowania. Na tej podstawie wnioskujemy, że zakres sumowania i zakres warunku mogą, ale nie muszą być identyczne. 
4. „A*” to drugi zakres, który określa dobór ocen tylko dla tych uczniów, których nazwisko zaczyna się na A. W naszym przypadku gwiazdka oznacza dowolną liczbę znaków. 

Po obliczeniach otrzymujemy poniższą tabelę.

Jak widać, formuła sumowała wartości na podstawie zakresu dynamicznego i na warunkach określonych przez użytkownika.

Selektywne sumowanie według stanu w Excelu

Załóżmy teraz, że chcemy uzyskać informacje o tym, jakie towary zostały wysłane do jakich krajów w ciągu ostatniego kwartału. Następnie znajdź łączne przychody z przesyłek za lipiec i sierpień.

Sam stół wygląda tak. 

Aby określić ostateczny wynik, potrzebujemy takiej formuły.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

W wyniku obliczeń przeprowadzonych tą formułą otrzymujemy następujący wynik.

Uwaga! Ta formuła wygląda na dość dużą, mimo że zastosowaliśmy tylko dwa kryteria. Jeśli zakres danych jest taki sam, możesz znacznie skrócić długość formuły, jak pokazano poniżej.

Funkcja SUMIFS do sumowania wartości w wielu warunkach

Podajmy teraz inny przykład do zilustrowania. W tym przypadku tabela pozostaje taka sama jak w poprzednim przypadku. 

Używamy następującej formuły (ale zapisujemy ją jako formułę tablicową, to znaczy wprowadzamy ją za pomocą kombinacji klawiszy CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Po funkcji SUMMESLIMN zsumuje tablicę wartości na podstawie kryteriów określonych w formule (czyli kraje Chiny i Gruzja), wynikowa tablica jest sumowana przez zwykłą funkcję SUMA, który jest zapisany jako formuła tablicowa.

Jeśli warunki zostały przekazane jako stała tablicowa dla więcej niż jednej pary, formuła da niepoprawny wynik.

Przyjrzyjmy się teraz tabeli zawierającej sumy.

Jak widać, nam się udało. Tobie też na pewno się uda. Wielki sukces na tym polu. Jest to bardzo prosta funkcja, którą może zrozumieć osoba, która dopiero weszła na ścieżkę nauki Excela. I już wiemy, że funkcja SUMMESLIMN pozwala być skutecznym w każdej dziedzinie działalności, od księgowości po nawet edukację. Nawet jeśli budujesz karierę w jakiejkolwiek innej dziedzinie, która nie została opisana powyżej, ta funkcja nadal pomoże Ci zarabiać pieniądze. Dlatego jest cenna.

Co najważniejsze, pozwala zaoszczędzić czas, który jest niestety ograniczonym zasobem. Wydawałoby się, że na zastosowanie dwóch funkcji jest kilka sekund, ale gdy trzeba wykonać ogromną liczbę powtarzalnych operacji, te sekundy sumują się w godziny, które można przeznaczyć na coś innego. Dlatego zalecamy ćwiczenie korzystania z tej funkcji. Co więcej, jest to niezwykle proste.