Excel: poprawnie oblicz sumy warunkowe
W programie Excel można obliczyć sumy, które nie uwzględniają wszystkich elementów wybranego obszaru - tzw. Sumy warunkowe. Pokażemy Ci, jak wykonać najbardziej sensowne obliczenia.
Trzy różne funkcje dla sum warunkowych w Excelu
Excel oferuje różne opcje i funkcje dla sum warunkowych. Właściwy wybór zależy od rodzaju warunków:
- Jeśli ustalony warunek ma zastosowanie do każdej wartości, o której mowa, użyj funkcji SUMIF.
- Jeśli trzeba wziąć pod uwagę kilka warunków, istnieje również funkcja SUMIFS w programie Excel 2007.
- Jednak w zmiennych warunkach potrzebujesz formuły macierzowej lub funkcji SUM PRODUCT.
- W poniższych akapitach wyjaśniamy trzy funkcje na przykładach.
SUMIF: Sumy warunkowe z ustalonymi warunkami
- Załóżmy, że dane znajdują się w komórkach A1 do A10, a różne wartości w komórkach B1 do C10, które są używane dla warunków.
- Pierwsze zadanie z ustalonym warunkiem oznacza na przykład sumowanie wszystkich wartości w kolumnie A, dla których odpowiednia wartość w kolumnie B jest w przybliżeniu większa niż 10.
- Aby to zrobić, użyj wzoru »= SUMIF (B1: B10;„> 10 ”; A1: A10)«.
- Pierwszy argument »B1: B10« określa obszar do oceny, a następnie warunek »"> 10 "«. Pamiętaj, że warunki z symbolami lub operatorami muszą być w cudzysłowie.
- Wreszcie »A1: A10« określa obszar do zsumowania.
- Zakresy warunków i dat mogą być również identyczne, tak że wzór »= SUMIF (A1: A10;„> 10 ”)« jest wystarczający.
SUMA JEŻELI: Sumy warunkowe z wieloma warunkami
Począwszy od Excela 2007 funkcja SUMIFS rozszerza tę zasadę na kilka do maksymalnie 127 warunków:
- Zwróć uwagę na drugą kolejność argumentów. Obszar do zsumowania jest tutaj pierwszy. Po tym następuje obszar kryteriów i powiązany warunek, przy czym obszary muszą być zawsze określone osobno.
- Na przykład formuła „= SUMIFS (A1: A10; B1: B10;„> 10 ”; C1: C10;„ <100 ”)« podsumowuje wszystkie wartości w kolumnie A, dla których odpowiadające wartości w kolumnie B są większe niż 10 i odpowiednie wartości w kolumnie C są mniejsze niż 100.
PRODUKT SUMOWY: Suma warunkowa ze zmiennymi warunkami
Zmienne kryteria, takie jak „jeśli wartość w kolumnie B jest większa niż w kolumnie C”, wymagają formuły macierzowej do oceny:
- Wartości, które należy wziąć pod uwagę, wybiera się, mnożąc je przez wartość logiczną.
- Aby to zrobić, wpisz około „= SUMA (A1: A10 * (B1: B10> C1: C10))” i zawsze zamykaj wpis, nawet po zmianie za pomocą kombinacji klawiszy [Ctrl] + [Shift] + [Enter], więc formuła jest w nawiasach klamrowych.
- W takim przypadku można osiągnąć ten sam wynik za pomocą funkcji „= SUM PRODUCT (A1: A10; 1 * (B1: B10> C1: C10))”.
- Należy jednak pamiętać, że najpierw należy pomnożyć drugi argument przez 1, aby przekonwertować wartość logiczną porównania na liczbę.
Uwaga: praktycznie we wszystkich przypadkach można wyraźnie utworzyć sumę za pomocą kolumny pomocniczej. Jest to również zalecane podczas opracowywania obliczeń w celu sprawdzenia wyników złożonych formuł. Wskazówka: wyjaśnimy tutaj, jak dodać wiele komórek w programie Excel.