Работа с табличным процессором ms excel icon

Работа с табличным процессором ms excel



НазваниеРабота с табличным процессором ms excel
Дата конвертации14.09.2012
Размер117.63 Kb.
ТипПрограмма



Практикум по Excel. Занятие 13

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

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


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


Цель работы:

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



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


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












^ Турагентсво «ТурИн»










Объем реализации туров






















 

Филиал

Программа тура

Страна

Цена, $

Количество

Объем продаж

1

Филиал в Туле

Крит

Греция

200

8

 

2

Филиал в Орле

Крит

Греция

200

6

 

3

Филиал в Туле

Эдика

Греция

150

24

 

4

Филиал в Орле

Эдика

Греция

150

16

 

5

Филиал в Туле

Анталия

Турция

465

9

 

6

Филиал в Орле

Анталия

Турция

465

8

 

7

Филиал в Туле

Солоники-Афины

Греция

415

28

 

8

Филиал в Орле

Солоники-Афины

Греция

415

20

 

9

Филиал в Туле

Париж

Франция

405

11

 

10

Филиал в Орле

Париж

Франция

405

12

 

11

Филиал в Туле

Афины

Греция

228

14

 

12

Филиал в Орле

Афины

Греция

228

10

 

13

Филиал в Туле

Южный Крым

Украина

74

22

 

14

Филиал в Орле

Южный Крым

Украина

74

17

 

15

Филиал в Туле

Солоники

Греция

228

16

 

16

Филиал в Орле

Солоники

Греция

228

12

 

17

Филиал в Туле

Коктебель

Украина

116

8

 

18

Филиал в Орле

Коктебель

Украина

116

7

 


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



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

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

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



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

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

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

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


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

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


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

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


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

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

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

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


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

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

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

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

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

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


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

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

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


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

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




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


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

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

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

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

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

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

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

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

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

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

И
зучите кнопки панели инструментов «Сводные таблицы».

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

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

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

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

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

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

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

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


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

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

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




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


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

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

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

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

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

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

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











i Т.к. на рабочем листе содержатся формулы, для вставки следует использовать команду меню Правка - Специальная вставка

ii Если после копирования листа на новом листе не сохранилась структура, ее нужно создать заново командой меню Данные – Структура.

iii Неиспользуемые рабочие листы можно скрыть командой меню Формат – Лист – Скрыть.

www.alural.narod.ru/inform/intro.htm Александр Ю. Алексеев






Похожие:

Работа с табличным процессором ms excel iconРабота с табличным процессором ms excel
На рабочем столе создайте ярлык для запуска программы Excel (программа обычно расположена по адресу: "C:\Program Files\Microsoft...
Работа с табличным процессором ms excel iconРабота с табличным процессором ms excel
Откройте файл с ведомостью результатов тестирования, составленной на практическом занятии 10
Работа с табличным процессором ms excel iconРабота с табличным процессором ms excel
Получение практических навыков практической работы по созданию и редактированию электронных таблиц
Работа с табличным процессором ms excel iconРабота с табличным процессором ms excel
В состав Excel входит несколько шаблонов, возможно создание и сохранение вариантов шаблонов для себя и других пользователей. Шаблон...
Работа с табличным процессором ms excel iconРабота с табличным процессором ms excel
Скопируйте в свою папку файл с ведомостью результатов тестирования, составленной на практическом занятии 10
Работа с табличным процессором ms excel iconРабота с табличным процессором ms excel
Исходные данные для выполнения практической работы: созданный на практическом занятии №3 документ «Сортировка и выборка хls»
Работа с табличным процессором ms excel iconРабота с табличным процессором ms excel
Скопируйте в свою папку файл data-for-10. txt, из папки serv\\ \2 курс, откройте его в блокноте и изучите структуру данных, содержащихся...
Работа с табличным процессором ms excel iconРабота с табличным процессором ms excel
В документе Tabl xls сделайте активным лист Таблица. Выделите и очистите лист. Создайте приведенную на рис. 1 таблицу
Работа с табличным процессором ms excel iconРабота с табличным процессором ms excel. Практическое занятие 6
Длстр(Текст) возвращает количество символов аргумента, например: =длстр(C12) – вернет количество символов текста в ячейке С12
Работа с табличным процессором ms excel iconДокументы
1. /Excel/Основные приемы работы с ТП Excel.doc
2. /Excel/Планирование...

Разместите кнопку на своём сайте:
Документы


База данных защищена авторским правом ©podelise.ru 2000-2014
При копировании материала обязательно указание активной ссылки открытой для индексации.
обратиться к администрации
Документы

Разработка сайта — Веб студия Адаманов