Министерство образования Нижегородской области

Государственное бюджетное профессиональное

образовательное учреждение

«Нижегородский Губернский колледж»

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

По теме:«Компьютерное моделирование средствами

MS Excel»

Автор разработки - преподаватель ГБПОУ НГК Васильева Т.Н.

г. Н-Новгород

2018

1

Содержание

Пояснительная записка………………………………………………..

2

Практическая работа № 1.Оптимальное распределение сыпучих материалов

по видам тары средствами Ms Excel……………….…………………..………..4

Практическая работа № 2.Оптимальное распределение ресурсов предприя-

тия средствами Ms Excel……………………………………….…………..……7

Практическая работа № 3.Решение транспортной задачив Ms Excel «Достав-

ка товаров со складов в магазины с минимальными транспортными расхода-

ми………………………………………………………………………….……...9

Литература……………………………………………………………….…..…..13

Заключение………………………………………………………………………13

Пояснительная записка

Данное пособие по дисциплине «Информационные технологии» содержит

методические указания по выполнению практических работ по теме «Компью-

терное моделирование средствами MS Excel» и предназначено для получения

обучающимися практических навыков решения задач линейного программирова-

ния на оптимизацию (минимизацию) в MS Excel.

Данные методические указания для выполнения практических работ

со-

ставлены в соответствии с рабочей программой учебной дисциплины «Инфор-

мационные технологии» с учетом требований ФГОС по специальности 09.02.03.

«Программирование в компьютерных системах».

Пособие предназначено для студентов, обучающихся на дневном отделении

колледжа по специальности 09.02.03. «Программирование в компьютерных си-

стемах», и составлены в соответствии с рабочей программой учебной дисци-

плины «Информационные технологии» с учетом требований ФГОС по специ-

альности 09.02.03. «Программирование в компьютерных системах».

Практические работы, представленные в данном пособии,

разработаны с

учетом требований к результатам освоения учебной дисциплины, а именно: в

результате освоения учебной дисциплины обучающийся должен уметь обраба-

тывать экономическую и статистическую информацию, используя средства па-

кета прикладных программ. Каждая из трех практических работ методического

пособия рассчитана на 2 часа.

2

Такие

задачи,

как

оптимизация

распределения

ресурсов,

минимизация

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

мирования, описываются системами линейных уравнений, для решения которых

можно использовать инструмент MS Excel Поиск решения из меню Сервис.

При выполнении практических работ обучающиеся должны:

уметь:



делать постановку задач для дальнейшей реализации их средствами ли-

нейного программирования в MSExcel;



использовать компьютерные технологии при моделировании и опти-

мизации производственных задач

с использованием электронных

таблиц

MSExcel;



создавать систему уравнений для выбранной модели и находить опти-

мальное решение, используя такой инструмент

MSExcel, как «Поиск реше-

ния» из меню «Сервис»;

знать:



назначение оптимальных моделей различных процессов;



возможности и интерфейс MS Excel при реализации задач моделирова-

ния и оптимизации различных процессов;



возможности и особенности линейного программирования для реше-

ния экономических, производственных задач.

3

Практическая работа № 1

«Оптимальное распределение сыпучих материалов по видам

тары средствами Ms Excel»

Цель задачи: научиться строить оптимальную модель распределения сыпу-

чих материалов на складе по различным видам тары для получения оптималь-

ной прибыли, используя инструмент

MS Excel Поиск решения из меню Сер-

вис и средства линейного программирования.

Технические и программные средства: компьютер

класса IBMPC, таблич-

ный процессор MS Excel.

Содержание задачи:

Требуется расфасовать 1400кг сыпучего материала по контейнерам (каждый

вмещает по 270кг), бочкам (каждая вмещает по 130 кг) и канистрам (каждая вме-

щает по 90кг). Определить, сколько контейнеров, бочек и канистр потребуется

для расфасовки всего сыпучего материала.

Методические указания по выполнению практической работы

1.

Составьте таблицу и заполните её исходными данными (см.рис. 1).

Рис.1

2.

В ячейку $B$4 запишите выражение для расчета объёма реально расфа-

