Анализ данных

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

Математически задача состоит в решении уравнения f(x)=a, где функция f(x) описывается формулой, x является исходным параметром, значение которого требуется подобрать, a – требуемый результат ее решения.

Решение проблемы ищется в соответствии с алгоритмом:

• В ячейку, где должен быть получен результат подбора, записывается формула, которая требует своего решения;

• В меню Сервис выбирается команда Подбор параметра.

• В поле Установить в ячейке вводится ссылка на ячейку, содержащую формулу (по умолчанию, в эту ячейку вводится адрес текущей ячейки);

• В поле Значение ввести значение, которое нужно получить при решении задачи;

• В поле Изменяя ячейку вести ссылку на ячейку, где находится значение изменяемого параметра (аргумента);

• Щелкнуть клавишу Ок.

При вводе формулы указывается адрес ячейки, где хранится значение изменяемого параметра (аргумента).

После выполнения команды в ячейке с аргументом появится его подобранное значение, а в ячейке с формулой – определенное при этом значение вычисляемой величины (функции). При этом в таблице будут пересчитаны значения всех ячеек, которые влияют на или зависят от величины изменяемого параметра.

Диспетчер сценариев

В среде Excel можно хранить наборы значений данных, которые приводят к различным результатам. Это полезно, когда есть необходимость проанализировать (сравнить) результативность нескольких разных наборов исходных данных (ситуаций). Сохранение данных производится в виде сценария.

Сценарий – это множество входных значений, которые называют изменяемыми ячейками. Их можно сохранит под некоторым именем и применить впоследствии к модели рабочего листа, чтобы проследить влияние изменения значений изменяемых ячеек на другие значения модели. Для каждого сценария можно определить для хранения данных до 32 ячеек.

Для создания сценария:

• В меню Сервис выбрать команду Сценарии;

• Щелкнуть по кнопке Добавить. Открывается окно Добавление сценария;

• В поле Название сценария необходимо ввести имя нового сценария;

• В поле Изменяемые ячейки ввести ссылки на изменяемые ячейки. Ссылки на отдельные ячейки отделяются точкой с запятой, их можно ввести с клавиатуры или путем выделения ячеек на рабочем листе. Несмежные ячейки выделяются при нажатой кнопке Ctrl;

• Согласиться с введенными данными (щелчок по кнопке Ок);

• В открывшемся диалоговом окне Значения ячеек сценария ввести значения каждой изменяемой ячейки;

• Для создания других сценариев щелкнуть по кнопке Добавить, при этом открывается диалоговое окно Добавление сценария. Повторить процедуру ввода данных для нового сценария;

• Для завершения работы с Диспетчером сценариев щелкнуть по кнопке Ок, потом – по кнопке Закрыть.

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

Для просмотра сценария:

• Подать команду Сервис Сценарии;

• В поле Сценарии выделить имя сценария, который требуется просмотреть:

• Щелкнуть по кнопке Вывести.

Чтобы отредактировать сценарий (изменить исходные значения записанных в нем параметров):

• Подать команду Сервис Сценарии;

• В поле Сценарии выделить имя сценария для редактирования;

• Щелкнуть по кнопке Изменить;

• Внести изменения в сценарий (его имя, адреса изменяемых ячеек и их значения);

• Для завершения работы с Диспетчером сценариев щелкнуть по кнопке Ок, потом – по кнопке Закрыть.

Для создания итогового отчета по сценариям:

• Выбрать команду Сервис Сценарии;

• Щелкнуть по кнопке Отчет;

• Выбрать тип отчета: структура или сводная таблица;

• В отчете Структура перечислены все сценарии с определенными для них значениями ячеек. Такой отчет полезен, когда каждый пользователь определяет сценарий со своими данными;

• Отчет типа Сводная таблица позволяет провести анализ имеющихся сценариев. Можно проанализировать результаты проведения поиска решения по разным наборам изменяющихся ячеек, провести анализ для разных комбинаций сценариев;

• В поле Ячейки результата ввести ссылки на ячейки, значения которых надо представить в отчете. В качестве разделителя ссылок используется запятая. Ссылки можно ввести с клавиатуры или выделить на рабочем листе. Несмежные ячейки вводятся при нажатой клавиш Ctrl. Итоговые таблицы создаются на отдельных листах.

.