Пример 5. Сравнение эффективности работы

В мастерской работают три слесаря: Иванов, Петров и Сидоров. В табл. 1.7 приведены усреднённые данные о расходе ресурсов и выполненной работе каждым слесарем в течение рабочего дня. Затраченные ресурсы распадаются на две основные категории: расход материалов и затраты рабочего времени. Выполняемые заказы подразделяются на три категории. Необходимо определить, кто из слесарей работает эффективно, а кто нет.

Таблица 1.7

Расход ресурсов и объём работ, выполненных каждым слесарем в течение дня

Рассмотрим модель, представленную на рис. 1.24. Варьируемыми параметрами здесь являются условная стоимость затрат и условная цена выполнения заказа. Формула, записанная в ячейку В15:

=СУММПРОИЗВ(B11:C11;B7:C7), (1.20)

а в ячейку D15:

=СУММПРОИЗВ(F11:H11;F7:H7). (1.21)

Рис. 1.24. Модель задачи о сравнении эффективности работы

Аналогичные формулы используются в ячейках В16:В17 и D16:D17, чтобы подсчитать соответственно стоимость затрат и цену продукта, произведённого слесарями Петровым и Сидоровым. В ячейке В20 записано выражение:

=ВПР(B3;A15:D17;2). (1.22)

Функция ВПР ищет число, записанное в ячейке В3 в первом столбце массива A15:D17 и возвращает число, находящееся во втором столбце и той же строке указанного массива, что и число из ячейки В3.

В целевой ячейке А23 записана формула:

=

Рис. 1.25. Заполнение диалогового окна команды: Поиск решения

Образец заполнения диалогового окна команды: Поиск решения показан на рис. 1.25. Нам необходимо воспользоваться командой: Поиск решения три раза (по числу работников), каждый раз устанавливая в ячейке В3 число, соответствующее определённому работнику.

Логика действий в этой задаче такова. 1) Выбираем какого либо конкретного работника и записываем его номер в ячейку В3. 2) Варьируя стоимость разных видов затрат, подбираем полную стоимость затрат, сделанных работником, равной единице. Это позволяет ограничить сверху диапазон значений затрат. Вместо единицы можно было бы взять и другую конечную величину. Снизу диапазон варьирования ограничен нулевым значением. 3) Варьируя стоимость разных видов произведённой работником продукции, стремимся уравнять её с величиной затрат при условии, что для других работников, при тех же варьируемых параметрах, цена продукта не должна превзойти стоимость затрат. Если нам удастся приравнять стоимость затрат и цену произведённого продукта для каждого работника, то мы должны заключить, что все они работают эффективно.

В нашем примере не удаётся уравнять стоимость затрат и цену произведённого продукта для слесаря Петрова. Действительно, из условия видно, что он, расходуя больше материалов и затрачивая рабочего времени больше, чем Иванов и Сидоров, не опережает их ни по одному из видов продукции.

При заданном нами ограничении – положительности стоимости затрат и цены продукции − мы можем выявить лишь явное отставание Петрова в работе. Мы видим, что в процессе поиска оптимального решения программа зануляет стоимость материалов и расценки на заказы категории 2. В реальности этого произойти не может. Поэтому, для более точной оценки эффективности труда работников, необходимо задать более узкие пределы варьирования параметров.