Линия тренда

Цель урока

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

Конструируя данное приложение, вы освоите на практике:

• Построение многостраничных диалоговых окон
  
• Построение линии тренда
  
• Применение метода offset для вывода данных на рабочем листе
  
• Считывание данных из каждой конкретной ячейки диапазона
  

Теория

В этом разделе предлагается рассмотреть следующие две задачи.

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

На основе своих наблюдений Сосулькин заметил, что объем продажи мороженого зависит, кроме прочих факторов, от температуры на улице. Объемы продаж эскимо "За ухо не оторвешь" за первые шесть дней торговли приведены в табл. 31.1. Температура в эти шесть дней возрастала.

Таблица 31.1. Температура и объемы продажи

Сосулькин решил определить на основе этих данных, каков ожидаемый объем продажи мороженого в зависимости от температуры, чтобы зная прогноз погоды составить соответствующий запас. Из курса математики он вспомнил, что для этих целей служит уравнение тренда, а критерием адекватности модели является коэффициент корреляции. Сосулькин также вспомнил, что уравнение тренда можно построить на рабочем листе на основе диаграммы. Коэффициенты m и b линейного тренда у = mx + b можно также определить с помощью функций рабочего листа НАКЛОН (SLOPE) и ОТРЕЗОК (INTERCEPT), a коэффициент корреляции функцией КОРРЕЛ (CORREL).

Функция НАКЛОН определяет коэффициент наклона линейного тренда, а функция ОТРЕЗОК определяет точку пересечения линии линейного тренда с осью ординат.

Синтаксис:

НАКЛОН(известные_значения_у; известные_значения_х) ОТРЕЗОК(известные_значения_х; известные_значения_у) КОРРЕЛ(известные_значения_х; известные_значения_у)

Аргументы:

Вторая задача. Сосулькин продолжал наблюдения за продажей эскимо в течение 29 дней. Температуры и объемы продаж на протяжении этого срока повторялись. Результат своих наблюдений Теодор занес в табл. 31.2.

Таблица 31.2. Температура и объемы продажи

Отличием второй задачи от первой является то, что таблица данных состоит не из двух строк, в которые введены значения зависимой и независимой переменных, что, как вы уже знаете, в первой задаче позволило найти решение как графически, так и при помощи функций рабочего листа НАКЛОН, ОТРЕЗОК и КОРРЕЛ, Теодор решил, что это несущественное различие. Достаточно только написать программу, которая преобразует вторую таблицу к виду первой таблицы, а по полученной таблице программа сможет найти и уравнение тренда, и коэффициент корреляции, как и в первом случае.

Практика

В редакторе форм создадим диалоговое окно линейная регрессия (рис. У12.1). Это окно состоит из двух вкладок тренд и параметры (рис. У12.2).

Рис. У12.1. Вкладка Тренд диалогового окна Линейная регрессия

Рис. У12.2. Вкладка Параметры диалогового окна Линейная регрессия

Обсудим, как приведенная ниже программа решает описанную задачу и что в ней происходит.

Рис. У 12.3. Результат решения первой задачи

Рис. У12.4. Результат решения второй задачи

'

Option Explicit

' Переменные уровня модуля

Dim Независимая As String

Dim Зависимая As String

Dim Повторения As String

Dim НезависимаяЗависимая As Object

Dim Корреляция As Double

Dim m As Double

Dim b As Double

'

'

Private Sub CommandButtonl_Click()

' При выборе переключателя С повторениями

' производится расчет по процедуре ТрендСПовторениями,

' а при выборе переключателя.Без повторений

' производится расчет по процедуре ОбьиныйТренд

'

If OptionButtonl.Value = True Then ОбычныйТренд

End If

'

If OptionButton2.Value = True Then

ТрендСПовторениями

End If

'

End Sub

Private Sub CommandButton2_Click()

'

' Закрытие диалогового окна

'

UserForm1.Hide

End Sub

'

Private Sub OptionButtonl_Click()

'

' Обеспечивается скрытие надписи Повторения и RefEdit3

