Подбор параметров и поиск решения. Использование макросов в ms excel (усрс 2 часа)

Цель работы:

изучить основные правила работы с инструментами подбор параметра и поиск решения, познакомиться с возможностями Visual Basic for Applications по созданию и использованию макросов, форм и элементов управления.

Задание 10.5.

Используя инструмент Подбор параметра найти все корни уравнения х³+2,28х²–1,9347х-3,907574=0 с относительной погрешностью 0,000001.

Рекомендации по выполнению:

Рекомендации по выполнению:

1. Создайте книгу и сохраните ее в своей папке под именем Лабораторная работа 10-УСРС.

Переименуйте лист 1 в Подбор параметра-корни и создайте.

2. Установить погрешность вычислений: Файл → Параметры → Формулы → Относительная погрешность (рис 10.13).

Рис. 10.3. Установка погрешности вычислений

3. Для определения корней предварительно рассчитать функцию х³+2,28х²–1,9347х-3,907574=0 при х [-3; 2]:

• в ячейку А1 ввести Х, в ячейку В1 ввести Y.

• в ячейку А2 ввести -3,0 в ячейку А3 ввести -2,9, выделить ячейки А2:А3 и, используя механизм автозаполнения, растянуть значения Х до 2 (ячейка А52);

• в ячейку В2 ввести формулу =A2^3+2,28*A2^2-1,9347*A2-3,907574;

• скопировать формулу в диапазон В3:В52, определить для значений Y число десятичных знака – 4 (рис. 10.14).

Рис. 10.4. Фрагмент ввода значений Х и Y

4. Построить график функции (рис. 10.15):

• выделить диапазон А1:В52;

• выполнить команду Вставка (Диаграммы) → Точечная → Точечная с гладкими кривыми;

• вызвав контекстное меню для оси Х, выбрать Формат оси → Параметры оси и указать цену основных делений фиксированное, 0,2.

Рис. 10.15. График функции Y = х³+2,28х²–1,9347х-3,907574

5. Графически определить корни уравнения х³+2,28х²–1,9347х-3,907574=0. Визуально на графике видно, что:

• первый корень находится на отрезке х [-2,6; -2,4];

• второй корень находится на отрезке х [-1,4; -1,2];

• третий корень находится на отрезке х [1,2; 1,4].

6. Для уточнения корней воспользоваться методом Подбор параметра:

• ввести в ячейку D1 значение Х1, в D2 – Х2, в D3 – Х3;

• ввести в ячейки начальные приближения корней уравнения: в ячейку Е1 значение -2,5 (средина отрезка на котором находиться первый корень (-2,6+(-2,4))/2=-2,5); в Е2 – -1,3 (средина отрезка на котором находиться второй корень); в ячейку Е3 – 1,3 (средина отрезка на котором находиться второй корень);

• в ячейку F1 ввести формулу расчета значения функции от начального приближения первого корня =E1^3+2,28*E1^2-1,9347*E1-3,907574;

• скопировать значение ячейки F1 в ячейки F2 и F3 (рис. 10.16).

Рис. 10.16. Начальные приближения корней уравнения

7. Воспользоваться методом Подбор параметра:

• выделить ячейку F1, выполнить команду Данные (Работа с данными) → Анализ «что если» → Подбор параметра (рис. 10.17).

Рис. 10.17. Обращение к механизму Подбор параметра

• задать параметры в окне Подбор параметра (рис. 10.18):

  • Установить в ячейки: –F1 (указать адрес ячейки рассчитываемой функции);

  • Значение: – 0 (искомый результат);

  • Изменяя значение ячейки: – $E$1 (ячейка, значение которой нужно подобрать).

Рис. 10.18. Параметры окна Подбор параметра

Нажать кнопку ОК.

В окне Результат подбора параметра нажать кнопку ОК

8. Аналогично выполнить Подбор параметра для значений в ячейках F2, F3. Задать для ячеек F1, F2, F3 экспоненциальный формат с числом десятичных знаков – 4 (рис. 10.19).

