Таҳлили коррелятсия дар 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: Ҳисоб кардани коррелятсия бо истифода аз 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 доред.

X тағирёбандаи вобаста аст ва y мустақил аст. Дар байни ин нишондихандахо самт ва кувваи алокаро ёфтан лозим аст. Дастури қадам ба қадам:

1. Биёед бо истифода аз функсия арзишҳои миёнаро пайдо кунем ДИЛ.

2. Биёед ҳар якро ҳисоб кунем х и xavg, у и авг бо истифода аз оператори «-».

3. Мо фарқиятҳои ҳисобшударо зиёд мекунем.

4. Мо чамъи нишондихандахои ин сутунро хисоб мекунем. Ҳисобкунанда натиҷаи пайдошуда мебошад.

5. Маҳрахҳои фарқиятро ҳисоб кунед х и х-миёна, y и y-миёна. Барои ин мо чоркунҷаро иҷро мекунем.

6. Истифодаи функсия АВТОСУММА, нишондихандахоро дар сутунхои хосилшуда пайдо кунед. Мо зарбкунӣ мекунем. Истифодаи функсия ROOT натиҷаро квадрат кунед.

7. Мо хисоротро бо истифода аз арзишҳои махраҷ ва ҳисобкунак ҳисоб мекунем.

8. CORREL функсияи ҳамгирошудаест, ки ба шумо имкон медиҳад, ки ҳисобҳои мураккабро пешгирӣ кунед. Мо ба "Ёвари функсияҳо" меравем, CORREL -ро интихоб мекунем ва массивҳои нишондиҳандаҳоро муайян мекунем х и у. Мо графикеро месозем, ки арзишҳои бадастомадаро нишон медиҳад.

Матритсаи коэффисиентҳои коррелятсияи ҷуфтӣ дар Excel

Биёед таҳлил кунем, ки чӣ тавр ҳисоб кардани коэффициентҳои матритсаҳои ҷуфтшуда. Масалан, матритсаи чор тағирёбанда мавҷуд аст.

Дастури қадам ба қадам:

1. Мо ба "Таҳлили маълумот" меравем, ки дар блоки "Таҳлил" -и ҷадвали "Маълумот" ҷойгир аст. Аз рӯйхате, ки пайдо мешавад, Коррелятсияро интихоб кунед.
2. Мо ҳама танзимоти заруриро муқаррар мекунем. «Фасраи вуруд» - фосилаи ҳамаи чор сутун. "Фасилаи баромад" - ҷойе, ки мо мехоҳем ҷамъбастҳоро нишон диҳем. Мо тугмаи "OK" -ро пахш мекунем.
3. Дар ҷои интихобшуда матритсаи коррелятсия сохта шудааст. Ҳар як буриши сатр ва сутун коэффисиенти коррелятсия мебошад. Вақте ки координатҳо мувофиқат мекунанд, рақами 1 нишон дода мешавад.

Функсияи CORREL барои муайян кардани муносибат ва коррелятсия дар Excel

CORREL – функсияе, ки барои ҳисоб кардани коэффисиенти коррелятсияи байни 2 массив истифода мешавад. Биёед чор мисоли тамоми қобилиятҳои ин функсияро дида бароем.

Намунаҳои истифодаи функсияи CORREL дар Excel

Аввалин мисол. Дар ин чо лавхае гузошта шудааст, ки дар он маълумот дар бораи маоши миёнаи коргарони корхона дар давоми ёздах сол ва курби доллар. Муносибати байни ин ду миқдорро муайян кардан лозим аст. Ҷадвал чунин менамояд:

Алгоритми ҳисобкунӣ чунин менамояд:

Натиҷаи нишондодашуда ба 1 наздик аст. Натиҷа:

Муайян кардани коэффисиенти коррелятсияи таъсири амал ба натиҷа

Мисоли дуюм. Ду довталаб ба ду агентии гуногун барои кӯмак дар пешбурди понздаҳрӯза муроҷиат карданд. Ҳар рӯз як назарсанҷии иҷтимоӣ гузаронида мешуд, ки дараҷаи дастгирии ҳар як муроҷиаткунандаро муайян мекард. Ҳар мусоҳиба метавонад яке аз ду довталабро интихоб кунад ё ба ҳама мухолифат кунад. Муайян кардан лозим аст, ки ҳар як таблиғ то чӣ андоза ба дараҷаи дастгирии довталабон таъсир расонидааст, ки кадом ширкат самараноктар аст.

