Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Рассчитать время исходящих вызовов в Power Query
 
#1
05.02.2020 15:16:10
Коллеги, добрый день! Прошу помощи в решении задачи. Необходимо рассчитать время исходящих вызовов с помощью PQ. В файле-примере есть несколько таблиц таблиц:
Таблица data это исходные данные.
Таблица Решение это это решение задачи с помощью формул.
В запросе PQ попытался реализовать ту же логику расчета, что и с помощью формул. Вроде даже цифры сходятся, но если в таблице с исходными данными в столбце I удалить отмеченные желтым ячейки, то PQ вместо результата выдаст пустоту. Оно и понятно, ведь когда мы удалим 0 в столбце I, то PQ воспримет их как null, а все математические операции с null возвращают null. Подскажите пожалуйста как это решить? Также при решении задачи в PQ использовал функцию Table.Group(), возможно есть более красивое решение.
 
 
 
#2
05.02.2020 15:39:05
А почему нельзя просто сразу после импорта заменить в столбцах с данными null на 0?
Код
#"Замененное значение" = Table.ReplaceValue(source,null,0,Replacer.ReplaceValue,{"Avail", "TotalLogOnTime", "TotalNotReadyTime", "BusyOther", "TalkTime", "HoldTime", "WrapTime", "Reserved"}),
Я не волшебник, я только учусь.
 
 
 
#3
05.02.2020 15:44:01
Wiss, блин точно. Все же просто. Спасибо вам  :)  
 
 
 
#4
05.02.2020 16:03:32
Murderface_, не пойму, почему таблицы Решение  и data без значков фильтра? Как так же сделать?
 
 
 
#5
05.02.2020 16:08:41
Murderface_, Это лишнее преобразование, а PQ и так скоростью не блещет. Проще уж так:
Код
List.Sum({
                List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[BusyOther]),
               -List.Sum(Table.SelectRows(_, each ([Навык] = "voice"))[TalkTime]),
               -List.Sum(Table.SelectRows(_, each ([Навык] = "voice"))[HoldTime]),
               -List.Sum(Table.SelectRows(_, each ([Навык] = "voice"))[WrapTime]),
               -List.Sum(Table.SelectRows(_, each ([Навык] = "voice"))[Reserved]),
                List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[TalkTime]),
                List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[HoldTime]),
                List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[WrapTime]),
                List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[Reserved]) })


abc1, не очень понял, ваш вопрос про это?
Изменено: PooHkrd - 05.02.2020 16:56:35
Вот горшок пустой, он предмет простой...
 
 
 
#6
05.02.2020 17:03:49
PooHkrd, да, про это. Спасибо
 
 
 
#7
06.02.2020 07:05:43
PooHkrd, то что нужно. Ваше решение позволило побороть еще одну проблему, например, когда у человека вообще нет навыка voice, то было null, а тут все работает. Спасибо большое  :)  
 
 
 
#8
06.02.2020 09:50:06
Не в тему, но все же закину формулу.
В S2 массивную
=СУММ(ЕСЛИ((data[Agent]=[@Агент])*(data[Навык]="default");data[[BusyOther]:[Reserved]]))-СУММ(ЕСЛИ((data[Agent]=[@Агент])*(data[Навык]="voice");data[[TalkTime]:[Reserved]]))
и запрос
Код
let    source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
Add = Table.AddColumn( Table.AddColumn(source, "a", each List.Sum
(List.Range(Record.FieldValues(_),6,5))), "b", each List.Sum(List.Range
(Record.FieldValues(_),7,4))) in Table.Group(Add, {"Agent"}, {{"обзвон",
 each List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[a]) -
  List.Sum(Table.SelectRows(_, each ([Навык] = "voice"))[b]) }})

Изменено: abc1 - 06.02.2020 12:30:07
 
 
 
#9
06.02.2020 14:05:21
abc1, ого, ничего не понятно, но очень интересно. Обязательно разберусь на досуге. Спасибо. Кстати если у сотрудника не будет навыка voice, то ваш вариант выдаст null. Вот дополнил пример.
 
 
 
#10
06.02.2020 15:03:27
Murderface_, походу нашел выход :)
Код
let    source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
Add = Table.AddColumn( Table.AddColumn(source, "a", each List.Sum
(List.Range(Record.FieldValues(_),6,5))), "b", each List.Sum(List.Range
(Record.FieldValues(_),7,4))) in Table.Group(Add, {"Agent"}, {{"обзвон",
 each List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[a]) -
  List.Sum({0}&Table.SelectRows(_, each ([Навык] = "voice"))[b]) }})
 
 
 
#11
06.02.2020 15:13:53
abc1, спасибо, работает!
Код
{0}&Table.SelectRows
Подскажите пожалуйста как это работает?
 
 
 
#12
06.02.2020 15:24:09
Цитата
Murderface_ написал:
как это работает?
Я как понимаю?
5 + null = null    
5 + 0 = 5
5 + Sum.List( {0,null} ) = 5    или       5 + Sum.List( {0} & {null} ) = 5
Вот это и сделал в последнем решении
 
 
 
#13
06.02.2020 15:29:33
abc1, круто! Спасибо еще раз  :)  
 
 
 
#14
07.02.2020 09:43:30
Добрый день! Подскажите пожалуйста как можно сделать, чтобы при получении отрицательного времени исходящего обзвона результат выводился как null? Кроме логической проверки
Скрытый текст
я ничего не придумал. Но это какой то лютый колхоз получается. Уверен, что существует более красивое решение.
 
 
 
#15
07.02.2020 10:02:45
Может так?
Код
let 
    sum = List.Sum({
               List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[BusyOther]),
              -List.Sum(Table.SelectRows(_, each ([Навык] = "voice"))[TalkTime]),
              -List.Sum(Table.SelectRows(_, each ([Навык] = "voice"))[HoldTime]),
              -List.Sum(Table.SelectRows(_, each ([Навык] = "voice"))[WrapTime]),
              -List.Sum(Table.SelectRows(_, each ([Навык] = "voice"))[Reserved]),
               List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[TalkTime]),
               List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[HoldTime]),
               List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[WrapTime]),
               List.Sum(Table.SelectRows(_, each ([Навык] = "default"))[Reserved])})
in
    if sum<0 then null else sum
Вот горшок пустой, он предмет простой...
 
 
 
#16
07.02.2020 10:06:38
PooHkrd, Спасибо! Возьму на заметку.
 
 
 
#17
07.02.2020 10:33:42
А вообще ИМХО так нагляднее, и по идее работать должно шустрее:
Код
let 
    default = Table.Buffer( Table.SelectRows(_, each [Навык] = "default") ),
    voice = Table.Buffer( Table.SelectRows(_, each [Навык] = "voice") ),
    sum = List.Sum({
    List.Sum(List.Combine({ default[BusyOther], default[TalkTime], default[HoldTime], default[WrapTime], default[Reserved] }) ),
    -List.Sum(List.Combine({ voice[TalkTime], voice[HoldTime], voice[WrapTime], voice[Reserved]}) ) })
in
    if sum<0 then null else sum
Изменено: PooHkrd - 07.02.2020 10:37:31 (исправил формулу)
Вот горшок пустой, он предмет простой...
 
 
 
#18
07.02.2020 11:32:13
PooHkrd, очень круто! Перенесу именно это решение в рабочий файл.
 
 
 
Страницы: 1
Наверх