Применение диаграмм для анализа данных.

Лабораторная работа №11 ПОСТРОЕНИЕ ГРАФИКОВ И ДИАГРАММ

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

Методические указания

Диаграммы используют для наглядного представления результатов вычислений. В MS Excel можно построить два типа диаграмм: внедренные (эффективны при подготовке отчетов для отображения диаграммы рядом с данными, по которым они построены) и на отдельных листах (удобны для вывода на печать при создании слайдов).

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

1. MS Excel предполагает, что ось категорий Х идет вдоль длинной стороны выделенного диапазона ячеек. Если выделен квадратный диапазон или он занимает по ширине больше ячеек, чем по высоте, то названия категорий будут расположены в верхней строке диапазона. Если высота выделенного диапазона больше ширины, то названия категорий идут вниз по левому столбцу диапазона.
2. Названия вдоль короткой стороны выделенного диапазона используются, как правило, как метки легенды для каждого ряда данных. Если ряд данных один, MS Excel использует это название в качестве заголовка диаграммы.
3. Если в ячейках, предназначенных для названий категорий, находятся числа (а не текст или значения даты), MS Excel предполагает, что в этих ячейках содержится ряд данных, и строит диаграмму без меток на оси категорий, вместо этого нумеруя категории.
4. Если числа находятся в ячейках, предназначенных для названий рядов, MS Excel предполагает, что в этих ячейках содержатся первые точки рядов данных, а каждому ряду данных присваивает имя Ряд1, Ряд2 и т.д.
При использовании Мастера диаграмм сначала рекомендуется выделить на рабочем листе данные, по которым нужно строить диаграмму, включая заголовки данных.

Существуют следующие типы стандартных диаграмм:

1. Гистограмма (столбиковая диаграмма). Используется для сравнения отдельных величин или их изменений в течение некоторого периода времени. Возможно отображение вклада каждой категории в общую сумму при построении нормированной гистограммы или гистограммы с накоплением. Гистограммы могут быть двумерными и объемными. Объемные гистограммы не добавляют ничего нового в представлении данных, однако они производят большее впечатление и используются при подготовке
отчетов и слайдов для докладов. Анализ данных удобнее делать на плоских (двумерных) диаграммах. В качестве разновидности гистограмм следует отметить цилиндрическую, коническую и пирамидальную диаграммы.
2. Линейчатая. Используется для сопоставления отдельных значений в определенный момент времени. Горизонтальное расположение полос позволяет подчеркнуть положительные или отрицательные отклонения от некоторой величины. Данный тип диаграмм можно использовать для отображения отклонений по разным статьям бюджета в заданный момент времени. Возможно отображение вклада каждой категории в общую
сумму, построение нормированной диаграммы.
3. График. Отображает зависимость данных от величины, которая изменяется с постоянным шагом. Графики часто применяются для отображения ситуации с производством, продажей или уровнем цен, чтобы продемонстрировать тенденцию изменения дохода или продаж за равные промежутки времени.
4. Круговая. Отображает соотношение частей целого. Строится по одному ряду, первому в выделенном диапазоне. Предполагается, что все компоненты в сумме составляют 100%. Диаграмма отображает вклад в общую сумму каждого компонента. Процентное соотношение частей вычисляется автоматически. Сектора круговой диаграммы можно выдвинуть из общего круга, чтобы подчеркнуть точки данных, которые они
отображаю (выделяется один сектор и мышкой вытаскивается из диаграммы).
Для сравнения большого числа точек данных лучше использовать вторичные круговые диаграммы или вторичные гистограммы, так как трудно сопоставлять части, если их больше пяти.
5. Точечная. Отображает функциональные зависимости данных от величины, которая может меняться с произвольным непостоянным шагом. Используется для построения графиков функций в декартовой системе координат.
6. С областями. Позволяет прослеживать непрерывные изменения как суммы значений всех рядов данных, так и вклад каждого ряда в эту сумму. Удобны для построения динамики процесса производства или продаж.
7. Кольцевая. Подобно круговой, сравнивает вклад частей в целое, однако может отображать несколько рядов данных.
8. Лепестковая. Является аналогом графика, в полярной системе координат отображая распределение значений, относительно начала координат.
9. Поверхность. Отображает изменение функции двух переменных в виде поверхности. Может использоваться для построения линий уровня.
10. Пузырьковая. Отображает на плоскости набор из трех значений. Подобна точечной, но третья величина отображает размер пузырька.
11. Биржевая. Отображает наборы из трех (самый высокий курс, самый низкий курс, курс закрытия), четырех (курс открытия, самый высокий курс, самый низкий курс, курс закрытия и объем) либо пяти значений (объем, высокий курс, низкий курс и курс закрытия, а также объем, курс открытия, высокий курс, низкий курс и курс закрытия).

Пример 1. Используя Мастер диаграмм в пошаговом режиме построить график функции y = cos(p x) ², при [- 30о; 30о] с шагом 5о. При разработке формулы учесть, что для встроенных тригонометрических функций MS Excel требуются аргументы, заданные в радианах.

Решение. Для построения графика функции создайте таблицу ее значений при различных значениях аргумента, изменяющегося с шагом 5^о (рис. 8.1).

Рис. 8.1. Таблица значений

1. Команда Вставка – Диаграммы.
2. Выбрать Тип диаграммы (рис. 8.2) – позволяет выбрать тип и вид диаграммы.

Рис. 8.2. Выбор типа диаграмм

3.Выбор источника данных диаграммы (рис. 8.3) – позволяет задать диапазон, содержащий данные для построения диаграммы. Диапазон можно задать как с клавиатуры, так и с помощью мыши. Следует указать, как размещаются ряды данных (в строках или в столбцах). Имеется также возможность добавления новых рядов, удаления существующих, задания названий рядов данных, их значений, подписей по горизонтальной оси Х.

Рис. 8.3. Выбор источник данных диаграммы

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

Рис. 8.4. З адание параметров

5. Размещение диаграммы (рис. 8.5) – позволяет определить, является ли диаграмма встроенной или ее следует разместить на отдельном листе (вкладка Конструктор – категория Расположение – команда Переместить диаграмму).

Рис. 8.5. Расположение диаграммы

В результате проделанных действий будет построен график функции, изображенный на рис. 8.6.

Рис. 8.6. График функции

Пример 2. Постройте поверхность z для х и у Î [-1; 1] с шагом 0,1.

z=3 x² - 2y² sin²x

Решение. Для построения поверхности z создайте таблицу ее значений при различных значениях переменных, изменяющихся с шагом 0,1 (рис. 8.7).

Рис. 8.7. Таблица значений функции z при различных значениях переменных

Выделим все исходные данные, вставка диаграммы. Затем выберем тип диаграммы – поверхность (рис. 8.8).

Рис. 8.8. Поверхность для функции z

Редактирование диаграмм.

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

Чтобы удалить диаграмму, еѐ нужно выделить и нажать «Delete».

В любой диаграмме различают следующие элементы: область диаграммы, область построения, ряды данных, легенды, стрелки, линии максимума и минимума, линии сетки, надписи, проекции, оси.
Любой из элементов диаграммы можно редактировать (форматировать). Существует множество инструментов для изменения внешнего вида построенной диаграммы и любого ее объекта. Можно изменить тип уже построенной диаграммы, добавить на диаграмму новые ряды или точки данных, удалить часть включенных в диаграмму данных, добавить заголовки, легенду и любые другие надписи, поясняющие диаграмму.
Так как диаграмма связана с данными, на основе которых она построена, при любых изменениях данных на рабочем листе диаграмма будет автоматически обновляться. Если появилась необходимость добавить на уже существующую диаграмму какие-то данные, достаточно выделить на рабочем листе их и перетащить в область построения диаграммы.
Самым простым способом форматирования, как самой диаграммы, так и отдельных ее объектов, является использование команд контекстного меню. Форматирование диаграмм предполагает изменение цвета, размера шрифта, масштаба осей.

Применение диаграмм для анализа данных.

Возможны два варианта действий.
1. Перемещение маркеров для изменения значений на рабочем листе;
2. Анализ с помощью линий тренда.

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

Для добавления линии тренда к ряду данных, который необходимо проанализировать, следует в контекстном меню выбранного ряда данных выбрать команду Добавить линию тренда (рис. 8.9)

Рис. 8.9. Диалоговое окно команды Формат линия тренда

В диалоговом окне «Формат линии тренда» можно выбрать один из шести типов аппроксимации данных:
а) линейная: y = mx + b
б) логарифмическая: y = c ´ ln(x) + b
в) полиномиальная: y = b + c ₁ ´ x + c ₂ ´ x ² +... + c ₆ ´ x ⁶
г) степенная: y = c ´ x^b
д) экспоненциальная: y = c ´ e^bx
е) скользящее среднее (линейная фильтрация).

Помимо этого можно определить название аппроксимирующей кривой, сделать прогноз на указанное количество периодов вперѐд или назад, задать точку пересечения с осью Y, показать уравнение кривой на диаграмме, поместить на диаграмму величину lостоверности аппроксимации. Чем ближе величина R ^2 достоверности аппроксимации к 1, тем достоверней прогноз.
Для анализа данных могут быть использованы планки погрешностей. Как правило, они используются для визуального представления допущенных погрешностей или степени неопределѐнности ряда данных. Чем больше неопределѐнностей, тем шире планка погрешностей.
Для того, чтобы добавить на диаграмму планки погрешностей, следует сначала выбрать требуемый ряд данных. Затем на вкладке Макет группа Анализ выбрать команду Планки погрешностей и задать необходимые параметры погрешностей (тип планки, величину погрешности: фиксированное значение, относительное значение, стандартное отклонение, стандартная погрешность, пользовательская погрешность).

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