Excel програмын корреляцийн шинжилгээ. Корреляцийн шинжилгээ хийх жишээ

Корреляцийн шинжилгээ нь 1-р утгын 2-р утгын хамаарлын түвшинг тодорхойлох судалгааны нийтлэг арга юм. Хүснэгт нь энэ төрлийн судалгааг хэрэгжүүлэх боломжийг олгодог тусгай хэрэгсэлтэй.

Корреляцийн шинжилгээний мөн чанар

Хоёр өөр хэмжигдэхүүн хоорондын хамаарлыг тодорхойлох шаардлагатай. Өөрөөр хэлбэл, хоёр дахь өөрчлөлтөөс хамааран үнэ цэнэ нь аль чиглэлд (бага / том) өөрчлөгдөж байгааг харуулдаг.

Корреляцийн шинжилгээний зорилго

Корреляцийн коэффициентийг тодорхойлж эхлэхэд хамаарал үүсдэг. Корреляци ашиглан тооцсон ганц үзүүлэлт байдаг тул энэ арга нь регрессийн шинжилгээнээс ялгаатай. Интервал нь +1-ээс -1 болж өөрчлөгддөг. Хэрэв энэ нь эерэг бол эхний утгын өсөлт нь 2-р утгыг нэмэгдүүлэхэд хувь нэмэр оруулдаг. Хэрэв сөрөг байвал 1-р утгын өсөлт нь 2-р утгыг бууруулахад хувь нэмэр оруулдаг. Коэффициент өндөр байх тусам нэг утга нь хоёр дахь нь хүчтэй нөлөөлнө.

Чухал! 0-р коэффициент дээр хэмжигдэхүүнүүдийн хоорондын хамаарал байхгүй.

Корреляцийн коэффициентийн тооцоо

Хэд хэдэн дээж дээр тооцоололд дүн шинжилгээ хийцгээе. Жишээлбэл, зар сурталчилгааны сурталчилгаанд зарцуулсан зардал, борлуулалтын хэмжээг тус тусад нь баганад сараар тодорхойлсон хүснэгтэн мэдээлэл байдаг. Хүснэгтэд үндэслэн бид зар сурталчилгааны сурталчилгаанд зарцуулсан мөнгөнөөс борлуулалтын хэмжээ хэр хамааралтай болохыг олж мэдэх болно.

Арга 1: Функцийн шидтэний тусламжтайгаар хамаарлыг тодорхойлох

CORREL – корреляцийн шинжилгээ хийх боломжийг олгодог функц. Ерөнхий хэлбэр - CORREL(massiv1; massiv2). Нарийвчилсан заавар:

1. Тооцооллын үр дүнг харуулахаар төлөвлөж буй нүдийг сонгох шаардлагатай. Томъёог оруулахын тулд текст талбарын зүүн талд байрлах "Функц оруулах" дээр дарна уу.

2. Функцийн мастер нээгдэнэ. Эндээс та олох хэрэгтэй КОРРЕЛ, дээр нь дараад "OK" дээр дарна уу.

3. Аргументуудын цонх нээгдэнэ. "Масив1" мөрөнд та утгуудын 1 дэх интервалын координатыг оруулах ёстой. Энэ жишээнд энэ нь Борлуулалтын үнэ цэнэ багана юм. Та энэ баганад байгаа бүх нүдийг сонгох хэрэгтэй. Үүний нэгэн адил та хоёр дахь баганын координатыг "Масив2" мөрөнд нэмэх хэрэгтэй. Бидний жишээнд энэ нь Зар сурталчилгааны зардал багана юм.

4. Бүх мужийг оруулсны дараа "OK" товчийг дарна уу.

Коэффицентийг бидний үйлдлийн эхэнд заасан нүдэнд харуулав. Үр дүн нь 0,97 байна. Энэ үзүүлэлт нь эхний утгын хоёр дахь утгаас ихээхэн хамааралтай болохыг харуулж байна.

Арга 2: Analysis ToolPak ашиглан корреляцийг тооцоол

Корреляцийг тодорхойлох өөр нэг арга бий. Энд шинжилгээний багцаас олдсон функцүүдийн нэгийг ашигласан болно. Үүнийг ашиглахын өмнө та хэрэгслийг идэвхжүүлэх хэрэгтэй. Нарийвчилсан заавар:

1. "Файл" хэсэгт очно уу.

2. Шинэ цонх нээгдэх бөгөөд та "Тохиргоо" хэсгийг дарах хэрэгтэй.
3. "Нэмэлтүүд" дээр дарна уу.
4. Бид доод хэсэгт "Менежмент" гэсэн элементийг олдог. Энд та контекст цэснээс "Excel Нэмэлтүүд" -ийг сонгоод "OK" дээр дарна уу.

5. Тусгай нэмэлт цонх нээгдэв. "Шинжилгээний багц" элементийн хажууд тэмдэг тавь. Бид "OK" дээр дарна уу.
6. Идэвхжүүлэлтийг амжилттай хийлээ. Одоо Дата руу орцгооё. "Шинжилгээ" блок гарч ирэх бөгөөд та "Өгөгдлийн шинжилгээ" дээр дарах хэрэгтэй.
7. Гарч ирэх шинэ цонхонд "Харилцаа" элементийг сонгоод "OK" дээр дарна уу.

8. Дэлгэц дээр дүн шинжилгээ хийх тохиргооны цонх гарч ирэв. "Оролтын интервал" гэсэн мөрөнд шинжилгээнд оролцсон бүх баганын мужийг оруулах шаардлагатай. Энэ жишээнд эдгээр нь "Борлуулалтын үнэ цэнэ" ба "Зар сурталчилгааны зардал" гэсэн баганууд юм. Гаралтын дэлгэцийн тохиргоог эхлээд Шинэ Ажлын хуудас болгон тохируулсан бөгөөд энэ нь үр дүнг өөр хуудсан дээр харуулах болно гэсэн үг юм. Сонголтоор та үр дүнгийн гаралтын байршлыг өөрчилж болно. Бүх тохиргоог хийсний дараа "OK" дээр дарна уу.

Эцсийн оноо гарлаа. Үр дүн нь эхний аргынхтай ижил байна - 0,97.

MS Excel дээр олон корреляцийн коэффициентийн тодорхойлолт, тооцоо

Хэд хэдэн хэмжигдэхүүний хамаарлын түвшинг тодорхойлохын тулд олон коэффициент ашигладаг. Ирээдүйд үр дүнг корреляцийн матриц гэж нэрлэдэг тусдаа хүснэгтэд нэгтгэн харуулав.

Дэлгэрэнгүй гарын авлага:

1. "Өгөгдөл" хэсэгт бид аль хэдийн мэдэгдэж байсан "Шинжилгээ" блокийг олоод "Өгөгдлийн шинжилгээ" дээр дарна уу.

2. Гарч ирсэн цонхон дээр "Харилцаа" элемент дээр дараад "OK" дээр дарна уу.
3. "Оролтын интервал" гэсэн мөрөнд бид эх хүснэгтийн гурав ба түүнээс дээш баганын интервалаар явдаг. Хүрээг гараар оруулах эсвэл зүгээр л LMB-ээр сонгох боломжтой бөгөөд энэ нь хүссэн мөрөнд автоматаар гарч ирнэ. "Бүлэглэх" хэсэгт тохирох бүлэглэх аргыг сонгоно уу. "Гаралтын параметр" хэсэгт корреляцийн үр дүнг харуулах байршлыг зааж өгнө. Бид "OK" дээр дарна уу.

4. Бэлэн! Корреляцийн матрицыг бүтээсэн.

Excel дээрх хос корреляцийн коэффициент

Excel хүснэгтэд хос корреляцийн коэффициентийг хэрхэн зөв зурахыг олж мэдье.

Excel дээр хос корреляцийн коэффициентийг тооцоолох

Жишээлбэл, танд x ба y утгууд байна.

Х нь хамааралтай хувьсагч, у нь бие даасан хувьсагч юм. Эдгээр үзүүлэлтүүдийн хоорондын харилцааны чиглэл, хүчийг олох шаардлагатай. Алхам алхмаар зааварчилгаа:

1. Функцийг ашиглан дундаж утгыг олъё ЗҮРХ.

2. Тус бүрийг тооцоолъё х и xavg, у и avg "-" оператор ашиглан.

