Средства анализа данных в таблицах в ms Excel. Анализ данных с помощью сводной таблицы

По прошествии некоторого времени после заполнения таблицы возникает необходимость в определении структуры доходов и расходов семьи. Это можно сделать двумя способами:

• с применением встроенных инструментов Excel;

• путем создания собственных средств анализа на основании формул.

Наиболее удобным встроенным инструментом для анализа данных в Excel является сводная таблица. Это вспомогательная таблица, построенная с помощью мастера сводных таблиц и анализирующая данные исходной таблицы. Исходной в данном случае является таблица, расположенная на листе ЖурналРегистрации (рис. 9.1).

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

• критерии, по которым производится анализ (к ним относятся столбцы Дата, Кто, Откуда/Куда, На что и Что именно),

• значения (находятся в столбцах Доход и Расход).

Для вызова мастера сводных таблиц выделите на рабочем листе диапазон, где находится таблица с журналом регистрации, и выполните команду Данные/Сводная таблица. На экране появится первое диалоговое окно Мастер сводных таблиц - шаг 1 из 4 (рис. 9.3).

Рис. 9.3. Первое диалоговое окно мастера сводных таблиц.

В диалоговом окне Мастер сводных таблиц - шаг 1 из 4 указывается источник данных для сводных таблиц. В области Создать таблицу на основе данных, находящихся расположены переключатели, указывающие, откуда поступают данные. Нас вполне устраивает заданный по умолчанию переключатель В списке или базе данных Microsoft Excel. При нажатии кнопки Далее открывается следующее диалоговое окно, Мастер сводных таблиц - шаг 2 из 4 (рис. 9.4).

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

Рис. 9.4. Второе диалоговое окно мастера сводных таблиц.

Pис. 9.5. Третье диалоговое окно мистера сводных таблиц.

Как видите, здесь определяется структура создаваемой таблицы. В центре диалогового окна расположены области:

• Строка - используется в качестве заголовка строки в сводной таблице;

• Столбец - служит заголовком столбца в сводной таблице;

• Страница - задает критерий выборки данных, по которым создается сводная таблица;

• Данные - рассчитанные итоговые значения.

Все заголовки исходной таблицы, расположенной на рабочем листе ЖурналРе-гистрации, отображены в правой части диалогового окна в виде кнопок. Каждую из этих кнопок с помощью мыши можно перетащить в любую из четырех перечисленных областей.

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

• заголовок поля Откуда/Куда - в область Страница;

• заголовок На что - в область Строка;

• заголовок Кто - в область Столбец;

• заголовок Расход - в область Данные.

При помещении кнопки с заголовком Расход в область Данные заголовок последней изменится на К-во значений по полю Расход.

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

После щелчка на заголовке Откуда/Куда, расположенном в области Страница диалогового окна Мастер сводных таблиц - шаг 3 из 4, появится диалоговое окно Вычисление поля сводной таблицы (рис. 9.6). Здесь, в списке Скрыть элементы, нужно выделить элементы, которые нет необходимости отражать в создаваемой сводной таблице. Например, в нашем списке присутствуют статьи доходов Альфа, Гонорар и т. д., а также статьи расходов Питание, Одежда и прочие. А как вы понимаете, элементы, которые указывают на источник дохода, не должны отображаться в создаваемой сводной таблице, фиксирующей расходы.

Рис. 9.6. Диалоговое окно Вычисление поля сводной таблицы.

В результате двойного щелчка на заголовке в области Данные диалогового окна Мастер сводных таблиц - шаг 3 из 4 вызывается следующее диалоговое окно, Вычисление поля сводной таблицы (рис. 9.7). В списке Операция укажите операцию, которая будет производиться в диапазоне ячеек, находящихся под данным заголовком. В нашем случае следует выбрать элемент Сумма. В поле Имябудет указано имя операции - Сумма по полю Расход.

Рис. 9.7. Диалоговое окно Вычисление поля сводной таблицы.

При нажатии кнопки Далее появляется диалоговое окно Мастер сводных таблиц - шаг 4 из 4 (рис. 9.8). В области Поместить таблицу впри создании новой таблицы активизируйте переключатель новый лист, в результате чего сводная таблица будет расположена на вновь созданном рабочем листе. Сводную таблицу можно поместить и на лист ЖурналРегистрации, активизировав предварительно переключательсуществующий лист.

Рис. 9.8. Четвертое диалоговое окно мастера сводных таблиц.

Щелкните на кнопке Параметры, и на экране появится диалоговое окно Параметры сводной таблицы (рис. 9.9). Оно состоит из двух областей:

• Формат - устанавливаются параметры форматирования и вид сводной таблицы;

• Данные - задаются параметры источника сводной таблицы.

После нажатия кнопки Готово четвертого диалогового окна мастера сводная таблица будет расположена на вновь созданном листе (Лист1), название которого можно изменить (рис. 9.10).

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

Рис. 9.10. Рабочий лист со сводной таблицей и панелью инструментов Сводные таблицы.

Щелкните на кнопке, расположенной в ячейке ВЗ, выберите элемент Питание, и ваша сводная таблица обновится. Теперь в ней будет представлена более детальная информация о затратах на питание, причем отдельно по каждому члену семьи. В одной книге Excel можно создать несколько сводных таблиц, обобщив данные самым различным образом.