Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Подсчет часов переработки с учетом будних, пятниц и выходных - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет часов переработки с учетом будних, пятниц и выходных (Формулы/Formulas)
Подсчет часов переработки с учетом будних, пятниц и выходных
romanermulin Дата: Суббота, 28.10.2023, 10:34 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

16.66.1
Всем доброго времени суток!
Нужна ваша помощь, необходимо посчитать переработку именно с учетом будних дней (завершение рабочего дня в 17:00), пятниц (в 16:00) и выходных (все проведенное время в течение суток).
Считаем только после указанного времени, то, что работник приходит на работу раньше 8:00 не учитываем. Пример с комментариями приложил.
К сообщению приложен файл: 2865126.xlsx (9.8 Kb)


Роман
 
Ответить
СообщениеВсем доброго времени суток!
Нужна ваша помощь, необходимо посчитать переработку именно с учетом будних дней (завершение рабочего дня в 17:00), пятниц (в 16:00) и выходных (все проведенное время в течение суток).
Считаем только после указанного времени, то, что работник приходит на работу раньше 8:00 не учитываем. Пример с комментариями приложил.

Автор - romanermulin
Дата добавления - 28.10.2023 в 10:34
DrMini Дата: Суббота, 28.10.2023, 11:51 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1645
Репутация: 208 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Здравствуйте romanermulin,
Вот такой вариант посмотрите.
Только не понял в каком формате вам нужно вывести результат. В часах или как у меня в часах и минутах.
В принципе формат поменять не проблема.
К сообщению приложен файл: 9161190.xlsx (10.5 Kb)
 
Ответить
СообщениеЗдравствуйте romanermulin,
Вот такой вариант посмотрите.
Только не понял в каком формате вам нужно вывести результат. В часах или как у меня в часах и минутах.
В принципе формат поменять не проблема.

Автор - DrMini
Дата добавления - 28.10.2023 в 11:51
i691198 Дата: Суббота, 28.10.2023, 12:11 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 218
Репутация: 85 ±
Замечаний: 0% ±

DrMini, Добрый день. У вас ошибка в последней строке, это суббота и часы считаются по другому.
 
Ответить
СообщениеDrMini, Добрый день. У вас ошибка в последней строке, это суббота и часы считаются по другому.

Автор - i691198
Дата добавления - 28.10.2023 в 12:11
DrMini Дата: Суббота, 28.10.2023, 12:14 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1645
Репутация: 208 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
У вас ошибка в последней строке

Да точно. Условие TC прочитал невнимательно.
 
Ответить
Сообщение
У вас ошибка в последней строке

Да точно. Условие TC прочитал невнимательно.

Автор - DrMini
Дата добавления - 28.10.2023 в 12:14
DrMini Дата: Суббота, 28.10.2023, 12:33 | Сообщение № 5
Группа: Проверенные
Ранг: Старожил
Сообщений: 1645
Репутация: 208 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Получилось коряво.
Может, кто нибудь сделает более универсальную формулу.
У меня получается либо длиннющая, либо с доп столбцом, либо вот так из "кусков".
Не понятно, как будет это использоваться в дальнейшем. На неделю, месяц или на год.
К сообщению приложен файл: 0553812.xlsx (10.6 Kb)
 
Ответить
СообщениеПолучилось коряво.
Может, кто нибудь сделает более универсальную формулу.
У меня получается либо длиннющая, либо с доп столбцом, либо вот так из "кусков".
Не понятно, как будет это использоваться в дальнейшем. На неделю, месяц или на год.

Автор - DrMini
Дата добавления - 28.10.2023 в 12:33
romanermulin Дата: Суббота, 28.10.2023, 14:56 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

16.66.1
DrMini, по итогам месяца будет вбиваться переработка в часах и считаться уже потом сумма по каждому работнику.
А может для универсальности в одном столбце прочесывать весь период именно по будним дням, где будет считаться уход после 17 или 16 часов, а в дополнительном столбце этот же период прочесывать на предмет выходов в выходные дни? Пример приложил.
К сообщению приложен файл: 9826529.xlsx (11.7 Kb)


Роман
 
Ответить
СообщениеDrMini, по итогам месяца будет вбиваться переработка в часах и считаться уже потом сумма по каждому работнику.
А может для универсальности в одном столбце прочесывать весь период именно по будним дням, где будет считаться уход после 17 или 16 часов, а в дополнительном столбце этот же период прочесывать на предмет выходов в выходные дни? Пример приложил.

Автор - romanermulin
Дата добавления - 28.10.2023 в 14:56
romanermulin Дата: Суббота, 28.10.2023, 15:13 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

16.66.1
DrMini, или вообще, разделить весь период на две части, будние дни и выходные дни, и уже для каждого столбца считать переработку по своему. Вот в будние дни прям круто получилось, то, что нужно, хорошо считает, а вот с выходными пока неясно как быть.


Роман
 
Ответить
СообщениеDrMini, или вообще, разделить весь период на две части, будние дни и выходные дни, и уже для каждого столбца считать переработку по своему. Вот в будние дни прям круто получилось, то, что нужно, хорошо считает, а вот с выходными пока неясно как быть.

Автор - romanermulin
Дата добавления - 28.10.2023 в 15:13
AlienSphinx Дата: Суббота, 28.10.2023, 18:09 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 6 ±
Замечаний: 0% ±

power query
[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    f = (tbl as table) =>
        [wd = Date.DayOfWeek(Date.From(Table.FirstValue(tbl)), Day.Monday),
        t = #time(if wd > 4 then 0 else if wd = 4 then 16 else 17, 0, 0),
        recs = List.Buffer(Table.ToRecords(tbl)),
        tx = Duration.TotalHours(
            List.Sum(
                List.Transform(recs,
                    (x) => List.Max({t, Time.From(x[Выход])}) - List.Max({t, Time.From(x[Вход])})
                )
            )
        )][tx],
    group = Table.Group(
        Source, "Вход",
        {{"Результат", each Date.From([Вход]{0})},
        {"Переработка", each f(_)}},
        GroupKind.Local, (s, c) => Number.From(Date.From(c) <> Date.From(s))
    )[[Результат], [Переработка]]
in
    group
[/vba]
ну и 365 заодно
[vba]
Код
=LET(
    in, Table1[Вход],
    in_time, in - INT(in),
    out, Table1[Выход],
    out_time, out - INT(out),
    days, UNIQUE(INT(in)),
    wd, WEEKDAY(days),
    wt, SWITCH(wd, 6, 16, 7, 0, 1, 0, 17) / 24,
    t, MAP(
        days,
        wt,
        LAMBDA(x, y,
            SUM(
                MAP(FILTER(out_time, INT(out) = x), LAMBDA(w, MAX(w, y))) -
                    MAP(FILTER(in_time, INT(in) = x), LAMBDA(w, MAX(w, y)))
            )
        )
    ),
    HSTACK(days, t * 24)
)
[/vba]
К сообщению приложен файл: 2653928.xlsx (24.0 Kb)


Сообщение отредактировал AlienSphinx - Суббота, 28.10.2023, 19:27
 
Ответить
Сообщениеpower query
[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    f = (tbl as table) =>
        [wd = Date.DayOfWeek(Date.From(Table.FirstValue(tbl)), Day.Monday),
        t = #time(if wd > 4 then 0 else if wd = 4 then 16 else 17, 0, 0),
        recs = List.Buffer(Table.ToRecords(tbl)),
        tx = Duration.TotalHours(
            List.Sum(
                List.Transform(recs,
                    (x) => List.Max({t, Time.From(x[Выход])}) - List.Max({t, Time.From(x[Вход])})
                )
            )
        )][tx],
    group = Table.Group(
        Source, "Вход",
        {{"Результат", each Date.From([Вход]{0})},
        {"Переработка", each f(_)}},
        GroupKind.Local, (s, c) => Number.From(Date.From(c) <> Date.From(s))
    )[[Результат], [Переработка]]
in
    group
[/vba]
ну и 365 заодно
[vba]
Код
=LET(
    in, Table1[Вход],
    in_time, in - INT(in),
    out, Table1[Выход],
    out_time, out - INT(out),
    days, UNIQUE(INT(in)),
    wd, WEEKDAY(days),
    wt, SWITCH(wd, 6, 16, 7, 0, 1, 0, 17) / 24,
    t, MAP(
        days,
        wt,
        LAMBDA(x, y,
            SUM(
                MAP(FILTER(out_time, INT(out) = x), LAMBDA(w, MAX(w, y))) -
                    MAP(FILTER(in_time, INT(in) = x), LAMBDA(w, MAX(w, y)))
            )
        )
    ),
    HSTACK(days, t * 24)
)
[/vba]

Автор - AlienSphinx
Дата добавления - 28.10.2023 в 18:09
romanermulin Дата: Суббота, 28.10.2023, 19:31 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

16.66.1
AlienSphinx, похоже это то, что нужно, но я не умею работать с элементами программирования, даже не знаю, как это использовать))


