Лабораторная работа №5 Линии тренда Лабораторная работа №5

Тема: Линии тренда

Цель: Научиться строить линию тренда.

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

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

Задание №1. Построить линию тренда для данных X и Y.

Шаг 1. В Excel введем таблицу (рис. 31) со значениями X и Y.

Рис. 31. Исходные данные

Шаг 2. Выделяем таблицу на рис. 31 и заходим в меню «Вставка». В появившемся окне выбираем параметр «Диаграмма». После произведенных действий появляется окно «Мастер диаграмм» (рис. 32). В меню «Стандартные» существует два параметра «Тип» и «Вид». В параметре «Тип» выбираем «Точечная». В параметре «Вид» выбираем «Точечная диаграмма, на которой значения соединены отрезками», и нажимаем кнопку «Готово» (рис. 32).

57

В итоге получается график. Для того чтобы на графике появилась линия тренда необходимо установить курсор мышки на любую точку графика и щелкнуть правой клавишей. После этого появляется меню, где мы выбираем команду «Добавить линию тренда». В окне «Линия тренда» в меню «Тип» выбираем параметр «Линейная» и нажимаем кнопку «ОК» (рис. 33).

Рис. 32. Диалоговое окно «Мастер диаграмм»

Рис. 33. Диалоговое окно «Линия тренда»

В итоге появляется график изображенный на рисунке 35.

58

Для появления на линии тренда R – квадрат, необходимо курсором мыши щелкнуть на линии тренда и зайти в меню «Формат» - «Выделенная область построения». В появившемся окне «Формат линии тренда» в меню «Параметры» устанавливаем галочку, где написано «поместить на диаграмму величину достоверности аппроксимации (R^2)» и нажимаем кнопку «ОК» (рис. 34).

Рис. 34. Диалоговое окно «Формат линии тренда»

Рис. 35. Линия тренда с величиной достоверности аппроксимации

Вывод: научились строить линию тренда

59

Задание №2. Самостоятельно построить линию тренда для значений X и Y:

Таблица 32

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

1	X	472	538	467	572	517	372	433	438	428	548	448	330	390	482
	Y	135,6	165,0	113,0	180,9	151,1	81,7	128,2	131,4	130,3	162,3	123,3	71,1	108,1	139,9
2	X	557	388	525	465	453	413	405	496	372	452	525	483	461	402
	Y	151,9	105,6	156,5	140,9	124,2	106,7	107,5	83,8	81,7	119,7	148,3	147,O	138,6	129,8
3	X	543	500	442	440	461	477	502	490	377	458	481	345	436	440
	Y	155,4	155,5	126,2	133,1	138,6	139,7	122,6	134,4	96,1	121,7	132,7	95,9	116,7	105,7
4	X	471	480	401	525	484	453	499	522	458	411	398	422	453	493
	Y	143,9	153,3	100,1	162,1	147,5	126,4	144,5	143,8	130,7	112,9	109,O	122,9	138,2	154,5
5	X	458	452	458	460	518	331	452	453	457	394	369	439	468	408
	Y	121,7	116,1	121,7	140,7	151,3	74,1	119,7	138,2	126,4	117,7	84,3	119,2	128,6	110,O
6	X	457	443	394	451	510	411	518	477	573	408	480	445	320	436
	Y	126,4	135,7	112,1	124,3	153,9	112,9	151,3	146,O	156,5	109,O	153,9	124,7	72,6	114,3
7	X	406	462	377	382	447	486	446	505	376	411	409	433	501	473
	Y	113,8	135,7	96,O	98,1	117,5	139,4	144,O	137,5	112,8	112,9	121,O	131,5	130,4	136,4
8	X	418	437	399	422	542	513	506	366	331	424	482	540	443	480
	Y	109,8	129,2	73,4	122,9	146,1	159,3	153,5	93,6	84,6	117,1	139,9	156,7	121,9	146,1
9	X	523	440	410	516	453	525	458	515	448	353	421	458	382	481
	Y	172,8	105,7	110,5	132,6	126,4	165,9	104,7	155,8	123,3	98,O	107,9	130,7	113,9	132,7
10	X	510	391	439	436	480	440	547	408	591	544	498	532	458	464
	Y	162,3	98,2	119,2	117,9	146,1	105,7	164,4	109,O	156,9	166,7	145,5	158,7	133,5	131,3
