Excel: вычислить скользящее среднее на основе динамического диапазона

Question

Я пытаюсь рассчитать скользящее среднее на основе динамического диапазона. Чтобы дать вам пример, мой набор данных выглядит так, как показано ниже. Я пытаюсь вычислить скользящее среднее значение параметра "Количество пользователей" на основе диапазона "Ящик №". (12, 13, 14...). Обычно, можно вычислить скользящее среднее путем вычисления среднего для первых N строк, а затем перетащить его вниз. Однако вопроса здесь нет. пользователей для ящика нет. столбцы различаются, и поэтому я не могу использовать традиционный метод скользящего среднего.

Набор данных (учитывайте все значения в разных строках под столбцом № ящика и Пользователи)

Ящик № = 12, 12, 12, 13, 13, 14, 14, 14, 14, 14

Пользователи = 5, 5, 8, 6, 8, 10, 8, 3, 5, 1

Итак, я ищу такой результат.

Результат (рассмотрите все значения в другой строке в столбце № ячейки и скользящее среднее для № пользователей)

Ящик № 12, 13, 14

Скользящее среднее для (Количество пользователей) = 6, 7, 5,4

Любая помощь будет оценена.

Answer 1

Здесь есть две части.

ЧАСТЬ 1

Чтобы рассмотреть динамический диапазон, я предлагаю вам использовать таблицу. Если ваш набор данных это:

затем щелкните в наборе данных и выберите Table от Insert вкладка команды. Убедитесь, что у вас есть My table has headers выбран.

Результат должен выглядеть так:

Цвета могут варьироваться в зависимости от вашей версии и настроек.

ЧАСТЬ 2

Чтобы получить средние значения на основе этой таблицы, щелкните в таблице и выберите Pivot Table от Insert вкладка команды. Примите значения по умолчанию, и вы окажетесь на новом листе с пустой сводной таблицей.

Перетащите Box No. поле до Rows блок и Users поле до Values блок. Нажмите на стрелку справа от User имя в Values заблокировать и выбрать Value Field Settings... Затем выберите Average в Summarize Values By, Вы также можете переименовать отображаемое имя поля в Custom Name:,

Примечание. После добавления данных щелкните правой кнопкой мыши в сводной таблице и выберите Refresh чтобы увидеть новые данные.

Answer 2

Когда вы говорите "скользящее среднее", вы подразумеваете что-то вроде "среднего, если", которое катится? Ваш пример просто показывает условное среднее, а не скользящее.

Кажется, что ваша проблема может быть решена с помощью averageif(), Для более сложных случаев вы можете использовать формулу average() с if() формула внутри, которая задает условия, которые вы хотите соответствовать, чтобы вычислить среднее. Это становится формулой массива и должно быть введено как Ctrl+Shift+Enter или эквивалентный.

Допустим, у вас есть данные вашего ящика в B1:K14 и ваши данные о пользователях в B2:K14. Вы получите свои результаты для Box 12 с обоими:

{= СРЗНАЧ (ЕСЛИ (В1: К1=12,B2: К2 ""))}

а также

= СРЕСЛИ (В1:K1,12, В2: К2)

Конечно, условия могут быть в соседних ячейках, чтобы их можно было легко распространить на разные ящики или даже пользователей.