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

26640

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

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

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

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

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

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

  1. Жмем по значку создания нового листа, после чего программа автоматически добавит его справа от текущего и переключит нас на него. Подробная информация по данной операции представлена в нашей отдельной публикации – “Как добавить лист в Excel”.Добавление нового листа в ЭкселеПримечание: Можно переместить добавленный лист в удобное для нас место (например в конец списка) с помощью зажатой левой кнопки мыши, “зацепив” его за вкладку с названием. Также лист можно переименовать при желании (режим редактирования запускается двойным щелчком по имени, завершается нажатием Enter).
  2. В добавленном листе встаем в ячейку, с которой планируем вставить консолидированную таблицу (в нашем случае оставляем выбранную по умолчанию). Затем переходим во вкладку “Данные”, находим группу инструментов “Работа с данным”, где щелкаем по значку “Консолидация”.Запуск функции Консолидация в Экселе
  3. На экране отобразится небольшое окошко с настройками инструмента.Настройка параметров консолидации в Excel
  4. Здесь представлены следующие параметры:
    • Функция – кликнув по текущему варианту мы откроем список возможных действий, среди которых выбираем то, которое требуется выполнить для консолидируемых данных:
      • Сумма (выбрана по умолчанию; используется чаще всего, поэтому оставляем ее в качестве примера);
      • Количество;
      • Среднее;
      • Максимум;
      • Минимум;
      • Произведение;
      • Количество чисел;
      • Стандартное отклонение;
      • Несмещенное отклонение;
      • Дисперсия;
      • Несмещенная дисперсия.Выбор функции при консолидации данных в Экселе
  5. Переходим к полю “Ссылка”, щелкнув внутри него. Здесь мы поочередно выбираем диапазон ячеек исходных таблиц, которые должны быть обработаны. Для этого:
    • Переключаемся на лист с первой таблицей.
    • Видим, что в поле автоматически появилось название выбранного листа (но если смены листа не было, название добавлено не будет). Теперь с помощью зажатой левой кнопки мыши выделяем таблицу вместе с шапкой (например, от самой левой верхней ячейки до правой нижней). Убеждаемся, что координаты выбранных элементов корректно указаны в поле, после чего жмем кнопку “Добавить”. Кстати, указать/изменить координаты можно и вручную, введя их с помощью клавиатуры, но это не так удобно, как при работе с мышью, к тому же, в этом случае есть вероятность ошибиться.Указание диапазона ячеек в настройках консолидации данных в ЭкселеПримечание: чтобы выбрать диапазон из другого файла, предварительно открываем его в программе. Затем, запустив функцию консолидации в первой книге и находясь в поле “Ссылка”, переключаемся во вторую книгу, выбираем в ней нужный лист и выделяем требуемую область ячеек. При этом в начале ссылки обязательно должно добавиться имя файла. В нашем случае это не нужно, мы просто продемонстрировали, как это можно сделать.Указание диапазона ячеек из другой книги в настройках консолидации данных в Excel
  6. В результате в “Списке диапазонов” появилась первая запись, соответствующая выполненному нами выделению.Список диапазонов в настройках консолидации в Экселе
  7. Возвращаемся в поле “Ссылка”, удаляем содержащуюся в нем информацию, после чего добавляем в “Список диапазонов” координаты двух оставшихся таблиц.Список диапазонов в настройках консолидации в Excel
  8. Теперь остаются только заключительные штрихи – напротив нужных опций ставим галочки:
    • “Подписи верхней строки” – в результате консолидации в полученную таблицу автоматически будет добавлена шапка с учетом исходных данных.
    • “Значения левого столбца” – требуется, чтобы была заполнена левая колонка соответствующими значениями.
    • “Создавать связи с исходными данными” – очень важный параметр, включив который любые изменения первоначальных данных сразу же отобразятся в консолидированной таблице, которая, к тому же, будет сформирована с группировкой, что может быть очень удобно. Но стоит учитывать, что если в дальнейшем потребуется изменение структуры одной из исходных таблиц, процедуру придется выполнить повторно. Это же касается и случаев, когда галочка не установлена.
    • По готовности нажимаем OK.Дополнительные параметры консолидации в Экселе
  9. Эксель сделает консолидацию данных и сформирует новую таблицу согласно заданным настройкам и выбранным опциям.Консолидированная таблица в ЭкселеВ нашем случае – мы выбрали создание связи, поэтому получили группировку данных, которая позволяет отобразить/скрыть детализацию.Развернутая консолидированная таблица в Excel

Заключение

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

Подписаться
Уведомить о
guest
0 комментариев
Межтекстовые Отзывы
Посмотреть все комментарии