Данные, которыми я обладаю: Дата транзакции (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 месяцев назад, если только его нет; в таком случае просто сообщите мне первый заказ, о котором у нас есть записи.
Эта формула копируется вниз. Вот здесь:
Шаг 2: Превратите начальные данные диапазона в местоположение ячейки
В ячейке H2
:
=MATCH(G2,A:A,0)
Это просто. В каком порядковом месте в столбце A
мы находим дату 6-месячной давности, которую мы установили на шаге 1?
Эта формула копируется вниз. Вот мы:
Шаг 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
и скопировать вниз.
Примечание.
- Вы собираетесь использовать Format Painter в поле вывода, поскольку формулы в том виде, в котором они написаны, будут обрабатывать текстовые даты
A
как порядковые номера дат и также возвращать порядковый номер. - Обратите внимание на слабость Шага 1 — на снимке экрана поля
helper1
на Шаге 3 вы можете видеть, что в моих данных образца дата 6-месячной давности для 1 октября была 4/1, а дата 6-месячной давности для 5 октября былатакже4/1, потому что между 4/1 и 4/5 не было вообще никаких заказов. Это может потенциально вызвать ложные срабатывания. - Опять же, как обсуждалось в Шаге 3, при использовании этого метода в первой записи о продажах также будет ложноположительный результат.
решение3
Мой подход к решению проблемы немного отличается, так как я выбрал тему OP.
Новым заказом считается все, что клиент не заказывал в течение последних 6 месяцев или вообще никогда.
Повторный заказ будет оформлен, если клиент приобрел данный конкретный товар в течение последних 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 строки только что введенного заказа. Однако она может быть в другом месте, в том числе даже не в той же строке, не на том же листе, и может использоваться при создании теста условного форматирования для изменения цветов ячеек строки, чтобы таким образом отображать «Новый/Переупорядочить».