Excel SUMIFS: Suma tylko wartości spełnia wiele kryteriów

Funkcja SUMIFS zwiększa użyteczność funkcji SUMA.JEŻELI, umożliwiając określenie od 2 do 127 kryteriów, a nie tylko jednego, jak w SUMIF.

Dowiedz się, jak używać funkcji SUMA.JEŻELI w programach Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 dla komputerów Mac, Excel 2016 dla komputerów Mac, Excelach dla komputerów Mac 2011 i Excel Online.

01 z 11

Jak działa funkcja SUMA.JEŻELI

Zwykle SUMIFS działa z wierszami danych zwanych rekordami. W zapisie wszystkie dane w komórce lub polu w wierszu są powiązane - takie jak nazwa firmy, adres i numer telefonu.

Funkcja SUMA.JEŻELI wyszukuje określone kryteria w dwóch lub więcej polach w rekordzie i tylko wtedy, gdy znajdzie pasujące dane dla każdego podanego pola, dane dla tego rekordu zostaną zsumowane.

W samouczku SUMIF krok po kroku dopasowaliśmy pojedyncze kryterium agentów sprzedaży, którzy sprzedali ponad 250 zamówień w ciągu roku.

W tym samouczku ustawimy dwa warunki za pomocą SUMIFS: agentów sprzedaży w systemie Wschód region sprzedaży, który miał mniej niż 275 sprzedaży w ubiegłym roku.

Ustawienie więcej niż dwóch warunków można wykonać, określając dodatkowe argumenty Criteria_range i Criteria dla SUMIFS.

Wykonanie kroków opisanych w poniższych samouczkach poprowadzi Cię przez tworzenie i używanie funkcji SUMA.JEŻELI widocznej na powyższym obrazku.

02 z 11

Wprowadzanie danych samouczka

Pierwszym krokiem do użycia funkcji SUMA.JEŻELI w programie Excel jest wprowadzenie danych.

Wprowadź dane do komórek D1 do F11 arkusza programu Excel, jak widać na powyższym obrazku.

Funkcja SUMIFS i kryteria wyszukiwania (mniej niż 275 zamówień i agenci sprzedaży ze wschodniego regionu sprzedaży) zostaną dodane do wiersza 12 poniżej danych.

Instrukcje samouczka nie zawierają kroków formatowania arkusza.

Nie przeszkadza to w ukończeniu samouczka. Twój arkusz będzie wyglądał inaczej niż pokazany przykład, ale funkcja SUMIFS da ci takie same wyniki.

03 z 11

Składnia funkcji SUMIFS

W Excelu składnia funkcji odnosi się do układu funkcji i zawiera nazwę funkcji, nawiasy i argumenty.

Składnia funkcji SUMIFS to:

= SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, …)

Do 127 Criteria_range / Kryteria w funkcji można określić pary.

04 z 11

Argumenty funkcji SUMIFS

Argumenty funkcji informują o tym, jakie warunki są testowane i jaki zakres danych jest sumowany, gdy spełnione są te warunki.

Wszystkie argumenty w tej funkcji są wymagane.

Sum_range - dane w tym zakresie komórek są sumowane, gdy zostanie znalezione dopasowanie między wszystkimi określonymi Kryteria i odpowiadające im Criteria_range argumenty.

Criteria_range - grupa komórek funkcją jest szukanie dopasowania do odpowiadającego Kryteria argument.

Kryteria - ta wartość jest porównywana z danymi w odpowiednim Criteria_range . Dla tego argumentu można wprowadzić dane rzeczywiste lub odwołanie do komórki dla danych.

05 z 11

Uruchamianie funkcji SUMA.JEŻELI

Chociaż możliwe jest po prostu wpisanie funkcji SUMA.JEŻELI do komórki w arkuszu, wiele osób łatwiej korzysta z okna dialogowego funkcji, aby wejść do funkcji.

Kroki te

1. Kliknij komórkę F12, aby uczynić ją aktywną komórką. Tutaj przejdziemy do funkcji SUMA.JEŻELI.
2. Kliknij na Formuły patka.
3. Kliknij Math & Trig w grupie Function Library.
4. Kliknij SUMIFS na liście, aby wywołać okno dialogowe funkcji SUMIFS.

Excel Online nie ma karty Formuły. Aby użyć SUMA.JEŻELI w Excel Online, użyj przycisku Wstaw funkcję.

1. Kliknij komórkę F12 aby stała się aktywną komórką. Tutaj przejdziemy do funkcji SUMA.JEŻELI.
2. Kliknij Wstaw funkcję przycisk. Otworzy się okno dialogowe Wstawianie funkcji.
3. Kliknij Math & Trig na liście Kategorie.
4. Kliknij SUMIFS na liście, aby uruchomić funkcję.

Dane, które wprowadzimy do pustych linii w oknie dialogowym, będą tworzyć argumenty funkcji SUMA.JEŻELI.

Argumenty te określają, jakie warunki testujemy i jaki zakres danych sumuje się, gdy spełnione są te warunki.

06 z 11

Wprowadzanie argumentu Sum_range

The Sum_range argument zawiera odwołanie do danych, które chcemy dodać.

Gdy funkcja znajdzie dopasowanie między wszystkimi podanymi argumentami Kryteria i Kryterium_range dla rekordu, pole Sum_range dla tego rekordu jest uwzględnione w sumie.

