Давайте рассмотрим несколько аспектов этого вопроса отдельно:
ТЛ; др
=AVERAGE(INDIRECT("A2:"&ADDRESS(2,MONTH(NOW())-1)))
Почему ваша формула не работает?
Функция AVERAGEIF принимает аргументы критериев, но они, похоже, не способны делать что-либо очень сложное. Я не смог найти истинного объяснения их синтаксиса, но примеры в онлайн-документации не делают ничего более глубокого, чем ">32" , и вы пытаетесь использовать целые формулы. Я не видел никаких доказательств того, что этот подход будет работать даже в теории. (Я бы с радостью показал, что это неправильно!) AVERAGEIF (или AVERAGEIFS) не та функция, которую вы хотите.
Усреднение диапазона
Усреднить диапазон значений легко. Вы явно уже знаете, как это сделать, чтобы даже задавать вопрос, который вы есть! Предполагая значения в A2:L2 (верхние колонтитулы месяца):
=AVERAGE(A2:L2)
Но подождите, вы только хотели усреднить до и не включая текущий месяц. Так что нам нужен способ расчета диапазона, который входит в базовую формулу среднего. В мае это A2:D2 . Как мы можем рассчитать это?
Формула для расчета диапазона
Итак, новая проблема заключается в том, как получить диапазон (ссылку на функцию AVERAGE ) из текущего месяца. Функции Lookup & Reference помогут вам здесь. Возможно, есть более простой способ сделать это, но лучшее, что я мог придумать, это использовать INDIRECT для возврата ссылки / диапазона, если мы дадим ему соответствующий текст, что является более легкой проблемой. Теперь все, что нам нужно, это придумать текст «A2:D2» (на май). А2 достаточно прост: должен быть постоянным. Может быть способ конвертировать 4 (для апреля, последнего месяца, который вы хотите) в D (нужный вам столбец), но вы также можете использовать функцию ADDRESS для вычисления D2 по номерам строк / столбцов. Обратите внимание, что он думает "назад" от нормального. Таким образом, A2 будет введен как ADDRESS(2,1) . А в вашем случае вы знаете строку 2 и можете рассчитать нужный вам столбец, используя нужную функцию MONTH , а затем вычесть 1, чтобы вернуться к прошлому месяцу. Таким образом, мы можем получить 'D2' с помощью ADDRESS(2,MONTH(NOW())-1) . Тогда это просто вопрос, чтобы собрать это вместе. Объединение A2 и D2 сделано с амперсандом.
="A2:"&(ADDRESS(2,MONTH(NOW())-1))
Собираем все вместе
Теперь у нас есть текстовое представление области, которую мы хотим усреднить. Функция INDIRECT преобразует его в реальный диапазон, который может использовать функция AVERAGE . Последняя функция затем усредняет все это вместе:
=AVERAGE(INDIRECT("A2:"&ADDRESS(2,MONTH(NOW())-1)))
Некоторые заметки
Если ваш правильный диапазон действительно U192:AF192, замените 2 в формуле на 192 и добавьте 20 к параметру строки (так как U - 20 строк справа от A).
Я также прилагаю скриншот того, как рассчитываются различные промежуточные шаги. Окончательная формула - та, что в строке 8. Я также сделал следующий шаг - усреднил только последние несколько месяцев, а не весь год, используя ту же идею в строке 12.
Обратите внимание, что весь этот подход будет зависеть от того, когда открывается электронная таблица. Если вы откроете его в следующем феврале, в среднем он будет составлять только два месяца, а не весь 2017 год. Январь задохнется полностью. Возможно, вы захотите придумать более разумную формулу для обработки параметра месяца.
