Eng kichik kvadratlar usuli (OLS) regressiya tahlili sohasiga tegishli. U ko'plab ilovalarga ega, chunki u berilgan funktsiyani boshqa soddaroqlari tomonidan taxminiy ko'rsatishga imkon beradi. LSM kuzatishlarni qayta ishlashda juda foydali bo'lishi mumkin va u tasodifiy xatolarni o'z ichiga olgan boshqalarning o'lchovlari natijalariga asoslangan ba'zi miqdorlarni baholash uchun faol foydalaniladi. Ushbu maqolada siz Excelda eng kichik kvadratlarni hisoblashni qanday amalga oshirishni o'rganasiz.

Muayyan misol yordamida muammoning bayoni

Aytaylik, ikkita X va Y ko'rsatkichlari mavjud. Bundan tashqari, Y X ga bog'liq. OLS bizni regressiya tahlili nuqtai nazaridan qiziqtirganligi sababli (Excelda uning usullari o'rnatilgan funksiyalar yordamida amalga oshiriladi), biz darhol ko'rib chiqishga o'tishimiz kerak. muayyan muammo.

Shunday qilib, X kvadrat metrda o'lchanadigan oziq-ovqat do'konining chakana savdo maydoni bo'lsin va Y millionlab rubllarda o'lchanadigan yillik aylanmasi bo'lsin.

Agar u yoki bu chakana savdo maydonchasi mavjud bo'lsa, do'kon qanday aylanma (Y) bo'lishini prognoz qilish talab qilinadi. Shubhasiz, Y = f (X) funktsiyasi ortib bormoqda, chunki gipermarket stendga qaraganda ko'proq tovarlar sotadi.

Bashorat qilish uchun ishlatiladigan dastlabki ma'lumotlarning to'g'riligi haqida bir necha so'z

Aytaylik, bizda n do'kon uchun ma'lumotlardan foydalangan holda tuzilgan jadval mavjud.

Matematik statistika ma'lumotlariga ko'ra, agar kamida 5-6 ob'ekt bo'yicha ma'lumotlar tekshirilsa, natijalar ko'proq yoki kamroq to'g'ri bo'ladi. Bundan tashqari, "anomal" natijalardan foydalanish mumkin emas. Xususan, elita kichik butik "masmarket" sinfidagi yirik savdo nuqtalarining aylanmasidan bir necha baravar ko'p aylanmaga ega bo'lishi mumkin.

Usulning mohiyati

Jadval ma'lumotlari M 1 (x 1, y 1), ... M n (x n, y n) nuqtalari ko'rinishida Dekart tekisligida tasvirlanishi mumkin. Endi masalaning yechimi M 1, M 2, .. M n nuqtalarga imkon qadar yaqin o'tuvchi grafigi y = f (x) ga yaqinlashtiruvchi funksiyani tanlashga keltiriladi.

Albatta, siz polinomdan foydalanishingiz mumkin yuqori daraja, lekin bu variantni amalga oshirish nafaqat qiyin, balki shunchaki noto'g'ri, chunki u aniqlanishi kerak bo'lgan asosiy tendentsiyani aks ettirmaydi. Eng oqilona yechim eksperimental ma’lumotlarga, aniqrog‘i, a va b koeffitsientlariga eng yaqin keladigan y = ax+b to‘g‘ri chiziqni izlashdir.

Aniqlikni baholash

Har qanday yaqinlashuv bilan uning to'g'riligini baholash alohida ahamiyatga ega. X i nuqta uchun funktsional va eksperimental qiymatlar o'rtasidagi farqni (og'ish) e i bilan belgilaymiz, ya'ni e i = y i - f (x i).

Shubhasiz, yaqinlashishning to'g'riligini baholash uchun siz og'ishlar yig'indisidan foydalanishingiz mumkin, ya'ni X ning Y ga bog'liqligini taxminiy ko'rsatish uchun to'g'ri chiziqni tanlashda siz eng kichik qiymatga ustunlik berishingiz kerak. ko'rib chiqilayotgan barcha nuqtalarda so'm e i. Biroq, hamma narsa juda oddiy emas, chunki ijobiy og'ishlar bilan bir qatorda salbiylar ham bo'ladi.

Muammoni og'ish modullari yoki ularning kvadratlari yordamida hal qilish mumkin. Oxirgi usul eng keng tarqalgan. U ko'plab sohalarda qo'llaniladi, jumladan regressiya tahlili (Excelda ikkita o'rnatilgan funksiyadan foydalangan holda amalga oshiriladi) va o'zining samaradorligini uzoq vaqt davomida isbotlagan.

Eng kichik kvadrat usuli

Ma'lumki, Excelda tanlangan diapazonda joylashgan barcha qiymatlarning qiymatlarini hisoblash imkonini beruvchi o'rnatilgan AutoSum funksiyasi mavjud. Shunday qilib, (e 1 2 + e 2 2 + e 3 2 + ... e n 2) ifodaning qiymatini hisoblashimizga hech narsa to'sqinlik qilmaydi.

Matematik belgilarda bu quyidagicha ko'rinadi:

Qaror dastlab to'g'ri chiziq yordamida taxminan qabul qilinganligi sababli, bizda:

Shunday qilib, X va Y miqdorlarning o'ziga xos bog'liqligini eng yaxshi tavsiflovchi to'g'ri chiziqni topish vazifasi ikkita o'zgaruvchining funktsiyasining minimalini hisoblashga to'g'ri keladi:

Buning uchun siz a va b yangi o'zgaruvchilarga nisbatan qisman hosilalarni nolga tenglashtirishingiz va ikkita noma'lum shaklga ega ikkita tenglamadan iborat ibtidoiy tizimni yechishingiz kerak:

Ba'zi oddiy o'zgarishlardan so'ng, shu jumladan 2 ga bo'lish va yig'indilarni manipulyatsiya qilish natijasida biz quyidagilarni olamiz:

Uni hal qilish, masalan, Kramer usulidan foydalanib, biz a * va b * koeffitsientlari bilan statsionar nuqtani olamiz. Bu minimal, ya'ni ma'lum bir hudud uchun do'kon qanday aylanmaga ega bo'lishini taxmin qilish uchun y = a * x + b * to'g'ri chiziq mos keladi, bu ko'rib chiqilayotgan misol uchun regressiya modelidir. Albatta, bu sizga aniq natijani topishga imkon bermaydi, lekin bu sizga ma'lum bir hududni do'kon kreditiga sotib olish o'z samarasini beradimi yoki yo'qmi haqida tasavvurga ega bo'lishga yordam beradi.

Excelda eng kichik kvadratlarni qanday amalga oshirish mumkin

Excel eng kichik kvadratlar yordamida qiymatlarni hisoblash funktsiyasiga ega. U quyidagi shaklga ega: “TREND” (maʼlum Y qiymatlari; maʼlum X qiymatlari; yangi X qiymatlari; doimiy). Excelda OLS ni hisoblash formulasini jadvalimizga qo'llaymiz.

Buning uchun Excelda eng kichik kvadratlar usuli yordamida hisoblash natijasi ko'rsatiladigan katakka “=” belgisini kiriting va “TREND” funksiyasini tanlang. Ochilgan oynada tegishli maydonlarni to'ldiring, ta'kidlang:

• Y uchun ma'lum qiymatlar diapazoni (bu holda, savdo aylanmasi bo'yicha ma'lumotlar);
• diapazon x 1 , …x n , ya'ni chakana savdo maydoni hajmi;
• x ning ma'lum va noma'lum qiymatlari, buning uchun siz aylanma hajmini bilib olishingiz kerak (ularning ish varag'idagi joylashuvi haqida ma'lumot olish uchun pastga qarang).

Bundan tashqari, formulada "Const" mantiqiy o'zgaruvchisi mavjud. Agar siz tegishli maydonga 1 ni kiritsangiz, bu siz b = 0 deb hisoblab, hisob-kitoblarni bajarishingiz kerakligini anglatadi.

Agar siz bir nechta x qiymatlari uchun prognozni bilishingiz kerak bo'lsa, formulani kiritgandan so'ng siz "Enter" tugmasini bosmasligingiz kerak, lekin klaviaturada "Shift" + "Control" + "Enter" kombinatsiyasini kiritishingiz kerak.

Ba'zi xususiyatlar

Regressiya tahlili hatto qo'g'irchoqlar uchun ham mavjud. Noma'lum o'zgaruvchilar massivi qiymatini bashorat qilish uchun Excel formulasi - TREND - hatto eng kichik kvadratlar haqida hech qachon eshitmaganlar ham foydalanishi mumkin. Uning ishining ba'zi xususiyatlarini bilish kifoya. Ayniqsa:

• Agar siz y o'zgaruvchisining ma'lum qiymatlari oralig'ini bitta satr yoki ustunga joylashtirsangiz, u holda ma'lum x qiymatlari bo'lgan har bir satr (ustun) dastur tomonidan alohida o'zgaruvchi sifatida qabul qilinadi.
• Agar TREND oynasida ma'lum x bo'lgan diapazon ko'rsatilmagan bo'lsa, u holda Excelda funktsiyadan foydalanganda dastur uni butun sonlardan iborat massiv sifatida ko'rib chiqadi, ularning soni berilgan qiymatlari bilan diapazonga mos keladi. o'zgaruvchisi y.
• “Prognoz qilingan” qiymatlar massivini chiqarish uchun trendni hisoblash ifodasi massiv formulasi sifatida kiritilishi kerak.
• Agar x ning yangi qiymatlari belgilanmagan bo'lsa, TREND funktsiyasi ularni ma'lum bo'lganlarga teng deb hisoblaydi. Agar ular ko'rsatilmagan bo'lsa, u holda argument sifatida 1-massiv olinadi; 2; 3; 4;…, bu allaqachon belgilangan y parametrlari bilan diapazonga mos keladi.
• Yangi x qiymatlarini o'z ichiga olgan diapazon berilgan y qiymatlarini o'z ichiga olgan diapazon bilan bir xil yoki bir nechta satr yoki ustunlarga ega bo'lishi kerak. Boshqacha qilib aytganda, u mustaqil o'zgaruvchilarga mutanosib bo'lishi kerak.
• X qiymatlari ma'lum bo'lgan massiv bir nechta o'zgaruvchilarni o'z ichiga olishi mumkin. Ammo, agar biz faqat bittasi haqida gapiradigan bo'lsak, u holda berilgan x va y qiymatlari bo'lgan diapazonlar proportsional bo'lishi kerak. Bir nechta o'zgaruvchilar bo'lsa, berilgan y qiymatlari bo'lgan diapazon bitta ustun yoki bitta qatorga to'g'ri kelishi kerak.

PREDICTION funksiyasi

Excelda regressiya tahlili bir nechta funksiyalar yordamida amalga oshiriladi. Ulardan biri "BASHOROT" deb ataladi. U "TREND" ga o'xshaydi, ya'ni eng kichik kvadratlar usuli yordamida hisob-kitoblar natijasini beradi. Biroq, faqat bitta X uchun, Y qiymati noma'lum.

Endi siz Excel-da ma'lum bir indikatorning kelajakdagi qiymatini chiziqli tendentsiya bo'yicha taxmin qilish imkonini beruvchi qo'g'irchoqlar uchun formulalarni bilasiz.

Eng kichik kvadratlar usuli - bu ikki qator raqamlar to'plamiga eng to'g'ri mos keladigan chiziqli tenglamani qurishning matematik protsedurasi. Ushbu usuldan foydalanishdan maqsad umumiy kvadrat xatosini minimallashtirishdir. Excelda ushbu usulni hisob-kitoblaringizda qo'llashga yordam beradigan vositalar mavjud. Keling, bu qanday amalga oshirilganligini aniqlaylik.

· Excelda usuldan foydalanish

o "Yechimlarni qidirish" qo'shimchasini yoqish

o Muammoli sharoitlar

o Yechim

Excelda usuldan foydalanish

Eng kichik kvadratlar usuli (LSM) bir o'zgaruvchining boshqasiga bog'liqligini matematik tavsiflashdir. U prognoz qilish uchun ishlatilishi mumkin.

“Find Solution” qo‘shimchasini yoqish

Excelda MNC dan foydalanish uchun siz plaginni yoqishingiz kerak "Yechim topish", bu sukut bo'yicha o'chirilgan.

1. Yorliqga o'ting "Fayl".

2. Bo'lim nomini bosing "Tanlovlar".

3. Ochilgan oynada kichik bo'limni tanlang "Qo'shimchalar".

4. Blokda "Boshqaruv", oynaning pastki qismida joylashgan, kalitni holatiga o'rnating "Excel qo'shimchalari"(agar u boshqa qiymatga ega bo'lsa) va tugmani bosing — Boring....

5. Kichik oyna ochiladi. Parametrning yoniga belgi qo'yamiz "Yechim topish". Tugmani bosing "KELISHDIKMI".

Endi funksiya Yechim topish Excelda faollashtiriladi va uning vositalari lentada paydo bo'ladi.

Dars: Excelda yechim topish

Muammoning shartlari

Keling, ma'lum bir misol yordamida LSM dan foydalanishni tasvirlaylik. Bizda ikkita qator raqamlar mavjud x Va y, ularning ketma-ketligi quyidagi rasmda ko'rsatilgan.

Ushbu bog'liqlikni eng aniq funktsiya bilan tavsiflash mumkin:

Shu bilan birga, ma'lumki, qachon x=0 y ham teng 0 . Shuning uchun bu tenglamani bog'liqlik bilan tasvirlash mumkin y=nx.

Biz farq kvadratlarining minimal yig'indisini topishimiz kerak.

Yechim

Keling, usulning bevosita qo'llanilishining tavsifiga o'tamiz.

1. Birinchi qiymatning chap tomonida x raqam qo'ying 1 . Bu birinchi koeffitsient qiymatining taxminiy qiymati bo'ladi n.

2. Ustunning o'ng tomonida y boshqa ustun qo'shing - nx. Ushbu ustunning birinchi katakchasiga koeffitsientni ko'paytirish formulasini yozamiz n birinchi o'zgaruvchining har bir hujayrasi uchun x. Shu bilan birga, biz mutlaq koeffitsient bilan maydonga havola qilamiz, chunki bu qiymat o'zgarmaydi. Tugmani bosing Kirish.

3. To'ldirish belgisidan foydalanib, ushbu formulani quyidagi ustundagi jadvalning butun diapazoniga ko'chiring.

4. Alohida katakchada qiymatlar kvadratlari orasidagi farqlar yig’indisini hisoblang y Va nx. Buning uchun tugmani bosing "Funktsiyani kiritish".

5. Ochilgan holda "Funksiya ustasi" kirishni qidirmoqda "SUMMKVARNA". Uni tanlang va tugmani bosing "KELISHDIKMI".

6. Argumentlar oynasi ochiladi. Dalada "x massivi" y. Dalada "massiv_y" ustun katakchalari diapazonini kiriting nx. Qiymatlarni kiritish uchun kursorni maydonga qo'ying va varaqdagi tegishli diapazonni tanlang. Kiritgandan so'ng tugmani bosing "KELISHDIKMI".

7. Yorliqga o'ting "Ma'lumotlar". Asboblar qutisidagi lentada "Tahlil" tugmasini bosing "Yechim topish".

8. Ushbu vosita uchun parametrlar oynasi ochiladi. Dalada "Maqsad funktsiyasini optimallashtirish" formula bilan hujayraning manzilini ko'rsating "SUMMKVARNA". Parametrda "oldin" kalitni holatiga o'rnatganingizga ishonch hosil qiling "Eng kam". Dalada "O'zgaruvchan hujayralar" koeffitsient qiymati bilan manzilni ko'rsating n. Tugmani bosing "Yechim toping".

9. Koeffitsient katagida yechim ko'rsatiladi n. Bu qiymat funktsiyaning eng kichik kvadrati bo'ladi. Agar natija foydalanuvchini qoniqtirsa, tugmani bosing "KELISHDIKMI" qo'shimcha oynada.

Ko'rib turganingizdek, eng kichik kvadratlar usulini qo'llash ancha murakkab matematik protseduradir. Biz buni oddiy misol yordamida amalda ko'rsatdik, ammo ancha murakkab holatlar mavjud. Biroq, Microsoft Excel vositalari hisob-kitoblarni iloji boricha soddalashtirish uchun mo'ljallangan.

http://multitest.semico.ru/mnk.htm

Umumiy holat

Mutlaq qiymatdagi raqam qanchalik kichik bo'lsa, tanlangan to'g'ri chiziq shunchalik yaxshi bo'ladi (2). To'g'ri chiziqni (2) tanlashning aniqligi xarakteristikasi sifatida biz kvadratlar yig'indisini olishimiz mumkin.

S uchun minimal shartlar bo'ladi

	(6)
	(7)

(6) va (7) tenglamalarni quyidagicha yozish mumkin:

	(8)
	(9)

(8) va (9) tenglamalardan xi va y i ning eksperimental qiymatlaridan a va b ni topish oson. (8) va (9) tenglamalar bilan aniqlangan (2) chiziq eng kichik kvadratlar usuli bilan olingan chiziq deb ataladi (bu nom S kvadratlar yig'indisi minimalga ega ekanligini ta'kidlaydi). (2) to'g'ri chiziq aniqlanadigan (8) va (9) tenglamalar normal tenglamalar deyiladi.

Oddiy tenglamalarni tuzishning oddiy va umumiy usulini ko'rsatishingiz mumkin. Tajriba nuqtalari (1) va tenglama (2) yordamida a va b uchun tenglamalar tizimini yozishimiz mumkin