11	X	468	514	484	438	383	436	345	408	197	453	437	498	458	463
	Y	128,9	174,6	140,4	131,4	109,7	114,3	95,9	109,O	106,8	126,4	133,7	164,O	130,7	125,2
12	X	569	386	353	470	382	502	416	523	400	510	489	496	523	430
	Y	157,4	95,5	98,O	124,O	98,8	132,5	120,5	152,6	106,3	153,9	149,8	155,5	172,8	104,3
13	X	377	473	359	481	496	440	394	532	443	503	438	482	437	443
	Y	96,O	136,4	71,9	148,3	155,3	133,1	112,1	160,6	121,9	149,9	122,2	148,2	129,4	122,9
14	X	481	421	437	543	453	438	433	421	466	344	376	472	463	512
	Y	148,3	115,1	118,4	155,4	138,2	122,2	128,2	117,8	137,9	86,8	103,1	135,6	136,7	169,9
15	X	440	506	451	428	430	572	353	518	480	472	411	427	423	525
	Y	113,O	153,5	124,3	130,3	104,3	180,9	62,6	144,4	153,3	134,2	120,7	125,8	104,1	162,1
16	X	413	340	475	462	460	371	493	443	464	495	507	403	431	481
	Y	106,7	85,1	155,7	135,7	140,7	89,2	151,2	121,9	131,3	149,7	142,4	123,9	125,O	137,3
17	X	496	421	431	475	474	428	461	440	411	481	477	445	453	521
	Y	155,3	118,O	125,O	132,O	132,5	106,5	134,9	126,7	120,7	148,3	146,O	124,7	119,5	154,9

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Что такое тренд?

2. Как построить линию тренда?

3. Что такое величина достоверности аппроксимации (R^2)?

4

60

. Как вывести на линию тренда величину достоверности аппроксимации?

Лабораторная работа №6

Решение численных задач

Лабораторная работа №6

Тема: Решение численных задач.

Цель: Научиться решать уравнения и системы уравнений.

Задание №1.

Необходимо найти корень квадратного уравнения (формула 20). Уравнение необходимо представить в компьютерном виде и найти значение X, при котором это уравнение равно 0.

x²/2-3*x+4=0 (20)

Решение задания №1

Шаг 1. В ячейку А1 записываем правую часть уравнения, т.е. 0. В ячейку А2 записываем левую часть уравнения.

Анализируя уравнение, понимаем, что левая часть должна быть равна нулю (т.е. 0=0). Нам необходимо найти такое значение x, при котором данное уравнение равно нулю. При этом x²/2 можно представить как ½x². Следовательно, ½ x² – это 0,5 x².

Решение уравнения будем искать в ячейке А1, куда занесем исходное значение корня, например 0. Само уравнение запишем в ячейку А2. Считаем А1 – это Х, тогда уравнение x²/2-3*x+4 примет вид: 0,5*A1²-3*A1+4.

В результате в ячейку А1 вводим 0, а в ячейку А2 вводим уравнение 0,5*A1²-3*A1+4 (таблица 33). Чтобы возвести значение в квадрат, необходимо перейти на английский язык и нажать клавишу с цифрой «6». После этого появится значок « ^ ».

Для ввода формулы необходимо мышкой нажать на ячейку (для её активации). Набираем формулу вручную, но делаем это не в самой ячейке, а выше, там, где на панели инструментов находится обозначение f(x). Ввод формулы начинается со знака равенства « = ». Например, f(x)=……..

Таблица 33

Уравнение

	А
1	0
2	=0,5*A12-3*A1+4

В итоге в ячейках появляются следующие данные (таблица 34):

Таблица 34

	А	В	С
1	0
2	4

63

Шаг 2. Решим уравнение. Установите курсор мыши в любую ячейку, кроме А1, А2 и выберите команду «Сервис» - «Подбор параметра». Заполните окно «Подбор параметра» (рис. 36). В диалоговом окне «Подбор параметра» напротив поля «Установить в ячейке» должны появиться данные $А$2. Для этого установите курсор мыши в ячейку А2. В поле «Значение» наберите значение 0. В поле «Изменяя значение ячейки» должны появиться данные $А$1. Для этого перейдите курсором мыши в поле «Изменяя значение ячейки», а после этого установите курсор мыши в ячейку А1. Нажмите кнопку «ОК».

Рис. 36. Диалоговое окно «Подбор параметра»

После нажатия кнопки «ОК» получаем одно из решений квадратного уравнения (рис. 37).

