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

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

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

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

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

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

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

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

    

   

    

   Примечание: Добавленный рабочий лист можно переместить в удобное место (например, в конец списка), удерживая нажатой левую кнопку мыши и "прицепив" его к вкладке с именем. При желании можно также изменить название листа (режим редактирования запускается двойным щелчком по названию и завершается нажатием Enter).
2. В добавленном листе встаньте в ячейку, из которой вы планируете вставить сводную таблицу (в нашем случае мы оставим выбранную по умолчанию). Затем перейдите на вкладку "Данные", найдите группу инструментов "Работа с данными" и нажмите на значок "Консолидировать".

    

   

    

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

    

   

    

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

        

       

        

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

      

     

      

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

      

     

      

6. В результате в "Списке диапазонов" появится первая запись, соответствующая нашему выбору.

    

   

    

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

    

   

    

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

      

     

      

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

    

   

    

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

    

   

    

Заключение

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