Sumif z wieloma kryteriami opartymi na logice OR i AND
Podczas pracy z arkuszami programu Excel może być konieczne zsumowanie wartości na podstawie wielu kryteriów. Czasami wiele kryteriów pochodzi z tej samej kolumny (logika OR), ale czasami z różnych kolumn (logika AND). Jak w takim przypadku poradzić sobie z tym zadaniem w programie Excel?
- Sumif z wieloma kryteriami opartymi na logice OR
- Sumif z wieloma kryteriami opartymi na logice AND za pomocą funkcji SUMIFS
Sumif z wieloma kryteriami opartymi na logice OR
Przypuśćmy, że masz listę produktów zawierającą Dostawcę i Ilość, jak pokazano na poniższym zrzucie ekranu, teraz chcesz uzyskać wszystkie całkowite ilości dostarczone przez dostawcę Johna i Davida. Tutaj przedstawię dla Ciebie dwie proste formuły.
Używając SUMIF + SUMIF +…formuła
Jeśli chcesz zsumować liczby spełniające którekolwiek z kryteriów (logika OR) z wielu kryteriów, możesz dodać kilka funkcji SUMA.JEŻELI w jednej formule, ogólna składnia to:
- criteria_range: zakres komórek, które chcesz dopasować do kryteriów;
- criteria1: Pierwsze kryterium używane do określenia, które komórki należy zsumować;
- criteria2: Drugie kryterium używane do określenia, które komórki należy zsumować;
- sum_range: zakres komórek, z których chcesz zsumować.
Teraz skopiuj lub wprowadź dowolną z poniższych formuł do pustej komórki i naciśnij Wchodzę klucz do uzyskania wyniku:
=SUMIF(B4:B14,E4,C4:C14) + SUMIF(B4:B14,E5,C4:C14) (Use a cell reference)
Wyjaśnienie wzoru:
=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)
- Pierwszy SUMA.JEŻELI(B4:B14;"Jan";C4:C14) znajduje wiersze Jana i sumuje łączne ilości;
- Drugi SUMA.JEŻELI(B4:B14;"Dawid";C4:C14) znajduje wiersze Dawida i sumuje łączne ilości;
- Następnie dodaj te dwie formuły SUMA.JEŻELI, aby uzyskać wszystkie łączne ilości dostarczone przez Jana i Dawida.
Korzystając z funkcji SUMA i SUMA.JEŻELI
Powyższa formuła jest bardzo łatwa w użyciu, jeśli istnieje tylko kilka kryteriów, ale jeśli chcesz zsumować wartości z wieloma warunkami OR, powyższa formuła może być zbędna. W takim przypadku lepsza formuła, która jest tworzona na podstawie funkcji SUMA i SUMA.JEŻELI, może Ci wyświadczyć przysługę. Ogólne składnie to:
Formuła ogólna z tekstem zakodowanym na stałe:
- criteria_range: zakres komórek, które chcesz dopasować do kryteriów;
- criteria1: Pierwsze kryterium używane do określenia, które komórki należy zsumować;
- criteria2: Drugie kryterium używane do określenia, które komórki należy zsumować;
- sum_range: zakres komórek, z których chcesz zsumować.
Formuła ogólna z odwołaniami do komórek:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: zakres komórek, które chcesz dopasować do kryteriów;
- criteria_cells: komórki zawierające kryteria, których chcesz użyć;
- sum_range: zakres komórek, z których chcesz zsumować.
Wprowadź lub skopiuj dowolną z poniższych formuł do pustej komórki, a następnie uzyskaj wynik:
=SUM(SUMIF(B4:B14, E4:E5, C4:C14)) (Use cell references, array formula, should press Ctrl + Shift + Enter keys)
Wyjaśnienie wzoru:
=SUMA(SUMA.JEŻELI(B4:B14; {"Jan","Dawid"}; C4:C14))
> SUMA.JEŻELI(B4:B14, {"Jan","Dawid"}, C4:C14):
- {"Jan","Dawid"}: Stała tablicowa, która jest zbiorem wielu kryteriów podanych w nawiasach klamrowych.
- SUMA.JEŻELI(B4:B14;"Dawid";C4:C14) Stała tablicowa przy użyciu logiki OR wymusza na funkcji SUMA.JEŻELI sumowanie liczb w C4:C14 na podstawie jednego z wielu kryteriów („Jan” i „David”) i zwróci dwa oddzielne wyniki: {410,1520}.
> SUMA(SUMA.JEŻELI(B4:B14; {"Jan","Dawid"}; C4:C14))=SUMA({410,1520}): Na koniec ta funkcja SUMA dodaje te wyniki z tablicy, aby zwrócić wynik: 1930.
Sumif z wieloma kryteriami opartymi na logice AND za pomocą funkcji SUMIFS
Jeśli chcesz zsumować wartości z wieloma kryteriami w różnych kolumnach, możesz użyć funkcji SUMA.JEŻELI, aby szybko rozwiązać to zadanie. Ogólna składnia to:
- sum_range: zakres komórek, z których chcesz zsumować;
- criteria_range1: zakres, w którym stosowane jest kryterium1;
- criteria1: pierwsze kryterium, które jest sprawdzane względem zakresu kryteria_zakres1 i określa, które komórki należy dodać; (rodzajem kryteriów może być: liczba, wyrażenie logiczne, odwołanie do komórki, tekst, data lub inna funkcja Excela.)
- criteria_range2, criteria2…: Inne dodatkowe zakresy i związane z nimi kryteria. (możesz ustawić 127 par kryteria_zakres i kryteria w formule SUMIFS.)
Załóżmy, że mam tabelę z kolumnami Produkt, Dostawca, Ilość i Cena, jak pokazano na zrzucie ekranu. Teraz chcę poznać sumę całkowitej ceny produktu Apple i ilości większej lub równej 200.
Zastosuj jedną z poniższych formuł w pustej komórce i naciśnij Wchodzę klucz do zwrócenia wyniku:
=SUMIFS(C4:C14,A4:A14,G3,C4:C14,G4) (Use cell references)
Wyjaśnienie wzoru:
=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")
- Zakres A4:A14 sprawdza produkt „Jabłko”, a zakres C4:C14 wyodrębnia wszystkie ilości większe lub równe 200, a następnie funkcja SUMA.WARUNKI sumuje tylko dopasowane komórki z zakresu D4:D14.
Zastosowana funkcja względna:
- SUMA:
- Funkcja SUMA.JEŻELI może pomóc w sumowaniu komórek na podstawie jednego kryterium.
- SUMA:
- Funkcja SUMIFS w programie Excel może pomóc w sumowaniu wartości komórek na podstawie wielu kryteriów.
Więcej artykułów:
- Suma, jeśli zaczyna się lub kończy określonym tekstem lub znakami
- Aby zsumować wartości, jeśli odpowiednie komórki zaczynają się lub kończą określoną wartością, możesz zastosować funkcję SUMA.JEŻELI z symbolem wieloznacznym (*), aby ją wyciągnąć. W tym artykule szczegółowo przedstawimy, jak korzystać z formuły.
- Sumuj, jeśli komórki zawierają określony tekst w innej kolumnie
- Ten samouczek pokaże Ci, jak sumować wartości, jeśli komórki zawierają określony lub częściowy tekst w innej kolumnie. Jako przykład weź poniższy zakres danych, aby uzyskać łączne ilości produktów zawierających tekst „T-Shirt”, zarówno funkcja SUMA.JEŻELI, jak i funkcja SUMA.PRODUKT mogą poradzić sobie z tym zadaniem w programie Excel.
- Sumuj najmniejsze lub najniższe wartości N w programie Excel
- W programie Excel łatwo jest zsumować zakres komórek za pomocą funkcji SUMA. Czasami może być konieczne zsumowanie najmniejszych lub najniższych liczb 3, 5 lub n w zakresie danych, jak pokazano na poniższym zrzucie ekranu. W takim przypadku SUMPRODUCT wraz z funkcją MAŁY może pomóc rozwiązać ten problem w programie Excel.
Najlepsze narzędzia biurowe
Kutools dla programu Excel - pomaga wyróżnić się z tłumu
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)
- Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
- Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
- Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
- Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
