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

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

Рисунок 3.41

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

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

3. На вкладке Данные в группе Работа с данными выберите команду Консолидация.

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

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

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

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

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

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

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

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

Рисунок 3.42

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

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

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

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

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

Рисунок 3.43

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