Консолидация данных в Excel
В Excel данные, используемые для обработки и анализа, могут размещаться в разных ячейках и диапазонах ячеек одного листа, на нескольких листах одной книги и даже в различных книгах. При этом с помощью консолидации и связывания объектов процесс получения и отображения результатов обработки данных можно существенно упростить.
Консолидация объектов – это процесс объединения данных из разных источников в виде итоговых значений, размещаемых в диапазоне назначения.
Объектом консолидации или источником консолидируемых данных могут быть отдельные ячейки и диапазоны ячеек, размещенные на одном или нескольких листах, в одной или разных книгах. Если объектом консолидации являются данные, размещенные на одном рабочем листе, на нескольких листах одной книги или в разных книгах, то говорят о консолидации данных, листов и книг соответственно.
Консолидация листов и книг используется для обработки данных, поступающих, например, из различных отделений одной компании.
Между исходными и консолидированными данными, а также между ячейками и диапазонами ячеек, диапазонами ячеек и графическими объектами, в том числе и рисунками, может быть установлена связь, позволяющая мгновенно обновлять данные после изменения исходных значений.
Связь объектов часто используется для распределения большого объема информации по различным рабочим книгам или листам, которые по отдельности подвергаются проверке, а затем объединяются в виде единой информационной системы.
Процесс консолидации предполагает обязательное указание
Последние четыре действия выполняются с помощью диалогового окна Консолидация, которое вызывается по команде Данные – Консолидация (рис.1).
Диапазон назначения указывается с помощью выделения на рабочем:
— диапазона ячеек, куда необходимо поместить обобщенные данные.
Заполнение его осуществляется по правилам, представленным в табл.1.
— Если в качестве области назначения выделена одна ячейка, проверьте, не наложатся ли консолидированные данные на необходимую информацию, расположенную тут же на листе.
— Текст и формулы не переносятся с исходных листов в область назначения. Их необходимо создать вручную перед консолидацией.
Для указания источников данных могут быть использованы два способа:
Выделение исходного диапазона с помощью мыши осуществляется стандартными приемами.
Ввод ссылок на диапазон с клавиатуры. Существует два вида ссылок: внутренние и внешние.
Внутренняя ссылка – это ссылка на диапазоны ячеек рабочей книги. Она является частным случаем внешней ссылки.
Необходимо записать в ячейку В2 первого листа(Лист1) значение ячейки D4 следующего листа (Лист2).
Внешняя ссылка –это ссылка на диапазоны ячеек, расположенных в других книгах.
Необходимо в ячейку В2 первого листа (Лист1) записать значение ячейки А1 из листа 2кв1996 файла КВАРТАЛ2.xls, находящегося на диске Е: в папке USERS.
Для этого в ячейку В2 следует ввести следующую формулу:
Если исходная книга закрыта, полный путь к ней следует указывать обязательно. Путь можно набрать с клавиатуры или воспользоваться кнопкой Обзор окна Консолидация (рис.1) и выбрать файл на диске.
Для облегчения работы с исходными областями часто используют именные ссылки. Для этого диапазонам исходных данных, листам и книгам, где они находятся, присваивают имена.
Пример 3. Исходные области и область назначения находятся на одном листе. Например, включить диапазон Бюджет (диапазону ячеек предварительно присвоено имя Бюджет).
Пример 4. Исходные области и область назначения находятся на разных листах. В этом случае удобно использовать имя листа и имя или ссылку на диапазон. Например, чтобы включить диапазон с заголовком «Бюджет», находящийся в рабочей книге на листе «Бухгалтерия», необходимо ввести ссылку
Пример 5. Исходные области и область назначения находятся в разных книгах. Используйте имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Продажи» из листа «Дальний Восток» в книге «1996», находящейся в этой же папке, введите:
Пример 6. Исходные области и область назначения находятся в разных книгах разных каталогах диска. Используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Оборот» листа «Февраль» в книге «Отдел продаж», которая находится в папке «Бюджет» на диске С:, введите:
Если диапазонам назначены уникальные, не присвоенные автоматически имена, то в ссылке можно не указывать имена листов. Например ‘[1996.xls]’!Продажи или ‘[C:\Бюджет\Отдел продаж.xls]’!Оборот в примерах 5 и 6.
Чтобы добавить новый исходный диапазон для консолидации, следует открыть диалоговое окно Консолидация , перейти в поле Ссылка и указать диапазон или ввести ссылку. После нажатия кнопки Добавить новая ссылка будет включена в список уже имеющихся диапазонов.
Чтобы удалить ссылку из диапазонов консолидации, выделите ее в списке диапазонов и нажмите кнопку Удалить .
Чтобы отредактировать ссылку, выделите ее в диалоговом окне Консолидация в списке диапазонов. Она появится в поле Ссылка, где ее можно изменить. После внесения всех исправлений нажмите кнопку Добавить. Затем удалите старый вариант измененной ссылки.
Способ консолидации ячеек. Возможны четыре варианта: согласно расположению в диапазоне, согласно заголовкам строк и столбцов, с использованием ссылок и вручную. Первые два выбираются с помощью выделения опций в группе Использовать в качестве имен окна Консолидация (рис.1.).
Тип ( функция) консолидации. Обобщение исходных данных может быть осуществлено с использованием следующих функций:
Создание сводных таблиц в MS Excel. Консолидация данных
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кэшем, где находится моментальный снимок исходных данных.
Группируем по значению
В этой сводной таблице каждый идентификатор OrderID представлен в отдельной строке. Такие данные можно считать обобщёнными, но нам этого недостаточно.
Мы настраиваем параметры группировки данных — они будут сгруппированы по полю OrderID по десять последовательно пронумерованных заказов.
Вот так выглядят данные, сгруппированные по OrderID. Как видите, они значительно более обобщены, чем ранее.
Как объединить данные в нескольких файлах Excel — Mexn
- Группируем по значению
- Группируем по дате
- Группируем данные вручную
- Исправляем ошибки
- Детализация групп
- Группировка и несколько диапазонов консолидации
Способ консолидации ячеек. Возможны четыре варианта: согласно расположению в диапазоне, согласно заголовкам строк и столбцов, с использованием ссылок и вручную. Первые два выбираются с помощью выделения опций в группе Использовать в качестве имен окна Консолидация (рис.1.).