y 1 =ax 1 +b,
y 2 =ax 2 +b, ...		(10)
y n = ax n + b,

Keling, bu tenglamalarning har birining chap va o'ng tomonlarini birinchi noma'lum a koeffitsientiga (ya'ni x 1, x 2, ..., x n ga) ko'paytiramiz va hosil bo'lgan tenglamalarni qo'shamiz, natijada birinchi normal tenglama (8) hosil bo'ladi. .

Keling, bu tenglamalarning har birining chap va o'ng tomonlarini ikkinchi noma'lum b koeffitsientiga ko'paytiramiz, ya'ni. 1 ga, va hosil bo'lgan tenglamalarni qo'shing, natijada ikkinchi normal tenglama (9) hosil bo'ladi.

Oddiy tenglamalarni olishning bu usuli umumiydir: u, masalan, funktsiya uchun mos keladi

doimiy qiymat mavjud va u eksperimental ma'lumotlardan aniqlanishi kerak (1).

k uchun tenglamalar tizimini yozish mumkin:

Eng kichik kvadratlar usuli yordamida (2) to‘g‘ri chiziqni toping.

Yechim. Biz topamiz:

X i =21, y i =46,3, x i 2 =91, x i y i =179,1.

(8) va (9)91a+21b=179,1 tenglamalarni yozamiz,

21a+6b=46,3, bu yerdan topamiz
a=0,98 b=4,3.

Eng kichik kvadrat usuli regressiya tenglamasining parametrlarini baholash uchun ishlatiladi.

Xususiyatlar o'rtasidagi stokastik munosabatlarni o'rganish usullaridan biri bu regressiya tahlilidir.
Regressiya tahlili - bu regressiya tenglamasining hosilasi bo'lib, uning yordamida boshqa (yoki boshqa) o'zgaruvchilarning (omil-atributlarning) qiymati ma'lum bo'lsa, tasodifiy o'zgaruvchining (natija atributining) o'rtacha qiymati topiladi. U quyidagi bosqichlarni o'z ichiga oladi:

1. ulanish shaklini tanlash (analitik regressiya tenglamasining turi);
2. tenglama parametrlarini baholash;
3. analitik regressiya tenglamasining sifatini baholash.

Ko'pincha, chiziqli shakl xususiyatlarning statistik munosabatlarini tavsiflash uchun ishlatiladi. Chiziqli munosabatlarga e'tibor uning parametrlarining aniq iqtisodiy talqini, o'zgaruvchilarning cheklangan o'zgarishi va ko'p hollarda munosabatlarning nochiziq shakllari (logarifm yoki o'zgaruvchilarni almashtirish orqali) hisob-kitoblarni amalga oshirish uchun chiziqli shaklga aylantirilishi bilan izohlanadi. .
Chiziqli juftlik munosabatlarida regressiya tenglamasi quyidagi ko rinishda bo ladi: y i =a+b·x i +u i . Bu tenglamaning a va b parametrlari x va y statistik kuzatish ma’lumotlari asosida baholanadi. Bunday baholashning natijasi tenglama bo'ladi: , bu erda , a va b parametrlarining taxminlari , regressiya tenglamasidan olingan natijaviy atributning (o'zgaruvchining) qiymati (hisoblangan qiymat).

Ko'pincha parametrlarni baholash uchun ishlatiladi Eng kichik kvadratlar usuli (LSM).
Eng kichik kvadratlar usuli regressiya tenglamasining parametrlarini eng yaxshi (barqaror, samarali va xolis) baholashni ta'minlaydi. Ammo tasodifiy atama (u) va mustaqil o'zgaruvchi (x) bo'yicha ma'lum taxminlar bajarilgan taqdirdagina (OLS taxminlariga qarang).

Eng kichik kvadratlar usuli yordamida chiziqli juftlik tenglama parametrlarini baholash masalasi quyidagicha: parametrlarning bunday baholarini olish uchun , natijaviy xarakteristikaning haqiqiy qiymatlarining kvadratik og'ishlarining yig'indisi - y i hisoblangan qiymatlardan minimal bo'ladi.
Rasmiy ravishda OLS mezoni shunday yozilishi mumkin: .

Eng kichik kvadratlar usullarini tasniflash

1. Eng kichik kvadrat usuli.
2. Maksimal ehtimollik usuli (oddiy klassik chiziqli regressiya modeli uchun regressiya qoldiqlarining normalligi taxmin qilingan).
3. Umumlashtirilgan eng kichik kvadratlar OLS usuli xatolar avtokorrelyatsiyasi va geteroskedastizm holatlarida qo'llaniladi.
4. Og'irlangan eng kichik kvadratlar usuli (heteroskedastik qoldiqlarga ega OLSning alohida holati).

Keling, fikrni tushuntirib beraylik Grafik jihatdan klassik eng kichik kvadratlar usuli. Buning uchun to‘g‘ri burchakli koordinatalar sistemasida kuzatuv ma’lumotlari (x i, y i, i=1;n) asosida tarqalish grafigini quramiz (bunday tarqalish grafigi korrelyatsiya maydoni deb ataladi). Keling, korrelyatsiya maydonining nuqtalariga eng yaqin bo'lgan to'g'ri chiziqni tanlashga harakat qilaylik. Eng kichik kvadratlar usuliga ko'ra, chiziq korrelyatsiya maydonining nuqtalari va bu chiziq orasidagi vertikal masofalar kvadratlari yig'indisi minimal bo'lishi uchun tanlanadi.

Ushbu muammo uchun matematik belgilar: .
y i va x i =1...n qiymatlari bizga ma’lum, bular kuzatish ma’lumotlari. S funktsiyasida ular doimiylarni ifodalaydi. Ushbu funktsiyadagi o'zgaruvchilar parametrlarning kerakli baholari - , . Ikki o'zgaruvchining funktsiyasining minimalini topish uchun har bir parametr uchun ushbu funktsiyaning qisman hosilalarini hisoblash va ularni nolga tenglashtirish kerak, ya'ni. .
Natijada biz ikkita oddiy chiziqli tenglamalar tizimini olamiz:
Ushbu tizimni yechish orqali biz kerakli parametr baholarini topamiz:

Regressiya tenglamasining parametrlarini hisoblashning to'g'riligini miqdorlarni solishtirish orqali tekshirish mumkin (hisob-kitoblarni yaxlitlash tufayli ba'zi nomuvofiqliklar bo'lishi mumkin).
Parametr baholarini hisoblash uchun siz 1-jadvalni tuzishingiz mumkin.
Regressiya koeffitsienti b belgisi munosabatlarning yo'nalishini ko'rsatadi (agar b >0 bo'lsa, bog'liqlik to'g'ridan-to'g'ri, agar b bo'lsa.<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Rasmiy ravishda, a parametrining qiymati x nolga teng bo'lgan y ning o'rtacha qiymatidir. Agar atribut-omil nol qiymatga ega bo'lmasa va bo'lolmasa, u holda a parametrining yuqoridagi talqini mantiqiy emas.

Xususiyatlar o'rtasidagi munosabatlarning yaqinligini baholash chiziqli juft korrelyatsiya koeffitsienti - r x,y yordamida amalga oshiriladi. Uni quyidagi formula yordamida hisoblash mumkin: . Bundan tashqari, chiziqli juft korrelyatsiya koeffitsienti regressiya koeffitsienti b orqali aniqlanishi mumkin: .
Chiziqli juft korrelyatsiya koeffitsientining qabul qilinadigan qiymatlari diapazoni -1 dan +1 gacha. Korrelyatsiya koeffitsientining belgisi munosabatlarning yo'nalishini ko'rsatadi. Agar r x, y >0 bo'lsa, u holda ulanish to'g'ridan-to'g'ri bo'ladi; agar r x, y bo'lsa<0, то связь обратная.
Agar bu koeffitsient kattalik bo'yicha birlikka yaqin bo'lsa, u holda xarakteristikalar orasidagi bog'liqlikni juda yaqin chiziqli deb talqin qilish mumkin. Agar uning moduli bitta ê r x, y ê =1 ga teng bo‘lsa, xarakteristikalar orasidagi bog‘lanish funksional chiziqli bo‘ladi. Agar x va y xususiyatlar chiziqli mustaqil bo'lsa, u holda r x,y 0 ga yaqin.
r x,y ni hisoblash uchun 1-jadvaldan ham foydalanish mumkin.

Olingan regressiya tenglamasining sifatini baholash uchun nazariy aniqlash koeffitsientini hisoblang - R 2 yx:

,
bu yerda d 2 - regressiya tenglamasi bilan izohlangan y ning dispersiyasi;
e 2 - y ning qoldiq (regressiya tenglamasi bilan izohlanmagan) dispersiyasi;
s 2 y - y ning umumiy (jami) dispersiyasi.
Determinatsiya koeffitsienti natijaviy atribut y ning regressiya (demak, x omil) bilan izohlanadigan oʻzgaruvchanlik (dispersiya) nisbatini y umumiy oʻzgaruvchanlikda (dispersiyada) tavsiflaydi. Aniqlash koeffitsienti R 2 yx 0 dan 1 gacha bo'lgan qiymatlarni oladi. Shunga ko'ra, 1-R 2 yx qiymati model va spetsifikatsiya xatolarida hisobga olinmagan boshqa omillar ta'siridan kelib chiqadigan y dispersiyaning ulushini tavsiflaydi.
Juftlangan chiziqli regressiya bilan R 2 yx =r 2 yx.

Eng kichik kvadratlar usuli (LSM)

n ta noma'lumli m chiziqli tenglamalar tizimi quyidagi ko'rinishga ega:

Uchta holat mumkin: m n. Oldingi paragraflarda m=n ko'rib chiqilgan holat. Qachon m

Agar m>n bo'lsa va sistema izchil bo'lsa, u holda A matritsada kamida m - n chiziqli bog'liq qatorlar mavjud. Bu erda yechimni n ta har qanday chiziqli mustaqil tenglamalarni tanlash (agar ular mavjud bo'lsa) va X = A -1 CV formulasini qo'llash, ya'ni masalani ilgari yechilgan tenglamaga qisqartirish orqali erishish mumkin. Bunday holda, olingan yechim har doim qolgan m - n tenglamalarni qanoatlantiradi.

Biroq, kompyuterdan foydalanishda umumiyroq yondashuv - eng kichik kvadratlar usulini qo'llash qulayroqdir.

Algebraik eng kichik kvadratlar usuli

Algebraik eng kichik kvadratlar usuli chiziqli tenglamalar tizimini yechish usulidir

Evklid normasini minimallashtirish orqali

Balta? b? >inf. (1.2)

Eksperimental ma'lumotlarni tahlil qilish

Keling, ba'zi bir tajribani ko'rib chiqaylik, bu vaqt davomida

Masalan, harorat Q(t) o'lchanadi. O'lchov natijalari massiv bilan belgilansin

Tajriba sharoitlari shunday deb faraz qilaylikki, o'lchovlar ma'lum xatolik bilan amalga oshiriladi. Bu hollarda haroratning Q(t) o’zgarishi qonuni ma’lum ko’phad yordamida izlanadi

P(t) = + + + ... +,

noma'lum koeffitsientlarni aniqlash, ..., qiymat E(, ...,), tenglik bilan aniqlanadi.

Gauss algebraik ekselga yaqinlashishi

minimal qiymatni oldi. Kvadratlar yig'indisi minimallashtirilganligi sababli, bu usul ma'lumotlarga eng kichik kvadratchalar yaqinlashishi deb ataladi.

Agar P(t) ni uning ifodasi bilan almashtirsak, olamiz

Qiymat minimal bo'lishi uchun massivni aniqlash vazifasini qo'yaylik, ya'ni. Eng kichik kvadratlar usuli yordamida massivni aniqlaymiz. Buning uchun qisman hosilalarni nolga tenglashtiramiz:

Agar siz m × n matritsani kiritsangiz A = (), i = 1, 2..., m; j = 1, 2, ..., n, bu erda

I = 1, 2..., m; j = 1, 2, ..., n,

keyin yozma tenglik shakl oladi

Yozma tenglikni matritsalar bilan amallar nuqtai nazaridan qayta yozamiz. Matritsani ustunga ko'paytirish ta'rifiga ko'ra, biz bor

Transpoze qilingan matritsa uchun shunga o'xshash munosabatlar shunday ko'rinadi

Belgilashni kiritamiz: Ax vektorining i-komponentini belgilaymiz Yozma matritsa tengliklariga muvofiq, biz ega bo'lamiz.

Matritsa shaklida bu tenglikni qayta yozish mumkin

A T x=A T B (1.3)

Bu erda A to'rtburchaklar m×n matritsadir. Bundan tashqari, ma'lumotlarni yaqinlashtirish masalalarida, qoida tariqasida, m > n. (1.3) tenglama normal tenglama deyiladi.

Eng boshidan evklid vektor normasidan foydalanib, masalani ekvivalent matritsa shaklida yozish mumkin edi:

Bizning maqsadimiz bu funktsiyani x da minimallashtirishdir. Yechim nuqtasida minimumga erishish uchun bu nuqtada x ga nisbatan birinchi hosilalar nolga teng bo'lishi kerak. Bu funktsiyaning hosilalari

2A T B + 2A T Ax

va shuning uchun yechim chiziqli tenglamalar tizimini qanoatlantirishi kerak

(A T A)x = (A T B).

Bu tenglamalar normal tenglamalar deyiladi. Agar A m× n matritsa bo‘lsa, A>A - n × n matritsa, ya’ni. Oddiy tenglamaning matritsasi har doim kvadrat simmetrik matritsadir. Bundan tashqari, u (A>Ax, x) = (Ax, Ax) ? 0.

Izoh. Ba'zan (1.3) ko'rinishdagi tenglamaning yechimi Ax = B sistemaning yechimi deb ataladi, bu erda A eng kichik kvadratlar usulidan foydalangan holda to'rtburchaklar m × n (m > n) matritsadir.

Eng kichik kvadratlar muammosi ma'lumotlar nuqtalaridan model egri chizig'igacha bo'lgan vertikal masofalarni minimallashtirish sifatida grafik tarzda talqin qilinishi mumkin (1.1-rasmga qarang). Bu fikr yaqinlashtirishdagi barcha xatolar kuzatishlardagi xatolarga mos keladi degan taxminga asoslanadi. Agar mustaqil o'zgaruvchilarda ham xatolar mavjud bo'lsa, u holda ma'lumotlardan modelgacha bo'lgan Evklid masofasini minimallashtirish to'g'riroq bo'lishi mumkin.

Excelda MNC

Excelda OLS ni amalga oshirish uchun quyida keltirilgan algoritm barcha dastlabki ma'lumotlar allaqachon ma'lum ekanligini taxmin qiladi. Chapdagi sistemaning AChX=B matritsa tenglamasining ikkala tomonini A T sistemaning ko‘chirilgan matritsasiga ko‘paytiramiz:

A T AX=A T B

Keyin chapdagi tenglamaning ikkala tomonini (A T A) -1 matritsaga ko'paytiramiz. Agar bu matritsa mavjud bo'lsa, u holda tizim aniqlanadi. Shuni hisobga olib

(A T A) -1 *(A T A)=E, olamiz

X=(A T A) -1 A T B.

Olingan matritsali tenglama m>n uchun n ta noma’lumli m chiziqli tenglamalar sistemasining yechimidir.

Yuqoridagi algoritmni qo'llashni aniq misol yordamida ko'rib chiqamiz.

Misol. Tizimni hal qilish uchun zarur bo'lsin

Excelda ushbu muammo uchun formulani ko'rsatish rejimida yechim varag'i quyidagicha ko'rinadi:

Hisoblash natijalari:

Kerakli vektor X E11:E12 oralig'ida joylashgan.

Berilgan chiziqli tenglamalar tizimini echishda quyidagi funktsiyalardan foydalanilgan:

1. MOBR - massivda saqlangan matritsa uchun teskari matritsani qaytaradi.

Sintaksis: MOBR (massiv).

Massiv - qator va ustunlar soni teng bo'lgan raqamli massiv.

2. MULTIPULT - matritsalar mahsulotini qaytaradi (matritsalar massivlarda saqlanadi). Natijada qatorlar soni 1-massiv bilan bir xil va ustunlar soni 2-massiv bilan bir xil bo'lgan massiv hosil bo'ladi.

Sintaksis: MULTIPLE(massiv 1, massiv 2).

Massiv1, massiv2 ko'paytiriladigan massivlardir.

Massiv diapazonining yuqori chap katakchasiga funktsiyani kiritgandan so'ng, formulani o'z ichiga olgan katakdan boshlab massivni tanlang, F2 tugmalarini bosing va keyin CTRL+SHIFT+ENTER tugmalarini bosing.

3. TRANSPORT - vertikal hujayralar to'plamini gorizontalga yoki aksincha o'zgartiradi. Bu funksiyadan foydalanish natijasida qatorlar soni asl massivning ustunlar soniga, ustunlar soni esa boshlang‘ich massiv qatorlari soniga teng bo‘lgan massiv paydo bo‘ladi.

4.1. O'rnatilgan funktsiyalardan foydalanish

Hisoblash regressiya koeffitsientlari funksiyasi yordamida amalga oshiriladi

LINEST(Qadriyatlar_y; x-qiymatlari; Const; statistika),

Qadriyatlar_y- y qiymatlar massivi,