' при выборе переключателя Без повторений

'

RefEdit3.Visible = False

Label3.Visible = False

End Sub

Private Sub OptionButton2_Click()

'

' Обеспечивается видимость надписи Повторения и RefEdit3

' при выборе переключателя С повторениями

RefEdit3.Visible = True

Label3.Visible = True

End Sub

'

Private Sub UserForm_Initialize()

'

' Активизация диалогового окна

'

Caption = "Линейная регрессия" MultiPagel.Value = 0

CommandButton2.Cancel = True RefEddt3.Visible = False

Label3.Visible = False OptionButtonl.Value = True

UserForml.Show

End Sub

'

Sub ОбычныйТренд()

'

' Процедура расчета обычного тренда

'

' Ввод диапазонов данных

'

Независимая = RefEdit1.Value

Зависимая = RefEdit2.Value

' Проверка, располагаются ли данные в столбцах С или D.

' Если располагаются, то отображается соответствующее сообщение

If InStr(Range(Независимая).Address, "С") > 0 Or _ InStr(Range(Независимая).Address, "D") > 0 Then

MsgBox "Независимая переменная не может располагаться в" & Chr(13) & "столбцах С и D", vblnformation, "Линейная регрессия"

RefEditl.SetFocus

Exit Sub

End If

If InStr(Range(Зависимая).Address, "C") > 0 Or _ InStr(Range(Зависимая).Address, "D") > 0 Then

MsgBox "Зависимая переменная не может располагаться в" & Chr(13) & "столбцах С и D",

vblnformation, "Линейная регрессия" RefEdit2.SetFocus

Exit Sub

End If

'

' Проверка, располагаются ли данные только в столбцах,

' либо только в строках

'

If Range(Зависимая).Rows.Count > 1 And

Range(Зависимая).Columns.Count > 1 Then

MsgBox "Зависимая переменная должна располагаться " & Chr(13) & "либо в строке, либо в столбце", vblnformation, "Линейная регрессия" RefEdit2.SetFocus

Exit Sub

End If

'

If Range(Независимая).Rows.Count > 1 And _ Range(Независимая).Columns.Count > 1 Then

MsgBox "Независимая переменная должна располагаться" & Chr(13) & "либо в строке, либо в столбце",' vblnformation, "Линейная регрессия" RefEditl.SetFocus

Exit Sub

End If '

If (Range(Независимая).Rows.Count > 1 And _

Range(Зависимая).Columns.Count > 1) Or

(Range(Независимая).Columns.Count > 1 And _

Range(Зависимая).Rows.Count > 1) Then

MsgBox "Независимая и Зависимая переменные должны располагаться " & Chr(13) & "либо в строках, либо в столбцах", vblnformation, "Линейная регрессия"

RefEditl.SetFocus

Exit Sub

End If

'

' Ввод на рабочий лист заголовков

'

Range("Cl").Value = "Отрезок=" Range("C2").Value = "Наклон="

Range("C3").Value = "R=" '

' Расчет коэффициентов линии тренда ' и коэффициента корреляции

Range("D1'") .FormulaLocal = "=OTPE30K(" & Зависимая & ";" & Независимая & ")"

Range("D2").FormulaLocal = "=НАКЛОН(" & Зависимая & ";" & Независимая & ")"

Range("D3") .FormulaLocal = "=KOPPEЛ(" & Зависимая & ";" & Независимая & ")" '

b = Range("Dl").Value m = Range("D2").Value Корреляция = Range("D3").Value

'

' Вывод данных в диалоговое окно

'

TextBoxl.Text = CStr(b) TextBox2.Text = CStr(m)

TextBox3.Text = CStr(Корреляция)

'

' Построение диаграммы по двум диапазонам: Независимая и Зависимая

'

Set НезависимаяЗависимая = _

Application.Union(Range(Независимая) , Range(Зависимая)) Диаграмма НезависимаяЗависимая

End Sub

'

Sub ТрендСПовторениями()

'

Dim ИмяЛиста As String

Dim Ячейка As Object

