10 C
Москва
Среда, 28 сентября, 2022

Как вернуть среднемесячные продажи за последние n лет в Microsoft Excel

Популярное

[ad_1]

Изображение: Сай/Adobe Stock

Усреднение — это простое дело в Майкрософт Эксель. Благодаря функциям СРЗНАЧЕСЛИ() и СРЗНАЧЕСЛИМН() условные средние значения также просты, пока вы не захотите, чтобы условие было динамическим. Например, если вы ведете таблицу ежемесячных показателей продаж, и кто-то попросит вас дать среднее значение за последние n лет, вы можете почесать в затылке и почувствовать себя немного ошеломленным.

К счастью, удовлетворить этот запрос n проще, чем вы думаете. В этом уроке я покажу вам, как комбинировать часто используемые функции и функции, чтобы получить среднемесячные значения за последние n лет. Техника также хорошо подходит для приборной панели.

я использую Майкрософт 365 desktop в 64-разрядной системе Windows 10, но вы можете использовать более ранние версии .xlsx. Excel для Интернета полностью поддерживает этот метод. Для вашего удобства вы можете скачать демонстрационный файл .xlsx.

ПОСМОТРЕТЬ: Сравнение функций: программное обеспечение и системы учета рабочего времени (TechRepublic Premium)

Как быстро найти среднее значение в Excel

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

  1. Выберите данные о продажах за годы, которые вы хотите усреднить. Например, если вам нужны средние продажи за последние два года, 2021 и 2020, выберите C3:C26.
  2. На панели задач вы найдете среднее значение для выбранных значений. В данном случае это 1 356 157 долларов США, как показано на Рисунок А.
  3. Дважды щелкните среднее значение на панели задач.
  4. Выберите ячейку, в которую вы хотите ввести это среднее значение, и нажмите Ctrl + V.

Рисунок А

Как вернуть среднемесячные продажи за последние n лет в Microsoft Excel ⋆ VOLDEMARU ExcelAvgSales A
Изображение: Сьюзан Харкинс/TechRepublic. Excel отображает среднее значение для выбранных значений на панели задач.

Как вы можете видеть в Рисунок Б, вы можете скопировать среднее значение с панели задач на свой лист. Это довольно новая функция, поэтому вы, возможно, еще не слышали о ней. Если вам не нужно снова вычислять это значение, используйте этот быстрый метод для возврата среднего значения выбранных значений.

Рисунок Б

Как вернуть среднемесячные продажи за последние n лет в Microsoft Excel ⋆ VOLDEMARU ExcelAvgSales B
Изображение: Сьюзан Харкинс/TechRepublic. Вы можете скопировать среднее значение с панели задач в ячейку.

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

Как создать год из даты в Excel

Если вам повезет, ваши данные будут содержать год как единственное значение. В нашем примере листа (рисунок A) есть два столбца: дата месяца и объем продаж за каждый месяц. Это означает, что первое, что нам нужно сделать, это заполнить столбец соответствующим годом для каждой даты. В реальной ситуации у вас могут быть данные за десятки лет, но мы остановимся на четырех для простоты.

Чтобы вернуть год для каждого значения даты в столбце B, введите следующую функцию в D3 и скопируйте ее в остальные записи:

=YEAR([@Month])

Используйте эту функцию, если вы не используете таблицу:

=YEAR(B3)

Рисунок С показывает результаты. Если эта простая функция возвращает ошибку, проверьте значение даты, потому что, скорее всего, это недопустимая дата. Наш следующий шаг — добавить матрицу на основе уникального списка значений года.

Рисунок С

Как вернуть среднемесячные продажи за последние n лет в Microsoft Excel ⋆ VOLDEMARU ExcelAvgSales C
Изображение: Сьюзан Харкинс/TechRepublic. Функция Year() возвращает год для каждой даты в столбце B.

Теперь нам нужен способ работы с компонентом n, который ограничит оцениваемые объемы продаж.

Как разместить н

