Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Разделение промежутка времени на дни, Есть дата и время старта и дата и время финиша. Необходимо разделить на промежутки.
 
#1
14.09.2022 13:10:37
Всем привет!
Задачка такая: нужно создать файл, в котором пользователь задаст даты и время старта поездок и даты и время финиша. Excel должен разложить каждый промежуток следующим образом (во вложении). Думал попробовать сделать это при помощи Powerquery, но не придумал, как конкретно.  
Изменено: Excelopfer - 14.09.2022 13:56:17 (Уточнил задачу)
 
 
 
#2
14.09.2022 13:32:10
Excelopfer, Добрый день!
Реализация на коленке формулами, смотрите вложение.
 
 
 
#3
14.09.2022 13:35:29
Цитата
написал:
Excelopfer, Добрый день!
Реализация на коленке формулами, смотрите вложение.
Доброго дня! Спасибо, но не совсем то, что я хотел. Пользователь будет вводить несколько дат и времен старта и финиша. и на каждый должно быть такое разделение. Формулами тут вряд ли. Скорее Powerquery или макрос.
 
 
 
#4
14.09.2022 13:36:11
Код
=ПОСЛЕД(ЦЕЛОЕ(C6)-ЦЕЛОЕ(B6)+1;1;ЦЕЛОЕ(B6);1)
=ЕСЛИ(ПОСЛЕД(ЦЕЛОЕ(C6)-ЦЕЛОЕ(B6)+1;1;ЦЕЛОЕ(B6);1)=ЦЕЛОЕ(B6);B6-ЦЕЛОЕ(B6);0)
=ЕСЛИ(ПОСЛЕД(ЦЕЛОЕ(C6)-ЦЕЛОЕ(B6)+1;1;ЦЕЛОЕ(B6);1)=ЦЕЛОЕ(C6);C6-ЦЕЛОЕ(C6);0.999305555555556)
 
 
 
#5
14.09.2022 13:39:13
Excelopfer,
Ну началось, уже несколько дат, в примере же только один диапазон) в описании так же ничего не сказано про
Цитата
Excelopfer написал:
Пользователь будет вводить несколько дат и времен старта и финиша. и на каждый должно быть такое разделение.

Пишите сразу все условия и переделайте пример.....а то с вашими растущими хотелками далеко не уйдете в поиске решения.
Удачи.
Изменено: Wild.Godlike - 14.09.2022 13:40:52
 
 
 
#6
14.09.2022 13:41:24
Сделал тоже для одной строки, выложу уж, не пропадать же  :D
Код
let
    C = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]{0}[Старт],
    ДО= Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]{0}[финиш],
    Источник = List.Dates(Date.From(C), Number.From(Date.From(ДО))- Number.From(Date.From(C))+1, #duration(1,0,0,0)),
    ВТаблицу = Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(Источник, Splitter.SplitByNothing(), null, null, ExtraValues.Error), {"Column1", "Результат"}), {"Результат", type date}),
    Старт = Table.AddColumn(ВТаблицу, "Старт", each if [Результат] = Date.From(C) then Time.From(C) else #time(0,0,0), type time),
    Финиш = Table.AddColumn(Старт, "Финиш", each if [Результат] = Date.From(ДО) then Time.From(ДО) else #time(23,59,0), type time)
in
    Финиш
 
 
 
#7
14.09.2022 13:54:20
см. вложение
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#8
14.09.2022 13:56:54
Резонно. Исправил
Изменено: Excelopfer - 14.09.2022 15:06:38
 
 
 
#9
14.09.2022 13:58:17
Цитата
написал:
см. вложение
Спасибо, но не совсем то. Формулами тут вряд ли.  
 
 
 
#10
14.09.2022 14:19:53
в какой строке
на примере каких Д1 и Д2 не то?
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#11
14.09.2022 14:27:14
Цитата
написал:
в какой строке
на примере каких Д1 и Д2 не то
Написал уточнение к задаче. Нужно, чтобы можно было задавать несколько интервалов в одной форме.  
 
 
 
#12
14.09.2022 14:32:25
Цитата
Excelopfer написал:
Формулами тут вряд ли.  
Как раз вот формулами-то и несложно совсем
с 01.01.2022 по 31.12.2025
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ((СТРОКА($44562:$46022)>=ТРАНСП(Tabelle1[Старт дата]))*(СТРОКА($44562:$46022)<=ТРАНСП(Tabelle1[финиш]));СТРОКА($44562:$46022));СТРОКА(K1));"")
Любая дата (должно быть пошустрее, если в исходнике небольшой период
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ((СТРОКА(ИНДЕКС(A:A;МИН(Tabelle1[Старт дата])):ИНДЕКС(A:A;МАКС(Tabelle1[финиш])))>=ТРАНСП(Tabelle1[Старт дата]))*(СТРОКА(ИНДЕКС(A:A;МИН(Tabelle1[Старт дата])):ИНДЕКС(A:A;МАКС(Tabelle1[финиш])))<=ТРАНСП(Tabelle1[финиш]));СТРОКА(ИНДЕКС(A:A;МИН(Tabelle1[Старт дата])):ИНДЕКС(A:A;МАКС(Tabelle1[финиш]))));СТРОКА(K1));"")
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#13
14.09.2022 14:41:45
Так я пояснил сверху, что интервалов несколько будет)))
Изменено: Excelopfer - 14.09.2022 15:06:58
 
 
 
#14
14.09.2022 14:47:37
Excelopfer,
1. кнопка Цитировать не для ответа, а для АКЦЕНТИРОВАНИЯ ВНИМАНИЯ на определенной фразе из текста, а не всего полностью. для ответа есть кнопка "ИМЯ". лучше бы исправить свои предыдущие сообщения.(удалить бесполезные цитаты)
2. вы бы прежде чем отвечать посмотрели бы файл от _Boroda_, в #13 сообщении.
Изменено: Wild.Godlike - 14.09.2022 14:48:06
 
 
 
