Пример 2. Задача оптимизации составления рациона (задача о диете, задача о смесях).

Имеются два вида корма I и II, содержащие питательные вещества (витамины) S₁, S₂, S₃. Содержание числа единиц питательных веществ в 1 кг каждого вида корма необходимый минимум питательных веществ приведены в таблице 7.2 (цифры условные).

Таблица 7.2

Питательное вещество (витамины)	Необходимый минимум питательных веществ	Число единиц питательных веществ в 1 кг корма
Питательное вещество (витамины)	Необходимый минимум питательных веществ	I	II
S₁	9	3	1
S₂	8	1	2
S₃	12	1	6

Стоимость 1 кг корма I и II соответственно равна 4 и 6 руб.

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

Составим оптимизационную модель задачи.

Обозначим x₁, x₂ – количество кормов I и II, входящих в дневной рацион. Тогда этот рацион будет включать (3* x₁+1* x₂) единиц питательного вещества S₁, (1* x₁+2* x₂) единиц веществ S₂, (1* x₁+6* x₂) единиц питательного вещества S₃. Так как содержание питательных веществ S₁, S₂, S₃ в рационе должно быть не менее, соответственно 9, 8 и 12 единиц, то получим систему неравенств:

, (1)

Переменные неотрицательные x₁ ³ 0, x₂ ³ 0.

Общая стоимость рациона F составит:

. (2)

Обозначим x_j (j=1,2,…, n) – число единиц корма n- го вида; b_i (i=1,2,…, m) – необходимый минимум содержания в рационе питательного вещества S_i; a_ij – число единиц питательного вещества S_ij в единице корма j-го вида; c_j - стоимость единицы корма j-го вида. Математическая модель задачи составления рациона в общей постановке примет вид. Найти такой рацион X=(x₁, x₂,…,x_j,…,x_n), удовлетворяющий системе:

и условию

при котором функция

принимает минимальное значение.

Пример 3. Задача оптимизации транспортных затрат. Имеются три поставщика и четыре потребителя. Мощность поставщиков и спрос потребителей, а также затраты на перевозку единицы груза для каждой пары «поставщик - потребитель» сведены в таблицу поставок (таблица 7.3).

Таблица 7.3

Поставщики	Мощности поставщиков	Потребители и их спрос
		1	2	3	4
		20	110	40	110
1	60	1	2	5	3
2	120	1	6	5	2
3	100	6	3	7	4

В левом верхнем углу произвольной (i,j) клетки стоит коэффициент затрат – затраты на перевозку единицы груза от i –го поставщика к j-му потребителю. Задача формулируется следующим образом: найти объемы перевозок для каждой пары «поставщик - потребитель» так, чтобы: мощности всех поставщиков были реализованы, спросы всех потребителей были удовлетворены, суммарные затраты на перевозку были бы минимальны. Обозначим через x_ij объем перевозки от i –го поставщика к j-му потребителю. Заданные мощности поставщиков и спросы потребителей накладывают ограничения на значения неизвестных x_ij. Чтобы мощность каждого из поставщиков была реализована, необходимо составить уравнения баланса для каждой строки таблицы поставок:

Аналогично, чтобы спрос каждого из потребителей был удовлетворен, подобные уравнения баланса составляются для каждого столбца таблицы поставок:

Очевидно, что объем перевозимого груза не может быть отрицательным, поэтому следует ввести ограничение не отрицательности переменных:

x_ij ≥0.

Суммарные затраты F на перевозку выражаются через коэффициенты затрат следующим образом:

Для математической постановки транспортной задачи в общей постановке обозначим через с_ij коэффициенты затрат, через M_i – мощности поставщиков, через N_j – мощности потребителей, (i=1,2,…,m)., (j=1,2,…,n), m – число поставщиков, n – число потребителей. Тогда система ограничений примет вид:

(7)

Система (7) включает в себя уравнения баланса по строкам и по столбцам.

При этом суммарная мощность поставщиков равна суммарной мощности потребителей, т.е.

Целевая функция в данном случае следующая:

(8)

Таким образом, на множестве неотрицательных решений системы ограничений (7) найти такое решение, при котором значение целевой функции (8) будет минимально.

Реализация задач планирования экономических процессов средствами пакета Microsoft Excel

Прокомментируем решение задачи оптимизации управления ресурсами предприятия. Первым действием необходимо ввести исходные данные. Введем на рабочий лист исходную таблицу с данными как это показано на рисунке 7.1.

Для формирования ограничений задачи в ячейку Е5 вводим функцию: СУММПРОИЗВ ($C$10:$D$10;C5:D5).

