Funkcja SUMIFS, jak używać funkcji do sumowania wielu warunków w programie Excel

Funkcja Sumify w programie Excel jest niezwykle przydatna, gdy trzeba zsumować wiele warunków. Poniżej znajdują się szczegółowe informacje na temat korzystania z funkcji Sumify w programie Microsoft Excel .

Funkcja SUMIFS w programie Excel jest jedną z podstawowych funkcji programu Excel , powszechnie używaną funkcją obliczeniową w programie Excel. Do obliczenia sumy w Excelu skorzystamy z funkcji SUMA.Jeżeli będziemy chcieli dodać jakiś warunek do funkcji sumy skorzystamy z funkcji SUMA . W przypadkach, gdy tabela danych wymaga sumy z wieloma warunkami, musimy skorzystać z funkcji SUMIFS. W poniższym artykule dowiesz się, jak korzystać z funkcji SUMIFS w programie Excel.

• MS Excel - Lekcja 5: Formuły i funkcje Excela
• Jak połączyć funkcję Sumif i funkcję Vlookup w programie Excel
• 3 sposoby obliczania sum w programie Excel
• Jak naprawić błąd funkcji SUMA, który nie dodaje się w Excelu

Spis treści artykułu

• Składnia funkcji SUMIFS w programie Excel
• Uwaga dotycząca korzystania z funkcji SUMIFS w programie Excel
• Instrukcja korzystania z funkcji SUMIFS w programie Excel
  • Użyj symboli wieloznacznych
  • Użyj nazwanych zakresów z funkcją SUMIFS
  • Przykład funkcji SUMIFS w Excelu
• Ograniczenia funkcji SUMIFS

Składnia funkcji SUMIFS w programie Excel

Składnia funkcji SUMIFS w programie Excel to =SUMIFS(zakres_sumy,zakres_kryteriów1,kryteria1,zakres_kryteriów2,kryteria2,…).

Tam:

• Suma_zakres: to komórki, które należy zsumować w tabeli danych, wartości puste i wartości tekstowe są ignorowane, wymagane parametry.
• Kryteria_zakres1: zakres, który należy przetestować przy użyciu kryteriów kryterium1, jest wartością wymaganą.
• Kryteria1: warunek zastosowany do zakresu_kryteriów1, może być liczbą, wyrażeniem, odwołaniem do komórki w celu określenia, które komórki w zakresie_kryteriów1 zostaną zsumowane, a także wymaganą wartością.
• Kryteria_zakres2, kryteria2,…: opcjonalne dodatkowe zakresy i warunki, maksymalnie 127 par kryteriów_zakres, kryteria.

Uwaga dotycząca korzystania z funkcji SUMIFS w programie Excel

• Dla każdej komórki w zakresie zakres sumy_zakresu zostanie zsumowany, gdy zostaną spełnione wszystkie odpowiednie warunki uznane za prawdziwe dla tej komórki.
• Komórki w zakresie sumy, które zawierają PRAWDA, są uznawane za 1, komórki zawierające FAŁSZ są uznawane za 0.
• Każdy zakres_kryteriów musi mieć taki sam rozmiar wyboru jak zakres_sumy, zakres_kryteriów i zakres_sumy muszą mieć tę samą liczbę wierszy i tę samą liczbę kolumn.
• W kryteriach można używać znaku zapytania (?) zamiast pojedynczego znaku, gwiazdki (*) zamiast ciągu znaków. Jeśli warunkiem jest znak zapytania lub gwiazdka, należy z przodu wpisać znak ~, wartością warunku jest tekst zawarty w „.
• Stosuje się wiele warunków przy użyciu logiki AND, tj. kryteria1 ORAZ kryteria2 itd.
• Każdy dodatkowy zakres musi mieć taką samą liczbę wierszy i kolumn jak cały zakres, ale zakresy nie muszą sąsiadować ze sobą. Jeśli podasz niedopasowane zakresy, pojawi się błąd #VALUE.
• Ciąg tekstowy w kryteriach musi być ujęty w cudzysłów („”), np. „apple”, „>32”, „jap*”
• Odwołań do komórek w kryteriach nie można ująć w cudzysłów, tj. „<>
• SUMIF i SUMIFS mogą obsługiwać zakresy, ale nie mogą obsługiwać tablic. Oznacza to, że w zakresie kryteriów nie można używać innych funkcji, takich jak ROK, ponieważ wynikiem jest tablica. Jeśli potrzebujesz tej funkcjonalności, użyj funkcji SUMPRODUCT.
• Kolejność argumentów pomiędzy funkcjami SUMIFS i SUMIF jest inna. Zakres sumy jest pierwszym argumentem w SUMIFS, ale trzecim argumentem w SUMIF.

