Форум программистов, компьютерный форум CyberForum.ru
Наши страницы

MS Office Excel

Войти
Регистрация
Восстановить пароль
 
 
DanGo5
0 / 0 / 0
Регистрация: 30.01.2017
Сообщений: 24
#1

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

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

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

Есть данные во вложении. Суть такова - сотрудник, работающий после 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):

Progress Bar, как посчитать ориентировочное время работы приложения на сервере? - C# ASP.NET
На сайте реализована такая фича: Клиент кликает кнопку, и всё, что он видит на экране печатается в ворде с помощью Word componentы....

Посчитать время работы сортировки - PascalABC.NET
Здравствуйте,возникла задача посчитать время работы сортировки...но в PascalABC.NET как я понял нету.ни dos'a,ни utils'a....как бы???как...

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

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

Как посчитать время выполнения программы? - Ruby
Подскажет ли кто то каким способом можно посчитать время исполнения программы? Добавлено через 1 час 2 минуты И так, если что: ...

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

Надоела реклама? Зарегистрируйтесь и она исчезнет полностью.
AleksSid
380 / 351 / 166
Регистрация: 05.01.2014
Сообщений: 754
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
380 / 351 / 166
Регистрация: 05.01.2014
Сообщений: 754
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
Сообщений: 24
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
380 / 351 / 166
Регистрация: 05.01.2014
Сообщений: 754
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
Сообщений: 24
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
Сообщений: 24
10.08.2017, 14:32  [ТС] #7
Есть наряд на работы, в котором он напротив граф с видами работ проставляет минуты, потраченные на эти виды работ. В конце получается n-ая сумма мнут на этот наряд. Выполнял он его например с 00: до 03:00 ночи. эту n-ую сумму минут умноженную на 5,49 он получил уже и она же ложится в графу "Сумма (ч/м), на одного", но в рамках данного эксперимента можно поставить лбую сумму. Но в эти часы действовал еще и повышающий коэффициент 1,7, нужно вычислить сумму в рублях этого коэффициента.

Я не знаю как ещё объяснить))))
0
AleksSid
380 / 351 / 166
Регистрация: 05.01.2014
Сообщений: 754
10.08.2017, 19:57 #8
Лучший ответ Сообщение было отмечено автором темы, экспертом или модератором как ответ
В общем как понял, время отработанное до 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
Сообщений: 24
11.08.2017, 04:21  [ТС] #9
Спасибо большое... Урааааааааааааааааааааааа!!!!!!! На первый тест вроде бы все работает правильно. Даже не нужно высчитывать теперь сколько минут они потратили до 00:00 часов...

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

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

Уж очень она сложна в написании...
0
AleksSid
380 / 351 / 166
Регистрация: 05.01.2014
Сообщений: 754
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
Сообщений: 24
11.08.2017, 09:34  [ТС] #11
Нашел ошибку - не работает формула, если час не целый. Ввожу с 01:20 до 03:40 - в конечном столбце ошибка.

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

Добавлено через 1 минуту
Умножаем на 0,7 и 1,5 чтобы не делать лишнее вычитание минут - А это откуда взялось?
0
AlexM
1581 / 1198 / 485
Регистрация: 04.09.2015
Сообщений: 2,299
11.08.2017, 09:45 #12
Цитата Сообщение от DanGo5 Посмотреть сообщение
А это откуда взялось?
Из математики.
Х * 1,7 - Х = Х * (1,7 - 1) = Х * 0,7
0
AleksSid
380 / 351 / 166
Регистрация: 05.01.2014
Сообщений: 754
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
Сообщений: 24
11.08.2017, 11:39  [ТС] #14
А вот тут не поможете - Нашел ошибку - не работает формула, если час не целый. Ввожу с 01:20 до 03:40 - в конечном столбце ошибка.

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

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

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

Выкладываю фрагмент, чтобы было понимание о чем я говорю.
0
Вложения
Тип файла: xlsx Монтажники и техники.xlsx (12.2 Кб, 2 просмотров)
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
11.08.2017, 11:49
Привет! Вот еще темы с ответами:

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

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

Как посчитать время пребывания пользователя на сайте? - PHP БД
Есть БД с пользователями, и там есть поле &quot;общее время&quot; - это общее время проведенное на сайте. Надо его как то высчитывать. Самому в...

Посчитать в секундах время нажатия на каждую клавишу и время ее удержания - Visual Basic .NET
Здравствуйте, нужна ваша помощь! Есть фраза, которую пользователь вводит в текстовое поле, необходимо посчитать в секундах время нажатия...


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

Или воспользуйтесь поиском по форуму:
Yandex
Объявления
11.08.2017, 11:49
Ответ Создать тему
Опции темы

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