Консолидация 76

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

Цели:

Освоить технологии:

  • консолидации данных;
  • создания сводных таблиц.

4.5.1. Консолидация данных
4.5.2. Сводные таблицы

Версия для печати
Хрестоматия
Практикумы
Презентации

4.5.1. Консолидация данных

Консолидация данных является способом получения итоговой информации (Рис. 1), которая выполняется в соответствии с выбранной функцией обработки.

Консолидация – агрегирование (объединение) данных, представленных в исходных областях–источниках.

Результат консолидации находится в области назначения. Таблица консолидации создается путем применения функции обработки к исходным значениям. Области–источники могут находиться на разных листах или рабочих книгах. В консолидации может участвовать до 255 областей–источников.

Рис. 1. Подведение итогов в консолидируемых диапазонах.

Варианты консолидации данных:

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

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

  • все области на одном листе – в ссылках указывается адрес блока ячеек, например D1:C8;
  • области на разных листах – в ссылках указывается название листа, диапазон, например, лист1!D1:лист2!C8;
  • области в разных книгах, на разных листах – в ссылках указывается название книги, название листа, диапазон, например, лист1!D1: лист2!C8.

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

Пример

На отдельных листах рабочей книги по каждой учебной группе хранятся сведения о среднем балле по фиксированному перечню предметов в разрезе видов занятий (Рис. 2). Серым цветом показана консолидируемая область источников.

Номер группы

Предметы

Вид занятий

Предмет 1

Предмет 2

Лекции

Консолидируемая

Семинары

область

Лаб.работы

Рис. 2. Пример областей-источников при консолидации данных по расположению

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

Пример

На рабочих листах представлена информация областей-источников в виде структуры (Рис. 3). Число строк – переменное, состав предметов и виды занятий – повторяются и могут рассматриваться как имена столбцов для консолидации по категориям. Серым цветом показана консолидируемая область источников.

Группа 101

Предметы

Предмет 1

Предмет 2

Лекции

Лаб.раб.

Группа 102

Предметы

Предмет 3

Предмет 2

Лекции

Лаб.раб.

Семинары

Рис. 3. Пример областей-источников при консолидации данных по категориям.

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

Рис. 4. Диалоговое окно Консолидация

Таблица 1. Формы ссылок

Вид ссылки Источники и назначение

Ссылки на ячейки –

на одном листе

Ссылки на лист и ячейки

на разных листах

Ссылки на книгу, лист и ячейки

в разных книгах

Полный путь и все ссылки

в различных местах диска

Имя поименованной области

область-источник поименована

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

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

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

Нельзя корректировать ссылки на области-источники (добавлять или удалять новые области-источники) при наличии флажка Создавать связи с исходными данными.

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

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

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

Команда Данные/Сводная таблица вызывает Мастера сводных таблиц для построения сводов-итогов определенных видов на основании данных списков, других сводных таблиц, внешних баз данных, нескольких разрозненных областей данных электронной таблицы Microsoft Excel. Сводная таблица обеспечивает различные способы агрегирования информации.

Мастер сводных таблиц осуществляет построение сводной таблицы в несколько этапов (шагов):

1-й шаг. Указание вида источника сводной таблицы

  • использование списка или базы данных Excel;
  • использование внешнего источника данных;
  • использование нескольких диапазонов консолидации;
  • использование данных из другой сводной таблицы.

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

2-й шаг. Указание диапазона ячеек с исходными данными

  • список должен обязательно содержать имена полей (столбцов). Полное имя диапазона ячеек записывается в виде: имя_листа!диапазон ячеек;
  • предварительная установка курсора в списке автоматически указывает интервал ячеек;
  • для ссылки на закрытый интервал другой рабочей книги используется кнопка Обзор, для выбора диска, папки и файла закрытой рабочей книги, вводится имя рабочего листа и диапазон ячеек либо имя блока ячеек.

3-й шаг. Построение макета сводной таблицы

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

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

Рис. 5. Макет сводной таблицы

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

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

Рис. 6. Диалоговое окно Вычисление поля сводной таблицы.

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

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

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

Таблица 2. Функции, выполняемые над значениями поля Данные.

Функция

Результат

Отличие

Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках поле и элемент

Доля

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

Приведенное отличие

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

С нарастающим итогом в поле

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

Доля от суммы по строке

Значения ячеек области данных отображаются в процентах от итога строки

Доля от суммы по столбцу

Значения ячеек области данных отображаются в процентах от итога столбца

Доля от общей суммы

Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы

Индекс

При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог))/((Итог строки) * (Итог столбца))

