Консолидация и фильтрация данных

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

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

В качестве примера консолидации рассмотрим обработку данных об объемах продаж некоторой фирмы за первый квартал текущего года (рис. 11.1).

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

Существует несколько способов консолидации.

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

Общая схема

1. Выделить область для размещения результата операции консолидации (на том же или новом листе).

2. Выполнить операцию Данные – Консолидация.

3. На экране появится окно Консолидация (рис. 11.2).

Рис. 11.2. Окно "Консолидация"

4. Выполнить в этом окне следующие действия:

а) в поле 1 указать операцию вычисления итогов;

б) в поле 2 ввести область для консолидации (заметим, что данную область можно также выделить мышью);

в) щелкнуть по кнопке Добавить; в поле 3 появится адрес выделенного диапазона;

г) повторить шаги (б) и (в) для всех консолидируемых областей;

д) в поле 4 указать, что использовать в качестве имен (подписи верхней строки или значения левого столбца);

е) в поле 5 отметить флажок Создавать или не создавать связи между данными;

ж) щелкнуть мышью по кнопке ОК.

Опишем процесс консолидации данных приведенного примера пошагово.

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

2. Выполните команду Данные – Консолидация.На экране отобразится диалоговое окно Консолидация(рис. 11.2).

3. В списке Функцияукажите тип консолидации. Допустимы типы: Сумма, Кол -во значений, Среднее, Максимум, Минимум, Произведение, Кол-во чисел, Смещенное отклонение, Несмещенное отклонение, Смещенная дисперсия, Несмещенная дисперсия.В данном случае выберите значение Сумма.

4. В поле Ссылкаукажите исходный диапазон данных, который должен быть консолидирован. В данном случае январь!$В$2:$В$7.Нажмите кнопку Добавить.Врезультате диапазон будет добавлен в список Список диапазонов.

5. Повторите пп. 3, 4 для других консолидируемых диапазонов. В данном случае для диапазонов Февраль!$В$2: $В$7 и Март!$В$2: $В$7.

6. Укажите, что должен содержать диапазон назначения: фиксированные значения, которые в дальнейшем не будут изменяться при изменениях в исходных данных, или связанные величины, обновляющиеся при изменениях в исходных данных. Если фиксированные значения, то снимите флажок Создавать связи с исходными данными.Вданном случае этот флажок надо установить.

7. Нажмите кнопку ОК.

Способ “По категориям” рекомендуется использовать для неупорядоченных данных, имеющих разную структуру, но одинаковые заголовки.

Общая схема

1. Выделить верхнюю левую ячейку области консолидируемых данных.

2. Выполнить команду Данные – Консолидация.

3. Выбрать в поле Функция необходимую функцию.

4. Ввести адрес области консолидируемых данных в поле Ссылка (вручную или выделить мышью – см. описание способа “По расположению”). Данная область должна иметь заголовок!

5. Щелкнуть мышью по кнопке Добавить.

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

Способ “С помощью сводной таблицы” позволяет использовать Мастер сводных таблиц (команда Данные – Сводная таблица).

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

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

- легко преобразуются;

- разрешают выполнять автоматический отбор информации;

- на основе сводных таблиц строятся диаграммы, которые динамически перестраиваются вместе с изменением сводной таблицы.

Пошаговое описание создания сводной таблицы

Опишем пошаговый процесс создания сводной таблицы на примере таблицы Продавцы,имеющей структуру, приведенную на рис. 11.3.

Рис. 11.3. Структура таблицы Продавцы

1. Установите курсор в любую ячейку таблицы и выполните команду Данные – Сводная таблица.На экране появится первое окно Мастера сводных таблиц (рис. 11.4).

Рис. 11.4. Первое окно Мастера сводных таблиц

2. На шаге 1 мастера убедитесь, что в качестве ответа на первый вопрос выбран параметр в списке или базе данных Microsoft Office Excel.

3. Убедитесь, что в качестве ответа на следующий вопрос выбран вариант сводная таблица.

4. Нажмите кнопку Готово.

Процедура завершена. При нажатии кнопки Готово Мастер организует область нового отчета сводной таблицы на основе стандартных настроек.

На новом листе книге Excel появятся три элемента: Список полей сводной таблицы, макет отчета, содержащий выделенные контуром области, на которые нужно перетаскивать поля, а также панель инструментов Сводные таблицы(рис. 11.5).

Рис. 11.5. Макет сводной таблицы Мастера

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

1. Из окна Список полей сводной таблицыперетащите поле Продавец в область Перетащите сюда поля строк(рис. 11.6). Для перетаскивания можно щелкнуть на имени поля или находящейся рядом с ним кнопку. После перетаскивания в область макета имена полей остаются в списке, но выделяются жирным шрифтом.

2. Из окна Список полей сводной таблицы перетащите поле Сумма заказа в область Перетащите сюда элементы данных.

Рис. 11.6. Содержимое окна Список полей сводной таблицы

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

Вот как должны выглядеть первые строки отчета сводной таблицы:

Фильтрация данных

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

Автофильтр. Позволяет выбирать отдельные записи непосредственно в рабочем листе.

Общая схема автофильтрации

1. Выделить строку “шапки” таблицы (или одну из ячеек первой строки) и выполнить команду Данные – Фильтр – Автофильтрация.

2. Первая строка таблицы примет вид строки с раскрывающимися списками (рис. 11.7).

Рис. 11.7. Окно Excel-таблицы после выполнения команды