Dimx(), y(), Nxy(), Nx(), Ny() As Double

Dim i, j, k, p, N_x, N_y, Nобщая As Integer '

Независимая = RefEditl.Value '

If Range(Независимая).Columns.Count > 1 Then

MsgBox "Данные для независимой переменной" & Chr(13) & "должны располагаться в одном столбце", vblnformation, "Линейная регрессия"

Exit Sub

End If

'

For Each Ячейка In Range(Независимая).Cells

If IsNumeric(Ячейка.Value) = False Then

MsgBox "В ячейках данных для независимой" & Chr(13) & _

"переменной должны быть только числа", vblnformation, "Линейная регрессия"

Exit Sub

End If

Next Ячейка

'

Зависимая = RefEdit2.Value

'

If Range(Зависимая).Rows.Count > 1 Then

MsgBox "Данные для независимой переменной" & Chr(13) & "должны располагаться в одной строке", vblnformation, "Линейная регрессия"

Exit Sub

End If '

For Each Ячейка In Range(Зависимая).Cells

If IsNumeric(Ячейка.Value) = False Then

MsgBox "В ячейках данных для зависимой" & Chr(13) & "переменной должны быть только числа", vblnformation, "Линейная регрессия"

Exit Sub

End If

Next Ячейка

Повторения = RefEdit3.Value

'

'

N_x = Range(Повторения).Rows.Count

N_y = Range(Повторения).Columns.Count

'

' N_x - число различных реализаций независимой переменной

' N у - число различных реализаций зависимой переменной

If Range(Независимая).Columns.Count = N_x And _

Range(Зависимая).Rows.Count = N_y Then

MsgBox "Размеры таблицы повторений должны быть" & Chr(13) & "согласованы с диапазонами данных наблюдаемых величин ", vblnformation, "Линейная регрессия"

Exit Sub

End If

For Each Ячейка In Range(Повторения).Cells

If IsNumeric(Ячейка.Value) - False Then

MsgBox "В ячейках данных таблицы повторений" & Chr(13) & "переменной должны быть только числа", vblnformation, "Линейная регрессия"

Exit Sub

End If

Next Ячейка

ReDim Nxy(1 To N_x, 1 To N_y) , Nx(l To N_x) , Ny(l To N_y) ,

x(l To N_x), y(1 To N_y) '

For i = 1 To N_x

For j = 1 To N_y

Nxy(i, j) = Range(Повторения).Cells(i, j).Value

Next j

Next i '

For i = 1 To N_x

Nx(i) =0

For j = 1 To N_y

Nx(i) = Nx(i) + Nxy(i, j)

Next j

Range(Повторения).Cells(i, N_y).Select

Selection.Offset(0, 1).Value = Nx(i)

Next i

' Nx(i) - число повторений i-го значения независимой переменной '

Nобщая = 0

For i = 1 То N_x

Ыобщая = Ыобщая + Nx(i)

Next i

'

' Ыобщая - число наблюдений

For j = 1 То N_y

Ny(j) = 0

For i = 1 To N_x

Ny(j) = Ny(j) + Nxy(i, j)

Next i

Range(Повторения).Cells(N_x, j).Select

Selection.Offset(1, 0).Value = Ny(j)

Next j

'

' Ny(j) - число повторений i-го значения зависимой переменной

'

Range(Повторения).Cells(N_x, N_y).Select

Selection. Offset (1, 1) .Value = Nобщая

'

' x(i) - i-e значение независимой переменной

'

For i = 1 To N_x

x(i) = Range(Независимая).Cells(i).Value

Next i

'

' y(i) - i-e значение зависимой переменной

For i = 1 To N_y

y(i) = Range(Зависимая).Cells(i).Value

Next i

' Записывание значений зависимой и независимой переменной ' в два столбца с учетом повторений

'

Р = 1

For i = 1 То N_x

For j = 1 То N_y

If Nxy(i, j) <> 0 Then

For k = 1 To Nxy(i, j)

Cells(p, 100).Value = x(i)

Cells(p, 101).Value = y(j) P = p + 1