x-qiymatlari- ixtiyoriy qiymatlar massivi x, agar massiv X tashlab qo‘yilgan bo‘lsa, bu massiv (1;2;3;...) bilan bir xil o‘lchamdagi massiv deb taxmin qilinadi. Qadriyatlar_y,

Const- konstanta kerak yoki yo'qligini ko'rsatadigan mantiqiy qiymat b 0 ga teng edi. Agar Const ma'noga ega TO'G'RI yoki o'tkazib yuborilgan, keyin b odatdagi usulda hisoblab chiqiladi. Agar argument bo'lsa Const demak, FALSE b 0 va qiymatlar deb qabul qilinadi a munosabat bajarilishi uchun tanlanadi y = ax.

Statistika qo'shimcha regressiya statistikasini qaytarish zarurligini ko'rsatadigan mantiqiy qiymatdir. Agar argument bo'lsa Statistika ma'noga ega TO'G'RI, keyin funksiya LINEST qo'shimcha regressiya statistikasini qaytaradi. Agar argument bo'lsa Statistika ma'noga ega YOLG'ON yoki tashlab qo'yilgan, keyin funksiya LINEST faqat koeffitsientni qaytaradi a va doimiy b.

Funktsiyalarning natijasi ekanligini unutmaslik kerak LINEST() qiymatlar to'plami - massiv.

Hisoblash uchun korrelyatsiya koeffitsienti funksiyasidan foydalaniladi

CORREL(Massiv 1;Massiv 2),

korrelyatsiya koeffitsientining qiymatlarini qaytarish, bu erda Massiv 1- qiymatlar massivi y, Massiv 2- qiymatlar massivi x. Massiv 1 Va Massiv 2 bir xil o'lchamda bo'lishi kerak.

MISOL 1. Giyohvandlik y(x) jadvalda keltirilgan. Qurmoq regressiya chizig'i va hisoblang korrelyatsiya koeffitsienti.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Keling, MS Excel varag'iga qiymatlar jadvalini kiritamiz va tarqalish sxemasini tuzamiz. Ish varag'i rasmda ko'rsatilgan shaklga ega bo'ladi. 2.

Regressiya koeffitsientlarining qiymatlarini hisoblash uchun A Va b hujayralarni tanlang A7:B7, Keling, funktsiya ustasiga va toifaga o'tamiz Statistik funksiyani tanlang LINEST. Keling, rasmda ko'rsatilgandek paydo bo'lgan dialog oynasini to'ldiramiz. 3 va bosing KELISHDIKMI.

Natijada, hisoblangan qiymat faqat katakchada paydo bo'ladi A6(4-rasm). Qiymat hujayrada paydo bo'lishi uchun B6 tahrirlash rejimiga kirishingiz kerak (kalit F2) ni bosing va keyin tugmalar birikmasini bosing CTRL+SHIFT+ENTER.

Yacheykadagi korrelyatsiya koeffitsientining qiymatini hisoblash C6 quyidagi formula kiritildi:

C7=CORREL(B3:J3;B2:J2).

Regressiya koeffitsientlarini bilish A Va b funksiya qiymatlarini hisoblaymiz y=bolta+b berilgan uchun x. Buning uchun biz formulani kiritamiz

B5=$A$7*B2+$B$7

va uni diapazonga nusxalash C5: J5(5-rasm).

Regressiya chizig‘ini diagrammada chizamiz. Grafikdagi tajriba nuqtalarini tanlang, o'ng tugmasini bosing va buyruqni tanlang Dastlabki ma'lumotlar. Ko'rsatilgan muloqot oynasida (5-rasm) yorliqni tanlang Qator va tugmani bosing Qo'shish. Keling, rasmda ko'rsatilganidek, kirish maydonlarini to'ldiramiz. 6 va tugmani bosing KELISHDIKMI. Eksperimental ma'lumotlar grafigiga regressiya chizig'i qo'shiladi. Odatiy bo'lib, uning grafigi tekislash chiziqlari bilan bog'lanmagan nuqtalar sifatida chiziladi.

Guruch. 6

Regressiya chizig'ining ko'rinishini o'zgartirish uchun quyidagi amallarni bajaring. Chiziqli grafik tasvirlangan nuqtalarni o'ng tugmasini bosing va buyruqni tanlang Diagramma turi va rasmda ko'rsatilganidek, tarqalish diagrammasining turini o'rnating. 7.

Chiziq turi, rangi va qalinligi quyidagicha o'zgartirilishi mumkin. Diagrammada chiziqni tanlang, o'ng tugmasini bosing va kontekst menyusidagi buyruqni tanlang Ma'lumotlar seriyasi formati... Keyin, masalan, rasmda ko'rsatilganidek, sozlamalarni o'rnating. 8.

Barcha transformatsiyalar natijasida biz bitta grafik maydonda eksperimental ma'lumotlarning grafigini va regressiya chizig'ini olamiz (9-rasm).

4.2. Trend chizig'idan foydalanish.

MS Excelda har xil taxminiy bog'liqliklarni qurish diagramma xususiyati sifatida amalga oshiriladi - trend chizig'i.

2-MISA. Tajriba natijasida ma'lum bir jadvalga bog'liqlik aniqlandi.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Taxminlovchi bog'liqlikni tanlang va tuzing. Jadval va tanlangan analitik bog‘liqliklarning grafiklarini tuzing.

Muammoni yechish quyidagi bosqichlarga bo'linishi mumkin: dastlabki ma'lumotlarni kiritish, tarqalish grafigini qurish va ushbu grafikga trend chizig'ini qo'shish.

Keling, ushbu jarayonni batafsil ko'rib chiqaylik. Dastlabki ma’lumotlarni ish varag‘iga kiritamiz va tajriba ma’lumotlarini chizamiz. Keyinchalik, grafikdagi tajriba nuqtalarini tanlang, o'ng tugmasini bosing va buyruqdan foydalaning Qo'shish l trend chizig'i(10-rasm).

Ko'rsatilgan dialog oynasi sizga yaqinlashuvchi bog'liqlikni yaratishga imkon beradi.

