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

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

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

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

Совет: При частом объединении данных возможно создание новых листов на основе шаблона листа, который использует одинаковый макет. Дополнительные сведения о шаблонах см. в статье Создание шаблона. Также советуем добавить в шаблон таблицы Excel.

Существует два способа консолидации данных: по положению или категории.

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

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

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

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

Чтобы объединить несколько листов в главный лист, выполните указанные ниже действия.

Если вы еще не сделали этого, настройте данные на каждом листе, выполнив указанные ниже действия.

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

Разместите каждый диапазон на отдельном листе, но не вводите ничего на главную лист, на котором вы планируете консолидировать данные. Excel сделает это автоматически.

Убедитесь, что все диапазоны имеют одинаковый макет.

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

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

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

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

Ниже приведен пример выбора трех диапазонов на листе.

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

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

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

Ниже приведен пример выбора трех диапазонов на листе.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник:
http://support.microsoft.com/ru-ru/office/%D0%BA%D0%BE%D0%BD%D1%81%D0%BE%D0%BB%D0%B8%D0%B4%D0%B0%D1%86%D0%B8%D1%8F-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85-%D1%81-%D0%BD%D0%B5%D1%81%D0%BA%D0%BE%D0%BB%D1%8C%D0%BA%D0%B8%D1%85-%D0%BB%D0%B8%D1%81%D1%82%D0%BE%D0%B2-007ce8f4-2fae-4fea-9ee5-a0b2c9e36d9b

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

Консолидация данных – опция в Excel, которая позволяет объединять данные из разных таблиц в одну, а также объединять листы, расположенные в одном или разных файлах, в один. Чем же так полезна консолидация для интернет-маркетолога?

Разберем два наглядных примера.

Пример №1

У нас есть статистика по ключевым словам из контекстной рекламы с привязкой к основным показателям – кликам, расходам, транзакциям и доходу.

Специальный отчет в Google Analytics

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

«Дубли» ключевых слов в статистике

Это связано с тем, что статистика выгружена из двух рекламных систем (Яндекс.Директ и Google Ads), и в Google Рекламе для ключевых слов используется модификатор широкого соответствия (знак «+»).

То есть, вместо двух строчек доставка цветов москва и +доставка +цветов +москва мы должны получить одну с объединенными данными. Ведь это один ключ и нам нужно оценить его эффективность.

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

Суммирование данных вручную

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

Но перед этим нужно не забыть воспользоваться функцией Заменить, убрав все + из таблицы.

Удаляем + в ключевых словах перед консолидацией

— переходим на соседний лист (так удобнее);

— выделяем ячейку, в которую хотим вставить данные;

— переходим в Данные — Консолидация

В открывшемся окне нас интересуют следующие настройки:

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

Нажимаем ОК. Если все сделали верно, то на текущем листе получите результат в виде объединенных (консолидированных) данных:

Итоговая таблица после консолидации

Таблица наша также уменьшилась. До консолидации было 252 строки, после объединения данных их стало 212:

ДО и ПОСЛЕ консолидации

Видео консолидации примера №1:

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

Пример №2

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

Да, можно просто выгрузить данные из рекламных кабинетов Яндекс.Директ и Google Ads за общий период. Но что делать, когда данные по показам, кликам, расходам и конверсиям привязаны к бизнес-показателям, которых нет в интерфейсах, но которые завязаны на контекстной рекламе? А если в каждый из этих месяцев работали разные рекламные кампании (в один месяц – 10, в другой – 5 и т.д.)? Опять ручной процесс сведения данных.

И для таких случаев пригодится Консолидация в Excel. Давайте сведем данные с помощью данной настройки на примере выгрузки интернет-магазина за 3 месяца в один отчет. Статистика по месяцам расположена на разных вкладках (сентябрь — ноябрь — декабрь).

Статистика по 3 месяцам на разных вкладках файла

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

В открывшемся окне нас интересуют следующие настройки:

  • ФункцияСумма (поскольку хотим суммировать данные);
  • Ссылка – выбираем весь диапазон данных на листах поочередно (с помощью кнопки Добавить);
  • Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

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

Если все сделали правильно, то на текущем листе получите результат в виде объединенных (консолидированных) данных с трех таблиц в одной:

Слева появятся новые значки:

Связи с исходными данными

  • 1 – свернуть все связи с исходными данными;
  • 2 – развернуть все связи с исходными данными.

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

Видео консолидации примера №2:

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

Читайте также  Как добавить график на график в Excel

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

Понравился эксперимент с видео? Поставь 5.0 статье ->

