Создание и редактирование сводных таб-лиц в программе Excel

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

Задание 1. Изучим возможность использования сводных таблиц на практическом примере.

1. Загрузите программу MS Excel. Переименуйте Лист 1, присвоив ему новое имя - Исходные данные.

2. На листе Исходные данные постройте таблицу по приведенной ниже форме.

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

4. Во втором окне мастера сводных таблиц определяется диапазон ячеек, данные из которого будут включены в сводную таблицу. Если перед запуском мастера указатель ячейки находился внутри таблицы, то программа автоматически вставит в поле Диапазон адрес всей таблицы. Если данные для сводной таблицы находятся в другой книге, следует нажать кнопку Обзор, чтобы попасть в диалоговое окно выбора файла, и загрузить нужную книгу. Укажите диапазон ячеек ($A$2:$E$12), на основе которого должна быть составлена сводная таблица, и перейдите в следу-ющее окно мастера, нажав кнопку Далее.

5. В третьем окне мастера сводных таблиц определяется структура создаваемой таблицы. В центре этого диалогового окна расположены области строк, столбцов, страниц и данных. Все заголовки (метки) полей таблицы отображаются справа от перечисленных областей. Каждый заголовок поля можно переместить в любую область (в сводную таблицу). Переместите поле Продавец в область полей страниц. Таким образом, вы зададите размещение данных о каждом продавце на «отдельной странице». Имена продавцов будут находиться в поле списка. Для отображения на экране данных о каком-либо продавце нужно будет выбрать его имя в списке.

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

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

Выполнение установок в диалоговых окнах мастера сводных таблиц следует завершить нажатием кнопки Готово. В результате будет создана сводная таблица в соответствии с установленными параметрами. Новому листу с созданной вами сводной таблицей присвойте новое имя - Сводная таблица.

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

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

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

8. Выполненную работу сохранить на личном диске в файле под именем table-1.xls.

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

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

2. Изменение вида сводной таблицы выполняется путем перемещения поля данных в новую позицию. Таким образом, в область полей страницы можно поместить несколько полей, содержимое которых будет использоваться в качестве критерия при «разбиении» данных на отдельные страницы.

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

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

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

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

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

После нажатия кнопки ОК в диалоговом окне Отобразить детали поле данных будет перемещено из области полей страниц в область полей столбцов. Результатом будет «расшифровка» оборота за день для каждого продавца.

3. Измененную сводную таблицу сохранить на личном диске в файле под именем table-2.xls.