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

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

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

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

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

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

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

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

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

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

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

1. Если вы еще не сделали этого, для этого нужно сделать следующее:

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

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

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

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

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

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

4. org/ListItem»>

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

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

5. Выделите данные.

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

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

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

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

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

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

   Примечания: 

   • org/ListItem»>

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

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

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

   • org/ListItem»>

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

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

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

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

 

Совет: Чтобы ввести ссылку на ячейку, например «Продажи»! B4— в формуле, не вводя текст, введите формулу до нужной точки, а затем перейдите на вкладку и щелкните ячейку. Excel заполнит имя листа и адрес ячейки. ПРИМЕЧАНИЕ. Формулы в таких случаях могут быть непреднамерены, так как можно легко случайно выбрать неправильная ячейка. Ошибку также сложно обнаружить после ввода сложной формулы.

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Сочетания клавиш и горячие клавиши в Excel

Функции Excel (по алфавиту)

Функции Excel (по категориям)

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

Автор Амина С.

На чтение 10 мин Опубликовано 14.08.2020

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

Содержание

1. Что такое консолидация данных
2. Консолидация данных с нескольких листов
3. Консолидация данных с нескольких таблиц в одну
4. Для чего может использоваться консолидация данных
5. Представителям каких профессий пригодится функция консолидации данных
6. Как сделать консолидацию данных в Excel
7. Условия для выполнения процедуры консолидации
8. Консолидация по расположению
9. Консолидация по категории
10. Консолидация данных в Excel при помощи формул
11. Консолидация данных в Excel: практика
12. Консолидация данных в 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

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

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

Оцените качество статьи. Нам важно ваше мнение:

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

Количество просмотров: 947

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

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

Рисунок 1

Хотелось бы заметить, что в исходной таблице было почти 5 тысяч строк,10 граф. Однако после консолидации все несущественные столбцы были удалены, число же строк после консолидирования данных в Excel по каждой дате поступлений сократилось до 15. Другими словами, вместо большой таблицы мы имеем короткую, сжатую и понятную итоговую информацию

Консолидация по сути очень похожа на инструмент ПРОМЕЖУТОЧНЫЙ ИТОГ, но имеет важные отличия.

1. ПРОМЕЖУТОЧНЫЙ ИТОГ может работать только с одной таблицей, а количество таблиц в консолидации может быть до 255
2. ПРОМЕЖУТОЧНЫЙ ИТОГ требует обязательной сортировки колонки с критериями, для которых ведется расчет. КОНСОЛИДАЦИЯ сама группирует данные независимо от их расположения в колонке
3. ПРОМЕЖУТОЧНЫЙ ИТОГ выводит результат по умолчанию выводит результат только в последней графе, остальные нужно указывать вручную. Консолидация подводит итоги во всех столбцах, кроме первого.
4. ПРОМЕЖУТОЧНЫЙ ИТОГ выводи результат непосредственно в таблице, консолидация может использовать данные из любого диапазона.

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

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

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

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

Создание консолидации в Excel – процесс несложный и доступный даже новичку. Разберем его на примере консолидирования одной таблицы. Делаем следующее:

1. Выбираем на отдельном чистом листе ячейку для начала будущей таблицы консолидации и на вкладке ВСТАВКА нажимаем кнопку КОНСОЛИДАЦИЯ.

Рисунок 2

Если в книге пустого листа нет, его надо создать. Выбираем функцию для вычисления. Выбор довольно большой, есть фактически все необходимые варианты. Присутствует сумма, количество (количество позиций), максимум, минимум, среднее, отклонение и другие варианты для расчета. Надо учесть, что консолидация не умеет вычислять математические функции с ТЕКСТОВЫМИ ДАННЫМИ. По сути, это не умеет делать никакая программа. Так что если вы будете обрабатывать нечисловые данные, то максимум что вы сможете сделать, это подсчитать количество значений.

рисунок 3
После выбора нажимаем кнопку ДОБАВИТЬ, и наша выделенная таблица попадает в список диапазонов. Если что-то выбрано неправильно, то можно нажать кнопку УДАЛИТЬ и повторить процесс заново. Кроме этого, не забудьте, что после создания таблицы консолидации на этой странице Excel «запомнит» исходные данные. Поэтому вы всегда можете запустить на ней создание консолидации и поменять в частности расчет. Сам адрес исходный диапазон будет сохранен
Нажимаем в строке ссылка и выделяем нужную таблицу. При этом выделение надо начинать с колонки, по позициям которой нужно консолидировать данные и при этом захватить заголовок. Если нужная колонка с позициями находится в конце или середине таблицы, то перед началом консолидации ее надо скопировать в начало таблицы. Кроме этого, учтите, что консолидация не работает с несмежными диапазонами. Так что, если между колонкой с позициями и колонкой с данными есть дополнительный столбец, то его придется захватить.

