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

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

Требования к таблицам

Обращаем ваше внимание на то, что не всегда можно использовать «Консолидацию». Чтобы это было возможно, исходные таблицы (они могут находиться на одном листе или на разных листах / книгах) должны соответствовать следующим требованиям:

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

Выполняем консолидацию

После использования функции «Консолидация» формируется новая так называемая консолидированная таблица. Давайте рассмотрим пошаговый практический пример, как собрать его из 3-х таблиц, отвечающих перечисленным выше требованиям. Итак, выполняем следующие действия:

1. Щелкните значок, чтобы создать новый лист, после чего программа автоматически добавит его справа от текущего и переключится на него. Подробная информация об этом представлена ​​в нашей отдельной публикации — «Как добавить лист в Excel”. Примечание: Вы можете переместить добавленный лист в удобное для нас место (например, в конец списка), удерживая левую кнопку мыши, «закрепив» его на вкладке с названием. Вы также можете переименовать лист, если хотите (режим редактирования запускается двойным щелчком по имени, заканчивается нажатием Enter).
2. В добавленном листе мы находимся в ячейке, в которую собираемся вставить сводную таблицу (в нашем случае мы оставляем ее выбранной по умолчанию). Затем переходим на вкладку «Данные», находим группу инструментов «Работа с данными», где щелкаем по значку «Консолидация”.
3. На экране появится небольшое окно с настройками инструмента.
   
4. Здесь представлены следующие параметры:
   • Функция: нажав на текущую опцию, мы откроем список возможных действий, среди которых выбираем то, которое необходимо выполнить для консолидированных данных:
     • Сумма (выбрана по умолчанию; используется чаще всего, поэтому оставим ее в качестве примера);
     • Количество;
     • В среднем;
     • Максимум;
     • Минимум;
     • Опера;
     • Количество номеров;
     • Среднеквадратичное отклонение;
     • Беспристрастное отклонение;
     • Дисперсия;
     • Беспристрастная дисперсия.
       
5. Перейдите в поле «Ссылка», щелкнув по нему. Здесь мы, в свою очередь, выбираем диапазон ячеек в исходных таблицах для обработки. Из-за этого:
   • Переключитесь на лист с первой таблицей.
   • Видим, что имя выбранного листа появилось автоматически в поле (но если не было смены листа, имя не добавится). Теперь, удерживая нажатой левую кнопку мыши, выберите таблицу вместе с заголовком (например, от самой верхней левой ячейки до нижней правой ячейки). Убедитесь, что координаты выбранных элементов указаны правильно в поле, затем нажмите кнопку «Добавить». Кстати, вы также можете указать / изменить координаты вручную, введя их с клавиатуры, но это не так удобно, как при работе с мышью, к тому же в этом случае есть вероятность ошибиться. Примечание. Чтобы выбрать диапазон из другого файла, сначала откройте его в программе. Затем, выполнив функцию консолидации в первой книге и находясь в поле «Ссылка», перейдите во вторую книгу, выберите в ней нужный лист и выберите необходимый диапазон ячеек. В этом случае имя файла необходимо добавить в начало ссылки. В нашем случае в этом нет необходимости, мы просто показали, как это можно сделать.
6. Следовательно, первый элемент, соответствующий сделанному нами выбору, появился в «Списке диапазонов.
7. Вернемся к полю «Ссылка», удалим содержащуюся в нем информацию, а затем добавим координаты двух оставшихся таблиц в «Список диапазонов.
8. Теперь остались только последние штрихи: поставьте галочки напротив необходимых опций:
   • «Заголовки верхней строки»: после консолидации в результирующую таблицу будет автоматически добавлен заголовок с учетом исходных данных.
   • «Значения левого столбца» — левый столбец должен быть заполнен соответствующими значениями.
   • «Создавать ссылки на исходные данные» — очень важный параметр, при его включении любые изменения исходных данных будут сразу отображаться в консолидированной таблице, которая также будет сформирована с группировкой, что может быть очень удобно. Но следует учитывать, что если в будущем потребуется изменить структуру одной из исходных таблиц, процедуру придется повторить. То же верно и для случаев, когда флажок не установлен.
   • Когда все будет готово, нажмите ОК.
     
9. Excel объединит данные и сформирует новую таблицу на основе указанных настроек и выбранных параметров.В нашем случае мы решили создать ссылку, поэтому мы получили группировку данных, которая позволяет отображать / скрывать детали.

Заключение

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