Построение сводных таблиц

Цель - познакомить с работой электронных таблиц как баз данных, методологией их использования в профессиональной работе.

1. Обычно базы данных представляют собой набор взаимосвязанных таблиц. Простейшие базы данных состоят из одной таблицы. В качестве такой базы данных вполне можно использовать электронную таблицу Excel. Программа Excel включает набор функций, позволяющих выполнять все основные операции, присущие базам данных. Информация в базе данных состоит из набора записей, каждая из которых содержит один и тот же набор полей. Записи характеризуются порядковыми номерами, а каждое поле имеет заголовок, описывающий его назначение. Чтобы содержимое рабочего листа рассматривалось как база данных Excel, необходимо придерживаться строгих правил. Каждому полю записи соответствует один столбец рабочего листа. Столбцы базы данных должны идти подряд, без промежутков между ними. В первой строке каждого столбца (точнее, в той строке, с которой начинается база данных) должен быть указан заголовок соответствующего поля. Заголовок поля должен занимать не более одной ячейки. Содержимое ячейки заголовка должно быть уникально в пределах рабочего листа.

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

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

Рис. 1. Управление сортировкой базы данных

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

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

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

По умолчанию используется вариант Все, указывающий, что записи базы данных должны отображаться без фильтрации. Вариант Первые 10 позволяет отобрать определенное число (или процент) записей по какому-либо критерию. Вариант Усло­вие позволяет задать специальное условие фильтрации. Кроме того, имеется воз­можность отбора записей, имеющих в нужном поле конкретное значение.

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

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

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

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

При выборе расширенной фильтрации командой Данные ► Фильтр ► Расширенный фильтр можно выполнить фильтрацию на месте или извлечь отфильтрованные записи и поместить их отдельно, на любой рабочий лист любой открытой рабочей книги.

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

2. Построение сводной таблицы.

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

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

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

Каждой области сводной таблицы должны соответствовать одно или несколько полей базы данных. Исключение делается только для поля Страница, которое можно оставить пустым. При заполнении сводной таблицы данные из соответствующих полей берутся автоматически. Для заполнения этих полей служат кнопки, назва­ния которых совпадают с названиями полей базы данных. Поместить кнопку в нужное поле можно методом перетаскивания. В сводной таблице можно исполь­зовать только те поля, которые необходимы. Незадействованные поля в отборе и вычислениях не участвуют.

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

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

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

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

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

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

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

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

Вопросы для самоконтроля: