Сапа
Здесь может быть ваша реклама
|

 Урок 12. Тема: Линия тренда

Урок 12. Тема: Линия тренда

Цель урока

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

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

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

Теория

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

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

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

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

         
 

Температура

Продажа, шт.

23

46

24

49

25

50

26

52

28

52

30

55

 
         

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

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

Синтаксис:

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

Аргументы:

    
 

известные значения у

Массив известных значений зависимой наблюдаемой величины

 
 

известные значения х

Массив известных значений независимой наблюдаемой величины. Если аргумент известные значения х опущен, то предполагается, что этот массив {1; 2; 3; ...} такого же размера, как и известные значения у

 
    

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

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

 46484950525255
2373     
24       
25       
26       
27  3    
28 2 1 1 
29  1212 
30    132

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

Практика

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

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

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

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

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

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

    
 

UserForm Initialize

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

Нажатие кнопки ок запускает на выполнение процедуру

CommandButtonl Click

При выборе переключателя с повторениями производит расчет по процедуре трендсповто-рениями (вторая задача), а при выборе переключателя Без повторений - по процедуре Обыч-ныйтренд (первая задача).

 
 

Нажатие кнопки выход запускает на выполнение процедуру

CommandButton2 Click

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

 
 

OptionButton1_Click

и

OptionButton2_Click

Обеспечивает скрытие и отображение в диалоговом окне надписи повторения и соответствующего поля.

 
 

ОбычныйТренд

  1. В поля Независимая величина (в данном Случае Температура) и Зависимая величина (объем продаж) вводит ссылки на диапазоны, по которым строится линия тренда. Исходя из рис. У 12.3 В Поле Независимая величина вводится ссылка на диапазон А2:А7, а в поле Зависимая величина — В2 : В7 .
  2. Проверяет, располагаются ли данные только в столбцах, либо только в строках. Также проверяет, располагаются ли данные в столбцах с или D (эти два столбца зарезервированы программой для вывода результатов расчета). Если располагаются, то отображается соответствующее сообщение.
  3. На рабочий лист вводятся функции рабочего листа НАКЛОН, ОТРЕЗОК и КОРРЕЛ, по которым вычисляются параметры линии тренда и коэффициент корреляции.
  4. При помощи процедуры диаграмма строится диаграмма и линия тренда.



 
    

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

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

    
 

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

В поля Независимая величина (в данном случае температура) и зависимая величина (объем продаж) вводит ссылки на диапазоны, по которым строится линия тренда. Исходя из рис. У 12. 4 в поле Независимая величина вводится ссылка на диапазон А2:А9, в поле Зависимая величина — В1:Н1, 3 в поле Повторения — В2:Н9

В остальном процедура действует аналогично процедуре обычныйТренд за исключением того, что перед вычислением параметров уравнения тренда она:

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

Диаграмма

Строит диаграмму и линию тренда по диапазону, заданному в аргументе диапазон.

 
    

 

Visual Basic for Applications (VBA), программы, электронные книги, раскрутка, оптимизация

Рис. У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

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

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

 
MKPortal©2003-2008 mkportal.it
MultiBoard ©2007-2009 RusMKPortal