У меня есть сложная формула в Excel, и я не знаю, как ее разобрать. Можете ли вы помочь мне исправить приведенную ниже формулу?

Вот временной диапазон:

Start Time        End Time
Row L6 9:00:00       Row M6  09:57:15
Row L7 9:57:15       Row M7  10:21:07
Row L8 10:21:07      Row M8  14:48:19
Row L9 14:48:19      Row M9  19:00:00
Row L10 9:00:00      Row M10 13:27:12
Row L11 13:27:12     Row M11 17:37:02
Row L12 17:37:02     Row M12 19:00:00
Row L13 9:00:00      Row M13 09:34:19

Вот решение, которое я хочу:

          START DATE WİTH TİME         END DATE WİTH TİME
Row N6    20.06.2024   9:00:00   Row O6    20.06.2024  09:57:15
Row N7    20.06.2024   9:57:15   Row O7    20.06.2024  10:21:07
Row N8    20.06.2024  10:21:07   Row O8    20.06.2024  14:48:19
Row N9    20.06.2024  14:48:19   Row O9    20.06.2024  19:00:00
Row N10   21.06.2024   9:00:00   Row O10   21.06.2024  13:27:12 
Row N11   21.06.2024  13:27:12   Row O11   21.06.2024  17:37:02
Row N12   21.06.2024  17:37:02   Row O12   21.06.2024  19:00:00 
Row N13   24.06.2024   9:00:00   Row O13   24.06.2024  09:34:19

Дата начала находится в ячейке H5 и содержит только дату и время.
Если в диапазоне «Время окончания» время равно 19:00:00, вам следует увеличить день на 1 день для следующего дня строки и начать с 09:00:00, как указано во времени начала. Суббота и воскресенье – выходные. Если обе даты выпадают на выходные, вам следует пропустить 2 дня и начинать всегда с понедельника.
Диапазон поиска выходных:

Row T2:AR2  for the weekday name 
Row T3:AR3  for the weekday date

Выходные называются «HS», а выходные — «TT».

Я попробовал эту формулу для даты начала со временем.

=IF(ROW()-ROW($H$5)<=4;TEXT($H$5;"gg.aa.yyyy")&" "&TEXT(INDIRECT("M"&ROW());"ss:dd:nn");
IF(AND(ROW()-ROW($H$5)>=5;ROW()-ROW($H$5)<=8);TEXT(WORKDAY.INTL($H$5;0;"1111100";$T$3:$AR$3)+IF(ROW()-ROW($H$5)=5;1;IF(ROW()-ROW($H$5)=8;4;0));"gg.aa.yyyy")&" "&TEXT(INDIRECT("M"&ROW()-ROW($H$5)+5);"ss:dd:nn");
TEXT(WORKDAY.INTL($H$5;0;"1111100";$T$3:$AR$3)+4;"gg.aa.yyyy")&" "&TEXT($M$20;"ss:dd:nn")))

Все работает по очереди, но когда дело доходит до этого момента:

21.06.2024 13:27:12
20.06.2024 17:37:02
20.06.2024 19:00:00
24.06.2024 09:34:19

как видите, даты не увеличиваются в соответствии с датой 21.06.2024.
Мой вопрос заключается в том, как я могу увеличить количество дней в соответствии с предыдущей датой.

Вам нужно отредактировать это, два блока текста трудно читать и есть шанс ответить на вопрос.

Solar Mike 24.06.2024 14:10

Я уже отредактировал. Теперь вы можете просмотреть детали моего вопроса. Заранее спасибо за вашу помощь.

Yücel Emir 24.06.2024 14:15
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
0
2
76
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Теперь вы написали себе красивую непонятную формулу :-)

Чтобы понять, что она делает (и, возможно, решить вашу проблему), вы можете попытаться шаг за шагом оценить формулу, как показано в следующем примере:

Ответ принят как подходящий

Интересно, сработает ли здесь СКАН:

  1. incr_day_step1 - получить приращение в зависимости от установленного времени.
  2. incr_day — значения сдвига для применения к следующей строке.

Поскольку вы используете значение по умолчанию (суббота и воскресенье), мы могли бы опустить это значение; но, пожалуйста, добавьте параметр праздника (я его пропустил, например date_, TEXT(WORKDAY.INTL(start_date, incr_day,,holidays), "dd.mm.yyyy"))

Кроме того, примените строку формата и разделитель параметров (,) к своим региональным настройкам.

Введите следующую формулу в N6:

=LAMBDA(start_time, end_time, start_date,
    LET(
        incr_day_step1, SCAN(
            0,
            end_time,
            LAMBDA(acc, cur, IF(cur = TIMEVALUE("19:00:00"), acc + 1, acc))
        ),
        incr_day, VSTACK(0, DROP(incr_day_step1, -1)),
        date_, TEXT(WORKDAY.INTL(start_date, incr_day), "dd.mm.yyyy"),
        start_and_end_times_with_date, HSTACK(
            date_ & " " & TEXT(start_time, "hh:mm:ss"),
            date_ & " " & TEXT(end_time, "hh:mm:ss")
        ),
        start_and_end_times_with_date
    )
)(L6:L13, M6:M13, $H$5)

Большое спасибо за ваши усилия. Проблема решена идеально. Поэтому мне нужно очень хорошо изучить функцию Lambda.

Yücel Emir 26.06.2024 17:52

Другие вопросы по теме