Запуск макроса из окна Макрос

Для запуска макроса можно назначать или не назначать кнопку, в любом случае он фиксируется в списке макросов. Его можно увидеть в окне Макрос, которое открывается командой Вид \ группа Макросы \ Макросы \ Макросы (рисунок 6.3).

           

Рисунок 6.3 – Вывод окна Макросы (справа)

 

Поэтому для запуска макроса достаточно выделить в окне имя нужного макроса, например График и нажать клавишу Выполнить.

               Более сложные макросы создаются с помощью редактора Visual Basic.

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

1 Что называется макросом, для чего он предназначен?

2 Как записать макрос?

3 Способы запуска макроса (перечислить).

4Запись макроса, запускаемого из окна Макрос и сочетанием клавиш с клавиатуры.

5 Использование относительной и абсолютной адресации при записи макросов.

6 Как сделать макрос доступным для всех открытых книг MS Excel?

 

Задание

1 Разработать макрос для вычисления прогнозируемых значений функции y = 0,2 · xⁿ, где 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) =A₁*X₁+A₂*X₂+...+A_n*X_n             (1)

 

           B₁₁*X₁+B₁₂*X₂+...+B_1n*X_n<=C₁

           B₂₁*X₁+B₂₂*X₂+...+B_2n*X_n<=C₂

          ................................................            (2)

           B_n1*X₁+B_n2*X₂+...+B_nn*X_n<=C_n       

 

                 Xi>=0, i=1...n                                  (3)

              

 Система уравнений (1)..(3) является математической моделью ЗЛП.

Рассмотрим, как применяется процессора MS Excel для решения ЗЛП:

 

Задача. МП выпускает товары Х₁,Х₂,Х₃,Х₄, получая от реализации каждого прибыль в 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Х₁+70Х₂+120Х₃+130Х₄ = F_max – целевая функция прибыли.

 

Х₁+Х₂+Х₃+Х₄ <= 16

6Х₁+5Х₂+4Х₃+Х₄ <= 110  - ограничения модели

4Х₁+6Х₂+10Х₃+13Х₄ <= 100

 

  Х_j >=0                             - граничные условия модели

 

   Отметим, что целевая функция и система ограничений математически представляют собой сумму произведений аргументов Х_j на числовые коэффициенты, поэтому при вводе модели в MS Excel  применяется функция СУММПРОИЗВ().

 

Решение задачи средствами MS Excel состоит из 3 этапов:

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

2 Ввод данных из формы в окно Поиск решения из меню Данные.

3 Задание параметров поиска и решение задачи.

 

Создание формы

   а) Составление формы в виде:

 

   

Рисунок 7.2 – Форма в MS Excel, созданная по модели

 

 б) Запись в ячейки В4:Е4 коэффициентов целевой функции F_max (1),  в В5:Е7 коэффициентов из системы ограничений (2) и в ячейки Н5:Н7 - свободных членов из системы (2).

в) Ввод формул с помощью процедуры f_х- Мастер функций.

Для целевой функции: щелкнуть левой клавишей мыши по ячейке F4, затем по значку Мастер функций f_х в Строке ввода, в появившемся окне Мастер функций, шаг 1 из 2 в списке Категория: Математические, в нижней части – выделить функцию СУММПРОИЗВ, нажать клавишу ОК, в окне " Аргументы функции " в поле Массив 1 ввести с клавиатуры В2:Е2 (ячейки, в которых будут варьироваться Х₁..Х₄), в поле Массив 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 Х₁+ V Х₂= F_max – целевая функция прибыли.

A ₁ Х₁+ B 1Х₂ <= C ₁

A ₂ Х₁+ B ₂ Х₂ <= C ₂   - ограничения модели

A ₃ Х₁+ B ₃ Х₂<= C ₃

  Х_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