Форум программистов, компьютерный форум, киберфорум
Наши страницы
MS Office Excel
Войти
Регистрация
Восстановить пароль
 
 
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 25
#1

Как посчитать время работы ночью с умножением на коэффициент - MS Excel

09.08.2017, 17:10. Просмотров 892. Ответов 23
Метки нет (Все метки)

Всем привет. ПОМОГИТЕ ПОЖАЛУЙСТА!

http://www.cyberforum.ru/asp-net/thread481974.html
Есть данные во вложении. Суть такова - сотрудник, работающий после 18:00 получает за выход на физ. лицо 600 рублей дополнительно к сумме за работы и 1000 рублей за выход на юр. лицо дополнительно к сумме за работы. Кроме того, если сотрудник работает в этот день еще и ночью (не важно, переходит ли наряд из вечера в ночь или же новый наряд, выполняемый уже ночью), ему начисляется ночной коэффициент с 00:00 до 03:00 - 1,7; с 03:00 до 06:00 - 2,5. То есть - человек работал например с 00:00 до 03:00 и у него, допустим сумма наряда составила в минутах 300 минут (данные берутся из спецификации по видам работ и стоимости в минутах на каждый вид работ). Мы 300*на 1,7 (так как за это время коэффицент 1,7) =510 минут, потом отнимаем саму сумму наряда (потому что сама сумма ему уже легла в оплату и нам нужно вычислить лишь сумму, полученную от коэффициента) 510-300=210 минут, и умножаем полученную сумму на 5,49 (это стоимость 1 минуты) 210*5,49=1152,9 рубля. Если он работал и в первую половину ночи, и во вторую, то нужно вычислить сколько времени приходится на первую и на вторую половину ночи, далее каждую часть прогнать по вышеописанной формуле. Допустим, он отработал с 01:00 до 05:00 и сумма наряда например равно 500, то мы делим сумму наряда на количество часов, в данном случае это 4 часа 500/4=125 минут и далее умножаем на количество часов в каждой части ночи 125*2=250 минут в обеих половинах ночи. Затем каждую часть прогоняем по формуле выше с той лишь разницей, что коэффициенты будут в первом случае 1,7, а во втором 2,5. Если же сотрудник приступил к работам вечером и затем перешел в ночь, то затраченное время до 00:00 часов не умножается на коэффициент, а лишь отнимается из суммы минут. Например, он начал работать в 22:00, а закончил в 02:00 и сумма минут составила 200 минут, получаем 200/4 (так как отработал 4 часа) = 50 и умножаем 50 на 2 и на ночной коэффициент 1,7 (так как работал в первой половине ночи), с вычетом суммы минут за эти 2 часа - 100*1,7=170, 170-100=70. Время работы в вечернее время не умножаем и соответственно не учитываем, так как ему это время было оплачено ранее автоматом и в данной таблице нас оно не интересует. Выход в один день хоть на один объект, хоть на несколько оплачивается один раз.

Теперь вопрос - как все это подвергнуть автоматическому алгоритму? Всю голову сломал. Люди, кто шарит в Экселе, помогите!!!
0
Вложения
Тип файла: xlsx Образец.xlsx (10.7 Кб, 6 просмотров)
Лучшие ответы (1)
Надоела реклама? Зарегистрируйтесь и она исчезнет полностью.
Similar
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
09.08.2017, 17:10
Я подобрал для вас темы с готовыми решениями и ответами на вопрос Как посчитать время работы ночью с умножением на коэффициент (MS Excel):

Посчитать время работы сортировки
Здравствуйте,возникла задача посчитать время работы сортировки...но в...

Как посчитать коэффициент детерминации функции с двумя параметрами?
Есть функция от двух переменных q = f(x,y) Нужно посчитать коэффициент...

Как вывести время работы программы ПОСЛЕ завершения работы progressBar?
У меня почему-то сначала выводится время в MessageBox, а затем уже завершает...

Как посчитать время выполнения скрипта?
Нужно посчитать время выполнения скрипта и его частей. Структура такая:...

Как посчитать время вывода данных?
В общем, дали такое задание: Выяснить в сколько раз вывод данных при помощи...

23
AleksSid
581 / 481 / 227
Регистрация: 05.01.2014
Сообщений: 1,043
09.08.2017, 21:05 #2
Вариант. Что то намудрили с объяснениями, формула сделана по принципу
Цитата Сообщение от DanGo5 Посмотреть сообщение
с 00:00 до 03:00 - 1,7; с 03:00 до 06:00 - 2,5.
минус отработанное время, за каждый период.
Код
=ЕСЛИ(ОСТАТ(H4;1)>0,125;(ТЕКСТ(H4;"[м]")-180)*2,5-(ТЕКСТ(H4;"[м]")-180)+(180*1,7-180);ТЕКСТ(H4;"[м]")*1,7-ТЕКСТ(H4;"[м]"))
Столбец <Итого часов> не обязателен, он в расчетах не участвует.
0
Вложения
Тип файла: xlsx Образец_1.xlsx (11.6 Кб, 8 просмотров)
AleksSid
581 / 481 / 227
Регистрация: 05.01.2014
Сообщений: 1,043
09.08.2017, 21:30 #3
Немного сократил формулу.
Код
=ЕСЛИ(ОСТАТ(H4;1)>0,125;(ТЕКСТ(H4;"[м]")-180)*2,5-(ТЕКСТ(H4;"[м]")-180)+180*0,7;ТЕКСТ(H4;"[м]")*0,7)
0
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 25
10.08.2017, 03:08  [ТС] #4
Доброго времени суток! Спасибо за потраченное время, но формула считает неверно. В столбце "Сумма (ч/м), на одного" данные не должны быть завязаны за столбец "конец работ". Они проставляются исходя из другой таблицы и в этой таблице проставляются автоматически. Но в рамках теста их можно ставить рандомно. Например, если взять последние две строки, где работал Мелконов и Козубов, то у каждого конечный расчет должен быть таков: Мы имеем сумму минут на наряд на каждого =126 минут и работали они с 00:00 до 03:00 (коэффициент 1,7). Считаем 126*1,7=214,2; Вычитаем из этого результата сумму минут 214,2-126=88,2 - это и есть чистая сумма минут за коэффициент 1,7 без первоначальной суммы минут 126. Далее умножаем полученную сумму на 5,49 (стоимость 1 минуты в рублях) 88,2*5,49=484,22 рубля в рублях за чистый коэффициент 1,7 в последнем столбце.

Сейчас как она считает я не понял, но итог таков, что цифры из предпоследнего столбца умножаются на 5,49, но ответ неверный в итоге.

Где есть работа в разные половины ночи - тоже неверный итог, и где с вечера переход в ночь, соответственно тоже неверный расчёт.

Добавлено через 23 минуты
И собственно мне нужно, чтобы автоматически шло распознавание работы с 00:00 до 03:00 и шел счет по коэффициенту 1,7 с вычетом основной суммы минут, с 03:00 до 06:00 по коэффициенту 2,5, а если с вечера перетекает в ночь, то всё, что было до 00:00 часов, не подвергается никаким коэффициентам и сумма минут за вечернее время вычитается из итоговой.

Я понимаю, что трудно понять, что мне нужно в итоге, но я готов поэтапно вести переговоры))).

Для первого этапа хватило бы расчетов исключительно по чистым работам в рамках 00:00 до 03:00 и с 03:00 до 06:00 с коэффициентами 1,7 и 2,5. Дальше - больше...
0
AleksSid
581 / 481 / 227
Регистрация: 05.01.2014
Сообщений: 1,043
10.08.2017, 10:52 #5
Цитата Сообщение от DanGo5 Посмотреть сообщение
Мы имеем сумму минут на наряд на каждого =126 минут и работали они с 00:00 до 03:00 (коэффициент 1,7). Считаем 126*1,7=214,2;
Откуда взялись 126 минут, с 00:00 до 03:00 это 3 часа=180 мин. х 1,7=306 мин.-180 мин.=126 мин.
Если вы берете минуты отсюда
Цитата Сообщение от DanGo5 Посмотреть сообщение
(данные берутся из спецификации по видам работ и стоимости в минутах на каждый вид работ)
то в вашем файле нет этой спецификации, нет и времени в минутах на работы согласно спецификации, сделайте доп столбец, время на работу в минутах.
0
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 25
10.08.2017, 14:21  [ТС] #6
Уважаемый AleksSid, 126 минут я взял из "Сумма (ч/м), на одного".

Откуда взялись 126 минут, с 00:00 до 03:00 это 3 часа=180 мин. х 1,7=306 мин.-180 мин.=126 мин.
Если вы берете минуты отсюда:

Тут изначально пошло неправильное понимание расчетов: Время с 00:00 до 03:00 это сигнал к тому, что у сотрудника в эти часы действует ночной коэффициент 1,7, но умножать нужно не сами эти часы (3 часа) на коэффициент 1,7, а "Сумма (ч/м), на одного", и при этом мы получаем 214,2 человекоминут на одного человека, затем мы отнимаем из этой суммы 126 человекоминут, так как эта сумма была оплачена ранее данному сотруднику =, а нам нужно лишь посчитать сумму коэффициента 1,7 и умножить на 5,49 (стоимость 1 минуты):

126*1,7=214,2; 214,2-126=88,2; 88,2*5,49=484,21 рублей.

Прикрепил файл, в нем ниже ваших расчетов моя таблица, как мог с примечаниями и примитивной формулой попытался раскрыть суть.
0
Вложения
Тип файла: xlsx Образец_1.xlsx (14.3 Кб, 1 просмотров)
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 25
10.08.2017, 14:32  [ТС] #7
Есть наряд на работы, в котором он напротив граф с видами работ проставляет минуты, потраченные на эти виды работ. В конце получается n-ая сумма мнут на этот наряд. Выполнял он его например с 00: до 03:00 ночи. эту n-ую сумму минут умноженную на 5,49 он получил уже и она же ложится в графу "Сумма (ч/м), на одного", но в рамках данного эксперимента можно поставить лбую сумму. Но в эти часы действовал еще и повышающий коэффициент 1,7, нужно вычислить сумму в рублях этого коэффициента.

Я не знаю как ещё объяснить))))
0
AleksSid
581 / 481 / 227
Регистрация: 05.01.2014
Сообщений: 1,043
10.08.2017, 19:57 #8
Лучший ответ Сообщение было отмечено DanGo5 как решение

Решение

В общем как понял, время отработанное до 24:00, вычитается.
Код
=(ЕСЛИ(ОСТАТ(H4;1)>0,125;(L4-(L4/ТЕКСТ(I4;"ч"))*ТЕКСТ(I4-H4;"ч"))/ТЕКСТ(H4;"ч")*3*0,7+(L4-(L4/ТЕКСТ(I4;"ч"))*ТЕКСТ(I4-H4;"ч"))/ТЕКСТ(H4;"ч")*(ТЕКСТ(H4;"ч")-3)*1,5;(L4-(L4/ТЕКСТ(I4;"ч"))*ТЕКСТ(I4-H4;"ч"))*0,7))*5,49
1
Вложения
Тип файла: xlsx Образец_1 (1).xlsx (14.2 Кб, 2 просмотров)
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 25
11.08.2017, 04:21  [ТС] #9
Спасибо большое... Урааааааааааааааааааааааа!!!!!!! На первый тест вроде бы все работает правильно. Даже не нужно высчитывать теперь сколько минут они потратили до 00:00 часов...

Если в дальнейшем что-то замечу в неправильной работе, то я могу к тебе тут обратиться за помощью?

И еще вопрос - не мог бы ты мне объяснить работу формулы, чтобы я сам во-первых понимал ее алгоритм, и во-вторых, мог бы подправить что-то на будущее...

Уж очень она сложна в написании...
0
AleksSid
581 / 481 / 227
Регистрация: 05.01.2014
Сообщений: 1,043
11.08.2017, 09:28 #10
Так будет лучше.
Код
=((L4-(L4/ТЕКСТ(I4;"ч"))*ТЕКСТ(I4-H4;"ч"))/ТЕКСТ(H4;"ч")*3*0,7+(L4-(L4/ТЕКСТ(I4;"ч"))*ТЕКСТ(I4-H4;"ч"))/ТЕКСТ(H4;"ч")*(ТЕКСТ(H4;"ч")-3)*1,5)*5,49
Цитата Сообщение от DanGo5 Посмотреть сообщение
объяснить работу формулы
Объяснять в принципе и нечего. Функция ТЕКСТ преобразует время в число, 3:00 в 3, 5:00 в 5 и т.д., а дальше чисто арифметические действия. Умножаем на 0,7 и 1,5 чтобы не делать лишнее вычитание минут, убираем из коэф. по единице.
0
Вложения
Тип файла: xlsx Образец_1 (2).xlsx (14.3 Кб, 1 просмотров)
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 25
11.08.2017, 09:34  [ТС] #11
Нашел ошибку - не работает формула, если час не целый. Ввожу с 01:20 до 03:40 - в конечном столбце ошибка.

Как-то можно это исправить?