2. Теперь ОБЯЗАТЕЛЬНО ставим внизу ОБА флажка (галочки) под надписью ИСПОЛЬЗОВАТЬ В КАЧЕСТВЕ ИМЕН. Если не включить в качестве имен заголовки, у вас будут безымянные колонки. Итог получится примерно такой.

Рисунок 4

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

Рисунок 5

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

Флажок же СОЗДАВАТЬ СВЯЗИ поставьте если только это действительно необходимо. К примеру, если вы собираете с помощью консолидации ежемесячный отчет, то вряд ли вам понравится, когда из-за включенной связи данные за март попадут в отчет за январь. С другой стороны, если вы собираете постоянно итоговый отчет на текущий момент, эта галочка будет полезна. С другой стороны, несомненным плюсом создания связей является возможность обновления данных. Достаточно, находясь на нужном листе, на вкладке ДАННЫЕ нажать кнопку ОБНОВИТЬ ВСЕ, и вы получите свежие данные из исходной таблицы. Однако при добавлении данных в новые строки исходной таблицы вам в любом случае придется создавать консолидацию заново. После создания можно удалить ненужные колонки. Например, можно по каждому наименованию получить таблицу консолидации по ценам, но при выборе диапазона захватить также и столбец с количеством единиц товара. Для цен вывести среднее значение, затем создать новую консолидацию рядом с предыдущей. Так как она на одном листе с консолидацией цен, то ничего дополнительно выделять уже не нужно. В этой консолидации просуммировать количество единиц по каждому наименованию. Наконец, удалить ненужные столбцы, получить итоговую таблицу консолидирования с средними ценами и общим количеством по каждому наименованию товара.

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

Рисунок 6

Кстати, именно из-за этого говорят, что таблицы для консолидации должны иметь абсолютно одинаковый размер и начинаться с одной ячейки. На самом деле это ПОЛНАЯ ЧУШЬ. Таблица для создания таблицы консолидации может начинаться откуда угодно и иметь абсолютно разное количество строк. А вот заголовок у всех таблиц действительно должен быть одинаковым. В следующем примере показан консолидированный годовой отчет по городам Актау, Караганда и Семей. Сразу отмечу, что не во всех городах есть продажи по каждому из наименований за каждый месяц. Тем не менее такая ситуация никак не влияет на создание таблицы консолидации в Excel. Обратите внимание на адреса диапазонов!

Рисунок 7

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

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

Второй способ заключается в том, что файл должен быть предварительно открыт. Тогда можно выбрать его на панели задач рабочего стола, а затем указать нужную таблицу. В этом случае надо быть особо внимательным, так как когда файлов много, то после добавления диапазона, при попытке выбрать новый диапазон, Excel попытается вернуться в ту же книгу, откуда уже был добавлен диапазон. Обойти это можно удалением содержимого строки ИСТОЧНИК после КАЖДОГО ДОБАВЛЕНИЯ ДИАПАЗОНА.

Рисунок 8

Обратите внимание, что на примере выше первый диапазон был в том же файле, что и создаваемая таблица консолидации. Второй диапазон был получен из файла Алматы, который предварительно был открыт. Ну а третий был получен из закрытого файла Нур-султан.xlsx по имени диапазона. Для удобства этот диапазон имеет такое же имя, как и сам файл, но из-за ограничений на структуру имен в нем дефис заменен на знак подчеркивания.  

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

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

Желаю всем успешной работы и хорошего настроения! Если остались вопросы, пишите в комментариях

 

 

119410cookie-checkКонсолидация данных в Excel. no

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

Excel

Введите и отформатируйте данные

Проверка данных

Проверка данных

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

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Дополнительно… Меньше

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

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

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

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

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

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

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

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

Выполните следующие действия, чтобы объединить несколько рабочих листов в главный рабочий лист:

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

   • org/ListItem»>

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

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

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

2. В мастер-листе щелкните верхнюю левую ячейку области, в которой должны отображаться консолидированные данные.

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

3. Щелкните Данные > Консолидировать (в группе Инструменты данных ).

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

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

5. org/ListItem»>

   Выберите свои данные.

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

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

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

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

6. org/ListItem»>

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

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

   Примечания:

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

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

8. Нажмите OK , и Excel создаст для вас консолидацию. По желанию можно применить форматирование. Форматирование необходимо только один раз, если только вы не запустите консолидацию повторно.

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

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

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

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