сованного материала по тарам

=$B$2*SBS3+SCS2*SCS3+SDS2*SDS3 (1)

Выражение (1) представляет собой сумму произведений вместительности

данного типа емкости на количество емкостей данного типа (рис. 1).

3 . В ячейку $B$6 записываем целевую функцию, выраженную как разность

между исходным

количеством сыпучего материала (ячейка $B$5) и реально

расфасованным количеством (ячейка $B$4):

=$B$5-$B$4 (2)

В соответствии с поставленной задачей именно эту разность необходимо све-

сти к минимуму.

4

4. В меню СЕРВИС выберете ПОИСК РЕШЕНИЯ. Если указанной инстру-

мент отсутствует, то в меню СЕРВИС выберите пункт НАДСТРОЙКИ (рис.2) и

в диалоговом окне установите флажок Поиск решения.

Рис. 2

5. В диалоговом окне ПОИСК РЕШЕНИЯ (рис. 3) выполните следующие

действия:



в поле Установить целевую ячейку запишите $B$6



в

группе

переключателей Равной: установите

переключатель мини-

мальному значению;



в поле Изменяя ячейки укажите диапазон $B$3:$D$3;



в

п о л е Ограничения задайте

искомые

значения

как

целые

$B$3:$D$3=>0 (количество контейнеров, бочек и канистр не может выражаться

дробным и отрицательным числом); исходное количество сыпучего материала

( ячейка $B$5) не может быть меньше расфасованного (ячейка $B$4), поэтому

запишите ещё одно ограничение $B$4<=$B$5. Задача подготовлена к поиску ре-

шения.

Рис.3

5

6. Щелкните левой кнопкой мыши по кнопке Выполнить. В ячейках B3:D3,

B4 и B6 будут представлены результаты расчета (рис .4). Обратите внимание, что

сыпучий материал расфасован без остатка, так как значение целевой функции в

ячейке $B$6 равно нулю. Найденное решение оптимально.

Рис.4

6

Практическая работа № 2

«Оптимальное распределение ресурсовпредприятия

средствами MsExcel»

Цель задачи: научиться строить оптимальную модель производства това-

ров А и Б для получения оптимальной прибыли, используя инструмент MSExcel-

Поиск решения из меню Сервис и средства линейного программирования

Технические

и

программные

средства: компьютер

класса IBMPC, та-

бличный процессорMSExcel.

Содержание

задачи:перед

вами

стоит

задача

распределения

ресурсов.

Предприятие производит два типа изделий: изделие А и изделие Б. На произ-

водство изделия А расходуется 4 единицы условного сырья, на изделие Б расхо-

дуется 7 единиц условного сырья. На одну рабочую смену предприятие снабжа-

ется 22 единицами условного сырья.

Для изготовления изделия А требуется 8 рабочих, а для изготовления изде-

лия Б требуется 5 рабочих. Общее количество рабочих на предприятии состав-

ляет 30 человек.

Транспортные расходы на перевозку изделия А составляют 3 условные

единицы, на перевозку изделия Б составляют 4 условные единицы. Общие транс-

портные расходы в течение рабочего дня не должны превышать 20 условных еди-

ниц.

Прибыль от реализации одного экземпляра продукта А составляет 7 денеж-

ных единиц, прибыль от реализации одного экземпляра продукта Б составляет 6

денежных единиц.

С учетом заданных ограничений на ресурсы вам надо рассчитать опти-

мальные количества изделия А и изделия Б, производимых за одну рабочую сме-

ну, для получения предприятием максимальной прибыли.

Методические указания по выполнению практической работы

1.

Создайте таблицу с исходными данными (РИС.1).

2.

Составьте выражение для целевой функции и запишите его в ячейку В15

=В9*В13+С9*С13. (1)

Здесь количество каждого из изделий умножается на прибыль от реализации од-

ного экземпляра изделия.

3. Сформулируйте ограничения. Реальные затраты не должны превышать лими-

ты на ресурсы. Запишите в ячейки диапазона Е3:Е5 расчетные формулы для рас-

хода ресурсов соответственно по сырью, людям и транспорту.

=В3*В9+С3*С9 (2)

