Лабораторная работа 4 Тема Анализ данных в Excel с использованием метода наименьших квадратов

Название	Лабораторная работа 4 Тема Анализ данных в Excel с использованием метода наименьших квадратов
Дата	17.07.2019
Размер	121.5 Kb.
Формат файла
Имя файла	Laboratornaya_rabota_%b94_Ispolzovanie_funkcii_Excel.doc
Тип	Лабораторная работа #84224
страница	1 из 2

1 2

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

Тема: Анализ данных в Excel

с использованием метода наименьших квадратов
Порядок выполнения работы (вариант согласно таблицы 1):

1.Для данного набора пар значений независимой переменной и экспериментального значения функции определить наилучшие линейное приближение в виде прямой с уравнением y=ax+bи показательное приближение в виде линии с уравнением y=b*a^x двумя способами. Построить графики функций.

Запустите программу Excel (Пуск ►Программы►Microsoft Excel). Дважды щелкните на ярлычке листа и переименуйте его как Обработка эксперимента.
В первой строке оформите шапку таблицы. Для этого введите в ячейку A1-x, в ячейку B1-y, в ячейку C1-a, в ячейку D1-b.
В столбцы A и B, начиная с ячеек А2 и B2, введите значения независимой переменной x и набор значений функции y согласно варианта.

Отсортируйте данные. Для этого выделите методом протягивания, начиная с ячеек A2 и B2, значения независимой переменной x и набор значений функций y, затем щелкните на кнопке

Сделайте ячейку С2 текущей и щелкните на кнопке Мастер формул. В списке Категория выбрать пункт Ссылки и массивы, а в списке функции – функцию ИНДЕКС. В новом диалоговом окне выберите первый вариант набора аргументов (массив; номер строки; номер столбца).
Установите текстовый курсор в первое поле (Массив) для ввода параметров в палитре формул и выберите пункт Другие функции в раскрывающемся списке в строке формул.
С помощью Мастера функций выберите функцию ЛИНЕЙН категории Статистические.
В качестве первого параметра функции ЛИНЕЙН выберите диапазон, содержащий значения функцииy (столбец В).
В качестве второго параметра функции ЛИНЕЙН выберите диапазон, содержащий значения независимой переменной х (столбец А).
Переместите текстовый курсор в строке формул, чтобы он стоял на имени функции ИНДЕКС. В качестве второго параметра функции ИНДЕКС задайте число 1. Щелкните на кнопке ОК на палитре формул.
Сделайте ячейку D2 текущей. Повторите операции, описанные в пп.5-9, чтобы в итоге в этой ячейке появилась формула :=ИНДЕКС(ЛИНЕЙН(В2:В10;А2:А10);2). Ее можно ввести и вручную (посимвольно). Теперь в ячейках C2 и D2 вычислены, соответственно, коэффициенты а и b уравнения наилучшей прямой.
Сделайте текущей ячейку С3. Повторите операции, описанные в пп.5-9, только вместо функции ЛИНЕЙН выберите функцию ЛГРФПРИБЛ. Или введите вручную следующую формулу: = ИНДЕКС(ЛГРФПРИБЛ(В2:В10;А2:А10);1).
Сделайте текущей ячейку D3. Повторите операции, описанные в пп.5-9, только вместо функции ЛИНЕЙН выберите функцию ЛГРФПРИБЛ. Или введите вручную следующую формулу: =ИНДЕКС(ЛГРФПРИБЛ(В2:В10;А2:А10);2). Теперь ячейки С3 и D3 содержат, соответственно, коэффициенты а и b уравнения наилучшего показательного приближения.
Для построения наилучшей кривой другим способом дайте команду Сервис ► Анализ данных.

Выполнение статистического анализа

В меню Сервис выберите команду Анализ данных.

Если эта команда недоступна, загрузите пакет анализа.

В меню Сервис выберите команду Надстройки.
В списке надстроек выберите Пакет анализа и нажмите кнопку OK.
Выполните инструкции программы установки, если это необходимо.

Выберите нужную функцию в диалоговом окне Анализ данных и нажмите кнопку ОК.
Установите параметры анализа в соответствующем диалоговом окне.

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

В списке Инструменты анализа выберите пункт Регрессия, после чего щелкните на кнопке ОК.
В поле Входной интервал Y укажите методом протягивания диапазон, содержащий значения функции (столбец В).
В поле Входной интервал Х укажите методом протягивания диапазон, содержащий значения функции (столбец А).
Установите переключатель Новый рабочий лист и задайте для него имя Результат расчета.
Щелкните на кнопке ОК и по окончании расчета откройте рабочий лист Результат расчета. Убедитесь, что вычисленные коэффициенты (см. лист Результат расчета ячейки В17 и В18) совпали с полученными первым методом.
Построение графика функций. Так как Excel не позволяет непосредственно строить графики функций, заданных формулами, необходимо сначала табулировать формулу, то есть создать таблицу значений функций для заданных значений переменной. Сделайте текущей ячейку Е3 и занесите в нее значение 0. Эта ячейка будет использоваться как ячейка ввода, на которую будут ссылаться формулы.
Методом протягивания выделите значения независимой переменной в столбце А. Дайте команду Правка ►Копировать, чтобы перенести эти данные в буфер обмена. Сделайте текущей ячейку F2 и дайте команду Правка ►Вставить, чтобы скопировать заданные значения независимой переменной в столбец F, начиная со второй строки.
В ячейку G1 введите формулу = E3*$C$2+$D$2. Здесь E3 – ячейка ввода, а в качестве других ссылок используются вычисленные методом наименьших квадратов коэффициенты уравнения прямой.
В ячейку Н1 введите формулу = $D$3*$C$3^E3 для вычисления значения показательной функции. В программе можно табулировать несколько функций одной переменной в рамках единой операции.
Выделите прямоугольный диапазон, включающий столбцы F,G и Н и строки от строки 1, содержащей формулы, до последней строки с данными в столбце F.
Дайте команду Данные►Таблица подстановки. Выберите поле Подставлять значения по строкам и щелкните на ячейке ввода Е3.
Щелкните на кнопке ОК, чтобы заполнить пустые ячейки в столбцах G и Н выделенного диапазона значениями формул в ячейках первой строки для значений независимой переменной, выбранных из столбца F.
Удерживая клавишу Ctrl методом протягивания выделите ячейки с набором значений функции yи с табулированными данными (столбцы B,F,G,H начиная со второй строки). Щелкните на кнопке Мастер диаграмм на стандартной панели инструментов. В списке Тип выберите пункт Точечная (для отображения графика, заданного парами значений). В палитре Вид выберите средний пункт в первом столбце (маркеры, соединенные гладкими кривыми). Щелкните на кнопке Далее.
Так как диапазон ячеек был введен заранее, мастер диаграмм автоматически определяет расположение рядов данных. Убедитесь, что данные на диаграмме выбраны правильно. На вкладке Ряд в поле Ряд установите курсор на Ряд 1, переведите курсор в поле Имя укажите: Исходный наборзначений. Переведите курсор на Ряд 2, а в поле Имя введите Наилучшая прямая. Затем переведите курсор на Ряд 3, а в поле Имя введите Показательная функция. Щелкните на кнопке Готово.
Сохраните рабочую книгу под своей фамилией.

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

Таблица 1

N варианта

Независимая переменная Х

Экспериментальное значение функции У

0.92

4.31

0.09

7.92

4.11

1.80

5.13

1.84

9.09

0.85

1.92

1.88

3.40

9.40

6.34

4.21

2.48

7.77

6.27

0.47

2.18

9.80

8.92

0.47

1.37

4.89

7.94

1.65

2.35

3.47

7.76

9.53

7.39

9.64

6.07

8.86

5.56

9.04

1.10

5.37

3.82

2.03

94.39

24.52

75.18

96.93

72.72

78.27

0.41

0.76

9.81

8.87

1.80

8.88

44.71

31.86

16.61

57.85

48.44

51.94

2.67

7.30

5.01

9.05

3.55

7.78

16.64

76.49

28.20

97.64

38.51

9.36

9.26

0.73

0.07

6.01

7.01

3.54

2.92

90.49

41.35

38.95

66.05

82.71

7.95

5.77

4.99

7.22

5.77

1.68

37.02

42.84

22.09

5.79

94.14

44.14

1.80

3.51

9.99

7/82

3.99

8.16

27.02

61.51

75.64

62.25

68.98

25.08

9.44

6.10

3.16

2.24

6.01

6.36

79.22

91.14

84.52

80.04

3.54

40.47

9.87

8.90

0.24

3.67

3.17

9.29

77.52

85.65

6.32

8.99

96.59

29.29

1.01

9.06

4.95

3.72

9.91

1.80

39.23

94.16

91.74

7.99

97.85

88.11

5.15

6.89

9.22

2.13

5.56

3.63

14.06

51.69

11.50

71.77

66.78

48.14

8.37

0.83

5.32

8.53

9.88

5.43

46.09

63.44

37.07

94.28

31.1

97.94

3.14

7.22

2.21

9.35

0.79

7.91

22.65

88.82

78.60

64.45

28.56

9.30

9.61

3.95

7.55

6.70

7.22

5.48

77.33

1.15

62.18

21.90

7.84

75.28

4.82

0.55

9.72

1.99

9.59

0.26

9.20

8.53

33.42

30.88

69.96

26.42

2.22

4.02

6.69

5.40

1.70

4.56

68.37

10.94

40.52

36.06

56.47

34.42

6.95

0.51

8.07

3.20

6.65

5.47

69.81

98.41

26.10

60/81

81.48

9.46

3.16

8.32

0.29

1.78

2.13

9.78

33.23

17.01

34.49

34.02

11.30

98.26

1 2