Автоматическое подведение итогов. Консолидация данных. Создание сводной таблицы.

Работа с табличным процессором MS Excel 2000.

Освоение приемов работы с электронными таблицами

Практическое занятие 8.

Автоматическое подведение итогов. Консолидация данных. Создание сводной таблицы.

Цель работы:

1. Освоение методов обобщения данных в Excel

1. Подготовка к работе

Откройте новую рабочую книгу, введите на рабочий лист таблицу реализации по двум филиалам туристического агентства «ТурИн». Присвойте рабочему листу имя Исходный.

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

2.Автоматическое подведение итогов
Задание. Вычислить суммарное количество туров, реализованных каждым филиалом, объем реализации для каждого филиала и подвести итоги по тур агентству в целом.
Указание. Используйте средство автоматического подведения итогов – команду Данные – Итоги.
Выделите итоговые данные полужирным курсивом и размером 12 пт.

3.Вычисления на основе итоговых данных
На основании таблицы с итоговыми данными можно выполнять дополнительные вычисления.
Задание. Определить эффективность работы каждого филиала его долей в общем объеме продаж.
Указания. Скопируйте рабочий лист Итоги на лист Вычисления. Сверните структуру до уровня промежуточных и общих итогов (т.е. виден заголовок и три строки итогов).
Добавьте столбец «Доля филиала » и рассчитайте процентную долю каждого филиала в общем объеме продаж. Откройте все уровни структуры и скопируйте формулу во все ячейки столбца «Доля филиала ».

Функция Автовычисления. Щелкните правой кнопкой мыши по полю для автовычислений в строке состояния, выберете из меню функцию Сумма. Выделите в столбе диапазон ячеек, относящихся к одному филиалу. Сравните результат в поле для автовычислений с итоговыми данными в таблице.

Предъявите результаты преподавателю. Сохраните работу как книгу «Итоги ».

4. Консолидация данных
Подготовка к работе. Создайте в рабочей книге Итоги три новых рабочих листа и назовите их ТурИн, ТурАут и Консолидация.
Скопируйте рабочий лист Исходный на лист ТурИн и выполните вычисления в столбце Объем продаж.
Скопируйте данные рабочего листа ТурИн на лист ТурАут, откорректируйте данные на рабочем листе ТурАут:
- измените название турагентства;
- т.к.. в агентстве ТурАут есть единственный филиал в Калуге, удалите в таблице один из филиалов, измените название оставшегося и некоторые данные в столбце Количество.

Задание. Вычислить суммарный объем продаж и количество туров, реализованных всеми подразделениями двух турагентств.

Порядок выполнения. Для решения используется консолидация данных по категориям.
На рабочем листе Консолидация укажите ячейку А1 – левый верхний угол области вставки консолидированных данных.
Введите команду Данные – Консолидация.
В диалоговом окне Консолидация из раскрывающегося списка Функция выберете функцию Сумма (см. рис. 13-1)..
Щелкните мышью в поле Ссылка, перейдите на рабочий лист ТурИн и укажите первый диапазон ячеек, данные из которого должны быть консолидированы. Обратите внимание: заголовки строк и столбцов должны быть включены в области источники. Щелкните по кнопке Добавить, чтобы включить выбранный диапазон в Список диапазонов. Повторите эти действия для рабочего листа ТурАут.
Установите переключатели:
- использовать в качестве имен: подписи верхней строки и левого столбца;
- создавать связи с исходными данными.
Установка переключателя «использовать в качестве имен: подписи левого столбца» позволит просуммировать значения в строках с одинаковыми метками – названиями стран, даже если они расположены в несмежных областях.
Диалоговое окно консолидации должно иметь вид, показанный на рис. 13-1.
Щелкните Ok, для консолидации данных.

Рисунок 13-1 Диалог "Консолидация"

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

5. Создание сводной таблицы
Сводные таблицы Excel служат средством обобщения и анализа больших объемов информации, находящейся в различных источниках.
Создайте новый рабочий лист с именем Сводная и скопируйте на него рабочий лист Итоги.
Удалите структуру и все строки с итоговыми данными.
Задание. Определить эффективность работы филиалов турагентства с помощью сводной таблицы.
Установите курсор внутри таблицы, введите команду Данные-Сводная таблица и используйте инструкции Мастера сводных таблиц и диаграмм.
На третьем шаге щелкните кнопку Макет перетащите мышью в область Строка кнопку Филиал, а в область Данные кнопку Количество и две кнопки Объем продаж (вторая потребуется для дополнительных вычислений). (см. рис. 13-2)
Для выполнения дополнительных вычислений сделайте двойной щелчок по кнопке Объем продаж-2, и выберете в списке Дополнительные вычисления строку Доля от суммы по столбцу.
Ok для возврата в третье окно Мастера.
Установить переключатель Новый лист и щелкните кнопку Готово.
Проанализируйте результаты. Изучите возможность детализации и обобщения данных с помощью списков в заголовке таблицы. Изучите назначение кнопок панели инструментов.
Перетащите кнопка «Страна» последовательно в область Строка, в область Данные и в область Поля страниц (над таблицей). Исследуйте появляющиеся возможности.
Предъявите результаты преподавателю

Рисунок 13-2 Макет сводной таблицы