Электронные таблицы обладают мощным средством анализа данных – построением сводных таблиц. Этот режим дает возможность объединения данных из нескольких диапазонов одной таблицы, нескольких листов рабочей книги или даже из нескольких рабочих книг.
Порядок выполнения задания
Задание 1. Осуществить ввод исходных данных по доходам и построение сводной таблицы для проведенияанализа доходов.
Задание 2. Провести модификацию сводной таблицы дляудобства проведения анализа.
Задание 3. Построить диаграмму по данным своднойтаблицы.
Выполнение задания 1
3.1.1. Запуск программы. Откройте новую рабочую книгу
3.1.2. Ввод исходных данных по доходам:
· активизируйте ячейку А2 и введите текст Парфюмерия. В ячейкуA3 введите текст Бытовые товары, в ячейкуА4введите текст Продовольствие. В ячейкуВ1 введите текст Альфа, в ячейкуС1 введите текст Бета, в ячейкуD1 введите текст Гамма, в ячейкуЕ1 введите текст Дельта (см. табл. 49);
· выделите ячейки с А1 по Е4 и выполните команду меню Формат – Автоформат. На экране появится диалоговое окно Автоформат;
· в поле Список форматов выберите Объемный 2 и нажмите кнопку ОК. В таблице установится выбранный формат;
· щелкните правой кнопкой мыши по ярлыку первого листа Лист1. Появится контекстное меню, в котором выберите команду Переместить/ Скопировать. Появится диалог Переместить или скопировать;
· установите флажок Создавать копию и нажмите кнопку ОК. В рабочей книге появится лист с названием Лист1(2);
Примечание: все тексты, форматы, размеры ячеек на нем точно такие же, как и на листе с названием Лист1.
Аналогичным способом создайте еще одну копию листа.
· щелкните правой кнопкой мыши по ярлычку Лист1(3). В появившемся диалоговом окне выберите команду Переименовать;
· в поле Имя листа введите новое имя Июнь и нажмите клавишу <Enter>. Название листа на ярлычке изменится. Аналогично поменяйте названия двух других листов на Июль и Август соответственно;
· введите данные в ячейки с В2 по Е4 из табл. 50 – 51, по доходам в тыс. руб. для четырех магазинов фирмы на конкретный рабочий лист (по месяцам). После ввода данных таблицы готовы для работы.
Таблица 49. Данные за июнь
| Данные за июнь | ||||
| Альфа | Бета | Гамма | Дельта | |
| Парфюмерия | ||||
| Бытовые товары | ||||
| Продовольствие |
Таблица 50. Данные за июль
| Данные за июль | ||||
| Альфа | Бета | Гамма | Дельта | |
| Парфюмерия | ||||
| Бытовые товары | ||||
| Продовольствие |
Таблица 51. Данные за август
| Данные за август | ||||
| Альфа | Бета | Гамма | Дельта | |
| Парфюмерия | ||||
| Бытовые товары | ||||
| Продовольствие |
Примечание: Обрабатывать таблицы в том виде, в котором они получены, довольно трудный процесс. Значительно облегчают эту работу сводные таблицы.
Работа со сводными таблицами в Excel
3.1.3. Построение сводной таблицы для проведения анализа доходов:
· выполните команду меню Данные – Сводная таблица. Мастер сводных таблиц начнет работать с первого шага;
· установите переключатель в положение В нескольких диапазонах консолидации, так как данные расположены на различных листах рабочей книги. Нажмите кнопку Далее;
· установите переключатель в положение Создать одно поле страницы, так как все листы идентичны и отличаются только одним параметром - месяцем получения дохода. Нажмите кнопку Далее;
· щелкните мышью в поле Диапазон, затем щелкните мышью по ярлычку листа Июнь и выделите ячейки с А1 по Е4. Если окно Мастер шаблонов закрывает нужные ячейки, переместите окно за заголовок мышью и сдвиньте его вниз;
· нажмите кнопку Добавить и диапазонИюнь!$А$1:$Е$4 будет добавлен в поле Список диапазонов;
· щелкните мышью по ярлычку листа Июль и нажмите кнопку Добавить;
· щелкните мышью по ярлычку листа Август и нажмите кнопку Добавить. В поле Диапазон добавятся адреса трех диапазонов. Нажмите кнопку Далее;
· в появившемся окне Мастер сводных таблиц (шаг 3) щелкните по кнопке Макет;
· кнопку Строка перетащите мышью в поле Страница, которое находится в левой части окна, а кнопку Страница из правого столбца в поле Строка в левой части окна и нажмите кнопку Ок;
· в появившемся диалоговом окне установите флажок Новый лист и нажмите кнопку Готово. Сводная таблица построится на новом листе;
· дважды щелкните по ярлычку этого листа и введите новое имя листа Сводная таблица.
Выполнение задания 2
3.2.1. Модификация сводной таблицы для удобства проведения анализа:
· щелкните дважды по ячейкеА1, откроется диалог Вычисление поля сводной таблицы. Вместо слова Строка введите в поле Имя слова Тип товара, нажмите кнопку ОК.
3.2.2. Аналогично в ячейкеВ3 поменяйте название Столбец на Магазин, в ячейкеА4 название Страница на Месяц.
3.2.3. Щелкните в ячейкеА5 и введите в строке формул название месяца Август. В ячейкуА6 введите название Июль, а в ячейкуА7 введите название Июнь. Названия месяцев даны правильно, но расположены в неверном порядке. Не применяя сортировку, изменим порядок следующим способом. В ячейкуА5введите – Июнь, в ячейкуА6– Июль, а в ячейку А7– Август. Обратите внимание, что все строки списка поменялись местами. Список отсортирован, сводная таблица готова.
3.2.4. После создания сводной таблицы значения доходов просуммированы по месяцам и по магазинам. Теперь можно приступать к анализу доходов, так как это стало значительно проще. Нажмите кнопку со стрелкой, расположенную в ячейкеВ1. Откроется список. Нажмите для примера на слово Парфюмерия. Все значения в таблице изменяются и выводится информация только по товару Парфюмерия.
3.2.5. Щелкните дважды мышью по ячейкеВ3 (по полю Магазин). Откроется диалог Вычисление поля сводной таблицы. В поле Скрыть элементы щелкните по названиям магазинов, которые в данный момент вас не интересуют, например Альфа и Гамма. Нажмите кнопку ОК. В сводной таблице останутся результаты только по магазинам, названия которых не убраны.
3.2.6. Восстановите первоначальный вид сводной таблицы, сняв отметки в поле Скрыть элементы.
Выполнение задания 3
3.3.1. Нажмите кнопку Мастер диаграмм на панели инструментов Стандартная.
3.3.2. В окне Мастера диаграмм щелкните мышью по типу круговой диаграммы.
3.3.3. В следующем окне Мастера диаграмм выберите второй вид диаграмм (объемная целая) и нажмите кнопку Далее.
3.3.4. В открывшемся окне в поле Диапазон введите, выделяя мышью, диапазоны ячеек $А$5:$А$7 и через точку с запятой $F$5:$F$7. Установите переключатель Ряды в в положение В столбцах и нажмите кнопку Далее.
3.3.5. В следующем диалоговом окне нажмите кнопку Готово. На листе появится готовая диаграмма. Установите указатель мыши в области диаграммы и нажмите левую кнопку мыши, вокруг диаграммы появятся восемь маркировочных квадратиков. Установите указатель мыши внутри области диаграммы, нажмите левую кнопку и перемещайте мышь, удерживая кнопку нажатой. Диаграмма изменит свое положение. Измените сводную таблицу, выведя информацию только по Бытовым товарам. Обратите внимание на изменения, происшедшие в диаграмме. Выведите в сводной таблице данные по всем типам товара.
3.3.6. Перейдите на лист Июнь. Измените число 1 200 на 12 000.Перейдите на лист Сводная таблица. Обратите внимание, что в сводной таблице изменений не произошло.
3.3.7. Выделите на сводной таблице ячейки с В5 по Е7 и щелкните правой кнопкой мыши по выделенным ячейкам. В появившемся контекстном меню выполните команду Обновить данные. Данные в ячейках сводной таблицы обновятся. Вид диаграммы также изменится.
Самостоятельная работа
3.4.1. Измените данные, введенные за август месяц, следующим образом: замените название магазина Дельта на Сигма, а данные по продажам: 285 на 200, 468 на 300, 138 на 400.
3.4.2. Внесите эти изменения в сводную таблицу.
3.4.3. Постройте диаграмму с объемами продаж по магазинам.
3.4.4. Продемонстрируйте результаты работы преподавателю.
3.4.5. Завершите работу табличного процессора.