Следующим шагом является размещение компонента n, который будет определять, сколько лет вы хотите оценить. Рисунок D показывает несколько новых меток и выражений. Выражения в столбце I — это выражения, используемые в столбце G.

Рисунок D

Как вернуть среднемесячные продажи за последние n лет в Microsoft Excel ⋆ VOLDEMARU ExcelAvgSales D
Изображение: Сьюзан Харкинс/TechRepublic. Выражения в столбце I содержат компонент n.

В двух словах, G3 — это входное значение — оно равно количеству прошлых лет, которые вы хотите оценить. Например, если вы введете 2, два последних года будут 2021 и 2020, если вы введете 3, последние три года будут 2021, 2020 и 2019 и так далее.

Функция MAX() возвращает последний год в таблице продаж (B2:D50). Вы также можете ввести последний год в качестве буквального значения. В любом случае работает, но использование Max() избавляет от необходимости знать последний год. Нет необходимости сортировать данные, чтобы все работало. В зависимости от того, сколько данных имеется и откуда они были получены, визуально определить последний год может быть сложно.

Выражение =G3-(G2-1) возвращает самый старый год в оцениваемой таблице продаж. G3 всегда является последним годом с текущими данными. Это выражение вычитается из последнего года в G2, чтобы определить самый ранний год, включенный в среднее значение.

Последнее выражение, которое мы рассмотрим, =IFERROR(AVERAGEIF(Sales[Year],">="&$G$4,Sales[Sales]),""), выполняет большую часть тяжелой работы. Функция AVERAGEIF() использует компонент критерия «>=»&$G$4 для определения лет, представленных n. Например, если вы введете 2, этот компонент оценивается следующим образом:

">="&$G$4

">="&2020

>=2020

Следовательно, функция СРЗНАЧЕСЛИ() оценивает только значения продаж, для которых год в столбце D больше или равен 2020. В нашей таблице это означает 2020 и 2021 годы. 1, 2, 3 или 4 — потому что таблица содержит записи только за четыре года.

Вы также можете использовать элемент управления проверкой данных, чтобы отклонить входные значения, отличные от 1, 2, 3 или 4. Просто выберите G2, перейдите на вкладку «Данные», а затем выберите «Проверка данных» в группе «Инструменты данных». В появившемся диалоговом окне выберите «Целое число» в раскрывающемся списке «Разрешить», «Между» в раскрывающемся списке «Данные», а затем введите 1 и 4 в поля «Минимум» и «Максимум» соответственно, как показано на Рисунок Е.

Рисунок Е

Как вернуть среднемесячные продажи за последние n лет в Microsoft Excel ⋆ VOLDEMARU ExcelAvgSales E
Изображение: Сьюзан Харкинс/TechRepublic. Определите элемент управления проверкой данных, который отклоняет значения, отличные от 1, 2, 3 или 4.

Как вы можете видеть в Рисунок F, среднемесячный объем продаж за последние два года составляет 1 356 156,88 долларов США. Попробуйте ввести 1, 3 и 4, чтобы представить последний год, последние три года и последние 4 года соответственно. Затем попробуйте ввести 0 или 5. Элемент управления проверкой данных отклоняет недопустимые входные значения.

Рисунок F

Как вернуть среднемесячные продажи за последние n лет в Microsoft Excel ⋆ VOLDEMARU ExcelAvgSales F
Изображение: Сьюзан Харкинс/TechRepublic. Функция усреднения возвращает то же самое, что и предыдущий прием быстрого выбора (рис. B).

Теперь удалите значение в G2 и посмотрите, что произойдет. Вы ожидали ошибку? ISERROR() перехватывает эту ошибку и возвращает пустую строку («») вместо ошибки.

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

Оригинал

[ad_2]

- Advertisement -

Выбор редакции

ОСТАВЬТЕ ОТВЕТ

Пожалуйста, введите ваш комментарий!
пожалуйста, введите ваше имя здесь

- Advertisement -

Последние статьи