Занятие 4

Учебные вопросы:

1. Анализ и обобщение данных с помощью сводных таблиц и сводных диаграмм

Краткая теоретическая часть.

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

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

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

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

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

Кроме того, сводные таблицы и сводные диаграммы можно создавать:

• на основе таблиц, полученных в результате консолидации данных;

• на основе других сводных таблиц;

• на основе внешних источников данных.

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

Создание и модификация сводных таблиц и сводных диаграмм выполняются с помощью Мастера сводных таблиц и диаграмм, окно которого появляется на экране после ввода команды меню ДАННЫЕ ► Сводная таблица или щелчка по кнопке Мастер сводных таблиц на панели инструментов Сводные таблицы.

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

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

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

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

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

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

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

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

В области Строка и Столбец помещают поля, которые должны быть представлены в сводной таблице.

В области Страница, Строка и Столбец каждое поле может помещаться только один раз.

Для того чтобы удалить поле из области построения, его кнопку нужно просто перетащить за пределы области построения.

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

• Сумма (по умолчанию);

• Количество значений;

• Среднее;

• Максимум и др.

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

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

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

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

• щелкнуть мышью по сводной таблице или отчету сводной диаграммы;

• на панели инструментов Сводные таблицы щелкнуть по кнопке Сводная таблица или Сводная диаграмма и выбрать в раскрывающемся списке команду Формулы ► Вычисляемое поле;

• щелкнуть по кнопке Добавить, а затем - по кнопке ОК;

• расположить вычисляемое поле в требуемом месте отчета.

На основе готовой сводной таблицы можно построить сводную диаграмму с помощью Мастера диаграмм или пункта контекстного меню сводной таблицы Сводная диаграмма. Сводная диаграмма будет помещена на отдельный рабочий лист.

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

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

• ввести команду меню ДАННЫЕ ► Сводная таблица;

• щелкнуть по кнопке Мастер сводных таблиц на панели инструментов Сводные таблицы;

• ввести команду контекстного меню сводной таблицы Мастер.

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

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

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

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

Для удаления всей сводной таблицы нужно:

• установить курсор в любую ячейку сводной таблицы и на панели инструментов Сводные таблицы открыть список Сводная таблица;

• выбрать команду Выделить ► Таблица целиком при активированной кнопке Разрешить выделение;

• ввести команду меню окна Excel ПРАВКА ► Очистить ► Все.

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