Ushbu oynaning birinchi yorlig'i (11-rasm) taxminiy bog'liqlik turini ko'rsatadi.

Ikkinchisida (12-rasm) qurilish parametrlari aniqlanadi:

· yaqinlashuvchi bog‘liqlikning nomi;

· tomonidan oldinga (orqaga) bashorat qilish n birliklar (bu parametr trend chizig'ini qancha birlik oldinga (orqaga) uzaytirish kerakligini aniqlaydi);

to'g'ri chiziq bilan egri chiziqning kesishish nuqtasini ko'rsatish kerakmi y=const;

· diagrammada yaqinlashuvchi funktsiyani ko'rsatish yoki ko'rsatmaslik (tenglamani diagrammada ko'rsatish varianti);

· diagrammada standart og'ish qiymatini joylashtirish yoki qo'ymaslik (diagrammada taxminiy ishonchlilik qiymatini joylashtirish varianti).

Ikkinchi darajali ko‘phadni yaqinlashuvchi bog‘liqlik sifatida tanlaymiz (11-rasm) va bu ko‘phadni tavsiflovchi tenglamani grafikda ko‘rsatamiz (12-rasm). Olingan diagramma rasmda ko'rsatilgan. 13.

Xuddi shunday foydalanish trend chiziqlari kabi bog'liqliklarning parametrlarini tanlashingiz mumkin

chiziqli y=a∙x+b,

logarifmik y=a∙ln(x)+b,

· eksponentsial y=a∙e b,

· tinchlantiruvchi y=a∙x b,

polinom y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d va hokazo, 6-darajali ko'phadga qadar,

· chiziqli filtrlash.

4.3. Erituvchi blokdan foydalanish

MS Excel dasturida hal qiluvchi blok yordamida eng kichik kvadratlar usuli yordamida parametrlarni tanlashning amalga oshirilishi katta qiziqish uyg'otadi. Bu texnika har qanday turdagi funksiya parametrlarini tanlash imkonini beradi. Keling, quyidagi masalani misol sifatida ishlatib, bu imkoniyatni ko'rib chiqaylik.

MISOL 3. Tajriba natijasida z(t) bog`liqligi olindi, jadvalda keltirilgan

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Bog'liqlik koeffitsientlarini tanlang Z(t)=4 +Bt 3 +Ct 2 +Dt+K da eng kichik kvadratlar usuli.

Bu masala besh o‘zgaruvchili funksiyaning minimalini topish masalasiga tengdir

Optimallashtirish masalasini yechish jarayonini ko'rib chiqamiz (14-rasm).

Qadriyatlarga ruxsat bering A, IN, BILAN, D Va TO hujayralarda saqlanadi A7: E7. Funktsiyaning nazariy qiymatlarini hisoblaylik Z(t)=4 +Bt 3 +Ct 2 +Dt+K da berilgan uchun t(B2: J2). Buning uchun hujayra ichida B4 birinchi nuqtaga funktsiyaning qiymatini kiriting (hujayra B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Keling, ushbu formulani diapazonga ko'chiraylik C4: J4 va abscissalari katakchalarda saqlanadigan nuqtalarda funksiyaning kutilayotgan qiymatini oling B2: J2.

Hujayraga B5 Eksperimental va hisoblangan nuqtalar orasidagi farqning kvadratini hisoblaydigan formulani kiritamiz:

B5=(B4-B3)^2,

va uni diapazonga nusxalash C5: J5. Hujayrada F7 umumiy kvadrat xatoni (10) saqlaymiz. Buning uchun formulani kiriting:

F7 = SUM(B5:J5).

Keling, buyruqdan foydalanamiz Service® Yechimni qidiring va optimallashtirish muammosini cheklovlarsiz hal qiling. Keling, rasmda ko'rsatilgan dialog oynasidagi kiritish maydonlarini mos ravishda to'ldiramiz. 14 va tugmani bosing Bajarish. Agar yechim topilsa, rasmda ko'rsatilgan oyna. 15.

Qaror blokining natijasi hujayralarga chiqariladi A7: E7parametr qiymatlari funktsiyalari Z(t)=4 +Bt 3 +Ct 2 +Dt+K da. Hujayralarda B4: J4 olamiz kutilgan funktsiya qiymati boshlang'ich nuqtalarida. Hujayrada F7 saqlanadi umumiy kvadrat xato.

Diapazonni tanlab, bitta grafik maydonda tajriba nuqtalari va oʻrnatilgan chiziqni koʻrsatishingiz mumkin B2: J4, qo'ng'iroq qiling Grafik ustasi va keyin formatlash tashqi ko'rinish grafiklarni oldi.

Guruch. 17 hisob-kitoblar bajarilgandan so'ng MS Excel ish varag'ini ko'rsatadi.

5. ADABIYOTLAR

1. Alekseev E.R., Chesnokova O.V., Mathcad12, MATLAB7, Maple9 paketlarida hisoblash matematikasi masalalarini yechish. – NT Press, 2006.–596 b. :il. – (qo‘llanma)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, muhandislik va matematik muammolarni hal qilish. –M., BINOM, 2008.–260 b.

3. Berezin I.S., Jidkov N.P., Hisoblash usullari.– M.: Nauka, 1966. – 632 b.

4. Garnayev A.Yu., Iqtisodiyot va moliya fanida MS EXCEL va VBA dan foydalanish. – Sankt-Peterburg: BHV - Peterburg, 1999.–332 p.

5. Demidovich B.P., Maron I.A., Shuvalova V.Z., Tahlilning raqamli usullari.– M.: Nauka, 1967. – 368 b.

6. Korn G., Korn T., Olimlar va muhandislar uchun matematika qo'llanmasi. – M., 1970, 720 b.

7. Alekseev E.R., Chesnokova O.V. MS EXCEL da laboratoriya ishlarini bajarish bo'yicha ko'rsatmalar. Barcha mutaxassisliklar talabalari uchun. Donetsk, DonNTU, 2004. 112 p.