Профессиональные приемы работы в Microsoft Excel

Обмен опытом => Microsoft Excel => Тема начата: darts180 от 06.03.2012, 12:07

Название: Вычисление времени переработки
Отправлено: darts180 от 06.03.2012, 12:07
Добрый день уважаемые форумчане.
Помогите в решении задачки.
Есть время прихода на работу и время ухода с работы, надо вычислить среднее время переработки, при условии что рабочий день с 09.00 до 18.00
Образец таблицы во вложенном файле.
Название: Re: Вычисление времени переработки
Отправлено: ShAM от 06.03.2012, 12:54
Здравствуйте, darts180.
Позвольте уточнить:
1. Для ячейки В3 переработка 10 мин или 11 мин?
2. Для ячейки Е3 переработка: - 1 час 05 мин (с минусом) или 0?
3. Можно время прихода и время ухода учитывать в отдельных строках в формате времени, чтобы не увеличивать формулу, пытаясь вытащить время из текста?
4. При подсчете среднего пустые ячейки учитываются или нет?
Было бы хорошо если бы Вы поставили несколько значений, подсчитанных вручную, как должно быть.
Название: Re: Вычисление времени переработки
Отправлено: Poltava от 06.03.2012, 13:21
Да и неплохо было бы узнать это должна быть одна формула в одной ячейке ? макрос ? или можно использовать дополнительные ячейки для промежуточных подсчетов
Название: Re: Вычисление времени переработки
Отправлено: darts180 от 06.03.2012, 14:10
1. Среднее время следует учитывать только если работник ушел после 18.00 т.е в ячейке В3 переработка составит 11 минут. (Пришедшие до 9.00 не учитываются)
2. В пятницу рабочий день до 16.45
3. Т.к. временные интервалы запросов могут быть разными (месяц или несколько дней) нужно во второй строке найти последнюю заполненную ячейку и справа от неё в третьей пустой ячейке создать ячейку «Среднее время переработки» и под ней производить подсчет.
4. Желательно создать макрос.
Название: Re: Вычисление времени переработки
Отправлено: ShAM от 06.03.2012, 15:35
Формулами.
Название: Re: Вычисление времени переработки
Отправлено: darts180 от 06.03.2012, 17:19
ShAM спасибо за формулу.
Одно уточнение, в те дни когда работник ушёл раньше 18.00 считается что он переработал 0 минут, т.е. недоработок не должно быть.
Если можно подкоректируйте формулу пожалуйста.
Название: Re: Вычисление времени переработки
Отправлено: Jim от 06.03.2012, 17:30
{=ЕСЛИ(СРЗНАЧ(ЕСЛИ(B3:W3="";"";ЕСЛИ(ДЕНЬНЕД(B$2:W$2)=5;ПРАВСИМВ(B3:W3;5)-ВРЕМЯ(16;45;0);ПРАВСИМВ(B3:W3;5)-ВРЕМЯ(18;0;0))*1440))<0;0;СРЗНАЧ(ЕСЛИ(B3:W3="";"";ЕСЛИ(ДЕНЬНЕД(B$2:W$2)=5;ПРАВСИМВ(B3:W3;5)-ВРЕМЯ(16;45;0);ПРАВСИМВ(B3:W3;5)-ВРЕМЯ(18;0;0))*1440)))}
Название: Re: Вычисление времени переработки
Отправлено: Poltava от 06.03.2012, 17:40
Я бы еще вместо ячеек ссылался на название диапазонов так проще будет менять формулу под файл
Название: Re: Вычисление времени переработки
Отправлено: darts180 от 06.03.2012, 17:50
Цитата: Poltava от 06.03.2012, 17:40
Я бы еще вместо ячеек ссылался на название диапазонов так проще будет менять формулу под файл
Уважаемый Poltava немогли бы Вы свою версию файла сбросить.
Заранее спасибо.
Название: Re: Вычисление времени переработки
Отправлено: MCH от 06.03.2012, 20:21
еще вариант (в желтых ячейках)
Название: Re: Вычисление времени переработки
Отправлено: darts180 от 06.03.2012, 22:10
Цитата: MCH от 06.03.2012, 20:21
еще вариант (в желтых ячейках)
Большое спасибо MCH.
То что надо.
Название: Re: Вычисление времени переработки
Отправлено: ShAM от 06.03.2012, 23:07
Ну, и я свое добавлю, с доп.условием:
=СУММПРОИЗВ((B3:W3<>"")*((ДЕНЬНЕД(B$2:W$2;2)=5)*(ПРАВСИМВ(0&B3:W3;5)-ВРЕМЯ(16;45;0))*(ПРАВСИМВ(0&B3:W3;5)-ВРЕМЯ(16;45;0)>0)+(ДЕНЬНЕД(B$2:W$2;2)<>5)*(ПРАВСИМВ(0&B3:W3;5)-ВРЕМЯ(18;0;0))*(ПРАВСИМВ(0&B3:W3;5)-ВРЕМЯ(18;0;0)>0)))*1440/СЧЁТЗ(B3:W3)
Название: Re: Вычисление времени переработки
Отправлено: darts180 от 06.03.2012, 23:28
Цитата: ShAM от 06.03.2012, 23:07
Ну, и я свое добавлю, с доп.условием:
=СУММПРОИЗВ((B3:W3<>"")*((ДЕНЬНЕД(B$2:W$2;2)=5)*(ПРАВСИМВ(0&B3:W3;5)-ВРЕМЯ(16;45;0))*(ПРАВСИМВ(0&B3:W3;5)-ВРЕМЯ(16;45;0)>0)+(ДЕНЬНЕД(B$2:W$2;2)<>5)*(ПРАВСИМВ(0&B3:W3;5)-ВРЕМЯ(18;0;0))*(ПРАВСИМВ(0&B3:W3;5)-ВРЕМЯ(18;0;0)>0)))*1440/СЧЁТЗ(B3:W3)

Cпасибо. Ваш вариант так же хорош.
Название: Re: Вычисление времени переработки
Отправлено: Данила от 27.05.2019, 08:16
Как скачать вложение???