W tym samouczku wyjaśniono różnicę między funkcjami SUMA.JEŻELI i SUMA.WARUNKÓW pod względem ich składni i użycia i zawiera szereg przykładów formuł do sumowania wartości z wieloma kryteriami AND / OR w programach Excel 2019, 2016, 2013, 2010, 2007, 2003 i wcześniejszych.
Jak każdy wie, Microsoft Excel udostępnia tablicę funkcji do wykonywania różnych obliczeń na danych. Kilka artykułów temu omówiliśmy LICZ.JEŻELI i LICZ.WARUNKI, które są przeznaczone do liczenia komórek odpowiednio na podstawie jednego warunku i kilku warunków. W zeszłym tygodniu omówiliśmy Excel SUMIF, który dodaje wartości spełniające określone kryteria. Teraz nadszedł czas, aby przejść do liczby mnogiej wersji SUMA.JEŻELI – Excel SUMA.WARUNKÓW, która umożliwia sumowanie wartości według wielu kryteriów.
Osoby zaznajomione z funkcją SUMA.JEŻELI mogą pomyśleć, że przekształcenie jej na SUMA.WARUNKÓW wymaga tylko dodatkowego wysiłku „S” i kilka dodatkowych kryteriów. Wydawałoby się to całkiem logiczne… ale „logiczne” nie zawsze tak jest w przypadku firmy Microsoft 🙂
- Funkcja SUMA.JEŻELI – składnia & użycie
- funkcja SUMA.WARUNKÓW – składnia & użycie
- Jak używać funkcji SUMIFS w programie Excel (logika AND)
- Excel SUMIF z wieloma kryteriami (logika OR)
- Excel SUMIFS z wieloma warunkami LUB
- Używanie Excel SUM w formułach tablicowych
Funkcja SUMA.JEŻELI programu Excel – składnia & użycie
Funkcja SUMA.JEŻELI służy do warunkowego sumowania wartości na podstawie pojedynczych kryteriów. Szczegółowo omówiliśmy składnię SUMIF w poprzednim artykule, więc pozwólcie, że przedstawię teraz krótkie podsumowanie.
-
range– zakres komórek do oceny według kryteriów, wymagany. -
criteria– warunek, który musi zostać spełniony, wymagane. -
sum_range– komórki do zsumowania, jeśli warunek jest spełniony, opcjonalne.
Jak widać, składnia funkcji Excel SUMA.JEŻELI dopuszcza tylko jeden warunek. Mimo to mówimy, że Excel SUMA.JEŻELI może służyć do sumowania wartości z wieloma kryteriami. Jak to możliwe? Dodając wyniki kilku funkcji SUMA.JEŻELI i używając formuł SUMA.JEŻELI z kryteriami tablicowymi, jak pokazano w poniższych przykładach.
Funkcja SUMA.JEŻELI programu Excel – składnia & użycie
Używasz funkcji SUMA.WARUNKÓW w programie Excel, aby znaleźć warunkową sumę wartości na podstawie wielu kryteriów. Funkcja SUMA.WARUNKÓW została wprowadzona w programie Excel 2007, dzięki czemu można jej używać we wszystkich nowoczesnych wersjach programu Excel 2019, 2016, 2013, 2010 i 2007.
W porównaniu z funkcją SUMA.JEŻELI składnia SUMA.WARUNKÓW jest nieco bardziej złożona :
Pierwsze 3 argumenty są obowiązkowe, dodatkowe zakresy i powiązane z nimi kryteria są opcjonalne.
-
sum_range– wymagana jest jedna lub więcej komórek do zsumowania. Może to być pojedyncza komórka, zakres komórek lub nazwany zakres. Sumowane są tylko komórki z liczbami; puste i wartości tekstowe są ignorowane. -
criteria_range1– wymagany jest pierwszy zakres do oceny według powiązanych kryteriów. -
criteria1– pierwszy warunek, który należy spełnić, wymagany. Możesz podać kryteria w postaci liczby, wyrażenia logicznego, odwołania do komórki, tekstu lub innej funkcji programu Excel. Na przykład możesz użyć takich kryteriów, jak 10, „> = 10″, A1, „wiśnie” lub DZIŚ (). -
criteria_range2, criteria2, …– są to dodatkowe zakresy i związane z nimi kryteria, opcjonalne. W formułach SUMIFS można użyć maksymalnie 127 par zakres / kryteria.
A teraz niech ” s spójrz na formułę SUMA.WARUNKÓW programu Excel z dwoma warunkami. Załóżmy, że masz tabelę zawierającą partie owoców od różnych dostawców. Nazwy owoców znajdują się w kolumnie A, nazwy dostawców w kolumnie B i ilość w kolumnie C. Chcesz poznać sumę kwot odnoszących się do danego owocu i dostawcy np wszystkie jabłka dostarczone przez Petea.
Kiedy „uczysz się czegoś nowego”, zawsze dobrze jest zacząć od prostych rzeczy. Zatem na początek zdefiniujmy wszystkie argumenty dla naszej formuły SUMA.WARUNKÓW:
- zakres_sumowania – C2: C9
- zakres_kryteriów1 – A2: A9
- kryteria1 – „jabłka”
- kryteria_zakres2 – B2: B9
- kryteria2 – „Pete”
Teraz zbierz powyższe parametry i otrzyma następującą formułę SUMIFS:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Aby doprecyzować formułę dalej, możesz zastąpić kryteria tekstowe „jabłka” i „Pete” odwołaniami do komórek.W tym przypadku nie będziesz musiał zmieniać wzoru w celu obliczenia ilości innych owoców od innego dostawcy:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Używanie funkcji SUMA.JEŻELI i SUMA.JEŻELI w programie Excel – rzeczy do zapamiętania
Ponieważ celem tego samouczka jest omówienie wszystkich możliwych sposobów sumowania wartości według kilku warunków, omówimy przykłady formuł z obiema funkcjami – Excel SUMA.WARUNKÓW i SUMA.JEŻELI z wieloma kryteriami. Aby używać ich poprawnie, musisz jasno zrozumieć, co łączy te dwie funkcje i czym się różnią.
Chociaż część wspólna jest jasna – podobne przeznaczenie i parametry – różnice nie są tak oczywiste, ale bardzo istotne.
1. Kolejność argumentów
W funkcjach SUMA.JEŻELI i SUMA.WARUNKÓW kolejność argumentów jest różna. W szczególności, zakres sumy jest pierwszym parametr w SUMIFS, ale jest trzeci w SUMIF.
Na pierwszy rzut oka może się wydawać, że Microsoft celowo komplikuje krzywą uczenia się swoich użytkowników. eee, po bliższym przyjrzeniu się, zobaczysz uzasadnienie tego. Chodzi o to, że suma_zakres jest opcjonalna w SUMA.JEŻELI. Jeśli go pominiesz, nie ma problemu, twoja formuła SUMA.JEŻELI zsumuje wartości z zakresu (pierwszy parametr).
W SUMA.WARUNKACH suma_zakres jest bardzo ważna i obowiązkowa, dlatego jest na pierwszym miejscu. Zapewne chłopaki z Microsoftu myśleli, że po dodaniu dziesiątej lub setnej pary zakres / kryteria ktoś mógłby zapomnieć o określeniu zakresu do zsumowania 🙂
2. Zakres sumy i zakres kryteriów powinny mieć taką samą wielkość
W funkcji SUMA.JEŻELI argument sum_zakres niekoniecznie musi mieć taki sam rozmiar jak argument zakresu, o ile górna lewa komórka znajduje się po prawej stronie. W programie Excel SUMIFS każdy zakres kryteriów musi zawierać taką samą liczbę wierszy i kolumn, jak parametr sum_range.
Na przykład =SUMIF(A2:A9,F1,C2:C18) zwróci prawidłowy wynik, ponieważ program Excel uwzględnia tylko górna lewa komórka w argumencie sum_range (C2 w tym przykładzie, która jest poprawna), a następnie zawiera tyle kolumn i wierszy, ile zawiera argument zakresu.
W porządku, wystarczająca strategia (tj. teoria) przejdźmy do taktyki (np. przykłady formuł:)
Jak używać SUMA.WARUNKÓW w Excelu – przykłady formuł
Przed chwilą omawialiśmy prostą formułę SUMA.WARUNKÓW z dwoma tekstami Kryteria. W ten sam sposób można używać funkcji SUMA.WARUNKÓW programu Excel z wieloma kryteriami wyrażonymi przez liczby, daty, wyrażenia logiczne i inne funkcje programu Excel.
Przykład 1. Excel SUMIFS z operatorami porównania
Załóżmy, że w naszej tabeli dostawców owoców chcesz zsumować wszystkie dostawy dokonane przez Mikea w ilości 200 lub więcej. Aby to zrobić, użyj operatora porównania „większe niż o r równe „(> =) w kryteriach2 i uzyskaj następującą formułę SUMA.WARUNKÓW:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Omawiając funkcję Excel SUMIF, omówiliśmy szczegółowo wszystkie możliwe operatory porównania, te same operatory może być używany w kryteriach SUMA.WARUNKÓW. Na przykład poniższa formuła zwraca sumę wszystkich wartości w komórkach C2: C9, które są większe lub równe 200 i mniejsze lub równe 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Przykład 2. Używanie funkcji SUMA.WARUNKÓW Excel z datami
Jeśli chcesz zsumować wartości z wieloma kryteriami w oparciu o bieżącą datę, użyj funkcji DZIŚ () w SUMA.WARUNKÓW kryteria, jak pokazano poniżej. Poniższa formuła sumuje wartości w kolumnie D, jeśli odpowiednia data w kolumnie C przypada w ciągu ostatnich 7 dni, w tym dnia dzisiejszego:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
W podobny sposób możesz użyć funkcji SUMA.JEŻELI programu Excel do zsumowania wartości w podanym zakresie dat. Na przykład poniższa formuła SUMA.WARUNKÓW dodaje wartości w komórkach C2: C9, jeśli data w kolumnie B przypada między 1 października 2014 r. A 31 października 2014 r. Włącznie.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Ten sam wynik można osiągnąć, obliczając różnicę dwóch funkcji SUMA.JEŻELI, jak pokazano w tym przykładzie – Jak używać funkcji SUMA.JEŻELI do sumowania wartości w danym zakresie dat. Jednak funkcja SUMA.WARUNKÓW programu Excel jest znacznie łatwiejsza i bardziej zrozumiała, prawda?
Przykład 3. SUMA.WARUNKÓW programu Excel z pustymi i niepustymi komórkami
Analizując raporty i inne dane, często trzeba zsumować wartości odpowiadające pustym lub niepustym komórkom.
A teraz zobaczmy, jak można użyć formuły SUMA.WARUNKÓW z kryteriami „puste” i „niepuste” w rzeczywistych danych.
Załóżmy, że masz datę zamówienia w kolumnie B, datę dostawy w kolumnie C i ilość. w kolumnie D. Jak znaleźć całkowitą liczbę produktów, które nie zostały jeszcze dostarczone? Oznacza to, że chcesz poznać sumę wartości odpowiadających niepustym komórkom w kolumnie B i pustym komórkom w kolumnie C.
Rozwiązaniem jest użycie formuły SUMA.WARUNKÓW z 2 kryteriami:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Używanie Excela SUMA.JEŻELI z wieloma kryteriami LUB
Jak wspomniano na początku tego samouczka, funkcja SUMA.WARUNKÓW została zaprojektowana z logiką AND. Ale co, jeśli musisz zsumować wartości z wieloma kryteriami OR, tj. Gdy przynajmniej jeden z warunków jest spełniony?
Przykład 1. SUMA.JEŻELI + SUMA
Najprostszym rozwiązaniem jest sumowanie wyniki zwrócone przez kilka funkcji SUMA.JEŻELI. Na przykład poniższy wzór pokazuje, jak znaleźć całkowitą liczbę produktów dostarczonych przez Michała i Jana:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Jak widać, pierwsza funkcja SUMA.JEŻELI dodaje ilości odpowiadające „Michałowi”, druga funkcja SUMA.JEŻELI zwraca kwoty związane z „Janem”, a następnie dodajesz te dwie liczby.
Przykład 2. SUMA & SUMA.JEŻELI z argumentem tablicowym
Powyższe rozwiązanie jest bardzo proste i może wykonać zadanie szybko, gdy są tylko kilka kryteriów. Jednak formuła SUMA.JEŻELI + SUMA.JEŻELI może ogromnie wzrosnąć, jeśli chcesz zsumować wartości z wieloma warunkami LUB. W takim przypadku lepszym podejściem jest użycie argumentu kryteriów tablicowych w funkcji SUMA.JEŻELI. Przeanalizujmy teraz to podejście.
Możesz zacząć od wypisania wszystkich warunków oddzielonych przecinkami, a następnie zawrzeć wynikową listę rozdzieloną przecinkami w {nawiasach klamrowych}, co technicznie nazywa się tablicą.
Argument tablicowy składający się z 3 wartości zmusza twoją formułę SUMA.JEŻELI do zwrócenia trzech oddzielnych wyników, ale ponieważ piszemy formułę w jednej komórce, zwróci ona tylko pierwszy wynik – tj. sumę produktów dostarczonych przez Jana . Aby to podejście oparte na kryteriach tablic zadziałało, musisz użyć jeszcze jednej małej sztuczki – zawrzeć formułę SUMA.JEŻELI w funkcji SUMA, na przykład:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Jak widać, kryteria tablicowe sprawiają, że formuła jest znacznie bardziej zwarta w porównaniu z SUMA.JEŻELI + SUMA.JEŻELI i pozwala dodać tyle wartości, ile chcesz w tablicy.
To podejście działa zarówno z liczbami, jak i wartościami tekstowymi. Na przykład, jeśli zamiast nazw dostawców w kolumnie C, masz identyfikatory dostawców, takie jak 1, 2, 3 itd. ., Twoja formuła SUMIF wyglądałaby podobnie do tego:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
W przeciwieństwie do wartości tekstowych, liczby nie muszą być ujęte w podwójne cudzysłowy w tablica argumentów.
Przykład 3. SUMPRODUCT & SUMIF
W przypadku, gdy preferowanym sposobem jest umieszczenie kryteriów w niektórych komórkach, a nie określ je bezpośrednio w formule, możesz użyć SUMA.JEŻELI w połączeniu z funkcją SUMA.RODUKTU, która mnoży składniki w podanych tablicach i zwraca sumę tych iloczynów.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Gdzie G2: G4 to komórki zawierające kryteria, w naszym przypadku nazwy dostawców, jak pokazano na poniższym zrzucie ekranu.
Ale oczywiście nic nie stoi na przeszkodzie wypisując wartości w kryteriach tablicowych funkcji SUMA.JEŻELI, jeśli chcesz:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
Wynik zwracany przez obie formuły będzie identyczny z tym, co widać na zrzucie ekranu:
Excel SUMIFS z wiele kryteriów LUB
Jeśli chcesz warunkowo sumować wartości w programie Excel nie tylko z wieloma warunkami LUB, ale z kilkoma zestawami warunków, będziesz musiał użyć SUMA.WARUNKÓW zamiast SUMA.JEŻELI. Formuły będą bardzo podobne do tego, co przed chwilą omówiliśmy.
Jak zwykle, przykład może pomóc lepiej zilustrować ten punkt. W naszej tabeli dostawców owoców dodajmy datę dostawy (kolumna E) i znajdź całkowitą ilość dostarczoną przez Mikea, Johna i Petea w październiku.
Przykład 1. SUMY + SUMA.WARUNKÓW
Wzór utworzony przez to podejście zawiera wiele powtórzeń i wyglądów uciążliwe, ale łatwe do zrozumienia i, co najważniejsze, działa 🙂
Przykład 2. SUMA & SUMIFS z argumentem tablicowym
Próbowałem wyjaśnić istotę tego podejścia w przykładzie SUMA.JEŻELI, więc teraz możemy po prostu skopiować tę formułę, zmienić kolejność argumentów (jak pamiętacie, jest inna w SUMA.JEŻELI i SUMA.JEŻELI) i dodać dodatkowe kryteria. wynikowa formuła jest bardziej zwarta niż SUMA.WARUNKÓW + SUMA.WARUNKÓW:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
Wynik zwracany przez tę formułę jest dokładnie taki sam jak w powyższy zrzut ekranu.
Przykład e 3. SUMPRODUCT & SUMIFS
Jak pamiętasz, podejście SUMPRODUCT różni się od poprzednich dwóch tym, że każde kryterium wpisujesz w osobnej komórce raczej określają je bezpośrednio w formule. W przypadku kilku zestawów kryteriów funkcja SUMPRODUCT nie będzie wystarczająca i będziesz musiał również użyć ISNUMBER i MATCH.
Zakładając, że nazwy materiałów eksploatacyjnych znajdują się w komórkach H1: H3, data rozpoczęcia w komórce H4, a data zakończenia w komórce H5, nasza formuła SUMPRODUCT przyjmuje następujący kształt:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Wiele osób zastanawia się, dlaczego w formułach SUMPRODUCT używa się podwójnego myślnika (-). Chodzi o to, że Excel SUMPRODUCT ignoruje wszystkie wartości oprócz wartości liczbowych, podczas gdy operatory porównania w naszej formule zwracają wartości logiczne (PRAWDA / FAŁSZ), które nie są liczbami. Aby przekonwertować te wartości logiczne na 1 „si 0”, należy użyć podwójnego znaku minus, który technicznie nazywa się podwójnym operatorem jednoargumentowym. Pierwsza jednostka jednoargumentowa wymusza odpowiednio wartość TRUE / FALSE na -1/0. Drugi unarnik neguje wartości, tj. Odwraca znak, zamieniając je na +1 i 0, co funkcja SUMPRODUCT może zrozumieć.
Mam nadzieję, że powyższe wyjaśnienie ma sens. A nawet jeśli tak nie jest, po prostu zapamiętaj tę praktyczną zasadę – użyj podwójnego operatora jednoargumentowego (-), gdy używasz operatorów porównania w formułach SUMPRODUCT.
Korzystanie z funkcji SUMA programu Excel w formułach tablicowych
Jak pamiętasz, firma Microsoft zaimplementowała funkcję SUMA.WARUNKÓW w programie Excel 2007. Jeśli ktoś nadal używa programu Excel 2003, 2000 lub starszego, będziesz musiał użyć formuły tablicowej SUMA, aby dodać wartości z wieloma kryteriami ORAZ. działa również w nowoczesnych wersjach programu Excel 2013 – 2007 i można go uznać za staromodny odpowiednik funkcji SUMA.WARUNKÓW.
W formułach SUMA.JEŻELI omówionych powyżej użyłeś już argumentów tablicowych, ale formuła tablicowa to coś innego.
Przykład 1. Suma z wieloma kryteriami AND w programie Excel 2003 i wcześniejszych
Wróćmy do pierwszego przykładu, w którym ustaliliśmy sumę kwot związanych z do danego owocu i dostawcy:
Jak już wiesz, to zadanie można łatwo wykonać za pomocą zwykła formuła SUMA.WARUNKÓW:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
A teraz zobaczmy, jak to samo zadanie można wykonać we wczesnych wersjach programu Excel bez funkcji SUMIFS . Na początek wypisujesz wszystkie warunki jakie powinny być spełnione w postaci przedział = „warunek”. W tym przykładzie mamy dwie pary zakres / warunek:
Warunek 1: A2: A9 = „jabłka”
Warunek 2: B2: B9 = „Pete”
Następnie piszesz formułę SUMA, która „mnoży” wszystkie pary zakres / warunek, każdą w nawiasach. Ostatni mnożnik to zakres sumy, w naszym przypadku C2: C9:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Jak pokazano na poniższym zrzucie ekranu, formuła doskonale działa w najnowszej wersji programu Excel 2013.
Przykład 2. SUMA formuł tablicowych w nowoczesnych wersjach programu Excel
Nawet we współczesnych wersjach programu Excel nie należy lekceważyć mocy funkcji SUMA. Formuła tablicowa SUMA nie jest po prostu gimnastyką umysłu, ale ma praktyczną wartość, jak pokazano w poniższym przykładzie.
Załóżmy, że , masz dwie kolumny, B i C, i musisz policzyć, ile razy kolumna C jest większa niż kolumna B, gdy wartość w kolumnie C jest większa lub równa 10. Natychmiastowym rozwiązaniem, które przychodzi na myśl, jest użycie SUMA formuła tablicowa:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Nie widzę żadnych praktycznych aplikacja do powyższego wzoru? Pomyśl o tym w inny sposób:)
Załóżmy, że masz listę zamówień pokazaną na poniższym zrzucie ekranu i chcesz wiedzieć, ile produktów nie zostało w całości dostarczonych w podanym terminie. Po przetłumaczeniu na język Excela mamy następujące warunki:
Warunek 1: Wartość w kolumnie B (zamówione elementy) jest większa niż 0
Warunek 2: Wartość w kolumnie C (Dostarczono) w mniej niż w kolumnie B
Warunek 3: Data w kolumnie D (Termin) jest mniejsza niż 11/1/2014.
Wstawienie trzech zakresów / par warunków razem, otrzymujemy następującą formułę:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Cóż, przykłady formuł omówione w tym samouczku tylko zarysowały powierzchnię tego, co naprawdę potrafią funkcje SUMA.WARUNKÓW i SUMA.JEŻELI. Miejmy nadzieję, że pomogły wskazać właściwy kierunek i teraz możesz sumować wartości w skoroszytach programu Excel bez względu ile skomplikowanych warunków musisz wziąć pod uwagę.
- Jak używać funkcji SUMA.JEŻELI w programie Excel – przykłady formuł do warunkowego sumowania komórek
- Jak używać funkcji SUMA.JEŻELI i SUMA.JEŻELI z wieloma kryteriami – przykłady formuł
- Jak korzystać z funkcji WYSZUKAJ.PIONOWO & SUMA lub SUMA.JEŻELI w programie Excel
- SUMA.JEŻELI w Arkuszach Google z przykładami formuł
- LICZ.JEŻELI w programie Excel – licz, jeśli nie puste, większe niż, zduplikowane lub niepowtarzalne
- Jak liczyć i sumować komórki według koloru w programie Excel