Formuła ogólna
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Podsumowanie
Aby uzyskać średnią miesięczną, możesz użyć formuły opartej na funkcji ŚREDNIA.WARUNKÓW z pomocą funkcji EOMONTH. W pokazanym przykładzie formuła w F4 to:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Ta formuła wykorzystuje nazwane zakresy „kwoty” (D5: D104) i „daty” (C5: C104).
Wyjaśnienie
Funkcja ŚREDNIA.JEŻELI może uśredniać zakresy na podstawie wielu kryteriów. W tym przypadku konfigurujemy AVERAGEIFS do średnich kwot według miesiąca przy użyciu dwóch kryteriów: (1) dopasuj daty większe lub równe pierwszemu dniu miesiąca, (2) dopasuj daty mniejsze lub równe ostatniemu dniu miesiąca. Gdybyśmy na stałe zakodowali daty ze stycznia 2016 r. W formule za pomocą funkcji DATA, wyglądałoby to tak.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Ale nie chcemy ustalać dat na stałe, chcemy, aby Excel generował te daty za nas. Zwykle jest to uciążliwe, ponieważ jeśli dodasz nazwy miesięcy jako tekst (np. "Styczeń", "luty", "marzec" itp.) W kolumnie F, 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 F zamiast wpisywać nazwy miesięcy dodajemy rzeczywiste daty pierwszego dnia każdego miesiąca (01.01.2016, 02.01.2016, 3 / 1/2016 itd.) I użyj niestandardowego formatu daty („mmm”), aby wyświetlić nazwy miesięcy.
Ułatwia to tworzenie kryteriów, których potrzebujemy dla AVERAGEIFS. 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:
"<="&EOMONTH(E4,0)
EOMONTH automatycznie zwraca ostatni dzień tego samego miesiąca, ponieważ jako argument miesięcy podajemy zero.
Uwaga: konkatenacja z ampersandem (&) jest konieczna podczas tworzenia kryteriów na podstawie odwołania do komórki.
Rozwiązanie tabeli przestawnej
Tabela przestawna to doskonałe rozwiązanie, gdy trzeba podsumować dane według roku, miesiąca, kwartału itd., Ponieważ tabele przestawne zapewniają elementy sterujące do automatycznego grupowania według daty. Aby zobaczyć bezpośrednie porównanie formuł z tabelami przestawnymi, zobacz ten film: Dlaczego tabele przestawne.