Next k

End If

Next j

Next i

'

Независимая = "R1C100:R" & CStr(p - 1) & "C100" Зависимая.= "R1C101:R" & CStr(p - 1) & "C101"

'

'

' Расчет коэффициентов линии тренда

' и коэффициента корреляции

'

Cells (1, 102).FormulaLocal =

"=ОТРЕЗОК(" & Зависимая & ";" & Независимая & ")" Cells (2, 102).FormulaLocal =

"=НАКЛОН(" & Зависимая & ";" & Независимая & ")" Cells(3, 102).FormulaLocal =

"=КОРРЕЛ(" & Зависимая & ";" & Независимая & ")" '

b = Cells(1, 102).Value

m = Cells(2, 102).Value Корреляция = Cells(3, 102).Value

'

TextBoxl.Text = CStr(b)

TextBox2.Text = CStr(m) TextBox3.Text = CStr(Корреляция)

'

' Построение диаграммы Диаграмма Range(Cells(1, 100), Cells(p - 1, 101))

'

End Sub

Sub Диаграмма(Диапазон As Object)

'

' Построение диаграммы по диапазону

'

ActiveSheet.ChartObjects.Delete

ActiveSheet.ChartObjects.Add(150, 49.25, 259.5, 169.5).Select

Application.CutCopyMode = False

ActiveChart.ChartWizard Source:=Диапазон, Gallery:=xlXYScatter, Format:=1,

PlotBy:=xlColumns, CategoryLabels:=l, SeriesLabels:=0, HasLegend:=False,

Title:="", CategoryTitle:="",

ValueTitle:="", ExtraTitle:=""

'

' Добавление в диаграмму линии тренда

'

ActiveSheet.ChartObjects(1).Activate

ActiveChart.SeriesCollection(1).Select

ActiveChart.SeriesCollection(1)

.Trendlines.Add(Type:=xlLinear,

Forward:=0, Backward:=0, DisplayEquation:=True,

DisplayRSquared:=True).Select

'

End Sub

Самостоятельное задание

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

НОВОСТИ ФОРУМА Рыцари теории эфира

10.11.2021 - 12:37: ПЕРСОНАЛИИ - Personalias -> WHO IS WHO - КТО ЕСТЬ КТО - Карим_Хайдаров. 10.11.2021 - 12:36: СОВЕСТЬ - Conscience -> РАСЧЕЛОВЕЧИВАНИЕ ЧЕЛОВЕКА. КОМУ ЭТО НАДО? - Карим_Хайдаров. 10.11.2021 - 12:36: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от д.м.н. Александра Алексеевича Редько - Карим_Хайдаров. 10.11.2021 - 12:35: ЭКОЛОГИЯ - Ecology -> Биологическая безопасность населения - Карим_Хайдаров. 10.11.2021 - 12:34: ВОЙНА, ПОЛИТИКА И НАУКА - War, Politics and Science -> Проблема государственного терроризма - Карим_Хайдаров. 10.11.2021 - 12:34: ВОЙНА, ПОЛИТИКА И НАУКА - War, Politics and Science -> ПРАВОСУДИЯ.НЕТ - Карим_Хайдаров. 10.11.2021 - 12:34: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Вадима Глогера, США - Карим_Хайдаров. 10.11.2021 - 09:18: НОВЫЕ ТЕХНОЛОГИИ - New Technologies -> Волновая генетика Петра Гаряева, 5G-контроль и управление - Карим_Хайдаров. 10.11.2021 - 09:18: ЭКОЛОГИЯ - Ecology -> ЭКОЛОГИЯ ДЛЯ ВСЕХ - Карим_Хайдаров. 10.11.2021 - 09:16: ЭКОЛОГИЯ - Ecology -> ПРОБЛЕМЫ МЕДИЦИНЫ - Карим_Хайдаров. 10.11.2021 - 09:15: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Екатерины Коваленко - Карим_Хайдаров. 10.11.2021 - 09:13: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Вильгельма Варкентина - Карим_Хайдаров.