=В4*В9+С4*С9 (3)

= В5*В9+С5*С9 (4)

7

Рис.1

4. В меню СЕРВИС выберите ПОИСК РЕШЕНИЯ (рис.2). В качестве целевой

укажите ячейку $B$15 и поставьте переключатель РАВНОЙ на максимальному

значению. В поле изменяя ячейки запишите ячейки $B$9:$C$9, в коорых будут

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

Рис.2

В поле ограничения к ограничениям (2, 3, 4) следует добавить требова-

ния на неотрицательность и целочисленность результата (так как количество из-

делий не может быть отрицательным и нецелым):

$B$9:$C$9=целое(5) $B$9:C$9>=0 (6)

5.

Щелкните по кнопке ВЫПОЛНИТЬ. Результаты приведены на рис.3

Рис.3

8

Практическая работа № 3

Решение транспортной задачив Ms Excel «Доставка то-

варов со складов в магазины с минимальными транс-

портными расходами»

Цель задачи: научиться строить оптимальные модели транспортировки

товаров со складов в магазины, используя инструмент

MS Excel Поиск ре-

шения из меню Сервис и средства линейного программирования

Технические и программные средства: компьютер класса IBMPC, та-

бличный процессорMSExcel.

Содержание задачи:

Рассмотрим производственную модель при следующих условиях: имеются

три склада с товарами и два магазина. Количество товаров на каждом из складов

80, 60 и 120 тонн соответственно. Количество товара, которое требуется доста-

вить в каждый из магазинов, 170 и 100 тонн. Расценки (стоимость перевозок в

руб.) на транспортировку товара для каждой пары склад-магазин представлены

на рис.1.

Определить количество товара, доставляемое с каждого из складов в магази-

ны, при условии минимальных расходов на транспортировку.

Методические указания по выполнению практической работы

1.

Создайте таблицу и заполните ее, как на рис 1.

Рис. 1

В

ячейки С3:С5

и Е3:Е5 внесите значения стоимостей перевозок для

каждого магазина.

В

ячейку

$C$7

(рис.1)

запишите

выражение

для линейнойцелевой

функции:

=С3*D3 + C4*D4 + C5*D5 + E3*F3 + E4*F4 + E5*F5 (1)

2.

Выражение для целевой функции (1) представляет собой сумму транс-

портных затрат, где стоимость перевозки для каждой пары склад-магазин

умножается на количество доставляемого товара. Это именно те затраты, кото-

рые по условию задачи требуется свести к минимуму.

9

3.

Для успешного решения транспортной задачи крайне важно

корректно задать систему ограничений. Рассматриваемый пример транспорт-

ной задачи относится к сбалансированной задаче, где суммарное количество

товара на трех складах равно количеству товара, запрашиваемому двумя мага-

зинами.

В данном случае суммарное количество равно 270 тонн. То есть при лю-

бых комбинациях маршрутов перевозок по схеме склад-магазин со всех трех

складов товар должен быть вывезен полностью и доставлен в два магазина.

Отсюда следует первая группа ограничений. В таблице на рис. 1 в ячейку

$B$3 запишите следующую формулу (см. рис.2 ниже):

=$D$3 + $F$3

(2)

Аналогично запишите в ячейку $B$4

=$D$4 + $F$4

(3)

В ячейку $B$5

=$D$5 + $F$5(4)

В ячейку $B$6 запишите выражение для суммы товара на трех складах:

=$B$3 + $B$4 + $B$5(5)

Рис 2

Каждое из выражений (2-4) задает ограничения на равенство суммар-

ного количества товаров, доставленных в оба магазина, количеству товара, со-

держащемуся на каждом из складов. Ограничение (5) контролирует суммарное

количество товаров на трех складах.

5. Вторая группа ограничений составляется для каждого из магазинов. В

ячейку $D$6 для магазина 1 (рис. 1) запишите

= $D$3 + $D$4 + $D$5

(6)

В ячейку $F$6 для магазина 2 запишите:

= $F$3 + $F$4 + $F$5 (7)

Эти выражения говорят о том, что суммарное количество товара, достав-

ленное в каждый из магазинов со всех трех складов, должно быть ограничено

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

6.

Для решения задачи воспользуемся инструментом ПОИСК РЕШЕ-

НИЯ из меню СЕРВИС, диалоговое окно которого приведено на рис. 3

10

Рис. 3

7. В поле

УСТАНОВИТЬ ЦЕЛЕВУЮ ЯЧЕЙКУ запишите адрес $C$7, а

в

группе

переключателей РАВНОЙ:выберите

переключатель минимальному

значению.

8. В список ОГРАНИЧЕНИЯ внесите числовые значения (из условий за-

дачи)

$B$3=80 (8)

$B$4=70 (9)

$B$5=120 (10)

$B$6=270 (11)

$D$6=170 (12)

$F$6=100 (13)

В список ОГРАНИЧЕНИЯ добавьте требование на неотрицательность ре-

зультата (количество товара не может выражаться отрицательным числом) :

$D$3:$D$5>=0

$F$3: $F$5>=0

Обратите внимание. Что выражения (2-7) и (8-13) ссылаются на одни и те же

ячейки, задавая тем самым управляющие ограничения вычислительному процес-

су. По сути, это просто разделенные на две части выражения:

$D$3 + $F$3=80

$D$4 + $F$4=70

$D$5 + $F$5=120

$B$3 + $B$4 + $B$5=270

$D$3 + $D$4 + $D$5=170

$F$3 + $F$4 + $F$5=100

9. В поле ИЗМЕНЯЯ ЯЧЕЙКИвведите $D$3:$D$ 5 и $F$3:$F$5, отведен-

ные на рис. 1 под искомые значения доставленного товара в каждый из магази-

нов. При записи двух диапазонов их следует разделять точкой с запятой, напри-

мер $D$3:$D$5;$F$3:$F$5.

10. Щелкните по кнопке ВЫПОЛНИТЬ и проанализируйте полученное ре-

шение (рис. 4). Как видно, с учетом всех предусмотренных ограничений мини-

мальная стоимость перевозки составляет 196500 руб.

11

Рис 4

12

Литература

1. Михеева Е.В., Практикум по информационным технологиям в профессио-

нальной деятельности:учебное пособие для студ. учреждений сред.проф. об-

разования/Михеева Е.В.–10-е изд., - М.: Издательский центр Академия, 2014,

256 с.

2. Михеева Е.В., Практикум по информатике, учебное пособие для студ. учре-

ждений сред.проф. образования/ Михеева Е.В.- М.:Издательский центр Ака-

демия, 2012, 208 с.

3. Зеньковский В.А., Применение Excel в экономических и инженерных расче-

тах/ Зеньковский В.А. - М.: СОЛОН-Пресс, 2014 – 192 с.

Заключение

Данное методическое пособие, составленное в соответствии с рабочей про-

граммой

по

дисциплине«Информационные

технологии»,

дает

возможность

обучающимся сформировать умения и навыки по компьютерному моделирова-

нию средствами MSExcel:

Представленные в пособии практические работы выполняются в соответ-

ствии с тематическим планом рабочей программы учебной дисциплины «Инфор-

мационные технологии» по теме 2.4. «Технология обработки числовой инфор-

мации - экономической, статистической» с учетом требований

к результатам

освоения учебной дисциплины, а именно: в результате освоения учебной дисци-

плины обучающийся должен уметь:

- обрабатывать

экономическую

и

статистическую

информацию,

используя

средства пакета прикладных программ MS Excel;

- использовать компьютерные технологии при моделировании и оптимизации

производственных задач с использованием инструмента MS Excel«Поиск

решений».

Задачи на оптимизацию (минимизацию) имеют довольно высокий уровень

сложности, поэтому требует от учащихся большого внимания при изучении

методических указаний, хороших навыков работы в Ms Excel, а также понима-

ния важности знания методов и средств реализации компьютерного моделиро-

вания при решении задач линейного программирования.

Знакомство с таким классом задач дает студентам возможность оценить

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

осмысление круга вопросов, которые предстоит решать будущим программи-

стам в дальнейшей профессиональной деятельности.

13