Категория: Инструкции
Существует много способов сведения данных в 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 (Консолидация), внесите все необходимые изменения и нажмите ОК .
Достоинство инструмента Консолидация в том, что он позволяет оставить данные там, где они изначально находились (т.е. на разных листах и даже в разных книгах), но все равно объединить их для проведения аналитической работы. Да, консолидация не позволит изменять порядок данных, как это можно сделать в сводной таблице. Но в случае, когда такой функционал не требуется, и нужно простое обобщение информации, причем представленное в такой же форме, как и исходные данные, то консолидация – это простое и быстрое решение.
50 видеоуроков, 0.2Gb
Консолидация в Excel 2010
Процесс консолидации данных представляет собой объединение значений из нескольких диапазонов данных, например, сведение в итоги значений, находящихся в диапазонах на разных рабочих листах. Рассмотрим, как консолидировать данные.
Для начала нам понадобится два диапазона данных, представленных в формате списка: первая строка каждого столбца содержит название, а остальные строки — однотипные данные. При этом данные должны размещаться на разных листах рабочей книги.
Теперь необходимо задать каждому диапазону собственное имя. Для этого выделите диапазон данных и в строке имени введите новое имя для диапазона.
Аналогичным образом введите имя и для второго диапазона значений.
Теперь перейдите на пустой лист, предназначенный для консолидации данных, и выделите на нем ячейку. При этом справа и снизу данной ячейки должны быть свободные ячейки для данных консолидации.
Перейдите на вкладку «Данные» и в группе «Работа с данными» выполните команду «Консолидация».
В появившемся диалоговом окне «Консолидация» раскройте выпадающий список «Функция» и выберите итоговую функцию, которая будет применяться ко всему диапазону табличных данных. Например, отметьте пункт «Произведение».
В поле «Ссылка» необходимо ввести первый диапазон данных. Для этого кликните по пиктограмме, расположенной справа от поля и перейдите к первому листу, содержащему данные.
Выделите диапазон ячеек и, вновь кликнув по данной пиктограмме, нажмите кнопку «Добавить». Заданный диапазон отобразится в поле «Список диапазонов».
Аналогичным образом, выделите и добавьте второй диапазон значений.
После ввода диапазонов нажмите кнопку «ОК».
Как вы можете заметить, появилась новая таблица, значения которой соответствуют произведению данных первого диапазона на данные второго.
Таким образом, используя консолидацию табличных данных, вы сможете выполнять сведение значений различных таблиц, находящихся на разных листах.
"Консолидация данных в Excel»
Консолидация данных в Excel
НазначениеФункция консолидация используется в том случае, если необходимо вычислить итоги для данных, расположенных в различных областях таблицы. С помощью функции консолидация над значениями, расположенными в несмежных диапазонах ячеек, можно выполнить такие операции, как сумма значений, количество значений, среднее арифметическое и другие.
Подлежащие консолидации диапазоны ячеек могут располагаться как на одном рабочем листе, так и на разных листах, равно как и в различных книгах. С помощью консолидации могут быть сведены в одной таблице различные итоговые данные, например об обороте нескольких филиалов банка.
При консолидации данных объединяются значения из нескольких диапазонов данных. Например, если имеется лист расходов для каждого регионального представительства, консолидацию можно использовать для преобразования этих данных в лист корпоративных расходов.
Консолидировать данные в Microsoft Excel можно несколькими способами. Наиболее удобный метод заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами.
Методы консолидации данныхВ табличном редакторе MicrosoftExcelпредусмотрено несколько способов консолидации:
С помощью трехмерных ссылок, что является наиболее предпочтительным способом. При использовании трехмерных ссылок отсутствуют ограничения по расположению данных в исходных областях.
По расположению, если данные исходных областей находятся в одном и том же месте и размещены в одном и том же порядке. Используйте этот способ для консолидации данных нескольких листов, созданных на основе одного шаблона.
Если данные, вводимые с помощью нескольких листов-форм, необходимо выводить на отдельные листы, используйте мастер шаблонов с функцией автоматического сбора данных.
По категориям, если данные исходных областей не упорядочены, но имеют одни и те же заголовки. Используйте этот способ для консолидации данных листов, имеющих разную структуру, но одинаковые заголовки.
С помощью сводной таблицы. Этот способ сходен с консолидацией по категориям, но обеспечивает большую гибкость при реорганизации категорий.
В качестве описания практического применения методов приведем следующий пример.
Предположим, для предоставления отчетов в налоговую инспекцию и пенсионный фонд необходимо предоставлять каждый квартал отчет нарастающим итогом по заработной плате каждого из сотрудников предприятия. Таким образом, в конце каждого отчетного периода данные по заработной плате и удержанным налогам всех сотрудников предприятия сводятся в один отчет, суммируются и анализируются.
Необходимо по возможности автоматизировать процесс составления отчетности, минимизировать вероятность ошибки и оптимизировать процесс корректировки исходных данных на этапе составления отчета в конце каждого из отчетных периодов.
Для решения поставленной задачи в табличном процессоре Excelвыполните следующие действия:
Сначала введите Ваши исходные данные таблицу расчета заработной платы: размер заработной платы, величину подоходного налога и сумму к выплате. Вставьте эти данные для нашего примера в диапазон ячеек B3:B6 на листах «Январь » - «Июнь », как показано на рисунке.
Ввод исходных данных в таблицу расчета заработной платы
Консолидация данных по расположениюКонсолидацию по расположению следует использовать в случае, если данные всех исходных областей находятся в одном месте и размещены в одинаковом порядке; например, если имеются данные из нескольких листов, созданных на основе одного шаблона, как показано на рисунке 1.
Создайте в книге «Заработная плата 2002 год » новый лист с именем «Консолидация » Укажите верхнюю левую ячейкуконечнойобласти консолидируемых данных, т.е. левый верхний угол области в которую будут вставляться ячейки с результатами.
В меню Данные выберите командуКонсолидация. как показано на рисунке.
Выбор пункта Консолидация в меню Данные
Выберите из раскрывающегося списка Функция функцию, которую следует использовать для обработки данных, как показано на рисунке.
Функции обработки при консолидации данных
Перечень доступных функций для консолидации данных и результат их выполнения приведены в таблице. В нашем примере для составления отчетности нарастающим итогом необходимо выбрать функцию Сумма .
Перечень доступных функций для консолидации данных
Введите в поле Ссылка исходную областьконсолидируемых данных и нажмите кнопкуДобавить. как показано на рисунке.
Определение функции для консолидации данных по диапазону
Данные действия необходимо выполнить для всех консолидируемых исходных областей, в нашем примере для листов начиная с «Январь » по «Июнь ».
Чтобы автоматически обновлять итоговую таблицу при изменении источников исходных данных, установите флажок Создавать связи с исходными данными. Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже входящие в консолидацию.
Примечание Так же необходимо помнить, что при консолидации данных по расположению заголовки категорий исходных областей не копируются автоматически в область назначения. Если в области назначения требуется разместить заголовки, скопируйте или введите их вручную, как это показано на рисунке.
Создание заголовка для консолидируемых данных в области назначения
Результат консолидации данных по расположению приведен на рисунках. Результат можно представить и отправить на печать в развернутом и кратком форматах.
Сводная таблица расчета заработной платы сотрудников за полугодие 2002 года
Посмотрите пример консолидации данных по заработной плате работников
Консолидация данных с использованием трехмерных ссылокПри использовании в формулах трехмерных ссылок не существует ограничений на расположение отдельных диапазонов данных. Консолидацию можно изменять любыми способами. Консолидация автоматически обновляется при изменении данных в исходном диапазоне.
Пример консолидации данных с использованием трехмерных ссылок
Для реализации консолидации данных с использованием трехмерных ссылок в табличном процессоре Excelвыполните следующие действия:
На листе консолидации скопируйте или задайте надписи для данных консолидации.
Укажите ячейку, в которую следует поместить данные консолидации.
Введите формулу. Она должна включать ссылки на исходные ячейки каждого листа, содержащего данные, для которых будет выполняться консолидация. Для получения сведений об источниках данных нажмите. Данные действия необходимо выполнить для всех для всех консолидируемых исходных областей.
ПримечаниеТак же необходимо помнить, что для того чтобы ввести ссылку, не используя клавиши на клавиатуре, введите формулу до того места, где требуется вставить ссылку, а затем укажите на листе нужную ячейку. Если ячейка находится на другом листе, перейдите на этот лист и укажите нужную ячейку .
Чтобы упростить слежение за исходными областями, поименуйте каждый диапазон и используйте имена в поле Ссылка. См. сведения о подписях и именах в формулах.
Правила ввода трехмерных ссылок на исходные области в формулах консолидации данных
На том же листе Если исходные области и область назначения находятся на одном листе, используйте имена или ссылки на диапазоны.
На разных листах Если исходные области и область назначения находятся на разных листах, используйте имя листа и имя или ссылку на диапазон. Например, чтобы включить диапазон с заголовком «Зарплата », находящийся в книге на листе «Январь », введите:
В разных книгах Если исходные области и область назначения находятся в разных книгах, используйте имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Зарплата » с листа «Январь » книги «Заработная плата 2002 год », введите:
'[Заработная плата 2002 год.xls] Январь'! Зарплата
На разных устройствах Если исходные области и область назначения находятся в разных книгах разных каталогов диска, используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Зарплата » с листа «Январь » книги «Заработная плата 2002 год », которая находится в папке «Отчетность », введите:
'[С:\Отчетность\Заработная плата 2002 год.xls] Январь'! Зарплата
Примечание Так же необходимо помнить, что для того чтобы задать описание источника данных, не нажимая клавиш клавиатуры, укажите полеСсылка. а затем выделите исходную область. Чтобы задать исходную область в другой книге, нажмите кнопкуОбзор. Чтобы убрать диалоговое окноКонсолидация на время выбора исходной области, нажмите кнопкуСвернуть диалоговое окно .
Консолидация данных по категорииКонсолидацию по категории следует использовать в случае, если требуется обобщить набор листов, имеющих одинаковые заголовки рядов и столбцов, но различную организацию данных. Этот способ позволяет консолидировать данные с одинаковыми заголовками со всех листов.
Если установлено автоматическое обновление консолидации при изменении исходных данных, изменить набор ячеек и диапазонов, входящих в консолидацию, невозможно. Данная функция доступна только при обновлении консолидации вручную.
Для реализации консолидации данных по категории в табличном процессоре Excelвыполните следующие действия:
Укажите верхнюю левую ячейку конечной области консолидируемых данных.
В меню Данные выберите командуКонсолидация .
Выберите из раскрывающегося списка Функция функцию, которую следует использовать для обработки данных.
Введите исходную область консолидируемых данных в поле Ссылка. Убедитесь, что исходная область имеет заголовок. Для получения более подробных сведений об источниках данных нажмите.
Нажмите кнопку Добавить .
Повторите шаги 4 и 5 для всех консолидируемых исходных областей.
В наборе флажков Использовать в качестве имен установите флажки, соответствующие расположению в исходной области заголовков: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно.
Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок Создавать связи с исходными данными .
Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже входящие в консолидацию.
ПримечаниеТак же необходимо помнить, что если метки в одной из исходных областей не совпадают с метками в других исходных областях, то при консолидации данных для них будут созданы отдельные строки или столбцы.
Консолидация данных в отчете сводной таблицыМожно создать отчет сводной таблицы из нескольких диапазонов консолидации. Данный метод сходен с консолидацией по категории, однако обладает большей гибкостью в отношении реорганизации категорий.
Если данные вводятся в несколько листов-форм, основанных на одном, и при этом требуется объединить данные из форм на отдельном листе, следует воспользоваться мастером шаблонов с функцией автоматического сбора данных.
Для реализации консолидации данных в отчете сводной таблицы в табличном процессоре Excelвыполните следующие действия:
Откройте книгу, в которой требуется создать отчет сводной диаграммы.
Если отчет создается на основе списка Microsoft Excel или базы данных, щелкните ячейку в этом списке или базе данных.
Выберите в меню Данные команду Сводная таблица, как показано на рисунке.
Выбор пункта Сводная таблица в меню Данные
На шаге 1 выполнения мастера сводных таблиц и диаграмм установите переключатель Вид создаваемого отчета в положениеСводная таблица. как показано на рисунке.
Выбор создаваемого отчета
Следуйте инструкциям на шаге 2 мастера.
Выполните одно из следующих действий:
Если на шаге 3 была нажата кнопка Макет. выполните формирование макета отчета, нажмите кнопкуOK в диалоговом окнеМастер сводных таблиц и диаграмм–Макет. а затем кнопкуГотово для создания отчета.
Если кнопка Макет на шаге 3 не была нажата, нажмите кнопкуГотово. а затем сформируйте макет отчета на листе.
.jpg)