Построение линейчатой диаграммы с накоплением

18. Создайте новый рабочий лист.

19. Переименуйте вставленный Лист4 в «Линейчатая_диаг-рамма».

20. Постройте сравнительную диаграмму распределения мужчин и женщин по возрастным группам в соответствии с прилагаемой таблицей.

Для этого выполните следующие действия:

• задайте диапазон ячеек для построения диаграммы (A1:C8);

• перейдите на вкладку Вставка и в группе Диаграммы выберите тип диаграммы – линейчатая с накоплением;

• добавьте горизонтальные линии сетки, щелкнув по кнопке Элементы диаграммы и выполнив команду Сетка/Основные горизонтальные по основной оси;

• для размещения меток делений с левой стороны из контекстного меню вертикальной оси выберите Формат оси и в поле Подписи установите Положение метки «внизу» вместо «рядом с осью»;

• для перевода отрицательных процентов в положительные на оси значений создайте и примените специальный пользовательский формат: 0%;0%;0%. Для этого вызовите контекстное меню горизонтальной оси, выберите Формат оси, в диалоговом окне Формат оси в поле Число введите нужный Код формата, нажмите кнопки Добавить и Закрыть.

Построение пузырьковой диаграммы

21. На новом листе Пузырьковая_диаграмма самостоятельно постройте пузырьковую диаграмму, характеризующую зависимость суммы продаж товара от затрат на его рекламу в течение 12 месяцев по данным приведенной таблицы.

Примечание. Пузырьковые диаграммы - это специфический тип диаграмм, способных отображать трехмерные данные в двумерном пространстве. Они позволяют сравнивать наборы из трех, а не двух значений. Третье значение определяет размер пузырьков (например, сумму продаж).

Построение лепестковой диаграммы

22. На новом листе «Лепестковая_диаграмма» самостоятельно постройте заполненную лепестковую диаграмму, характеризующую сезонность продаж туристских путевок в течение года по данным приведенной таблицы.

Построение диаграммы Ганта

23. На новом листе «График_Ганта» постройте простую диаграмму Ганта, отображающую во времени начала работ проекта и их продолжительность. Исходные данные содержатся в приведенной ниже таблице.

Для построения диаграммы выполните следующие действия:

• выделите диапазон ячеек А1:В7 и вставьте Линейчатую диаграмму с накоплением;

• добавьте на диаграмму данные о продолжительности работ. Для этого из контекстного меню области диаграммы выберите Выбрать данные, в окне Выбор источника данных нажмите кнопку Добавить. В диалоговом окне Изменение ряда в поле Имя ряда щелчком мыши введите ссылку на ячейку С1 с именем «Продолжительность в днях», а в поле Значения введите мышью ссылку на диапазон ячеек С2:С7 с данными о продолжительности работ;

• два раза нажмите ОК;

• выделите ряд «Начало работы», вызовите контекстное меню и выберите команду Формат ряда данных. В появившемся окне нажмите на кнопку Заливка и границы , выберите Нет заливки. Нажмите кнопку Закрыть;

• вызовите контекстное меню вертикальной оси (категорий), и выберите команду Формат оси;

• в окне Формат оси в группе Пересечение с горизонтальной осью установите флажок в точке с максимальным значением категории, а в группе Положение оси – обратный порядок категорий. Нажмите кнопку Закрыть;

• вызовите контекстное меню горизонтальной оси (значений), наведя перед этим стрелку курсора на одну из дат. В диалоговом окне Формат оси в группе Параметры оси измените минимальное значение границы, введя дату 01.02.15. Цену основных делений введите 10, а цену промежуточных делений – 2;

• нажмите кнопку Закрыть;

• введите название диаграммы «График Ганта»;

• отредактируйте размеры шрифтов отдельных элементов и размеры области диаграммы.

Построение линий тренда

Если имеются существующие данные, для которых следует спрогнозировать ожидаемый спрос на продукты или услуги или оценить затраты следующего года, можно создать на диаграмме линию тренда (графическое представление направления изменения ряда данных), которая представит общие тенденции (рост, снижение или стабилизацию), т.е. продемонстрирует предполагаемую тенденцию на ближайший период. Периодом считается временной промежуток (день, месяц, год и т.д.), через который представлены имеющиеся данные, предшествующие прогнозу.

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

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

