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

 

Microsoft Excel содержит мощные средства анализа данных. К ним относятся:

- средства финансового анализа;

- средства статистического анализа;

- средства анализа «что-если».

Финансовый и статистический анализ производится с помощью соответствующих функций.

Большие возможности предоставляет анализ типа «что-если». В рамках этого анализа можно быстро изменять основные переменные и сразу же видеть результаты этих изменений. Автоматическое обновление вычислений обеспечивает интерактивную обратную связь с результирующими данными. Если для модели установлен автоматический пересчет, то можно изменить значение в ячейке и тут же увидеть результаты пересчета во всех ячейках, которые зависят от измененного значения.

Анализ данных может производиться следующими способами:

- Таблица подстановки;

- Создание сценариев

- Подбор параметра;

- Поиск решений.

Таблица данных или таблица чувствительности позволяет представить результаты формул в зависимости от значений одной или двух переменных, которые используются в этих формулах. Пиктограмма Анализ «что – если» / ТАБЛИЦА ПОДСТАНОВКИ может создать два типа таблиц данных: таблицу для одной переменной, которая проверяет воздействие этой переменной на несколько формул, или таблицу для двух переменных, которая проверяет их влияние на одну формулу.

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

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

Рис. 9.30. Применение таблицы подстановки с одной переменной.

Рис.9.31. Использование таблицы подстановки, ориентированной по строкам.

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

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

Рис. 9.32. Использование таблицы подстановки с двумя переменными.

Таблица данных является хорошим средством в относительно простых ситуациях, когда используются только одна или две переменных, но реальные задачи обычно имеют значительно больше неизвестных величин. При моделировании более сложных задач, имеющих до 32 переменных, можно обратиться к диспетчеру сценариев.

Модель «что-если» - это любой рабочий лист, в котором можно подставлять различные значения для переменных, чтобы увидеть их влияние на другие величины, которые вычисляются по формулам, зависящим от этих переменных.

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

Сценарий – это именованные комбинации значений, заданных для одной или нескольких изменяемых ячеек в модели «что-если».

Вызвать диалоговое окно Диспетчерсценариев можно пиктограммой Анализ «что – если». При работе с диспетчером сценариев можно:

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

2) Распространить модель «что-если» между членами рабочей группы, чтобы они моли добавить свои сценарии. Затем можно собрать версии и объединить все сценарии в отдельном листе.

3) Без труда отслеживать варианты сценария, т.к. диспетчер сценариев сохраняет дату и имя пользователя при каждом изменении сценария.

4) Использовать пароль для защиты сценариев от изменений и даже скрыть их.

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

6) Определить сценарий с помощью кнопки Сценарии на пользовательской панели инструментов.

Для создания сценария необходимо выполнить следующие действия:

- пиктограмма Анализ «что – если» / ДИСПЕЧЕР СЦЕНАРИЕВ;

- в окне диалога Диспетчерсценариев нажать кнопку Добавить;

- в окне Добавление сценария ввести имя сценария;

- в поле Изменяемые ячейки указать ячейки, которые будут изменяться. По умолчанию в этом поле выводится ссылка на ячейку или диапазон, которые был выделен перед выполнением команды, но его можно изменить;

- нажать кнопку ОК, чтобы создать первый сценарий;

- откроется окно диалога Значения ячеек сценария с полями для каждой изменяемой ячейки. Эти поля содержат значения, которые в данный момент выведены на рабочем листе. Эти значения можно изменить. В каждое поле можно ввести константу или формулу, например, увеличить значение переменной на какую-либо величину или в несколько раз;

- чтобы создать другой сценарий, нажать кнопку Добавить для возвращения в окне Добавление сценария. Так можно создать сколько угодно сценариев. После окончания создания сценариев нажать кнопку ОК для возврата в окно диспетчера сценариев, а затем Закрыть для возврата в рабочий лист (рис. 9.33).

Рис. 9.33 Диалоговое окно Диспетчер сценариев.

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

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

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

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

1) Отчет Структура сценария – это средство, позволяющее отслеживать все варианты. В этом отчете приводятся имя изменяемых и результирующих ячеек (если имена назначены этим ячейкам). Имена сценариев выводятся в качестве заголовков столбцов. А столбцы появляются в порядке определения сценариев. Изменяемые ячейки для каждого сценария выделяются серым цветом, что позволяет с первого взгляда определить, какими сценариями контролируются конкретные ячейки в модели «что-если». Выше и левее итогового отчета выводятся символы структуры, позволяющие отображать и скрывать детальные данные.

2) Отчет Сводная таблица по сценарию предоставляет дополнительные инструменты анализа «что-если», позволяя работать с элементами самого отчета. Сводная таблица является инструментом, позволяющим с помощью мыши выполнять прямые действия с ячейками для смешивания и сравнения различных сценариев в отчете и наблюдать получаемые при этом значения в ячейках результата. Этот инструмент особенно полезен для анализа сложных моделей, которые включают в себя сценарии с различными множествами изменяемых ячеек, созданных разными пользователями.

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

