Сделать индивидуальное задание №25

АНАЛИЗ «Что-если»

К средствам анализа «что-если» относят:
- Таблицы данных;
- Диспетчер сценариев;
- Подбор параметра;
- Поиск решения.
Все эти средства позволяют анализировать зависимость и влияние одних данных на другие.
Рассмотрим только два средства анализа данных.

ПОДБОР ПАРАМЕТРА

Подбор параметра является удобным средством для решения задач, которые имеют точное целевое значение, зависящее от одного неизвестного параметра. С помощью данного средства анализа можно определить значение,
которое будет давать желаемый результат.
В общем виде задачи, которые могут быть решены с помощью Подбора параметра, можно представить следующим образом. Есть значение х от
которого зависит формула или цепочка последовательно зависящих друг от
друга формул. Требуется изменить х таким образом, чтобы в конечной формуле получить заданное значение.
Для того чтобы воспользоваться средством анализа Подбор параметра,
нужно выбрать команду Данные  Работа с данными  Анализ «что-если»
 Подбор параметра. На экране появится окно диалога с 3 полями ввода.
В поле Установить в ячейке: нужно указать ячейку, в которой находится конечная формула. Обычно, прежде чем обращаться к средству Подбор
параметра, выделяют ячейку с формулой. В этой ячейке нужно получить некое конкретное значение. В этом случае в поле Установить в ячейке уже
будет указана абсолютная ссылка на активную ячейку.
В поле Значение: нужно ввести значение, которое д.б. достигнуто в
ячейке, указанной в поле Установить в ячейке.
В поле Изменяя значение ячейки: необходимо ввести ссылку на  ячейку, значение которое нужно подобрать таким образом, чтобы в ячейке
поля Установить в ячейке было получено значение, указанное в поле Значение. Т.е. в этом поле нужно указать ссылку на ячейку, значение которой
является неизвестным.
ПОИСК РЕШЕНИЯ
Инструмент Поиск решения может применяться для решения задач, которые включают много изменяемых ячеек, и помогает найти комбинации переменных, которые устанавливают целевую ячейку в требуемое значение
(например, максимальное или минимальное). Он также позволяет задать одно
или несколько ограничений – условий, которые должны выполняться при
поиске решений.
С помощью средства Поиск решения могут быть решены следующие
задачи:
- транспортная задача;
- задача о назначениях;
- составление оптимального плана производства;
- решение систем нелинейных уравнений;
- решение уравнения регрессии.
Прежде чем обращаться к инструменту Поиск решения, нужно проанализировать задачу и построить математическую модель. Для построения модели необходимо:
1) определить переменные модели;
2) выбрать целевую функцию;
3) задать ограничения, которым должны удовлетворять переменные.
При подготовке рабочего листа к решению задачи нужно:
1) отвести диапазон ячеек для хранения переменных величин;
2) в отдельную ячейку ввести функцию цели. Функция цели всегда зависит от переменных, поэтому в ячейке с целевой функцией будут использованы ссылки на ячейки, где хранятся переменные;
3) подготовить значения и формулы для задания ограничений. Поскольку ограничения накладываются на переменные, то в формулах для задания ограничений будут использованы ссылки на ячейки, где хранятся переменные.
После построения математической модели можно обратиться к средству Поиск решения. Для этого нужно воспользоваться командой Данные 
Анализ  Поиск решения. (Если эта команда недоступна, то сначала нужно
выполнить следующую последовательность действий: Кнопка «Office» 
Параметры Excel  Надстройки  Управление: Надстройки Excel 
Перейти и установить флажок Поиск решения).
В поле Установить ЦФ нужно указать ссылку на ячейку с целевой
функцией (рекомендуется произвести выделение этой ячейки, до вызова инструмента Поиск решения).
Ниже поля ввода расположены элементы управления – переключатели,
- позволяющие задать, какое значение ЦФ должно быть достигнуто при решении задачи: минимальное, максимальное или некоторое конкретное значение.
Поле Изменяя ячейки позволяет задать диапазон, в котором располагаются неизвестные величины, влияющие на целевую функцию.
Список Ограничения представляет все ограничения, накладываемые
кнопкой Добавить (появится новое диалоговое окно).
Поле Ссылка на ячейку появившегося диалогового окна предназначено для указания ссылки на ячейки, где хранятся переменные и формулы, используемые для задания ограничений. В поле Ограничение можно задать
константу, ссылку на ячейки со значениями и формулами. Значения из полей Ссылка на ячейку и Ограничение сравниваются с помощью операций
(>=, <=, =, цел, двоич), которую можно выбрать из списка, расположенного
между этими двумя полями.
Кнопка Добавить позволит задать несколько ограничений, кнопка ОК
добавляет ограничение и закрывает это окно. 
Кнопка Изменить диалогового окна Поиск решения позволяет модифицировать выделенное в списке Ограничение ограничение. При этом на
экране появится диал. окно Добавление ограничение с уже заполненными
элементами управления.
Кнопка Удалить диалогового окна Поиск решения позволяет удалить
выделенное ограничение.
После того как все данные для инструмента Поиск решения будут заданы, следует воспользоваться кнопкой Выполнить. Если решение будет найдено, Excel выдаст об этом сообщение, и результаты расчета задачи будут
размещены в соответствующие ячейки. Если решение не может быть найдено, то Excel также выдаст об этом сообщение.
Параметры поиска решения.
Чтобы изменить параметры поиска решения исследуемой задачи, нужно воспользоваться кнопкой Параметры диал. окна Поиск решения для вызова окна диалога Параметры поиска решения.

Пример. Решение системы нелинейных уравнений.


Система содержит уравнение окружности и уравнение прямой.
Решением системы являются точки пересечения окружности и прямой (2, 1
или вообще нет). Таким образом, решений этой системы может быть не более
2-х или не быть вообще.
Пара (х, у) является решением системы уравнения в том случае, если
она является решением уравнения с двумя неизвестными:

Левая часть этого уравнения представляет собой функцию от двух переменных. Чтобы решить уравнение нужно протабулировать эту функцию. За начальные приближения к корням уравнения следует выбрать пары (х, у), в которых функция ближе к 0.

Углубленное освоение инструментария связанного с вычислительными процедурами, с анализом числовых данных, с иллюстративным представлением результатов анализа в табличном процессоре MS Excel.

Описание проектного задания
Варианты заданий. Представить графическое и аналитическое решение системы нелинейных уравнений, в среде табличного процессора Microsoft Excel: