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

7.  Определение сценария

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

Дадим имена изменяющимся ячейкам В1:В2. Ячейке В1 – имя «количество мороженого первого вида», ячейке В2 – соответственно, «количество мороженого второго вида».

Чтобы определить сценарий, требуется выполнить команду меню СЕРВИС/СЦЕНАРИИ. Появится диалоговое окно ДИСПЕТЧЕР СЦЕНАРИЕВ.

Рис.6. Окно диалога ДИСПЕТЧЕР СЦЕНАРИЕВ

В этом окне нажмите на кнопку «Добавить…». После чего в появившемся диалоговом окне ДОБАВЛЕНИЕ СЦЕНАРИЯ введите имя для своего сценария, в нашем случае введите имя «мороженое». В поле Изменяемые ячейки укажите какие ячейки вы собираетесь изменять, в нашем случае укажите ячейки В1:В2. Далее щелкните кнопку ОК, чтобы создать первый сценарий. Откроется окно диалога ЗНАЧЕНИЯ ЯЧЕЕК СЦЕНАРИЯ с полями для каждой изменяемой ячейки.

Рис.7. Поскольку мы ранее присвоили имена изменяемым ячейкам, эти имена отображаются в окне диалога ЗНАЧЕНИЕ ЯЧЕЕК СЦЕНАРИЯ

Эти поля содержат значения, которые в данный момент введены на рабочем листе.

Для завершения создания сценария нажмите кнопку ОК, чтобы вернуться в окно диалога ДИСПЕТЧЕР СЦЕНАРИЕВ. Для закрытия этого окна нажмите кнопку ЗАКРЫТЬ.

Для нашей задачи чтобы создать несколько сценариев и провести отчёт по сценариям, изменим в системе уравнений цену обоих видов мороженого, а остальное (сумма, выделенная на мороженое; общее количество мороженого, которое взял мальчик) оставим нетронутым. Всего пусть будет 4 системы уравнений, не считая первой:

1 система уравнений:  5*x+6*y=50

x+y=14

2 система уравнений:  4,5*x+2,5*y=50

x+y=14

3 система уравнений:  1,5*x+5,5*y=50

x+y=14

4 система уравнений:  10*x+2*y=50

x+y=14

Далее для решения каждой системы уравнений используется инструмент Поиск решения и строится сценарий так, как описано выше.

8.  Создание отчётов по сценариям

Чтобы иметь перед глазами все сценарии вместе, в ДИСПЕТЧЕРЕ СЦЕНАРИЕВ существует командная кнопка «Отчёт». Отчёт показывает значения, которые каждый сценарий назначает изменяемым ячейкам.

Рис.8. Существующие сценарии в диалоговом окне ДИСПЕТЧЕР СЦЕНАРИЕВ

При нажатии кнопки «Отчёт» в окне диалога ДИСПЕТЧЕРА СЦЕНАРИЕВ выводится диалоговое окно «Отчёт по сценарию».

Рис.9. Окно диалога «Отчёт по сценарию» используется для задания типа отчёта

На выбор предлагается тип отчёта: «структура» или «сводная таблица». Установим переключатель «структура». Появится новый рабочий лист «Структура сценария».

Рис.10. Появление нового листа с именем «Структура сценария»

9.  Графический способ решения

Эту же систему уравнений можно решить графическим способом. Создаём таблицу из 3 столбцов. В первый столбец занести значения Х от 0 до 16 с интервалом, равным 2. Во втором столбце выводятся  значения Y1 с помощью формулы =(50-4*А118)/3. В третьем столбце выводятся значения Y2 с помощью формулы =14-А118.

На основе полученных результатов строится диаграмма.

10.  Мастер диаграмм

Диаграммы используются для наглядного представления данных. Многие данные будут более понятными, если изобразить их на диаграмме. Перед построением диаграммы выделите любую из ячеек таблицы, содержащей исходные данные диаграммы. Затем в меню ВСТАВКА выберите команду ДИАГРАММА или нажмите кнопку МАСТЕР ДИАГРАММ () на стандартной панели инструментов. Выбрав нужный тип диаграммы (график с маркерами, помечающими точки данных), перейдите на вкладку ДАЛЕЕ. В появившемся втором окне диалога МАСТЕРА ДИАГРАММ задаются данные, используемые Excel при построении диаграммы. Поле «Диапазон» вкладки «Диапазон данных» позволяет подтвердить или задать правильный диапазон ячеек с исходными данными. Задайте диапазон, который включает в себя столбцы Y1 и Y2 (вместе с именами столбцов). Затем перейдите на вкладку «Ряд» этого окна и в поле «Подписи по оси Х» укажите значения из столбца Х (без имени). Если в окошке предварительного просмотра диаграмма выглядит так, как нужно, можно нажать кнопку ДАЛЕЕ, чтобы перейти к следующему окну. На вкладках этого окна задаются параметры диаграммы. После задания параметров и нажатия кнопки ДАЛЕЕ вызывается последнее диалоговое окно, где выбирается размещение диаграммы (на активном или отдельном рабочем листе).