Консолидация данных в excel

Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels) . Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

После нажатия на ОК видим результат нашей работы:

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

Консолидация данных в Excel с примерами использования

При выполнении ряда работ у пользователя Microsoft Excel может быть создано несколько однотипных таблиц в одном файле или в нескольких книгах.

Данные необходимо свести воедино. Собрать в один отчет, чтобы получить общее представление. С такой задачей справляется инструмент «Консолидация».

Как сделать консолидацию данных в Excel

Есть 4 файла, одинаковых по структуре. Допустим, поквартальные итоги продаж мебели.

Нужно сделать общий отчет с помощью «Консолидации данных». Сначала проверим, чтобы

• макеты всех таблиц были одинаковыми;
• названия столбцов – идентичными (допускается перестановка колонок);
• нет пустых строк и столбцов.

Диапазоны с исходными данными нужно открыть.

Для консолидированных данных отводим новый лист или новую книгу. Открываем ее. Ставим курсор в первую ячейку объединенного диапазона.

Внимание. Правее и ниже этой ячейки должно быть свободно. Команда «Консолидация» заполнит столько строк и столбцов, сколько нужно.

Переходим на вкладку «Данные». В группе «Работа с данными» нажимаем кнопку «Консолидация».

Открывается диалоговое окно вида:

На картинке открыт выпадающий список «Функций». Это виды вычислений, которые может выполнять команда «Консолидация» при работе с данными. Выберем «Сумму» (значения в исходных диапазонах будут суммироваться).

Переходим к заполнению следующего поля – «Ссылка».

Ставим в поле курсор. Открываем лист «1 квартал». Выделяем таблицу вместе с шапкой. В поле «Ссылка» появится первый диапазон для консолидации. Нажимаем кнопку «Добавить»

Открываем поочередно второй, третий и четвертый квартал – выделяем диапазоны данных. Жмем «Добавить».

Таблицы для консолидации отображаются в поле «Список диапазонов».

Чтобы автоматически сделать заголовки для столбцов консолидированной таблицы, ставим галочку напротив «подписи верхней строки». Чтобы команда суммировала все значения по каждой уникальной записи крайнего левого столбца – напротив «значения левого столбца». Для автоматического обновления объединенного отчета при внесении новых данных в исходные таблицы – напротив «создавать связи с исходными данными».

Внимание. Если вносить в исходные таблицы новые значения, сверх выбранного для консолидации диапазона, они не будут отображаться в объединенном отчете. Чтобы можно было вносить данные вручную, снимите флажок «Создавать связи с исходными данными».

Для выхода из меню «Консолидации» и создания сводной таблицы нажимаем ОК.

Консолидированный отчет представляет собой структурированную таблицу. Нажмем «плюсик» в левом поле – появятся значения, на основе которых сформированы итоговые суммы по количеству и выручке.

Консолидация данных в Excel: практическая работа

Программа Microsoft Excel позволяет выполнять разные виды консолидации данных:

1. По расположению. Консолидированные данные имеют одинаковое расположение и порядок с исходными.
2. По категории. Данные организованы по разным принципам. Но в консолидированной таблице используются одинаковые заглавия строк и столбцов.
3. По формуле. Применяются при отсутствии постоянных категорий. Содержат ссылки на ячейки на других листах.
4. По отчету сводной таблицы. Используется инструмент «Сводная таблица» вместо «Консолидации данных».

Консолидация данных по расположению (по позициям) подразумевает, что исходные таблицы абсолютно идентичны. Одинаковые не только названия столбцов, но и наименования строк (см. пример выше). Если в диапазоне 1 «тахта» занимает шестую строку, то в диапазоне 2, 3 и 4 это значение должно занимать тоже шестую строку.

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

Созданы книги: Магазин 1, Магазин 2 и Магазин 3. Структура одинакова. Расположение данных идентично. Объединим их по позициям.

