Подготовка исходных данных

2.1.1. Создаем исходную таблицу и вносим в нее имеющиеся исходные данные (рис.5).

Рис. 5. Исходные данные для решения задачи.

2.2. Заполнение исходной таблицы формулами, связывающими план, ограничения и целевую функцию (значение дохода).

2.2.1. Заполняем формулами столбец ограничений «использовано», для чего:

устанавливаем курсор в ячейку на пересечении исходного продукта «мука» и столбца «использовано» (ячейка Е17) и активируем ее щелкнув левой кнопкой мыши (рис. 6);

Рис. 6. Заполнение формулами исходной таблицы.

вызываем мастер функций, щелкнув по пиктограмме f_x и в категории «математические» выбираем функцию СУММПРОИЗВ, нажимаем ОК;

в появившемся окне (рис. 7) в строку массив 1 вводим нормы расхода муки на изготовление ед. производства хлеба и батона, выделив в строке «мука» ячейки В17:С17. Для этого необходимо установить курсор в ячейку В17, щелкнуть левой кнопкой мыши и удерживая ее в нажатом положении протащить курсор до ячейки С17 включительно;

устанавливаем курсор в строку массив 2 и вводим в нее адреса ячеек, в которых будут помещены данные по плану производства. Для этого устанавливаем курсор в ячейку на пересечении строки «план» и столбца «хлеб» (В22), щелкаем левой кнопкой мыши и удерживая ее в нажатом положении протаскиваем курсор до ячейки С22 включительно. После этого нажимаем ОК;

заполняем формулами оставшиеся ячейки в столбце «использовано» для каждого исходного продукта. С этой целью фиксируем «замораживаем» абсолютные координаты плана (массив 2) формулы в первой ячейке столбца «использовано», поставив знак $ перед буквенным и цифровым обозначением адреса ячеек (рис. 8), нажимаем ОК и копируем формулу с «замороженными» координатами (адресами ячеек) плана производства в оставшиеся ячейки в столбце использовано. Для копирования необходимо активировать ячейку на пересечении строки «мука» и столбца «использовано», щелкнув по ячейке левой кнопкой мыши, и удерживая ее в нажатом положении, протащить курсор по столбцу использовано до последней ячейки (Е20) включительно.

Рис. 7. Заполнение формулами столбца ограничений «использовано».

Рис. 8. Копирование формул с предварительным «замораживанием» координат массива плана производства.

2.2.2. Заполняем формулами столбец «остаток», для чего:

устанавливаем курсор в ячейку на пересечении строки «мука» и столбца «остаток» и активируем ячейку, щелкнув левой кнопкой мыши;

вводим в ячейку формулу (рис. 9) =D17-E17;

Рис. 9. Ввод формул в столбец «остаток».

копируем формулу =D17-E17 в оставшиеся ячейки этого столбца. Для этого активируем ячейку с введенной формулой, щелкнув левой кнопкой мыши, и удерживая курсор в нажатом положении, протаскиваем его до последней ячейки в этом столбце, соответствующей последнему исходному продукту (ячейка Е20);

2.2.3. Вводим формулу для определения значения дохода, для чего:

активируем свободную ячейку, в которую будет заноситься значение целевой функции;

вызываем мастер функций, щелкнув по пиктограмме f_x, и в появившемся окне в категории «математические» выбираем функцию СУММПРОИЗВ, щелкаем ОК;

в появившемся окне в строку массив 1 вводим адреса ячеек, в которых размещены значения цен на продукцию предприятия (хлеб и батон), а в строку массив 2 адреса ячеек, в которых будут размещены неизвестные значения количества ед. продукции для получения оптимального плана производства (рис 10). Нажимаем ОК;

Рис. 10. Ввод формулы для определения величины дохода.

2.3. Определение оптимального плана производства.

2.3.1. Запускаем программу «Поиск решения» к омандой Данные/Анализ / Поиск решения (Excel 2007) или Сервис/Поиск решения (Excel 2003 и ниже).[1]

2.3.2. Заполняем поля в появившемся окне:

в поле «установить целевую ячейку» вводим адрес ячейки ($F$26), в которой будет находиться искомое значение дохода (рис. 11);

в поле «изменяя ячейки» вводим адреса ячеек (рис. 11), в которых будут находиться искомые величины производства количества ед. хлеба и батона (В22:С22);

устанавливаем курсор в поле ограничения и нажимаем кнопку добавить, щелкнув по ней левой кнопкой мыши;

в появившемся окне «добавление ограничения» в поле «ссылка на ячейку» вводим значения столбца «использовано», для чего после активации ячейки на пересечении строки «мука» и столбца «использовано» (ячейка Е17) протаскиваем курсор до последней строки в этом столбце, соответствующей значению количества ед. последнего исходного продукта (ячейка Е20)

Рис. 11. Заполнение полей программы «Поиск решения»

в поле «знак ограничения» (оно находится правее «изменяя ячейки» выбираем соответствующий знак ≤;

в поле ограничение вводим значения количества ед. запасов по каждому исходному продукту, выделив столбец «Запасы» (рис. 12)

переходим к вводу ограничений на значения переменных (выпуск хлеб) и , (выпуск батон) выполнив аналогичные действия, как и при вводе ограничений на запасы;

2.3.3. Переходим к заполнению окна «Параметры поиска решения», нажав кнопку «параметры» в окне «Поиск решения»:

устанавливаем переключатель «линейная модель» (рис. 13);

устанавливаем переключатель «неотрицательные значения» (рис. 13);

нажимаем ОК.

2.3.4. Определяем оптимальный план производства:

в окне «Поиск решения» нажимаем кнопку «выполнить»;

Рис. 12. Ввод ограничений в окне «поиск решения».

Рис. 13. Заполнение окна «Параметры поиска решения».

в появившемся окне «Результаты поиска решения» (рис. 14)в поле тип отчета выбираем «результаты», «устойчивость» и нажимаем ОК.

Рис. 14. Работа в окне «Результаты поиска решения».

3.3. Методические рекомендации к решению задачи по разработке плана перевозок продукции, обеспечивающего организации минимальные издержки

Поиск по сайту: