Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Количество номеров, по которым есть запись с разницей в 1 час.
 
#1
27.01.2020 14:32:37
Всем привет!
Помогите с решением, пожалуйста.

Дано: номер/дата/время
Задача: посчитать кол-во номеров по которым есть запись с разницей в 1 час.
Пояснение: номера входящих обращений это столбец А, по ним есть дата и время. Нужно посчитать сколько клиентов перезвонили в течении часа от своего первого обращения. Кол-во строк не лимитировано, поэтому решить вопрос нужно либо макросом либо powerpivot/powerquery

Пробы - ИНДЕКС И ПОСИКПОЗ
1) при больших объемах умирает комп
2) нужно сортировать даты и время по порядку

Я предполагаю что это можно реализовать методом запроса powerpivot/powerquery но не нашла примерно похожих примеров в сети, в связи с чем задаю вопрос тут в надежде на умные головы.

Подсчет нужен на постоянной основе
Пример файла во вложении

Спасибо!
 
 
 
#2
30.01.2020 02:01:55
К сожалению решение макросом либо powerpivot/powerquery никто не предлагает. Наверное, потому что не совсем ясен алгоритм. Поэтому предлагаю более простое решение - функциями Экселя.
Формулы прописал только до 4000 строки. Дальше протяните сами. А то размер файла вылазит за 300 кб.

Ответьте, «умирает ли комп». И как выглядит процесс - «Подсчет нужен на постоянной основе»

Кстати, теперь алгоритм решения можно «выцарапать» из внешнего вида таблицы и формул таблицы.
Конечно если предложенное решение правильное.
 
 
 
#3
30.01.2020 02:30:59
anikravtsova, в правилах форума читали, что такое ПРИМЕР? Зачем почти 5000 строк с  данными?

Цитата
Valera2 написал: Формулы прописал только до 4000 строки.
Только?! Зачем столько? Зачем подгонять под 300 кБ? Автор что, не сможет сам протянуть после 10-й строки, нужно обязательно 4000?

Заменил файл. Неужели 20 кБ мало, чтобы  применить решение?
 
 
 
#4
31.01.2020 09:15:03
Цитата
Valera2 написал:
Поэтому предлагаю более простое решение
Вот это класс! Я не ТС, но спасибо, очень красивое решение. :)
(«комп умирает» только при протягивании формулы с UDF в крайнем правом столбце).
 
 
 
#5
31.01.2020 11:08:20
Доброе время суток
Цитата
Valera2 написал:
powerquery никто не предлагает.
Руки не дошли :)   Вариант
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    lessOrEqualHour = #duration(0, 1, 0, 0),
    callDate = Table.AddColumn(Source, "callDate", each [dbdate] + Duration.From([time]), DateTime.Type)[[НОМЕР], [callDate]],
    defineStats = Table.Group(callDate, {"НОМЕР"}, {"lessOrEqualHourStats", (sub) =>
    let
        setOrder = Table.Sort(sub[[callDate]], {"callDate", Order.Ascending}),
        rowCount = Table.RowCount(sub),
        shifts = Table.FromColumns({List.Range(setOrder[callDate], 1), List.Range(setOrder[callDate], 0, rowCount - 1)}),
        diff = Table.AddColumn(shifts, "diff", each [Column1] - [Column2], Duration.Type),
        filter = Table.SelectRows(diff, each [diff] < lessOrEqualHour)
    in
        [min = Table.Min(filter, "diff"), max = Table.Max(filter, "diff"), count = Table.RowCount(filter)]
    }),
    statsExpanded = Table.ExpandRecordColumn(defineStats, "lessOrEqualHourStats", {"min", "max", "count"}),
    validOnly = Table.SelectRows(statsExpanded, each [min] <> null),
    createStatsInfo = Table.AddColumn(validOnly, "temp", each [
        minStarted = [min][Column2],
        nextMinStarted = [min][Column1],
        minDuration = [min][diff], 
        maxStarted = [max][Column2],
        nextMaxStarted = [max][Column1],
        maxDuration = [max][diff]
    ])[[НОМЕР], [temp], [count]],
    #"Expanded {0}" = Table.ExpandRecordColumn(createStatsInfo, "temp", {"minStarted", "nextMinStarted", "minDuration", "maxStarted", "nextMaxStarted", "maxDuration"}, {"minStarted", "nextMinStarted", "minDuration", "maxStarted", "nextMaxStarted", "maxDuration"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded {0}",{{"minStarted", type datetime}, {"nextMinStarted", type datetime}, {"maxStarted", type datetime}, {"nextMaxStarted", type datetime}, {"minDuration", type duration}, {"maxDuration", type duration}, {"НОМЕР", type text}})