1. Открываем все три книги. Плюс пустую книгу, куда будет помещена консолидированная таблица. В пустой книге выбираем верхний левый угол чистого листа. Открываем меню инструмента «Консолидация».
2. Составим консолидированный отчет, используя функцию «Среднее».
3. Чтобы показать путь к книгам с исходными диапазонами, ставим курсор в поле «Ссылка». На вкладке «Вид» нажимаем кнопку «Перейти в другое окно».
4. Выбираем поочередно имена файлов, выделяем диапазоны в открывающихся книгах – жмем «Добавить».

Примечание. Показать программе путь к исходным диапазонам можно и с помощью кнопки «Обзор». Либо посредством переключения на открытую книгу.

Консолидация данных по категориям применяется, когда исходные диапазоны имеют неодинаковую структуру. Например, в магазинах реализуются разные товары. Какие-то наименования повторяются, а какие-то нет.

1. Для создания объединенного диапазона открываем меню «Консолидация». Выбираем функцию «Сумма» (для примера).
2. Добавляем исходные диапазоны любым из описанных выше способом. Ставим флажки у «значения левого столбца» и «подписи верхней строки».
3. Нажимаем ОК.

Excel объединил информацию по трем магазинам по категориям. В отчете имеются данные по всем товарам. Независимо от того, продаются они в одном магазине или во всех трех.

Примеры консолидации данных в Excel

На лист для сводного отчета вводим названия строк и столбцов из консолидируемых диапазонов. Удобнее делать это путем копирования.

В первую ячейку для значений объединенной таблицы вводим формулу со ссылками на исходные ячейки каждого листа. В нашем примере – в ячейку В2. Формула для суммы: =’1 квартал’!B2+’2 квартал’!B2+’3 квартал’!B2.

Копируем формулу на весь столбец:

Консолидация данных с помощью формул удобна, когда объединяемые данные находятся в разных ячейках на разных листах. Например, в ячейке В5 на листе «Магазин», в ячейке Е8 на листе «Склад» и т.п.

Если в книге включено автоматическое вычисление формул, то при изменении данных в исходных диапазонах объединенная таблица будет обновляться автоматически.

Консолидация данных с нескольких листов

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Для подведения итогов и результатов из отдельных листов, можно объединить данные из каждого листа в главном листе. Листы может быть в той же книге как главном листе или в других книгах. При объединении данных, чтобы упростить можно обновить и статистической обработки при необходимости собирать данные.

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

Совет: Если вы часто консолидировать данные, он помогут создать новые листы на основе шаблона с единообразным макетом. Дополнительные сведения о шаблонах читайте в статье: Создать шаблон. Это также идеально время Настройка шаблона с помощью таблиц Excel.

Консолидация данных по положению или категории двумя способами.

Консолидация данных по расположению: данные в исходных областях том же порядке и использует одинаковых наклеек. Этот метод используется для консолидации данных из нескольких листов, например отделов бюджета листов, которые были созданы из одного шаблона.

Консолидация по категории: данные в исходных областях не расположены в одном и том же порядке, но имеют одинаковые метки. Используйте этот способ, чтобы консолидировать данные из нескольких листов с разными макетами, но одинаковыми метками данных.

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

Примечание: В этой статье были созданы с Excel 2016. Хотя представления могут отличаться при использовании другой версии Excel, шаги одинаковы.

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

Если вы еще не сделано, настройте данные на каждом листе составные, сделав следующее:

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

Разместите каждый диапазон на отдельном листе, но не было введено никаких данных в основном листе где план для консолидации данных. Excel будет сделать это для вас.

Убедитесь, что всех диапазонов совпадают.

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

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

Нажмите кнопку данные > Консолидация (в группе Работа с данными ).

Выберите в раскрывающемся списке Функция функцию, которую вы хотите использовать для консолидации данных. По умолчанию используется значение СУММ.

Вот пример, в котором выбраны три диапазоны листа:

Далее в поле ссылка нажмите кнопку Свернуть, чтобы уменьшить масштаб панели и выбрать данные на листе.

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

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

Вот пример, в котором выбраны три диапазоны листа выбранного:

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

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

