Методика выполнения работы

Лабораторная работа №9. Консолидация данных

Цель работы: изучить способы группировки данных и подсчета итоговых значений.

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

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

На рис. 5.93 представлен простой пример книги, содержащей итоговый лист Усредненный, который устроен так же, как четыре исходных листа. Эти листы – 2002 год, 2003 год, 2004 год и 2005 год – могут быть консолидированы по расположению, так как все они имеют идентичную структуру данных, размещенных в пяти столбцах и пяти строках.

1. Дайте имена рабочим листам: Усредненный, 2002 год, 2003 год, 2004 год, 2005 год. Отформатируйте и внесите данные, как показано на рисунке 5.93.

2. Мы воспользуемся командой Консолидация из меню Данные для консолидации данных из листов 2002 год, 2003 год, 2004 год и 2005 год в листе Усредненный.

3. Активизируйте итоговый лист и выделите конечную область, то есть блок ячеек, в который будут помещены консолидированные данные. На рис. 2 конечная область – это диапазон B3:Е6 в листе Усреднение.

Рисунок 5.93 – Заготовка для консолидации

4. На ленте Данные щелкните кнопку Консолидация.

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

6. Введите ссылку для каждого исходного диапазона в поле Ссылка или выделите эти диапазоны с помощью мыши.

Рисунок 5.94 – Заполнение окна консолидации

Конечно, использование мыши является наиболее простым способом ввода ссылок, но если необходимо сослаться на закрытые в данный момент исходные листы, придется ввести эти ссылки с клавиатуры. (Можно использовать кнопку Обзор, чтобы определить местонахождение файла, а затем вручную ввести ссылку на ячейку.)

Вводимая ссылка должна иметь следующую форму:

[ИмяФайла]ИмяЛиста!Ссылка

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

7. Нажмите кнопку Добавить в окне диалога Консолидация. Excel перенесет ссылку из поля Ссылка в поле Список диапазонов.

Команда Консолидация использует ссылки из списка Список диапазонов для создания консолидированных средних значений.

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

8. Нажмите кнопку ОК. Excel усреднит исходные значения и поместит их в итоговый лист, как показано на рис. 5.95.

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

9. В столбец F добавьте формулы для подсчета среднего по магазинам с использованием функции СРЗНАЧ, как показано на рис.5.95.

Рисунок 5.95 – Итоговая таблица консолидации

Диапазон B3:Е6 в листе Усредненный теперь содержит средние значения для соответствующих ячеек в четырех исходных листах.

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

Теперь рассмотрим более сложный пример. На этот раз исходные листы будут содержать различное количество строк, но одинаковое количество столбцов (рис. 5.96).

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

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

Дата добавления: 2017-02-01