W tym samouczku dane dla argumentu Sum_range znajdują się w pliku Całkowita sprzedaż kolumna.

Kroki te

1. Kliknij na Sum_range linia w oknie dialogowym.
2. Zaznacz komórki F3 do F9 w arkuszu roboczym, aby dodać odwołania do komórki do pliku Sum_range linia.

07 z 11

Wprowadzanie argumentu Criteria_range1

W tym samouczku próbujemy dopasować dwa kryteria w każdym rekordzie danych:

1. Przedstawiciele handlowi z regionu sprzedaży East.
2. Przedstawiciele handlowi, którzy w tym roku zrealizowali mniej niż 275 sprzedaży.

The Criteria_range1 Argument wskazuje zakres komórek, które SUMA ma wyszukiwać, próbując dopasować pierwsze kryteria - region sprzedaży East.

Kroki te

1. Kliknij na Criteria_range1 linia w oknie dialogowym.
2. Wyróżnij komórki od D3 do D9 w arkuszu, aby wprowadzić te odwołania do komórek jako zakres do przeszukiwania przez funkcję.

08 z 11

Wpisywanie argumentu Criteria1

W tym samouczku pierwsze kryteria, które chcemy dopasować, to dane w zakresie D3: D9 Wschód .

Chociaż rzeczywiste dane, takie jak słowo Wschód , można go wprowadzić w oknie dialogowym dla tego argumentu, zwykle najlepiej jest dodać dane do komórki w arkuszu, a następnie wprowadzić odwołanie do komórki w oknie dialogowym.

Kroki te

1. Kliknij na Kryteria1 linia w oknie dialogowym.
2. Kliknij komórkę D12 aby wprowadzić odwołanie do tej komórki. Funkcja przeszuka zakres wybrany w poprzednim kroku dla danych pasujących do tych kryteriów.

Wyszukiwany termin (wschód) zostanie dodany do komórki D12 w ostatnim kroku samouczka.

Jak Referencje komórek zwiększają wszechstronność SUMA

Jeśli odwołanie do komórki, takie jak D12, zostanie wprowadzone jako Kryteria Argument, funkcja SUMA.JEŻELI będzie szukać dopasowań do wszystkich danych, które zostały wpisane do tej komórki w arkuszu.

Więc po znalezieniu kwoty sprzedaży dla Wschód region łatwo będzie znaleźć te same dane dla innego regionu sprzedaży po prostu przez zmianę Wschód do Północ lub Zachód w komórce D12. Funkcja automatycznie zaktualizuje i wyświetli nowy wynik.

09 z 11

Wprowadzanie argumentu Criteria_range2

Argument Criteria_range2 wskazuje zakres komórek, które SUMA ma wyszukiwać, próbując dopasować drugie kryterium - agentów sprzedaży, którzy w tym roku sprzedali mniej niż 275 zamówień.

Kroki te

1. Kliknij na Criteria_range2 linia w oknie dialogowym.
2. Zaznacz komórki E3 do E9 w arkuszu roboczym, aby wprowadzić te odwołania do komórek jako drugi zakres do przeszukiwania przez funkcję.

10 z 11

Wprowadzanie argumentu Criteria2

Drugi standard, który chcemy dopasować, to fakt, że dane w zakresie E3: E9 to mniej niż 275 zamówień sprzedaży.

Podobnie jak w przypadku Kryteria1 argument, wpisujemy odwołanie do lokalizacji Kryterium 2 w oknie dialogowym, a nie do samych danych.

Kroki te

1. Kliknij na Kryterium2 linia w oknie dialogowym.
2. Kliknij komórkę E12 aby wprowadzić odwołanie do tej komórki. Funkcja przeszuka zakres wybrany w poprzednim kroku dla danych zgodnych z kryteriami.
3. Kliknij dobrze aby ukończyć funkcję SUMIFS i zamknąć okno dialogowe.

Odpowiedź zerowa (0) pojawi się w komórce F12 (komórce, w której wprowadziliśmy funkcję), ponieważ nie dodaliśmy jeszcze danych do pól Kryterium 1 i Kryterium 2 (C12 i D12). Dopóki tego nie zrobimy, funkcja nie będzie się sumować, a więc całkowita pozostanie na poziomie zero.

Kryteria wyszukiwania zostaną dodane w następnym kroku samouczka.

11 z 11

Dodanie kryteriów wyszukiwania i ukończenie samouczka

Ostatnim krokiem w samouczku jest dodanie danych do komórek w arkuszu roboczym zidentyfikowanym jako zawierającym Kryteria argumenty.

Kroki te

Aby uzyskać pomoc dotyczącą tego przykładu, patrz obrazek powyżej.

1. W komórce D12 rodzaj Wschód i naciśnij Wchodzić klawisz na klawiaturze.
2. W komórce E12 rodzaj <275 i naciśnij Wchodzić klawisz na klawiaturze (symbol "<" oznacza mniej niż w programie Excel).

Odpowiedź 119 719 $ powinna pojawić się w komórce F12.

Tylko dwa rekordy w wierszach 3 i 4 spełniają oba kryteria i dlatego sumy sprzedaży dla tych dwóch rekordów są sumowane przez funkcję.

Suma 49 017 USD i 70 702 USD wynosi 119 719 USD.

Po kliknięciu komórki F12, pełna funkcja= SUMA (F3: F9, D3: D9, D12, E3: E9, E12) pojawia się na pasku formuły nad arkuszem.