Instrukcja korzystania z funkcji SUMIFS w programie Excel

Użyj symboli wieloznacznych

Znaki wieloznaczne, takie jak „*” i „?” można użyć w argumencie kryteria podczas korzystania z funkcji SUMA. Użycie tych symboli wieloznacznych pomoże Ci znaleźć podobne, ale nie dokładne dopasowania.

Gwiazdka (*) — dopasowuje dowolny ciąg znaków, który może zostać użyty po, przed lub z otaczającymi kryteriami, aby umożliwić częściowe wykorzystanie kryteriów wyszukiwania.

Na przykład, jeśli w funkcji SUMIFS zastosujesz następujące kryteria:

• N * — oznacza wszystkie komórki w zakresie zaczynającym się od N
• * N — oznacza wszystkie komórki w zakresie kończącym się na N
• *N* - Komórki zawierające N

Znak zapytania (?) — dopasowuje dowolny pojedynczy znak. Załóżmy, że jako kryterium zastosujesz N?r. „?” tutaj zastąpi pojedynczy znak. Nr będzie odpowiadać North, Nor itp. Jednak nie będzie uwzględniać nazwy.

A co jeśli podane dane zawierają gwiazdkę lub prawdziwy znak zapytania?

W tym przypadku możesz użyć tyldy (~). W takim przypadku należy wpisać „~” przed znakiem zapytania.

Użyj nazwanych zakresów z funkcją SUMIFS

Nazwany zakres to opisowa nazwa zestawu komórek lub zakresu komórek w arkuszu. Podczas korzystania z funkcji SUMIFS można używać nazwanych zakresów.

Przykład funkcji SUMIFS w Excelu

Sumę obliczymy na podstawie poniższej tabeli danych z wieloma różnymi warunkami.

Przykład 1 : Oblicz łączną liczbę produktów, które pracownik Hoai sprzedaje po cenie jednostkowej mniejszej niż 400 000 VND.

W polu wprowadzania wyników wpisujemy formułę =SUMIFS(D2:D7,C2:C7,"Hoai",E2:E7,"<400000") i następnie="" press="">

Tam:

• D2:D7 to obszar, w którym należy obliczyć całkowitą pozycję.
• C2:C7 to zakres wartości warunku dotyczącego nazwiska pracownika.
• „Hoai” to warunek imienia i nazwiska pracownika, który ma obliczyć liczbę pozycji zawartych w obszarze C2:C7.
• E2:E7 to stan przedmiotu.
• Warunkiem umieszczenia przedmiotów w obszarach E2:E7 jest „400 000”.

Wynikiem jest łączna liczba elementów, jak pokazano poniżej.

Przykład 2 : Oblicz całkowitą liczbę artykułów sprzedanych przez rosyjskich pracowników o numerze seryjnym <>

W polu wprowadzania wyników wpisujemy formułę =SUMIFS(D2:D7,C2:C7,"=Rosja",A2:A7,"<5"), a następnie="" press="" enter.="" w = "" name="" multiplication="" tablet="" "nga"="" enter="" znak="lub" usuń="" znak="nawet">

W rezultacie otrzymujemy całkowitą liczbę przedmiotów sprzedanych przez rosyjskich pracowników o numerach seryjnych < 5="" czyli 550 artykułów="">

Przykład 3 : Oblicz całkowitą liczbę produktów sprzedanych przez rosyjskich pracowników, z wyłączeniem szalików.

W polu wprowadzania wyników wpisujemy formułę =SUMIFS(D2:D7,C2:C7,„Rosja”,B2:B7,„<>szalik”). W którym znak <> służy do wykluczenia określonego obiektu z obszaru danych jako warunku.

Wynikiem jest łączna liczba produktów, jak pokazano poniżej.

Przykład 4:

Oblicz sumę wszystkich statystyk dla Pokemona typu wodnego generacji 1:

Warunek jest taki, że Woda i Wytwarzanie wynoszą 1.

Uwaga: pełny zestaw danych jest kontynuowany po wierszu 14, aż do wiersza 759.

Oto szczegóły krok po kroku:

1. Wybierz komórkę H3 .
2. Wpisz =SUMIFS.
3. Kliknij dwukrotnie polecenie SUMIFS.
4. Wybierz całkowity zakres jako C2:C759.
5. Typ,
6. Wybierz zakres dla pierwszego warunku B2:B759(wpisz wartość 1)
7. Wybierz kryteria (komórka F3z wartością Water)
8. typ ,
9. Wybierz zakres dla drugiego warunku z D2:D759(Wartość generacji)
10. Typ,
11. Określ kryteria (komórka G3 ma wartość 1).
12. Naciśnij enter .

Uwaga: możesz dodać więcej warunków, powtarzając kroki od 9 do 12 przed naciśnięciem klawisza Enter.

Ta funkcja oblicza teraz sumę całkowitych statystyk Pokemona wodnego generacji 1. Funkcję tę można powtórzyć, aby przyszłe pokolenia mogły je porównać.

Powyżej znajduje się kilka przykładów wykorzystania funkcji SUMIFS do obliczania wartości całkowitej przy wielu połączonych warunkach. Musimy odpowiednio ułożyć zakresy i towarzyszące im warunki, aby Excel mógł rozpoznać wzór funkcji do obliczeń.

Ograniczenia funkcji SUMIFS

Funkcja SUMIFS ma pewne ograniczenia, o których powinieneś wiedzieć:

• Warunki w SUMIFS są łączone za pomocą logiki AND. Innymi słowy, wszystkie warunki muszą mieć wartość PRAWDA, aby komórka została uwzględniona w sumie. Aby zsumować komórki za pomocą logiki OR, w prostych przypadkach można zastosować alternatywne rozwiązanie.
• Funkcja SUMIFS wymaga rzeczywistych zakresów dla wszystkich argumentów zakresu; nie można używać tablicy. Oznacza to, że nie można na przykład wyodrębnić roku z daty w funkcji SUMIFS. Aby przed zastosowaniem kryteriów zmienić wartości pojawiające się w argumencie zakresu, elastycznym rozwiązaniem jest funkcja SUMPRODUCT.
• W SUMIFS wielkość liter nie jest rozróżniana. Aby zsumować wartości w oparciu o warunek uwzględniający wielkość liter, można użyć formuły opartej na funkcji SUMPRODUCT z funkcją DOKŁADNY.

Najczęstszym sposobem obejścia powyższych ograniczeń jest użycie funkcji SUMPRODUCT. W aktualnych wersjach programu Excel inną opcją jest użycie nowszych funkcji BYROW i BYCOL.

Jak naprawić błąd #VALUE w funkcji SUMIFS programu Excel

Problem: Formuła odwołuje się do komórek w zamkniętym skoroszycie

Funkcja SUMA, która odwołuje się do komórki lub zakresu komórek w zamkniętym skoroszycie, spowoduje zwrócenie błędu #WARTOŚĆ!.

Uwaga: jest to częsty błąd. Ten komunikat często pojawia się również podczas korzystania z innych funkcji programu Excel, takich jak LICZ.JEŻELI, LICZ., LICZ.PUSTE...

Rozwiązanie:

Otwórz skoroszyt zawarty w tej formule, naciśnij klawisz F9, aby odświeżyć formułę. Problem można także rozwiązać, używając funkcji SUMA i JEŻELI w formule tablicowej.

Problem: ciąg kryteriów jest dłuższy niż 255 znaków

Funkcja SUMIFS zwraca błędne wyniki przy próbie połączenia ciągów dłuższych niż 255 znaków.

Rozwiązanie:

Jeśli to możliwe, skróć sznurek. Jeśli nie jest to możliwe, użyj funkcji CONCATENATE lub operatora &, aby podzielić wartość na wiele ciągów. Na przykład:

=SUMIF(B2:B12,"long string"&"another long string")

Problem: w SUMIFS argument zakres_kryteria jest niezgodny z argumentem zakres_sumy.

Argument zakresu musi być zawsze taki sam w SUMIFS. Oznacza to, że argumenty zakres_kryteria i zakres_sumy powinny odnosić się do tej samej liczby wierszy i kolumn.

W poniższym przykładzie formuła zwraca całkowitą dzienną sprzedaż jabłek w Bellevue. Jednak argument zakres_sumy (C2:C10) nie odpowiada liczbie wierszy i kolumn w zakresie zakresu_kryteriów (A2:A12 i B2:B12). Użycie formuły =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)spowoduje błąd #VALUE.

Rozwiązanie:

Zmień sum_rangena C2:C12 i spróbuj ponownie zastosować formułę.

Życzę powodzenia!