Рис. 37. Фрагмент листа

Нажимаем кнопку «ОК». При этом диалоговое окно исчезнет, а в ячейках А1-А2 будут следующие данные, показанные на рисунке 38.

Р

64

ис. 38. Фрагмент столбцов А,В,С

Шаг 3. Значения (рис. 38) необходимо округлить до целых чисел. Для этого нужно выделить ячейки А1-А2 и зайти в меню «Формат»-«Ячейки». Появится диалоговое окно «Формат ячеек» (рис. 39).

Рис. 39. Диалоговое окно «Формат ячеек»

Во вкладке «Число» в поле «Числовые форматы» выбираем «Числовой» формат. Выбираем число десятичных знаков «0» и нажимаем «ОК». В результате найденное решение уравнения округляется до целых чисел (рис. 40).

Рис. 40. Фрагмент столбца А, В.

Таким образом, решение уравнения найдено. При значении х=2 уравнение будет равняться 0.

Задание №2

Решить систему двух нелинейных уравнений.

65

(21)

Решение задания №2

Шаг 1. На основе заданной системы уравнений заполним таблицу в Excel. В ячейку А2 введем значение 10, т.е. предполагаем что у=10 (левая часть уравнения). В ячейку В2 введем правую часть первого уравнения. Так как х=у, следовательно х=А2 (значение у=10 находится в ячейке А2). В ячейку В2 вводим формулу: =2-А2. Нажимаем кнопку «Enter». В ячейку В3 вводим правую часть второго уравнения, т.е.: = А2^2-1 (рис. 41). Нажимаем кнопку «Enter».

	А	В
1	Начальное значение	Формулы
2	10	=2-А2
3		= А2^2-1

Рис. 41. Форма для ввода условий задания

В результате таблица 41 примет следующий вид (рис. 42):

Рис. 42. Фрагмент таблицы

Шаг 2. В ячейку С2 вводим целевую функцию (рис. 43). Для этого выделяем ячейку С2 и вводим вручную формулу: =СРОТКЛ(B2;B3) и нажимаем кнопку «Enter».

	А	В	С
1	Начальное значение	Формулы	Целевая ячейка
2	10	=2-А2	=СРОТКЛ(B2;B3)
3		= А2^2-1

Рис. 43. Ввод целевой функции

В результате таблица (рис. 43) примет следующий вид (рис. 44):

Рис. 44. Фрагмент таблицы

66

Шаг 3. Выделяем курсором мыши ячейку С2. Для поиска необходимого решения выделяем С2 и воспользуемся функцией «Сервис»-«Поиск решения» (рис. 45). Для этого заходим в меню «Сервис»-«Поиск решения». Заполняем окно «Поиск решения» согласно данным рисунка 45.

Примечание: Если в меню «Сервис» нет «Поиска решения», то его необходимо установить через «Сервис»-«Надстройки», и поставить галочку, где написано «Поиск решения». Нажимаем кнопку «ОК».

Рис. 45. Диалоговое окно «Поиск решения»

Для заполнения окна «Поиск решения», согласно данным рисунка 45, необходимо:

А) Установить целевую ячейку С2.

Б) Установить минимальное значение.

В) В поле «Изменяя ячейки» ввести диапазон ячеек $В$2:$В$3.

Г) Нажать кнопку «Выполнить».

В результате появляется окно «Результаты поиска решения» (рис. 46). Нажимаем кнопку «ОК» и таблица (рис. 44) принимает следующий вид (рис. 47).

Рис. 46. Результаты поиска решения

Р

67

ис. 47. Результирующая таблица

Шаг 4. Нам необходимо округлить значения до целых чисел. Для этого выделяем ячейку С2 и во вкладке «Число» в поле «Числовые форматы» выбираем «Числовой» формат. Выбираем число десятичных знаков «0» и нажимаем «ОК». После округления таблица (рис. 48) примет следующий вид:

Рис. 48. Итоговая результирующая таблица

Таким образом, решена система нелинейных уравнений при исходном значении «10». То есть при значениях х=45, y= 10.

Шаг 5. Получив решение для исходного значения +10, аналогично выполним то же самое для исходного значения -10, используя функцию «Поиск решения». В результате получаем таблицу (рис. 49).

	А	В	С
1	Начальное значение	Формулы	Целевая ячейка
2	-10	55	0
3		55

Рис. 49. Результирующая таблица (при исходном значении «-10»)