Formuła ogólna
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Podsumowanie
Aby podsumować według miesięcy, możesz użyć formuły opartej na funkcji SUMA.WARUNKÓW z pomocą funkcji EOMONTH. W pokazanym przykładzie formuła w F4 to:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Ta formuła używa nazwanych zakresów „kwota” (C4: C9) i „data” (B4: B9).
Wyjaśnienie
Funkcja SUMA.WARUNKÓW może sumować wartości w zakresach na podstawie wielu kryteriów.
W tym przypadku konfigurujemy SUMA.WARUNKÓW, aby sumować kwoty według miesiąca przy użyciu dwóch kryteriów: jednego do dopasowania dat większych lub równych pierwszemu dniu miesiąca, drugiego do dopasowania dat mniejszych lub równych ostatniemu dniu miesiąca. Innymi słowy, potrzebujemy takiego wzoru:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Lub bardziej kuloodporna alternatywa:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Ta wersja jest bardziej niezawodna, ponieważ daty są konstruowane z osobnymi wartościami roku, miesiąca i dnia i nie są wprowadzane jako tekst, który musi zostać zinterpretowany przez program Excel.
Zwykle jest to uciążliwe, ponieważ jeśli dodasz nazwy miesięcy jako tekst (np. „Styczeń”, „luty”, „marzec” itp.) W kolumnie E, musisz zadać sobie dodatkowy trud tworzenia dat, których możesz użyć jako kryteriów . Jednak w tym przypadku używamy prostego triku, aby to ułatwić: w kolumnie E zamiast wpisywać nazwy miesięcy dodajemy rzeczywiste daty (01.01.2016, 02.01.2016, 3.01.2016), następnie użyj niestandardowego formatu daty („mmmm”), aby wyświetlić nazwy miesięcy.
Ułatwia to tworzenie kryteriów, których potrzebujemy dla SUMIFS. Aby dopasować daty większe lub równe pierwszemu dniu miesiąca, używamy:
">="&E4
Aby dopasować daty mniejsze lub równe ostatniemu dniu miesiąca, używamy tego:
"<="&EOMONTH(E4,0)
Z zerem dla miesięcy funkcja EOMONTH automatycznie zwraca ostatni dzień miesiąca podany przez datę.
Połączenie z ampersandem (&) jest konieczne podczas budowania kryteriów, które używają operatora logicznego z wartością liczbową.
Rozwiązanie tabeli przestawnej
Tabela przestawna to doskonałe rozwiązanie, gdy chcesz podsumować dane według roku, miesiąca, kwartału itd., Ponieważ wykonają tego rodzaju grupowanie bez żadnych formuł. Aby zobaczyć bezpośrednie porównanie formuł z tabelami przestawnymi, zobacz ten film: Dlaczego tabele przestawne.
