ФОРМИРОВАНИЕ СВОДНОЙ ИНФОРМАЦИИ. КОНСОЛИДАЦИЯ ДАННЫХ И СВОДНЫЕ ТАБЛИЦЫ

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

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

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

· формированием сводных таблиц. Простейший способ получения итогов — с помощью команды Итоги меню Данные.

Для выполнения этой команды необходимо:

· представить данные в виде списка;

· командой Сортировка меню Данные упорядочить записи списка в соответствии со значениями того поля, по которому будут подводиться промежуточные итоги;

· установить указатель на ячейку списка;

· выполнить команду Итоги меню Данные;

· в диалоговом окне Промежуточные итоги задать нужные параметры.

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

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

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

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

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

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

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

· на листе консолидации (итоговом листе) создать (или скопировать) надписи для данных консолидации;

· указать ячейку на листе консолидации, куда следует поместить результат консолидации;

· ввести формулу, которая должна содержать ссылки на консолидируемые исходные области листов, данные которых будут участвовать в консолидации;

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

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

· указать левую верхнюю ячейку области размещения консолидируемых данных;

· выполнить команду Консолидация меню Данные;

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

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

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

Обрабатывать большие объемы информации и составлять сложные многоуровневые отчеты достаточно непросто без использования средств автоматизации. Excel 2010 как раз и является инструментом, позволяющим упростить эти задачи, путем создания сводных (перекрестных) таблиц данных (Pivot table). Сводная таблица в Excel 2010 используется для: выявления взаимосвязей в большом наборе данных; группировки данных по различным признакам и отслеживания тенденции изменений в группах; нахождения повторяющихся элементов, детализации и т.п.; создания удобных для чтения отчетов, что является самым главным.

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

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

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

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

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

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

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

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

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

1. выполнить команду Обновить данные меню Данные;

2. выполнить команду Обновить данные контекстного меню.

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

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

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

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

Мастер сводных таблиц автоматически группирует элементы внутреннего поля для каждого заголовка внешнего поля. Но иногда удобнее группировать элементы другим способом. Excel предлагает несколько вариантов группировки элементов. Создание групп элементов производится с помощью команды Гриппа и структура — Группировать меню Данные или при нажатии кнопки Группировать на панели инструментов Сводные таблицы.

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

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

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

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

Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица» Перед тем как создавать отчет сводной таблицы, определимся, что будет использоваться в качестве источника данных. Рассмотрим вариант с источником, находящимся в этом же документе.

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

2. Откройте вкладку «Вставка» и выберите из раздела «Таблицы» инструмент «Сводная таблица». Если вместе со сводной таблицей нужно создать и сводную диаграмму – нажмите на стрелку в нижнем правом углу значка «Сводная таблица» и выберите пункт «Сводная диаграмма».

3. В открывшемся диалоговом окне «Создание сводной таблицы» выберите только что созданную таблицу с данными или ее диапазон. Для этого выделите нужную область. В качестве данных для анализа можно указать внешний источник: установите переключатель в соответствующее поле и выберите нужное подключение из списка доступных.

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

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

5. После подтверждения действия нажатием кнопки «ОК», будет создан и открыт макет отчета.

Рассмотрим его.

В правой половине окна создается панель основных инструментов управления - «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет». Отметьте необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.

Рис. 2.170 Макет отчета

Расположением полей можно управлять – делать их названиями строк или столбцов, перетаскивая в соответствующие окна, а так же и сортировать в удобном порядке. Можно фильтровать отдельные пункты, перетащив соответствующее поле в окно «Фильтр». В окно «Значение» помещается то поле, по которому производятся расчеты и подводятся итоги.

Рис. 2.171 Управление полями сводной таблицы

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

Способ 2. Создание сводной таблицы с использованием инструмента «Мастер сводных таблиц и диаграмм».

Чтобы применить этот способ, придется сделать доступным инструмент, который по умолчанию на ленте не отображается.

Откройте вкладку «Файл» - «Параметры» - «Панель быстрого доступа». В списке «Выбрать команды из» отметьте пункт «Команды на ленте». А ниже, из перечня команд, выберите «Мастер сводных таблиц и диаграмм». Нажмите кнопку «Добавить». Иконка мастера появится вверху, на панели быстрого доступа.

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

Мастер сводных таблиц и диаграмм Мастер сводных таблиц в Excel 2010 совсем не многим отличается от аналогичного инструмента в Excel 2007. Для создания сводных таблиц с его помощью выполните следующее.

1. Кликните по иконке мастера в панели быстрого допуска. В диалоговом окне поставьте переключатель на нужный вам пункт списка источников данных:

· «в списке или базе данных Microsoft Excel» - источником будет база данных рабочего листа, если таковая имеется;

· «во внешнем источнике данных» - если существует подключение к внешней базе, которое нужно будет выбрать из доступных;

· «в нескольких диапазонах консолидации» - если требуется объединение данных из разных источников;

· «данные в другой сводной таблице или сводной диаграмме» - в качестве источника берется уже существующая сводная таблица или диаграмма.

Рис. 2.173 Мастер сводных таблиц и диаграмм, шаг 1

2. После этого выбирается вид создаваемого отчета – «сводная таблица» или «сводная диаграмма (с таблицей)».

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

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

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

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

Рис. 2.174 Мастер сводных таблиц и диаграмм, шаг 26

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

· При выборе внешнего источника данных используется приложение Microsoft Query, входящее в комплект поставки Excel 2010 или, если требуется подключиться к данным Office, используются опции вкладки «Данные».

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

Поиск по сайту: