Często chcemy obliczyć niektóre wartości według miesiąca. Na przykład, ile sprzedaży dokonano w danym miesiącu. Cóż, można to łatwo zrobić za pomocą tabel przestawnych, ale jeśli chcesz mieć raport dynamiczny, możemy użyć formuły SUMPRODUCT lub SUMIFS, aby zsumować według miesiąca.
Zacznijmy od rozwiązania SUMPRODUCT.
Oto ogólna formuła, aby uzyskać sumę według miesiąca w Excelu
=SUMPRODUCT(suma_zakres,--( TEKST(zakres_dat;"MMM")=miesiąc_tekst))
Sum_zakres : Jest to zakres, który chcesz zsumować według miesiąca.
Zakres dat : jest to zakres dat, w którym będziesz szukać przez miesiące.
Tekst_miesiąca: Jest to miesiąc w formacie tekstowym, którego wartości chcesz zsumować.
Zobaczmy teraz przykład:
Przykład: Sumuj wartości według miesiąca w Excelu
Tutaj mamy pewną wartość związaną z datami. Daty te dotyczą stycznia, lutego i marca w 2019 roku.
Jak widać na powyższym obrazku, wszystkie daty dotyczą roku 2019. Teraz wystarczy zsumować wartości w E2:G2 według miesięcy w E1:G1.
Teraz, aby zsumować wartości według miesięcy, wpisz ten wzór w E2:
=PROD.SUMA(B2:B9,--(TEKST(A2:A9;"MMM")=E1)))
Jeśli chcesz skopiować go do sąsiednich komórek, użyj odwołań bezwzględnych lub nazwanych zakresów, tak jak na obrazku.
To daje nam dokładną sumę każdego miesiąca.
Jak to działa?
Zaczynając od środka, spójrzmy na TEKST(A2:A9;"MMM") część. Tutaj funkcja TEKST wyodrębnia miesiąc z każdej daty z zakresu A2:A9 w formacie tekstowym do tablicy. Tłumaczenie na formułę na =SUMPRODUCT(B2:B9,--({"sty";"sty";"lut";"sty";"lut";"mar";"sty";"lut"}=E1) )
Następnie TEKST(A2:A9;"MMM")=E1: Tutaj każdy miesiąc w tablicy jest porównywany z tekstem w E1. Ponieważ E1 zawiera „Jan”, każdy „Jan” w tablicy jest konwertowany na PRAWDA, a inne na FAŁSZ. To tłumaczy formułę na =SUMPRODUCT($B$2:$B$9,--{PRAWDA;PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ})
Następnie --(TEKST(A2:A9;"MMM")=E1) konwertuje PRAWDA FAŁSZ na wartości binarne 1 i 0. Formuła jest tłumaczona na =SUMPRODUCT($B$2:$B$9,{1;1;0; 1;0;0;1;0}).
Wreszcie SUMA PRODUKT($B$2:$B$9,{1;1;0;1;0;0;1;0}): funkcja SUMPRODUCT mnoży odpowiednie wartości w $B$2:$B$9 do tablicy {1;1; 0;1;0;0;1;0} i sumuje je. Stąd otrzymujemy sumę według wartości jako 20052 w E1.
SUMA JEŚLI Miesiące z innego roku
W powyższym przykładzie wszystkie daty pochodzą z tego samego roku. A gdyby pochodzili z różnych lat? Powyższy wzór będzie sumował wartości według miesiąca niezależnie od roku. Na przykład, sty 2018 i sty 2019 zostaną dodane, jeśli użyjemy powyższej formuły. Co w większości przypadków jest złe.
Stanie się tak, ponieważ w powyższym przykładzie nie mamy żadnych kryteriów na rok. Jeśli dodamy również kryteria roczne, to zadziała.
Ogólna formuła, aby uzyskać sumę według miesiąca i roku w programie Excel
=SUMAPRODUKT(suma_zakres,--( TEKST(zakres_dat;"MMM")=miesiąc_tekst),--( TEKST(zakres_dat;"rrrr")=TEKST(rok;0)))
Tutaj dodaliśmy jeszcze jedno kryterium sprawdzające rok. Cała reszta jest taka sama.
Rozwiążmy powyższy przykład, napisz tę formułę w komórce E1, aby uzyskać sumę stycznia 2017 roku.
=PROD.SUMA(B2:B9,--(TEKST(A2:A9;"MMM")=E1),--(TEKST(A2:A9;"rrrr")=TEKST(D2,0)))
Przed skopiowaniem w poniższych komórkach użyj nazwanych zakresów lub odwołań bezwzględnych. Na obrazku użyłem nazwanych zakresów do kopiowania w sąsiednich komórkach.
Teraz możemy również zobaczyć sumę wartości w miesiącach i latach.
Jak to działa?
Pierwsza część formuły jest taka sama jak w poprzednim przykładzie. Rozumiemy dodatkową część, która dodaje kryteria roku.
--(TEKST(A2:A9,"rrrr")=TEKST(D2,0)): TEKST(A2:A9,"rrrr") konwertuje datę w formacie A2:A9 jako lata w formacie tekstowym na tablicę. {"2018";"2019";"2017";"2017";"2019";"2017";"2019";"2017"}.
W większości przypadków rok zapisywany jest w formacie liczbowym. Aby porównać liczbę z tekstem my, przekonwertowałem tekst rok int za pomocą TEKST(D2,0). Następnie porównaliśmy ten rok tekstowy z tablicą lat jako TEKST(A2:A9,"rrrr")=TEKST(D2,0). Zwraca tablicę wartości prawda-fałsz {FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}. Następnie zamieniliśmy prawdę fałsz na liczbę za pomocą operatora --. To daje nam {0;0;1;1;0;1;0;1}.
Tak więc ostatecznie formuła zostanie przetłumaczona na = PRODUKT SUMA(B2:B9,{1;1;0;1;0;0;1;0},{0;0;1;1;0;1;0;1 }). Gdzie pierwsza tablica to wartości. Następny jest dopasowany miesiąc, a trzeci rok. W końcu otrzymujemy naszą sumę wartości jako 2160.
Używanie funkcji SUMIFS do sumowania według miesiąca
Formuła ogólna
=SUMIFS(suma_zakres,zakres_dat”>=” i data_początkowa, zakres_dat”<=” i EOMONTH(data_początkowa,0))
Tutaj,Sum_zakres : Jest to zakres, który chcesz zsumować według miesiąca.
Zakres dat : jest to zakres dat, w którym będziesz szukać przez miesiące.
Data rozpoczęcia : Jest to data rozpoczęcia, od której chcesz podsumować. W tym przykładzie będzie to 1. dzień danego miesiąca.
Przykład: Sumuj wartości według miesiąca w Excelu
Tutaj mamy pewną wartość związaną z datami. Daty te dotyczą stycznia, lutego i marca w 2019 roku.
Musimy tylko zsumować te wartości do miesiąca. Teraz było łatwo, gdybyśmy mieli osobno miesiące i lata. Ale nie są. Nie możemy tutaj użyć żadnej kolumny pomocy.
Aby przygotować raport, przygotowałem format raportu, który zawiera miesiąc i sumę wartości. W kolumnie miesiąca faktycznie mam datę rozpoczęcia miesiąca. Aby zobaczyć tylko miesiąc, wybierz datę rozpoczęcia i naciśnij klawisze CTRL+1.
W formacie niestandardowym napisz „mmm”.
Teraz mamy gotowe dane. Zsumujmy wartości według miesiąca.
Zapisz tę formułę w E3, aby zsumować według miesiąca.
=SUMA.WARUNKI(B3:B10;A3:A10;">="&D3;A3:A10;"<="&EOMIESIĄC(D3,0))
Przed skopiowaniem formuły użyj odwołań bezwzględnych lub nazwanych zakresów.
Więc w końcu otrzymaliśmy wynik.
Więc jak to działa?
Jak wiemy, funkcja SUMIFS może sumować wartości według wielu kryteriów.
W powyższym przykładzie pierwszym kryterium jest suma wszystkich wartości w B3:B10, gdzie data w A3:A10 jest większa lub równa dacie w D3. D3 zawiera 1 stycznia. To też się przekłada.
=SUMA.WARUNKI(B3:B10;A3:A10;">="& "1 stycznia-2019" ,A3:A10;"<="EOMIESIĄC(D3,0))
Następne kryterium jest sumą tylko wtedy, gdy Data w A3:A10 jest mniejsza lub równa EOMONTH(D3,0). EOMONTH Funkcja po prostu zwraca numer seryjny ostatniej podanej daty miesiąca. W końcu przekłada się też formuła.
=SUMA.WARUNKI(B3:B10;A3:A10;">=1-sty-2019” ,A3:A10;"<=31-sty-2019”)
Stąd otrzymujemy sumę z miesiąca na miesiąc w Excelu.
Zaletą tej metody jest możliwość dostosowania daty rozpoczęcia sumowania wartości.
Jeśli daty mają różne lata, najlepiej użyć tabel przestawnych. Tabele przestawne mogą pomóc w łatwym segregowaniu danych w formacie rocznym, kwartalnym i miesięcznym.
Więc tak, tak można sumować wartości według miesiąca. Oba sposoby mają swoje własne specjalności. Wybierz sposób, w jaki lubisz.
Jeśli masz jakiekolwiek pytania dotyczące tego artykułu lub innych zapytań związanych z programem Excel i VBA, sekcja komentarzy jest dla Ciebie otwarta.
Powiązane artykuły:
Jak korzystać z funkcji SUMIF w programie Excel?
SUMIFS z datami w Excelu
SUMA.JEŻELI z niepustymi komórkami
Jak korzystać z funkcji SUMIFS w programie Excel?
SUMIF przy użyciu logiki AND-OR
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność: Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak używać tFunkcja WYSZUKAJ.PIONOWO w programie Excel: Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel: Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel: To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.