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

Создайте в рабочей книге Итоги.xls три новых рабочих листа и присвойте им имена Юпитер, Сатурн и Консолидация.

Скопируйте рабочий лист Исходный на лист Юпитер, введите в ячейку G5 нужную формулу и выполните вычисления в столбце Объем продаж.

Скопируйте данные рабочего листа Юпитер на лист Сатурн. Откорректируйте данные на рабочем листе Сатурн:

• измените название фирмы;

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

Требуется вычислить суммарный объем продаж и количество программ от различных фирм-производителей, реализованных всеми продавцами обеих фирм ("Юпитер" и "Сатурн").

Для решения этой задачи используйте консолидацию данных по категориям. Таблицу с итоговыми (консолидированными) данными разместите на рабочем листе Консолидация.

Для консолидации данных, находящихся на рабочих листах Юпитер и Сатурн, выполните следующую последовательность действий:

1. Сделайте активным рабочий лист Консолидация и укажите ячейку A1 - левый верхний угол области вставки консолидированных данных.

2. Введите команду меню ДАННЫЕ - Консолидация.

3. В диалоговом окне Консолидация из раскрывающегося списка Функция выберите функцию Сумма.

4. Щелкните мышью в поле Ссылка, перейдите на рабочий лист Юпитер и укажите диапазон ячеек D4:G22 - первый диапазон, данные из которого должны быть консолидированы. При выделении диапазонов заголовки столбцов и строк (метки) должны быть включены в области-источники.

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

5. Щелкните по кнопке Добавить для включения выбранного диапазона в поле Список диапазонов.

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

7. Щелкните по кнопке Добавить.

8. Установите переключатели:

• Использовать в качестве имен: подписи верхней строки и значения левого столбца;

• Создавать связи с исходными данными.

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

5. Щелкните по кнопке ОК для выполнения консолидации данных из областей, указанных в поле Список диапазонов. Откорректируйте в таблице с консолидированными данными ширину столбцов так, чтобы был виден текст всех заголовков столбцов.

Удалите столбец с меткой Цена, $.

Обратите внимание на структуру таблицы, появившуюся на листе Консолидация. Откройте все уровни структуры. Сверните структуру документа щелчком по номеру уровня 1.

Используя кнопку Автосумма, вычислите общий объем продаж и количество программ, реализованных на обеих фирмах - "Юпитер" и "Сатурн".

Проанализируйте полученные результаты. Сохраните работу.

Отформатируйте таблицу с консолидированными данными рамками и заливкой, выделите метки столбцов и строк полужирным шрифтом.

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

Постройте диаграмму на основе таблицы с консолидированными данными.

Откройте в таблице одну из групп детальных данных и обратите внимание на изменение диаграммы.

Сохраните работу.