Файл: Лабораторная работа Теоретический материал графики, диаграммы, прогнозирование в ms exсel д иаграммы.pdf

1
Лабораторная работа
Теоретический материал
ГРАФИКИ,
ДИАГРАММЫ,
ПРОГНОЗИРОВАНИЕ
В
MS
EXСEL
Д
ИАГРАММЫ
Диаграммы применяются для анализа данных и занимают важное место в решении различных профессиональных задач, выявлении тенденций развития различных ситуаций, при выборе обоснованных вариантов действий. Если электронная таблица содержит большое количество числовых данных, то проанализировать числа (сравнить, оценить их изменение с течением времени, установить соотношение между ними и др.) достаточно трудно.
Наглядное представление значений показателей в графическом виде позволяет наглядно увидеть изменения, тенденции, а также существенно упрощает процесс их обработки.
Кроме того, графики позволяют обнаружить ошибки, совершенные при вводе данных (например, за счет резких скачков показателей в один из периодов времени). Для графического отображения представленных в электронной таблице данных в Microsoft Excel используется меню диаграммы.
В данном случае под термином ДИАГРАММЫ понимается один из видов графического представления данных, которые могут быть реализован в виде:

2
Плоские или объемные гистограммы:
Графики:
Круговые диаграммы
Линейчатые диаграммы:
Диаграммы с областями:

3
Точечные диаграммы:
Биржевые диаграммы:
Поверхности:
Лепестковая:

4
Комбинированная:
А так же их модификации и разновидности.
Кроме того в Excel есть возможность использования Спарклайнов.
Спарклайн (англ. sparkline, от англ. spark — искра, англ. line — линия) — термин, который придумал Эдвард Тафти для обозначения небольших по размеру, но достаточно информационно-плотных графиков.
Основная особенность спарклайна – их небольшой размер. Так как графики маленькие – они строятся в одной ячейке, то почти всегда места для отображения осей или каких-либо подписей просто нет, поэтому узнать точные значения по ним невозможно. Вместо этого спарклайны дают возможность увидеть общую картину. Спарклайны могут быть очень полезны как дополнительный источник ценной информации, занимая при этом очень мало места.

5
Или
Excel предоставляет широкий диапазон стандартных типов диаграмм научного и делового назначения. Диаграммы в Excel включают много объектов, каждый элемент которых можно выделять и изменять отдельно (табл. 10).
Таблица 10
Элемент
Описание
Ось
Линия, используемая как основа измерений для построения данных на диаграмме.
Ось категорий (X) и ось значений (Y) образуют границы диаграммы и имеют шкалу для нанесения данных.
Ось значений Z используется в трехмерных диаграммах
Точка данных
Элемент данных
Ряд данных
Совокупность точек данных, отображающая значения строк или столбцов рабочего листа. Каждый ряд данных отображается по-своему. На диаграмме может быть отображен один или несколько рядов данных
Легенда
Перечень, в котором определяются цвета рядов или категорий данных на диаграмме
Маркер
Элемент, который представляет точку данных на диаграмме. Вид маркера зависит от типа и вида диаграммы. Все маркеры одного ряда данных имеют одинаковую форму и цвет
Область построения
В двухмерной диаграмме это прямоугольная область, ограниченная двумя осями и содержащая на себе все ряды диаграммы.
В трехмерной диаграмме это область, ограниченная осями и включающая ряды данных, названия категорий, подписи делений и названия осей
Область диаграммы
Вся диаграмма вместе со всеми ее элементами
Формула рядов
Формула с внешними ссылками на данные определенного листа, по которым построена диаграмма
Текст
Надписи (название диаграммы, значения и категории на осях) и подписи (текст, связанный с точками данных). Любой текст

6 на диаграмме можно изменять и перемещать
Деление
Деление шкалы, метки единиц измерения вдоль оси категорий (X) и осей значений (Y и Z)
Таблица данных
Таблица, содержащая отображаемые на диаграмме данные; обычно связана с осью категорий и заменяет подписи оси категорий. Каждая строка таблицы содержит ряд данных
Алгоритм создания диаграммы:
1) определить цель построения диаграммы;
2) выбрать тип диаграммы, соответствующий цели ее построения;
3) выполнить подготовку исходных данных на рабочем листе в соответствии с видом выбранной диаграммы;
4) выполнить построение диаграммы;
5) выполнить форматирование элементов диаграммы для выделения ее особенностей;
6) добавить на диаграмму дополнительные элементы, позволяющие упростить работу с ней.
В Excel диаграммы создаются на основе имеющихся в электронной таблице данных с помощью кнопок группы Диаграммы на вкладке Вставка.
Готовая диаграмма сохраняет связи с таблицей, на основе которой она построена, и автоматически обновляется при изменении исходных данных в таблице.
П
ОСТРОЕНИЕ ДИАГРАММ
В соответствии с указанным выше алгоритмом для построения диаграммы необходимо выполнить следующие действия:
– на вкладке Вставка в группе инструментов Диаграммы указать необходимый тип диаграммы и из раскрывающегося меню выбрать ее;
– в контекстно зависимой области вкладок Работа с диаграммами выбрать вкладку Конструктор и в группе инструментов Данные – команду Выбрать
данные (рис. 11);
Рис. 11
– в появившемся диалоговом окне Выбор источника данных указать
диапазон исходных данных для диаграммы, если нужный диапазон не был выделен заранее, т. е. до начала построения диаграммы.

7
Чтобы диалоговое окно не закрывало исходную таблицу, его можно свернуть, щелкнув по кнопке
, находящейся справа, а для восстановления размеров диалогового окна – сделать повторный щелчок по кнопке (рис. 12);
Рис. 12
– в этом же окне можно задать элементы легенды или имена рядов данных, добавить или удалить один или несколько рядов, нажав необходимые кнопки в области Элементы легенды (ряды), а также указать подписи горизонтальной оси или название категорий, нажав кнопку Изменить в области Подписи
горизонтальной оси (категории);
– для детальной настройки вида диаграммы следует перейти на вкладку
Макет контекстно зависимой области вкладок Работа с диаграммами и выбрать необходимые команды;
– для размещения диаграммы в нужном месте Книги надо на вкладке
Конструктор выбрать группу инструментов Расположение и нажать кнопку
Переместить диаграмму.
ЛИНИИ
ТРЕНДА
И
ПРОГНОЗИРОВАНИЕ
Линия тренда на графике – это линия, которая показывает среднюю тенденцию поведения данных, представленных на графике. Линия тренда строится на основе математического анализа с использованием различных функций. Мы будем пользоваться ЛИНЕЙНЫМ трендом, который представляет прямую линию и задается уравнением прямой: y=ax+b, где а – коэффициент, определяющий наклон прямой, а b
– смещение относительно начала координат.

8
Порядок построения линии тренда:
1. Построить график.
2. На линии графика щелкнуть правой кнопкой мыши и найти пункт меню:
ДОБАВИТЬ ЛИНИЮ ТРЕНДА

9 3. Выбрать параметры линии тренда (отмечено красным маркером), (R
2
- параметр достоверности: 1- достоверность 100%, 0 – тренд ошибочен)
4. При необходимости указать период прогнозирования (в данном случае на 2 периода вперед).
Для расчета числовых значений прогноза в уравнение тренда необходимо подставить прогнозируемые значения x (в данном случае – это 9 и 10).
Пример: для значения 9 имеем: y = 0,0845*9 + 0,1029 = 0,8634. Полученное значение должно лежать на линии тренда при х=9.
КОНТРОЛЬНОЕ ЗАДАНИЕ
1. На ЛИСТЕ 1 Создать и заполнить таблицу по образцу:
2. Постройте 2 графика.
- на первом графике отобразите данные 1-й строки таблицы.
- на втором графике отобразите данные 2, 3 и 4-й строк таблицы.
3. На каждом графике постройте линию Линейного тренда с прогнозом на 2,0 периода вперед (т.е. на 2 года вперед).
4. На каждой линии тренда ее отобразить уравнение (формулу).
№
Год/вид преступлений
2010 2011 2012 2013 2014 2015
прогноз
2016
прогноз
2017
Спарклайн
2010-2015
Спарклайн
2010-2017 1
Зарегистрировано преступлений ВСЕГО (тыс)
16267 15376 13482 13274 12334 14406 2
Зарегистрировано тяжких и особо-тяжких преступлений
(тыс)
3380 2992 2772 2579 2370 2553 3
Зарегистрировано убийств и покушений на убиства (тыс)
114 99 93 72 57 4
Зарегистрировано преступлений, совершенных несовершеннолетними (тыс)
391 419 342 363 276 356

10 5. С использованием формулы прямой линии y=ax+b (линии тренда, полученной в п.4) рассчитать числовые значения для каждой строки таблицы для 2016 и 2017 года
(х=7 и 8 соответственно).
6. Построить новые графики с учетом расчитанных значений прогноза.
8. Постройте спарклайны графиков в соответствующих ячейках таблицы.