Statistikada qurilgan modelning sifatini tavsiflovchi ko'rsatkichlardan biri aniqlash koeffitsientidir (R ^ 2), bu ham taxminiy ishonch qiymati deb ataladi. Uning yordamida siz bashoratning aniqlik darajasini aniqlashingiz mumkin. Keling, turli xil Excel vositalari yordamida ushbu ko'rsatkichni qanday hisoblashingiz mumkinligini bilib olaylik.
Aniqlanish koeffitsientini hisoblash
Aniqlanish koeffitsienti darajasiga qarab modellarni uch guruhga bo'lish odatiy holdir.
- 0,8 - 1 - sifatli model;
- 0,5 - 0,8 - maqbul sifat modeli;
- 0 - 0.5 - sifatsiz model.
Ikkinchi holda, modelning sifati uni prognozlash uchun ishlatish mumkin emasligini ko'rsatadi.
Belgilangan qiymatni Excel-da qanday hisoblashni tanlash regressiya chiziqli yoki yo'qligiga bog'liq. Birinchi holda, siz funktsiyadan foydalanishingiz mumkin KVPIRSON, ikkinchisida siz tahlil paketidan maxsus vositani ishlatishingiz kerak.
1-usul: chiziqli funktsiya yordamida aniqlash koeffitsientini hisoblash
Avvalo, chiziqli funktsiyani aniqlash koeffitsientini qanday topish mumkinligini bilib olamiz. Bunday holda, bu ko'rsatkich korrelyatsiya koeffitsientining kvadratiga teng bo'ladi. Biz buni quyida keltirilgan aniq jadval misolida o'rnatilgan Excel funktsiyasidan foydalanib hisoblaymiz.
- Hisoblashdan keyin aniqlash koeffitsienti ko'rsatiladigan katakchani tanlang va belgini bosing "Qo'shish funktsiyasi".
- Boshlaydi Xususiyat ustasi. Uning toifasiga o'tish "Statistik" va ismni belgilang KVPIRSON. Keyingi tugmani bosing "OK".
- Funktsiya argumentlari oynasi boshlanadi. KVPIRSON. Statistik guruhning ushbu operatori Pearson funktsiyasining korrelyatsiya koeffitsientining kvadratini, ya'ni chiziqli funktsiyani hisoblash uchun mo'ljallangan. Va esda tutganimizdek, chiziqli funktsiya bilan aniqlash koeffitsienti korrelyatsiya koeffitsientining kvadratiga tengdir.
Ushbu gapning sintaksisi:
= KVPIRSON (ma'lum_ qiymatlar_; ma'lum_x qiymatlar)Shunday qilib, funktsiya ikkita operatorga ega, ulardan biri funktsiya qiymatlari ro'yxati, ikkinchisi - argument. Operatorlarni to'g'ridan-to'g'ri nuqta-vergul bilan sanab o'tilgan qiymatlar sifatida ko'rsatish mumkin (;) va ular joylashgan diapazonlarga bog'lanish shaklida. Ushbu misolda biz foydalanadigan ikkinchi variant.
Maydonga kursorni o'rnating Ma'lum bo'lgan qadriyatlar. Biz sichqonchaning chap tugmachasini ushlab turamiz va ustun tarkibini tanlaymiz "Y" jadvallar. Ko'rib turganingizdek, ko'rsatilgan ma'lumotlar qatorining manzili darhol derazada ko'rsatiladi.
Xuddi shu tarzda, maydonni to'ldiring Ma'lum x qiymatlar. Kursorni ushbu maydonga qo'ying, ammo bu safar ustun qiymatlarini tanlang "X".
Argumentlar oynasida barcha ma'lumotlar ko'rsatilgandan so'ng KVPIRSONtugmachani bosing "OK"uning pastki qismida joylashgan.
- Ko'rib turganingizdek, shundan so'ng dastur aniqlanish koeffitsientini hisoblab chiqadi va natijani qo'ng'iroqdan oldin tanlangan katakchada namoyish etadi Funktsiya sehrgarlari. Bizning misolimizda, hisoblangan indikatorning qiymati 1 ga aylandi, bu taqdim etilgan model mutlaqo ishonchli ekanligini anglatadi, ya'ni xatoni bartaraf qiladi.
Dars: Microsoft Excel-da xususiyatlar ustasi
2-usul: chiziqli bo'lmagan funktsiyalarda aniqlash koeffitsientini hisoblash
Ammo kerakli qiymatni hisoblash uchun yuqoridagi parametr faqat chiziqli funktsiyalarga qo'llanilishi mumkin. Uni chiziqli bo'lmagan funktsiyada hisoblash uchun nima qilish kerak? Excel-da bunday imkoniyat mavjud. Buni asbob yordamida bajarish mumkin. "Regresiya"paketning bir qismi "Ma'lumotlar tahlili".
- Ammo ko'rsatilgan vositani ishlatishdan oldin uni o'zingiz faollashtirishingiz kerak Tahlil paketi, Excel-da sukut bo'yicha o'chirilgan. Yorliqqa o'ting Faylva keyin boring "Tanlovlar".
- Ochilgan oynada bo'limga o'ting "Qo'shimcha dasturlar" chap vertikal menyuga o'tish orqali. Oynaning o'ng panelining pastki qismida maydon mavjud "Menejment". U erda mavjud bo'limlar ro'yxatidan ismni tanlang "Excel plaginlari ..."va keyin tugmachani bosing "Bor ..."maydonning o'ng tomonida joylashgan.
- Qo'shimcha dasturlar oynasi ishga tushirildi. Uning markaziy qismida mavjud qo'shimchalar ro'yxati. Joyning yonidagi katakchani o'rnating Tahlil paketi. Shundan so'ng tugmani bosing "OK" deraza interfeysining o'ng tomonida.
- Asboblar to'plami "Ma'lumotlar tahlili" joriy Excel-da faollashtiriladi. Unga kirish yorliqdagi lentada joylashgan "Ma'lumotlar". Belgilangan yorliqqa o'tamiz va tugmani bosing "Ma'lumotlar tahlili" sozlamalar guruhida "Tahlil".
- Oyna faollashtirildi "Ma'lumotlar tahlili" ixtisoslashtirilgan axborotni qayta ishlash vositalari ro'yxati bilan. Ushbu ro'yxatdan elementni tanlang "Regresiya" va tugmachani bosing "OK".
- Keyin asbob oynasi ochiladi "Regresiya". Birinchi sozlash bloki "Kiritish". Bu erda ikkita maydonda argument va funktsiyaning qiymatlari joylashgan diapazonlarning manzillarini ko'rsatish kerak. Kursorni maydonga qo'ying "Y kiritish oralig'i" va varaqdagi ustun tarkibini tanlang "Y". Massiv manzili oynada ko'rsatilgandan so'ng "Regresiya"kursorni maydonga qo'ying "Y kiritish oralig'i" va ustunlar kataklarini xuddi shu tarzda tanlang "X".
Parametrlar haqida "Yorliq" va Doimiy nol bayroqlarni qo'ymang. Parametr yonida belgilash katakchasini o'rnatish mumkin "Ishonchlilik darajasi" va qarama-qarshi maydonda mos keladigan indikatorning kerakli qiymatini ko'rsating (sukut bo'yicha 95%).
Guruhda Chiqish parametrlari hisoblash natijasi qaysi sohada ko'rsatilishini ko'rsatishingiz kerak. Uch variant mavjud:
- Joriy varaqdagi maydon;
- Boshqa varaq;
- Boshqa kitob (yangi fayl).
Birinchi variantni tanlaymiz, shunda dastlabki ma'lumotlar va natijalar bir xil ishchi varaqqa joylashtiriladi. Kalitni parametrga yaqin joylashtiramiz "Chiqish oralig'i". Ushbu elementga qarama qarshi maydonga kursorni qo'ying. Hisoblash natijalari jadvalining yuqori chap hujayrasi bo'lishga mo'ljallangan varaqdagi bo'sh elementni chap tugmachasini bosing. Ushbu elementning manzili deraza oynasida ko'rsatilishi kerak "Regresiya".
Parametr guruhlari "Soqchilar" va "Oddiy ehtimollik" E'tibor bermang, chunki ular vazifani hal qilishda muhim emas. Shundan so'ng, tugmachani bosing "OK"oynaning yuqori o'ng burchagida joylashgan "Regresiya".
- Dastur ilgari kiritilgan ma'lumotlar asosida hisoblab chiqadi va natijani belgilangan oraliqda namoyish etadi. Ko'rib turganingizdek, ushbu vosita varaqdagi turli xil parametrlar bo'yicha juda ko'p natijalarni namoyish etadi. Ammo hozirgi dars kontekstida biz ko'rsatkichga qiziqamiz R-kvadrat. Bunday holda, u tanlangan modelni yaxshi sifat modeli sifatida tavsiflaydigan 0.947664 ga teng.
3-usul: trend chizig'ini aniqlash koeffitsienti
Yuqoridagi variantlardan tashqari, aniqlash koeffitsienti tendentsiya chizig'i uchun to'g'ridan-to'g'ri Excel ish varag'ida qurilgan grafikda ko'rsatilishi mumkin. Buni qanday qilib aniq bir misol yordamida amalga oshirish mumkinligini bilib olamiz.
- Bizda oldingi misol uchun ishlatilgan argumentlar va funktsiya qiymatlari jadvaliga asoslangan grafik mavjud. Biz unga trend chizig'ini quramiz. Biz sichqonchaning chap tugmasi bilan grafik joylashtirilgan qurilish maydonchasining istalgan joyini bosamiz. Shu bilan birga, lentada qo'shimcha yorliqlar paydo bo'ladi - "Grafik bilan ishlash". Yorliqqa o'ting "Layout". Tugmani bosing Trend liniyasiasboblar blokida joylashgan "Tahlil". Trend liniyasi turini tanlash bilan menyu paydo bo'ladi. Muayyan vazifaga mos keladigan turni tanlashni to'xtatamiz. Keling, bizning misolimiz uchun bitta variantni tanlaylik "Eksponensial yaqinlashish".
- Excel sxemada to'g'ri qo'shimcha chiziq shaklida trend chizig'ini quradi.
- Endi bizning vazifamiz - o'zini o'zi aniqlash koeffitsientini ko'rsatish. Trend chizig'ini o'ng tugmasini bosing. Kontekst menyusi faollashtirilgan. Biz unda tanlovni to'xtatamiz "Trend yo'nalishining formati ...".
Trend liniyasi formati oynasiga o'tishni amalga oshirish uchun siz alternativ harakatni amalga oshirishingiz mumkin. Sichqonchaning chap tugmachasini bosib, chiziq chizig'ini tanlang. Yorliqqa o'ting "Layout". Tugmani bosing Trend liniyasi blokda "Tahlil". Ochilgan ro'yxatda harakatlar ro'yxatidagi eng oxirgi bandni bosing - "Trend yo'nalishining qo'shimcha parametrlari ...".
- Yuqoridagi ikkala harakatlardan so'ng, qo'shimcha sozlashlarni amalga oshirishingiz mumkin bo'lgan format oynasi ishga tushiriladi. Xususan, vazifamizni bajarish uchun yonidagi katakchani belgilash kerak "Taxminiy ishonchlilik qiymatini (R ^ 2) diagrammaga qo'ying". U derazaning eng pastki qismida joylashgan. Ya'ni, shu tarzda biz qurilish maydonida aniqlanish koeffitsientini ko'rsatishga imkon beramiz. Keyin tugmani bosishni unutmang Yopish joriy oynaning pastki qismida.
- Yaqinlashuvning ishonchliligi qiymati, ya'ni aniqlash koeffitsientining qiymati qurilish maydonidagi varaqda ko'rsatiladi. Bunday holda, bu qiymat, biz ko'rib turganimizdek, 0.9242 ni tashkil etadi, bu yaqinlikni yaxshi sifat modeli sifatida tavsiflaydi.
- Mutlaq aniq shu tarzda siz har qanday boshqa trend yo'nalishi uchun aniqlanish koeffitsientini o'rnatishingiz mumkin. Yuqorida ko'rsatilgandek, lenta tugmachasi yoki uning parametrlari oynasidagi kontekst menyusi orqali o'tish orqali trend liniyasining turini o'zgartirishingiz mumkin. Keyin guruhda derazaning o'zida "Trend yo'nalishlarini yaratish" Siz boshqa turga o'tishingiz mumkin. Shu bilan birga, nuqta atrofida buni boshqarishni unutmang "Taxminiy ishonchlilik qiymatini diagrammaga qo'ying" katakcha belgilandi. Yuqoridagi amallarni bajarganingizdan so'ng tugmani bosing Yopish oynaning pastki o'ng burchagida.
- Lineer turga ega bo'lgan holda, trend chizig'i allaqachon yaqinlashuvning ishonchlilik qiymatiga ega, bu esa avvalroq ko'rib chiqilgan eksponensial turning trend chizig'iga qaraganda ushbu modelni yanada ishonchli deb tavsiflaydi.
- Shunday qilib, turli xil yo'nalish yo'nalishlarini almashtirish va ularning yaqinlik ishonchliligini (aniqlash koeffitsienti) taqqoslab, biz taqdim etilgan grafikani eng aniq tasvirlaydigan variantni topishimiz mumkin. Aniqlanish koeffitsientining eng yuqori koeffitsientiga ega variant eng ishonchli bo'ladi. Unga asoslanib, eng aniq prognozni tuzishingiz mumkin.
Masalan, bizning misolimizda eksperimental ravishda ikkinchi darajadagi trend chizig'ining polinomial turi eng yuqori ishonch darajasiga ega ekanligini aniqlash mumkin edi. Bu holda aniqlash koeffitsienti 1 ga teng, bu model mutlaqo ishonchli ekanligini anglatadi, bu xatolarning to'liq chiqarib tashlanishini anglatadi.
Ammo, shu bilan birga, bu umuman boshqa chizma uchun trend liniyasining ushbu turi eng ishonchli bo'lishini anglatmaydi. Trend chizig'i turini maqbul tanlash grafik asosida qurilgan funktsiya turiga bog'liq. Agar foydalanuvchi eng yaxshi sifatli variantni ko'z bilan baholash uchun etarli ma'lumotga ega bo'lmasa, yuqorida keltirilgan misolda ko'rsatilgandek, eng yaxshi prognozni aniqlashning yagona usuli - aniqlash koeffitsientlarini taqqoslashdir.
Shuningdek o'qing:
Excel-da trend liniyasini qurish
Excelda yaqinlashish
Excel-da aniqlash koeffitsientini hisoblashning ikkita asosiy varianti mavjud: operatordan foydalanish KVPIRSON va vositalardan foydalanish "Regresiya" asboblar qutisidan "Ma'lumotlar tahlili". Bundan tashqari, ushbu variantlarning birinchisi faqat chiziqli funktsiyani qayta ishlashda foydalanish uchun mo'ljallangan, ikkinchisi esa deyarli barcha holatlarda ishlatilishi mumkin. Bundan tashqari, jadvalning trend chizig'i uchun aniqlash koeffitsientini taxminiy ishonchlilik qiymati sifatida ko'rsatish mumkin. Ushbu indikatordan foydalanib, ma'lum bir funktsiya uchun eng yuqori ishonch darajasiga ega bo'lgan trend chizig'ini aniqlash mumkin.
