Formuła ogólna
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Podsumowanie
Aby uśrednić czas odpowiedzi w miesiącu, możesz użyć formuły opartej na funkcji ŚREDNIA.WARUNKÓW wraz z funkcją EOMONTH.
W pokazanym przykładzie formuła w G5 to:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Wyjaśnienie
Ta formuła używa nazwanych zakresów „dat” (B5: B25) i „czasów trwania” (D5: D25). Kolumna D czasów trwania jest w minutach i jest obliczana przez odjęcie daty otwarcia od daty zamknięcia.
Funkcja ŚREDNIA.JEŻELI służy do uśredniania zakresów na podstawie wielu kryteriów. W tym przypadku konfigurujemy AVERAGEIFS do średnich czasów trwania według miesiąca przy użyciu dwóch kryteriów: (1) dopasowanie dat większych lub równych pierwszemu dniu miesiąca, (2) dopasowanie dat mniejszych lub równych ostatniemu dniu miesiąca.
Aby podzielić daty na miesiące, używamy prostego triku, aby ułatwić sprawę: w kolumnie F zamiast wpisywać nazwy miesięcy („Jan”, „Lut”, Mar ”itp.) Dodajemy rzeczywiste daty pierwszego z każdego miesiąca (1 stycznia 2016 r., 1 stycznia 2016 r., 1 marca 2016 r., itd.) Następnie używamy niestandardowego formatu daty („mmm”) do wyświetlania nazw miesięcy.
To znacznie ułatwia budowanie kryteriów, których potrzebujemy dla ŚREDNIA.JEŻELI przy użyciu wartości w kolumnie F. Aby dopasować daty większe lub równe pierwszemu dzień miesiąca, używamy:
">="&F5
Aby dopasować daty mniejsze lub równe ostatniemu dniu miesiąca, używamy:
"<="&EOMONTH(F5,0)
Otrzymujemy EOMONTH, aby zwrócić ostatni dzień tego samego miesiąca, podając zero dla argumentu miesięcy .
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ć lub uśrednić dane według roku, miesiąca, kwartału itd., Ponieważ tabele przestawne zapewniają kontrolę automatycznego grupowania dat. Aby zobaczyć bezpośrednie porównanie formuł z tabelami przestawnymi, zobacz ten film: Dlaczego tabele przestawne.
