Материал: 5442

31

Рисунок 20 – Измененная структура таблицы с отображением только итоговых значений

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

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

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

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

Сводные таблицы

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

32

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

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

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

Для выполнения команды Сводная таблица не требуется предварительная сортировка списка, поскольку команда Сводная таблица включает сортировку, фильтрацию и формирование итогов.

Создание сводной таблицы

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

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

Шаг 1. Пользователю будет предложен выбор источника данных.

Вдиалоговом окне Мастер сводных таблиц и диаграмм – шаг 1 из 3

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

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

33

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

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

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

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

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

Шаг 3. Указание местоположения сводной таблицы. В диалоговом окне

Мастер сводных таблиц и диаграмм – шаг 3 из 3 необходимо указать местопо-

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

После щелчка на кнопке Готово Excel отобразит на рабочем листе шаблон сводной таблицы (рисунок 21) и панели инструментов Сводные таблицы и Спи-

сок полей сводной таблицы. Перетащите поле Наименование товара из списка полей сводной таблицы в область строк, поле Дата – в область столбцов, и, наконец, поле Сумма – в область данных. Сводная таблица создана (рисунок 22)

34

Рисунок 21 – Создание шаблона сводной таблицы

Рисунок 22 – Вид сводной таблицы

35

В диалоговом окне Мастер сводных таблиц и диаграмм – шаг 3 из 3 имеются кнопки Макет и Параметры.

Щелчок на кнопке Макет открывает диалоговое окно Мастер сводных таблиц и диаграмм – макет (рисунок 23), в котором можно создать макет сводной таблицы. Точно так же, как при работе с шаблоном сводной таблицы, необходимо перетащить поля, находящиеся справа на рисунке, зажав каждое из них мышкой и поместив в нужное поле: столбец, строка, данные, страница. После этого закрыть диалоговое окно, щёлкнув на кнопке ОК. Затем щёлкнуть на

кнопке Готово. Excel вставит на рабочий лист готовую сводную таблицу с данными. Начиная с версии

Excel 2000 можно ис-

пользовать любой способ создания сводной таблицы.

Рисунок 23 – Создание сводной таблицы в окне диалога Макет

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

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

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