У меня есть следующая проблема:
В G4 у меня 19/01/2016 10:00
В F4 у меня есть значение 200 (units)
В Е4 у меня есть значение 38 (minutes)
Я хочу рассчитать G4 + (E4*F4) т.е. 19/1/16 10:00 + 7,600 minutes но исходя из рабочей недели:
Monday to Thursday 8am-5pm и Friday 8am-1pm
поэтому показываю значение 09/02/2016 13:40
Мое понимание проблемы:
Настройте таблицу для вашего графика
Составьте таблицу с двухнедельным графиком. Нам нужно, чтобы он длился две недели, потому что таким образом мы можем выбрать первую подходящую дату начала (например, вторник), а затем включить следующие 7 строк и знать, что у нас полная рабочая неделя (например, со вторника по понедельник). Если у вас не было расписания дважды, и вы начали в пятницу, вы получите один день, а затем кучу пустых строк. Не забудьте включить субботу и воскресенье. Вот снимок таблицы, которую я назвал tblSchedule:
... и версия CSV (время указывается в долях дня):
Weekday,Day,Start Time,Work Hours 1,Sunday,0,0 2,Monday,0.333333333333333,9 3,Tuesday,0.333333333333333,9 4,Wednesday,0.333333333333333,9 5,Thursday,0.333333333333333,9 6,Friday,0.333333333333333,5 7,Saturday,0,0 1,Sunday,0,0 2,Monday,0.333333333333333,9 3,Tuesday,0.333333333333333,9 4,Wednesday,0.333333333333333,9 5,Thursday,0.333333333333333,9 6,Friday,0.333333333333333,5 7,Saturday,0,0
Настройте таблицу для своих производственных прогонов
У вас уже есть начало этого. Мы собираемся добавить несколько полей, и я предполагаю, что вы используете фактическую таблицу (Вставить ленту> Таблица). Если вы этого не сделаете, это все еще будет работать, но понять формулы будет сложнее, потому что это будет набор ссылок на ячейки вместо имен полей. Таблицы потрясающие. Вот снимок того, что я назвал tblProduction: (Обратите внимание, что моя система использует формат не-ISO для дат m/d/yyyy потому что Америка.)
... и строка заголовка CSV:
Min / Unit,Qty Units,Start,Production Time (hrs),Weeks,Days,Hours,End
Добавьте свои формулы
Время производства (часы)
=[@[Min / Unit]]*[@[Qty Units]]/60
Этот довольно очевиден. Единственная хитрость в том, что мы конвертируем минуты в часы, потому что остальная часть математики будет использовать часы.
Недели
=[@[Production Time (hrs)]]/(SUM(tblSchedule[Work Hours])/2)
Это просто конвертируется из часов в рабочие недели. Обратите внимание, что мы должны разделить сумму всех рабочих часов в неделю на 2, потому что наш график составляет две недели, а не одну.
дней
=MATCH(TRUE,INDEX((SUBTOTAL(9,OFFSET(tblSchedule[Work Hours],WEEKDAY([@Start])-1,0,ROW($A$1:$A$7)))-ROUND((((((TIME(HOUR([@Start]),MINUTE([@Start]),SECOND([@Start])))-INDEX(tblSchedule[Start Time],WEEKDAY([@Start])-1))*24))+(MOD([@Weeks],1)*(SUM(tblSchedule[Work Hours])/2))),2))>=0,0),0)-1
Это большой монстр. Это может быть разбито на несколько частей, если это поможет, но я пытался сохранить его как хороший готовый продукт, и я подумал, что ваш друг не захочет все дополнительные столбцы там. Если вы собираетесь это сломать, это может выглядеть примерно так:
... и строка заголовка CSV:
Days,Start Time,Work Start Time,Hours in Last Week,Hours from Start of Last Week
Начальное время
=TIME(HOUR([@Start]),MINUTE([@Start]),SECOND([@Start]))
Время начала работы
=INDEX(tblSchedule[Start Time],WEEKDAY([@Start])-1)
Часы на прошлой неделе
=MOD([@Weeks],1)*(SUM(tblSchedule[Work Hours])/2)
Часы с начала прошлой недели
=ROUND(((([@[Start Time]]-[@[Work Start Time]])*24)+[@[Hours in Last Week]]),2)
Последняя формула нам нужна. Если развернуть эти столбцы, формула для Days будет такой:
=MATCH(TRUE,INDEX((SUBTOTAL(9,OFFSET(tblSchedule[Work Hours],WEEKDAY([@Start])-1,0,ROW($A$1:$A$7)))-[@[Hours from Start of Last Week]])>=0,0),0)-1
Основная идея состоит в том, чтобы найти первый день в графике, когда общее количество рабочих часов с начального дня больше, чем количество часов, необходимых для завершения производства. Вот что делает MATCH(TRUE,INDEX((SUBTOTAL()-[Hours Left])>=0,0),0)-1 . Функция SUBTOTAL делает что-то магическое с OFFSET и возвращает массив значений. Эти значения представляют собой общее количество рабочих часов к концу первого дня, второго дня, третьего дня и т.д. Вычтите оставшиеся часы, которые нам нужны для работы, и вы получите либо положительные, либо отрицательные числа (или ноль, поэтому >=0 вместо просто >0). INDEX возвращает массив значений FALSE и TRUE поэтому MATCH находит первое значение TRUE . Так как мы выяснить , сколько еще дней нам нужно не только в первый день, отнимите один в конце. Это сложный процесс, чтобы получить одно число от 0 до 6.
часов
=(MOD([@Weeks],1)*(SUM(tblSchedule[Work Hours])/2))-IF([@Days]=0,0,SUM(OFFSET(tblSchedule[Work Hours],WEEKDAY([@Start])-1,0,[@Days])))
Начало этого аналогично формуле « Hours in Last Week мы использовали выше. Оператор IF в конце вычитает рабочее время между первым и последним днем, исключая. Если мы начнем вторник и закончим пятницу, вычтите среду и четверг. Это дает нам часы, которые мы должны работать в последний день (который может быть таким же, как в первый день).
Конец
=[@Start]+7*TRUNC([@Weeks])+[@Days]+[@Hours]/24
Дата начала + 7 * (число недель в виде целого числа, отбрасывая дробь) + Дни + (часы преобразуются в десятичное значение дней) = дата и время и конец производства.
Да, это некоторые большие формулы. Тем не менее, я сделал это довольно простым в обслуживании и протестировал пару различных крайних случаев, так что я думаю, что все работает правильно. Если ваш друг хочет аккуратный лист, используйте большую формулу для Days . Если он хочет тот, который он может объяснить легче, используйте версию с несколькими формулами помощника.
Что бы это ни стоило, ваш друг также может добавить в реальный мир такие факторы, как ожидаемый уровень брака и время простоя (включая запланированные перерывы). Вот пример, который учитывает их до того, как их кормят сложными формулами выше.
Отклонить рейтинг
Вводится вручную в процентах.
Время производства (часы)
=[@[Min / Unit]]*([@[Qty Units]]/(1-[@[Reject Rate]]))/60
Стандартные часы
Набирается вручную. Те же значения, что и в исходных примерах выше.
время простоя
Вводится вручную в виде количества часов, а не в процентах. Вы можете изменить его в процентах, но часы показывать легче.
Рабочие часы
=[@[Standard Hours]]-[@Downtime]
Обратите внимание, что это может испортить фактическое время окончания в последний день производства. Формулы предполагают, что вы начинаете в 8 утра и работаете в течение X часов, когда действительно где-то есть перерыв в середине. Это все еще будет работать для приближения, хотя.