in
    #"Changed Type"
Изменено: Андрей VG - 31.01.2020 11:15:14 (Добавил - сколько было таких перезвонов)
 
 
 
#6
31.01.2020 14:24:14
Андрей VG, у меня другой результат :)  Номер45 не захватило и много других. Позже проверю поштучно
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Added Custom1" = Table.AddColumn(Source, "datatime", each Number.From([dbdate])+Number.From([time]))[[НОМЕР],[datatime]],
    Custom = Table.Group(#"Added Custom1", {"НОМЕР"}, {"temp", each [ПервыйЗвонок = _{0}?[datatime]?, ВторойЗвонок = _{1}?[datatime]? ]}),
    #"Expanded {0}" = Table.ExpandRecordColumn(Custom, "temp", {"ПервыйЗвонок", "ВторойЗвонок"}),
    #"Added Custom" = Table.AddColumn(#"Expanded {0}", "Пользовательская", each ([ВторойЗвонок]-[ПервыйЗвонок])*24*60>0 and
    ([ВторойЗвонок]-[ПервыйЗвонок])*24*60<=60),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"ПервыйЗвонок", type datetime}, {"ВторойЗвонок", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Пользовательская] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Пользовательская"})
in
    #"Removed Columns"
 
 
 
#7
31.01.2020 14:33:45
Цитата
abc1 написал:
Номер45 не захватило
Выборка
номер4519.01.202010:48:52
номер4519.01.202012:18:36
номер4519.01.202017:25:45
номер4519.01.202017:43:10
номер4519.01.202018:38:01
Два звонка перекрываются
 
 
 
#8
31.01.2020 14:41:06
Андрей VG, я так понял задачу: найти номера, у которых между ПЕРВЫМ и СЛЕДУЮЩИМ(если следующий звонок был) звонком интервал не более часа
 
 
 
#9
31.01.2020 14:57:13
Цитата
abc1 написал:
между ПЕРВЫМ
А вот тут, коллега, мы не дождёмся определения - что такое первый.
1. Это самая первая запись по дате и времени во входных данных для этого номера - получается то, что делали вы.
2. Первый это любой звонок данного номера, для которого следующий звонок наступил по дате и времени менее чем через час.

Плюс, ещё один момент
Цитата
Нужно посчитать сколько клиентов перезвонили в течении часа от своего первого обращения
А где, собственно, данные самих обращений? По идее, должен быть ещё один столбец - номер обращения!

Народ пошёл последнее время креативный :)  Им такие детали скучны и не интересны - слишком приземлены и не дают простора полёту фантазии...
Изменено: Андрей VG - 31.01.2020 15:02:55
 
 
 
#10
04.02.2020 22:31:53
USER=101479]zav[/USER], спасибо за хорошие слова, ласкающие глаз и греющие душу.
И спасибо за деликатное второе предложение.
Да, а что такое ТС?
 
 
 
#11
04.02.2020 22:42:31
Цитата
Valera2 написал: что такое ТС?
в данном контексте топикстартер (ТС) транслитерация с англ. topic starter «начинающий тему»
*Викисловарь
 
 
 
Страницы: 1
Наверх