4-й шаг. Выбор места расположения и параметров сводной таблицы

Рис. 7. Диалоговое окно Мастера сводных таблиц (4 шаг).

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

Кнопка Параметры в диалоговом окне 4-го шага вызывает диалоговое окно Параметры сводной таблицы, в котором устанавливается вариант вывода информации в сводной таблице:

  • общая сумма по столбцам – внизу сводной таблицы выводятся общие итоги по столбцам;
  • общая сумма по строкам – в сводной таблице формируется итоговый столбец;
  • сохранить данные вместе с таблицей – сохраняется не только макет, но результат построения сводной таблицы, на который можно ссылаться из других таблиц;
  • автоформат – позволяет форматировать сводную таблицу с помощью команды Формат/Автоформат и др. параметры.

После завершения построения сводной таблицы могут измениться исходные данные. Для обновления данных сводной таблицы в этом случае следует выполнить команду Данные/Обновить данные.

Для изменения структуры уже построенной сводной таблицы курсор устанавливается в область сводной таблицы, повторно выполняется команда Данные/Сводная таблица, которая вызывает Мастера сводных таблиц, шаг 3.

Версия для печати

Перейти к версии для печати

Хрестоматия

Название работы Аннотация

Практикумы

Название практикума Аннотация

Практикум 4.5. КОНСОЛИДАЦИЯ ДАННЫХ. СВОДНЫЕ ТАБЛИЦЫ

В рамках данной работы Вы должны будете освоить технологии:

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

Презентации

Название презентации Аннотация

Тьюторы

Название тьюториала Аннотация
Итоговый тьюториал по разделу «ИНФОРМАЦИОННАЯ ТЕХНОЛОГИЯ ОБРАБОТКИ ТАБЛИЧНЫХ ДОКУМЕНТОВ»
Тьюториал по разделу Для студентов очно-заочной формы обучения

Консолидация (76 стр.)

Посвящается Энн

В снах Контроля царит раннее утро, небо насыщенно-синее с едва проклевывающимся намеком на свет. Он смотрит со скалы в пучину, залив, бухту. Каждый раз по-другому. Его взгляд проникает в недвижную воду на целые мили. Он видит океанских левиафанов, скользящих в глубине, будто субмарины, колоколообразные орхидеи или широкие корпуса кораблей, безмолвные, пребывающие в непрестанном движении, и сами их размеры источают такое ощущение мощи, что даже высоко над ними он чувствует потрясения, порождаемые их прохождением. Часами взирает на их формы, движения, вслушивается в шепоты, эхом докатывающиеся до него… а затем падает. Медленно, невероятно медленно, беззвучно падает в темную воду, без всплеска, без малейшей ряби. И продолжает падение.

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

День первый. Начало его последнего шанса.

— Это и есть выжившие?

Контроль стоял рядом с заместительницей директора Южного предела за замызганным односторонним зеркалом, глядя на троих индивидуумов, сидящих в комнате для допросов. Вернувшихся из последней экспедиции в Зону Икс. Первой из экспедиций Двенадцатой цикла, или Х.12.А, если говорить точно.

Заместительница директора — высокая худая чернокожая женщина в возрасте за сорок — не откликнулась, что ничуть не удивило Контроля. С момента его прибытия нынче утром, потому что понедельник он посвятил обустройству, она не одарила его лишним словом. Да и взглядом, если уж на то пошло, исключая лишь момент, когда он указал ей и остальному персоналу звать его Контролем, а не Джоном или Родригесом. Помедлив мгновение, она ответила: «В таком случае зовите меня Пейшенс, а не Грейс», — к сдержанной потехе присутствующих. Смена значащего имени «милость» на столь же значащее «терпение» заинтересовала его. «Ладно, — ответил он. — Я могу звать вас просто Грейс», — в полной уверенности, что это придется ей не по нраву. Она парировала тем, что постоянно упоминала о нем как об «исполняющем обязанности» директора. И это было правдой: между ее восхождением на должность и его вознесением лежала пропасть, бездна времени и заполненных формуляров, выполненных процедур, откапывания и найма персонала. Так что до поры вопрос о полномочиях оставался открытым.

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

— Где их обнаружили? — спросил он теперь, хотя на самом деле хотел спросить, почему их не держат порознь. Из-за нехватки дисциплины, из-за того, что ваш департамент давным-давно превратился в крысиное гнездо? Крысы сейчас в подвале, грызут все подряд. А может, уже в стенах.

— Прочтите материалы, — отрезала она, давая понять, что ему следовало уже сделать это.

И вышла из комнаты.

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

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

About the author

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *