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 в листе Усредненный теперь содержит средние значения для соответствующих ячеек в четырех исходных листах.