Копирование макроса в другую книгу

Созданный макрос действует во всех листах открытой книги. Для копирования макроса в другую книгу нужно:

1 Открыть книгу, содержащую копируемый модуль, и книгу, в которую нужно его копировать – через меню Файл\Открыть или кнопку Открыть на панели инструментов Стандартная.

2 В меню Сервисустановить курсор на пункт Макрос и выбрать ко-

манду Редактор Visual Basic.

3 В меню Вид выбрать команду Окно проекта .

4. Перетащить в Окне проекта с помощью мыши копируемый модуль в нужную книгу.

Примечание. Чтобы получить возможность использовать макрос в любое время, сохраните его в личной книге макросов. Он получит имя следующим форматом: «PERSONAL.XLS. Имя макроса».

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

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

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

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

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

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

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

Создание новой панели инструментов. Назначение кнопки для запуска макроса на новой панели инструментов .

7 Добавление кнопки запуска макроса на существующие панели инструментов (Стандартная,Форматирование и др.).

8 Добавление кнопки запуска на рабочий лист с помощью панели инструментов Формы.

Копирование модуля макроса в другую книгу.

Задание

1 Разработать макрос для вычисления прогнозируемых значений функции y = 0,2·x ⁿ , где n – номер компьютера в аудитории. Предварительно функцию нужно ввести в ячейки столбца или строки процессора Excel, задав изменение аргумента хв пределах 1...7 с шагом 1 и вычислив в соседнем столбце (или строке) по введённой формуле значения функции у(см. л.р. excel – 3). Прогноз выполнить с помощью функций ТЕНДЕНЦИЯ или РОСТ (см. л.р. excel – 4).

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

2 Разработать макрос для построения графика функции y = 2·sin(n·x) , где n – номер компьютера в аудитории, для 10 значений аргумента х = 1...10. При записи макроса использовать относительную адресацию. Макрос должен запускаться сочетанием клавиш с клавиатуры и командой из меню Сервис.

3 Открыть новую книгу и скопировать в неё оба макроса. Создать новую панель инструментов. Назначить макросам новые кнопки для их запуска с новой панели инструментов .

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На дискете должны быть сохранены результаты работы

Лабораторная работа №7

Поиск решения в Excel

Цель работы:научиться использовать процессор Excel

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

Содержание работы:

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

2 Ввод данных в окно Поиск решения

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

Общие сведения

Математический аппарат Excel позволяет решать задачи линейного, нелинейного и целочисленного программирования. При этом оптимизация решения выполняется методом поиска решения, который запускается командой Сервис\Поиск решения.

Задача линейного программирования (ЗЛП) в общем случае формулируется следующим образом:

Определить максимум (минимум) целевой функции 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)

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

Задача. МП выпускает товары Х1,Х2,Х3,Х4, получая от реализации каждого прибыль в 60,70,120,130 руб. соответственно. Затраты на производство приведены в табл. 1. Определить:

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

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

Таблица 1

Затраты	Х1	Х2	Х3	Х4	Всего
Трудовые
Сырьевые
Финансы

Составим математическую модель процесса по описанию задачи:

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 - граничные условия модели

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

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

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

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

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

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

А B C D E F G H

1 Переменная Х1 Х2 Х3 Х4 Формула Знак Св. член

2 Значение

3 Нули

4 Коэф. ЦФ 60 70 120 130 СП(В2:Е2)(В4:Е4) max

5 Трудовые 1 1 1 1 СП(В2:Е2)(В5:Е5) 16

6 Сырьевые 6 5 4 1 СП(В2:Е2)(В6:Е6) 110

7 Финансы 4 6 10 13 СП(В2:Е2)(В7:Е7) 100

б)Запись в ячейки В4:Е4 коэффициентов целевой функции F (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, т.к. символ абсолютной адресации строк $ в них не введён.