Работа с математическими и статистическими функциями. Расчёт зарплаты сотрудникам кафедры с помощью формул. Анализ данных. Автоматизация процесса подготовки документа, страница 9

Запишем в ячейки В1 и В2 значения, равные 0;

В ячейку В3 пишем формулу: =4*В1+3*В2 – это левая часть 1-го уравнения из системы уравнений;

В ячейку В4 пишем формулу: =В1+В2 – это левая часть 2-го уравнения из системы уравнений;

В ячейку С3 записываем значение, равное 50 – это правая часть 1-го уравнения из системы уравнений;

 В ячейку С4 записываем значение, равное 14 – это правая часть 2-го уравнения из системы уравнений;

В ячейку В5 поместим значение: =В2.

Выделим ячейку целевой функции (С5).

Чтобы начать работать с инструментом поиск решения, выберите в меню СЕРВИС команду ПОИСК РЕШЕНИЯ. Откроется окно диалога, показанное на рис. 2. В этом окне диалога нужно указать цель, изменяемые ячейки и ограничения.

Подсказка: Если вы не обнаружите этой команды, то вам придется установить инструмент Поиск решения. Для этого выберите из меню СЕРВИС команду НАДСТРОЙКИ, в появившемся диалоговом окне НАДСТРОЙКИ найдите и поставьте отметку в поле Поиск решения, а затем щелкните на кнопке ОК.

Рис.2. Диалоговое окно Поиск решения

3.  Задание цели

В поле Установить целевую ячейку задаётся цель поиска решения. В данном примере в поле Установить целевую ячейку введем ссылку на ячейку В5 и установим переключатель максимальному значению в группе Равной.

4.  Задание переменных

Теперь нужно задать изменяемые ячейки. В данном примере это будут ячейки, расположенные в диапазоне В1:В2 (задаем ссылку на этот диапазон). В этих ячейках будут записано количество мороженого каждого вида, которое купил мальчик.

5.  Задание ограничений

Последний шаг – задание ограничений. Чтобы задать ограничения, в окне диалога Поиск решения щелкнем на кнопку Добавить. Появится диалоговое окно Добавление ограничения, показанное на рис. 3.  В поле Ссылка на ячейку: введем ссылку на ячейку В3, выберем оператор сравнения (=) и в поле Ограничение введем ссылку на ячейку С3. после этого щелкнем на кнопке ОК.

Рис.3. Наложение ограничения на значение в диалоговом окне

Проделаем то же самое для наложения ограничений на остальные значения. На рис.4. показано окно диалога Поиск решения после задания всех ограничений для данной задачи.

Рис.4. Поиск решения выводит список ограничений в алфавитном порядке

Ещё один важный момент при задании ограничений: мы должны предполагать, что количество мороженого обоих видов не может быть отрицательным. Следовательно, необходимо в поле «Ограничения» добавить две записи: $B$1>=0 и $B$2>=0.

После заполнения окна диалога поиск решения нажмем кнопку Выполнить для закрытия окна. Рассчитанные значения помещаются в соответствующие ячейки таблицы (рис.5).

Рис.5. Видоизменённая таблица

6.  Сценарии

Сценарии являются частью блока задач, который иногда называют инструментами анализа «что-если» (Анализ «что-если» - процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул на листе. Например, изменениепроцентной ставки, используемой в таблице амортизации для определения суммплатежей). Сценарий — это набор значений, которые Microsoft EXCEL сохраняет и может автоматически подставлять на листе. Сценарии можно использовать для прогноза результатов моделей и систем расчетов. Существует возможность создать и сохранить на листе различные группы значений, а затем переключаться на любой из этих новых сценариев для просмотра конечных результатов. Сценарий содержит специфический набор значений для изменяемых ячеек, этому набору дано имя. Можно изменить эти значения или сохранить их в другом сценарии. Благодаря этому средству пользователь таблицы может хранить в ней несколько вариантов расчётов.

После завершения работы процедуры Поиск решения можно в диалоговом окне Результаты поиска решения щёлкнуть на кнопке Сохранить сценарий. Определите для сценария имя и нажмите на кнопку ОК.

Диспетчер сценариев позволяет создать столько сценариев, сколько необходимо для одной модели «что-если», каждый из которых может иметь своё собственное множество переменных. Затем воспользоваться отчётом и сравнить между собой сценарии с разными множествами переменных.