Консолидация по заголовкам более удобна в использовании, чем консолидация по физическому расположению. Дело в том, что в первом случае исходные ячейки идентифицируются не по номеру, а по заголовкам, поэтому в разных исходных листах они могут быть расположены по-разному, т. е. может использоваться разный порядок строк и столбцов. Некоторые исходные листы даже могут содержать столбцы или строки, которые отсутствуют в других листах.
При консолидации по заголовкам происходит следующее. Для каждой ячейки диапазона консолидации определяются названия строки и столбца, в которых она расположена. Далее в каждом исходном диапазоне ищется ячейка, находящаяся в строке и столбце с такими же названиями. Отобранные таким образом ячейки становятся исходными для данной ячейки. Если названия строк не определены, но определены названия столбцов, то сами столбцы консолидируются по имени, а ячейки в столбцах консолидируются по расположению.
Чтобы осуществить консолидацию по заголовкам:
1. Выделите верхнюю левую ячейку диапазона, в который должны быть помещены консолидированные данные. Можно выделить и весь диапазон.
2. Выполните команду Данные | Консолидация (Data Consolidate). Откроется диалоговое окно Консолидация (Consolidate), в котором задаются (рис. К1):
Функция, Ссылка, Список диапазонов, Подписи верхней строки, Значения левого столбца, Создавать связи с исходными данными
Рис.. К1
3. В списке Функция (Function) выберите функцию, с помощью которой будет производиться консолидация. Функции, включенные в этот список, приведены в табл.
4. Введите ссылку на первый исходный диапазон в поле Ссылка (Reference). Ее можно ввести вручную, но удобнее указать диапазон, нажав кнопку Свернуть диалоговое окно (Collapse dialog) и выделив нужный диапазон с помощью мыши.
5. Если исходные данные находятся в другой книге и она в данный момент закрыта, нажмите кнопку Обзор (Browse) и выберите книгу. В поле Ссылка (Reference) появится путь к выбранной книге. После этого добавьте к пути ссылку (это придется сделать вручную).
6. Выполните действия, описанные в шагах 4 и 5, для каждого исходного диапазона. Если ссылка была введена неверно, выделите ее в списке Все ссылки (All references) и нажмите кнопку Удалить (Delete).
7. После ввода всех ссылок нажмите кнопку ОК. В указанном месте появятся консолидированные данные.
Связи консолидированных данных и исходных данных уст. если они помещены на разных листах.
Таблица 29.1. Функции консолидации Excel
Функция
Описание
Сумма (Sum)
Вычисление суммы значений исходных ячеек
Количество значений (Count)
Вычисление количества непустых исходных ячеек
Среднее (Average)
Вычисление среднего значения исходных ячеек
Максимум (Мах)
Определение максимального среди значений исходных ячеек
Минимум (Min)
Определение минимального среди значений исходных ячеек
Произведение
(Product)
Вычисление произведения значений исходных ячеек
Количество чисел {Count Nums)
Вычисление количества исходных ячеек, содержащих числа
Смещенное отклонение
(StdDev)
Оценка стандартного отклонения генеральной совокупности в предположении, что в исходных ячейках находится только выборка из генеральной совокупности
Несмещенное отклонение
(StdDevp)
Вычисление стандартного отклонения генеральной совокупности, которая находится в исходных ячейках
Смещенная дисперсия (Var)
Оценка дисперсии генеральной совокупности по выборке
Несмещенная дисперсия (Varp)
Вычисление дисперсии генеральной совокупности
Исходные диапазоны могут быть разных размеров. В этом случае одни ячейки будут иметь больше исходных ячеек, чем другие. Например, если указаны два исходных диапазона B2:D4 и B6:Е8, то диапазон консолидации будет содержать три строки и четыре столбца. Значения первых трех ячеек каждой строки будут сформированы на основе данных из обоих диапазонов, значения последних ячеек каждой строки будут сформированы на основе ячеек столбца E второго диапазона.
