Excel nishab energiyasidagi eng kichik kvadratlar usuli. Excelda eng kichik kvadratlar usuli

Eng kichik kvadratlar usuli (LS) o'rganilayotgan ma'lumotlardan tanlangan funksiyaning kvadratik og'ishlari yig'indisini minimallashtirishga asoslangan. Ushbu maqolada biz chiziqli funktsiya yordamida mavjud ma'lumotlarni taxmin qilamizy = a x + b .

Eng kichik kvadratlar usuli(inglizcha) Oddiy Eng kam Kvadratchalar , O.L.S.) noma'lum parametrlarni baholash nuqtai nazaridan regressiya tahlilining asosiy usullaridan biridir regressiya modellari namuna ma'lumotlariga ko'ra.

Faqat bitta o‘zgaruvchiga bog‘liq bo‘lgan funksiyalar bo‘yicha yaqinlashishni ko‘rib chiqamiz:

• Chiziqli: y=ax+b (ushbu maqola)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+s
• : y=ax 2 +bx+c

Eslatma: Ushbu maqolada 3-dan 6-darajali ko'phad bilan yaqinlashish hollari ko'rib chiqiladi. Bu erda trigonometrik ko'phad bilan yaqinlashish ko'rib chiqiladi.

Chiziqli bog'liqlik

Bizni ikkita o'zgaruvchi o'rtasidagi bog'liqlik qiziqtiradi X Va y. Bu degan taxmin bor y ga bog'liq X chiziqli qonunga muvofiq y = bolta + b. Ushbu munosabatlarning parametrlarini aniqlash uchun tadqiqotchi kuzatishlar o'tkazdi: x i ning har bir qiymati uchun y i o'lchovi amalga oshirildi (misol fayliga qarang). Shunga ko'ra, 20 juft qiymat bo'lsin (x i; y i).

Eslatma: Agar o'zgartirish bosqichi bo'lsa X doimiy, keyin qurish uchun tarqalish uchastkalari foydalanish mumkin, agar bo'lmasa, grafik turini ishlatishingiz kerak Spot .

Diagrammadan ko'rinib turibdiki, o'zgaruvchilar orasidagi bog'liqlik chiziqliga yaqin. Ko'p to'g'ri chiziqlardan qaysi biri o'zgaruvchilar o'rtasidagi munosabatni eng "to'g'ri" tasvirlashini tushunish uchun chiziqlar taqqoslanadigan mezonni aniqlash kerak.

Bunday mezon sifatida biz quyidagi iborani ishlatamiz:

Qayerda ŷ i = a * x i + b ; n - qiymatlar juftligi soni (bizning holatda n = 20)

Yuqoridagi ifoda y i va ŷ i ning kuzatilgan qiymatlari orasidagi kvadratik masofalarning yig'indisidir va ko'pincha SSE sifatida belgilanadi ( so'm ning Kvadrat Xatolar (Qoldiqlar), kvadratik xatolar yig'indisi (qoldiq)) .

Eng kichik kvadratlar usuli shunday qatorni tanlashdir ŷ = bolta + b, buning uchun yuqoridagi ifoda minimal qiymatni oladi.

Eslatma: Ikki o'lchovli fazodagi har qanday chiziq 2 parametrning qiymatlari bilan noyob tarzda aniqlanadi: a (qiyalik) va b (shift).

Kvadrat masofalar yig'indisi qanchalik kichik bo'lsa, mos keladigan chiziq mavjud ma'lumotlarga shunchalik yaqinroq bo'ladi va undan keyin x o'zgaruvchisidan y qiymatlarini bashorat qilish uchun ishlatilishi mumkin, deb ishoniladi. Ma'lumki, agar haqiqatda o'zgaruvchilar o'rtasida hech qanday bog'liqlik bo'lmasa yoki munosabatlar chiziqli bo'lmasa ham, OLS baribir "eng yaxshi" qatorni tanlaydi. Shunday qilib, eng kichik kvadratlar usuli o'zgaruvchilar o'rtasida haqiqiy munosabatlar mavjudligi haqida hech narsa aytmaydi, usul shunchaki bunday funktsiya parametrlarini tanlashga imkon beradi; a Va b , buning uchun yuqoridagi ifoda minimal.

Juda murakkab bo'lmagan matematik operatsiyalarni bajarib (batafsil ma'lumot uchun qarang), siz parametrlarni hisoblashingiz mumkin a Va b :

