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

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

Использование трехмерных формул

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

Используйте формулы для объединения данных. В приведенном ниже примере формула в ячейке A2 складывает три числа, расположенные в различных местах трех разных листов.

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

Консолидация по расположению

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

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

Консолидация по категории

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

Другие способы объединения данных

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

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

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

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

Формулы.  Трехмерные ссылки можно использовать в формулах для любого типа и расположения данных. Рекомендуется пользоваться этим способом.

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

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

Консолидируйте данные с использованием трехмерных ссылок или формул
1. На листе консолидации скопируйте или задайте надписи для данных консолидации.
2. Укажите ячейку, в которую следует поместить данные консолидации.
3. Введите формулу, включающую ссылки на исходные ячейки каждого листа, содержащего данные, для которых будет выполняться консолидация.
  
  Например, чтобы объединить данные в ячейке B3 листов с Лист2 по Лист7, введите =СУММ(Лист2:Лист7!B3). Если данные, которые требуется объединить, находятся в разных ячейках на разных листах, введите формулу в формате=СУММ(Лист3!B4; Лист4!A7; Лист5!C5). Чтобы ввести ссылку (например Лист3!B4), не используя клавиши на клавиатуре, введите формулу до того места, где требуется вставить ссылку, а затем укажите на листе нужную ячейку.
Консолидируйте данные по положению или категории
1. Настройте данные для консолидации.
  
  Инструкции
  - Убедитесь, что все диапазоны данных представлены в формате списка: первая строка каждого столбца содержит подпись, остальные строки — однотипные данные, пустые строки или столбцы в списке отсутствуют.
  - Разместите каждый диапазон на отдельном листе. Не помещайте диапазоны на лист, на котором должна выполняться консолидация.
  - Если консолидация выполняется по положению, убедитесь, что макеты всех диапазонов совпадают.
    
    Если консолидация выполняется по категории, убедитесь, что подписи столбцов или строк, которые требуется объединить, совпадают с учетом регистра букв.
  - Назначьте имя каждому диапазону: выделите диапазон, укажите в меню Вставка на пункт Имя, выберите команду Присвоить и введите имя для данного диапазона.
2. Щелкните левый верхний угол области, в которой требуется разместить консолидированные данные.
3. В меню Данные выберите команду Консолидация.
4. Выберите из раскрывающегося списка Функция весовую функцию, которую требуется использовать для консолидации данных.
5. Щелкните поле Ссылка, откройте лист, содержащий первый диапазон данных для консолидации, введите имя этого диапазона и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов.
6. Если таблицу консолидации требуется обновлять автоматически при каждом изменении данных в каком-либо исходном диапазоне, и позже точно не потребуется изменять или добавлять диапазоны исходных данных для консолидации, установите флажок Создавать связи с исходными данными.
7. Если консолидация выполняется по положению, оставьте все поля в группе Использовать в качестве имен пустыми. В Microsoft Excel подписи исходных строк и столбцов не копируются в консолидированные данные. Если требуется скопировать подписи в консолидированные данные, сделайте это вручную.
  
  Если консолидация выполняется по категории, в группе Использовать в качестве имен установите флажки, соответствующие расположению подписей в исходных диапазонах: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно. Все подписи, не совпадающие с подписями в других исходных областях, в консолидированных данных будут расположены в отдельных строках или столбцах.

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

Изменение итоговой таблицы консолидации данных

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

Щелкните верхнюю левую ячейку консолидированных данных.
В меню Данные выберите команду Консолидация.

Консолидацию изменить невозможно, если был установлен флажок Создавать связи с исходными данными. Если флажок установлен, нажмите кнопку Закрыть и создайте новую консолидацию.
Выполните одно или несколько следующих действий.

Добавьте диапазон данных в консолидируемые данные
В новом диапазоне данные должны иметь то же расположение, что и подписи столбцов, совпадающие с другими диапазонами консолидации.
1. Щелкните поле Ссылка.
2. Для консолидации щелкните вкладку листа первого диапазона.
3. Введите имя диапазона или выберите диапазон из списка.
4. Нажмите кнопку Добавить.
Измените размер или формы диапазона данных
1. В списке Список диапазонов щелкните диапазон данных, который требуется изменить.
2. Внесите изменения в выбранную область в поле Ссылка.
3. Нажмите кнопку Добавить.
Удалите диапазон данных и консолидации
1. В списке Список диапазонов щелкните диапазон данных, который требуется изменить.
2. Нажмите кнопку Удалить.
Автоматически обновите консолидацию

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

Установите флажок Создавать связи с исходными данными.
Чтобы сохранить изменения, нажмите кнопку OK.

Итоговые функции для анализа данных

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

Функция	Результат
Сумма	Сумма чисел. Эта операция используется по умолчанию для подведения итогов по числовым полям.
Количество	Количество данных. Эта операция используется по умолчанию для подведения итогов по нечисловым полям. Операция «Кол-во значений» работает так же, как и функция СЧЁТЗ.
Среднее	Среднее чисел.
Максимум	Максимальное число.
Минимум	Минимальное число.
Произведение	Произведение чисел.
Кол-во чисел	Количество данных, являющихся числами. Операция «Кол-во чисел» работает так же, как и функция СЧЁТ.
Несмещенное отклонение	Несмещенная оценка стандартного отклонения для генеральной совокупности, где выборка является подмножеством генеральной совокупности.
Смещенное отклонение	Смещенная оценка стандартного отклонения генеральной совокупности по выборке данных.
Несмещенная дисперсия	Несмещенная оценка дисперсии для генеральной совокупности, где выборка является подмножеством генеральной совокупности.
Смещенная дисперсия	Смещенная оценка дисперсии генеральной совокупности по выборке данных.