Excel содержит большой набор средств анализа данных. К ним, в частности, относится подбор параметра, позволяющий подобрать значения исходных данных, обеспечивающие желаемый результат.

Команда ПОДБОР ПАРАМЕТРА позволяет определить неизвестное значение (параметр), которое будет давать требуемый результат. Технология использования команды следующая:

• решить нужную задачу с каким-либо начальным значением параметра;

• выбрать пиктограмму Анализ «что – если»/ ПОДБОР ПАРАМЕТРА (рис. 9.34);

• в окне диалога Подбор параметра (рис. 9.35) в поле Установить в ячейке задать абсолютную ссылку на ячейку, содержащую расчетную формулу, а в поле Значение – то значение, которое следует получить в качестве результата формулы;

• в поле Изменяя значение ячейки ввести ссылку на ячейку с параметром;

• нажать кнопку ОК или клавишу Enter, на экране появится окно диалога Результат подбора параметра;

• для сохранения найденного значения нажать кнопку ОК. Для восстановления значения, которое было в ячейке с параметром до использования команды Подбор параметра нажать кнопку Отмена.

Рис. 9.34. Вызов диалогового окна Подбор параметра.

Рис. 9.35. Диалоговое окно Подбор параметра.

При подборе параметра Excel использует итерационный процесс. Он проверяет для изменяемой ячейки одно значение за другим, пока не получит нужное решение. Если задача подбора параметра занимает много времени, можно нажать кнопку Пауза в окне диалога Результат подбора параметра и прервать вычисление, а затем нажать кнопку Шаг, чтобы просмотреть результаты последовательных итераций.

По умолчанию команда Подбор параметра прекращает вычисления, когда выполняется 100 итераций или при получении результата, который находится в пределах 0,001 от заданного целевого значения.

Команда Подбор параметра находит только одно решение, даже если задача имеет несколько решений.

Поиск решений может применяться для решения задач, которые включают много изменяемых ячеек, и помогает найти комбинацию переменных, которые максимизируют или минимизируют значение в целевой ячейке. Он также позволяет задать одно или несколько ограничений условий, которые должны выполняться при поиске решений. Для запуска этого инструмента следует в диалоговом окне ПараметрыExcel в разделе Надстройки окна Управления выбрать Надстройки Excel. Нажать кнопку ПЕРЕЙТИ и в диалоговом окне Доступныенадстройки выбрать Поиск решений. Чтобы вызвать диалоговое окно Поискрешения, нужно выбрать пиктограмму Поиск решения группы Анализ вкладки ДАННЫЕ (рис 9.36).

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

В диалоговом окне Поиск решения в поле Установить целевую ячейку задается цель, которую должен достичь поиск решения. Например, значение в указанной ячейке должно стать равным минимальному значению. Целевая ячейка в поле Установить целевую ячейку может быть задана ссылкой или именем. Поиск решения может искать конкретное значение целевой функции, тогда это значение нужно задать в поле Установить целевую ячейку, установив переключатель Равной в положение Значению. В этом случае, задав только изменяемую ячейку без указания ограничений, можно использовать ПОИСК РЕШЕНИЯ вместо команды ПОДБОР ПАРАМЕТРА.

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

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

Последний шаг определения поиска решений – задание ограничений. Он не является обязательным. Чтобы задать ограничения, следует в окне ПОИСК РЕШЕНИЯ нажать кнопку Добавить и заполнить окно диалога Добавление ограничений. Ограничение состоит из трех компонентов: ссылки на ячейку, оператора сравнения и значения ограничения. В левой части от оператора сравнения кроме ссылки на ячейку может также задаваться ссылка на диапазон. В правой части может задаваться диапазон (той же размерности, что и в левой части), ссылка на ячейку или константное значение.

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

Найденные результаты решения задачи могут быть нецелыми. Их можно округлить или добавить новые ограничения, при которых результаты должны быть целыми числами. В последнем случае в качестве операции сравнения в окне Добавление ограничений следует выбрать Цел. Использование целочисленных ограничений в задаче поиска решений может увеличить сложность задачи в геометрической прогрессии., что приводит к существенным временным затратам. Некоторые задачи могут быть решены только при использовании целочисленных ограничений. В частности, целочисленные решения являются обязательными для задач, в которых переменные могут принимать только два значения, например 0 и 1. В этом случае в окне диалога Добавление ограничения можно использовать оператор сравнения Двоич.

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

1) Выбрать пиктограмму Поиск решения группы Анализ вкладки ДАННЫЕ.

2) Нажать кнопку Параметры, а затем в окне диалога Параметры поиска решения нажать кнопку Сохранить модель. Excel попросит указать ячейку или диапазон рабочего листа, в котором нужно сохранить параметры поиска решения.

3) Задать пустую ячейку, щелкнув на ней или введя ссылку, затем нажать кнопку ОК. Если задана одна ячейка, ПОИСК РЕШЕНИЯ вставит сохраняемые параметры поиска в лист, начиная с заданной ячейки. Если задан диапазон, ПОИСК РЕШЕНИЯ заполнит параметрами модели только заданные ячейки. Если диапазон слишком мал, некоторые из параметров не будут сохранены.

4) Чтобы снова использовать сохраненные параметры, следует нажать кнопку Параметры в окне диалога Поиск решения, затем нажать кнопку Загрузить модель и задать диапазон, в котором сохранена модель поиска решений.

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

Окно диалога Параметры поиска решений дает возможность задать еще ряд показателей, управляющих процессом поиска:

1) С помощью полей Максимальное время и Предельное число итераций можно указать ПОИСКУ РЕШЕНИЙ, сколько усилий он должен приложить для решения задачи. Если поиск решений достигает предела по времени или числу итераций до нахождения нужного результата, Excel спрашивает, нужно ли продолжать поиск решения. Используемые по умолчанию величины обычно достаточны для решения большинства задач, но если решения получить не удается, можно попробовать их подобрать.

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

3) Допустимое отклонение служит для задания допуск на отклонение от оптимального решения и применяется только в задачах с целочисленными ограничениями.

4) Переключатели Оценки, Разности и Метод поиска позволяют уточнить применяемые методы оптимизации, обычно используются значения, установленные по умолчанию.

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

6) Флажок Показывать результаты итераций позволяет исследовать различные комбинации значений изменяемых ячеек, а не только комбинацию, дающую оптимальный результат. После каждой итерации будет открываться окно диалога Текущее состояние поиска решений, которое позволяет сохранить сценарий, прекратить поиск или продолжить его со следующей итерации. Следует иметь ввиду, что промежуточные результаты могут не удовлетворять всем заданным ограничениям.

ПОИСК РЕШЕНИЙ может представлять свои результаты в виде трех отчетов: Результаты, Устойчивость и Пределы. Для генерации одного или нескольких отчетов следует выбрать их названия в окне диалога Результаты поиска решений (рис.9.49). Каждый отчет сохраняется на отдельном листе текущей книги, а имена отчетов отображаются на ярлычках.

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

Отчет по результатам содержит целевую ячейку, список изменяемых ячеек и ограничений. В этом отчете для каждого ограничения выводятся «статус» и «разница». Разница – это разность между значением, выводимым в ячейке ограничения при получении решения, и числом, заданным в правой части формулы ограничения. Статус может принимать три состояния: Связанное (ограничение, для которого значение разницы равно 0), Несвязанное (ограничение, которое было выполнено с ненулевым значением разницы) и Невыполненное.

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

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

1) Поиск решения не может найти подходящего решения. Это означает, что не найдено решение, удовлетворяющее всем ограничениям. Это может произойти из-за того, что ограничения логически противоречивы или не все ограничения могут быть выполнены. В некоторых случаях ПОИСК РЕШЕНИЙ выводит это сообщение, если начальные значения изменяемых ячеек находятся слишком далеко от их оптимальных значений. Если пользователь считает, что заданные ограничения логически правильны и задача имеет решение, можно попробовать изменить начальные значения и повторить поиск решения.

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

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

Контрольные вопросы:

1) Что такое электронная таблица и каковы ее основные функции?

2) Типы данных, используемые в электронных таблицах.

3) Что называется ячейкой?

4) Что такое рабочий лист и рабочая книга?

5) Что такое формула?

6) Что называется ссылкой?

7) Какие бывают ссылки? Дать определения.

8) Что необходимо для получения возможности вводить данные и выполнять команды в Excel?

9) Как выделить блок ячеек?

10) Как выделить строку?

11) Как выделить весь лист?

12) Какие средства редактирования данных в Excel вам известны?

13) Какие операции можно выполнять над рабочими листами книги?

14) Как называется процесс расчета формул с последующим выводом результатов в виде значений в ячейках?

15) Что такое циклическая ссылка?

16) Что такое функция?

17) Чем отличается формула от функции?

18) Что позволяет функция?

19) Что такое форматирование рабочего листа?

20) Перечислите параметры форматирования рабочего листа.

21) Что такое условное форматирование и для чего оно предназначено?

22) Перечислите средства защиты данных в электронной таблице.

23) Для чего защищают данные в таблицах?

24) Как скрыть содержимое рабочего листа?

25) Что такое список?

26) Из каких структурных элементов состоит список?

27) Что позволяет использование формы данных?

28) Почему нельзя отредактировать поля содержащие формулы?

29) Что такое сортировка списков?

30) Как называется способ быстрого выделения из списка данных для последующей работы с ними?

31) Перечислите способы фильтрации данных.

32) Для чего предназначен расширенный фильтр?

33) Какие существуют критерии отбора расширенного фильтра?

34) Что такое консолидация?

35) Перечислите способы консолидирование данных. Назначение каждого способа.

36) Для чего предназначена сводная таблица ?

37) Назначение команды Подбор параметра.

38) Для решения каких задач применяется «Поиск решения»?

39) Как построить диаграмму?

40) Перечислите основные компоненты диаграммы?

41) Какие типы диаграмм вы знаете, для использования интерпретации данных электронных таблиц?