Formuladan ko'rinib turibdiki, parametr a kovariantning nisbatini ifodalaydi va shuning uchun MS EXCEL da parametrni hisoblash uchun A Siz quyidagi formulalardan foydalanishingiz mumkin (qarang Chiziqli varaq fayli namunasi):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45) yoki

= KOVARIANS.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Shuningdek, parametrni hisoblash uchun A = formulasidan foydalanishingiz mumkin TILT(C26:C45;B26:B45). Parametr uchun b = formulasidan foydalaning Oyoq(C26:C45;B26:B45) .

Nihoyat, LINEST() funksiyasi ikkala parametrni birdaniga hisoblash imkonini beradi. Formulani kiritish uchun LINEST(C26:C45;B26:B45) Siz ketma-ket 2 katakni tanlashingiz va bosishingiz kerak CTRL + SHIFT + KIRISH(haqidagi maqolaga qarang). Qiymat chap katakda qaytariladi A , o'ngda - b .

Eslatma: Kirish bilan aralashmaslik uchun massiv formulalari qo'shimcha ravishda INDEX() funksiyasidan foydalanishingiz kerak bo'ladi. Formula = INDEX(LINEST(C26:C45,B26:B45),1) yoki shunchaki = LINEST(C26:C45;B26:B45) chiziqning qiyaligi uchun mas'ul bo'lgan parametrni qaytaradi, ya'ni. A . Formula = INDEX(LINEST(C26:C45,B26:B45),2) chiziqning Y o'qi bilan kesishishi uchun mas'ul bo'lgan parametrni qaytaradi, ya'ni. b .

Parametrlarni hisoblab chiqib, tarqalish diagrammasi mos keladigan chiziqni chizishingiz mumkin.

Eng kichik kvadratlar usuli yordamida to'g'ri chiziq chizishning yana bir usuli grafik asbobidir Trend chizig'i. Buning uchun diagrammani tanlang, menyudan tanlang Tartib yorlig'i, V guruh tahlili bosing Trend chizig'i, keyin Chiziqli yaqinlashish .

Muloqot oynasidagi "Tenglamani diagrammada ko'rsatish" katagiga belgi qo'yish orqali siz yuqoridagi parametrlar diagrammadagi qiymatlarga mos kelishiga ishonch hosil qilishingiz mumkin.

Eslatma: Parametrlar mos kelishi uchun diagramma turi bo'lishi kerak. Gap shundaki, diagramma tuzishda Jadval X o'qi qiymatlarini foydalanuvchi ko'rsata olmaydi (foydalanuvchi faqat nuqtalarning joylashishiga ta'sir qilmaydigan teglarni belgilashi mumkin). X qiymatlari o'rniga 1-ketlik ishlatiladi; 2; 3; ... (toifalarni raqamlash uchun). Shuning uchun, agar siz qursangiz trend chizig'i tip diagrammasi bo'yicha Jadval, keyin X ning haqiqiy qiymatlari o'rniga ushbu ketma-ketlikning qiymatlari qo'llaniladi, bu noto'g'ri natijaga olib keladi (agar, albatta, X ning haqiqiy qiymatlari 1-ketma-ketlikka to'g'ri kelmasa; 2; 3;

Xo'sh, ishda biz tekshiruvga xabar berdik, maqola konferentsiya uchun uyda yozilgan - endi biz blogda yozishimiz mumkin. Ma'lumotlarimni qayta ishlayotganimda, Excelda juda ajoyib va ​​kerakli plagin haqida yozishdan boshqa ilojim yo'qligini angladim. Shunday qilib, maqola ushbu qo'shimchaga bag'ishlanadi va men bu haqda foydalanish misolidan foydalanib aytib beraman eng kichik kvadratlar usuli(LSM) eksperimental ma'lumotlarni tavsiflashda noma'lum tenglama koeffitsientlarini qidirish uchun.

"Yechim izlash" qo'shimchasini qanday yoqish mumkin

Birinchidan, ushbu qo'shimchani qanday yoqishni aniqlaylik.

1. "Fayl" menyusiga o'ting va "Excel Options" ni tanlang.

2. Ko'rsatilgan oynada "Yechim izlash" ni tanlang va "o'tish" tugmasini bosing.

3. Keyingi oynada "yechimni qidirish" yonidagi katakchani belgilang va "OK" tugmasini bosing.

4. Qo'shimcha faollashtirildi - endi uni "Ma'lumotlar" menyusida topish mumkin.