Связи невозможно создать, если исходная и конечная области находятся на одном листе.

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

Нажмите кнопку ОК, а Excel создаст консолидации для вас. Кроме того можно применить форматирование. Бывает только необходимо отформатировать один раз, если не перезапустить консолидации.

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

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

Если данные для консолидации находятся в разных ячейках разных листов:

Введите формулу со ссылками на ячейки других листов (по одной на каждый лист). Например, чтобы консолидировать данные из листов «Продажи» (в ячейке B4), «Кадры» (в ячейке F5) и «Маркетинг» (в ячейке B9) в ячейке A2 основного листа, введите следующее:

Совет: Чтобы указать ссылку на ячейку — например, продажи! B4 — в формуле, не вводя, введите формулу до того места, куда требуется вставить ссылку, а затем щелкните лист, используйте клавишу tab и затем щелкните ячейку. Excel будет завершена адрес имя и ячейку листа для вас. Примечание: формулы в таких случаях может быть ошибкам, поскольку очень просто случайно выбираемых неправильной ячейки. Также может быть сложно ошибку сразу после ввода сложные формулы.

Если данные для консолидации находятся в одинаковых ячейках разных листов:

Ввод формулы с трехмерной ссылки, которая использует ссылку на диапазон имен листов. Например для консолидации данных в ячейках с A2 по продажам через маркетинга включительно, в ячейке E5 главном листе вы бы введите следующие данные:

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Как работает консолидация данных в Excel 2013 – 2003

Существует много способов сведения данных в Microsoft Excel. Вы можете использовать инструмент Таблицы и связанные с ним фильтры, чтобы отобразить только нужную информацию. Вы можете создать Сводную таблицу, чтобы представить информацию в различных ракурсах. Кроме этого, Вы можете соединять большие объемы данных в виде сводки, которая покажет столько данных, сколько Вы захотите увидеть. В этой статье я познакомлю Вас с инструментом Консолидация в Excel, с помощью которого можно извлекать данные из целого ряда листов и даже рабочих книг и обобщать их на одном листе.

Работаем с несколькими наборами данных

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

Для этого откройте пустой лист в рабочей книге Excel (добавьте новый, если необходимо) и кликните в нём по любой ячейке. На вкладке Data (Данные) нажмите Consolidate (Консолидация), чтобы открылось диалоговое окно Consolidate (Консолидация). Выберите функцию для анализа данных и ссылки на диапазоны, которые нужно свести. В нашем случае мы хотим просуммировать значения, поэтому в поле Function (Функция) выберем Sum (Сумма).

Вы можете выбрать любую из 11 операций: Sum (Сумма), Count (Количество), Average (Среднее), Max (Максимум), Min (Минимум), Product (Произведение), Count Numbers (Количество чисел), StdDev (Смещенное отклонение), StdDevp (Несмещенное отклонение), Var (Смещенная дисперсия) и Varp (Несмещенная дисперсия).

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

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

Если у Вас на каждом листе находятся одинаковые диапазоны данных, расположенные на одинаковой позиции, то все, что Вам нужно делать – это щелкать по ярлычку очередного листа, и диапазон будет выбран автоматически. Так что эта задача гораздо проще, чем кажется на первый взгляд.

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

Полезный совет

Вы можете присвоить диапазонам имена, прежде чем начинать процесс консолидации. Для этого выделите диапазон и задайте ему имя в поле Имя слева от строки формул. Когда Вы дадите имена всем диапазонам, то при настройке консолидации поставьте курсор в поле Reference (Ссылка), нажмите F3 и в открывшемся окне Paste Name (Вставка имени) выберите нужный диапазон. Таким образом, Вы можете дать каждому диапазону понятное имя, и тогда позже не придётся вспоминать, что за данные скрываются на листе Лист1 в ячейках A3:F40.

