Указания к выполнению контрольной работы

Цель работы: Получение практических навыков автоматизации задач анализа и оптимального планирования ресурсов.

Выбор варианта:

Номер варианта выбирается по коду М, где М – последняя цифра номера зачетной книжки студента

Задача 1. Работа с однотабличной базой данных.

Определить стоимость, название и характеристики ноутбука типа М средней ценовой категории по данному типу (имеющего цену, ближайшую к среднему арифметическому значению цены по данному типу ноутбуков). База данных находится на листе ПРАЙС рабочей книги MS Excel БД.xls. Тип ноутбука выбрать по таблице 1.

Для решения задачи используются функции MS Excel категории Работа с базой данных: ДСРЗНАЧ, ДМИН, БИЗВЛЕЧЬ. Функции данной категории включают три аргумента:

· База_данных — это интервал ячеек, формирующих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Верхняя строка списка содержит названия всех столбцов.

· Поле определяет столбец, по которому функция производит операцию, заданную именем функции. Аргумент «поле» может быть задан как текст с названием столбца в двойных кавычках или как число, задающее положение столбца в списке: 1 — для первого поля, 2 — для второго поля и так далее; или как ссылка на ячейку, которая содержит наименование столбца.

· Критерий — это интервал ячеек, который содержит задаваемые условия. Интервал создается вне базы данных и содержит по крайней мере одно название столбца и по крайней мере одну ячейку под названием столбца с условием.

 

Таблица 1 - Выбор типа объектов по заданию 1

Вариант М	Тип ноутбука	Вариант М	Тип ноутбука
0	Fujitsu-Siemens	5	Asus
1	Apple	6	Sony
2	Dell	7	Samsung
3	Acer	8	Toshiba
4	HP	9	BenQ

Последовательность решения:

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

I. Открыть файл БД.xls.

II. Определить стоимость, название и характеристики ноутбука типа MSI средней ценовой категории по данному типу (имеющего цену, ближайшую к среднему арифметическому значению цены по MSI).

1. Определить абсолютное отклонение цен ноутбуков от средней арифметической цены по MSI:

1.1. Для выполнения задания необходимо создать диапазон критериев для выбора из базы данных (диапазон ячеек В1:В2 на рабочем листе ПРАЙС). Первая строка критериев содержит имя поля критерия - ТИП, вторая – значение, по которому идет выбор - MSI (рисунок 1).

1.2. В столбце Е на рабочем листе ПРАЙС определить абсолютное значение разности цены и средней цены для MSI (поле Цена-Среднее на рис. 1). Для этого в ячейку Е11 ввести формулу:

=ABS(D11-ДСРЗНАЧ($A$10:$D$553;$D$10;$B$1:$B$2)),

которую с помощью маркера автозаполнения скопировать в диапазон Е12:Е553.

 

Рис. 1

 

1.3. Определить для ноутбуков MSI минимальное отклонение от средней арифметической цены MSI (минимум по полю Цена-Среднее). В ячейку D2 ввести формулу (рисунок 2):

=ДМИН(A10:E553;E10;B1:B2)

 

Рис. 2

 

Определить название, цену и описание ноутбука средней ценовой категории с помощью функции БИЗВЛЕЧЬ. Описание ноутбука находится в скрытом столбце С (третий столбец базы данных). В ячейки А2, А4 и А6 ввести формулы для извлечения (рисунок 3).

 

Рис. 3

Результаты формул приведены на рисунке 4.

Рис. 4

Задача 2. Определение оптимального плана поставки товара на оптовые базы при минимальных суммарных транспортных издержках.

На пять оптовых баз поставляется продукция от четырех представителей. Ежемесячное предложение продукции представителями [шт.], емкость оптовых баз [шт.] и тарифы [руб./шт.] за доставку продукции от представителей к оптовым базам приведены в таблице 2. Решить задачу определения оптимального плана поставки данного товара на оптовые базы так, чтобы суммарные транспортные издержки были минимальны.

Задача относится к классу транспортных задач линейного программирования.

Исходные параметры модели

1)  n = 4 - количество предприятий-представителей.

2)  m = 5 – количество потребителей – оптовых баз.

3) – ежемесячные объемы предложения продукции  () [шт.].

4) – спрос на продукцию (емкость оптовых баз)  () [шт.].

5) – затраты на производство и доставку продукции от представителей на оптовые базы потребителя [руб./шт.].

6) (суммарное предложение больше суммарного спроса)

или (суммарное предложение меньше суммарного спроса)

или (суммарное предложение равно суммарному спросу, сбалансированная модель)

 

Искомые параметры модели

1) – количество продукции, перевозимой из пункта отправления  в пункт назначения  [шт.].

2) – целевая функция - транспортные расходы на перевозку всей продукции [руб.].

Экономико-математическая модель

(4.1)

 

Таблица 2 – Параметры перевозок от представителей к оптовым базам

Тариф, руб./шт.			Оптовая база1	Оптовая база2	Оптовая база3	Оптовая база4	Оптовая база5	Предложение, шт.
Представитель 1	Вариант М	0	10	2	2	12	1	510
		1	26	37	12	45	10	200
		2	11	28	6	10	18	550
		3	25	8	12	17	5	720
		4	24	14	27	40	48	200
		5	16	24	14	30	42	420
		6	14	10	6	20	14	510
		7	24	39	14	35	42	200
		8	20	22	34	16	14	550
		9	40	25	32	38	30	720
Представитель 2	Вариант М	0	24	8	18	30	20	400
		1	10	12	50	58	8	800
		2	32	16	45	34	10	250
		3	26	35	42	52	35	480
		4	16	20	30	38	26	900
		5	20	12	48	44	30	420
		6	35	14	40	26	30	400
		7	58	20	58	48	26	800
		8	16	32	8	25	16	250
		9	30	30	22	38	20	480
Представитель 3	Вариант М	0	32	28	54	40	16	460
		1	10	30	60	30	20	650
		2	8	24	25	21	52	800
		3	15	40	38	28	25	160
		4	18	37	16	32	40	360
		5	26	34	20	46	45	480
		6	28	28	24	10	20	460
		7	35	38	50	44	28	650
		8	42	50	48	48	22	800
		9	10	20	15	12	10	160
Представитель 4	Вариант М	0	16	41	30	17	55	790
		1	24	30	24	35	23	510
		2	30	25	37	20	30	560
		3	16	20	18	33	48	800
		4	22	36	10	42	36	700
		5	28	40	40	25	18	400
		6	45	45	50	46	30	790
		7	28	38	30	30	25	510
		8	32	35	28	25	9	560
		9	50	48	52	50	20	800
Емкость базы, шт.			600	480	550	750	420

 

 

Пример решения:

По заказу трех потребителей А, Б, В на четырех предприятиях-изготовителях производится продукция. Ежемесячный спрос на продукцию [шт.], ежемесячные объемы производства [шт.] и затраты на производство и доставку продукции от изготовителей к потребителям [руб./шт.] приведены в табл.3. Необходимо выбрать оптимальный способ организации снабжения потребителей продукцией предприятий-изготовителей.

Таблица 3

Затраты, руб/шт	потребитель А	потребитель Б	потребитель В	Объем производства, шт
предприятие 1	27	10	31	510
предприятие 2	15	21	25	620
предприятие 3	11	22	27	660
предприятие 4	26	20	20	420
спрос, шт	300	420	730

1.  Описываем экономико-математическую модель транспортной задачи

Исходные параметры модели

1) n = 4 - количество предприятий-изготовителей, m = 3 – количество потребителей.

2) – ежемесячные объемы производства предприятия  () [шт.].

3) – спрос на продукцию потребителя  () [шт.].

4) – затраты на производство и доставку продукции от изготовителей к потребителям [руб./шт.].

5)

Искомые параметры модели

1) – количество продукции, перевозимой из пункта отправления  в пункт назначения  [шт.].

2) – транспортные расходы на перевозку всей продукции [руб.].

Экономико-математическая модель

Целевая функция представляет собой общие транспортные расходы на осуществление всех перевозок в целом. Первая группа ограничений указывает, что суммарный объем перевозок продукции из любого пункта отправления должен быть не больше запаса продукции в этом пункте. (Суммарные запасы превышают суммарные потребности на величину (510+620+660+420)-(300+420+730)=760 шт.) Вторая группа ограничений указывает, что суммарные перевозки продукции в некоторый пункт потребления должны полностью удовлетворить спрос на продукцию в этом пункте.

1. Создаем экранную форму, вводим условия задачи, исходные данные и зависимости

