Подготовка ЭТ

Для решения линейных оптимизационных задач

Применение процедуры Поиск решения

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

Постановка задачи оптимизации

Производится выпуск колбас двух типов – Краковской (переменная х1) и Московской (переменная х2), с определенной прибылью за 1кг. На каждый тип колбасы требуется необходимое количество основного сырья 1-го и 2-го сорта. Для выпуска 1кг. Краковской колбасы требуется 0,4 кг сырья 1-го сорта и 0,6кг. 2-го сорта. Соответственно для Московской 0,8кг. и 0,2кг. При этом запас основного сырья ограничен, а также наложены ограничения на выпуск колбас обеих типов. Все это представим таблицей 18.2:

Таблица 18.2 – данные для производства колбас

Основное сырье	Краковская (х1)	Московская (х2)	Запасы сырья (кг)
1-го сорта	0,4	0,8
2-го сорта	0,6	0,2
Прибыль гр/ кг.
Выпустить не меньше	600кг.	200кг.
Выпустить не больше	1000кг.	800кг.

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

Математическая формулировка задачи.

Запишем формулу = х1*2 +x2*4 в целевой ячейке, в которой нужно получить максимальное значение.

Запишем ограничения по сырью:

0,4*x1+0,8*x2<= 2000

0,6*x1+0,2*x2<= 1500

Запишем ограничения по выпуску продукции:

x1 >= 600 (не меньше)

x2 >= 200 (не меньше)

х1 <= 1000 (не больше)

x2 <= 800 (не больше)

Расположим данные в ЭТ (рис. 18.4). Режим листа – отображение формул.

В ячейках B4; С4 записываем нули (начальные значения х1 и х2).

Целевую формулу = 2*B4 + 4*C4 запишем в ячейку В5.

Ограничения по сырью располагаем в диапазоне В8:С9.

Ограничения по выпуску колбас располагаем в диапазоне В12:С15.

Подключить (если не был подключен) пакет «Поиск решения» командами: Сервис, Надстройки и установить флажок Поиск решения, ОК.

Рис. 18.4. Расположение данных в ЭТ  

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

Выполнить команды: Сервис, Поиск решения. В появившемся окне заполнить данные (рис. 18.6):

· Установить целевую ячейку: B5 р авной максимальному значению

· Изменяя ячейки: B4:С4

· В поле Ограничения необходимо ввести ограничения на сырье

B8:B9<=C8:C9

Для этого:

1. Щелкнуть кнопку Добавить. Появляется окно Добавление ограничения

2. В поле Ссылка на ячейку щелкнуть кнопку снятия окна и мышкой выделить ячейки В8:В9. Вернуться к окну Добавить.

3. Выбрать знаки £

4. В поле ограничения снять окно, мышкой выделить ячейки С8:С9 и вернуться к окну с названием Добавить ( рис. 18.5).

	Рис. 18.5. Ввод ограничений на сырье

5. Аналогично нужно выполнить ввод ограничений по выпуску продукции В12:В13³С12:С13

В14:В15£С14:С15

6. После ввода всех ограничений щелкнуть кнопку ОК.

Рис. 18.6. Окно Поиск решения  

В окне Поиск решения щелкнуть кнопку Выполнить (рис. 18.6).

Щелкнуть ОК в появившемся окне Результаты поиска решения.

Перевести ЭТ в режим отображения численных значений формул (команды - Сервис, Параметры, Вкладка Вид, снять флажок Формулы).

Результаты решения в ячейках ЭТ:

В4 - выпуск Краковской колбасы (х1)=1000кг.

С4 - выпуск Московской колбасы (х2)= 800кг.

В5 - максимальная величина прибыли= 5200гр.

Список литературы

1. Інформатика і комп”ютерна техніка. Войтюшенко Н.М., Останець А.І. - Київ, ЦУЛ, 2009.

2. Інформатика: теоретичні основи і практикум. Литвин І.І., Конончук О.М. - Київ, «Новий світ», 2007.

3. Інформатика. Комп’ютерна техніка. Комп’ютерні технології. Баженов В.А., Венгерський П.С., Горлач В.М. та інш. - Київ, «Каравела», 2003.

Дополнительная литература

1. Глушаков С.В. и др. Программирование на Visual Basic 6.0. Учебный курс – Харьков: Издательство ² Фолио ², 2002.

2. Коцюбинский А.О., Грошев С.В. Современный самоучитель профессиональной работы на компьютере. – 3-е изд. — М.: Триумф, 1999

3. Інформатика, Комп’ютерна техніка, Комп’ютерні технології. За редакцією О.І. Пушкаря. – Київ, "Академія", 2001.

4. Дібкова Л.М. Інформатика та комп’ютерна техніка. Посібник для студентів вищих навчальних закладів.– Київ: Видавничий центр "Академія", 2002.

5. Вильямс Орвис. Excel для ученых, инженеров и студентов. Пер. с англ. –К.: Юниор, 1999.

6. Гарнаев А. Использование MS Excel и VBA в экономике и финансах. - СПб.: БХВ- Санк-Петербург, 1999.

Содержание

Глава 1. Основные понятия информатики и компьютерной техники

1.1. Информация и информатика………………………………………….. 4

1.2. Устройства, характеристики и программное обеспечение ПК.…….. 5

1.3. Файловая система организация данных………………………………. 5

1.4. Операционная система Windows……………………………………… 6

1.5. Пользовательский графический интерфейс…………………………… 7

Глава 2. Обзор ресурсов и управление в Windows

2.1. Главное меню рабочего стола и справочная система…………………. 8

2.2. Программа Проводник для работы с папками и файлами……………. 9

2.3. Работа с папками в окне Проводник…………………………………... 9

2.4. Работа с файлами в окне Проводник…………………………………... 11

Глава 3. Текстовой редактор Word

3.1. Создание и сохранение документа……………………………………. 11

3.2. Форматирование страниц документа…………………………………. 13

3.3. Редактирование текста страницы…………………………………….. 14

3.4. Таблицы в Word……………………………………………………….. 15

Глава 4. Формульный редактор и графика в Word

4.1. Формульный редактор………………………………………………… 16

4.2. Работа с рисунками …………………………………………………… 18

4.3. Импорт графических объектов и команда Объект WordArt……….. 19

Глава 5. Табличный процессор MS Excel

5.1. Рабочие книги, листы и окно Excel……………………………………. 20

5.2. Некоторые операции над рабочей книгой и листами………………… 21

5.3. Типы данных в Excel и их ввод………………………………………. 22

5.4. Арифметические и логические выражения в Excel………………….. 23

Глава 6. Вычисления в Excel

6.1. Абсолютные, относительные и смешанные адреса………………….. 24

6.2. Стандартные функции…………………………………………………. 25

6.3. Логические функции ЕСЛИ в Excel…………………………………... 27

Глава 7. Диаграммы в Excel и решение математических задач

7.1. Мастер диаграмм………………………………………………………. 29

7.2. Построение диаграммы типа Поверхность…………………………… 29

7.3. Редактирование построенной диаграммы………………………….. 30

7.4. Решение нелинейных уравнений в Excel…………………………….. 31

7.5. Вычисления по итерационным формулам……………………………. 32

Глава 8. Обработка статистических данных в Excel

8.1. Некоторые понятия математической статистики……………………. 33

8.2. Генерация случайных чисел и построение гистограмм…………….. 34

8.3. Описательная статистика………………………………………………. 35

Глава 9. Работа с базами данных в Excel

9.1. Основные понятия и правила создания БД…………………………… 37

9.2. Применение команды Автофильтр……………………………………. 38

9.3. Применение команды Расширенный фильтр………………………… 38

Глава 10. Алгоритмизация вычислительных процессов

10.1. Алгоритм и его свойства ………………………………………………. 4

10.2. Линейные и разветвляющиеся алгоритмы ………………………….... 6

10.3. Простые циклические алгоритмы …………………………………….. 6

10.4. Циклические алгоритмы обработки массивов ……………………….. 8

Глава 11. Система программирования VBA

11.1. Основные положения ………………………………………………….. 11

11.2. Некоторые определения ……………………………………………….. 12

11.3. Запуск и элементы окна редактора VBA …………………………….. 13

Глава 12. Пример создания проекта в VBA и типы данных

12.1. Порядок действий на ПК при создании проекта ……………………. 15

12.2. Элементы языка VBA ………………………………………………….. 17

12.3. Описание переменных …………………………………………………. 18

Глава 13. Выражения в VBA и ввод – вывод данных

13.1. Выражения в VBA ………………………………………………........... 19

13.2. Операторы присваивания и способы ввода данных ………………… 20

13.3. Способы вывода данных ………............................................................. 21

Глава 14. Организация разветвляющихся программ

14.1. Операторы управления GOTO, IF ………………………………........ 22

14.2. Однострочный вложенный If …………………..................................... 24

14.3. Оператор выбора Select Case ………………………………………...... 25

Глава 15. Организация циклических программ в VBA

15.1. Простые циклические программы с оператором IF ………………… 26

15.2. Оператор цикла For... Next ……………………................................... 27

15.3. Операторы циклов типа Do ……………….……….............................. 28

Глава 16. Массивы данных в VBА

16.1. Характеристики и описание одномерных массивов ………….…….. 29

16.2. Примеры обработки одномерных массивов ……………………….... 30

16.3. Двумерный массив …………………………......................................... 32

Глава 17. Пользовательские процедуры типа Sub и Function в VBA

17.1. Подпрограммы ……………………........................................................ 34

17.2. Процедура типа Sub ……………............................................................ 34

17.3. Подпрограмма - процедура Function ………………………………… 36

Глава 18. Решение оптимизационных задач

18.1. Обработка электронных таблиц в среде VBA ……………………….. 37

18.2. Применение процедуры Поиск решения………………………… ….. 40

18. 3. Подготовка ЭТ…………………………………………………………. 41

Список литературы…………………………………………………………... 75

Дата добавления: 2014-01-13