Подсчёт часов в графике сменности

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Подсчёт часов в графике сменности (Формулы/Formulas)

DrMini

Дата: Четверг, 30.11.2023, 15:22 | Сообщение № 1

Группа: Друзья

Ранг: Старожил

Сообщений: 1760

Репутация: 244 ±

Замечаний: 0% ±

Excel LTSC 2024 RUS

Здравствуйте форумчане и гости форума.
Смена начинается в 7:00 и длится 24 часа. Сутки через трое.
Если человек работал в последний день месяца то ему считается 17 часов, а оставшиеся 7 часов переходят на следующий месяц.
Сделал подсчёт часов в месяце по сменам.
Получился вот такой монстр.

Код

=ЕСЛИ(СЧЁТЕСЛИ(A3:AE3;"Я")=8;СЧЁТЕСЛИ(A3:AE3;"Я")*24-ЕСЛИМН(И(ПОИСКПОЗ("я";A3:AE3;0)=4;ПОИСКПОЗX("я";A3:AE3;0;-1)=(ДЕНЬ(КОНМЕСЯЦА($A$2;0))));0;ПОИСКПОЗ("я";A3:AE3;0)=4;17;ПОИСКПОЗX("я";A3:AE3;0;-1)=(ДЕНЬ(КОНМЕСЯЦА($A$2;0)));17;1;0);СЧЁТЕСЛИ(A3:AE3;"Я")*24+ЕСЛИМН(И(ПОИСКПОЗ("я";A3:AE3;0)=4;ПОИСКПОЗX("я";A3:AE3;0;-1)=(ДЕНЬ(КОНМЕСЯЦА($A$2;0))));0;ПОИСКПОЗ("я";A3:AE3;0)=4;17;ПОИСКПОЗX("я";A3:AE3;0;-1)=(ДЕНЬ(КОНМЕСЯЦА($A$2;0)));17;1;0))

Хотелось бы уменьшить эту формулу и по возможности избавиться от функции

Код

ПОИСКПОЗX

так, как стоит Excel 2016, а эта функция вроде появилась только в excel 2021.
Дополнительный расчёт

Код

ЕСЛИМН(И(ПОИСКПОЗ("я";A3:AE3;0)=4;ПОИСКПОЗX("я";A3:AE3;0;-1)=(ДЕНЬ(КОНМЕСЯЦА($A$2;0))))

нужен для февраля в не високосный год.

К сообщению приложен файл: podschjot_chasov_za_mesjac.xlsx (15.6 Kb)

Ответить

Gustav

Дата: Четверг, 30.11.2023, 16:44 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2766

Репутация: 1140 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Я, может, чего не понял, но у меня вот такая простейшая формула получилась:

Код

=СЧЁТЕСЛИ(A3:AD3;"Я")*24-(AD3="Я")*7+(D3="Я")*7

Если надо, чтобы AD3 и D3 как-то через позиции внутри месяца (последний и четвертый день) рассчитывались, то скажите на что можно опереться. На дату начала месяца в A2? Она будет всегда в этой ячейке? Или в шаблоне месяца всегда будет 31 день зарезервирован, а по факту использоваться только дни месяца в нужном количестве (30,31,28,29), так?

И насчёт 4-го дня в месяце не очень понятно. Почему такая "дискриминация" этого дня? Человек сидит 24 часа, а в зачёт только 7 идёт?!
P.S. Дошло!! Нужно ДОБАВИТЬ 7 часов за предыдущий месяц, т.е. за смену, приходящуюся на последний день предыдущего месяца. Исправил формулы.

P.P.S. Сделал "позиционные ссылки" - формула, конечно, усложнилась, но не смертельно:

Код

=СЧЁТЕСЛИ(A3:AE3;"Я")*24 - (ИНДЕКС(A3:AE3;ДЕНЬ(КОНМЕСЯЦА($A$2;0)))="Я")*7 + (ИНДЕКС(A3:AE3;4)="Я")*7

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Четверг, 30.11.2023, 17:23

Ответить

AlexM

Дата: Четверг, 30.11.2023, 17:50 | Сообщение № 3

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1129 ±

Замечаний: 0% ±

Excel 2003

Еще вариант

Код

=СЧЁТЕСЛИ(A3:AD3;"я")*24+(D3="я")*7-(ПРОСМОТР(9^9;A$2:AE$2;A3:AE3)="я")*7

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Четверг, 30.11.2023, 17:51

Ответить

DrMini

Дата: Четверг, 30.11.2023, 19:52 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 1760

Репутация: 244 ±

Замечаний: 0% ±

Excel LTSC 2024 RUS

Цитата Gustav, 30.11.2023 в 16:44, в сообщении № 2 (

)

формула, конечно, усложнилась

Константин. После такого "усложнения" поглядел на свою и...

Второй вариант работает отлично.

Цитата AlexM, 30.11.2023 в 17:50, в сообщении № 3 (

)

Еще вариант

Алексей. Ваш вариант тоже работает, но ошибается 1 раз - когда 31 день в месяце. А если есть значения после последнего дня месяца (это когда дней в месяце менее 31) тоже бывает и "приврёт". Это мой косяк. "Вылечил" при помощи:

Код

=ЕСЛИ(A2="";"";ЕСЛИ(A2+1>КОНМЕСЯЦА($A$2;0);"";A2+1))

[p.s.]Сделал матрицу для проверки. Красным выделил где данные не совпадают.[/p.s.]
[p.s.]Оказалось, что я сделал изначально почти всё не так.[/p.s.]