#15
14.09.2022 14:47:50
Excelopfer, не жмите на кнопку цитирования. Достаточно нажать "Имя".
Как убрать секунды оставлю Вам на откуп, т.к. сам не знаю как.
Функция

Запрос
Изменено: whateverlover - 14.09.2022 14:49:28
 
 
 
#16
14.09.2022 14:51:59
whateverlover, Воооооот!!! То, что надо. Спасибо огромное! Странно, правда, в выгрузке формат меняетс у времени на числовой
 
 
 
#17
14.09.2022 14:55:19
whateverlover, а можно еще чтобы номер был из стартовой таблички?
 
 
 
#18
14.09.2022 15:02:20
whateverlover, и ещё выдаёт ошибку, если только 1 интервал. Что не нашёл колонку Старт
 
 
 
#19
14.09.2022 15:03:53
Excelopfer,
Оставить столбец с номерами

Поправьте свои предыдущие сообщения, а то в следующий раз могут и не помочь.
Изменено: whateverlover - 14.09.2022 15:05:38
 
 
 
#20
14.09.2022 15:13:24
whateverlover, исправил. Что-то с одним интервалом всё равно не работает(((
 
 
 
#21
14.09.2022 15:16:56
Excelopfer, с самым нижним?
Готовьте всегда пример, т.к. он есть в реальных данных. В Вашем примере нижняя строка была с какими-то точками, я ее удалил.
Запрос поменяйте на:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    f = Table.AddColumn(Источник,"tmp",each f_dates(_)),
    cmb = Table.Combine(f[tmp])
in
    cmb

upd. а понял, что с одной не работает, а не с последней, но решение то же. Так и оставьте, как код в этом сообщении, вряд ли у Вас в реальном примере эта строка с точками будет.
Изменено: whateverlover - 14.09.2022 15:31:49
 
 
 
#22
14.09.2022 15:24:13
whateverlover, Теперь всё работает, спасибо большое. Только формат меняет на числовой, если новые данные. Странно. Нельзя это исправить? В редакторе PQ стоят правильные форматы, а выдаёт такое
4426710,5833333330,999988426
44268100,999988426
44269100,999988426
44270100,999988426
 
 
 
#23
14.09.2022 15:29:08
Excelopfer, не подскажу, т.к. у меня не меняет, выгружается все корректно (на основе данных из примера).
 
 
 
#24
14.09.2022 15:33:27
whateverlover, да, странно, конечно. Решил так: создал ссылку на запрос и там ещё раз задал формат. РАботает. Спасибо огромное. Это гениально. Про функции в PQ я и не знал ;D
 
 
 
#25
19.09.2022 13:20:44
whateverlover, подскажите ещё, пожалуйста, как можно так же, как и номер, вставить страну. Я попробовал по аналогии
Код
    СтолбецСтрана = 
        Table.AddColumn(ВТаблицу, "Reiseziel", each x[Reiseziel], type text),

но не работает. Мне ещё нужно вставить столбец страна, чтобы он так же повторялся, как и номер, а также столбцы с чекбоксами, которые тоже будут повторяться в выгрузке.
Изменено: Excelopfer - 19.09.2022 13:21:24
 
 
 
#26
19.09.2022 13:42:03
Excelopfer, не знаю, в примере не было страны(
покажите пример, где есть страна и чек-боксы, хотя вряд ли Power Query перенесет Вам чек боксы
Изменено: whateverlover - 19.09.2022 13:48:46
 
 
 
#27
19.09.2022 14:10:35
whateverlover, не было,  не спорю. Появились новые требования. Нужно добавить страну и чекбоксы ну или хотя бы JA/Nein. Приложил пример
 
 
 
#28
19.09.2022 14:17:26
whateverlover, но главное, конечно, страну. Странно, что не сработало по аналогии. Видимо, я не понял логику кода.  
 
 
 
#29
19.09.2022 14:30:48
Логика должна быть такая, что в каждом следующем шаге мы указываем предыдущий шаг.
Т.е. у СтолбецСтрана мы не указываем "Table.AddColumn(ВТаблицу", а заменяем первый аргумент на предыдущий шаг, коим теперь является СтолбецСНомером.
Код
... 
ВТаблицу =                                                                       
        Table.TransformColumnTypes(
                Table.RenameColumns(
                    Table.FromList(
                        Даты, 
                        Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
                {"Column1","Tag"}),
        {"Tag", type date}),       
    СтолбецСНомером = 
        Table.AddColumn(ВТаблицу, "№", each x[N], type number),
    СтолбецСтрана = 
        Table.AddColumn(СтолбецСНомером, "Reiseziel", each x[Reiseziel], type text),
    Старт = 
        Table.AddColumn(
            СтолбецСтрана, 
            "Startzeit", 
            each if [Tag] = Date.From(x[Startdatum]) 
                 then Time.From(x[Startzeit]) 
                 else #time(0,0,0), type time)
...

Но если уж очень много столбцов надо добавлять, можно просто в функции создать диапазон периодов и развернуть их, тогда сохранятся все столбцы и не нужно будет к каждому создавать Table.AddColumn. (в файле сделал, но тогда слетают форматы в развернутых столбцах и надо заново им задавать форматы)
Изменено: whateverlover - 19.09.2022 14:37:20
 
 
 
#30
19.09.2022 14:32:12
whateverlover, ааааа, понял, спасибо
 
 
 
Страницы: 1
Наверх