В зависимости от вида источника изменяются последующие этапы работы по созданию сводной таблицы. Рассмотрим наиболее распространенный случай использования списков.
Консолидация с помощью формул.При консолидации с помощью формул в ссылках указывается название книги (если в разных книгах), название листа (если в разных листах), и диапазон. Например, [книга1] лист3!D5:[книга2]лист4!C8 или Лист2!D3:Лист 4!E6
Консолидация электронных таблиц или данных.
Организация межтабличных связей.
Структурирование таблицы с автоматическим подведением итогов.
Команда Данные\Итоги создает структуру таблицы и одновременно вставляет строки промежуточных и общих итогов для выбранных столбцов в соответствии с заданной функцией.
Обязательно: для получения итогов по группам необходимо заранее упорядочить строки списка с помощью команды Данные\Сортировка. Подведение итогов выполняется при изменении значений в столбце, который образует группы.
Команда Данные\Итоги может использоваться для одного и того же списка многократно, и можно заменять текущие итоги.
Столбец, при изменении значений в котором образуются группы, выбирается из списка посредством параметра При каждом изменении в. Перечень столбцов, по которым подводятся итоги, указывается в поле Добавить итоги по.
Итоги подводятся либо под данными, либо над данными установкой флажка Итоги под данными. Принудительно группы могут размещаться на отдельных печатных страницах (параметр Конец страницы между группами). Кнопка Убрать все удаляет все итоги из текущего списка.
При работе с электронными таблицами часто требуется их объединить. Если возникает необходимость обработать данные, расположенные в различных областях рабочего листа или в различных рабочих книгах, удобно обратиться к процедуре консолидации (термин консолидация означает объединение или упрочнение чего-либо).
Существует 3 способа объединения:
1. Организация межтабличных связей.
2. Консолидация электронных таблиц или их частей.
3. Объединением файлов.
Связи между таблицами осуществляются путем использования внешних ссылок (адресов ячеек), содержащих помимо имени столбца и номера строки имя файла, данные из которого используются. Например: [Имя книги] имя листа!диапазон
Таблица, в ячейках которой есть внешние ссылки на другие таблицы, считается основной. При загрузке этой основной таблицы необходимо загрузить все связанные с ней вспомогательные таблицы. Иначе появится сообщение об ошибке.
Помимо создания межтабличных связей многие ЭТ предлагают специальный режим консолидации. Этот режим содержит необходимые команды для объединения таблиц, расположенных как на одном листе, так и на разных листах или же на разных рабочих книгах. В консолидации могут участвовать до 255 областей-источников.
Существуют следующие варианты консолидации данных:
ü с помощью формул, где используются ссылки;
ü по расположению данных для одинаково организованных областей-источников (фиксированное расположение);
ü по категориям для различающихся по своей структуре области данных;
ü с помощью сводной таблицы.
Консолидация по расположению данных.При консолидации по расположению данных все источники имеют одинаковое расположение данных источников. Для консолидации курсор устанавливается в область места назначения. Выполняется команда Данные\Консолидация, выбирается вариант, и задаются условия консолидации.
Консолидация по категориям.При консолидации по категориям области-источники содержат однотипные данные, но организованные в различных областях-источниках неодинаково. Для консолидации по категориям используются имена строк или столбцов (имена включаются в выделенные области-источники). Выполняется командой Данные\Консолидация. Условия консолидации задаются в диалоговом окне Консолидация. В окне Функция выбирается функция. Для каждой области-источника строится ссылка, для чего курсор устанавливается в поле ссылки, затем переходят в область источника для выделения блока ячеек и нажимается кнопка Добавить.
Консолидация с помощью сводной таблицы.В табличном процессоре имеется чрезвычайно полезное средство анализа и обработки данных – сводные таблицы. С помощью сводных таблиц можно быстро и без труда обобщать большие объёмы данных. В результате громоздкие списки могут быть представлены в обозримом и удобном виде.
Команда Данные\Сводная таблица вызывает Мастер сводных таблиц для построения сводов-итогов определенных видов на основе списков, других сводных таблиц, внешних баз данных и т.д. Сводная таблица обеспечивает различные способы объединения информации. Мастер сводных таблиц осуществляет построение сводной таблицы в несколько этапов:
1) Указание вида источника сводных таблиц:
использование списка
использование внешнего источника данных
использование нескольких диапазонов консолидации
использование данных из другой сводной таблицы
Указание диапазонов, содержащих исходные данные. Список обязательно должен содержать имена полей (столбцов). Полное имя диапазона записывается в виде:
Если предварительно установить курсор в список, то диапазон будет автоматически указан. Для ссылки на закрытый диапазон другой рабочей книги нажимается кнопка Обзор, выбирается диск, каталог и файл закрытой рабочей книги, вводится имя рабочего листа и диапазон.
3) Построение макета сводной таблицы. Структура сводной таблицы состоит из следующих областей, определяемых в макете:
Страница - на ней размещаются поля, значения которых обеспечивают отбор записей на 1 - м уровне. На странице может быть размещено несколько полей, между которыми устанавливается иерархия связи- сверху вниз; страницу определять необязательно.
Столбец - поля размещаются слева направо, обеспечивая группировку данных сводной таблицы по иерархии полей; при условии существования области страницы или строки, определять столбец необязательно.
Строка - поля размещаются сверху вниз, обеспечивая группировку данных таблицы по иерархии полей; при существовании страницы или столбца определять строку необязательно.
Данные – поля, по которым подводятся итоги согласно выбранной функции; эту область определять обязательно.
Размещение полей выполняется путём перетаскивания в определенную область макета. Каждое поле размещается только 1 раз в областях страницы, строки или столбца. По этим полям можно формировать группы и получать итоговые значения в области данные-группировочные поля.
Для изменения структуры сводной таблицы выполняется перемещение полей одной области в другую.
В макете сводной таблицы можно выполнить настройку параметров полей, размещенных в области данных. Эта настройка осуществляется с помощью диалогового окна Вычисление поля сводной таблицы. Для этого нужно установить курсор на настраиваемое поле и дважды нажать левой кнопкой мыши для вызова диалогового окна Вычисление поля сводной таблицы, в котором можно переименовать поле, изменить операцию или изменить формат числа. Кнопка Дополнительно вызывает панель Дополнительные вычисления для выбора функций.
Выбор места расположения и параметров сводной таблицы (на новом или существующем листе). После нажатия кнопки Готово будет сформирована сводная таблица со стандартным именем.
Кнопка Параметры в диалоговом окне последнего шага вызывает диалоговое окно, в котором устанавливается вариант вывода информации в сводной таблице:
общая сумма по столбцам – внизу сводной таблицы выводятся общие итоги по столбцам.
общая сумма по строкам – формируется итоговый столбец.
сохранить данные вместе с таблицей - сохраняется не только макет, но и результат построения сводной таблицы.
Страница - на ней размещаются поля, значения которых обеспечивают отбор записей на 1 - м уровне. На странице может быть размещено несколько полей, между которыми устанавливается иерархия связи- сверху вниз; страницу определять необязательно.
Данные – поля, по которым подводятся итоги согласно выбранной функции; эту область определять обязательно.