Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Присвоения номера периода по дате - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Присвоения номера периода по дате (Формулы/Formulas)
Присвоения номера периода по дате
Shtein Дата: Пятница, 20.08.2021, 21:11 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
Доброго всем вечера.
Помогите, пожалуйста, в очень непростом вопросе.

Есть база данных с тремя столбцами: клиент, сумма покупок, дата покупки. 1 строка - 1 операция
Данные в массиве отсортированы по столбцам клиент и потом по дате. В столбце D формула, показывающая порядковые номера покупок конкретного клиента.
Я хочу сделать сводную таблицу и определить, сколько покупок делает клиент в первые 12 месяцев после 1-й покупки, сколько во вторые 12 месяцев (то есть 13-24 месяц или 2-й год после 1-й операции), сколько в третьи 12 месяцев и т.д.
Как я ни крутил данные, к одному виду привести сложно, т.к. у каждого клиента своя "стартовая дата" 1-й покупки, и соответственно у него свой промежуток в год, два, три.

Я думаю каждой покупке присвоить порядковый номер, который показал бы период, по конкретному клиенту, начиная от конкретно его даты 1-й покупки.
В столбце Е я прописал как это выглядит, вручную прописал результат.
На столбец F не обращайте внимание, это я посчитал дни от даты 1-й покупки конкретного клиента, чтобы вручную прописать период в ст.Е.
К сообщению приложен файл: 8410503.xlsx (17.4 Kb)


"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.
 
Ответить
СообщениеДоброго всем вечера.
Помогите, пожалуйста, в очень непростом вопросе.

Есть база данных с тремя столбцами: клиент, сумма покупок, дата покупки. 1 строка - 1 операция
Данные в массиве отсортированы по столбцам клиент и потом по дате. В столбце D формула, показывающая порядковые номера покупок конкретного клиента.
Я хочу сделать сводную таблицу и определить, сколько покупок делает клиент в первые 12 месяцев после 1-й покупки, сколько во вторые 12 месяцев (то есть 13-24 месяц или 2-й год после 1-й операции), сколько в третьи 12 месяцев и т.д.
Как я ни крутил данные, к одному виду привести сложно, т.к. у каждого клиента своя "стартовая дата" 1-й покупки, и соответственно у него свой промежуток в год, два, три.

Я думаю каждой покупке присвоить порядковый номер, который показал бы период, по конкретному клиенту, начиная от конкретно его даты 1-й покупки.
В столбце Е я прописал как это выглядит, вручную прописал результат.
На столбец F не обращайте внимание, это я посчитал дни от даты 1-й покупки конкретного клиента, чтобы вручную прописать период в ст.Е.

Автор - Shtein
Дата добавления - 20.08.2021 в 21:11
Pelena Дата: Пятница, 20.08.2021, 21:45 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19167
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Формула для периода
Код
=РАЗНДАТ(МИН(ЕСЛИ($A$2:$A$43=A2;$C$2:$C$43));C2;"y")+1

и сводная
К сообщению приложен файл: 9070601.xlsx (19.4 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Формула для периода
Код
=РАЗНДАТ(МИН(ЕСЛИ($A$2:$A$43=A2;$C$2:$C$43));C2;"y")+1

и сводная

Автор - Pelena
Дата добавления - 20.08.2021 в 21:45
Shtein Дата: Суббота, 21.08.2021, 00:45 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
Спасибо огромное hands
Тут ещё и формула массива


"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.


Сообщение отредактировал Shtein - Суббота, 21.08.2021, 00:46
 
Ответить
СообщениеСпасибо огромное hands
Тут ещё и формула массива

Автор - Shtein
Дата добавления - 21.08.2021 в 00:45
bmv98rus Дата: Суббота, 21.08.2021, 09:45 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
формула массива

ну можно и без
Код
=DATEDIF(AGGREGATE(15;6;$C$2:$C$43/($A$2:$A$43=A2);1);C2;"y")+1


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
формула массива

ну можно и без
Код
=DATEDIF(AGGREGATE(15;6;$C$2:$C$43/($A$2:$A$43=A2);1);C2;"y")+1

Автор - bmv98rus
Дата добавления - 21.08.2021 в 09:45
Shtein Дата: Суббота, 21.08.2021, 16:47 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
Спасибо, попробую и так)) у меня файл на 80 тысяч строк, формула массива протягивалась больше 3 часов. Но работала, а это главное. Попробую второй вариант.


"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.
 
Ответить
СообщениеСпасибо, попробую и так)) у меня файл на 80 тысяч строк, формула массива протягивалась больше 3 часов. Но работала, а это главное. Попробую второй вариант.

Автор - Shtein
Дата добавления - 21.08.2021 в 16:47
bmv98rus Дата: Суббота, 21.08.2021, 18:15 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
80 тысяч строк,
тогда через запросы MSQuery лучше сделать, а потом сводной на этой основе. правда это несколько сложнее, но будет в разы быстрее, или вычисление минимальной даты вынести в отдельный столбец.
Код
=IF(A2=A1;E1;AGGREGATE(15;6;$C$2:$C$43/($A$2:$A$43=A2);1))

Код
=DATEDIF(E2;C2;"y")+1

Это должно снизить нагрузку.
К сообщению приложен файл: example2408.xlsx (18.8 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Суббота, 21.08.2021, 18:23
 
Ответить
Сообщение
80 тысяч строк,
тогда через запросы MSQuery лучше сделать, а потом сводной на этой основе. правда это несколько сложнее, но будет в разы быстрее, или вычисление минимальной даты вынести в отдельный столбец.
Код
=IF(A2=A1;E1;AGGREGATE(15;6;$C$2:$C$43/($A$2:$A$43=A2);1))

Код
=DATEDIF(E2;C2;"y")+1

Это должно снизить нагрузку.

Автор - bmv98rus
Дата добавления - 21.08.2021 в 18:15
Shtein Дата: Суббота, 21.08.2021, 19:04 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
запросы MSQuery

имели ввиду Power Query? Я вообще делал запросы, но в данной задаче как-то не вижу применения.
Вот что еще ресурсоемко, это операция сортировки по двум столбцам, по емаил и дате, жутко долго 80 тысяч строк обрабатывает. В редакторе Power Query можно сделать легко сортировку по 1 столбцу, но вот по 2-м не видел.


"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.
 
Ответить
Сообщение
запросы MSQuery

имели ввиду Power Query? Я вообще делал запросы, но в данной задаче как-то не вижу применения.
Вот что еще ресурсоемко, это операция сортировки по двум столбцам, по емаил и дате, жутко долго 80 тысяч строк обрабатывает. В редакторе Power Query можно сделать легко сортировку по 1 столбцу, но вот по 2-м не видел.

Автор - Shtein
Дата добавления - 21.08.2021 в 19:04
bmv98rus Дата: Суббота, 21.08.2021, 19:08 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
имели ввиду Power Query?
нет именно MS и оно может быть источником для сводной. и с сортировкой и там и там проблем нет, при этом она и не нужна там будет.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
имели ввиду Power Query?
нет именно MS и оно может быть источником для сводной. и с сортировкой и там и там проблем нет, при этом она и не нужна там будет.

Автор - bmv98rus
Дата добавления - 21.08.2021 в 19:08
Shtein Дата: Суббота, 21.08.2021, 19:11 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 103
Репутация: 0 ±
Замечаний: 0% ±

Excel 2021
пошел гуглить, я даже не слышал про MSQuery ))
Power Query знаю, книгу Павлова прочитал, и разные гайды смотрю, сам даже кое-что делал, а вот с MSQuery не знаком


"В мире давным давно все известно, главное знать у кого спросить"
Рэй Бредбери.


Сообщение отредактировал Shtein - Суббота, 21.08.2021, 19:18
 
Ответить
Сообщениепошел гуглить, я даже не слышал про MSQuery ))
Power Query знаю, книгу Павлова прочитал, и разные гайды смотрю, сам даже кое-что делал, а вот с MSQuery не знаком

Автор - Shtein
Дата добавления - 21.08.2021 в 19:11
bmv98rus Дата: Суббота, 21.08.2021, 19:13 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Shtein, ну скажем так, с одним запросом вам не справится будет. там вложенность нужна и тут надо иметь навык.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеShtein, ну скажем так, с одним запросом вам не справится будет. там вложенность нужна и тут надо иметь навык.

Автор - bmv98rus
Дата добавления - 21.08.2021 в 19:13
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Присвоения номера периода по дате (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!