11. Консолидация и фильтрация данных
11. 1. Консолидация данных
Консолидация (то есть объединение) позволяет подводить итоги по данным, расположенным в различных областях таблицы, например, несмежных, находящихся на разных листах таблицы и т.п. Например, можно обрабатывать сведения, поступающие из различных отделов компании, и, таким образом, получить общую картину. Однако, консолидация – это не только суммирование. В ходе этого процесса можно вычислить такие статистические величины, как среднее, стандартное отклонение, количество значений. Совместно с консолидацией полезно использовать структурирование, причем структура может создаваться автоматически. Предназначенные для консолидации рабочие листы совсем не обязаны иметь одну и ту же структуру.
В
качестве примера консолидации рассмотрим
обработку данных об объемах продаж
некоторой фирмы за первый квартал
текущего года (рис. 11.1).
Рис. 11.1. Консолидация данных
Существует несколько способов консолидации.
Способ “По расположению” предполагает, что данные объединяемых областей находятся в одном и том же месте на рабочих листах и размещены в одном и том же порядке. Его рекомендуется использовать для консолидации данных нескольких листов, созданных на основе одного шаблона.
Общая схема
1. Выделить область для размещения результата операции консолидации (на том же или новом листе).
2. Выполнить операцию Данные – Консолидация.
3. На экране появится окно Консолидация (рис. 11.2).
Рис. 11.2. Окно "Консолидация"
4. Выполнить в этом окне следующие действия:
а) в поле 1 указать операцию вычисления итогов;
б) в поле 2 ввести область для консолидации (заметим, что данную область можно также выделить мышью);
в) щелкнуть по кнопке Добавить; в поле 3 появится адрес выделенного диапазона;
г) повторить шаги (б) и (в) для всех консолидируемых областей;
д) в поле 4 указать, что использовать в качестве имен (подписи верхней строки или значения левого столбца);
е) в поле 5 отметить флажок Создавать или не создавать связи между данными;
ж) щелкнуть мышью по кнопке ОК.
Опишем процесс консолидации данных приведенного примера пошагово.
1. Укажите диапазон назначения, где должны располагаться консолидируемые данные. Можно указывать не весь диапазон, а только его левую верхнюю ячейку. В нашем случае выделите ячейку А1 рабочего листа Консолидация.
Выполните команду Данные – Консолидация. На экране отобразится диалоговое окно Консолидация (рис. 11.2).
В списке Функция укажите тип консолидации. Допустимы типы: Сумма, Кол -во значений, Среднее, Максимум, Минимум, Произведение, Кол-во чисел, Смещенное отклонение, Несмещенное отклонение, Смещенная дисперсия, Несмещенная дисперсия. В данном случае выберите значение Сумма.
В поле Ссылка укажите исходный диапазон данных, который должен быть консолидирован. В данном случае январь!$В$2:$В$7. Нажмите кнопку Добавить. В результате диапазон будет добавлен в список Список диапазонов.
Повторите пп. 3, 4 для других консолидируемых диапазонов. В данном случае для диапазонов Февраль!$В$2: $В$7 и Март!$В$2: $В$7.
Укажите, что должен содержать диапазон назначения: фиксированные значения, которые в дальнейшем не будут изменяться при изменениях в исходных данных, или связанные величины, обновляющиеся при изменениях в исходных данных. Если фиксированные значения, то снимите флажок Создавать связи с исходными данными. В данном случае этот флажок надо установить.
Нажмите кнопку ОК.
Способ “По категориям” рекомендуется использовать для неупорядоченных данных, имеющих разную структуру, но одинаковые заголовки.