ТЕМА 4.5. КОНСОЛИДАЦИЯ ДАННЫХ. СВОДНЫЕ ТАБЛИЦЫ
- консолидации данных;
- создания сводных таблиц.
Оглавление
4.5.1. Консолидация данных
Консолидация данных является способом получения итоговой информации (Рис. 1), которая выполняется в соответствии с выбранной функцией обработки.
Консолидация – агрегирование (объединение) данных, представленных в исходных областях–источниках.
Результат консолидации находится в области назначения. Таблица консолидации создается путем применения функции обработки к исходным значениям. Области–источники могут находиться на разных листах или рабочих книгах. В консолидации может участвовать до 255 областей–источников.
Рис. 1. Подведение итогов в консолидируемых диапазонах.
Варианты консолидации данных:
- с помощью формул, где используются ссылки;
- по расположению данных для одинаково организованных областей–источников (фиксированное расположение);
- по категориям для различающихся по своей структуре области данных;
- с помощью сводной таблицы;
- консолидация внешних данных.
При консолидации данных с помощью формул используемые в них ссылки могут иметь разное представление в зависимости от взаимного расположения областей–источников и области–назначения:
- все области на одном листе – в ссылках указывается адрес блока ячеек, например D1:C8;
- области на разных листах – в ссылках указывается название листа, диапазон, например, лист1!D1:лист2!C8;
- области в разных книгах, на разных листах – в ссылках указывается название книги, название листа, диапазон, например, [книга1] лист1!D1: [книга2] лист2!C8.
При консолидации по расположению данных все источники имеют одинаковое расположение данных источников (имена категорий данных в выделяемые области–источники не включаются). Данные имеют одинаковую структуру, фиксированное расположение ячеек и могут быть консолидированы с определенной функцией обработки (среднее значение, максимальное, минимальное и т.п.) по их расположению. Для консолидации данных курсор устанавливается в область места назначения. Выполняется команда Данные/Консолидация, выбирается вариант и задаются условия консолидации.
На отдельных листах рабочей книги по каждой учебной группе хранятся сведения о среднем балле по фиксированному перечню предметов в разрезе видов занятий (Рис. 2). Серым цветом показана консолидируемая область источников.
Номер группы
Предметы
Рис. 2. Пример областей-источников при консолидации данных по расположению
При консолидации по категориям области-источники содержат однотипные данные, но организованные в различных областях-источниках неодинаково. Для консолидации данных по категориям используются имена строк и/или столбцов, (имена включаются в выделенные области-источники). Команда Данные/Консолидация, вызывает окно, в котором можно выбрать вариант задать условия консолидации.
На рабочих листах представлена информация областей-источников в виде структуры (Рис. 3). Число строк – переменное, состав предметов и виды занятий – повторяются и могут рассматриваться как имена столбцов для консолидации по категориям. Серым цветом показана консолидируемая область источников.
Группа 101
Предметы
Группа 102
Предметы
Рис. 3. Пример областей-источников при консолидации данных по категориям.
Условия консолидации задаются в диалоговом окне Консолидация (Рис. 4). В окне Функция выбирается функция консолидации данных. Для каждой области-источника строится ссылка, для чего курсор устанавливается в поле ссылки, затем нужно перейти в область-источника для выделения блока ячеек и нажать кнопку Добавить. Если диапазон указан неверно, нужно выделить его в списке диапазонов и нажать кнопку Удалить. Ссылки могут иметь разные формы (Рис. 4).
Рис. 4. Диалоговое окно Консолидация
Таблица 1. Формы ссылок
| Вид ссылки | Источники и назначение | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Название практикума | Аннотация | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Январь | Февраль Март | ||||||||||
| Маг1 | Маг2 | МагЗ | Маг1 | Маг2 | МагЗ | Маг1 | Маг2 | МагЗ | |||
| болты | 12 | 14 | болты | 8 | 21 | болты | 21 | 8 | |||
| гайки | 22 | 44 | гайки | 17 | 52 | гайки | 41 | ||||
| шайбы | 18 | 45 | шайбы | 4 | 11 | 5 | шайбы | 51 | |||
| шурупы | 78 | 22 | шурупы | 74 | 14 | шурупы | 22 | 11 | |||
Для консолидации по расположению пользователю следует заранее создать область назначения, где будет строка с заголовками столбцов (Marl, Маг2 и МагЗ) и строка с названиями строк (болты, гайки, шайбы и шурупы). Разумеется, расположение этих заголовков и названий должно соответствовать их расположению в исходных таблицах. После этого необходимо выделить курсором верхнюю левую ячейку, находящуюся на пересечении столбца Marl и строки болты, и дать команду Данные/Консолидация. . Затем в диалоговом окне Консолидация, вызванном этой командой, следует уточнить функцию, с помощью которой должны обрабатываться консолидируемые данные, и ввести в поле Список диапазонов ссылки на области-источники. Для этого ссылку на диапазон-источник надо поочередно ввести в поле Ссылка и нажать кнопку Добавить (рис. 6.55). Консолидация завершается нажатием кнопки OK после того, как будет заполнен список диапазонов.
Рис. 6.55. Диалоговое окно Консолидация
Нетрудно заметить, что в случае консолидации по расположению табличный процессор MS Excel осуществляет обработку элементов массивов данных в соответствии с их индексами, соответствующими положению того или иного элемента в массиве. Очевидно, что размерность всех обрабатываемых массивов должна быть одинаковой.
Консолидация по категориям. Консолидация по категориям применяется, если данные исходных областей не упорядочены, но имеют одни и те же заголовки. Используется этот способ для консолидации данных списков, имеющих разную структуру, но одинаковые заголовки. В этом случае табличный процессор MS Excel в процессе обработки данных присваивает им имена, используя при этом заголовки столбцов и названия строк. При этом способе консолидации первый выделенный столбец определяет поле группировки данных. Поясним это примером. Пусть на два склада (№ 1 и № 2), принадлежащих одной фирме, в разное время поступали товары от разных поставщиков (рис.
Рис. 6.56. Сведения о поступлении товаров
Пример 10. Предположим, что нам необходимо определить суммарную стоимость каждого из наименований всех товаров, поступивших в течение полугодия на оба склада от всех поставщиков. Для решения этой задачи следует группировать данные по столбцу Наименование товара. В этом случае пользователь должен сообщить программе необходимые уточнения, указав их в диалоговом окне Консолидация. Это окно приведено на рис. 6.57, а результаты консолидации — на рис. 6.58. Нетрудно заметить, что в этом случае программе необходимо указать, какие элементы оформления списка следует использовать в качестве имен данных, так как в этом случае имена используются в качестве аргументов формул. Следует обратить внимание на то,
Рис. 6.58. Результаты консолидации для примера 10
что диапазоны, включенные в список консолидации, начинаются со столбца Наименование товара.
Пример 11. Необходимо оценить суммарную стоимость каждого наименования товара, поступившего на оба склада от разных поставщиков. В этом случае группировать данные следует по столбцу Поставщик, т. е. включать в диапазон консолидации данные, начиная со второго столбца — Поставщик. Следует помнить, что перед включением в диапазон консолидации новых данных из него необходимо удалить ссылки, использовавшиеся ранее. Для этого требуется выделить ссылку на диапазон в поле Список диапазонов и нажать кнопку Удалить. Диалоговое окно с указанием сведений, необходимых для консолидации в этом случае, приведено на рис. 6.59.
Результаты консолидации данных в этом случае можно увидеть на рис. 6.60.
Рис. 6.59. Диалоговое окно с параметрами консолидации для примера 11
Рис. 6.60. Результаты консолидации для примера 11
Пример 12. Для обоснования финансового плана на первое полугодие необходимо дать оценку распределению объемов поставок по месяцам. При этом нет необходимости учитывать вид товаров и реквизиты поставщика. Чтобы ответить на этот во-
Рис. 6.61. Диалоговое окно с параметрами консолидации для примера 12
Рис. 6.62. Результаты консолидации для примера 12
прос, для группировки данных нужно выбрать столбец Дата поставки, т. е. включать в диапазон консолидации данные, содержащиеся только в двух правых столбцах. Диалоговое окно для решения данной задачи приведено на рис. 6.61.
Результаты консолидации представлены на рис. 6.62.
Источник
