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

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

Данные, которыми я обладаю: Дата транзакции (A:A), Имя клиента (B:B), Номер заказа на продажу (C:C), Название продукта (D:D), Единицы (E:E), Доход (F:F)

Новым заказом считается все, что клиент не заказывал в течение последних 6 месяцев или вообще никогда.

Повторный заказ будет оформлен, если клиент приобрел данный конкретный товар в течение последних 6 месяцев.

Я не могу понять, как воплотить эту логику в формулу Excel.

решение1

Возможно, я что-то не понимаю, но это кажется довольно простым. Я понимаю вопрос так: строка представляет собой повторный заказ, если есть хотя бы одна строка над текущей, которая имеет то же Имя клиента (Столбец  B), что и текущая строка, то же Имя продукта (Столбец  D) и Дату транзакции (Столбец  A) в течение последних шести месяцев от текущей Даты транзакции. Столбцы  C, Eи  Fможно игнорировать. Я предполагаю, что строки отсортированы по Дате транзакции (хотя, полагаю, мне не нужно делать это предположение).

Критерий даты транзакции является «самым сложным» (я использую этот термин в широком смысле). Прошедшая дата находится в пределах последних шести месяцев,  A2если она

> EDATE(A2,-6)

Итак, чтобы подсчитать строки вплоть до текущей, которые удовлетворяют трем критериям, мы используем

=COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)

Нотация A$2:A2интересная. Она представляет собой диапазон, который начинается в строке 2 и заканчивается в текущей строке; т. е. все до текущей строки (включая ее). Это число всегда будет не меньше 1, поскольку текущая строка имеет значение. Если оно больше 1, то была по крайней мере одна предыдущая строка, которая также совпала. Поэтому ответ — ввести

=IF(COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)<=1, "New Order", "Reorder")

в H2(или куда хотите) и перетащите/заполните вниз.

электронная таблица, показывающая подсчет и интерпретацию подсчета


Если строки находятся в неправильном порядке, нам нужно выполнить поиск по всей таблице и проверить, что дата меньше текущей даты:

=IF(COUNTIFS(A$2:A$99, ">" & EDATE(A2,-6), A$2:A$99, "<" & A2,
                                           B$2:B$99, B2, D$2:D$99, D2)=0, "New Order", "Reorder")

где я использую 99для представления последней строки данных. Я изменил тест с <=1на,  =0 поскольку < A2тест исключает текущую строку. Если ваши данные могут включать несколько строк с одинаковыми именем клиента и названием продукта, а также с одинаковой датой транзакции, укажите, как их следует обрабатывать.

решение2

О, это вам понравится.

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

Настраивать

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

рабочий лист

Шаг 1: Установите диапазон поиска

Сначала нам нужно узнать, когда было 6 месяцев назад (и где в таблице находится эта дата). В каждой записи мы будем использовать, EDATEчтобы узнать.

В ячейке G2:

VLOOKUP(EDATE(A2,-6),A:A,1,TRUE)

Это здорово - это дает нам последнюю запись о продаже, которая у нас есть, которая была более 6 месяцев назад. Обратите внимание, что если у вас нечастые продажи (т. е. большие разрывы между записями о продаже), то, как я это написал, означает, что вы можете "поймать" ложные срабатывания, если последний заказ рассматриваемого товара был, скажем, 6 месяцев и неделю назад И не было вообще никаких заказов между 6 месяцами и неделей назад и ровно 6 недель назад. Я решил, что это "достаточно близко" для вас.

Единственный другой недостаток — это некоторые неприятные ошибки в верхней части листа, потому что для первой записи (т. е. строки 2) нет никаких записей 6-месячной давности вообще. Так что давайте обернем с помощью IFERROR:

=IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2)

Другими словами, сообщите мне, когда был последний заказ, сделанный более 6 месяцев назад, если только его нет; в таком случае просто сообщите мне первый заказ, о котором у нас есть записи.

Эта формула копируется вниз. Вот здесь:

помощник1

Шаг 2: Превратите начальные данные диапазона в местоположение ячейки

В ячейке H2:

=MATCH(G2,A:A,0)

Это просто. В каком порядковом месте в столбце Aмы находим дату 6-месячной давности, которую мы установили на шаге 1?

Эта формула копируется вниз. Вот мы:

помощник2

Шаг 3: Сделайте это

Давайте перейдем к примеру, I21, чтобы увидеть, как это работает.

=INDEX(INDIRECT("A"&H21&":D"&ROW()-1),MATCH(D21,INDIRECT("D"&H21&":D"&ROW()-1),0),1)

Это, по сути, просто простой INDEX MATCHпоиск. Но мы заменили ссылки на ячейки массивов этих функций INDIRECTссылками на значение, которое мы только что засеяли в столбец H.

В моем образце данных строка 21— это запись о продажах с датой 10/1/2018. Столбец Gнаходит самую последнюю запись о продажах более чем за 6 месяцев до этой даты, которая в моем образце датой является 4/1/2018. Столбец Hприкрепляет (первое) местоположение этой даты в столбце Aна 8-й строке. Таким образом, INDIRECT("A"&H21&массив для поиска начинается со строки, Aидентифицированной значением в H. Отлично! А другая половина INDIRECT, ":D"&ROW()-1)говорит о завершении массива на строке, Dкоторая находится на одну строку выше записи, в которой мы сейчас находимся.

Другими словами, вы могли бы написать

=INDEX(A1:D999,MATCH(D21,D1:D9999,0),1)

Найти первое появление кода вашего продукта во DВСЕЙ книге продаж - но мы не хотим этого делать. Поэтому мы заменяем ссылки на ячейки динамическими диапазонами, которые мы использовали на предыдущих двух шагах для генерации. Со мной?

