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

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. Введите приведенную ниже таблицу.

 

 

Наименование	Январь	Февраль	Март	Апрель	Май	Июнь	Спарклайны
Товар 1						-55
Товар 2					-6
Товар 3	-2		-68
Товар 4		-24
Товар 5	-20		-69	-22
Товар 6
Товар 7			-96	-69	-67
Товар 8		-32		-76	-1

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

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

 

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

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

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