Бо истифода аз формулаҳои зерин мо коэффисиенти коррелятсияро ҳисоб мекунем:

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

Натиҷаҳо:

Аз натиҷаҳои бадастомада маълум мешавад, ки дараҷаи дастгирии довталаби 1-ум бо ҳар рӯзи таблиғи таблиғот зиёд мешуд, аз ин рӯ, коэффисиенти коррелятсия ба 1 наздик мешавад. Вақте ки таблиғ оғоз шуд, довталаби дигар шумораи зиёди эътимод дошт ва барои 5 рӯз тамоюли мусбӣ вуҷуд дошт. Баъд дарачаи боварй паст шуд ва то рузи понздахум аз нишондихандахои аввала паст шуд. Холҳои паст нишон медиҳанд, ки пешбарӣ ба дастгирӣ таъсири манфӣ мерасонад. Фаромӯш накунед, ки дигар омилҳои ҳамроҳ, ки дар шакли ҷадвал ба назар гирифта намешаванд, метавонанд ба нишондиҳандаҳо таъсир расонанд.

Таҳлили маъруфияти мундариҷа аз рӯи таносуби тамошои видео ва репостҳо

Мисоли сеюм. Шахсе, ки видеоҳои худро дар видеохостинги YouTube таблиғ мекунад, барои таблиғи канал шабакаҳои иҷтимоиро истифода мебарад. Вай қайд мекунад, ки байни шумораи репостҳо дар шабакаҳои иҷтимоӣ ва шумораи тамошобинон дар канал як таносуб вуҷуд дорад. Оё бо истифода аз абзорҳои ҷадвали электронӣ иҷрои ояндаро пешгӯӣ кардан мумкин аст? Оқилона будани истифодаи муодилаи регрессияи хатиро барои пешгӯии шумораи тамошои видео вобаста ба шумораи репостҳо муайян кардан лозим аст. Ҷадвал бо арзишҳо:

Ҳоло зарур аст, ки мавҷудияти робитаи байни 2 нишондиҳанда мувофиқи формулаи зерин муайян карда шавад:

0,7;Агар(CORREL(A3:A8;B3:B8)>0,7;"Муносибати қавӣ мустақим";"Муносибати қавии баръакс");"Муносибати заиф ё тамоман нест")' class='formula'>

Агар коэффисиенти натиҷавӣ аз 0,7 зиёд бошад, он гоҳ истифодаи функсияи регрессияи хатӣ мувофиқтар аст. Дар ин мисол, мо мекунем:

Ҳоло мо як график месозем:

Мо ин муодиларо барои муайян кардани шумораи диданҳо дар 200, 500 ва 1000 саҳмияҳо истифода мебарем: =9,2937*Д4-206,12. Мо натиҷаҳои зеринро ба даст меорем:

функсия Пешгӯӣ ба шумо имкон медиҳад, ки дар айни замон шумораи диданҳоро муайян кунед, агар масалан, дусаду панҷоҳ репост вуҷуд дошта бошад. Мо муроҷиат мекунем: 0,7; ПЕШГИРӢ(D7;B3:B8;A3:A8);"Арзишҳо ба ҳам алоқаманд нестанд")' class='формула'>. Мо натиҷаҳои зеринро ба даст меорем:

Хусусиятҳои истифодаи функсияи CORREL дар Excel

Ин функсия дорои хусусиятҳои зерин аст:

1. Ҳуҷайраҳои холӣ ба назар гирифта намешаванд.
2. Чашмакҳои дорои маълумоти навъи мантиқӣ ва матн ба назар гирифта намешаванд.
3. Инкори дукаратаи "-" барои ҳисоб кардани арзишҳои мантиқӣ дар шакли рақамҳо истифода мешавад.
4. Шумораи ячейкаҳои массивҳои омӯхташуда бояд мувофиқат кунанд, вагарна паёми #N/A намоиш дода мешавад.

Арзёбии аҳамияти омории коэффисиенти коррелятсия

Ҳангоми санҷиши аҳамияти коэффисиенти коррелятсия, фарзияи нол ин аст, ки нишондиҳанда арзиши 0 дорад, дар ҳоле ки алтернативӣ не. Барои санҷиш формулаи зерин истифода мешавад:

хулоса

Таҳлили коррелятсия дар ҷадвали электронӣ раванди оддӣ ва автоматӣ мебошад. Барои иҷрои он, шумо танҳо бояд бидонед, ки асбобҳои зарурӣ дар куҷо ҷойгиранд ва чӣ гуна онҳоро тавассути танзимоти барнома фаъол созед.