Роман
 
Ответить
СообщениеAlienSphinx, похоже это то, что нужно, но я не умею работать с элементами программирования, даже не знаю, как это использовать))

Автор - romanermulin
Дата добавления - 28.10.2023 в 19:31
i691198 Дата: Суббота, 28.10.2023, 19:46 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 218
Репутация: 85 ±
Замечаний: 0% ±

romanermulin, Посмотрите такой вариант.
К сообщению приложен файл: tbl1.xlsx (21.8 Kb)
 
Ответить
Сообщениеromanermulin, Посмотрите такой вариант.

Автор - i691198
Дата добавления - 28.10.2023 в 19:46
AlienSphinx Дата: Суббота, 28.10.2023, 19:49 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 6 ±
Замечаний: 0% ±

romanermulin, если у вас 365, то это просто формула. Делаете из исходных данных таблицу, именуете ее Table1 и копируете формулу в самую левую верхнюю ячейку той области, где вы хотите разместить результат. Главное - чтобы в этой вашей таблице были колонки Вход и Выход. Это готовое решение. Собственно, PQ - тоже готовое решение, которое будет работать на основе исходной таблицы с теми же требованиями к ней.
 
Ответить
Сообщениеromanermulin, если у вас 365, то это просто формула. Делаете из исходных данных таблицу, именуете ее Table1 и копируете формулу в самую левую верхнюю ячейку той области, где вы хотите разместить результат. Главное - чтобы в этой вашей таблице были колонки Вход и Выход. Это готовое решение. Собственно, PQ - тоже готовое решение, которое будет работать на основе исходной таблицы с теми же требованиями к ней.

Автор - AlienSphinx
Дата добавления - 28.10.2023 в 19:49
romanermulin Дата: Суббота, 28.10.2023, 23:12 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

16.66.1
i691198, я добавил еще дат, и дальнейший подсчет стал некорректным, приложил файл.
К сообщению приложен файл: primer_2.xlsx (23.1 Kb)


Роман

Сообщение отредактировал romanermulin - Суббота, 28.10.2023, 23:15
 
Ответить
Сообщениеi691198, я добавил еще дат, и дальнейший подсчет стал некорректным, приложил файл.

Автор - romanermulin
Дата добавления - 28.10.2023 в 23:12
i691198 Дата: Воскресенье, 29.10.2023, 10:01 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 218
Репутация: 85 ±
Замечаний: 0% ±

romanermulin, Так структура вашего файла значительно отличается от первого. В первом каждой дате соответствовали две строки и не было пропусков дат. Я на это и ориентировался.
 
Ответить
Сообщениеromanermulin, Так структура вашего файла значительно отличается от первого. В первом каждой дате соответствовали две строки и не было пропусков дат. Я на это и ориентировался.

Автор - i691198
Дата добавления - 29.10.2023 в 10:01
romanermulin Дата: Воскресенье, 29.10.2023, 11:14 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

16.66.1
Собрал в кучу все варианты и вроде получил, что хотел.
Подскажите пожалуйста, вот есть у меня два столбца с датами и часами, но между ними есть нулевые и отрицательные значения, можно ли как-то собрать в отдельном месте только ненулевые даты и значения напротив них, как в примере?
К сообщению приложен файл: 4911180.xlsx (9.9 Kb)


Роман
 
Ответить
СообщениеСобрал в кучу все варианты и вроде получил, что хотел.
Подскажите пожалуйста, вот есть у меня два столбца с датами и часами, но между ними есть нулевые и отрицательные значения, можно ли как-то собрать в отдельном месте только ненулевые даты и значения напротив них, как в примере?