Как видите, в области All References (Список диапазонов) листы располагаются в алфавитном порядке. Прежде чем продолжить, убедитесь, что указали ссылки на все требуемые диапазоны. Отметьте галочкой параметры Use labels in (Использовать в качестве имен): Top Row (Подписи верхней строки) и Left Column (Значения левого столбца). Поставьте галочку также для Create links to source data (Создавать связи с исходными данными) и нажмите ОК.

Консолидированные данные

Когда нажмете ОК, Excel обобщит все выбранные данные на новом листе. Вдоль левого края экрана Вы увидите инструменты группировки, которые можно использовать, чтобы отображать и скрывать данные.

Если был выбран пункт Create links to source data (Создавать связи с исходными данными), то полученные данные ссылаются на содержащие их исходные ячейки. Кликнув по ячейке с данными (не по ячейке с суммой), Вы увидите ссылку на лист и ячейку, содержащую эти данные.

Если вы не отметили параметр Create links to source data (Создавать связи с исходными данными), то полученная консолидация – это просто обобщение данных без каких-либо подробностей, без группировки и содержащее только результаты суммирования.

Полезный совет

Так как эти данные содержат ссылки, Вы можете использовать инструмент Trace Precedents (Влияющие ячейки), чтобы перейти к исходной ячейке, содержащей данные. Для этого кликните по ячейке, содержащей интересующие Вас данные. Откройте вкладку Formulas (Формулы) и найдите кнопку Trace Precedents (Влияющие ячейки). Поскольку исходная ячейка находится на другом листе, наведите указатель мыши на появившуюся чёрную стрелку, чтобы указатель принял вид пустой белой стрелки. Дважды щелкните, чтобы открыть диалоговое окно Go To (Переход) – ссылка на ячейку будет указана в этом окне. Кликните по ссылке и далее нажмите ОК, чтобы перейти к нужному месту.

Форматируем данные

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

Разные рабочие книги

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

Для этого потребуется выполнить те же самые действия, как мы делали только что: выберите пустой лист или добавьте новый, нажав Insert Sheet (Вставить лист) на вкладке Insert (Вставка). Нажмите команду Consolidate (Консолидация). На этот раз вместо того, чтобы выбрать лист в текущей рабочей книге, нажмите кнопку Browse (Обзор), чтобы открыть другую рабочую книгу.

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

Мне стало легче переключаться между открытыми рабочими книгами, когда я добавил кнопку Switch Windows (Перейти в другое окно) на Панель быстрого доступа.

Если включить параметр Create Links to Source Data (Создавать связи с исходными данными), то, когда сведение будет выполнено, все изменения в исходных листах и рабочих книгах будут отображаться и в консолидированных данных. Второй столбец в обобщенных данных все также будет отображать название рабочей книги, а команда Trace Precedents (Влияющие ячейки) быстро перенесет Вас к ячейкам, связанным ссылкой, если соответствующая рабочая книга открыта, но не сработает, если она закрыта.

Обновляем консолидацию

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

На вкладке Data (Данные) нажмите Consolidate (Консолидация) – Вы увидите, что указанные ранее ссылки сохранились. Внесите изменения, добавив или удалив диапазоны, или изменив их размер, и нажмите ОК, чтобы создать консолидацию заново.

Предостережение!

Если Вы включаете параметр Create Links to Source Data (Создавать связи с исходными данными), то Вы не сможете обновить консолидированные данные, нажав на вкладке Data (Данные) команду Consolidate (Консолидация), не удалив предварительно старые результаты. Причина в том, что в таком случае вместо обновления будет сделана попытка вставить одну консолидацию в другую, что в результате приведет к полной чепухе. Вы можете обновить диапазоны, изменяя формулы вручную, но более целесообразно будет создать консолидацию заново.

Если же Вы не включили параметр Create Links to Source Data (Создавать связи с исходными данными), то Ваши сведенные данные представляют из себя просто обобщение, без каких-либо подробностей. В таком случае, чтобы обновить консолидацию, кликните по верхней ячейке, содержащей результаты, затем нажмите на вкладке Data (Данные) команду Consolidate (Консолидация), внесите все необходимые изменения и нажмите ОК.

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