Источник:
http://osipenkov.ru/consolidate-excel/

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

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

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Хорошо заметно, что таблицы не одинаковы — у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы выполнить такую консолидацию:

  1. Заранее откройте исходные файлы
  2. Создайте новую пустую книгу (Ctrl + N)
  3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация(Data — Consolidate) . Откроется соответствующее окно:

  • Установите курсор в строку Ссылка(Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить(Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
  • Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

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

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

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

    Источник:
    http://www.planetaexcel.ru/techniques/2/138/

    Консолидированная таблица в 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

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

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

    Условия для выполнения процедуры консолидации

    Естественно, что не все таблицы можно консолидировать в одну, а только те, которые соответствуют определенным условиям:

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

    Создание консолидированной таблицы

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

    1. Открываем отдельный лист для консолидированной таблицы.
    2. На открывшемся листе отмечаем ячейку, которая будет являться верхней левой ячейкой новой таблицы.
    3. Находясь во вкладке «Данные» кликаем по кнопке «Консолидация», которая расположена на ленте в блоке инструментов «Работа с данными».
    4. Открывается окно настройки консолидации данных.

    В поле «Функция» требуется установить, какое действие с ячейками будет выполняться при совпадении строк и столбцов. Это могут быть следующие действия:

    • сумма;
    • количество;
    • среднее;
    • максимум;
    • минимум;
    • произведение;
    • количество чисел;
    • смещенное отклонение;
    • несмещенное отклонение;
    • смещенная дисперсия;
    • несмещенная дисперсия.

    В большинстве случаев используется функция «Сумма».

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

    Как видим, после этого диапазон добавляется в список.

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

    Если же нужный диапазон размещен в другой книге (файле), то сразу жмем на кнопку «Обзор…», выбираем файл на жестком диске или съемном носителе, а уже потом указанным выше способом выделяем диапазон ячеек в этом файле. Естественно, файл должен быть открыт.

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

    Для того, чтобы автоматически добавить название столбцов в шапку, ставим галочку около параметра «Подписи верхней строки». Для того, чтобы производилось суммирование данных устанавливаем галочку около параметра «Значения левого столбца». Если вы хотите, чтобы при обновлении данных в первичных таблицах обновлялась также и вся информация в консолидированной таблице, то обязательно следует установить галочку около параметра «Создавать связи с исходными данными». Но, в этом случае нужно учесть, что, если вы захотите в исходную таблицу добавить новые строки, то придется снять галочку с данного пункта и пересчитать значения вручную.

    Когда все настройки выполнены, жмем на кнопку «OK».

    Консолидированный отчет готов. Как видим, данные его сгруппированы. Чтобы посмотреть информацию внутри каждой группы, кликаем на плюсик слева от таблицы.

    Теперь содержимое группы доступно для просмотра. Аналогичным способом можно раскрыть и любую другую группу.

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

    Мы рады, что смогли помочь Вам в решении проблемы.

    Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

    Помогла ли вам эта статья?

    Привет всем! Сегодняшний материал для тех, кто продолжает осваивать работу с программами-приложениями, и не знает, как сделать сводную таблицу в excel.

    Создав общую таблицу, в каком либо из текстовых документов, можно осуществить её анализ, сделав в Excel сводные таблицы.

    Создание сводной Эксель таблицы требует соблюдения определенных условий:

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

    Как сделать сводную таблицу в excel: пошаговая инструкция

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

    Для создания сводной таблицы необходимо:

    Создался пустой лист, где видно списки областей и полей. Заголовки стали полями в нашей новой таблице. Сводная таблица будет формироваться путем перетаскивания полей.

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

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

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

    Категорию «Товары» мы поставим в виде строк. В «Названия строк» мы переносим необходимое нам поле.

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

    Столбец «Единицы», будучи в главной таблице, отображал количество товара проданного определенным продавцом по конкретной цене.

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

    Указываем периоды даты и шаг. Подтверждаем выбор.

    Видим такую таблицу.

    Сделаем перенос поля «Сумма» к области «Значения».

    Стало видно отображение чисел, а нам необходим именно числовой формат

    Для исправления, выделим ячейки, вызвав окно мышкой, выберем «Числовой формат».

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

    Оформление сводной таблицы

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

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

    Отдельно настраиваются и параметры поля. На примере мы видим, что определенный продавец Рома в конкретном месяце продал рубашек на конкретную сумму. Нажатием мышки мы в строке «Сумма по полю…» вызываем меню и выбираем «Параметры полей значений».

    Далее для сведения данных в поле выбираем «Количество». Подтверждаем выбор.

    Посмотрите на таблицу. По ней четко видно, что в один из месяцев продавец продал рубашки в количестве 2-х штук.

    Теперь меняем таблицу и делаем так, чтобы фильтр срабатывал по месяцам. Поле «Дата» мы переносим в «Фильтр отчета», а там где «Названия столбцов», будет «Продавец». Таблица отображает весь период продаж или за конкретный месяц.

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

    На самом деле рассказывать о настройках сводных таблиц можно еще очень долго. Проводите изменения под свой вкус, добиваясь удобного для вас пользования. Не бойтесь нажимать и экспериментировать. Любое действие вы всегда сможете изменить нажатием сочетания клавиш Ctrl+Z.

    Надеюсь, вы усвоили весь материал, и теперь знаете, как сделать сводную таблицу в excel.

    Источник:
    http://word-office.ru/konsolidirovannaya-tablica-v-excel-kak-sdelat.html

    Экономистам и финансистам приходится иметь дело с большим количеством данных и расчетов. Зачастую эта информация находится в разных таблицах, на разных листах программы Excel. Соответственно, возникает потребность создать сводную таблицу на основе объединения данных из разных источников. Из статьи вы узнаете, как быстро получить сводные итоги по нужным позициям, как консолидировать данные из нескольких таблиц.

    КАК БЫСТРО ПОЛУЧИТЬ СВОДНЫЕ ИТОГИ ПО НУЖНЫМ ПОЗИЦИЯМ

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

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

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

    Предположим, есть табл. 1, в которой за определенный период времени обобщены данные о продажах в разрезе номенклатуры товаров и продавцов.

    Таблица 1 довольно большая — 511 строк. Обработать вручную такой массив показателей не так просто, а для полноценного анализа нужны сводные данные о продажах в разрезе продавцов. Чтобы получить их, ставим курсор на первую ячейку с информацией, которую требуется объединить. Это будет ячейка «Продавец».

    Выбираем: ВставкаСводная таблица. Откроется меню «Создание сводной таблицы» (рис. 1).

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

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

    В нашем случае это поле «Продавец» и поле «Сумма продаж». Ставим соответствующие галочки и получаем следующий готовый результат (рис. 3).

    Общий итог продаж за период составляет 158 690,04 руб. Выведены и данные продаж по каждому продавцу.

    КАК КОНСОЛИДИРОВАТЬ ДАННЫЕ ИЗ НЕСКОЛЬКИХ ТАБЛИЦ

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

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

    Предположим, у нас есть три таблицы, содержащие поквартальные данные о зарплате работников (табл. 2–4). Они расположены на разных листах одного файла.


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

    Займемся консолидацией. Выбираем диапазон каждой таблицы и нажимаем кнопку «Добавить» (рис. 4).

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

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

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

    После проделанных действий получим сводную таблицу (рис. 5).

    В консолидированной таблице корректно обобщены все необходимые данные.

    Д. В. Кислов, канд. экон. наук

    Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 10, 2020.

    Источник:
    http://www.profiz.ru/peo/10_2020/konsolidaciya_dannyh/

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

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

    Что такое консолидация данных

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

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

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

    1

    Если мы загрузим всю эту информацию в Excel, то увидим, что много строк по сути повторяются.

    2

    Причина этого в том, что в Яндекс.Директ и Google Ads используется так называемый модификатор широкого соответствия. Из-за этого нам требуется объединить несколько строк в одну, которая является фактически одним ключевым словом, и его эффективность нужно оценивать.

    Многие люди делают это самостоятельно, что требует огромного вложения времени и усилий.

    3

    В Excel есть отдельная функция, которая позволяет делать это, которая называется «Консолидация данных». Также возможны некоторые другие способы консолидации данных. Давайте их рассмотрим более подробно.

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

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

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

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

    Для чего может использоваться консолидация данных

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

    Представителям каких профессий пригодится функция консолидации данных

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

    1. Бухгалтер.
    2. Инвестор.
    3. Трейдер.
    4. Математик.
    5. Ученый абсолютной любой специальности, требующей умения статистически обрабатывать информацию, начиная математиками и заканчивая социологами и психологами.

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

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

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

    Условия для выполнения процедуры консолидации

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

    Давайте их перечислим более подробно:

    1. Названия колонок должны быть одинаковыми. При этом допускаются небольшие отклонения от изначального формата. Например, можно переставлять местами колонки. Эксель все равно сможет выхватить правильные значения.
    2. Не должно быть ни одной ячейки, которая не содержала бы никаких значений.
    3. В целом, все таблицы должны быть организованы приблизительно по одинаковому шаблону.

    Консолидация по расположению

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

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

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

    6

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

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

    7

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

    8

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

    9

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

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

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

    Консолидация по категории

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

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

    Давайте приведем простой пример, как это работает.

    Предположим, у нас есть магазин, в котором продаются товары, обозначенные разными индексами (1, 2, 3, 4 и так далее). У нас есть несколько таблиц, описывающих их, и некоторые наименования повторяются. Что делать в такой ситуации?

    4

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

    5

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

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

    Консолидация данных в Excel при помощи формул

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

    10

    Как с помощью формулы их просуммировать? Наиболее простой вариант -это просто ввести формулу типа такой.

    =’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

    Также можно воспользоваться функцией СУММ , которая складывает значения из определенных диапазонов и возвращает результат в ту ячейку, где она прописывается.

    =СУММ(‘2001 год:2003 год’!B3)

    С помощью этого метода можно объединять данные из нескольких разных таблиц.

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

    Предположим, у нас есть такая таблица.

    11

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

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

    Также можно воспользоваться кнопкой «Обзор», чтобы указать путь к файлу Экселю. Или же банальным переключением стандартными средствами Windows.

    В результате, мы получаем такую таблицу.

    12

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

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

    13

    После этого в первой ячейке вводим такую формулу.

    =’1 квартал’!B2+’2 квартал’!B2+’3 квартал’!B2.

    После этого соответствующие ячейки всех трех листов объединятся. Далее осталось просто скопировать эту формулу на всю колонку.

    14

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

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

    Источник:
    http://office-guru.ru/excel/spiski-dannykh/konsolidaciya-dannyx-v-excel-primery-ispolzovaniya.html