Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PQ фильтрация по дате и удаление промежуточных строк
 
#1
09.06.2023 09:23:40
Добрый день!
Форумчане, прошу Вас о помощи. Есть выгрузка с кпп по входу/выходу сотрудников. Необходимо для анализа по каждой дате и каждому сотруднику оставить первое время (пришел на работу) и последнее (ушел с работы), а все промежуточные входы выходы не учитывать. Это основная проблема. В идеале, хотелось бы получить, чтобы оставшиеся данные выделялись цветом если человек пришел на работу позже 8:30 и ушел раньше 17:30.
Я что-то совсем растерялся, с одной стороны понимаю что не так сложно, а с другой не пойму с какой стороны зайти. Причем найти похожий вариант не смог, а может просто неверно формулировал запрос.
Заранее спасибо.

Попробовал сделать условиями.
Выдало следующую ошибку:
Expression.Error: Не удается применить доступ к полям в типе List.
Сведения:
   Value=List
   Key=Копия Столбец7.1
Как это можно исправить?
Изменено: Роман Богуславский - 10.06.2023 09:51:58
 
 
 
#2
12.06.2023 09:38:29
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Источник, each not List.Contains({null, "", "ФИО сотрудника"}, [Столбец1])),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Столбец7", type datetime}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Столбец7]), type date),
    f = (tbl as table) =>
        [a = Table.Sort(tbl, "Столбец7"),
        b = Table.RowCount(a),
        c = if b = 1 then tbl else Table.Combine({Table.FirstN(a, 1), Table.LastN(a, 1)})][c],
    g = Table.Group(#"Inserted Date", {"Столбец1", "Date"}, {{"r", each f(_)}}),
    z = Table.Combine(g[r])
in
    z
Пришелец-прораб.
 
 
 
#3
12.06.2023 10:18:47
Alien Sphinx, Большое спасибо! Я оказывается пытался пойти по совсем другому пути и обнаружил, что были бы ошибки.
 
 
 
#4
13.06.2023 18:21:37
Мне кажется, для целей анализа вам нужно не две строки, а одна - с началом и концом.
в таком случае всё делается кнопками:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Removed Top Rows" = Table.Skip(Источник,8),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"ФИО сотрудника", "Компания", "Подразделение", "Дата и время"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [ФИО сотрудника] <> null and [ФИО сотрудника] <> "ФИО сотрудника" and [ФИО сотрудника] <> ""),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Дата и время", type datetime}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Дата", each DateTime.Date([Дата и время]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted Date", {"ФИО сотрудника", "Компания", "Подразделение", "Дата"}, {{"Start", each List.Min([Дата и время]), type nullable datetime}, {"Finish", each List.Max([Дата и время]), type nullable datetime}})
in
    #"Grouped Rows"

Но вот вопрос - а у вас всегда последняя запись за день - это выход, а первая - вход? насколько знаю, на практике такое может встречаться вообще не всегда :)
F1 творит чудеса
 
 
 
#5
13.06.2023 19:29:28
Цитата
Максим Зеленский написал:
насколько знаю, на практике такое может встречаться вообще не всегда :)
"Теперь самодеятельность. Артисты, приглашенные нами на Первое мая, до сих пор не ушли. Мы все знаем их репертуар"
Пришелец-прораб.
 
 
 
#6
13.06.2023 20:04:11
Максим Зеленский, Максим, спасибо большое, это то, что я сам пытался сделать, по сути последнего шага не хватило. Сюда выкладывал предварительный вариант и пока никто не отвечал пытался сделать сам. Вариант с кнопками мне больше подходит.
Alien Sphinx, предложил очень интересный вариант, но с кодом мне пока сложновато, пришлось код подробно разбирать с помощью ChatGPT, только после этого стал понятен алгоритм.
 
 
 
#7
15.06.2023 15:03:33
Роман Богуславский, главное, не пытайтесь пока писать код М при помощи ChatGPT :)

и да, я хотел еще раз намекнуть. Сейчас мой код просто дает первое и последнее время за дату. Но если первое событие в дне - не вход, а выход, и/или последнее событие - не выход, а вход, то результат будет неверный.
Поэтому и задал уточняющий вопрос - всегда ли всё правильно, или есть нарушители, которые вошли и забыли отметиться на выходе, или забыли отметиться на входе. Тогда код будет сложнее.

Alien Sphinx, спасибо, поностальгировал :)
Изменено: Максим Зеленский - 15.06.2023 15:07:17
F1 творит чудеса
 
 
 
#8
15.06.2023 15:18:46
Максим Зеленский, я понял, учтем. Я думаю, что если такие случаи и бывают, то не часто. В любом случае, эта обработка уже огромный плюс и позволит существенно сократить время обработки  
 
 
 
Страницы: 1
Наверх