Вызов функции осуществляется следующими командами: Вставка – Функция – Математические – СУММПРОИЗВ - Массив 1 (C5:D5) (ресурсы единицы продукции) - Массив 2 ($C$10:$D$10) (искомые значения плана производства). Копируем функцию в ячейки С6:С8 с помощью маркера копирования.

В ячейку С11 вводим целевую функцию СУММПРОИЗВ (C9:D9;C10:D10).

Рис. 7.1 – Формульный вид документа

Перейдем теперь к постановке задачи для надстройки «Поиск решения». Для решаемой задачи целевой функцией будет функция в ячейке $С$11, изменяемыми данными – диапазон $С$10:$D$10, содержащий план производства, диапазон $Е$5:$Е$8 – используется для определения ограничений задачи.

Для обращения к надстройке «Поиск решения» используется команда меню Сервис. Но может быть так, что команда Поиск решения в этом меню отсутствует. Тогда необходимо выполнить следующую команду: Сервис - Надстройки, напротив Поиск решения поставить галочку. После активации команды Поиск решения, необходимо заполнить окно диалога по образцу (рис. 7.2).

Установить целевую ячейку $С$11 равной максимальному значению. Изменяя ячейки $С$10:$D$10. Для ввода ограничений нужно нажать кнопку Добавить.

1. Ограничения по ресурсам:

Ссылка на ячейку: = $Е$5:$Е$8, вид ограничения: <=, ограничение: ссылка на ячейку: = $В$5: $В$8. Добавить.

2. Ограничение на не отрицательность переменных:

Ссылка на ячейку: = $С$10:$D$10, вид ограничения: >=, ограничение 0. Добавить.

3. Ограничение на целостность переменных:

Ссылка на ячейку: = $С$10:$D$10, вид ограничения: цел. OК.

Рис. 7.2 – Окно диалога «Поиск решения»

После нажатия кнопки Выполнить надстройка «Поиск решения» приступает к итерациям, после вычислений открывает диалоговое окно Результаты поиска решения, в котором выводится сообщение о решении задачи.

Рис. 7.3 – Результаты решения задачи

В результате решения задачи можно сделать следующий вывод. Максимальная прибыль составит 24, продукции типа P₁ необходимо производить 6, продукции типа P₂ – 4 (рис.7.3)

Прокомментируем решение задачи оптимизации транспортных затрат. Представим данные для решения задачи в матричной форме. Формируется матрица 3:4. Шаблон представления данных для реализации задачи представлен на рис. 7.4.

Рис. 7.4 – Шаблон с исходными данными для решения задачи оптимизации транспортных затрат

Вводим в диапазон B2:E4 коэффициенты затрат на перевозку, в диапазон B5:E5 вводим потребности в грузах, в диапазон F2:F4 вводим мощности поставщиков.

В ячейку F5 вводим формулу (=СУММ(F2:F4)) для проверки типа транспортной задачи. Если задача закрытая, то сумма мощностей поставщиков равна сумме спросу потребителей. Если баланса не соблюдается, то необходимо добавить фиктивного поставщика или потребителя, в качестве коэффициентов затрат в добавленном столбце или строки вводится значение – 0.

Для формирования шаблона решения задачи необходимо ввести следующие расчетные формулы.

Вводим в ячейку В10 формулу =СУММ(В7:В9), в ячейки C10:E10 копируем данную формулу методом протягивания (таким образом, сформировано ограничение по спросу потребителей).

Вводим в ячейку F7 формулу =СУММ(B7:E7), в ячейки F8:F9 копируем данную формулу методом протягивания (таким образом, сформировано ограничение по мощности поставщиков).

Вводим в ячейку В11 формулу для расчета значения целевой функции (=СУММПРОИЗВ(В2:Е4;В7:Е9)).

Далее обращаемся к надстройке Поиск решения. Заполняем окно диалога как показано на рис. 7.5.

Рис. 7.5 – Окно диалога «Поиск решения»

Устанавливаем целевую ячейку $B$11 равной минимальному значению. Изменяя ячейки $B$7:$E$9. Далее приступаем к вводу ограничений, нажимая кнопку Добавить.

1. Ограничение по спросу:

Ссылка на ячейку: =$B$10:$E$10, вид ограничения: =, Ограничение $B$5:$E$5. Добавить.

2. Ограничение на целостность переменных:

Ссылка на ячейку: =$B$7:$E$9, вид ограничения: = цел. Добавить.

3. Ограничение на не отрицательность переменных:

Ссылка на ячейку: =$B$7:$E$7, вид ограничения: >=, Ограничение 0. Добавить.

4. Ограничение по мощностям поставщиков:

Ссылка на ячейку: =$F$2:$F$4, вид ограничения: =, Ограничение $F$7:$F$9. ОК.

Оптимальное решение получено после нажатия кнопки Выполнить. На рис. 7.6 в диапазоне B7:E9 указано оптимальное распределение перевозок грузов для каждой пары «поставщик - потребитель».

Рис. 7.6 – Решение задачи оптимизации транспортных затрат

Контрольные вопросы по теме

“Табличный процессор MS Exсel”

1. Табличный процессор MS Excel. Основные понятия.

2. Что такое адрес ячейки? Где отображается адрес активной ячейки?

3. Маркер заполнения. Создание автосписков. Автозаполнение.

4. Работа с листами. Перемещение по листу.

5. Понятие диапазона. Виды диапазонов? Выделение диапазонов. Выделение несмежных диапазонов.

6. Типы данных в ячейках. Как выравниваются разные типы данных в ячейках?

7. Форматирование ячеек.

8. Редактирование содержимого ячеек.

9. Работа с формулами. Диагностика ошибок в формулах.

10. Типы ссылок в формулах.

11. Как вывести таблицу в режиме формул?

12. Диаграммы. Создание диаграмм.

13. Форматирование диаграмм. Как добавить легенду к созданной диаграмме? Как изменить вид диаграммы?

14. Что такое сводная таблица? Для чего нужны сводные таблицы? Создание сводных таблиц. Как выбрать математическую операцию для вычисляемого поля? Как восстановить данные в сводной таблице?

15. Как построить диаграмму по сводной таблице? Как изменить вид диаграммы?

16. Функция. Виды функций. Форматы логических функций.

