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

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

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

Цель работы:

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

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

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

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

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

Указание. Используйте средство автоматического подведения итогов – команду Данные – Итоги.

Выделите итоговые данные полужирным курсивом и размером 12 пт.

3.                       Вычисления на основе итоговых данных
На основании таблицы с итоговыми данными можно выполнять дополнительные вычисления.

Задание. Определить эффективность работы каждого филиала его долей в общем объеме продаж.

Указания. Скопируйте рабочий лист[i] Итоги на лист Вычисления. Сверните[ii] структуру до уровня промежуточных и общих итогов  (т.е. виден заголовок и три строки итогов).

Добавьте столбец «Доля филиала» и рассчитайте  процентную долю каждого филиала в общем объеме продаж. Откройте все уровни структуры и скопируйте формулу во все ячейки столбца «Доля филиала».

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

Сохраните работу как книгу «Итоги».

4.                        Консолидация данных

Подготовка к работе. Создайте в рабочей книге  Итоги три новых рабочих листа и назовите их ТурИн, ТурАут и Консолидация[iii].

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

Скопируйте данные рабочего листа ТурИн на лист ТурАут, откорректируйте данные на рабочем листе ТурАут следующим образом:
- измените название турагентства;

- т.к.. в агентстве ТурАут имеется единственный филиал в Калуге, удалите в таблице один из филиалов, измените название оставшегося  и некоторые  -любые, во вашему выбору - данные в столбце Количество.

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

Порядок выполнения. Для решения используется консолидация данных по категориям.
На рабочем листе Консолидация укажите ячейкуА1 – левый верхний угол области вставки консолидированных данных.

Введите команду  Данные – Консолидация.

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

Щелкните мышью в поле Ссылка, перейдите на рабочий лист ТурИн и  укажите первый диапазон ячеек, данные из которого должны быть консолидированы. Обратите внимание: заголовки строк и столбцов должны быть включены в области источники. Щелкните по кнопке Добавить, чтобы включить выбранный диапазон в Список диапазонов. Повторите эти действия для рабочего листа ТурАут.

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

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

Установка переключателя «использовать в качестве имен: значения левого столбца» позволит просуммировать значения в строках с одинаковыми метками – названиями филиалов, даже если они расположены в несмежных областях.

Диалоговое окно консолидации после ввода двух диапазонов должно иметь вид, показанный на рис. 13-1.

Щелкните Ok,для консолидации данных.

Рисунок 13-1 Диалог "Консолидация". Выбраны два диапазона данных

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

5.                        Создание сводной таблицы

Сводные таблицы Excel служат средством обобщения и анализа больших объемов информации, находящейся в различных источниках.

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

Удалите структуру и все  строки с итоговыми данными.

Задание. Определить эффективность работы филиалов турагентства  с помощью сводной таблицы.

Установите курсор внутри таблицы, введите команду  Данные-Сводная таблица и используйте инструкции Мастера сводных таблиц и диаграмм.

На третьем шаге щелкните кнопку Макет и перетащите мышью в область Строка кнопку Филиал, а в область Данные кнопку Количество и две кнопки Объем продаж (вторая потребуется для дополнительных вычислений). (см. рис. 13-2)

Для выполнения дополнительных вычислений сделайте двойной щелчок по кнопке Объем продаж-2, и выберете в списке Дополнительные вычисления строку  Доля от суммы по столбцу.

Нажмите Ok для возврата в третье окно Мастера.

Установить переключатель Новый лист и щелкните кнопку Готово.

Проанализируйте результаты.

Рассмотрите результаты при отображении и скрытии деталей.

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

Перетащите кнопку «Страна» последовательно в область Строка, Данные, Страницы. Изучите появляющиеся при этом дополнительные возможности анализа данных.

Постройте диаграмму объема реализации по филиалам, используя Мастер диаграмм панели Структура.

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

Перетащите кнопку Страны в поле над легендой диаграммы.
Перетащите туда же поле Программа тура.

В списке Страны над легендой диаграммы удалите флажки у двух любых стран.

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

6.                        Защита рабочих листов и ячеек в Excel

Проверьте, как установлена защита «по умолчанию»:
в меню Формат выберете команду Ячейки и в окне диалога Формат ячеек на вкладке Защита  проверьте состояние  флажка Защищаемая ячейка. 

По умолчанию Excel блокирует от несанкционированного изменения или доступа ячейки рабочего листа, но  эта защита  вступает в силу только, когда  включена защита рабочего листа. Чтобы включить защиту рабочего листа, нужно выполнить команду Сервис – Защита – Защитить – Лист.

Рисунок 13-2 Диалог "Защита листа" в Excel-2000 и в Excel-2003

Ячейки защищены от изменения, если установлен флаг «Защитить листы в отношении содержимого» (для Excel-2000). Для Excel-2003 защита может устанавливаться раздельно для различных элементов рабочих ячеек (см. рисунок). Следует очень ответственно отнестись к назначению пароля защиты. После назначения пароля нет способа снятия пароля с листа или книги без ввода этого пароля. Пароль необходимо помнить с точностью до регистра ввода.

Обычно нет необходимости блокировать все ячейки рабочего листа. Прежде чем защищать лист, необходимо выделите ячейки которые должны остаться незаблокированными. Для этого командой меню Формат - Ячейки  вызывается  диалог Формат ячеек,  и на вкладке Защита  снимается флажок Защищаемая ячейка.

Только после этого выполняется защиту рабочего листа.

На рабочем листе «Исходный» защите от изменений ячейки, в которых задана цена тура в долларах.

7.     Интерфейс программы Excel - 2007.

7.1.             В программе Excel – 2007 для консолидации используется пиктограмма , расположенная в группе  «Работа с данными» вкладки «Данные».

7.2.             Защита ячеек в программе Excel – 2007 включается, так же как и в предыдущих версиях, после включения защиты рабочего листа. Диалог «Защита листа» вызывается пиктограммой из группы «Изменения» на вкладке «Рецензирование».

Скачано с www.znanio.ru