Excel: как получить среднее значение за последние 3 месяца, не используя пустые месяцы?

Question

Привет, люди, которые лучше меня разбираются в Excel! Я ценю любую помощь, которую вы можете предложить. Постараюсь быть кратким:

У меня есть таблица из 3 столбцов. Месяц (как 1,2,3 и т. д.), Имя продавца и Продажи. Каждая строка — это сколько долларов продаж получил продавец за этот месяц.

Он организован следующим образом: первые 1000 строк — все за янв. 1, следующие 1000 — все за февр. 2 и т. д.

Мне нужно получить среднюю сумму в $, проданную каждым продавцом за последние 3 месяца. Но — если один из этих последних 3 месяцев пуст, мне все равно нужно среднее за 3 месяца.

Поэтому мне нужна формула, которая распознавала бы, например, пустой результат за сентябрь и вместо него получала бы следующий самый последний результат (август, июль, что бы это ни было), чтобы она всегда усредняла результаты этого продавца за последние 3 месяца, а не только 1 или 2 результата.

Сейчас у меня очень простая сводная таблица для этого. Она отлично работала для моих целей, пока я не понял эту проблему :(

Есть ли способ (сводная таблица или нет), чтобы это можно было сделать? У нас более 1000 продавцов, поэтому вручную это невыполнимо. У меня много людей без цифр в течение месяца по какой-то причине, поэтому мне нужно заставить расчет работать вокруг этого.

Спасибо за любую помощь! Пожалуйста, дайте мне знать, если я могу что-то еще добавить или если это неясно.

Answer 1

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

Создайте таблицу на основе необработанных данных, а затем отсортируйте ее по месяцам (по убыванию) и лицам.

Далее отфильтруйте по столбцу Продажи и снимите выделение с пустых мест. Затем скопируйте это содержимое в другую область и вставьте.

Затем создайте столбец «Ранг». Формула для ячейки D2, которая ранжирует каждый месяц (самый последний — 1 и т. д.) для каждого человека.

    =IF(B2=B1,D1+1,1)

Затем создайте еще одну область с конкретными людьми (столбец F) и столбцы-заполнители для их последних 3 месяцев (столбцы с обозначениями 1, 2 и 3).

Формула ячейки G2:

    =SUMIFS($C:$C,$B:$B,$F2,$D:$D,G$1)

Перетащите его на заголовок столбца 3 и вниз. Наконец, создайте формулу для расчета среднего значения.

Answer 2

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

СтолбецРядпредставляет собой простую серию индексов, начинающуюся с 1, столбецЧеловек2такой же какЧеловекно использует функцию IF для вставки нулевой строки в строки с нулевыми значениямиПродажи.MCountобеспечивает подсчет, поЧеловек, месяцев с ненулевымПродажи.

MCountвзаимодействует со списком результатов, который содержит строку для каждого продавца, как показано далее.

В списке результатов, столбецМесяцыпросто подсчитывает количество месяцев с ненулевыми продажами в списке данных для каждого продавца. Формула в ячейке I2:

=COUNTIFS(A$2:A$21,H2,$C$2:$C$21,">0")

СтрокаM1показываетРядзначение в списке данных, соответствующее первому месяцу с ненулевыми продажами для каждого продавца. Формула в ячейке J2:

=MATCH(H2,$E$2:$E$21,0)

КолонныМесяц1,Месяц2иМесяц3номера месяцев в столбцеMCount(из списка данных), соответствующих первому, второму и третьему месяцам, используемым для расчета 3-месячного среднего значения, в то время какПродажи1,Продажи2иПродажи3— это объемы продаж за эти 3 месяца.Средний— это рассчитанное среднее значение за 3 месяца.

Формулы для ячеек K2, L2 и M2 соответственно:

=I2-1

=K2+1

=L2+1

Формула в ячейке N2:

=SUMIFS($C$2:$C$21,$E$2:$E$21,$H2,$F$2:$F$21,K2)

и опирается на ценности вMCountстолбец списка данных (диапазон $F$2:$F$21, см. далее). Ячейка N2 копируется в ячейки O2 и P2.

Диапазон I2:Q2 можно скопировать во все последующие строки списка результатов.

Список данных - столбецMCount

Формула в ячейке F2:

=IF(C2>0,1,0)

Это помещает 0 или 1 в ячейку. 0 указывает на то, что у человека в ячейке E2 не было продаж в месяце, указанном в ячейке B2, тогда как 1 указывает на то, что это был первый месяц продаж для этого человека.

Формула в ячейке F3 значительно сложнее и имеет вид

=IF(LEN(E3)=0,0,IF(VLOOKUP(E3,H$2:J$3,3,FALSE)=D3,1,1+MAXIFS(F$2:F2,E$2:E2,E3)))

Часть ИСТИНА первого ЕСЛИ просто возвращает 0, если строка соответствуетПродажинуля.

В противном случае,Продажине равны нулю и срабатывает второй IF. В этом втором IFЧеловекв ячейке E3 ищется в таблице результатов и, если СтрокаM1значение (номер строки в списке данных, соответствующий первому месяцу продаж) соответствуетРядзначение в ячейке D3, это означает, что строка 3 соответствует первой строке с ненулевыми продажами для лица, указанного в ячейке E3.

ЕслиСтрокаM1иРядзначения различаются, строка 3 в таблице данных соответствует последующему месяцу продаж (после первого) для человека в ячейке E3. В этом случае номер месяца получается путем прибавления 1 к максимальному значению в столбце F для продавца в ячейке E3 в предыдущих строках списка данных. Для определения этого максимума используется функция МАКСЕСЛИМН.

Формула в ячейке F3 копируется в последующие строки списка данных. Правильное использование относительной и абсолютной адресации в формуле гарантирует, что функция МАКСЕСЛИ использует соответствующие диапазоны ячеек в копиях.

Примечания

1. Для использования MAXIFS требуется Excel 2019 или более поздняя версия.
2. Предполагается, что (i) данные расположены в хронологическом порядке и (ii) имеется не более одной ненулевой строки данных о продажах для каждой комбинации продавца и месяца.
3. На изображениях синий шрифт обозначает формулу, а черный шрифт — статическое значение.