Экранные формы, задание переменных, целевой функции, ограничений и граничных условий задачи и ее решение представлены на рис.5, табл. 4, рис. 6 - 12.

 

Рис. 5 Экранная форма

 

Таблица 4 - Формулы экранной формы задачи

Объект математической модели	Выражение в Excel
Формула в целевой ячейке F15	=СУММПРОИЗВ(C3:E6;C12:E15)
Ограничения по строкам в ячейках F3, F4, F5, F6	=СУММ(C3:E3) =СУММ(C4:E4) =СУММ(C5:E5) =СУММ(C6:E6)
Ограничения по столбцам в ячейках С7, D7, E7	=СУММ(C3:C6) =СУММ(D3:D6) =СУММ(E3:E6)
Суммарные запасы и потребности в ячейках H8, G9	=СУММ(H3:H6) =СУММ(C9:E9)
Проверка баланса в ячейке Н9	=ЕСЛИ(G9=H8;"БАЛАНС";ЕСЛИ(G9>H8;"ПОТРЕБНОСТИ БОЛЬШЕ";"ЗАПАСЫ БОЛЬШЕ"))

1. Решаем задачу с помощью инструмента "Поиск решения", которое вызывается из меню "Сервис" в MS Excel 2003 (в MS Excel 2007: кнопка Office – параметры Excel – Надстройки – Подключить надстройку Поиск решения).

3.1. Установить курсор в поле "Установить целевую ячейку";

3.2. Ввести адрес целевой ячейки $F$15 или сделать одно нажатие левой клавиши мыши на целевую ячейку в экранной форме ¾ это будет равносильно вводу адреса с клавиатуры;

3.3. Ввести направление оптимизации ЦФ, щелкнув один раз левой клавишей мыши по селекторной кнопке "минимальному значению".

3.4. В окне "Поиск решения" в поле "Изменяя ячейки" впишем адреса $С$3:$E$6. Необходимые адреса можно вносить в поле "Изменяя ячейки" и автоматически путем выделения мышью соответствующих ячеек переменных непосредственно в экранной форме.

3.5. Ввести ограничения модели. В нашем случае на значения переменных накладывается граничное условие неотрицательности и целочисленности.

· Нажать кнопку "Добавить", после чего появится окно "Добавление ограничения" (рис.6).

· В поле "Ссылка на ячейку" ввести адреса ячеек переменных $С$3:$E$6.

· В поле знака открыть список предлагаемых знаков и выбрать цел. В поле "Ограничение" появится слово «целое».

· Нажмите кнопку "Добавить" и введите остальные условия, после чего нажмите кнопку «OK» (рис.7).

Рис.6. Ввод условия целочисленности переменных задачи

Рис. 7 Окно «Поиск решения»

3.6. Нажать кнопку "Параметры" и заполнить некоторые поля окна "Параметры поиска решения" (рис.8).

Рис.8. Параметры поиска решения, подходящие для большинства задач ЛП

Параметр "Максимальное время" служит для назначения времени (в секундах), выделяемого на решение задачи. В поле можно ввести время, не превышающее 32 767 секунд (более 9 часов).

Параметр "Предельное число итераций" служит для управления временем решения задачи путем ограничения числа промежуточных вычислений. В поле можно ввести количество итераций, не превышающее 32 767.

Параметр "Относительная погрешность" служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Чем меньше количество десятичных знаков во введенном числе, тем ниже точность. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.

Параметр "Допустимое отклонение" служит для задания допуска на отклонение от оптимального решения в целочисленных задачах. При указании большего допуска поиск решения заканчивается быстрее.

Параметр "Сходимость" применяется только при решении нелинейных задач.

Установка флажка "Линейная модель" обеспечивает ускорение поиска решения линейной задачи за счет применение симплекс-метода.

Подтвердим установленные параметры нажатием кнопки "OK".

3.7. Запустить задачу на решение из окна "Поиск решения" путем нажатия кнопки "Выполнить".

После запуска на решение задачи ЛП на экране появляется окно "Результаты поиска решения" с одним из сообщений, представленных на рис.9, 10 и 11.

 

Рис.9. Сообщение об успешном решении задачи

Рис.10. Сообщение при несовместной системе ограничений задачи

Рис.11. Сообщение при неограниченности ЦФ в требуемом направлении

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

