Pobierz przykładowy skoroszyt
Ten samouczek pokaże, jak używać funkcji SUMIFS do sumowania danych odpowiadających określonym miesiącom w Excelu i Arkuszach Google.
Suma, jeśli według miesiąca
Najpierw pokażemy, jak sumować dane odpowiadające datom przypadającym w określonym miesiącu i roku.
Możemy użyć funkcji SUMIFS wraz z funkcjami DATE, YEAR, MONTH i EOMONTH, aby zsumować Liczba sprzedaży w ramach każdego Miesiąc.
| 1 | =SUMA.WARUNKI(C3:C9;B3:B9;">="&DATA(ROK(E3);MIESIĄC(E3);1);B3:B9;"<="&EOMIESIĄC(E3,0)) |
Aby zbudować powyższy wzór, zaczynamy od zdefiniowania zakresu dat dla każdego miesiąca. Używamy funkcji DATE, aby zdefiniować pierwszy dzień miesiąca (np. 05.01.2021). Możemy to zrobić poprzez „zakodowanie” daty w formule:
| 1 | ">="&DATA(2021,5,1) |
Lub, jeszcze lepiej, możemy uelastycznić kryteria, odwołując się do daty w komórce E3, wyodrębniając rok i miesiąc daty (i ustawiając dzień równy 1), jak pokazano w powyższym przykładzie:
| 1 | ">="&DATA(ROK(E3);MIESIĄC(E3);1) |
Aby zdefiniować ostatni dzień miesiąca, możemy użyć funkcji EOMONTH:
| 1 | "<="&EOMIESIĄC(E3,0) |
Łącząc wszystkie te kryteria, możemy napisać następującą formułę SUMIF:
| 1 | =SUMA.WARUNKI(C3:C9;B3:B9;">="&DATA(ROK(E3);MIESIĄC(E3);1);B3:B9;"<="&EOMIESIĄC(E3,0)) |
Blokowanie odwołań do komórek
Aby ułatwić czytanie naszych formuł, pokazaliśmy formuły bez zablokowanych odwołań do komórek:
| 1 | =SUMA.WARUNKI(C3:C9;B3:B9;">="&DATA(ROK(E3);MIESIĄC(E3);1);B3:B9;"<="&EOMIESIĄC(E3,0)) |
Ale te formuły nie będą działać poprawnie po skopiowaniu i wklejeniu w innym miejscu pliku. Zamiast tego powinieneś używać zablokowanych odwołań do komórek w następujący sposób:
| 1 | =SUMATY($C$3:$C$9;$B$3:$B$9;">="&DATA(ROK(E3);MIESIĄC(E3);1);$B$3:$B$9;"<=" &EOMIESIĄC(E3,0)) |
Przeczytaj nasz artykuł na temat blokowania odwołań do komórek, aby dowiedzieć się więcej.
Formatowanie wartości miesiąca
W tym przykładzie wymieniliśmy miesiące w kolumnie E. Te wartości miesięcy są w rzeczywistości datami sformatowanymi w celu pominięcia dnia przy użyciu niestandardowego formatowania liczb.
Niestandardowy format danych to „mmm rrrr”, co oznacza maj 2022 r.
Suma według miesiąca przez wiele lat
W powyższym przykładzie zsumowano dane z datami przypadającymi w określonym miesiącu i roku. Zamiast tego możesz sumować dane z datami przypadającymi w ciągu miesiąca w dowolnym roku za pomocą funkcji SUMPRODUCT.
| 1 | =PROD.SUMA(C3:C8,--(MIESIĄC(B3:B8)=MIESIĄC(G3))) |
W tym przykładzie używamy funkcji SUMPRODUCT do wykonywania skomplikowanych obliczeń „suma jeśli”. Przejdźmy przez formułę.
Oto nasza ostateczna formuła:
| 1 | =PROD.SUMA(C3:C8,--(MIESIĄC(B3:B8)=MIESIĄC(G3))) |
Po pierwsze, funkcja SUMPRODUCT wyświetla listę Liczba sprzedaży dla każdego Data sprzedaży a następnie porównuje miesiąc każdego Data sprzedaży w stosunku do określonego Miesiąc, zwracając TRUE, jeśli miesiące się zgadzają, lub FALSE, jeśli nie:
| 1 | =PROD.SUMA({30;42;51;28;17;34},--({PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;PRAWDA;FAŁSZ})) |
Następnie podwójne myślniki (-) zamieniają wartości TRUE i FALSE na 1 i 0:
| 1 | =PROD.SUMA({30;42;51;28;17;34};{1;0;1;0;1;0}) |
Funkcja SUMPRODUCT mnoży następnie każdą parę wpisów w tablicach:
| 1 | =PROD.SUMA({30;0;51;0;17;0}) |
Na koniec sumuje się liczby w tablicy:
| 1 | =98 |
Więcej szczegółów na temat używania instrukcji Boolean i polecenia „-” w funkcji SUMPRODUCT można znaleźć tutaj
Suma jeśli według miesiąca w Arkuszach Google
Te formuły działają dokładnie tak samo w Arkuszach Google, jak w programie Excel.
