Excelda korrelyatsiya tahlili. Korrelyatsiya tahlilini bajarishga misol

Korrelyatsiya tahlili - 1-qiymatning 2-chiga bog'liqlik darajasini aniqlash uchun ishlatiladigan keng tarqalgan tadqiqot usuli. Elektron jadvalda ushbu turdagi tadqiqotni amalga oshirish imkonini beruvchi maxsus vosita mavjud.

Korrelyatsiya tahlilining mohiyati

Ikki xil miqdor o'rtasidagi munosabatni aniqlash kerak. Boshqacha qilib aytadigan bo'lsak, ikkinchidagi o'zgarishlarga qarab qiymat qaysi yo'nalishda (kichikroq / kattaroq) o'zgarishini ochib beradi.

Korrelyatsiya tahlilining maqsadi

Bog'liqlik korrelyatsiya koeffitsientini aniqlash boshlanganda o'rnatiladi. Bu usul regressiya tahlilidan farq qiladi, chunki korrelyatsiya yordamida hisoblangan faqat bitta ko'rsatkich mavjud. Interval +1 dan -1 gacha o'zgaradi. Agar u ijobiy bo'lsa, unda birinchi qiymatning oshishi 2-chi o'sishga yordam beradi. Agar salbiy bo'lsa, 1-qiymatning oshishi 2-chi qiymatning pasayishiga yordam beradi. Koeffitsient qanchalik baland bo'lsa, bitta qiymat ikkinchisiga qanchalik kuchli ta'sir qiladi.

Muhim! 0-koeffitsientda miqdorlar o'rtasida bog'liqlik yo'q.

Korrelyatsiya koeffitsientini hisoblash

Keling, bir nechta namunalar bo'yicha hisob-kitobni tahlil qilaylik. Misol uchun, jadval ma'lumotlari mavjud bo'lib, unda reklamani ilgari surish va sotish hajmi oylar bo'yicha alohida ustunlarda tavsiflanadi. Jadvalga asoslanib, biz savdo hajmining reklamani ilgari surish uchun sarflangan pulga bog'liqlik darajasini aniqlaymiz.

1-usul: Funksiya ustasi orqali korrelyatsiyani aniqlash

CORREL – korrelyatsiya tahlilini amalga oshirish imkonini beruvchi funksiya. Umumiy shakl - CORREL (massiv1; massiv2). Batafsil ko'rsatmalar:

1. Hisoblash natijasini ko'rsatish rejalashtirilgan katakchani tanlash kerak. Formulani kiritish uchun matn maydonining chap tomonida joylashgan "Funktsiyani qo'shish" tugmasini bosing.

2. Funktsiya ustasi ochiladi. Bu erda siz topishingiz kerak CORREL, ustiga bosing, keyin esa “OK” tugmasini bosing.

3. Argumentlar oynasi ochiladi. "Masiv1" qatoriga qiymatlarning 1-chi oraliqlari koordinatalarini kiritishingiz kerak. Ushbu misolda bu Sotish qiymati ustuni. Siz faqat ushbu ustundagi barcha hujayralarni tanlashingiz kerak. Xuddi shunday, siz ikkinchi ustunning koordinatalarini "Array2" qatoriga qo'shishingiz kerak. Bizning misolimizda bu reklama xarajatlari ustunidir.

4. Barcha diapazonlarni kiritgandan so'ng, "OK" tugmasini bosing.

Koeffitsient bizning harakatlarimiz boshida ko'rsatilgan katakda ko'rsatilgan. Olingan natija 0,97 ga teng. Bu ko'rsatkich birinchi qiymatning ikkinchisiga yuqori bog'liqligini aks ettiradi.

2-usul: Analysis ToolPak yordamida korrelyatsiyani hisoblang

Korrelyatsiyani aniqlashning yana bir usuli mavjud. Bu erda tahlil paketida topilgan funksiyalardan biri qo'llaniladi. Uni ishlatishdan oldin siz vositani faollashtirishingiz kerak. Batafsil ko'rsatmalar:

1. "Fayl" bo'limiga o'ting.

2. Yangi oyna ochiladi, unda siz "Sozlamalar" bo'limini bosishingiz kerak.
3. "Qo'shimchalar" ni bosing.
4. Pastki qismida "Menejment" elementini topamiz. Bu erda siz kontekst menyusidan "Excel plaginlari" ni tanlashingiz va "OK" tugmasini bosishingiz kerak.

5. Maxsus qo'shimchalar oynasi ochildi. "Tahlil paketi" elementi yonidagi belgini qo'ying. Biz "OK" tugmasini bosing.
6. Faollashtirish muvaffaqiyatli bo'ldi. Endi ma'lumotlarga o'tamiz. "Tahlil" bloki paydo bo'ldi, unda siz "Ma'lumotlarni tahlil qilish" ni bosishingiz kerak.
7. Ko'rsatilgan yangi oynada "Korrelyatsiya" elementini tanlang va "OK" tugmasini bosing.

8. Ekranda tahlil sozlamalari oynasi paydo bo'ldi. "Kirish oralig'i" qatorida tahlilda qatnashadigan mutlaqo barcha ustunlar oralig'ini kiritish kerak. Ushbu misolda bular "Sotuv qiymati" va "Reklama xarajatlari" ustunlari. Chiqishni ko'rsatish sozlamalari dastlab Yangi ish varag'iga o'rnatiladi, ya'ni natijalar boshqa varaqda ko'rsatiladi. Majburiy emas, siz natijaning chiqish joyini o'zgartirishingiz mumkin. Barcha sozlamalarni amalga oshirgandan so'ng, "OK" tugmasini bosing.

Yakuniy ballar chiqdi. Natija birinchi usul bilan bir xil - 0,97.

MS Excelda ko'p korrelyatsiya koeffitsientini aniqlash va hisoblash

Bir nechta miqdorlarning bog'liqlik darajasini aniqlash uchun bir nechta koeffitsientlar qo'llaniladi. Kelajakda natijalar korrelyatsiya matritsasi deb ataladigan alohida jadvalda umumlashtiriladi.

Batafsil qo'llanma:

1. "Ma'lumotlar" bo'limida biz allaqachon ma'lum bo'lgan "Tahlil" blokini topamiz va "Ma'lumotlarni tahlil qilish" tugmasini bosing.

2. Ko'rsatilgan oynada "Korrelyatsiya" elementini bosing va "OK" tugmasini bosing.
3. "Kirish oralig'i" qatorida biz manba jadvalining uch yoki undan ortiq ustunlari oralig'ida harakat qilamiz. Diapazonni qo'lda kiritish yoki oddiygina LMB bilan tanlash mumkin va u avtomatik ravishda kerakli qatorda paydo bo'ladi. "Guruhlash" da tegishli guruhlash usulini tanlang. "Chiqish parametri" da korrelyatsiya natijalari ko'rsatiladigan joyni belgilaydi. Biz "OK" tugmasini bosing.

4. Tayyor! Korrelyatsiya matritsasi tuzildi.

Excelda juft korrelyatsiya koeffitsienti

Keling, Excel elektron jadvalida juftlik korrelyatsiya koeffitsientini qanday qilib to'g'ri chizish kerakligini aniqlaylik.

Excelda juft korrelyatsiya koeffitsientini hisoblash

Masalan, sizda x va y qiymatlari mavjud.

X - bog'liq o'zgaruvchi va y - mustaqil. Bu ko'rsatkichlar orasidagi munosabatlarning yo'nalishi va kuchini topish kerak. Bosqichma-bosqich ko'rsatma:

1. Funktsiyadan foydalanib o'rtacha qiymatlarni topamiz YURAK.

2. Keling, har birini hisoblaylik х и xavg, у и avg «-» operatori yordamida.

3. Hisoblangan farqlarni ko'paytiramiz.

4. Ushbu ustundagi ko'rsatkichlar yig'indisini hisoblaymiz. Numerator topilgan natijadir.

5. Farqning maxrajlarini hisoblang х и x-o'rtacha, y и y-o'rta. Buning uchun kvadratlashtirishni amalga oshiramiz.

6. Funktsiyadan foydalanish AVTOZUMMA, olingan ustunlardagi ko'rsatkichlarni toping. Biz ko'paytirishni qilamiz. Funktsiyadan foydalanish ROOT natijani kvadratga aylantiring.

7. Biz maxraj va numerator qiymatlari yordamida qismni hisoblaymiz.

8. CORREL - murakkab hisob-kitoblarni oldini olish imkonini beruvchi integral funksiya. Biz "Funktsiya ustasi" ga o'tamiz, CORREL-ni tanlang va ko'rsatkichlar massivlarini belgilang. х и у. Olingan qiymatlarni aks ettiruvchi grafik tuzamiz.

Excelda juft korrelyatsiya koeffitsientlari matritsasi

Juftlangan matritsalar koeffitsientlarini qanday hisoblashni tahlil qilaylik. Masalan, to'rtta o'zgaruvchidan iborat matritsa mavjud.

Bosqichma-bosqich ko'rsatma:

1. Biz "Ma'lumotlar" yorlig'ining "Tahlil" blokida joylashgan "Ma'lumotlarni tahlil qilish" ga o'tamiz. Ko'rsatilgan ro'yxatdan Korrelyatsiya ni tanlang.
2. Biz barcha kerakli sozlamalarni o'rnatdik. "Kirish oralig'i" - barcha to'rtta ustunlar oralig'i. "Chiqish oralig'i" - biz jami ko'rsatmoqchi bo'lgan joy. Biz "OK" tugmasini bosamiz.
3. Tanlangan joyda korrelyatsiya matritsasi qurilgan. Qator va ustunning har bir kesishmasi korrelyatsiya koeffitsienti hisoblanadi. Koordinatalar mos kelganda 1 raqami ko'rsatiladi.

Excelda munosabatlar va korrelyatsiyani aniqlash uchun CORREL funktsiyasi

CORREL – 2 massiv orasidagi korrelyatsiya koeffitsientini hisoblash uchun foydalaniladigan funksiya. Keling, ushbu funktsiyaning barcha qobiliyatlarining to'rtta misolini ko'rib chiqaylik.

Excelda CORREL funksiyasidan foydalanishga misollar

Birinchi misol. Bu yerda kompaniya xodimlarining o‘n bir yil davomidagi o‘rtacha ish haqi va dollar kursi haqidagi ma’lumotlar yozilgan lavha bor. Bu ikki miqdor o'rtasidagi bog'liqlikni aniqlash kerak. Jadval quyidagicha ko'rinadi:

Hisoblash algoritmi quyidagicha ko'rinadi:

Ko'rsatilgan ball 1 ga yaqin. Natija:

Harakatlarning natijaga ta'sirining korrelyatsiya koeffitsientini aniqlash

Ikkinchi misol. Ikki ishtirokchi o'n besh kunlik reklama bo'yicha yordam so'rab ikki xil agentlikka murojaat qildi. Har kuni ijtimoiy so'rov o'tkazilib, har bir murojaat etuvchining qo'llab-quvvatlash darajasini aniqladi. Har qanday suhbatdosh ikkita ariza beruvchidan birini tanlashi yoki barchasiga qarshi chiqishi mumkin. Har bir reklama aktsiyalari murojaat etuvchilarni qo'llab-quvvatlash darajasiga qanchalik ta'sir qilganligini, qaysi kompaniya samaraliroq ekanligini aniqlash kerak.

Quyidagi formulalar yordamida korrelyatsiya koeffitsientini hisoblaymiz:

• =CORREL(A3:A17; B3:B17).
• =CORREL(A3:A17;C3:C17).

natijalar:

Olingan natijalardan ma'lum bo'ladiki, 1-abituriyentni qo'llab-quvvatlash darajasi reklama tashviqotining har bir kuni bilan ortib boradi, shuning uchun korrelyatsiya koeffitsienti 1-ga yaqinlashadi. Reklama ishga tushirilganda, boshqa arizachiga katta ishonch bor edi va 5 kun ichida ijobiy tendentsiya kuzatildi. Keyin ishonch darajasi kamaydi va o'n beshinchi kunga kelib u dastlabki ko'rsatkichlardan pastga tushdi. Past ballar ko'tarilish qo'llab-quvvatlashga salbiy ta'sir ko'rsatganligini ko'rsatadi. Shuni unutmangki, jadval shaklida hisobga olinmagan boshqa qo'shma omillar ham ko'rsatkichlarga ta'sir qilishi mumkin.

Video ko'rishlar va repostlar nisbati bo'yicha kontent ommabopligini tahlil qilish

Uchinchi misol. YouTube videoxostingida o'z videolarini targ'ib qilish uchun odam kanalni reklama qilish uchun ijtimoiy tarmoqlardan foydalanadi. U ijtimoiy tarmoqlardagi repostlar soni va kanaldagi tomoshalar soni o‘rtasida qandaydir bog‘liqlik borligini payqaydi. Elektron jadval vositalaridan foydalanib, kelajakdagi ish faoliyatini bashorat qilish mumkinmi? Repostlar soniga qarab video ko'rishlar sonini bashorat qilish uchun chiziqli regressiya tenglamasini qo'llashning asosliligini aniqlash kerak. Qiymatlar bilan jadval:

Endi quyidagi formula bo'yicha 2 ko'rsatkich o'rtasida bog'liqlik mavjudligini aniqlash kerak:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"Kuchli to'g'ridan-to'g'ri munosabat";"Kuchli teskari munosabat");"Zaif yoki aloqa yo'q")' class='formula'>

Olingan koeffitsient 0,7 dan yuqori bo'lsa, chiziqli regressiya funktsiyasidan foydalanish maqsadga muvofiqdir. Ushbu misolda biz shunday qilamiz:

Endi biz grafik quramiz:

200, 500 va 1000 ta aktsiyadagi ko'rishlar sonini aniqlash uchun ushbu tenglamani qo'llaymiz: =9,2937*D4-206,12. Biz quyidagi natijalarni olamiz:

vazifa Bashorat Agar, masalan, ikki yuz ellik repost mavjud bo'lsa, hozirda ko'rishlar sonini aniqlash imkonini beradi. Biz murojaat qilamiz: 0,7; PREDICTION(D7;B3:B8;A3:A8);”Qiymatlar o'zaro bog'liq emas”)' class='formula'>. Biz quyidagi natijalarni olamiz:

Excelda CORREL funksiyasidan foydalanish xususiyatlari

Ushbu funktsiya quyidagi xususiyatlarga ega:

1. Bo'sh hujayralar hisobga olinmaydi.
2. Mantiqiy va matn turi ma'lumotlarini o'z ichiga olgan katakchalar hisobga olinmaydi.
3. Ikki marta inkor "-" raqamlar ko'rinishidagi mantiqiy qiymatlarni hisobga olish uchun ishlatiladi.
4. O'rganilayotgan massivlardagi hujayralar soni mos kelishi kerak, aks holda #N/A xabari ko'rsatiladi.

Korrelyatsiya koeffitsientining statistik ahamiyatini baholash

Korrelyatsiya koeffitsientining ahamiyatini tekshirishda nol gipoteza ko'rsatkichi 0 qiymatiga ega, muqobil esa yo'q. Tasdiqlash uchun quyidagi formuladan foydalaniladi:

Xulosa

Elektron jadvaldagi korrelyatsiya tahlili oddiy va avtomatlashtirilgan jarayondir. Uni amalga oshirish uchun siz faqat kerakli vositalar qaerda joylashganligini va ularni dastur sozlamalari orqali qanday faollashtirishni bilishingiz kerak.