Основные теоретические положения. Электронные таблицы обладают мощным средством анализа данных – построением сводных таблиц

Электронные таблицы обладают мощным средством анализа данных – построением сводных таблиц. Этот режим дает возможность объединения данных из нескольких диапазонов одной таблицы, нескольких листов рабочей книги или даже из нескольких рабочих книг.

Порядок выполнения задания

Задание 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. Завершите работу табличного процессора.