Если при заполнении полей окна "Поиск решения" были допущены ошибки, не позволяющие Excel применить симплекс-метод для решения задачи или довести ее решение до конца, то после запуска задачи на решение на экран будет выдано соответствующее сообщение с указанием причины, по которой решение не найдено. Иногда слишком малое значение параметра "Относительная погрешность" не позволяет найти оптимальное решение. Для исправления этой ситуации увеличивайте погрешность поразрядно, например от 0,000001 до 0,00001 и т.д.

В окне "Результаты поиска решения" представлены названия трех типов отчетов: "Результаты", "Устойчивость", "Пределы". Они необходимы при анализе полученного решения на чувствительность. Для получения же ответа (значений переменных, ЦФ и левых частей ограничений) прямо в экранной форме просто нажать кнопку "OK". После этого в экранной форме появляется оптимальное решение задачи (рис.12).

 

Рис. 12 Экранная форма с полученным решением

Рекомендуемая литература:

1. Фрай К.Д. Microsoft Excel 2007: Русская версия – М.:ЭКОМ, 2007
2. Долженков В.А., Колесников Ю.В. Microsoft Excel 2003. – СПб.:BHV-СПб, 2006.
3. Мак-Федрис П. Формулы и функции в Microsoft Excel 2003. – М.:ВИЛЬЯМС, 2006.
4. Каплан А.В. Математика, статистика, экономика на компьютере. / Каплан А.В., Каплан В.Е., Мащенко М.В., Овечкина Е.В. – М.: ДМК Пресс, 2006.
5. Абрамян М.Э. Практикум по информатике для гуманитариев. – М.: «Дашков и К^о»; Ростов н/Д.:Академцентр, 2008.

6. Информационные системы в экономике: Практикум / кол. авторов под общ. ред. П.В. Аникина. – М.: КНОРУС, 2008.

Требования к оформлению контрольной работы:

• Задачи решить в табличном процессоре MS Excel или OpenOffice.Calk.
• Результаты и ход решения каждой задачи оформляются в виде пояснительной записки.
• Титульный лист оформляется по требованиям ННГУ.
• Пояснительная записка оформляется в MS Word и предоставляется в печатном виде.
• Весь текст оформляется единообразным стилем. Таблицы переносятся копированием из MS Excel (шрифт в таблицах допускается 10пт). Все таблицы и рисунки должны иметь соответствующую нумерацию.

· Таблицы представляются в двух вариантах – с формулами, по которым ведутся вычисления, и с результатами вычислений. (Переключение режимов отображения в MS Excel2003: Сервис => Параметры => Вид => Параметры окна => Формулы; в MS Excel2007: Кнопка «Office» => Параметры Excel => Дополнительно => Поставить флаг «Показывать формулы, а не их результат»).

· На всех страницах, исключая титульный лист, оформляются колонтитулы (в MS WORD 2003 Вид => Колонтитулы; в MS WORD 2007 Вставка – Колонтитулы – Верхний колонтитул или Нижний колонтитул.): верхний колонтитул – ФИО студента, номер группы, номер зачетной книжки; нижний колонтитул включает номер страницы и поля CreateDate и EditTime (MS Word2003: Вставка=>Поле; MS Word2007: Вставка => Экспресс-блоки).

Комплектация пояснительной записки к контрольной работе:

• Титульный лист.
• Содержание.
• Введение (включает цель работы, задачи, описание выбора варианта и исходные данные для выполнения заданий).
• Основная часть:

- Работа с однотабличной базой данных:

- База данных со значениями по полям Наименование, Тип, Цена,у.е., Цена-Среднее (три первые записи, записи со строк электронной таблицы № 1М1, 1М2, 1М3, три последние записи). Описание хода работы, включая пояснение к формулам и используемым функциям.

- Таблица с результатами решения задачи 1. Описание хода работы, включая пояснение к формулам и используемым функциям.

- Определение оптимального плана поставки товара на оптовые базы при минимальных суммарных транспортных издержках:

- Экономико-математическая модель задачи определения оптимального плана поставки товара на оптовые базы.

- Экранная форма с результатами решения задачи 2. Описание хода работы, включая пояснение к формулам, используемым функциям и инструментам электронных таблиц.

• Заключение (результаты и выводы).
• Список литературы.