Eng kichik kvadratlar usuli

Endi qisqacha Eng kichik kvadratlar usuli (LSM) va undan qayerda foydalanish mumkin.

Aytaylik, biz qandaydir tajriba o'tkazganimizdan so'ng bizda X qiymatining Y qiymatiga ta'sirini o'rganganimizdan so'ng ma'lumotlar to'plami mavjud.

Biz bu ta'sirni matematik tarzda tasvirlamoqchimiz, shunda biz ushbu formuladan foydalanamiz va agar X ning qiymatini shunchalik o'zgartirsak, Y ning falon va shunga o'xshash qiymatini olishimizni bilamiz ...

Men juda oddiy misol keltiraman (rasmga qarang).

Nuqtalar birin-ketin xuddi to'g'ri chiziqda joylashgani aqlga sig'maydi va shuning uchun bizning qaramligimiz tasvirlangan deb ishonch bilan taxmin qilamiz. chiziqli funksiya y=kx+b. Shu bilan birga, X nolga teng bo'lsa, Y ning qiymati ham nolga teng ekanligiga mutlaqo aminmiz. Bu shuni anglatadiki, bog'liqlikni tavsiflovchi funktsiya yanada sodda bo'ladi: y=kx (maktab o'quv dasturini eslang).

Umuman olganda, biz k koeffitsientini topishimiz kerak. Bu bilan biz nima qilamiz MNC "yechimlarni qidirish" qo'shimchasidan foydalanish.

Usul shundan iboratki (bu erda - diqqat: siz bu haqda o'ylashingiz kerak) eksperimental ravishda olingan va tegishli hisoblangan qiymatlar o'rtasidagi farqlar kvadratlarining yig'indisi minimaldir. Ya'ni, X1=1 bo'lganda haqiqiy o'lchov qiymati Y1=4,6 va hisoblangan y1=f (x1) 4 ga teng bo'lsa, farqning kvadrati (y1-Y1)^2=(4-4,6)^ bo'ladi. 2=0,36 . Bu quyidagi bilan bir xil: X2=2 bo'lsa, Y2 ning haqiqiy o'lchangan qiymati =8,1 va hisoblangan y2 8 bo'lsa, farqning kvadrati (y2-Y2)^2=(8-8,1)^2 bo'ladi. =0,01. Va bu barcha kvadratlarning yig'indisi imkon qadar kichik bo'lishi kerak.

Shunday qilib, LSM va foydalanish bo'yicha treningni boshlaylik Excel plaginlari"echim izlash" .

Yechim topish uchun qo'shimchani qo'llash

1. Agar siz “yechim izlash” qo‘shimchasini yoqmagan bo‘lsangiz, nuqtaga qayting. "Yechim izlash" qo'shimchasini qanday yoqish va uni yoqish 🙂

2. A1 katakka “1” qiymatini kiriting. Bu birlik y=kx funksional munosabatimizning (k) koeffitsientining haqiqiy qiymatiga birinchi yaqinlik bo'ladi.

3. B ustunida X parametrining qiymatlari, C ustunida Y parametrining qiymatlari mavjud. D ustunining kataklariga formulani kiritamiz: “k koeffitsienti X qiymatiga ko'paytiriladi. ” Masalan, D1 katakka “=A1*B1”, D2 katakchaga “=A1*B2” va hokazolarni kiritamiz.

4. Biz k koeffitsienti bir ga teng va f (x)=y=1*x funksiya yechimimizning birinchi yaqinlashuvi deb hisoblaymiz. Y ning o'lchangan qiymatlari va y = 1 * x formulasi yordamida hisoblanganlar o'rtasidagi farqlarning kvadratlari yig'indisini hisoblashimiz mumkin. Bularning barchasini quyidagi formulaga mos keladigan hujayra havolalarini kiritish orqali qo'lda qilishimiz mumkin: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... va hokazo. Oxir-oqibat biz adashadi va biz juda ko'p vaqtni yo'qotganimizni tushunamiz, Excelda kvadrat farqlar yig'indisini hisoblash uchun "SUMMQRAS" mavjud bo'lib, u biz uchun hamma narsani A2 katagiga kiritadi va dastlabki ma'lumotlarni o'rnatadi : o'lchangan qiymatlar diapazoni Y (ustun C) va hisoblangan Y qiymatlari diapazoni (D ustuni).

4. Kvadratchalar farqlarining yig'indisi hisoblab chiqilgan - endi "Ma'lumotlar" yorlig'iga o'ting va "Yechim izlash" ni tanlang.

5. Ko'rsatilgan menyuda o'zgartiriladigan katak sifatida A1 katakchani (koeffitsienti k) tanlang.

6. Maqsad sifatida A2 katakchasini tanlang va "minimal qiymatga teng o'rnatish" shartini qo'ying. Esda tutamizki, bu hisoblangan va o'lchangan qiymatlar o'rtasidagi farqlarning kvadratlari yig'indisini hisoblaydigan katakdir va bu summa minimal bo'lishi kerak. "Bajarish" tugmasini bosing.

7. k koeffitsienti tanlangan. Endi siz hisoblangan qiymatlar o'lchangan qiymatlarga juda yaqin ekanligini tekshirishingiz mumkin.

P.S.

Umuman olganda, Excelda eksperimental ma'lumotlarni taxmin qilish uchun chiziqli, eksponensial, quvvat va polinom funktsiyalaridan foydalangan holda ma'lumotlarni tavsiflash imkonini beruvchi maxsus vositalar mavjud, shuning uchun siz ko'pincha buni qilmasdan qilishingiz mumkin. "Yechim izlash" qo'shimchalari. Men o'zimdagi barcha taxminiy usullar haqida gapirdim, shuning uchun agar qiziqsangiz, ko'rib chiqing. Ammo ba'zi ekzotik funktsiya haqida gap ketganda bitta noma'lum koeffitsient bilan yoki optimallashtirish muammolari, keyin bu yerda ustki tuzilma yaxshiroq vaqtda kela olmadi.

Yechim qidirish qo'shimchasi boshqa vazifalar uchun ishlatilishi mumkin, asosiysi mohiyatni tushunishdir: biz qiymatni tanlaydigan hujayra mavjud va noma'lum parametrni tanlash sharti ko'rsatilgan maqsadli hujayra mavjud.
Bo'ldi shu! Keyingi maqolada men sizga ta'til haqida ertak aytib beraman, shuning uchun maqola nashr etilishini o'tkazib yubormaslik uchun,

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'lchangan oziq-ovqat do'konining chakana savdo maydoni bo'lsin va Y millionlab rubllarda aniqlangan 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.

Ga binoan matematik statistika, 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 yuqori darajali polinomdan foydalanishingiz mumkin, ammo bu variant nafaqat amalga oshirish qiyin, balki oddiygina 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 tasvirlash uchun to'g'ri chiziqni tanlashda siz eng kichik qiymatga ega bo'lganiga ustunlik berishingiz kerak. ko'rib chiqilayotgan barcha nuqtalarda e i summasi. 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 kvadratlar 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 ham kirishi mumkin. 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, Excelda funktsiyadan foydalanganda dastur uni butun sonlardan iborat massiv sifatida ko'rib chiqadi, ularning soni berilgan qiymatlari bilan diapazonga mos keladi. y o'zgaruvchisi.
• “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

Bir nechta funktsiyalar 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 qurish uchun matematik protsedura chiziqli tenglama, bu ikki qator raqamlar to'plamiga eng mos keladi. Ushbu usuldan foydalanishdan maqsad umumiy kvadrat xatosini minimallashtirishdir. Excelda siz foydalanishingiz mumkin bo'lgan vositalar mavjud bu usul hisob-kitoblar paytida. Keling, bu qanday amalga oshirilganligini aniqlaylik.

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.

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

Muammoli sharoitlar

Keling, LSM dan foydalanishni aniq bir misol yordamida 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.

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.

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'lchangan oziq-ovqat do'konining chakana savdo maydoni bo'lsin va Y millionlab rubllarda aniqlangan 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 butikning aylanmasi "masmarket" sinfidagi yirik savdo nuqtalarining aylanmasidan bir necha baravar ko'p 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 yuqori darajali polinomdan foydalanishingiz mumkin, ammo bu variant nafaqat amalga oshirish qiyin, balki oddiygina 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 tasvirlash uchun to'g'ri chiziqni tanlashda siz eng kichik qiymatga ega bo'lganiga ustunlik berishingiz kerak. ko'rib chiqilayotgan barcha nuqtalarda e i summasi. 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 kvadratlar 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, Excelda funktsiyadan foydalanganda dastur uni butun sonlardan iborat massiv sifatida ko'rib chiqadi, ularning soni berilgan qiymatlari bilan diapazonga mos keladi. y o'zgaruvchisi.
• “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.