17. Найдите в формуле ошибки =ЕСЛИ ((В3 “доллар;S4*5,05;S4*6,2

18. Печать таблиц. Изменение ориентации таблицы. Как центрировать таблицу для печати. Как добавить колонтитулы.

19. Перечислите основные типы задач экономического планирования.

20. Какие общие свойства присущи оптимизационным моделям задач экономического планирования?

21. По каким критериям решаются задачи экономического планирования?

22. Для чего формируется шаблон решения задач экономического планирования?

23. С помощью какого инструмента Microsoft Excel можно решить задачи экономического планирования?

24. Какие действия необходимо выполнить для активации надстройки «Поиск решения»?

25. Какие свойства должен иметь шаблон задач планирования экономических процессов?

26. Какую ссылку должна иметь целевая ячейка в надстройке «Поиск решения»?

27. Какие типы ограничений реализует надстройка «Поиск решения»?

28. Что означают изменяемые ячейки в надстройке «Поиск решения»?

29. Каким позициям в надстройке «Поиск решения» может равняться целевая ячейка?

Литература

1. Холи, Д. Excel 2007. Трюки / Д. Холи, Р. Холи; пер. с англ. А. Струсевич. ─ СПб.: Питер, 2008. ─ 363 с.: ил. ─ 978-5-91180-494-7

№ п/п	Чтобы выделить	Выполните действия
1.	Слово	Дважды щелкните слово.
2.	Рисунок	Щелкните рисунок.
3.	Предложение	Удерживая нажатой клавишу CTRL, щелкните на предложении.
4.	Строка текста	Переместите указатель на полосу выделения^***перед предложением и щелкните кнопкой мыши.
5.	Абзац	Переместите указатель на полосу выделения^*** перед абзацем, и дважды щелкните кнопкой мыши. Другой образ: трижды щелкните абзац.
6.	Несколько абзацев	Переместите указатель на полосу выделения^*** перед абзацем, и дважды щелкните кнопкой мыши, а потом перетяните указатель вверх или вниз.
7.	Небольшой фрагмент текста	Используйте перетаскивание. Установите курсор в начало фрагмента, нажмите клавишу SHIFT и удерживая ее нажимайте соответствующие клавиши управления курсором ® ¯
8.	Большой блок текста	Щелкните начало фрагмента, прокрутите документ так, чтобы на экране появился конец фрагмента, а потом щелкните его, удерживая нажатой клавишу SHIFT. Другой образ. Установите курсор в начало фрагмента, нажмите клавишу SHIFT и удерживая ее нажимайте соответствующие клавиши управления курсором ® ¯
9.	Весь документ	Переместите указатель на полосу выделения^*** перед текстом, после чего трижды щелкните кнопкой мыши.
10.	Колонтитулы	В обычном режиме выберите Вид - Колонтитулы. В режиме разметки дважды щелкните неяркий текст колонтитула. Переместите указатель на полосу выделения^*** перед колонтитулом, после чего трижды щелкните кнопкой мыши.
11.	Вертикальный блок текста (кроме текста внутри ячейки таблицы)	Удерживая нажатой клавишу ALT, перетяните указатель.

^***Полоса выделения - левое поле документа. На полосе выделения указатель мыши принимает вид белой стрелки, направленной вправо.

Реакцией на неправильный ввод формул является сообщения Excel об ошибках, то есть значение в ячейках, которые начинаются со знака #. Чтобы легче было находить и отстранять ошибки в формулах, в Excel предусмотренная простейшая диагностика ошибок, а именно: ошибки разделяются по категориям, и каждой категории отвечает свое сообщение. Список возможных значений ошибок:

† ##### –появляется, когда ширина ячейки недостаточна для размещения в ней числа, даты или времени. Чтобы отстранить ошибку, нужно расширить каморку или изменить формат числа.

† #ИМЯ? - невозможность распознать имя, которое используется. Это значение ошибки возникает, когда неправильно указано имя объекта или имеются ссылки на имя, которое было изъято; когда неверно записана функция; когда при записи адресов вместо латыни использована кириллица и т.д.

† #ЗНАЧ! - попытка некорректного использования функции. Обычной ошибкой является несоответствие данных установленному формату, например, вместо числа или даты в аргументе записан текст. Это же значение ошибки будет появляться, когда для функции или оператора, которые требуют одного значения аргумента, записывают несколько.

† #ЧИСЛО! – значение ошибки, которые означает проблему, связанную с представлением или с использованием чисел. Не исключено, что в функции с числовым аргументом используется аргумент нечислового формата. Возможно также, что в ячейку введена формула, которая возвращает слишком большое значение по модулю (свыше 1037).

† #ССЫЛКА! - означает наличие проблемы с интерпретацией ссылок, которые имеются в формуле. Возможно, что формула содержит ссылку на ячейку, которая уже изъята, или ссылку на ячейку, в которую скопировано содержимое других ячеек.

† #ДЕЛ/0! - попытка деления на нуль. Такая ситуация чаще возникает не из-за того, что в ячейке записано явное деление на нуль (оператор /0), а как следствие использования ссылки на пустую ячейку или ячейку, которая содержит нулевое значение.

† #ПУСТО! - значение ошибки, которое появляется в случае задания в ссылке пустого множества ячеек.

† #Н/Д – сокращение от термина «неопределенные дани». Это значения ошибки обычно специально вводится в ячейки, чтобы предотвратить вычисления, которые не могут быть сделаны из-за отсутствия данных.

Клавиши	Перемещение
<Home>	В начало текущей строки
<Ctrl+Home>	В ячейку A1
<Ctrl+End>	В последнюю заполненную ячейку таблицы
< >	На одну ячейку вверх
<¯>	На одну ячейку вниз
<®>	На одну ячейку вправо
< >	На одну ячейку влево
<Ctrl+ >	Вверх к первой заполненной ячейке
<Ctrl+¯>	Вниз к первой заполненной ячейке
<Ctrl+®>	Вправо к первой заполненной ячейке
<Ctrl+ >	Влево к первой заполненной ячейке
<Page Up>	Вверх на один экран
<Page Down>	Вниз на один экран
<Alt+Page Up>	Влево на один экран
<Alt+Page Down>	Вправо на один экран
<Ctrl+Page Up>	К предыдущему листу рабочей книги
<Ctrl+Page Down>	К следующему листу рабочей книги

Содержимое ячейки на любом этапе можно изменить, заменив его другим значением или подправивши лишь часть содержимого.

ü Для замены содержимого ячейки выберите нужную ячейку и введите новое значение.

ü Для частичного изменения содержимого ячейки можно, выбрав ее, нажать клавишу < F2 >, или щелкнуть мышью в строке формул, или дважды щелкнуть непосредственно на ячейке.

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

Относительные ссылки используются в Excel по умолчанию при задаче ссылки на клеточку или диапазон методом указания. Относительная ссылка воспринимается программой как указание маршрута (направления движения и расстояния) к адресованной клеточке от клеточки, которая содержит формулу.

Абсолютная ссылка задает абсолютные координаты клеточки в рабочем листе (относительно левого верхнего угла таблицы). Можно приказать Excel интерпретировать номера строки и (или) столбца как абсолютные путем введения символа доллара ($) перед именами строки и (или) столбца. Например, $A$7. При перемещении или копировании формулы абсолютную ссылку на клеточку (или диапазон клеточек) измененное не будет, и на новом месте скопированная формула будет ссылаться на ту же именно клеточку (диапазон клеточек).

Нажатие <F4>	Адрес	Ссылка
Один раз	$A$7	Абсолютная ссылка
Два раза	A$7	Абсолютная ссылка на строку
Трижды	$A7	Абсолютная ссылка на столбец
Четыре раза	A7	Относительная ссылка

Учебное издание

Давидчук Надежда Николаевна - к.э.н., доцент

ИНФОРМАЦИОННЫЕ СИСТЕМЫ И ТЕХНОЛОГИИ

Методические указания