Здравствуйте. Задача вычислить среднее последних N элементов часто возникает при анализе бизнес-процессов. Я расскажу, как сделать, чтобы формула принимала аргументом количество ячеек и считала среднее арифметическое именно для них. То есть, количество N последних элементов массива можно будет безболезненно менять.

На картинке с примером – таблица продаж по дням. Нужно посчитать средние продажи за последние 3 дня.

Основой для расчёта будет функция СРЗНАЧ:

=СРЗНАЧ(первый_элемент : последний_элемент)

Теперь выясним, как определить адреса первого и последнего элементов списка в формуле. Для удобства, я присвоил таблице с продажами имя «продажи» (диапазон B3:C12). Для подсчета понадобятся ещё функции ИНДЕКС и ЧСТРОК. А формула будет такой:

=СРЗНАЧ(ИНДЕКС(продажи; ЧСТРОК(продажи)-F2+1; 2): ИНДЕКС(продажи; ЧСТРОК(продажи); 2))

Порядок её работы такой:

  1. Функция ЧСТРОК(продажи) получит количество строк в массиве (=10). Это значение будет номером строки для последнего элемента
  2. ЧСТРОК(продажи)-F2+1 – получаем номер строки для первого элемента (=10-3+1=8)
  3. ИНДЕКС(продажи; ЧСТРОК(продажи)-F2+1; 2) – возвращает координаты начальной ячейки, на пересечении 8-й строки и 2 столбца массива (=C10)
  4. ИНДЕКС(продажи; ЧСТРОК(продажи); 2) – получает координаты конечной ячейки в 10-й строке второй колонки таблицы (С10)
  5. Функции СРЗНАЧ передаётся полученный только что диапазон C8:C10, и она вычисляет среднее арифметическое всех его ячеек.

Давайте проверим:

Если вы пересчитаете вручную среднее арифметическое последних трёх элементов – получите тот же результат – 1135. Теперь можно изменить тройку в ячейке F2 на другое число, результат будет пересчитан.

Скачать пример