Добавлено через 1 минуту
Умножаем на 0,7 и 1,5 чтобы не делать лишнее вычитание минут - А это откуда взялось?
0
AlexM
1726 / 1310 / 526
Регистрация: 04.09.2015
Сообщений: 2,580
11.08.2017, 09:45 #12
Цитата Сообщение от DanGo5 Посмотреть сообщение
А это откуда взялось?
Из математики.
Х * 1,7 - Х = Х * (1,7 - 1) = Х * 0,7
0
AleksSid
581 / 481 / 227
Регистрация: 05.01.2014
Сообщений: 1,043
11.08.2017, 10:38 #13
Цитата Сообщение от DanGo5 Посмотреть сообщение
если час не целый
Надо было сразу это отметить. Тогда меняем формулу.
Код
=((L4-(L4/(ОСТАТ(I4;1)*24))*ОСТАТ(I4-H4;1)*24)/(ОСТАТ(H4;1)*24)*3*0,7+(L4-(L4/(ОСТАТ(I4;1)*24))*(ОСТАТ(I4-H4;1)*24))/(ОСТАТ(H4;1)*24)*((ОСТАТ(H4;1)*24)-3)*1,5)*5,49
Принцип работы тот же.
0
Вложения
Тип файла: xlsx Образец_1 (2).xlsx (14.3 Кб, 5 просмотров)
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 25
11.08.2017, 11:39  [ТС] #14
А вот тут не поможете - Нашел ошибку - не работает формула, если час не целый. Ввожу с 01:20 до 03:40 - в конечном столбце ошибка.

Добавлено через 41 секунду
Не обновил страницу)))
0
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 25
11.08.2017, 11:49  [ТС] #15
Теперь есть еще один нюанс)))

Есть два вида работников - Монтажники СТР и инженеры ВОЛС, у которых ставка 5,49 рублей за минуту, а есть Техники, у которых ставка 4,05 рублей за минуту.

Можно в эту формулу как-то подстроить, чтобы он по столбцу "Должность" делал отбор и если встречается Техники - то все действия производились с 4,05 руб за мин, а если Монтажники СТР или инженеры ВОЛС, то по 5,49 руб за минуту?

Выкладываю фрагмент, чтобы было понимание о чем я говорю.
0
Вложения
Тип файла: xlsx Монтажники и техники.xlsx (12.2 Кб, 2 просмотров)
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 25
12.08.2017, 17:16  [ТС] #16
В последнем варианте если в столбце "Начало работ" ввожу час не целый, например, 01:30 - итоговый результат с минусом.

Добавлено через 3 часа 43 минуты
И есть еще пара нюансов по работе техников. Они на окладе с 9 утра до 6 вечера. И сумма минут из нарядов, которые закрывают в течении рабочего дня, в эту таблицу не входит. Сюда проставляется исключительно сумма минут, которая получается за работу после 6 вечера в будние дни и вся сумма минут нарядов, которые они выполняют в выходные дни. В эту формулу нужно поставить им расчет как раз таки их переработок в нерабочее время, причем без вычета самой суммы минут. Скажем, если у него стоит 100 минут, то это значит, что эту суму он заработал только в нерабочее время и далее идет анализ в какое время он работал (вечер или ночь) по графам "Время начала работ" и "Время окончания работ". Если работал до 00:00 часов, то эта сумма умножается только на 4,05 рубля за минуту, если работал с 00:00 до 03:00, то эта сумма умножается на коэффициент 1,7 без вычета самой суммы 100*1,7=170 минут; 170*4,05=688,5 рублей, если работал с 03:00 до 06:00, то умножается на 2,5 и на 4,05.

Если время переходящее из вечера в ночь, то все происходит также как в формуле с инженерами ВОЛС, за исключением того, что сумма минут до 00:00 часов не отнимается, а прибавляется к общей сумме и также работы во время ночи считается переработка не только коэффициента, а вместе с суммой минут в это время.

Надеюсь, понятно объяснил?

Из-за того, что много разных входящих данных мне до сих пор кажется, что это реализовать нереально.

Я надеюсь на то, что у Вас всё получится. Искать решение вопроса мне больше негде))) Не смотря на то, что Вы и так очень сильно мне помогли, прошу вас помочь мне доделать эту формулу, если это реально вообще.

Если что непонятно в этом моменте, я с большим удовольствием Вам объясню.

Добавлено через 22 часа 16 минут
Или я надоел со своими данными, или из-за выходных тишина, или все таки это действительно невозможно реализовать...