При добавлении линии тренда на диаграмму MS Excel можно выбрать любой из следующих шести различных типов тренда или регрессии: прямые, логарифмические, полиномиальные, степенные и экспоненциальные линии тренда, а также линии тренда с линейной фильтрацией. Тип линии тренда, который следует выбирать, определяется типом имеющихся данных.

Для оценки близости значений линии тренда к фактическим данным принято использовать коэффициент корреляции (коэффициент достоверности аппроксимации, коэффициент детерминации) R². Этот коэффициент изменяется в пределах от 0 до 1. Чем ближе к 1 значение R², тем лучше качество подгонки. При аппроксимации данных с помощью линии тренда значение R² рассчитывается приложением Excel автоматически. При необходимости полученный результат можно показать на диаграмме.

24. Вставьте новый лист Рабочей книги Excel и переименуйте его в «Линия_тренда».

25. Введите приведенную ниже таблицу.

26. Для этой таблицы сначала постройте диаграмму График, предварительно выделив диапазон ячеек А2:В8.

27. Для этого графика постройте линию тренда. Для этого:

• выделите график и из контекстного меню выберите команду Добавить линию тренда;

• в окне Формат линии тренда в группе Параметры линии тренда выберите вид линии тренда: полиномиаль­ная 4-й степени;

• в этом же окне установите: прогноз вперед на 1 период и поместить на диаграмму величину достоверности аппроксимации (R^2);

• нажмите кнопку Закрыть;

• покажите на диаграмме основные горизонтальные и вертикальные линии сетки;

• с помощью контекстного меню Формат линии тренда установите Цвет линии – красный, Тип штриха – сплошная линия;

• введите название диаграммы «Динамика спроса»;

• заливка области построения и цвет линий сетки – произвольные;

28. Сохраните результаты лабораторной работы в файле с именем lab2.xlsx.

Спарклайны

Тенденции в рядах значений (например, колебания цен, объемов продаж) можно отслеживать в Excel с помощью спарклайнов. Спарклайны располагаются внутри ячеек и представляют собой миниграфики, наглядно отображающие поведение данных, максимальные/минимальные значения и другие особые точки.

Преимуществом спарклайна по сравнению со стандартной диаграммой является скорость его создания.

Всего доступно три типа спарклайнов:

График – схож с диаграммой в виде графика. Для данного типа спарклайнов возможен вариант отображения маркера для каждой точки данных.

Гистограмма – как и обычная гистограмма.

Выигрыш/проигрыш – делит все данные на два типа: отрицательные и положительные и отображает их в виде верхнего и нижнего блока.

29. Вставьте новый лист Рабочей книги Excel и переименуйте его в «Спарклайны».

30. Введите приведенную ниже таблицу.

31. Выделите данные (ячейки В2:G9) и вызовите окно спарклайнов: Вставка/Спарклайны/График. В появившемся диалоговом окне укажите ячейки, куда требуется вставить спарклайны (Н2:Н9).

32. Отредактируйте спарклайны. Для этого щелкните по любой ячейке со спарклайном, на появившейся вкладке Работа со спарклайном в группе Показать поставьте галочки у флажков Максимальная, Минимальная, Первая и Последняя точка, настройте их цвет в группе Стиль и добавьте ось в группе Группировать.

33. На вкладке Работа со спарклайнами в группе Тип измените последовательно тип спарклайнов на Гистограмму и Выигрыш/Проигрыш.

34. Сохраните результаты лабораторной работы.

ЛАБОРАТОРНАЯ РАБОТА № 3

Структурирование, консолидация данных,

построение сводных таблиц и диаграмм

Цель лабораторной работы

Лабораторная работа помогает получить практические навыки по изучению следующих тем:

• Управление данными, расположенными на разных листах рабочей книги;

• Работа со списками и операции над ними (фильтрация, сортировка);

• Использование диалоговых окон для изменения информации в списках;

• Работа со структурой таблицы (создание и удаление);

• Формирование таблиц с общими и частными итогами;

• Консолидация данных, расположенных на разных листах рабочей книги;

• Построение сводных таблиц и сводных диаграмм.

Основные сведения о списках, структуре рабочего

листа, консолидации и сводных таблицах

Список – это упорядоченный набор данных, база данных на рабочем листе.

Столбцы списка называются полями, строки – записями.