Рис. 10.19. Результат нахождения корней уравнения

Задание 10.6.

Необходимо рассчитать цену нового товара исходя из того, что известна общая сумма планируемых продаж, планируемые объемы продаж по всем товарам и цены на все остальные товары.

Рекомендации по выполнению:

1. Переименуйте лист 2 книги в Подбор параметра-Товар.

2. Создать таблицу с указанием цены, количества и расчетом суммы по каждому товару по формуле Количество*Цена (рис. 10.20).

Рис. 10.20. Таблица с начальными значениями и расчетом поля Сумма

3. Добавить столбец Всего с расчетом общей суммы продаж (рис. 10.21).

Рис. 10.21. Расчет общей суммы продаж

4. Подобрать цену нового товара, исходя из того, что общая сумма продаж должна быть равна 68000 тыс. руб.:

• активизировать ячейку Е2, где указана общая сумма продаж;

• выполнить команду Данные (Работа с данными) → Анализ «что если» → Подбор параметра;

• задать параметры в окне Подбор параметра (рис. 10.22):

  • Установить в ячейки: – Е2 (указать адрес ячейки);

  • Значение: – 68000 (сумму продаж, на которую должны выйти);

  • Изменяя значение ячейки: – $D$4 (ячейка, где будет подбираться цена нового товара).

Рис. 10.22. Расчет цены нового товара

Нажать кнопку ОК. Получим цену нового товара (рис. 10.23).

Рис10.23. Результат вычислений

Задание 10.7.

Большинство задач, решаемых с помощью электронной таблицы, предполагают нахождение искомого результата по известным исходным данным. Но в Excel есть инструменты, позволяющие решить и обратную задачу: подобрать исходные данные для получения желаемого результата.

Одним из таких инструментов является Поиск решения, который особенно удобен для решения так называемых «задач оптимизации».

Рассмотреть распределение премии между сотрудниками производственного отдела в сумме 20 000 тыс. руб. пропорционально их должностным окладам, т. е. подобрать коэффициент пропорциональности для вычисления размера премии по окладу.

Рекомендации по выполнению:

1. Переименуйте лист 3 книги в Поиск решения-Премия.

2. Если раньше не использовали Поиск решения, то потребуется установить соответствующую надстройку. Для этого:

• выполнить команду Файл → Параметры → Надстройки;

• в окне нажать кнопку Перейти… (рис. 10.24);

• в окне Надстройки выбрать все доступные надстройки (рис. 10.25).

Рис 10.24. Параметры настройки MS Excel

Рис. 10.25. Выбор надстроек

Кнопка для запуска Поиск решения появится на вкладке Данные (рис. 10.26).

Рис. 10.26. Кнопка запуска инструмента Поиск решения

3. Создать таблицу с исходными данными и формулами, с помощью которых должен быть получен результат.

Результат – это суммарная величина премии. Очень важно, чтобы целевая ячейка С8 посредством формул была связана с искомой изменяемой ячейкой Е2. В задании они связаны через промежуточные формулы, вычисляющие размер премии для каждого сотрудника С2:С7 (для ячейки С2 формула =$E$2*B2).

Рис. 10.27. Вид таблицы с исходными данными и формулами

4. Запустить Поиск решения командой Данные (Анализ) → Поиск решения. В открывшемся диалоговом окне устанавливаем необходимые параметры (рис. 10.28):

• Оптимизировать целевую функцию: – $C$8

• Значения – 20000

• Изменяя ячейки переменных: – $Е$2.

Рис. 10.28. Фрагмент заполнения параметров Поиск решения

• В соответствии с ограничениями: – после активизации поля нажать кнопку Добавить, откроется окно Добавление ограничения. В открывшимся окне (рис. 10.29):

  • в поле Ссылка на ячейки: указать $Е$2;

  • выбрать знак ;

  • Указать Ограничение: – 0.

  • нажать кнопку ОК.

Рис. 10.29. Добавление ограничений