Заполняем формулами строку «Остаток»

Для этого у станавливаем курсор на пересечении строки, соответствующей 1-му исходному пункту и столбцу остаток (ячейка J12), нажимаем знак «=» на клавиатуре и выделяем ячейку на пересечении столбца «Запасы» и строки «Карьер №1» (соответственно ячейка Н12). Затем нажимаем знак «-» на клавиатуре и выделяем ячейку на пересечении столбца «Вывезено» и строки «Карьер №1» (соответственно ячейка I12), нажимаем клавишу «Enter» на клавиатуре.

Копируем формулу расчета остатка продукции, вывезенной из 1-го исходного пункта для остальных исходных пунктов. Для этого выделяем ячейку J12 и нажимаем иконку копировать на панели инструментов, а затем выделяем остальные исходные пункты (соответственно ячейки J13 и J14) и нажимаем иконку вставить на панели инструментов.

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

Для этого активируем ячейку, в которой будет размещаться значение целевой функции, (в нашем случае ячейка G17). Вызываем «Мастер функций» и в категории «Математические» выбираем «СУММПРОИЗВ», нажимаем «ОК» (рис. 19).

В окне «Аргументы функции» в строку «Массив 1» (рис. 2.6) вводим значения стоимости перевозок, для чего устанавливаем курсор в строку «Массив 1» и нажимаем на левую клавишу мыши.

Затем устанавливаем курсор в ячейку на пересечении исходного пункта №1 и 1-го пункта потребления в транспортной таблице, нажимаем левую клавишу мыши и, не отпуская клавишу мыши, «протаскиваем» курсор по диагонали до ячейки последнего исходного пункта и последнего пункта потребления включительно (в нашем случае от ячейки C4 до ячейки G6).

Рис. 19. Ввод формулы для расчета значения целевой функции.

В окне «Аргументы функции» в строку «Массив 2» (рис. 20) вводим значения искомых переменных, для чего сначала устанавливаем курсор в окне «Аргументы функции» в строку «Массив 2» и нажимаем на левую клавишу мыши.

Затем устанавливаем курсор в ячейку на пересечении исходного пункта №1 и 1-го пункта потребления во вспомогательной таблице. Нажимаем левую клавишу мыши и, не отпуская ее, протаскиваем курсор по диагонали до ячейки последнего исходного пункта и последнего пункта потребления включительно (в нашем случае от ячейки C12 до ячейки G14) (рис. 20).

В окне «Аргументы функции» нажимаем «ОК». В ячейке, где должно размещаться значение целевой функции, будет «0». После проведения подготовительных действий переходим к расчёту плана перевозок.

Рис. 20. Ввод массивов функции «СУММПРОИЗВ».

Вычислительный этап

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

2). Заполняем поля в появившемся окне.

В окне «Поиск решения» [3] устанавливаем курсор в строку «Установить целевую ячейку», а затем активируем ячейку, в которую мы хотим поместить значение целевой функции, установив на нее курсор и щелкнув левой кнопкой мыши. Эта ячейка будет мерцать (в нашем случае ячейка G17).

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

Для этого активируем радиокнопку «Минимальное значение». В разделе «Изменяя ячейки», вводим ячейки, соответствующие плану перевозок (C12:G14). Для их ввода устанавливаем курсор в ячейку, соответствующую пересечению исходного пункта № 1 и пункта потребления № 1 и щелкаем левой кнопкой мыши. «Протаскиваем» курсор с нажатой левой кнопкой мыши по диагонали до ячейки, соответствующей значению последнего пункта потребления и последнего исходного пункта включительно во вспомогательной таблице (в нашем случае от ячейки С12 до ячейки G14) (рис.21).

Рис. 21. Ввод данных в окне «Поиск решения».

Устанавливаем курсор в окно «Ограничения», щелкнув на нем левой кнопкой мыши. Нажимаем кнопку «Добавить». В окне «Ссылка на ячейку» вводим данные по количеству ввезенной продукции на каждую строительную площадку (пункт потребления), для чего помещаем курсор в строку «Ввезено» вспомогательной таблицы и ячейку, соответствующую 1-му пункту потребления. При нажатой левой кнопкой мыши «протаскиваем» курсор по строке «Ввезено» до ячейки, соответствующей последнему пункту потребления (в нашем случае от ячейки С16 до ячейки G16).

В окне правее «Ссылка на ячейку» выбираем знак ограничения. В нашем случае для потребности это будет знак равенства «=». Вводим значение ограничения, для чего устанавливаем курсор в ячейку на пересечении строки потребностей и 1-го пункта потребления (в транспортной таблице). Щелкнув левой кнопкой мыши и не отпуская ее, «протаскиваем» курсор по строке потребностей до последнего пункта потребления включительно (в нашем случае от ячейки С7 до ячейки G7). Нажимаем ОК.

Переходим к вводу следующего ограничения, нажав на кнопку «Добавить». Для ввода ограничений по запасам поступаем аналогично. Активируем ячейку, соответствующую пересечению исходного пункта № 1 и столбца «Вывезено» во вспомогательной таблице (рис. 22) и «протаскиваем» курсор по столбцу до ячейки, соответствующей последнему исходному пункту включительно (в нашем случае от ячейки I12 до ячейки I14). Переходим к вводу знака ограничений, для чего в окне, правее ссылки на ячейку выбираем соответствующий знак. В нашем случае это «<=». Вводим значение ограничения, установив курсор в окно ограничения и выделив после этого значение запасов в столбце «Запасы» (в исходной транспортной таблице) от 1-го пункта потребления до последнего пункта потребления включительно (в нашем случае от ячейки Н4 до ячейки Н6). Нажимаем «ОК» (рис. 22).

Рис. 22. Ввод данных в окне «Добавление ограничения».

Переходим к вводу параметров решения, нажав кнопку «Параметры» (рис 23). В окне «Параметры поиска решения» оставляем стандартные условия без изменения. Ставим галочку в чекбоксах «Линейная модель» и «Неотрицательные значения переменных». После чего нажимаем «ОК» (рис. 23).

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

Рис. 24. Работа в окне «Параметры поиска решения».

3.4. Методические рекомендации к решению задачи по разработке оптимального плана производства-хранения продукции в логистических цепях

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