Формирование сводной информации
Excel содержит средства формирования сводной информации для проведения анализа данных.
Сводная информация может быть получена:
• объединением данных с помощью промежуточных итогов;
• методом консолидации;
• формированием сводных таблиц.
Для вычисления промежуточных итогов используется команда Промежуточные итоги пиктограммы Структура группы Структура вкладки ДАННЫЕ. Для выполнения этой команды необходимо:
• представить данные в виде списка;
• пиктограммой СОРТИРОВКА упорядочить записи списка в соответствии со значениями того поля, по которому будут подводиться промежуточные итоги;
• установить указатель на ячейку списка;
• выполнить команду Промежуточные итоги;
• в диалоговом окне Промежуточные итоги (рис. 9.25) задать нужные параметры.
Рис. 9.25. Диалоговое окно Промежуточные итоги.
После выполнения команды создается структура, в которой данные (таблица) структурированы, т.е. разбиты на несколько уровней (рис. 9.26). С помощью уровней структуры можно управлять выводом данных соответствующего уровня из таблицы на экран, указывая, выводить данные или скрывать.
Рис 9.26. Подсчет промежуточных итогов.
Для удаления промежуточных итогов необходимо в диалоговом окне команды Итоги выбрать кнопку Убрать все. При удалении промежуточных итогов из списка удаляется и структура таблицы.
Консолидация – это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе. В Excel предусмотрено несколько способов консолидации данных:
• консолидация данных с помощью трехмерных ссылок;
• консолидация данных по положению;
• консолидация данных по категориям;
• отчет сводной таблицы.
Первый способ наиболее простой. Он позволяет объединить данные консолидируемых областей формулами. Технология выполнения консолидации с помощью трехмерных ссылок такова:
• на листе консолидации (итоговом листе) создать (или скопировать) надписи для данных консолидации;
• указать ячейку на листе консолидации, куда следует поместить результат консолидации;
• ввести формулу, которая должна содержать ссылки на консолидируемые исходные области листов, данные которых будут участвовать в консолидации;
• повторить два последних шага для каждой ячейки, в которую должен быть помещен результат консолидации.
Консолидация данных по расположению используется, если консолидируемые данные находятся в одном и том же месте разных листов и размещены в одном и том же порядке.
Если была выполнена консолидация данных по значению или по категории, то при изменениях в исходных областях следует повторить консолидацию. Можно избежать повторения консолидации в случае изменения исходных данных путем связывания консолидируемых данных с исходными данными. Для установки связей необходимо в диалоговом окне Консолидация, которое вызывается пиктограммой Консолидация группы Работа с данными вкладки ДАННЫЕ (рис. 9.27) установить параметр Создавать связи с исходными данными. Установка параметра означает, что между исходными данными и результатами консолидации устанавливается динамическая связь, обеспечивающая автоматическое обновление данных. Автоматическое обновление данных происходит, если исходные данные находятся в пределах одной книги.
Рис.9.27. Диалоговое окно Консолидация
Сводная таблица – это таблица, предназначенная для более наглядного представления и анализа данных из существующих списков и таблиц.
Сводная таблица может быть создана:
• на основе данных любой таблицы или области таблицы рабочего листа;
• на основе данных нескольких таблиц, полученных в результате консолидации данных;
• на основе данных сводной таблицы;
• на основе данных, находящихся во внешнем источнике данных Microsoft Access, FoxPro, dBase и т.д.
Для создания сводной таблицы необходимо выделить источник данных и щелкнуть левой мышью на пиктограмму Сводная таблица группы Таблица вкладки ВСТАВКА. Перед построением сводной таблицы из источника данных обязательно должны быть убраны промежуточные итоги и наложенные фильтры. В диалоговом окне Созданиесводнойтаблицы можно выбрать данные для анализа -это Таблица или диапазон или Использовать внешний источник подключения, а так же указать куда следует поместить отчет сводной таблицы (рис. 9.28).
Рис. 9.28. Диалоговое окно Создание сводной таблицы.
После нажатия кнопки ОК появляется отчет сводной таблицы (рис.9.29).
Рис. 9.29. Созданный отчет сводной таблицы.
Изменение исходных данных не приводит к автоматическому обновлению сводной таблицы. Чтобы обновить сводную таблицу используется пиктограмма Обновить группы Данные вкладки ПАРАМЕТРЫ.
Если одна сводная таблица служит источником данных для другой, то при обновлении любой из них пересчитываются обе таблицы.
Для изменения внешнего вида ячеек сводной таблицы можно использовать группы инструментов вкладки КОНСТРУКТОР, которая появляется при работе со сводной таблицей.
Изменение параметров сводной таблицы обеспечивают команды, находящиеся на вкладки ПАРАМЕТРЫ, которая появляется при работе со сводной таблицей.
По умолчанию Excel генерирует общие итоги для всех внешних полей сводной таблицы, и промежуточные итоги для всех внутренних полей за исключением самого внутреннего поля. Пользователь может, как удалить создаваемые по умолчанию итоги, так и сгенерировать промежуточные итоги для самых внутренних полей.
К числовым полям, помещенным в область данных сводной таблицы, Excel по умолчанию применяет функцию Сумма (изменить функцию в диалоговом окне Параметрыполейзначений), а к любым нечисловым полям - Количество значений. Но можно применять и другие формы вычислений. Кроме того, в сводных таблицах можно использовать вычисляемые поля и элементы. Вычисляемое поле – это новое поле, полученное с помощью операций над существующими полями сводной таблицы. Вычисляемые элемент – это новый элемент в существующем поле, полученный с помощью операций над другими элементами этого поля. После создания вычисляемых полей и элементов их можно использовать так, как будто они являются частью исходных данных. При создании вычисляемых полей и элементов можно использовать арифметические операции с любыми данными сводной таблицы (включая данные, генерируемые другими вычисляемыми полями и элементами), но нельзя ссылаться на данные рабочего листа, находящиеся вне таблицы.