Среднее по месяцам в нескольких диапазонах дат

Question

У меня есть данные по месяцам с января 2013 по октябрь 2017 года как таковые

MONTH   MY DATA
Jan-13  45.0
Feb-13  23.0
.   
.   
.   
Oct-17  98.4

Я хочу усреднить данные по календарному месяцу, для конкретных, несмежных диапазонов дат. Например, январь 2013 г. -> март 2013 г. и октябрь 2016 г. -> май 2017 г.

Я полагаю, что этого можно достичь, используя функции IF с OR для охвата нескольких диапазонов дат, и AND, чтобы охватить две даты в каждом диапазоне, для возврата массива (при вводе в качестве формулы массива), а затем с помощью среднего значения в возвращенном массиве.

С моими данными в B5:C62 и моими индивидуальными диапазонами дат, настроенными как:

Period 1 Start: S2
Period 1 End: T2
Period 2 Start: S3
Period 2 End: T3

И мои календарные месяцы в P6:P17, я ввожу свою формулу как

=AVERAGEIF($B$5:$B$62,P6,IF(OR(AND($B$5:$B$62>=$S$2,$B$5:$B$62<=$T$2),
AND($B$5:$B$62<=$S$3,$B$5:$B$62>=$T$3)),$C$5:$C$62,"ERROR"))

Я получаю # ЗНАЧЕНИЕ! ошибка, хотя я не понимаю, почему. Насколько я могу судить, мое предложение IF должно возвращать "средний_диапазон" для работы формулы AVERAGEIF.

В чем ошибка в моей формуле или моем подходе?

Answer 1

В вашей формуле часть IF() не будет возвращать массив, когда есть AND() как часть логического теста. Вы можете проверить это, используя встроенный инструмент отладки для формул Excel: выделите часть формулы на панели формул и нажмите F9.

Но есть способ рассчитать среднее значение, которое вы хотите. В Excel умножение логических значений преобразует Истинные / Ложные значения в 1 и 0, которыми затем можно манипулировать. В качестве примера это утверждение

=(A2:A25>=D2)*(A2:A25<=E2)+(A2:A25>=D3)*(A2:A25<=E3)

возвращает массив

{0;0;0;1;1;1;1;0;0;0;0;0;0;0;0;0;1;1;1;1;1;1;0;0}

из данных в таблице ниже. Здесь умножение действует как логическое И, а сложение почти как логическое ИЛИ. (@ Máté Juhász может позвонить сюда, чтобы объяснить, почему это не совсем ИЛИ :-)

Этот массив можно рассматривать как "маску" столбца A с единицами, соответствующими двум диапазонам дат, указанным в начальной и конечной датах.

Теперь формула IF() может использовать этот массив для генерации списка значений, которые вы хотите усреднить:

IF(((A2:A25>=D2)*(A2:A25<=E2)+(A2:A25>=D3)*(A2:A25<=E3)),B2:B25)

Это дает массив

{FALSE;FALSE;FALSE;42.9;82.3;90.5;15.6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;70.8;85.5;19.2;85.4;21.3;55.7;FALSE;FALSE}

который теперь может быть передан в AVERAGE().

Таким образом, одним из решений вашей проблемы является формула, аналогичная формуле (массива) в D6:

=AVERAGE(IF(((A2:A25>=D2)*(A2:A25<=E2)+(A2:A25>=D3)*(A2:A25<=E3)),B2:B25))

Измените диапазоны и назначенные значения даты, чтобы соответствовать вашей ситуации.

Ячейка ниже "Чек" содержит ручной расчет среднего значения с использованием

=AVERAGE(B5:B8,B18:B23)

Надеюсь, что это помогает и удачи.