Жұмыстың орындау әдістемесі

Екі байқалатын шама х пен у бар, мысалы, қолданыста болған автомобильдерді сататын фирманың өткеру көлемі, оның алты аптадағы жұмысы. Бұл байқалатын шамалардың мәндері 44-суретте келтірілген, онда х – есепті апта, ал у – сол аптада өткерілу көлемі. Бастапқы мәліметтері 1 суретте келтірілген.

44-сурет – Желілік модель салу үшін бастапқы мәліметтер.

Байқалатын мәндерді барынша жақсы көрсететін желілік моделін салу қажет. Әдетте m мен b байқалатын және тәуелді өзгермелі у теориялық мәнінің арасындағы айырмашылық текшелерінің сомасы минимальды болатындай етіп іріктеледі, яғни минималдандырылғанда:

мұнда n – бақылау саны (мына жағдайда n=6).

Бұл есепті шешу үшін m мен b өзгермелілеріне тиісінше D3 және E3 ұяларын бөлеміз,ал F3 ұясына минималдандырылған функцияны енгіземіз

{=СУММКВРАЗН(B2:B7;E3+D3*A2:A7)}.

СУММКВРАЗН функциясы көрсетілген массивтердің элементтері үшін айырмашылық текшелерінің сомасын есептейді.

Енді Сервис, Шешімді іздеу командаларын тереміз, ашылған Шешімді іздеу диалогты терезесін 45-суретте көрсетілгендей толтырамыз.

m мен b өзгермелілеріне шектеудің жоқ екенін атап өтеміз. Есептеу нәтижесінде іздеу құралы шешімді табады: m=1,88571 және b=5,400. Бұл нәтижелер 46-суретте келтірілген.

45-сурет – Регрессия теңдігін есептеу үшін шешімді Іздеу диалогты терезесі

46-сурет – Регрессия теңдігінің ұтымды шешімі.

Бұл бөлімде регрессияның желілік теңдеуінің түрлі сипаттарын тікелей есептеген жұмыс парағының функциялары келтірілді.

Алдыңғы бөлімдегі у=mx+b желілік моделінің m мен b параметрлерін НАКЛОН және ОТРЕЗОК функцияларының көмегімен анықтауға болады.

НАКЛОН – ұзындық бойындағы мәндердің өзгеру жылдамдығы. НАКЛОН функциясы желілік тренд еңісінің коэффициентін анықтайды. Синтаксис:

НАКЛОН (белгілі_мәндер_у; белгілі_мәндер_x).

ОТРЕЗОК (INTERCEPT) функциясы желілік тренд желісінің ординат кіндігімен қиылысқан нүктесін анықтайды. Синтаксис:

ОТРЕЗОК (белгілі_мәндер_х; белгілі_мәндер_у).

НАКЛОН мен ОТРЕЗОК функцияларының дәлелдері:

- белгілі_мәндер_у – тәуелді бақылаудағы шаманың белгілі мәндерінің массиві;

- белгілі_мәндер_x – тәуелсіз бақылаудағы шаманың белгілі мәндерінің массиві. Егер белгілі_мәндер_x дәлелі түсіп қалса, онда массив {1;2;3;...} белгілі_мәндер_у дәлеліндей мөлшерде деп топшыланады.

- НАКЛОН мен ОТРЕЗОК функциялары мына формулаларымен есептеледі:

,

мұнда ,

4 суретте көрсетілген D2 мен Е2 ұяларында тиісінше m мен b табылды, формулалары:

=НАКЛОН(В2:В7;А2:А7);

=ОТРЕЗОК(В2:В7;А2:А7).

m мен b коэффиценттерін басқа тәсілмен де табуға болады. А2:В7 ұяларының диапазоны бойынша нүктелі кескіндеме салыңыз, қосарлы шертпемен кескіндеме бөліңіз, содан соң мышьтың оң жақ кнопкасымен шертіңіз. Ашылған контексттік менюде 47-суретте көрсетілгенде тренд желесі командасын таңдаңыз.

47-сурет- Тренд желісін салудың басы.

Тренд желісін салу тобында Тип вкладында тренд Желісідиалогтық терезесінде (аппроксимация мен сглаживание) Желілік параметрін таңдаңыз, ал Параметрлер вкладына диаграммада теңдеуді Көрсету және аппроксимация шынайлығы шамасы диаграммасына Орналастыру (R^2) жалаушыларын қойыңыз (яғни диаграммаға корреляция коэффиценті текшесінің мәнін орналастыру қажет).

Корреляция коэффициентіне қарап, регрессияның желілік теңдеуін пайдаланудың дұрыстығын пайымдауға болады. Егер ол 0,9-дан 1-ге дейінгі диапазонда жатса, бұл тәуелділікті нәтижені алдын ала болжау үшін пайдалануға болады. Корреляция коэффициенті бірлікке тақаған сайын оның бақылаудағы шамалар арасындағы желілік тәуелдікті көрсететінін негіздей түседі. Корреляция коэффициенті -1-ге тақау болса, бақылаудағы шамалар арасында кері тәуелділіктің болғаны.

Нүкте белгілі болған жағдайда ғана қисық сызықпен Ү кескіндігін қиылысқан нүктесіне жалауша қойылады. Мысалы, жалауша қойылатын, оның өрісін 0 енгізілсе, y=mx моделі іздестірілуде деген сөз.

Тренд желісі командасының орындалу нәтижесі 48-суретте келтірілген

48-сурет – Тренд желісінің кескіндемесі

Суреттен көрініп отырғандай, коррелация коэффициентінің текшесі 0,9723-ке тең, демек желілік модель нәтижелерді алдын ала айту үшін қолдынылады.

Регрессия теңдеуінің табылған коэффициенттерінің негізінде бақылаудағы y шамасының теориялық мәнін анықтауға болады. 48-суретте көрсетілген С2 ұясында y теориялық мәнін

=$D$2*A2+$E$2

формуласы бойынша А2- ден x кезінде есептеп шығарамыз.

Алайда тіркелген нүктеде y теориялық мәнін желілік модель коэффициенттерін алдын ала анықтамай-ақ ПРЕДСКАЗ функциясының көмегімен есептеп шығаруға болады.

Синтаксис:

ПРЕДСКАЗ (t; белгілі_мәндер_y; белгілі_ мәндер_x).

Дәлелдер:

t – мәні алдын ала айтылатын мәләметтер нүктесі;

белгілі_мәндер_y – тәуелді бақылаудағы шаманың белгілі мәндерінің массиві;

белгілі_мәндер_x – тәуелсіз бақылаудағы шаманың белгілі мәндерінің массиві; Егер дәлел белгілі_мәндер_x түсіп қалса, бұл массивтің {1;2; 3;..}мөлшері белгілі_мәндер_y массивінікіндей деп болжанады.

46-суретте С2 ұяда теориялық мәнді мына формуламен шығаруға болады:

=ПРЕДСКАЗ (A2;$B$2:$B$7;$A$2:$A$7)

ТЕНДЕНЦИЯ функциясының регрессиясы бір өлшемді, сондай-ақ көр өлшемді деңгейі үшін тәуелсіз өзгермелерінің тұтас ауқымы үшін жиілік регрессиясы теңдеуінің мәндерін есептеп шығарады. Регрессияның көп өлшемді желілік моделінің түрі:

Синтаксис:

ТЕНДЕНЦИЯ (белгілі_мәндер_y; белгілі_мәндер_x; жаңа_мәндер_x; конст)

Дәлелдер:

- белгілі_мәндер_y – тәуелді бақыланатын шаманың белгілі мәндерінің массиві;

- белгілі_мәндер_x – тәуелсіз бақыланатын шаманың белгілі мәндерінің массиві; Егер дәлел белгілі_мәндер_x түсіп қалса, бұл массивтің {1;2;3;....} мөлшері белгілі_мәндер_y массивінікіндей деп болжанады.

- жаңа_мәндер_x – жаңа мәндер x, олар үшін тенденция функциясы тиісті мәндер y-ті қайтарады.

- конст- қисынды мән b константының 0-ге тең болуының қажетінің бар- жоғын көрсетеді. Егер дәлел конст ИСТИНА мәнінде болса немесе түсіп қалса, онда b әдеттегідей есептеледі. Егер конст ЛОЖЬ мәнінде болса, онда b 0-ге тең деп болжанады.

Егер көп өлшемді желілік модель салынса, онда дәлелдер белгілі_мәндер_x пен жаңа_мәндер_x әрбір тәуелсіз өзгермелі үшін баған (немесе жол) ұстауы керек. Егер дәлел жаңа_мәндер_x түсіп қалса, онда ол дәлел белгілі_мәндер_x-пен сәйкес келеді деп болжанады.

ЛИНЕЙН функциясы көп өлшемді желелік регрессия теңдігінің параметрлерінің мәндерінің массивін оралтады.

Синтаксис:

ЛИНЕЙН (белгілі_мәндер_y; белгілі_мәндер_x; конст; статистика).

Дәлелдер:

- белгілі_мәндер_y – тәуелді бақылаудағы шаманың белгілі мәндерінің массиві;

- белгілі_мәндер_x – тәуелсіз бақылаудағы шаманың белгілі мәндерінің массиві; Егер дәлел белгілі_мәндер_x түсіп қалса, бұл массивтің {1;2;3;....} мөлшері белгілі_мәндер_y-тегідей деп болжанады.

- жаңа_мәндер_x – жаңа мәндер x, олар үшін тенденция функциясы тиісті мәндер y-ті қайтарады.

- конст- қисындық мән конст b-ның 0-ге тең болуының қажетілігі- қажетсіздігін көрсетеді. Егер дәлел конст ИСТИНА мәнінде болса немесе түсіп қалса, онда b әдеттегідей есептеледі. Егер конст ЛОЖЬ мәнінде болса, онда b 0-ге тең деп болжанады.

- статистика- регрессия бойынша қосымша статистиканы, мысалы, корреляция коэффициентін шығарудың қажетілігі- қажетсіздігін көрсететін мән. Егер статистика ИСТИНА мәнінде болса, онда ЛИНЕЙН функциясы қосымша регрессиялық статистиканы оралтады. Егер дәлел статистика ЛОЖЬ мәнінде болса немесе түсіп қалса, ЛИНЕЙН функциясы коэффициенттердің мәнін ғана оралтады.

Бақылау сұрақтары:

1) Регрессиялық талдаудың қандай әдістері бар?

2) Регрессиялық талдауға қандай функциялар қатысады?

3) Тренд желілерінің мәні?

4) Қандай тренд желілерінің мәні