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

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

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

Читайте также: «Как сделать сводную таблицу в Excel”

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

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

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

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

1. Нажмите на иконку для создания нового листа, после чего программа автоматически добавит его справа от текущего и переключит нас на него. Подробная информация об этой операции представлена ​​в нашей отдельной публикации: «Как добавить лист в Excel”.

    

   

    

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

2. На добавленном листе располагаемся в ячейке, с которой планируем вставлять сводную таблицу (в нашем случае оставляем ее выбранной по умолчанию). Затем переходим во вкладку «Данные», находим группу инструментов «Работа с данными», где нажимаем на иконку «Консолидировать”.

    

   

    

3. На экране появится небольшое окно с настройками инструмента.

    

   

    

4. Вот следующие варианты:
   • Функция: нажав на текущую опцию, мы откроем список возможных действий, среди которых выберем то, которое следует выполнить для консолидированных данных:
     • Сумма (выбрана по умолчанию, используется чаще, поэтому оставим для примера);
     • Количество;
     • Средний;
     • Максимум;
     • Минимум;
     • Работа;
     • Количество номеров;
     • Среднеквадратичное отклонение;
     • Беспристрастное отклонение;
     • Дисперсия;
     • Беспристрастная дисперсия.

        

       

        

5. Перейдите в поле «Ссылка», нажав внутри него. Здесь мы альтернативно выбираем диапазон ячеек в исходных таблицах для обработки. За это:
   • Переключитесь на лист с первой таблицей.
   • Видим, что в поле автоматически появилось название выбранного листа (но если смены листа не было, имя не добавляется). Теперь с нажатой левой кнопкой мыши выделите таблицу вместе с заголовком (например, из верхней левой ячейки в нижнюю правую). Убеждаемся, что в поле правильно указаны координаты выбранных элементов, после чего нажимаем кнопку «Добавить». Кстати, можно указать/изменить координаты и вручную, введя их с клавиатуры, но это не так удобно, как при работе с мышью; кроме того, в этом случае есть возможность совершить ошибку.

      

     

      

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

      

     

      

6. В результате первая запись, соответствующая сделанному нами отбору, появилась в «Rank List.

    

   

    

7. Возвращаемся к полю «Справочник», удаляем содержащуюся в нем информацию, а затем добавляем координаты оставшихся двух таблиц в «Список диапазонов.

    

   

    

8. Теперь остались только последние штрихи: поставьте галочки напротив нужных опций:
   • «Заголовки верхней строки» — в результате объединения в результирующую таблицу будет автоматически добавлен заголовок с учетом исходных данных.
   • «Значения левого столбца»: левый столбец должен быть заполнен соответствующими значениями.
   • «Создавать связи с исходными данными» — очень важная опция, при ее включении любое изменение исходных данных будет отображаться сразу в сводной таблице, которая также будет формироваться с группировкой, что может быть очень удобно. Но имейте в виду, что если в дальнейшем потребуется изменить структуру одной из исходных таблиц, процедуру придется повторить. То же самое относится и к случаям, когда флажок не установлен.
   • Когда будете готовы, нажмите OK.

      

     

      

9. Excel объединит данные и создаст новую таблицу в соответствии с указанными настройками и выбранными параметрами.

    

   

    

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

    

   

    

Заключение

Читайте также: «Как сгруппировать данные в таблице Excel: строки, столбцы”

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