Добавлено через 1 час 28 минут
У меня созрела идея: =ЕСЛИ(ячейка в столбце где стоят должности="Техники";здесь формула, считающая техников;здесь формула, считающая не техников).
Ребята, помогите добить эти формулы...)))

Добавлено через 1 час 53 минуты
Еще заметил, если поставить время начала работ и конец работ до 00:00, например с 18:00 до 23:00, начинает показывать "погоду", а по всем вышеописанным правилам должно быть 0 рублей, так как ночного коэффициента не было в эти часы. И даже когда ставлю например с 18:00 до 01:00, то все равно какие-то отрицательные значения, а должно посчитать из всей суммы минут 1 час с переработками и выставить значение 1 часа переработок, так как был всего 1 час работы в ночное время с коэффициентом 1,7
0
OLEGOFF
966 / 426 / 107
Регистрация: 27.02.2013
Сообщений: 1,265
12.08.2017, 22:16 #17
Уважаемый DanGo5, поймите правильно,если что -то не сходится и вы хотите это исправить,то необходимо расписать всё по шагам.(для себя)
Вам показали принцип,а вы должны теперь взять и расписать для себя все по порядку.
Если то-то то-то,то считается так,а если другое,то считается так и т.д. (Отдельными формулами в разных ячейках)
А когда все получится как вам надо,то вам помогут все это объединить в единое целое.
0
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 25
13.08.2017, 05:08  [ТС] #18
Уважаемый OLEGOFF, есть уже готовая формула, которую сделал AleksSid:
Код
=((L4-(L4/(ОСТАТ(I4;1)*24))*ОСТАТ(I4-H4;1)*24)/(ОСТАТ(H4;1)*24)*3*0,7+(L4-(L4/(ОСТАТ(I4;1)*24))*(ОСТАТ(I4-H4;1)*24))/(ОСТАТ(H4;1)*24)*((ОСТАТ(H4;1)*24)-3)*1,5)*5,49,
К ней я добавил отбор по должностям:
Код
=ЕСЛИ(B4="Техник";K4/60*242,7;((K4-(K4/(ОСТАТ(H4;1)*24))*ОСТАТ(H4-G4;1)*24)/(ОСТАТ(G4;1)*24)*3*0,7+(K4-(K4/(ОСТАТ(H4;1)*24))*(ОСТАТ(H4-G4;1)*24))/(ОСТАТ(G4;1)*24)*((ОСТАТ(G4;1)*24)-3)*1,5)*5,49).
И все бы прекрасно работало, если бы не некоторые нюансы в формуле AleksSid, которые я не знаю как исправить:

Еще заметил, если поставить время начала работ и конец работ до 00:00, например с 18:00 до 23:00, начинает показывать "погоду", а по всем вышеописанным правилам должно быть 0 рублей, так как ночного коэффициента не было в эти часы. И даже когда ставлю например с 18:00 до 01:00, то все равно какие-то отрицательные значения, а должно посчитать из всей суммы минут 1 час с переработками и выставить значение 1 часа переработок, так как был всего 1 час работы в ночное время с коэффициентом 1,7

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

Выкладываю образец, красным отметил те моменты, которые считают неправильно.
0
Вложения
Тип файла: xlsx Образец_1 (3).xlsx (11.7 Кб, 3 просмотров)
AleksSid
581 / 481 / 227
Регистрация: 05.01.2014
Сообщений: 1,043
13.08.2017, 07:31 #19
Код
=ЕСЛИ(C2="Техники";M2*4,05;M2*5,49)
Это ответ на пост №15
0
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 25
13.08.2017, 09:19  [ТС] #20
Я уже догадался. А как исправить ошибку с монтажниками и инженерами ВОЛС, которые работали до 00:00?

Добавлено через 11 минут
И для инженеров ВОДА и монтажников СТР действует та формула, которую мы мучали изначально.
0
13.08.2017, 09:19
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
13.08.2017, 09:19
Привет! Вот еще темы с решениями:

Как нормально посчитать время в midi
Как работает время в midi я разобрался, и решил написать что-нибудь просто...

Как посчитать время выполнения программы?
Как посчитать время выполнения программы? нужно узнать за сколько секунд...

Как посчитать время выполнения программы?
Подскажет ли кто то каким способом можно посчитать время исполнения программы?...

Как посчитать время, затраченное на выполнение
Всем привет. Есть внешняя библиотека к которой обращаемся. Результат выводим в...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
20
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Рейтинг@Mail.ru