Построение графиков, диаграмм и

Линий тренда в EXCEL

Цель работы.Изучить:

· построение графиков функций;

· построение диаграмм;

· построение линии тренда.

Теоретическая часть

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

Рис. 5.1. Мастер диаграмм

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

Выделив диапазон, содержащий значение функции, вызывается Мастер диаграмм, выбирается тип Графики и в открывшемся окне выбирается тип График. В открывшемся окне выбирается вид графики.

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

Рис. 5.2. Построение графика

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

К графику можно добавить название, подписи по осям координат и др.

На любом шаге можно нажать Готово в результате чего построение графика завершится.

Рис. 5.3. Построение графика

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

Диаграммы позволяют наглядно, в наиболее выгодном свете представить информацию. В Excel можно построить объемные и плоские диаграммы. Для построения диаграмм также используется Мастер диаграмм.

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

Ниже приведен пример диаграммы (рис. 5.4) для таблицы счета (табл. 5.1):

Рис. 5.4. Диаграмма

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

Таблица 5.1

Таблица успеваемости студентов группы ВР

за 2 семестр 2016 года

№ п/п	ФИО студента	Наименование дисциплины
математика	физика	сопромат	теоретическая меха-ника	начертательная геометрия	сред. оценка за семестр
	Иванов А.С.						3, 8
	Петренко Р.И.						4, 0
	Кузнецова Т.А.						4, 0
	Новчихин С.М.						3, 8
	Петрова А.Р.						3, 6
	Евсяков П.П.						3, 4
	Зиняков Н.П.						3, 4
	Пронин В.В.						3, 6
	Былова С.Ю.						3, 6
	Средняя оценка по дисциплине	3, 9	3, 7	3, 9	3, 3	3, 7

Линия тренда

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

Рис. 5.5. Работа с линией тренда

В диалоговом окне выбирается тот тип линии, который наилучшим образом подходит для описания табличных данных (рис. 5.6).

Рис. 5.6. Работа с линией тренда

Рис. 5.7. Линия тренда

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

Очень важной характеристикой регрессионных зависимостей является мера их достоверности, которая оценивается величиной R², находящейся в пределах 0 ≤ R² ≤ 1. При R² = 0 величины, для которых определяются уравнения регрессии, являются независимыми. При R²= 1 имеет место функциональная (а не статистическая) зависимость. Принято считать допустимым R² = ≥ 0, 7. При этих значениях данную зависимость можно использовать для предсказания результата.

Варианты заданий

Варианты заданий для работы с приведены в табл. 5.2 – 5.5.

Задание 1

Построить на одной координатной сетке графики трех функций согласно полученному варианту. В легенде добавить соответствующие подписи. Дважды щелкнув ЛКМ на рисунке и по линии графика поэкспериментировать с цветом и типом линий графика.

Y1 = аx + в;

Y2 =ах² + вx + с;

Y3 = аx³ + вx.

Таблица 5.2

Исходные данные

№ варианта	Коэффициент – а	Коэффициент – в	Коэффициент – с	х – принадлежит промежутку	Шаг изменения х

		2, 2	3, 1	[-2, 2]	0, 1
	1, 2	2, 5	3, 2	[-5, 5]	0, 23
	1, 4	2, 7	3, 3	[-8, 5]	1, 3
	1, 6	2, 8	3, 4	[-8, 8]	1, 2
	1, 8	3, 2	0, 3	[-4, 5]
		3, 5	4, 2	[-4, 4]	1, 1
	2, 2	3, 8	0, 22	[-7, 5]	0, 9
	2, 5		3, 7	[-7, 7]	0, 87
	2, 7	1, 5	0, 5	[-9, 5]	0, 99
	2, 8	1, 7	0, 7	[-6, 5]	0, 2
	3, 2	2, 1	0, 9	[-5, 6]	0, 22
	3, 5	2, 3		[-4, 5]	0, 5
	3, 8	2, 6	1, 2	[-5, 55]	0, 6
		3, 1	1, 4	[-55, 55]	0, 7
	1, 5	3, 2	1, 6	[-45, 45]	0, 8
	1, 7	3, 3	1, 8	[-3, 3]	0, 55
	2, 1	3, 4		[-9, 9]	0, 95
	2, 3	0, 3	2, 2	[0, 5]	0, 34
	2, 6	4, 2	2, 5	[0, 9]	0, 44
	3, 1	0, 22	2, 2	[-9, 0]	1, 1
	3, 2	3, 7	2, 5	[-8, 2]	1, 54
	3, 3	0, 5	2, 7	[-6, 5]	0, 77
	3, 4	0, 7	2, 8	[-8, 0.5]	0, 66
	0, 3		3, 2	[-5, 5, 0]	0, 55
	4, 2	1, 2	3, 5	[0, 9]	0, 09
	0, 22	1, 4	3, 8	[0, 8]	0, 08
	3, 7	1, 6		[-3, 3, 3, 3]	0, 33
	0, 5	1, 8	2, 6	[-7, 8, 7, 8]	0, 78
	0, 7		3, 1	[-7, 14]	0, 7
	0, 9	2, 2	3, 2	[-5, 5]	1, 05
	0, 3	2, 5	3, 3	[-10, 10]

Задание 2

Построить линию тренда для имеющихся данных согласно полученному варианту (табл. 5.3). Показать на диаграмме уравнение регрессии и величину достоверности аппроксимации R².

Таблица 5.3

Исходные данные

№ варианта	Значения х одинаковы для всех вариантов

	Значения у	2, 2	2, 6	3, 5	4, 3	4, 6	5, 0	5, 6	6, 5	7, 1
	Значения у	1, 1	1, 5	2, 1	2, 6	3, 3	4, 2	4, 5	5, 6	5, 7
	Значения у	0, 78	1, 1	0, 9	1, 4	1, 45	1, 6	1, 65	1, 68	1, 7
	Значения у	2, 0	3, 6	4, 5	6, 0	7, 0	8, 1	9, 1	9, 9	11, 3
	Значения у	0, 3	2, 7	3, 1	5, 1	5, 3	6, 7	7, 1	8, 9	9, 0
	Значения у	1, 9	2, 5	3, 1	3, 9	5, 2	5, 8	7, 4	8, 2	9, 1
	Значения у	1, 1	2, 5	3, 3	4, 5	5, 1	6, 1	7, 4	8, 5	9, 1
	Значения у	2, 0	2, 6	3, 4	4, 3	4, 6	5, 0	5, 6	6, 5	7, 2
	Значения у	0, 88	1, 1	0, 9	1, 4	1, 41	1, 6	1, 65	1, 66	1, 7
	Значения у	2, 4	3, 6	4, 2	6, 0	7, 0	8, 1	9, 4	9, 9	11, 3
	Значения у	2, 88	4, 5	5, 2	7, 2	8, 4	9, 7	11, 2	12, 0	13, 7
	Значения у	1, 4	2, 1	3, 0	3, 5	4, 4	6, 1	6, 3	7, 7	8, 1
	Значения у	1, 1	1, 4	1, 1	1, 8	1, 7	2, 2	2, 0	2, 3	2, 4
	Значения у	3, 2	5, 4	6, 6	9, 2	11, 0	12, 2	13, 7	14, 5
	Значения у	0, 5	4, 2	4, 8	7, 8	8, 2	11, 3	11, 1	13, 2	13, 2
	Значения у	2, 2	2, 6	3, 4	4, 1	5, 6	6, 2	8, 2	9, 1	10, 2
	Значения у	1, 1	2, 7	3, 4	4, 9	4, 9	5, 9	7, 5	8, 7	9, 0
	Значения у	1, 9	2, 5	3, 4	4, 4	4, 2	4, 8	5, 4	6, 6	6, 9
	Значения у	1, 1	1, 32	1, 08	1, 68	1, 69	1, 92	1, 98	1, 99	2, 04
	Значения у	2, 6	3, 1	4, 1	5, 2	5, 5	6, 0	6, 8	7, 8	8, 7

Окончание табл. 5.3

№ варианта	Значения х одинаковы для всех вариантов

	Значения у	1, 3	1, 6	1, 3	2, 0	2, 2	2, 2	2, 2	2, 2	2, 5
	Значения у	0, 6	0, 8	0, 5	0, 7	0, 9	1, 0	1, 0	1,.2	1, 1
	Значения у	0, 8	0, 98	0, 77	1, 3	1, 2	1, 5	1, 4	1, 6	1, 68
	Значения у	1, 06	1, 33	1, 03	1, 75	1, 67	2, 12	1, 93	2, 25	2, 64
	Значения у	1, 2	1, 6	1, 1	1, 2	1, 5	1, 8	2, 0	2, 1	2, 5
	Значения у	0, 66	0, 88	0, 6	0, 9	1, 2	1, 1	1, 2	1, 44	1, 1
	Значения у	0, 18	0, 24	0, 15	0, 19	0, 25	0, 4	0, 5	0, 36	0, 4
	Значения у	0, 4	0, 6	0, 35	0, 4	0, 5	0, 9	1, 0	0, 8	0, 9
	Значения у	2, 6	4, 3	5, 3	7, 4	8, 5	9, 5	11, 0	11, 6	14, 5
	Значения у	1, 6	2, 6	3, 2	4, 5	5, 1	5, 7	7, 7	5, 8	11, 6
	Значения у	1, 8	2, 1	2, 7	3, 3	4, 5	5, 0	6, 6	7, 3	8, 2

Задание 3

1. Создать таблицу 5.4 изменения прибыли фирмы по кварталам в различные годы в тыс. руб. Данные приведены для варианта № 1, все последующие варианты используют представленные числа с коэффициентом, соответствующим номеру полученного варианта.

Таблица 5.4

Изменения прибыли фирмы

Год	1-й квартал	2-й квартал	3-й квартал	4-й квартал
	14, 2	26, 5	4, 2	19, 7
	20, 4	23, 4	52, 2	63, 5
	52, 1	78, 6	85, 2	82, 1

Представить данные в виде:

· обычной гистограммы (за любой квартал с 2000 года по 2002 год);

· трехмерной гистограммы (за весь период);

· круговой диаграммы (за любой год).

2. Задать в таблице свои оценки (по 10 балльной шкале) уровня сложности дисциплин, вынесенных на экзамен в предстоящую сессию. Создать по этим данным диаграмму с рисунками и легендой.

3. Просмотреть дополнительно 2-3 типа диаграмм. Объяснить, для чего они могут быть использованы.

Задание 4

1. Создать таблицу 5.5 зависимости y = f(x). Данные приведены для варианта № 1, все последующие варианты принимают функцию корня в степени 1 /коэффициент, где коэффициент соответствует номеру полученного варианта +1.

Таблица 5.5

Функциональная зависимость

x

2. Построить точечную диаграмму и график.

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

Выполнить задания 1-4 в соответствии с вариантом.

При выполнении заданий придерживаться следующей последовательности:

1. Создать таблицу со значениями x и y для построения графика с заданными значениями на заданном отрезке.

2. Открыть Мастер диаграмм, выбрать вид – Графики или Диаграммы и назначить тип графика или диаграммы.

3. В случае построения графика выбрать вкладку Ряд и, установив курсор в строке Подписи по оси х, указать диапазон значений аргумента. К графику добавить название, подписи по осям координат и легенду. После нажатия кнопки Готово получается построенный график или диаграмма.

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

Содержание отчета

Отчет должен содержать:

· номер, название и цель работы;

· краткую теоретическую часть;

· результаты выполнения работы согласно заданию, выданному преподавателем, а именно созданные графики, диаграммы и линии тренда;

· заключение по работе.

Контрольные вопросы

1. Как выполняется построение графиков функций в Excel?

2. Как выполняется построение диаграмм в Excel?

Как выполняется построение линии тренда в Excel?

ЛИТЕРАТУРА

1. Днепров, А.Г. Видеосамоучитель Word 2007 (+CD) [Текст] / А.Г. Днепров. – СПб.: Изд. дом «Питер», 2007. – 240 с.

2. Донцов, Д.А. Word 2007. Начали! [Текст] / Д.А. Донцов. – СПб.: Изд. дом «Питер», 2008. – 128 с.

3. Левин, А.Ш. Word – это очень просто! [Текст] / А.Ш. Левин. – 2-е изд. – СПб.: Изд. дом «Питер», 2008. – 128 с.

4. Меженный, О.А. Microsoft Word 2007 [Текст]: краткое руководство / О.А. Меженный. – Киев.: Изд-во «Диалектика», 2008. – 272 с.

5. Меженный, О.А. Microsoft Word 2007 [Текст]: самоучитель / О.А. Меженный. – Киев.: Изд-во «Диалектика», 2007. – 320 с.

6. Симонович, В.С. Microsoft Word 2007 [Текст]: практический справочник / В.С. Симонович. – СПб.: Изд. дом «Питер», 2008. – 480 с.

7. Симонович, В.С. MS Word 2007. Эффективная работа [Текст] / В.С. Симонович. – СПб.: Изд. дом «Питер», 2008. – 640 с.

⇐ Предыдущая 1 2 3 4 56