Совет: Чтобы ввести ссылку на ячейку, например Sales!B4, в формулу без ввода, введите формулу до нужного места, затем перейдите на вкладку листа и щелкните ячейку. Excel дополнит имя листа и адрес ячейки за вас. ПРИМЕЧАНИЕ. Формулы в таких случаях могут быть подвержены ошибкам, так как очень легко случайно выбрать не ту ячейку. Также может быть трудно обнаружить ошибку после ввода сложной формулы.

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

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

.

Нужна дополнительная помощь?

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

См. также

Обзор формул в Excel

Как избежать неработающих формул

Найдите и исправьте ошибки в формулах

Сочетания клавиш и функциональные клавиши Excel

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Получение консолидированных данных в Excel: подробное руководство

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

В этом руководстве мы демистифицируем процесс Excel для финансовых специалистов. Давайте начнем.

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

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

Excel делает это возможным с помощью инструмента «Консолидация». Удобная функция для аналитиков FP&A, она существует уже более двух десятилетий. Его основная функция проста: объединение информации из нескольких рабочих листов или рабочих книг и отображение ее в аккуратной сводке. Расположенный на вкладке «Данные», он помогает систематизировать данные для будущего анализа.

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

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

Зачем консолидировать данные в Excel?

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

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

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

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

Как консолидировать данные в Excel

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

1. По положению

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

2. По категориям

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

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

3. По формуле

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

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

Консолидация данных из нескольких рабочих листов в одной рабочей книге

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

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

Шаги консолидации:

Вот рабочие листы, которые мы придумали за три года, и мы назвали их: Год 2016, Год 2017 и 2018 год соответственно.

Как видите, у нас отсутствуют значения в ячейках B5, B4 и E6 на всех трех листах. Это означает, что в марте 2016 года Дэвид никогда не получал комиссионных; Ханна в апреле 2017 года; и Джой в мае 2018 года. Строка Джой в 2017 году не учтена, как и строка Алекса в 2018 году. Также отсутствует столбец в 2018 году, что означает, что ни один сотрудник не получил комиссионные за апрель.

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

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

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

Шаг 1:

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

Шаг 2:

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

Шаг 3:

На ленте Excel перейдите к «Данные», затем перейдите к «Консолидировать»:

Шаг 4:

Щелкните раскрывающийся список под «Функция». Вы получите различные варианты, такие как Sum, Min, Count, Max и другие. Мы выбрали «Сумма», потому что нам нужно сложить значения всех трех листов вместе.

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

Шаг 5:

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

В справочном разделе появится всплывающее окно, показывающее имя вашего рабочего листа с восклицательным знаком рядом с ним, а также подробный диапазон выбранных строк и столбцов. Например, мы получаем «2016 год !$A$3:$E$5».

Если вы хотите объединить данные из разных файлов, перейдите в «Обзор» и выберите файл. Затем нажмите «Открыть».

Шаг 6: 

Найдите кнопку «Добавить» рядом с «Все ссылки» и нажмите ее. Таким образом, вы включите диапазон 2016 года в свой объединенный список ссылок.

Шаг 7: 

Повторите шаги 5, 6 и 7 для остальных рабочих листов.

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

Шаг 8:

Установите все флажки в разделе «Использовать метки в».

Когда мы проверяем «Верхнюю строку», мы включаем инструмент консолидации для включения наших меток в самые высокие строки: прибыль за февраль, март, апрель и май. Точно так же, когда мы отмечаем «Левый столбец», мы разрешаем функциональности включать наши крайние левые ярлыки: Джой, Ханна, Эндрю, Алекс и Дэвид.

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

Шаг 9:  

Нажмите «ОК», чтобы разрешить Excel консолидировать вашу информацию на целевом листе.

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

После выполнения этих шагов вы получите конечный продукт, который выглядит примерно так:

Однако вы должны отметить несколько моментов:

• Вы можете создавать ссылки, только если исходная информация и сводная таблица находятся в разных рабочие листы. Когда они находятся на одном листе, консолидированная информация не может обновляться автоматически.
• Чтобы изменить диапазон вашей ссылки, вам сначала нужно удалить схему ссылки в разделе «Список», прежде чем создавать ее. После удаления верните его на свой лист и снова перетащите, чтобы получить подходящий диапазон.
• Если оставить «Верхнюю строку» и «Левый столбец» не отмеченными, платформа объединяет все ячейки, расположенные одинаково, например: ячейки C5 (2016 год) + C5 (2017 год) + C5 (2018 год). Избегайте этого, нажимая на коробки.

Datarails — это решение для Excel, которое вам нужно

Работа вручную над большинством задач, связанных с электронными таблицами, может быть сложной, утомительной и повторяющейся. Datarails предлагает технологию, которая автоматизирует работу с Excel, позволяя вам экономить время и выполнять работу без ошибок. Лучшая часть? Вам не нужно менять свои процессы; вы можете продолжать делать то, что вы делали, но теперь лучше.

Программное обеспечение поощряет финансовых аналитиков к независимой работе с данными и предоставлению информации на основе данных. Оптимизируйте связь между финансами и операциями вашей компании и поощряйте принятие более эффективных организационных решений с помощью программного обеспечения Datarails FP&A.

>> Нажмите здесь, чтобы прочитать о консолидированной финансовой отчетности

Как консолидировать данные в Excel (шаг за шагом) • Блог Layer

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

В этой статье вы найдете полное объяснение того, что такое консолидация данных в Excel, и пошаговое руководство о том, как консолидировать данные в Excel из нескольких рабочих листов в один рабочий лист. В качестве альтернативы Layer предлагает функции автоматизации, которые помогут вам быстро консолидировать данные из различных источников, включая Excel и Google Sheets.

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

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

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

Как консолидировать данные в Excel?

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

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

1. 1.  Создайте отдельный лист и переименуйте его в «Сводка» или аналогичный термин. Здесь Excel создаст сводную таблицу.

Как консолидировать данные в Excel (шаг за шагом) — отдельный лист

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

Как объединить данные в Excel (шаг за шагом) — нажмите на ячейку

Как объединить несколько файлов Excel в один

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

ЧИТАТЬ ДАЛЕЕ

ЧИТАТЬ ДАЛЕЕ

1. 3. Выберите Данные > Консолидировать.

Как консолидировать данные в Excel (шаг за шагом) — Консолидировать

1. 4. Щелкните раскрывающееся меню «Функция», а затем «Сумма», чтобы добавить значения всех рабочих листов.

Как консолидировать данные в Excel (шаг за шагом) — Функция > Сумма

1. 5. В «Справке» нажмите кнопку со стрелкой вверх, чтобы выбрать источник данных для консолидации.

Как консолидировать данные в Excel (шаг за шагом) — стрелка вверх

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

Как консолидировать данные в Excel (шаг за шагом) — Консолидация, ссылка

1. 7. Как показано ниже, Excel автоматически вводит диапазон исходных данных. Он показывает имя рабочего листа, за которым следует восклицательный знак, выбранные строки и столбцы. После выбора нажмите кнопку со стрелкой, чтобы вернуться в главное окно настроек «Консолидация».

Как консолидировать данные в Excel (шаг за шагом) — диапазон консолидации

1. 8. Нажмите «Добавить», чтобы включить в список «Все ссылки».

Как консолидировать данные в Excel (шаг за шагом) — все ссылки

1. 9. Повторите шаги 5, 6, 7 и 8 для остальных листов. Когда вы закончите, вы должны увидеть полный список справочных данных, как показано ниже.

Как консолидировать данные в Excel (шаг за шагом) — полный список

Руководство для начинающих по управлению версиями Excel

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

ЧИТАТЬ ДАЛЕЕ

1. 10. Выберите поля «Верхняя строка» и «Левая колонка», чтобы включить метки в верхних строках и метки в крайнем левом углу.

Как консолидировать данные в Excel (шаг за шагом) — верхние строки и левые столбцы

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

Как консолидировать данные в Excel (шаг за шагом) — создание ссылок

1. 12. Нажмите кнопки «+» слева, чтобы просмотреть исходные данные из нового основного листа.

Как консолидировать данные в Excel (шаг за шагом) — Нажмите +

1. 13. Если вы хотите отобразить эту исходную информацию для всех строк, вы можете нажать кнопку «2», прямо над первой кнопкой «+».

Как консолидировать данные в Excel (шаг за шагом) — нажмите 2

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

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

Как консолидировать данные в Google Таблицах?

Layer – это надстройка, которая предоставляет финансовым командам инструменты для повышения эффективности и качества данных в их процессах планирования и анализа поверх Google Таблиц. Делитесь частями своих Google Таблиц, отслеживайте, просматривайте и одобряйте изменения, а также синхронизируйте данные из разных источников — и все это за считанные секунды.

Используя Layer, вы можете:

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

Предложение с ограниченным сроком действия:  Зарегистрируйтесь сегодня и  получите бесплатный доступ к Layer , включая все платные функции, чтобы вы могли управлять, автоматизировать и масштабировать свои процессы FP&A поверх Google Sheets!

Оставьте это поле пустым

руководств и ресурсов по Excel и Google Sheets прямо в вашей почте!

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