Автор - romanermulin
Дата добавления - 29.10.2023 в 11:14
DrMini Дата: Воскресенье, 29.10.2023, 12:15 | Сообщение № 15
Группа: Проверенные
Ранг: Старожил
Сообщений: 1645
Репутация: 208 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
как в примере

Так?
Код
=ИНДЕКС($B$1:$B$37;ПОИСКПОЗ(ДЕНЬ(D1);ДЕНЬ($A$1:$A$37);0))
К сообщению приложен файл: 9130295.xlsx (11.1 Kb)
 
Ответить
Сообщение
как в примере

Так?
Код
=ИНДЕКС($B$1:$B$37;ПОИСКПОЗ(ДЕНЬ(D1);ДЕНЬ($A$1:$A$37);0))

Автор - DrMini
Дата добавления - 29.10.2023 в 12:15
romanermulin Дата: Воскресенье, 29.10.2023, 13:28 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

16.66.1
DrMini, а можно, чтобы еще и даты собирались не нулевые? Грубо говоря, зеленые два столбца должны автоматически собираться из столбцов А и B


Роман

Сообщение отредактировал romanermulin - Воскресенье, 29.10.2023, 13:29
 
Ответить
СообщениеDrMini, а можно, чтобы еще и даты собирались не нулевые? Грубо говоря, зеленые два столбца должны автоматически собираться из столбцов А и B

Автор - romanermulin
Дата добавления - 29.10.2023 в 13:28
DrMini Дата: Воскресенье, 29.10.2023, 13:56 | Сообщение № 17
Группа: Проверенные
Ранг: Старожил
Сообщений: 1645
Репутация: 208 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
зеленые два столбца должны автоматически собираться из столбцов А и B

Код
=ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$37;НАИМЕНЬШИЙ(ЕСЛИ($B$1:$B$37>=0;СТРОКА($A$1:$A$37));СТРОКА()));"")

так подойдёт?
так будет вернее:
Код
=ЦЕЛОЕ(ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$37;НАИМЕНЬШИЙ(ЕСЛИ($B$1:$B$37>=0;СТРОКА($A$1:$A$37));СТРОКА()));""))
К сообщению приложен файл: 4021756.xlsx (11.3 Kb)


Сообщение отредактировал DrMini - Воскресенье, 29.10.2023, 14:02
 
Ответить
Сообщение
зеленые два столбца должны автоматически собираться из столбцов А и B

Код
=ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$37;НАИМЕНЬШИЙ(ЕСЛИ($B$1:$B$37>=0;СТРОКА($A$1:$A$37));СТРОКА()));"")

так подойдёт?
так будет вернее:
Код
=ЦЕЛОЕ(ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$37;НАИМЕНЬШИЙ(ЕСЛИ($B$1:$B$37>=0;СТРОКА($A$1:$A$37));СТРОКА()));""))

Автор - DrMini
Дата добавления - 29.10.2023 в 13:56
romanermulin Дата: Воскресенье, 29.10.2023, 14:20 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

16.66.1
DrMini, да, прекрасно! Большое спасибо!!!
Спасибо всем, кто помогал, очень признателен, вопрос решен!)


Роман
 
Ответить
СообщениеDrMini, да, прекрасно! Большое спасибо!!!
Спасибо всем, кто помогал, очень признателен, вопрос решен!)

Автор - romanermulin
Дата добавления - 29.10.2023 в 14:20
i691198 Дата: Воскресенье, 29.10.2023, 16:07 | Сообщение № 19
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 218
Репутация: 85 ±
Замечаний: 0% ±

romanermulin, Посмотрите более универсальный вариант с макросом. Нажмите на кнопку.
К сообщению приложен файл: primer_4.xlsm (26.7 Kb)
 
Ответить
Сообщениеromanermulin, Посмотрите более универсальный вариант с макросом. Нажмите на кнопку.

Автор - i691198
Дата добавления - 29.10.2023 в 16:07
romanermulin Дата: Воскресенье, 29.10.2023, 16:20 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

16.66.1
i691198, отличное решение, спасибо!


Роман
 
Ответить
Сообщениеi691198, отличное решение, спасибо!

Автор - romanermulin
Дата добавления - 29.10.2023 в 16:20
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет часов переработки с учетом будних, пятниц и выходных (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!