Ведение списка можно осуществлять в диалоговом окне (форме).

Над списками можно выполнять такие операции, как фильтрация и сортировка.

В процессе сортировки списка строки переупорядочиваются в соответствии с видом сортировки (по возрастанию или убыванию)

Фильтрация – это быстрый способ выделения подмножества данных списка для последующей работы с ним. В результате фильтрации списка на экран выводятся только те строки, которые содержат определенные значения, либо те, которые удовлетворяют некоторому набору условий поиска (критерию).

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

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

Консолидация – это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе. В Excel предусмотрено несколько способов консолидации данных. После создания итоговой таблицы с помощью команды Консолидация можно добавлять, удалять или изменять исходные области данных. Кроме этого, можно создать связи итоговой таблицы с исходными данными, с тем чтобы данные области назначения автоматически обновлялись при изменении данных в исходных областях. Основным фактором, влияющим на успешность консолидации, является способ размещения информации в рабочих листах. Если эти способы во всех рабочих листах одни и те же, в этом случае задача консолидации становится достаточно простой.

Сводная таблица–это таблица, обобщающая и анализирующая данные из одной или нескольких таблиц. Исходные данные для сводной таблицы могут находиться в списке на одном листе рабочей книги, на нескольких листах, во внешней базе данных или в другой сводной таблице. Меняя структуру таблицы, можно получать различные сводные ведомости одних и тех же исходных таблиц. Excel позволяет также создавать сводные диаграммы, наглядно представляющие информацию сводных таблиц.

Содержание лабораторной работы

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

Выполнение лабораторной работы

1. Загрузите программу Excel 2013.

2. На листе рабочей книги (Лист1) создайте табл.1 с исходными данными, приведенными ниже.

Таблица 1

3. Рассчитайте цену одного экземпляра по каждому наименованию книжной продукции путем ввода следующей формулы:

= F2/E2

4. Переименуйте Лист1 в Заказ.

5. Получите итоговую сумму по столбцу Сумма. Для этого выделите ячейку F12, и на вкладке Главная дважды щелкните по кнопке автосуммирования (∑).

6. Создайте структуру построенной таблицы для скрытия детальных числовых данных. Для этого выделите столбцы с числовыми значениями и выполните следующие действия: Данные/Структура/Группировать/Колонны. На экране структуры таблицы щелкните кнопку «–», чтобы скрыть столбцы с числами, а затем кнопку «+» для показа скрытой информации

7. Удалите структуру, выделив, ячейки с числовыми значениями и выполнив команду Данные/Разгруппировать/Колонны.

8. Добавьте к существующим листам рабочей книги еще три. Переименуйте Лист2, Лист3, Лист4 в Январь, Февраль, Март, так как они будут содержать информацию о реализации продукции за первые три месяца 2015 года (табл. 2,3,4).

9. Сгруппируйте листы Январь, Февраль, Март и введите общую для них информацию (названия столбцов и наименование товара). Группа листов создается щелчком мышью на ярлыке листа при нажатой клавише CTRL.

10. Для ввода индивидуальной для каждой таблицы информации разгруппируйте листы путем выбора в контекстном меню любого листа команды Разгруппировать листы.

Таблица 2

Таблица 3

Таблица 4

11. Введите и размножьте формулы для подсчета стоимости и итоговых сумм во введенные таблицы. Для нахождения цены каждой продукции в таблицах на листах Январь, Февраль и Март используйте функцию ВПР:

(для таблицы 2)

12. Используйте консолидацию рабочих листов для получения итоговой информации о продаже литературы в целом за квартал (Январь, Февраль, Март). Для этого:

• добавьте новый лист переименуйте его в Консолидация;

• выделите ячейку на новом листе Консолидация, начиная с которой будут размещены итоговые данные (например, A1);

• выполните: Данные/ Работа с данными/ Консолидация;

• в диалоговом окне Консолидация выберите в списке функций функцию Сумма;

• в строку Ссылка введите абсолютную ссылку на консолидируемые данные (например, Январь!$A$2:$C$12) и нажмите кнопку Добавить;

• повторите ввод и добавление данных для ввода всей консолидируемой информации (Март!$A$2:$C$12 и Февраль!$A$2:$C$12);

• включите флажки подписи верхней строки и значения левого столбца;

• нажмите кнопку OK.