Usul eng kichik kvadratlar(LSM) oʻrganilayotgan maʼlumotlardan tanlangan funksiyaning kvadratik chetlanishlar yigʻindisini minimallashtirishga asoslangan. Ushbu maqolada biz chiziqli funktsiya yordamida mavjud ma'lumotlarni taxmin qilamizy = a x + b .

Eng kichik kvadrat 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 ifodadan foydalanamiz:

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), kvadrat xatolar yig'indisi (qoldiq)) .

Eng kichik kvadrat 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 yuqorida topilgan 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; ...).

U juda ko'p foydalanishga ega, chunki u taxminiy vakillik qilish imkonini beradi berilgan funksiya boshqalar oddiyroq. 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 oziq-ovqat do'konining chakana savdo maydoni bo'lsin kvadrat metr, va Y - millionlab rubllarda aniqlangan yillik aylanma.

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 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, shu jumladan regressiya tahlili(Excelda uni amalga oshirish ikkita o'rnatilgan funksiya yordamida amalga oshiriladi) va uning samaradorligini uzoq vaqtdan beri 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 (in Ushbu holatda savdo aylanmasi to'g'risidagi 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 diapazonga mos keladi berilgan parametrlar y.
• 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 (LSM)

Tizim m chiziqli tenglamalar n noma'lumli quyidagi shaklga 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 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 echilganga qisqartirish yo'li bilan yechim topish 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 algoritmning qo'llanilishini ko'rib chiqamiz aniq misol.

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 - qaytaradi teskari matritsa massivda saqlangan matritsa uchun.

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.

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.

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.

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 munosabatlarni o'rnatish imkonini 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. Variantlarni tahlil qilish vositasidan foydalanish: Yechim topish.

MS Excel dasturida variantni tahlil qilish vositasi yordamida eng kichik kvadratlar usuli yordamida funktsional munosabatlar parametrlarini tanlashning amalga oshirilishi katta qiziqish uyg'otadi: Yechimni qidirish. 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

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 olingan grafiklarning ko'rinishini formatlang.

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