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

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

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

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

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

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

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

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

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

Источник

2 ответа

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

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

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

Затем создайте столбец «Ранг». Формула для ячейки 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 и вниз. Наконец, создайте формулу среднего значения.

Источник

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

Столбец Row — это простая серия индексов, начинающаяся с 1, столбец Person2 аналогичен Person , но использует функцию ЕСЛИ для вставки нулевой строки в строки с нулевым значением Sales. MCount предоставляет количество месяцев с ненулевыми продажами по Person .

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

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

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

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

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

Столбцы Month1 , Month2 и Month3 — это номера месяцев в столбце MCount (списка данных), соответствующие первому, второму и третьему месяцам, используемым для расчета среднего значения за 3 месяца, а Sales1 , Sales2 и Sales3 — значения продаж для этих трех месяцев. месяцы. Среднее значение — это рассчитанное среднее значение за 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)))

Часть TRUE первого IF просто возвращает 0, если строка соответствует нулевому значению Sales .

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

Если значения RowM1 и Row различаются, строка 3 в таблице данных соответствует следующему месяцу продаж (после первого) для человека в ячейке E3. В этом случае номер месяца получается путем прибавления 1 к максимальному значению в столбце F для продавца в ячейке E3 в предыдущих строках списка данных. Для определения этого максимума используется функция MAXIFS.

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

Примечания

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