Периоды манипулирования датами
У меня эта проблема по работе. Итак, у меня есть этот набор данных:
Client Date Transaction Num
A 7/20/2017 1
A 7/26/2017 1
A 7/31/2017 1
A 8/23/2017 2
A 8/31/2017 2
A 9/11/2017 2
A 9/19/2017 3
A 9/27/2017 3
A 10/4/2017 3
B 6/1/2017 1
B 6/29/2017 1
B 7/6/2017 2
B 8/27/2017 3
B 9/28/2017 4
B 10/16/2017 4
B 11/30/2017 5
Что мне нужно сделать, так это сгенерировать номер транзакции на основе даты для каждого клиента следующим образом:
Для начальной даты (для клиента A это 20.07.17) мне нужно назначить номер начальной транзакции = 1. Затем каждые 30 дней с этой начальной даты мне нужно увеличивать номер транзакции на единицу. Таким образом, 30 дней с 20.07.17 - это 19.08.17, поэтому все даты, попадающие в этот диапазон, получают номер транзакции = 1, а затем, если они превышают, номер транзакции увеличивается на единицу за каждые 30 дней с даты начала. Этот шаблон продолжается, поэтому 30 дней с 19.08.17 равняются 18.09.17, поэтому даты в этом диапазоне получают транзакцию номер = 2, а после 18.09.17 транзакцию получает номер транзакции = 3 и так далее.
Мне нужно сделать это для большого успеха. Любая помощь будет оценена по достоинству. Если на python это проще, дайте мне знать.
Спасибо, Сэмми
Да, для каждого клиента даты будут в порядке возрастания (я исправил ошибку в первой строке B).
Ответы 2
Интересный вопрос, возможно, несколько решений, но я придумал следующее:
Итак, в C1 введите эту формулу:
=FLOOR(ROUND(B1-MIN(IF($A$1:$A$17=A1,$B$1:$B$17)),1)/30,1)+1
Подтвердите, нажав CTRL + SHIFT + ENTER, и перетащите формулу вниз.
Примечание: извините за разницу в расположении дат, мне приходится иметь дело с голландской версией Excel :)
Обновлено: объяснение
Шаг 1 - Получите минимальную дату, соответствующую ячейке A1:
=MIN(IF($A$1:$A$17=A1,$B$1:$B$17))
Шаг 2 - Получите разность ячейки B1 и минимума и округлите ее. Не имеет значения, один или 0 десятичных знаков:
=ROUND(B1-MIN(IF($A$1:$A$17=A1,$B$1:$B$17)),1)
Шаг 3 - Определите разницу через 30 дней:
=ROUND(B1-MIN(IF($A$1:$A$17=A1,$B$1:$B$17)),1)/30
Шаг 4 - Убедитесь, что вы округлили этот результат ниже (вероятно, плохой английский) с функцией пола до ближайшего кратного, до которого вы хотите округлить. В этом случае это будет 1.
=FLOOR(ROUND(B1-MIN(IF($A$1:$A$17=A1,$B$1:$B$17)),1)/30,1)
Шаг 5 - Теперь нам просто нужно добавить 1 к этому результату, чтобы не начинать с 0
=FLOOR(ROUND(B1-MIN(IF($A$1:$A$17=A1,$B$1:$B$17)),1)/30,1)+1
Подтвердите все с помощью CTRL + SHIFT + ENTER
@ Sammy7, надеюсь, мое объяснение подойдет :)
Круто, я получаю все, кроме того, как формула узнает, что следующий клиент, например «B», встречается, а затем использует новый набор минимальных дат?
Хороший ответ. Также можно использовать MINIFS, если вам посчастливилось иметь Excel 365 :-)
Потому что вы перетащите формулу вниз, она будет выглядеть в столбце этой строки. Поиск минимального значения ищет во всем столбце b минимальное значение, в то время как критерий в столбце должен соответствовать этому значению поиска.
Если даты в порядке, вы можете просто выполнить ВПР, чтобы получить первый и вычесть, но ответ @JvdV является более общим
=INT((B2-VLOOKUP(A2,A:B,2,FALSE))/30)+1
Хороший Том! :)
Будут ли даты располагаться в порядке возрастания, как вы их показали (кроме первой из B, которая, возможно, должна быть A)?