Вот как это выглядит:

найти совпадение

После того, как мы скопируем эту формулу (обернутую в другую IFERRORдля видимости) вверх по строке 2и обратно вниз, у нас получится:

Франкенштейн

В первом ряду ложный положительный результат. С этим мне тоже придется жить.

Шаг 4: Объединить

Итак, формула в Iссылках H, которая ссылается на G. Обратное расширение дает окончательную формулу, которая выглядит так:

=IFERROR(INDEX(INDIRECT("A"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),MATCH(D2,INDIRECT("D"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),0),1),"NEW ORDER")

И выглядит так:

супер финал

Так что, если хотите, вы можете просто скопировать эту формулу в столбец «переупорядочить» в строке 2и скопировать вниз.

Примечание.

  1. Вы собираетесь использовать Format Painter в поле вывода, поскольку формулы в том виде, в котором они написаны, будут обрабатывать текстовые даты Aкак порядковые номера дат и также возвращать порядковый номер.
  2. Обратите внимание на слабость Шага 1 — на снимке экрана поля helper1на Шаге 3 вы можете видеть, что в моих данных образца дата 6-месячной давности для 1 октября была 4/1, а дата 6-месячной давности для 5 октября былатакже4/1, потому что между 4/1 и 4/5 не было вообще никаких заказов. Это может потенциально вызвать ложные срабатывания.
  3. Опять же, как обсуждалось в Шаге 3, при использовании этого метода в первой записи о продажах также будет ложноположительный результат.

решение3

Мой подход к решению проблемы немного отличается, так как я выбрал тему OP.

  1. Новым заказом считается все, что клиент не заказывал в течение последних 6 месяцев или вообще никогда.

  2. Повторный заказ будет оформлен, если клиент приобрел данный конкретный товар в течение последних 6 месяцев.


введите описание изображения здесь

  • Формула массива (CSE) в ячейке H41, закончить сCtrl+Shift+Enter.

{=IFERROR(LOOKUP(DATEDIF(IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match"),I41,"m"),{0,6,12},{"New Order","Order before 6 month","Order before 12 months"}),"Cust's. New Pro. Order")}


Ситуация 1:

Новая дата тяги: 03/26/19.

Имя Клиента: Bob.

Название продукта: Cake.

Заказать Сатаус: Order before 12 months.

введите описание изображения здесь


Ситуация 2:

Новая дата тяги: 03/26/19.

Имя Клиента: Bob.

Название продукта: Milk.

Заказать Сатаус: New Order.

введите описание изображения здесь

Примечание.

Поскольку разница между старой датой транзакции ( 10/01/18) и новой датой транзакции ( 03/26/19) составляет менее 6 месяцев.


Ситуация 3:

Новая дата тяги: 03/26/19.

Имя Клиента: Bob.

Название продукта: Wheat.

Заказать Сатаус: Order before 6 months.

введите описание изображения здесь

Ситуация 4:

Новая дата тяги: 03/26/19.

Имя Клиента: Bob.

Название продукта: Fruit.

Заказать Сатаус: Cust's. New Pro.Order.

введите описание изображения здесь


Примечание:

Если вы введете имя нового клиента, а также старый или новый продукт и дату, вы получите Cust's . New Pro. Orderстатус.

введите описание изображения здесь


Теперь позвольте мне объяснить, как работает Формула.

Формулу можно разделить на две части.

Part 1

 {=IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match")}

По сути, это поиск по 2 критериям, который находит Old Transaction Date, Customer & the Productа формула учитывает его, поскольку Start Dateформула DATEDIFнаходится в пределах A41:A47..

Part 2

Оригинал DATEDIF:

{=LOOKUP(DATEDIF(A41:A47,I41,"m"),{0,6,12},{"New order","Order before 6 month","Order before 12 months"})}

Где A41:A47было заменено на Part 1Формулу как Start Dateи End Dateнаходится в ячейке I41.

И обе части красиво упакованы IFERROR.

Примечание.

  • При необходимости вы можете изменить ссылки на ячейки.
  • Сообщения с формулой также могут быть изменены по вашему желанию.

решение4

Вы можете использовать следующую формулу:

=IF( SUM( ($B$2:$B9=A10) * ($D$2:$D9=D10) * ($A$2:$A9>($A10-183)) ) = 0, "New", "Reorder" )

Он использует старую технику простых проверок «range=" для создания массивов, которые сообщают вам каждую вещь (есть ли имя клиента в какой-либо ячейке диапазона столбца A? Находится ли название продукта в диапазоне столбца D? Находятся ли даты в столбце A в пределах 183 дней от сегодняшнего заказа?), а затем умножает их для получения окончательного массива.

Необработанные значения массива являются значениями ИСТИНА/ЛОЖЬ, но их умножение заставляет Excel изменить их на значения 1/0, которые прекрасно умножаются. Конечный результат — массив с единицами, где все три вышеуказанных условия выполнены, и нулями, где это не так. Excel не преобразует элементы массива обратно в значения ИСТИНА/ЛОЖЬ, поэтому весь массив является числовым.

SUMзатем суммирует их все в одно значение. Если результат любой, кроме 0, то по крайней мере один такой заказ существует за последние 183 дня. Если 0, то таких не существует. Просто IFпроверяет, какой это результат, и сообщает вам «Новый» или «Повторный заказ».

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

Если не ясно с самого начала, я утверждаю, что информация появляется в ячейке столбца G строки только что введенного заказа. Однако она может быть в другом месте, в том числе даже не в той же строке, не на том же листе, и может использоваться при создании теста условного форматирования для изменения цветов ячеек строки, чтобы таким образом отображать «Новый/Переупорядочить».

Связанный контент