Формирование сводной информации

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

• объединением данных промежуточных итогов;

• методом консолидации;

• формированием сводных таблиц.

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

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

Консолидация – это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе. В Excel предусмотрено несколько способов консолидации данных:

• консолидация данных с помощью трехмерных ссылок;

• консолидация данных по расположению;

• консолидация данных по категориям.

Первый способ наиболее простой. Он позволяет объединить данные консолидируемых областей формулами.

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

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

Сводная таблица – это таблица, предназначенная для более наглядного представления и анализа данных из существующих списков и таблиц.

Сводная таблица может быть создана:

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

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

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

• на основе данных, находящихся во внешнем источнике данных Microsoft Access, FoxPro, dBase и т. д.

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

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

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

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

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

2. Переместите поле в область фильтра отчета в списке полей, одновременно с этим в область фильтра отчета будет перемещено поле в отчете сводной таблицы.

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

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

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

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

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