Ten samouczek pokazuje, jak używać Excel SUMIF i SUMIFS Funkcjacje w Excelu i Arkuszach Google, aby zsumować dane spełniające określone kryteria.
Omówienie funkcji SUMA.JEŻELI
Możesz użyć funkcji SUMA.JEŻELI w programie Excel do sumowania komórek zawierających określoną wartość, sumowania komórek większych lub równych wartości itp.
(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)
SUMIF Składnia i argumenty funkcji:
| 1 | =SUMA.JEŻELI (zakres, kryteria, [suma_zakres]) |
zasięg – zakres komórek, do których chcesz zastosować kryteria.
kryteria – kryteria używane do określenia, które komórki należy dodać.
sum_zakres - [opcjonalnie] Komórki do dodania. Jeśli sum_zakres zostanie pominięty, zamiast tego komórki w zakresie są dodawane razem.
Co to jest funkcja SUMA.JEŻELI?
Funkcja SUMA.JEŻELI jest jedną ze starszych funkcji używanych w arkuszach kalkulacyjnych. Służy do skanowania zakresu komórek w poszukiwaniu określonego kryterium, a następnie dodawania wartości w zakresie odpowiadającym tym wartościom. Pierwotna funkcja SUMA.JEŻELI była ograniczona tylko do jednego kryterium. Po 2007 roku powstała funkcja SUMIFS, która pozwala na wiele kryteriów. Większość ogólnych zastosowań pozostaje taka sama między tymi dwoma, ale istnieją pewne krytyczne różnice w składni, które omówimy w tym artykule.
Jeśli jeszcze tego nie zrobiłeś, możesz przejrzeć wiele podobnych struktur i przykładów w artykule COUNTIFS .
Podstawowy przykład
Rozważmy tę listę zarejestrowanych sprzedaży i chcemy poznać całkowity dochód.
Ponieważ mieliśmy wydatek, wartość ujemną, nie możemy po prostu zrobić podstawowej sumy. Zamiast tego chcemy zsumować tylko wartości większe niż 0. „Większe niż 0” będzie naszymi kryteriami w funkcji SUMA.JEŻELI. Nasza formuła, aby to stwierdzić, to
| 1 | =SUMA.JEŻELI(A2:A7; ">0") |
Przykład dwukolumnowy
Chociaż oryginalna funkcja SUMA.JEŻELI została zaprojektowana tak, aby umożliwić zastosowanie kryterium do zakresu liczb, które chcesz zsumować, w większości przypadków będziesz musiał zastosować jedno lub więcej kryteriów do innych kolumn. Rozważmy tę tabelę:
Teraz, jeśli użyjemy oryginalnej funkcji SUMA.JEŻELI, aby dowiedzieć się, ile mamy bananów (wymienionych w komórce D1), będziemy musieli podać zakres, który chcemy suma jako ostatni argument, a więc nasza formuła byłaby
| 1 | =SUMA.JEŻELI(A2:A7;D1;B2:B7) |
Kiedy jednak programiści w końcu zdali sobie sprawę, że użytkownicy chcą podać więcej niż jedno kryterium, powstała funkcja SUMIFS. Aby utworzyć jedną strukturę, która będzie działać dla dowolnej liczby kryteriów, SUMIFS wymaga, aby zakres sumy był wymieniony jako pierwszy. W naszym przykładzie oznacza to, że formuła musi być
| 1 | =SUMA.WARUNKI(B2:B7; A2:A7; D1) |
UWAGA: Te dwie formuły dają ten sam wynik i mogą wyglądać podobnie, więc zwróć szczególną uwagę na to, która funkcja jest używana, aby upewnić się, że wszystkie argumenty zostały wymienione we właściwej kolejności.
Praca z datami, wiele kryteriów
Podczas pracy z datami w arkuszu kalkulacyjnym, chociaż można wprowadzić datę bezpośrednio do formuły, najlepiej jest umieścić datę w komórce, aby można było odwoływać się do komórki w formule. Na przykład pomaga to komputerowi wiedzieć, że chcesz użyć daty 27.05.2020, a nie liczby 5 podzielonej przez 27 podzielonej przez 2022.
Przyjrzyjmy się kolejnej tabeli, w której co dwa tygodnie rejestrujemy liczbę odwiedzających witrynę.
Możemy określić punkt początkowy i końcowy zakresu, na który chcemy spojrzeć w D2 i E2. Nasz wzór na zsumowanie liczby odwiedzających w tym zakresie mógłby wyglądać następująco:
| 1 | =SUMA.WARUNKI(B2:B7; A2:A7; ">==&D2, A2:A7; "<=&E2) |
Zwróć uwagę, jak udało nam się połączyć porównania „=” z odwołaniami do komórek, aby utworzyć kryteria. Ponadto, mimo że oba kryteria były stosowane do tego samego zakresu komórek (A2:A7), zakres należy wpisać dwukrotnie, raz na każde kryterium.
Wiele kolumn
Korzystając z wielu kryteriów, możesz zastosować je do tego samego zakresu, co w poprzednim przykładzie, lub możesz zastosować je do różnych zakresów. Połączmy nasze przykładowe dane w tej tabeli:
Skonfigurowaliśmy kilka komórek, aby użytkownik mógł wprowadzić to, czego chce szukać w komórkach od E2 do G2. Potrzebujemy zatem formuły, która zsumuje całkowitą liczbę jabłek zebranych w lutym. Nasza formuła wygląda tak:
| 1 | =SUMA.WARUNKI(C2:C7; B2:B7; ">="&F2, B2:B7;"<="&G2, A2:A7; E2) |
SUMIFS z logiką typu OR
Do tego momentu wszystkie przykłady, których używaliśmy, były porównaniem opartym na AND, w którym szukamy wierszy spełniających wszystkie nasze kryteria. Teraz rozważymy przypadek, w którym chcesz poszukać możliwości wiersza spełniającego to lub inne kryterium.
Spójrzmy na tę listę sprzedaży:
Chcielibyśmy zsumować łączną sprzedaż zarówno Adama, jak i Boba. Aby to zrobić, masz kilka opcji. Najprościej jest dodać dwa SUMIFy razem, tak jak poniżej:
| 1 | =SUMA.WARUNKÓW(B2:B7;A2:A7;"Adam")+SUMA.WARUNKÓW(B2:B7;A2:A7;"BOB") |
Tutaj komputer obliczył nasze indywidualne wyniki, a następnie dodaliśmy je do siebie.
Nasza następna opcja jest dobra, gdy masz więcej zakresów kryteriów, tak że nie chcesz wielokrotnie przepisywać całej formuły. W poprzedniej formule ręcznie powiedzieliśmy komputerowi, aby dodał razem dwa różne SUMIFS. Możesz to jednak również zrobić, pisząc swoje kryteria w tablicy, na przykład:
| 1 | =SUMA(SUMA.WARUNKÓW(B2:B7; A2:A7; {"Adam", "Bob"})) |
Zobacz, jak zbudowana jest tablica w nawiasach klamrowych. Kiedy komputer oceni tę formułę, będzie wiedział, że chcemy obliczyć funkcję SUMIFS dla każdego elementu w naszej tablicy, tworząc w ten sposób tablicę liczb. Zewnętrzna funkcja SUMA weźmie następnie tę tablicę liczb i zamieni ją w pojedynczą liczbę. Przechodząc przez ocenę formuły, wyglądałoby to tak:
| 123 | =SUMA(SUMA.WARUNKÓW(B2:B7; A2:A7; {"Adam", "Bob"}))=SUMA(27401, 43470)=70871 |
Uzyskujemy ten sam wynik, ale byliśmy w stanie napisać formułę nieco bardziej zwięźle.
Radzenie sobie z pustymi miejscami
Czasami Twój zestaw danych będzie zawierał puste komórki, które musisz znaleźć lub uniknąć. Ustalenie kryteriów dla nich może być trochę trudne, więc spójrzmy na inny przykład.
Zauważ, że komórka A3 jest naprawdę pusta, podczas gdy komórka A5 ma formułę zwracającą ciąg znaków o zerowej długości „”. Jeśli chcemy znaleźć łączną sumę naprawdę puste komórki, użyjemy kryterium „=", a nasza formuła będzie wyglądać tak:
| 1 | =SUMA.WARUNKI(B2:B7;A2:A7;"=") |
Z drugiej strony, jeśli chcemy uzyskać sumę dla wszystkich komórek, które wizualnie wyglądają na puste, zmienimy kryteria na „”, a formuła wygląda tak
| 1 | =SUMA.WARUNKI(B2:B7;A2:A7;"") |
Odwróćmy to: co, jeśli chcesz znaleźć sumę niepustych komórek? Niestety obecny projekt nie pozwala uniknąć ciągu o zerowej długości. Możesz użyć kryterium „”, ale jak widać w przykładzie, nadal zawiera ono wartość z wiersza 5.
| 1 | =SUMA.WARUNKI(B2:B7;A2:A7;"") |
Jeśli nie chcesz zliczać komórek zawierających ciągi o zerowej długości, rozważ użycie funkcji LEN wewnątrz SUMPRODUCT
SUMIF w Arkuszach Google
Funkcja SUMA.JEŻELI działa dokładnie tak samo w Arkuszach Google, jak w programie Excel:
