Для запуска макроса можно назначать или не назначать кнопку, в любом случае он фиксируется в списке макросов. Его можно увидеть в окне Макрос, которое открывается командой Вид \ группа Макросы \ Макросы \ Макросы (рисунок 6.3).
Рисунок 6.3 – Вывод окна Макросы (справа)
Поэтому для запуска макроса достаточно выделить в окне имя нужного макроса, например График и нажать клавишу Выполнить.
Более сложные макросы создаются с помощью редактора Visual Basic.
Контрольные вопросы
1 Что называется макросом, для чего он предназначен?
2 Как записать макрос?
3 Способы запуска макроса (перечислить).
4Запись макроса, запускаемого из окна Макрос и сочетанием клавиш с клавиатуры.
5 Использование относительной и абсолютной адресации при записи макросов.
6 Как сделать макрос доступным для всех открытых книг MS Excel?
Задание
1 Разработать макрос для вычисления прогнозируемых значений функции y = 0,2 · xn, где n – номер компьютера в аудитории. Предварительно функцию нужно ввести в ячейки столбца или строки процессора Excel, задав изменение аргумента х в пределах 1...7 с шагом 1 и вычислив в соседнем столбце (или строке) по введённой формуле значения функции у. Прогноз выполнить с помощью функций ТЕНДЕНЦИЯ или РОСТ. Макрос должен запускаться из окна Макрос и быть доступен во всех открытых книгах MS Excel.
2 Разработать макрос для построения графика функции y = 2· sin (n · x), где n – номер компьютера в аудитории, для 10 значений аргумента х = 1...10. При записи макроса использовать относительную адресацию. Макрос должен запускаться сочетанием клавиш с клавиатуры и быть доступен во всех открытых книгах MS Excel.
Открыть новую книгу и выполнить в ней оба макроса.
Содержание отчёта
1Название, цель, содержание работы
2 Задание своего варианта
3 Письменные ответы на контрольные вопросы
4 Выводы по работе
На своём носителе должны быть сохранены результаты работы
Практическое занятие №7
Поиск решения в MS Excel
Цель работы: научиться использовать процессор MS Excel
для решения задач оптимизации
Содержание работы:
1 Создание формы
2 Ввод данных в окно Поиск решения
3 Задание параметров поиска и решение задачи
Общие сведения
Математический аппарат MS Excel позволяет решать задачи линейного, нелинейного и целочисленного программирования. При этом оптимизация решения выполняется методом поиска решения, который запускается командой Данные\Поиск решения… (Активируется в Параметры Excel \Надстройки) – рисунок 7.1.
Рисунок 7.1. Вкладка Данные, группа Анализ
Задача линейного программирования (ЗЛП) в общем случае формулируется следующим образом:
Определить максимум (минимум) целевой функции F max(min) при заданной системе ограничений (2) и граничных условий (3):
Fmax(min) =A1*X1+A2*X2+...+An*Xn (1)
B11*X1+B12*X2+...+B1n*Xn<=C1
B21*X1+B22*X2+...+B2n*Xn<=C2
................................................ (2)
Bn1*X1+Bn2*X2+...+Bnn*Xn<=Cn
Xi>=0, i=1...n (3)
Система уравнений (1)..(3) является математической моделью ЗЛП.
Рассмотрим, как применяется процессора MS Excel для решения ЗЛП:
Задача. МП выпускает товары Х1,Х2,Х3,Х4, получая от реализации каждого прибыль в 60,70,120,130 руб. соответственно. Затраты на производство приведены в табл. 1. Определить:
1 Максимум прибыли в зависимости от оптимального распределения затрат.
2 Минимум ресурсов, необходимых для получения максимальной прибыли.
Таблица 1
| Затраты | Х1 | Х2 | Х3 | Х4 | Всего |
| Трудовые | 1 | 1 | 1 | 1 | 16 |
| Сырьевые | 6 | 5 | 4 | 1 | 110 |
| Финансы | 4 | 6 | 10 | 13 | 100 |
Составим математическую модель процесса по описанию задачи:
60Х1+70Х2+120Х3+130Х4 = Fmax – целевая функция прибыли.
Х1+Х2+Х3+Х4 <= 16
6Х1+5Х2+4Х3+Х4 <= 110 - ограничения модели
4Х1+6Х2+10Х3+13Х4 <= 100
Хj >=0 - граничные условия модели
Отметим, что целевая функция и система ограничений математически представляют собой сумму произведений аргументов Хj на числовые коэффициенты, поэтому при вводе модели в MS Excel применяется функция СУММПРОИЗВ().
Решение задачи средствами MS Excel состоит из 3 этапов:
1 Создание формы для ввода условий задачи, ввод в неё исходных данных и зависимостей из математической модели.
2 Ввод данных из формы в окно Поиск решения из меню Данные.
3 Задание параметров поиска и решение задачи.
Создание формы
а) Составление формы в виде:
Рисунок 7.2 – Форма в MS Excel, созданная по модели
б) Запись в ячейки В4:Е4 коэффициентов целевой функции Fmax (1), в В5:Е7 коэффициентов из системы ограничений (2) и в ячейки Н5:Н7 - свободных членов из системы (2).
в) Ввод формул с помощью процедуры fх- Мастер функций.
Для целевой функции: щелкнуть левой клавишей мыши по ячейке F4, затем по значку Мастер функций fх в Строке ввода, в появившемся окне Мастер функций, шаг 1 из 2 в списке Категория: Математические, в нижней части – выделить функцию СУММПРОИЗВ, нажать клавишу ОК, в окне " Аргументы функции " в поле Массив 1 ввести с клавиатуры В2:Е2 (ячейки, в которых будут варьироваться Х1..Х4), в поле Массив 2 ввести В4:Е4 (коэффициенты целевой функции ЦФ).
Примечание. Можно вводить В2:Е2 не с клавиатуры, а поставить курсор в окно Массив 1, а затем протащить курсор при нажатой левой клавише мыши по ячейкам В2:Е2, имена ячеек сами запишутся в окно. Аналогично поступить с полем Массив 2.
Нажать клавишу ОК, в ячейку F4 запишется формула 60*Х1+70*Х2+120*Х3+ 130*Х4 в виде СУММПРОИЗВ(В2:Е2)(В4:Е4)
Для левых частей ограничений аналогично:
- в ячейку F5 вносим СУММПРОИЗВ(В2:Е2)(В5:Е5),
- в ячейку F6 вносим СУММПРОИЗВ(В2:Е2)(В6:Е6),
- в ячейку F7 вносим СУММПРОИЗВ(В2:Е2)(В7:Е7).
Примечание. Чтобы каждый раз для новой ячейки F5..F7 не вызывать Мастер функций fх, можно скопировать в буфер команду из F4 СУММПРОИЗВ(B$2:E$2)(B4:E4) кнопкой на панели инструментов Копировать в буфер или командой из пункта меню Главная, затем вставить в выделенную ячейку F5..F7 эту команду с помощью кнопки Вставитьиз пункта меню Главная, при этом ячейки B$2:E$2 не изменятся, а В4:Е4 поменяются на В5:Е5, В6:Е6 и В7:Е7, т.к. символ абсолютной адресации строк $ в них не введён.
2 Ввод данных в окно Поиск решения
Выбрать в пункте меню Данные команду Поиск решения, поставить курсор в поле целевой функции, выделить ячейку F4 в форме(или ввести F4 с клавиатуры), поставить точку в кружок строки "Максимальному значению".
В поле "Изменяя ячейки" ввести В2:Е2 (с клавиатуры или протащив мышью).
Нажать клавишу "Добавить", в окне "добавление ограничения в поле "Ссылка на ячейку" ввести F5, выбрать через "стрелка вниз" знак " <= ", в поле справа ввести Н5.
Аналогично через "Добавить" ввести F6<=H6, F7<=H7 для системы ограничений B2>=B3,C2>=C3,D2>=D3 и Е2>=E3 для граничных условий Хi>=0.
После ввода последнего граничного условия вместо "Добавить" нажать клавишу ОК, появится окно "Поиск решения".
Для изменения или удаления ограничений и граничных условий используются клавиши Изменить.., Удалить.
Задание параметров поиска и решение задачи
В окне "Поиск решения" нажать клавишу "Параметры", выбрать по
умолчанию Максимальное время - 100 с.(может быть до 2^15=32767 c.> 4 час.), число итераций- 100(для большинства задач это количество просчётов подходит с большим запасом), установить флажок "птичка" в строке "Линейная модель", нажать ОК, в появившемся окне Поиск Решения нажать Выполнит ь, появится окно:
Результаты поиска решения с таблицей результатов:
т.е. оптимальный план Х(Х1,Х2,Х3,Х4)=(10,0,6,0)
при минимальном использовании ресурсов
- Трудовые - 16 (У1)
- Сырьевые - 84 (У2)
- Финансы - 100 (У3)
даёт максимум прибыли F в 1320 руб.
ВЫВОД: Максимальная прибыль F в 1320 руб. получается при выпуске
только товаров Х1 и Х3 в количестве 10 и 6 штук соответственно, товары Х3 и Х4 выпускать не нужно (это приведёт к снижению прибыли). Трудовые (У1) и финансовые (У3) ресурсы используются полностью, по сырьевым ресурсам (У2) есть запас в 110-84=26 ед.
Кроме того, это означает, что изменение трудовых (У1) и финансовых (У3) ресурсов приведёт к изменению прибыли F, а изменение сырьевых ресурсов (У2) - нет.
Разности между плановыми ресурсами и использованными являются двойственными переменными У1,У2 и У3 сопряжённой задачи линейного программирования. В данном случае У1=У3=0, а У2=26 ед. Таким образом, ресурс У2 можно уменьшить на 26 ед., тогда план по сырью тоже будет оптимальным.
Контрольные вопросы
1 Сформулировать основную задачу линейного программирования. Записать математическую модель ЗЛП.
2 Основные этапы решения ЗЛП с помощью процессора MS Excel 2007.
3 Способы ввода формул математической модели ЗЛП в форму
4 Ввод ограничений и граничных условий математической модели в форму
5 Ввод параметров поиска решения в процессор MS Excel 2007.
Задание
1 Составить собственную математическую модель задачи линейного программирования, прибавив к каждому коэффициенту рассмотренного примера свой номер № из списка группы в журнале преподавателя, т.е.
Аi=Ai+№, Вij=Bij+№, Ci=Ci+№.
2 Рассчитать максимальную прибыль и оптимальный план выпуска товаров для её достижения. Определить минимально необходимое количество ресурсов.
3 Выполнить то же, увеличив все ресурсы в 2 раза, сравнить результаты пунктов 2 и 3.
4 Для производства двух видов продукции А и В можно использовать сырье трех видов. При этом на изготовление единицы продукции вида А расходуется а1 кг. сырья первого вида, а2 кг сырья второго вида и а3 кг сырья третьего вида. На изготовление единицы продукции вида В расходуется в1 кг сырья первого вида, в2 кг. сырья второго вида и в3 кг сырья третьего вида (табл. 2).
На складе имеется всего сырья первого вида с1 кг,сырья второго вида с2 кг и третьего вида с3 кг. От реализации единицы готовой продукции вида А предприятие имеет прибыль Q тыс.руб., от реализации единицы готовой продукции вида В прибыль составляет V тыс.руб. Определить максимальную прибыль от реализации всей продукции видов А и В.
Математическая модель имеет вид:
Q Х1+ V Х2= Fmax – целевая функция прибыли.
A 
1 Х1+ B 1Х2 <= C 1
A 2 Х1+ B 2 Х2 <= C 2 - ограничения модели
A 3 Х1+ B 3 Х2<= C 3
Хj >=0 - граничные условия модели
Таблица 2
| № вар | A1 | A2 | A3 | B1 | B2 | B3 | C1 | C2 | C3 | Q | V |
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| 1 | 20 | 12 | 14 | 28 | 9 | 10 | 758 | 526 | 541 | 10 | 12 |
| 2 | 120 | 212 | 114 | 281 | 924 | 140 | 711 | 526 | 541 | 16 | 21 |
| 3 | 20 | 12 | 14 | 28 | 25 | 10 | 758 | 526 | 541 | 20 | 22 |
| 4 | 23 | 17 | 145 | 285 | 91 | 105 | 558 | 326 | 141 | 10 | 20 |
| 5 | 201 | 122 | 114 | 28 | 52 | 150 | 758 | 526 | 541 | 56 | 52 |
| 6 | 25 | 32 | 12 | 28 | 9 | 32 | 32 | 32 | 541 | 32 | 21 |
| 7 | 22 | 62 | 21 | 72 | 9 | 72 | 72 | 72 | 541 | 72 | 42 |
| 8 | 20 | 52 | 42 | 212 | 32 | 62 | 62 | 62 | 541 | 62 | 17 |
| 9 | 26 | 92 | 17 | 12 | 72 | 82 | 82 | 82 | 541 | 82 | 26 |
| 10 | 29 | 42 | 122 | 17 | 62 | 42 | 42 | 56 | 541 | 42 | 32 |
| 11 | 30 | 32 | 14 | 12 | 82 | 32 | 32 | 52 | 541 | 10 | 72 |
| 12 | 45 | 72 | 14 | 28 | 42 | 10 | 72 | 26 | 541 | 32 | 62 |
| 13 | 25 | 62 | 14 | 32 | 32 | 32 | 58 | 32 | 32 | 72 | 82 |
| 14 | 29 | 82 | 14 | 72 | 72 | 72 | 758 | 72 | 53 | 62 | 42 |
| 15 | 12 | 42 | 32 | 62 | 32 | 62 | 32 | 62 | 62 | 82 | 17 |
| 16 | 50 | 32 | 72 | 82 | 72 | 82 | 72 | 82 | 42 | 10 | 12 |
| 17 | 40 | 72 | 62 | 42 | 62 | 42 | 62 | 42 | 44 | 32 | 28 |
| 18 | 60 | 62 | 82 | 32 | 82 | 32 | 82 | 32 | 32 | 72 | 32 |
| 19 | 22 | 82 | 42 | 28 | 42 | 72 | 42 | 72 | 46 | 62 | 72 |
| 20 | 20 | 12 | 32 | 28 | 32 | 62 | 758 | 62 | 64 | 10 | 62 |
Содержание отчёта
1Название, цель, содержание работы
2 Задание своего варианта
3 Письменные ответы на контрольные вопросы
4 Выводы по работе
На своём носителе должны быть сохранены результаты работы
Практическое занятие №8