3. Бид тооцоолсон зөрүүг үржүүлдэг.

4. Бид энэ баганад байгаа үзүүлэлтүүдийн нийлбэрийг тооцоолно. Тоолуур нь олдсон үр дүн юм.

5. Зөрүүний хуваагчийг тооцоол х и x-дундаж, y и y-дунд. Үүнийг хийхийн тулд бид квадратыг гүйцэтгэх болно.

6. Функцийг ашиглах AUTOSUMMA, үр дүнгийн баганад байгаа үзүүлэлтүүдийг олох. Бид үржүүлэх ажлыг хийдэг. Функцийг ашиглах Үндэс үр дүнг квадрат.

7. Бид хуваагч ба тоологчийн утгыг ашиглан хуваалтыг тооцоолно.

8. CORREL нь нарийн төвөгтэй тооцооллоос урьдчилан сэргийлэх боломжийг олгодог нэгдсэн функц юм. Бид "Функцийн шидтэн" рүү очоод CORREL-г сонгоод индикаторуудын массивыг зааж өгнө үү. х и у. Бид олж авсан утгыг харуулсан графикийг бүтээдэг.

Excel дээрх хос корреляцийн коэффициентүүдийн матриц

Хосолсон матрицын коэффициентийг хэрхэн тооцоолох талаар дүн шинжилгээ хийцгээе. Жишээлбэл, дөрвөн хувьсагчийн матриц байдаг.

Алхам алхмаар зааварчилгаа:

1. Бид "Өгөгдөл" табын "Шинжилгээ" хэсэгт байрлах "Өгөгдлийн шинжилгээ" рүү очно. Харагдах жагсаалтаас Корреляцийг сонгоно уу.
2. Бид шаардлагатай бүх тохиргоог хийсэн. "Оролтын интервал" - бүх дөрвөн баганын интервал. "Гаралтын интервал" - нийт дүнг харуулахыг хүссэн газар. Бид "OK" товчийг дарна.
3. Сонгосон газарт корреляцийн матрицыг барьсан. Мөр ба баганын огтлолцол бүр нь корреляцийн коэффициент юм. Координатууд таарч байвал 1-ийн тоо гарч ирнэ.

Excel-д хамаарал, хамаарлыг тодорхойлох CORREL функц

CORREL – 2 массивын хоорондын хамаарлын коэффициентийг тооцоолох функц. Энэ функцын бүх чадварын дөрвөн жишээг авч үзье.

Excel дээр CORREL функцийг ашиглах жишээ

Эхний жишээ. Тус компанийн ажилчдын арван нэгэн жилийн дундаж цалин, ам.долларын ханшийн талаарх мэдээлэл бүхий хавтан бий. Энэ хоёр хэмжигдэхүүн хоорондын хамаарлыг тодорхойлох шаардлагатай. Хүснэгт дараах байдлаар харагдаж байна.

Тооцооллын алгоритм нь дараах байдалтай байна.

Үзүүлсэн оноо 1-тэй ойролцоо байна. Үр дүн:

Үйл ажиллагааны үр дүнд үзүүлэх нөлөөллийн корреляцийн коэффициентийг тодорхойлох

Хоёр дахь жишээ. Хоёр тендерт оролцогч хоёр өөр агентлагт хандаж, арван таван өдрийн турш сурталчилгаа явуулахаар тусламж хүссэн. Өдөр бүр нийгмийн санал асуулга явуулж, өргөдөл гаргагч бүрийн дэмжлэгийн түвшинг тодорхойлдог. Ямар ч ярилцлага авагч хоёр өргөдөл гаргагчийн аль нэгийг сонгох эсвэл бүгдийг нь эсэргүүцэж болно. Зар сурталчилгааны сурталчилгаа бүр өргөдөл гаргагчдад үзүүлэх дэмжлэгийн зэрэгт хэр их нөлөөлсөн, аль компани илүү үр дүнтэй болохыг тодорхойлох шаардлагатай.

Дараах томъёог ашиглан корреляцийн коэффициентийг тооцоолно.

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

үр дүн:

Хүлээн авсан үр дүнгээс харахад 1-р өргөдөл гаргагчийн дэмжлэгийн зэрэг нь сурталчилгааны өдөр бүр нэмэгдэж байгаа тул корреляцийн коэффициент 1-д ойртож байгаа нь тодорхой болсон. 5 өдөр эерэг хандлага ажиглагдаж байна. Дараа нь итгэлийн түвшин буурч, арван тав дахь өдөр нь анхны үзүүлэлтээс доогуур байв. Бага оноо нь дэвших нь дэмжлэгт сөргөөр нөлөөлсөн болохыг харуулж байна. Хүснэгт хэлбэрээр авч үзэхгүй бусад дагалдах хүчин зүйлүүд нь үзүүлэлтүүдэд нөлөөлж болохыг мартаж болохгүй.

Видео үзэх болон дахин байршуулах харьцаагаар агуулгын түгээмэл байдлын шинжилгээ

Гурав дахь жишээ. YouTube видео хостинг дээр өөрийн видеогоо сурталчлах хүн сувгийг сурталчлахын тулд нийгмийн сүлжээг ашигладаг. Нийгмийн сүлжээн дэх репостуудын тоо болон суваг дээрх үзэлтүүдийн тоо хооронд тодорхой хамаарал байгааг тэрээр анзаарчээ. Хүснэгтийн хэрэгслийг ашиглан ирээдүйн гүйцэтгэлийг урьдчилан таамаглах боломжтой юу? Репостуудын тооноос хамааран видео үзэлтийн тоог урьдчилан таамаглахын тулд шугаман регрессийн тэгшитгэлийг ашиглах үндэслэлтэй эсэхийг тодорхойлох шаардлагатай. Утга бүхий хүснэгт:

Одоо дараах томъёоны дагуу 2 үзүүлэлтийн хооронд хамаарал байгаа эсэхийг тодорхойлох шаардлагатай.

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Хүчтэй шууд хамаарал”;”Хүчтэй урвуу хамаарал”);”Сул эсвэл огт хамааралгүй”)' анги='томьёо'>

Хэрэв үр дүнгийн коэффициент 0,7-оос их байвал шугаман регрессийн функцийг ашиглах нь илүү тохиромжтой. Энэ жишээнд бид:

Одоо бид график үүсгэж байна:

200, 500, 1000 хувьцааны хандалтын тоог тодорхойлохын тулд бид энэ тэгшитгэлийг ашигладаг. =9,2937*D4-206,12. Бид дараах үр дүнг авна.

Чиг үүрэг Урьдчилсан таамаглал Жишээ нь, хоёр зуун тавин репост байсан бол тухайн үед үзсэн тоог тодорхойлох боломжийг танд олгоно. Бид өргөдөл гаргана: 0,7; ТААМАГЛАЛ(D7;B3:B8;A3:A8);”Утгууд хоорондоо хамааралгүй”)' class='formula'>. Бид дараах үр дүнг авна.

Excel-д CORREL функцийг ашиглах онцлог

Энэ функц нь дараах онцлогтой.

1. Хоосон нүдийг тооцохгүй.
2. Boolean болон Text төрлийн мэдээлэл агуулсан нүднүүдийг тооцохгүй.
3. Давхар үгүйсгэх "-" нь логик утгыг тоо хэлбэрээр тооцоход хэрэглэгддэг.
4. Судалгаанд хамрагдсан массивуудын нүднүүдийн тоо таарч байх ёстой, эс тэгвээс #N/A гэсэн мессеж гарч ирнэ.

Корреляцийн коэффициентийн статистикийн ач холбогдлын үнэлгээ

Корреляцийн коэффициентийн ач холбогдлыг шалгахдаа тэг таамаглал нь индикатор нь 0 утгатай байхад альтернатив нь үгүй. Баталгаажуулахын тулд дараах томъёог ашиглана.

Дүгнэлт

Хүснэгт дэх корреляцийн шинжилгээ нь энгийн бөгөөд автоматжуулсан процесс юм. Үүнийг хийхийн тулд та зөвхөн шаардлагатай хэрэгслүүд хаана байрлаж, програмын тохиргоогоор дамжуулан тэдгээрийг хэрхэн идэвхжүүлэхийг мэдэх хэрэгтэй.