Сложный расчет часов ротации в Excel

Question

Итак, здесь работает несколько смен, и мне нужна формула для расчета общего количества часов.

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

некоторые из них 2-FIN, некоторые 7-5 вечера, некоторые РАНЬШЕ или ПОЗДНЕЕ

Теперь я знаю, сколько часов длится каждая смена, но мне нужен способ, чтобы Excel мог их рассчитать. Теперь я полагаю, что я мог бы создать таблицу с кучей возможных смен и их общим количеством часов, а затем сопоставить расписание с таблицей и суммировать общее количество часов за неделю.

но я понятия не имею, как это сделать, все мои попытки сделать это возвращаются к проблеме с вашей формулой и никаких дальнейших объяснений

Answer 1

Поскольку вы используете Excel 2010, у вас есть несколько замечательных инструментов, которые упростят это. Вот что я бы сделал:

1. Создайте простую таблицу для ваших смен. Она может состоять из двух столбцов: Name&Length

   

2. Составьте таблицу для своего расписания. Она должна иметь четыре столбца: Date, Employee Name, Shift, Hours.

   

   Dateможно ввести вручную.

   Оба Employee Nameмогут Shiftиспользовать проверку данных Excel для создания раскрывающегося списка определенных значений.

   Hoursбудет использовать VLOOKUP для определения его значения из вашей таблицы смещений. В моем примере формула =VLOOKUP([@Shift],Table1,2,FALSE). Это будет искать Shiftзначение во второй таблице в первой таблице, Table1, и возвращать второй столбец, Lengthи будет возвращать только Exact Match (2)(что означает, что вам не нужно иметь вещи в алфавитном порядке).

   Я также добавил в таблицу итоговую строку для быстрой проверки часов.

3. Наконец, добавьте дополнительную функциональность, создав Pivot Tableиз вашей второй Таблицы. Это позволит вам быстро суммировать и сообщать ваши данные. Вы можете сделать несколько сводок из источника данных, поэтому вы можете легко сделать эти два и можете обновлять их всякий раз, когда обновляются ваши исходные данные (Таблица 2).

   

Answer 2

Вы можете настроить таблицу с выпадающими списками, чтобы каждая смена была в одном формате. Затем сделайте таблицу со сменами и часами рядом с ними. Например, с 7 до 9 вечера в одной ячейке, а затем 2 часа в ячейке рядом с ней.

Они используют сумму ВПР, например

=SUM(VLOOKUP(B6;Sheet2.A3:B4;2;0);VLOOKUP(B7;Sheet2.A3:B4;2;0))

Это даст вам общее количество часов.

Обратите внимание, что я использовал Open Office, чтобы проверить, работает ли это, если использовать Microsoft Excel, измените ";" на "," в формуле. Если в расписании нет часов, вы можете либо вписать оператор if, либо указать нерабочие 0 часов в расписании.