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

Для работы с данными в MS Excel предназначена вкладка Данные. В группе команд Получение внешних данных находятся команды для получения внешних данных из базы данных MS Access, из других книг и источников. В группе Подключения приведены команды для отображения и обновления существующих подключений. В группе Сортировка и фильтр находятся соответствующие команды для обработки списков. В группе Работа с данными находятся команды для обработки данных по принципу Что если, консолидации и проверки данных. В группе Структура находятся команды для группировки данных и подведения итогов по группе. В группе Анализ находятся команды для статистической обработки данных.

Таблица данных

Таблицей данных называется диапазон ячеек, содержащий формулу и показывающий, как изменение значений одного или двух ее переменных (значений данных) влияет на возвращаемый формулой результат. Таблицы данных обеспечивают возможность просмотра и сравнения полученных результатов при разных значениях переменных. Формулы в таблице данных ссылаются на ячейки ввода. Ячейки ввода содержат некоторые первоначальные значения переменных и не включаются в таблицы данных. Диапазон результатов создается Excel с помощью формулы массива.

9.1.1. Таблицы с двумя переменными

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

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

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

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

3. В строку ниже формулы (в ячейки этого же столбца) ввести возможные значения для первой переменной.

4. В столбец правее формулы (в ячейки этой же строки) ввести возможные значения для второй переменной.

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

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

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

8. Нажать клавишу < OK > и просмотреть полученный результат.

9. При необходимости можно изменить формулу или значения переменных.

10. Для построения диаграммы надо выделить таблицу результатов расчетов и нажать клавишу < F11 >. Это самый удобный и быстрый способ построения диаграмм с несколькими рядами данных. Удобство состоит в том, что формула содержится только в одной ячейке листа, нет необходимости ее копировать для всевозможный значений аргумента и параметров.

9.1.2. Таблицы данных с одной переменной

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

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

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

3. Правее первой формулы можно последовательно в ячейки ввести другие формулы, содержащие ссылки на те же входные ячейки.

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

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

6. Выполнить на вкладке Данные / команду Таблица данных.

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

8. Снять выделение таблицы данных и проанализировать диапазон результатов, который создан Excel с помощью формулы массива.

9. При необходимости можно выполнить редактирование формул в верхней строке или входных значений переменной в левом столбце.

10. Кроме того, диапазон результатов, созданный Excel, можно скопировать в другую часть листа или полностью очистить.