- •1. Основные правила работы в excel
- •Список условных обозначений
- •1.2. Основные понятия Excel
- •1.3. Выделение блока ячеек
- •1.4. Ввод текстов
- •1.5. Ввод чисел
- •1.6. Ввод стандартных списков.
- •1.7. Ввод формул
- •1.8. Работа с Мастером функций
- •1.9. Присваивание имён ячейкам и блокам
- •1.10. Использование подписей данных
- •1.11. Правка информации
- •1.12. Копирование и перемещение информации
- •1.13. Команды форматирования
- •Рисование графиков и диаграмм
- •Первый шаг Мастера диаграмм: тип диаграммы
- •2.2. Второй шаг Мастера диаграмм: источник данных диаграммы
- •Третий шаг Мастера диаграмм: параметры диаграммы
- •Четвёртый шаг Мастера диаграмм: размещение диаграммы
- •Исправление диаграммы
- •Построение линии тренда
- •Расчётные алгоритмы в excel
- •3.1. Расчёт таблицы значений функции от одного аргумента
- •3.2. Расчёт таблицы значений функции от двух аргументов
- •3.3. Использование функции если для анализа информации
- •3.4. Оценка определённого интеграла
- •3.5. Нахождение корня уравнения
- •3.6. Решение систем уравнений
- •3.7. Решение задач оптимизации
- •4. Использование visual basic в excel
- •4.1. Работа с макросами
- •5. Задания для самостоятельной работы
- •Литература
- •Содержание
3.7. Решение задач оптимизации
Команда СервисПоиск решения… предоставляет пользователю следующие возможности:
поиск безусловных экстремумов функции одного или нескольких аргументов;
поиск экстремумов функции одного или нескольких аргументов при наличии ограничений на найденное решение;
поиск аргументов, при которых функция примет нужное значение;
выбор метода решения поставленной задачи;
ввод ограничения на точность и время выполнения задачи.
Эти возможности реализуются с помощью параметров, собранных в основном окне Поиск решения и дополнительном Параметры поиска решения. Дополнительное окно вызывается кнопкой <Параметры> из основного. Кнопка <Справка> вызывает окно с разъяснением смысла каждого параметра и возможностей, которые предоставляются при его заказе.
Методы оптимизации можно так же применять для решения систем нелинейных уравнений. Для этого из уравнений системы
f1(x1,x2…,xn)=0; f2(x1,x2…,xn)=0; …; fn(x1,x2…,xn)=0;
составляют вспомогательную целевую функцию
S=f12+ f22+…+ fn2
S – неотрицательная функция, её минимальное значение равно нулю и достигается только тогда, когда все слагаемые одновременно равны нулю. А это и есть решения исходной задачи.
Рассмотрим в качестве примера систему двух нелинейных уравнений
x2+y2=3; 2x+3y=1
Введём исходные данные задачи по представленному ниже плану.
Для удобства дальнейшей работы можно провести форматирование созданной таблицы, аналогичное п. 3.5.
Вызовем команду СервисПоиск решения… В окне Поиск решения установим следующие параметры:
"Установить целевую ячейку:" А9
"Равной:" минимальному значению
"Изменяя ячейки:" А4:В4
Нажмём кнопку <Параметры> и в дополнительном окне Параметры поиска решения проверим, что флажок Линейная модель не установлен. Закроем дополнительное окно кнопкой <ОК>
Запустим команду кнопкой <Выполнить> основного окна.
Когда команда закончит работу, на экране автоматически появляется окно Результаты поиска решения. Пояснения к параметрам, представленным в нём, вызываются кнопкой <Справка>. Закажем, к примеру, параметры "Сохранить найденное решение" и "Тип отчёта: результаты". В этом случае начальные значения переменных в ячейках А4:В4 заменятся на найденные и в таблицу будет вставлен новый лист "Отчёт по результатам 1". Просмотрите отчёт. Проверьте, какое значение приняла вспомогательная целевая функция в А9 при найденных решениях. Если она существенно отличается от нуля, то решение найдено неверно.
Ячейки |
Информация |
Значение |
А1 |
Заголовок расчёта |
Решение системы нелинейных уравнений |
А2 |
Заголовок |
Переменные |
А3:В3 |
Название переменных |
А3: Х, В3: Y |
А4:В4 |
Начальные значения переменных |
А3: 1, В3: -1 |
А5 |
Заголовок |
Функции системы |
А6:В6 |
Названия функций системы |
А6: f1, B6: f2 |
А7:В7 |
Формулы для расчёта функций |
=A4^2+B4^2-3 =2*A4+3*B4-1 |
А8 |
Заголовок |
Вспомогательная целевая функция |
А9 |
Формула целевой функции |
=A7^2+B7^2 |
Успешность поиска решения во многом зависит от выбора начального приближения переменных. В случае двух уравнений с двумя переменными можно не делать аналитического исследования функций системы, а составить таблицу вспомогательной целевой функции (см. п. 3.2) и выбрать по ней те комбинации аргументов, при которых функция принимает наименьшие значения.
Задание.
Составьте таблицу значений целевой функции S=(x2+y2-3)2+(2x+3y-1)2 в диапазоне аргументов -3<x<3, -3<y<3. Выберите 4 – 5 точек с наименьшими значениями функции, проведите поиск решения из каждой из них. В результате должно быть получено только два разных решения: х1=-1,268; у1=1,179 и х2=1, 576; у2=-0,717. Графически уравнения системы представляются окружностью и прямой линией. Система такого типа не может иметь больше двух точек пересечения.