Консолидация данных — это способ получения итоговой информации, при котором данные, расположенные в нескольких различных областях, объединяются в соответствии с выбранной функцией обработки.
При этом данные, расположенные в одной или нескольких исходных областях, обрабатываются и отображаются в одной итоговой таблице. Источники данных могут находиться на том же листе, что и итоговая таблица, на других листах той же книги или в других книгах. При консолидации для обработки данных используются те же функции, что и при подведении итогов. Место расположения исходных (консолидируемых) данных называется областью-источником. Та часть рабочего листа, где будут размещаться итоговые (консолидированные) данные — областью назначения.
Предусмотрены два способа консолидации данных: по расположению и по категориям.
Консолидация по расположению. Консолидация по расположению используется, если данные исходных областей расположены в одном и том же порядке и имеют одни и те же заголовки. Используется этот способ для консолидации данных листов, например бюджетов отделов предприятия, созданных на основе единого шаблона. Рассмотрим пример учета метизов, поступавших в три разных магазина в первом квартале (рис. 6.54). Для сравнения учетные данные, расположенные на разных листах (Январь, Февраль, Март) рабочей книги, на рисунке изображены рядом, что позволяет заметить, что взаимное расположение данных на каждом из трех листов полностью совпадает и по строкам, и по столбцам.
| Январь | Февраль Март | ||||||||||
| Маг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 | |||
Как объединить две таблицы и более в одну? / Объединение нескольких таблиц в Excel
6.54. Данные учета
Как сделать консолидацию данных в Excel. Пошаговая инструкция консолидации | Козлов Алексей. РУНО
Для консолидации по расположению пользователю следует заранее создать область назначения, где будет строка с заголовками столбцов (Marl, Маг2 и МагЗ) и строка с названиями строк (болты, гайки, шайбы и шурупы). Разумеется, расположение этих заголовков и названий должно соответствовать их расположению в исходных таблицах.
После этого необходимо выделить курсором верхнюю левую ячейку, находящуюся на пересечении столбца Marl и строки болты, и дать команду Данные/Консолидация. . Затем в диалоговом окне Консолидация, вызванном этой командой, следует уточнить функцию, с помощью которой должны обрабатываться консолидируемые данные, и ввести в поле Список диапазонов ссылки на области-источники. Для этого ссылку на диапазон-источник надо поочередно ввести в поле Ссылка и нажать кнопку Добавить (рис. 6.55). Консолидация завершается нажатием кнопки OK после того, как будет заполнен список диапазонов.
Рис. 6.55. Диалоговое окно Консолидация
Нетрудно заметить, что в случае консолидации по расположению табличный процессор MS Excel осуществляет обработку элементов массивов данных в соответствии с их индексами, соответствующими положению того или иного элемента в массиве. Очевидно, что размерность всех обрабатываемых массивов должна быть одинаковой.
Консолидация по категориям. Консолидация по категориям применяется, если данные исходных областей не упорядочены, но имеют одни и те же заголовки. Используется этот способ для консолидации данных списков, имеющих разную структуру, но одинаковые заголовки.
В этом случае табличный процессор MS Excel в процессе обработки данных присваивает им имена, используя при этом заголовки столбцов и названия строк. При этом способе консолидации первый выделенный столбец определяет поле группировки данных. Поясним это примером. Пусть на два склада (№ 1 и № 2), принадлежащих одной фирме, в разное время поступали товары от разных поставщиков (рис.
6.56). Используя эти данные как области источника, несложно получить различные варианты данных в области назначения.
Рис. 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.
Источник: economics.studio
1. Консолидация данных в excel
В Excel данные, используемые для обработки и анализа, могут размещаться в разных ячейках и диапазонах ячеек одного листа, на нескольких листах одной книги и даже в различных книгах. При этом с помощью консолидации и связывания объектов процесс получения и отображения результатов обработки данных можно существенно упростить.
Консолидация объектов – это процесс объединения данных из разных источников в виде итоговых значений, размещаемых в диапазоне назначения.
Объектом консолидации или источником консолидируемых данных могут быть отдельные ячейки и диапазоны ячеек, размещенные на одном или нескольких листах, в одной или разных книгах. Если объектом консолидации являются данные, размещенные на одном рабочем листе, на нескольких листах одной книги или в разных книгах, то говорят о консолидации данных, листов и книг соответственно.
Консолидация листов и книг используется для обработки данных, поступающих, например, из различных отделений одной компании.
Возможны следующие виды консолидации:
— по физическому расположению (п.1.2);
— по заголовкам строк и столбцов (п.1.3);
— с использованием ссылок (п.1.4);
— ручная консолидация (п.1.5).
Между исходными и консолидированными данными, а также между ячейками и диапазонами ячеек, диапазонами ячеек и графическими объектами, в том числе и рисунками, может быть установлена связь, позволяющая мгновенно обновлять данные после изменения исходных значений.
Связь объектов часто используется для распределения большого объема информации по различным рабочим книгам или листам, которые по отдельности подвергаются проверке, а затем объединяются в виде единой информационной системы.
Проблемам связывания объектов посвящен раздел 2.
1.1 Общее описание процесса консолидации
Процесс консолидации предполагает обязательное указание
— наличия связи между объектами консолидации;
— типа (функции) консолидации.
Последние четыре действия выполняются с помощью диалогового окна Консолидация, которое вызывается по команде Данные – Консолидация (рис.1).
Рис.1. Вид окна Консолидация
Диапазон назначения указывается с помощью выделения на рабочем:
— диапазона ячеек, куда необходимо поместить обобщенные данные.
Заполнение его осуществляется по правилам, представленным в табл.1.
Заполняются все ячейки, необходимые для всех консолидируемых категорий (элементов) исходных данных
Заполняются ячейки вниз от выделения. Ширина области назначения в точности совпадает с шириной выделения
Заполняются ячейки вправо от выделения. Высота области назначения в точности совпадает с высотой выделения
Консолидируется ровно столько категорий, сколько поместится в выделенном диапазоне. Если диапазон недостаточен, появится соответствующее сообщение
— Если в качестве области назначения выделена одна ячейка, проверьте, не наложатся ли консолидированные данные на необходимую информацию, расположенную тут же на листе.
— Текст и формулы не переносятся с исходных листов в область назначения. Их необходимо создать вручную перед консолидацией.
Источники данных представляют собой диапазоны ячеек. Число диапазонов может достигать 255. Источники данных не обязаны быть открыты во время консолидации. Исходные области обрабатываемых данных задаются либо трехмерными формулами непосредственно в ячейках диапазона назначения, либо в поле Ссылка диалогового окна Консолидация (рис.1). Источники данных могут находиться на том же листе, что и таблица консолидации, на других листах той же книги, в других книгах или в файлах Lotus 1-2-3.
Для указания источников данных могут быть использованы два способа:
— выделение исходного диапазона с помощью мыши;
— ввод ссылки на диапазон с клавиатуры.
Выделение исходного диапазона с помощью мыши осуществляется стандартными приемами.
Ввод ссылок на диапазон с клавиатуры. Существует два вида ссылок: внутренние и внешние.
Внутренняя ссылка – это ссылка на диапазоны ячеек рабочей книги. Она является частным случаем внешней ссылки.
Синтаксис внутренней ссылки:
Необходимо записать в ячейку В2 первого листа(Лист1) значение ячейки D4 следующего листа (Лист2).
Для этого в ячейку В2 введите формулу:
Внешняя ссылка –это ссылка на диапазоны ячеек, расположенных в других книгах.
Синтаксис внешней ссылки:
Для этого в ячейку В2 следует ввести следующую формулу:
Если исходная книга закрыта, полный путь к ней следует указывать обязательно. Путь можно набрать с клавиатуры или воспользоваться кнопкой Обзор окна Консолидация (рис.1) и выбрать файл на диске.
Для облегчения работы с исходными областями часто используют именные ссылки. Для этого диапазонам исходных данных, листам и книгам, где они находятся, присваивают имена.
Пример 3. Исходные области и область назначения находятся на одном листе. Например, включить диапазон Бюджет (диапазону ячеек предварительно присвоено имя Бюджет).
Для этого вводим ссылку
Пример 4. Исходные области и область назначения находятся на разных листах. В этом случае удобно использовать имя листа и имя или ссылку на диапазон. Например, чтобы включить диапазон с заголовком «Бюджет», находящийся в рабочей книге на листе «Бухгалтерия», необходимо ввести ссылку
Пример 5. Исходные области и область назначения находятся в разных книгах. Используйте имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Продажи» из листа «Дальний Восток» в книге «1996», находящейся в этой же папке, введите:
Пример 6. Исходные области и область назначения находятся в разных книгах разных каталогах диска. Используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Оборот» листа «Февраль» в книге «Отдел продаж», которая находится в папке «Бюджет» на диске С:, введите:
Удаление и редактирование ссылок
Чтобы добавить новый исходный диапазон для консолидации, следует открыть диалоговое окно Консолидация , перейти в поле Ссылка и указать диапазон или ввести ссылку. После нажатия кнопки Добавить новая ссылка будет включена в список уже имеющихся диапазонов.
Чтобы удалить ссылку из диапазонов консолидации, выделите ее в списке диапазонов и нажмите кнопку Удалить .
Чтобы отредактировать ссылку, выделите ее в диалоговом окне Консолидация в списке диапазонов. Она появится в поле Ссылка, где ее можно изменить. После внесения всех исправлений нажмите кнопку Добавить. Затем удалите старый вариант измененной ссылки.
Способ консолидации ячеек. Возможны четыре варианта: согласно расположению в диапазоне, согласно заголовкам строк и столбцов, с использованием ссылок и вручную. Первые два выбираются с помощью выделения опций в группе Использовать в качестве имен окна Консолидация (рис.1.).
Наличие связи между объектами. При наличии связи результаты будут обновляться при изменении данных, а в области назначения будет создана структура. Т.е. в диапазон консолидации между итоговых строк будет вставлена детальная информация, связанная внешними ссылками с исходными диапазонами. Причем, диапазон назначения будет структурирован. Верхним уровнем структуры будут итоговые данные, нижним — исходные (см. пример _8.).
Тип ( функция) консолидации. Обобщение исходных данных может быть осуществлено с использованием следующих функций:
Источник: studfile.net
Консолидация данных в Excel
В Excel данные, используемые для обработки и анализа, могут размещаться в разных ячейках и диапазонах ячеек одного листа, на нескольких листах одной книги и даже в различных книгах. При этом с помощью консолидации и связывания объектов процесс получения и отображения результатов обработки данных можно существенно упростить.
Консолидация объектов – это процесс объединения данных из разных источников в виде итоговых значений, размещаемых в диапазоне назначения.
Объектом консолидации или источником консолидируемых данных могут быть отдельные ячейки и диапазоны ячеек, размещенные на одном или нескольких листах, в одной или разных книгах. Если объектом консолидации являются данные, размещенные на одном рабочем листе, на нескольких листах одной книги или в разных книгах, то говорят о консолидации данных, листов и книг соответственно.
Консолидация листов и книг используется для обработки данных, поступающих, например, из различных отделений одной компании.
Возможны следующие виды консолидации:
— по физическому расположению (п.1.2);
— по заголовкам строк и столбцов (п.1.3);
— с использованием ссылок (п.1.4);
— ручная консолидация (п.1.5).
Между исходными и консолидированными данными, а также между ячейками и диапазонами ячеек, диапазонами ячеек и графическими объектами, в том числе и рисунками, может быть установлена связь, позволяющая мгновенно обновлять данные после изменения исходных значений.
Связь объектов часто используется для распределения большого объема информации по различным рабочим книгам или листам, которые по отдельности подвергаются проверке, а затем объединяются в виде единой информационной системы.
Проблемам связывания объектов посвящен раздел 2.
1.1 Общее описание процесса консолидации
Процесс консолидации предполагает обязательное указание
— наличия связи между объектами консолидации;
— типа (функции) консолидации.
Последние четыре действия выполняются с помощью диалогового окна Консолидация, которое вызывается по команде Данные – Консолидация (рис.1).
Рис.1. Вид окна Консолидация
Диапазон назначения указывается с помощью выделения на рабочем:
— диапазона ячеек, куда необходимо поместить обобщенные данные.
Заполнение его осуществляется по правилам, представленным в табл.1.
Таблица 1. Заполнение диапазона назначения
| Выделение | Результат |
| Ячейка | Заполняются все ячейки, необходимые для всех консолидируемых категорий (элементов) исходных данных |
| Строка ячеек | Заполняются ячейки вниз от выделения. Ширина области назначения в точности совпадает с шириной выделения |
| Столбец ячеек | Заполняются ячейки вправо от выделения. Высота области назначения в точности совпадает с высотой выделения |
| Диапазон ячеек | Консолидируется ровно столько категорий, сколько поместится в выделенном диапазоне. Если диапазон недостаточен, появится соответствующее сообщение |
— Если в качестве области назначения выделена одна ячейка, проверьте, не наложатся ли консолидированные данные на необходимую информацию, расположенную тут же на листе.
— Текст и формулы не переносятся с исходных листов в область назначения. Их необходимо создать вручную перед консолидацией.
Источники данных представляют собой диапазоны ячеек. Число диапазонов может достигать 255. Источники данных не обязаны быть открыты во время консолидации. Исходные области обрабатываемых данных задаются либо трехмерными формулами непосредственно в ячейках диапазона назначения, либо в поле Ссылка диалогового окна Консолидация (рис.1). Источники данных могут находиться на том же листе, что и таблица консолидации, на других листах той же книги, в других книгах или в файлах Lotus 1-2-3.
Для указания источников данных могут быть использованы два способа:
— выделение исходного диапазона с помощью мыши;
— ввод ссылки на диапазон с клавиатуры.
Выделение исходного диапазона с помощью мыши осуществляется стандартными приемами.
Ввод ссылок на диапазон с клавиатуры. Существует два вида ссылок: внутренние и внешние.
Внутренняя ссылка – это ссылка на диапазоны ячеек рабочей книги. Она является частным случаем внешней ссылки.
Синтаксис внутренней ссылки:
Необходимо записать в ячейку В2 первого листа(Лист1) значение ячейки D4 следующего листа (Лист2).
Для этого в ячейку В2 введите формулу:
Внешняя ссылка –это ссылка на диапазоны ячеек, расположенных в других книгах.
Синтаксис внешней ссылки:
Для этого в ячейку В2 следует ввести следующую формулу:
Если исходная книга закрыта, полный путь к ней следует указывать обязательно. Путь можно набрать с клавиатуры или воспользоваться кнопкой Обзор окна Консолидация (рис.1) и выбрать файл на диске.
Для облегчения работы с исходными областями часто используют именные ссылки. Для этого диапазонам исходных данных, листам и книгам, где они находятся, присваивают имена.
Пример 3. Исходные области и область назначения находятся на одном листе. Например, включить диапазон Бюджет (диапазону ячеек предварительно присвоено имя Бюджет).
Для этого вводим ссылку
Пример 4. Исходные области и область назначения находятся на разных листах. В этом случае удобно использовать имя листа и имя или ссылку на диапазон. Например, чтобы включить диапазон с заголовком «Бюджет», находящийся в рабочей книге на листе «Бухгалтерия», необходимо ввести ссылку
Пример 5. Исходные области и область назначения находятся в разных книгах. Используйте имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Продажи» из листа «Дальний Восток» в книге «1996», находящейся в этой же папке, введите:
Пример 6. Исходные области и область назначения находятся в разных книгах разных каталогах диска. Используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Оборот» листа «Февраль» в книге «Отдел продаж», которая находится в папке «Бюджет» на диске С:, введите:
Удаление и редактирование ссылок
Чтобы добавить новый исходный диапазон для консолидации, следует открыть диалоговое окно Консолидация , перейти в поле Ссылка и указать диапазон или ввести ссылку. После нажатия кнопки Добавить новая ссылка будет включена в список уже имеющихся диапазонов.
Чтобы удалить ссылку из диапазонов консолидации, выделите ее в списке диапазонов и нажмите кнопку Удалить .
Чтобы отредактировать ссылку, выделите ее в диалоговом окне Консолидация в списке диапазонов. Она появится в поле Ссылка, где ее можно изменить. После внесения всех исправлений нажмите кнопку Добавить. Затем удалите старый вариант измененной ссылки.
Способ консолидации ячеек. Возможны четыре варианта: согласно расположению в диапазоне, согласно заголовкам строк и столбцов, с использованием ссылок и вручную. Первые два выбираются с помощью выделения опций в группе Использовать в качестве имен окна Консолидация (рис.1.).
Наличие связи между объектами. При наличии связи результаты будут обновляться при изменении данных, а в области назначения будет создана структура. Т.е. в диапазон консолидации между итоговых строк будет вставлена детальная информация, связанная внешними ссылками с исходными диапазонами. Причем, диапазон назначения будет структурирован. Верхним уровнем структуры будут итоговые данные, нижним — исходные (см. пример _8.).
Тип ( функция) консолидации. Обобщение исходных данных может быть осуществлено с использованием следующих функций:
| — Сумма | — Количество значений |
| — Среднее значение | — Смещенное отклонение |
| — Максимум | — Несмещенное отклонение |
| — Минимум | — Смещенная дисперсия |
| — Произведение | — Несмещенная дисперсия |
| — Количество чисел |
По умолчанию используется функция Сумма.
Источник: kazedu.com