კორელაციური ანალიზი Excel-ში. კორელაციური ანალიზის შესრულების მაგალითი

კორელაციური ანალიზი არის საერთო კვლევის მეთოდი, რომელიც გამოიყენება 1-ლი მნიშვნელობის მე-2-ზე დამოკიდებულების დონის დასადგენად. ცხრილს აქვს სპეციალური ინსტრუმენტი, რომელიც საშუალებას გაძლევთ განახორციელოთ ამ ტიპის კვლევა.

კორელაციური ანალიზის არსი

აუცილებელია ორ განსხვავებულ რაოდენობას შორის კავშირის დადგენა. სხვა სიტყვებით რომ ვთქვათ, ის ცხადყოფს, თუ რომელი მიმართულებით (პატარა / უფრო დიდი) იცვლება მნიშვნელობა მეორეში ცვლილებების მიხედვით.

კორელაციის ანალიზის მიზანი

დამოკიდებულება დგინდება, როდესაც იწყება კორელაციის კოეფიციენტის იდენტიფიცირება. ეს მეთოდი განსხვავდება რეგრესიული ანალიზისგან, რადგან არსებობს მხოლოდ ერთი ინდიკატორი, რომელიც გამოითვლება კორელაციის გამოყენებით. ინტერვალი იცვლება +1-დან -1-მდე. თუ ის დადებითია, მაშინ პირველი მნიშვნელობის მატება ხელს უწყობს მე-2-ის ზრდას. თუ უარყოფითია, მაშინ 1-ლი მნიშვნელობის ზრდა ხელს უწყობს მე-2-ის შემცირებას. რაც უფრო მაღალია კოეფიციენტი, მით უფრო ძლიერია ერთი მნიშვნელობა მეორეზე.

მნიშვნელოვანია! მე-0 კოეფიციენტზე სიდიდეებს შორის კავშირი არ არის.

კორელაციის კოეფიციენტის გამოთვლა

მოდით გავაანალიზოთ გაანგარიშება რამდენიმე ნიმუშზე. მაგალითად, არის ცხრილის მონაცემები, სადაც რეკლამების ხელშეწყობაზე დახარჯული და გაყიდვების მოცულობა აღწერილია თვეების მიხედვით ცალკე სვეტებში. ცხრილიდან გამომდინარე გავარკვევთ გაყიდვების მოცულობის დამოკიდებულების დონეს რეკლამისთვის დახარჯულ ფულზე.

მეთოდი 1: კორელაციის განსაზღვრა ფუნქციების ოსტატის მეშვეობით

CORREL - ფუნქცია, რომელიც საშუალებას გაძლევთ განახორციელოთ კორელაციის ანალიზი. ზოგადი ფორმა - CORREL(massiv1;massiv2). დეტალური ინსტრუქციები:

1. აუცილებელია შეარჩიოთ უჯრედი, რომელშიც დაგეგმილია გაანგარიშების შედეგის ჩვენება. დააწკაპუნეთ "ფუნქციის ჩასმა", რომელიც მდებარეობს ტექსტის ველის მარცხნივ, ფორმულის შესაყვანად.

2. ფუნქციის ოსტატი იხსნება. აქ თქვენ უნდა იპოვოთ კორელიდააწკაპუნეთ მასზე, შემდეგ "OK".

3. არგუმენტების ფანჯარა იხსნება. სტრიქონში "Array1" თქვენ უნდა შეიყვანოთ 1-ლი მნიშვნელობების ინტერვალების კოორდინატები. ამ მაგალითში, ეს არის გაყიდვების ღირებულების სვეტი. თქვენ უბრალოდ უნდა აირჩიოთ ყველა უჯრედი, რომელიც ამ სვეტშია. ანალოგიურად, თქვენ უნდა დაამატოთ მეორე სვეტის კოორდინატები "Array2" ხაზში. ჩვენს მაგალითში ეს არის რეკლამის ხარჯების სვეტი.

4. ყველა დიაპაზონის შეყვანის შემდეგ დააჭირეთ ღილაკს "OK".

კოეფიციენტი ნაჩვენები იყო უჯრედში, რომელიც მითითებული იყო ჩვენი მოქმედებების დასაწყისში. მიღებული შედეგია 0,97. ეს მაჩვენებელი ასახავს პირველი მნიშვნელობის მაღალ დამოკიდებულებას მეორეზე.

მეთოდი 2: კორელაციის გამოთვლა Analysis ToolPak-ის გამოყენებით

არსებობს კორელაციის დადგენის კიდევ ერთი მეთოდი. აქ გამოყენებულია ანალიზის პაკეტში ნაპოვნი ერთ-ერთი ფუნქცია. მის გამოყენებამდე საჭიროა ინსტრუმენტის გააქტიურება. დეტალური ინსტრუქციები:

1. გადადით "ფაილი" განყოფილებაში.

2. გაიხსნება ახალი ფანჯარა, რომელშიც უნდა დააჭიროთ განყოფილებას "პარამეტრები".
3. დააჭირეთ "დამატებებს".
4. ჩვენ ვპოულობთ ელემენტს "მართვა" ბოლოში. აქ თქვენ უნდა აირჩიოთ "Excel Add-ins" კონტექსტური მენიუდან და დააჭირეთ "OK".

5. გაიხსნა სპეციალური დანამატების ფანჯარა. მოათავსეთ გამშვები ნიშანი "ანალიზის პაკეტის" ელემენტის გვერდით. ჩვენ დააჭირეთ "OK".
6. გააქტიურება წარმატებით დასრულდა. ახლა მოდით გადავიდეთ დათაზე. გამოჩნდა ბლოკი "ანალიზი", რომელშიც თქვენ უნდა დააჭიროთ "მონაცემთა ანალიზს".
7. ახალ ფანჯარაში, რომელიც გამოჩნდება, აირჩიეთ "კორელაცია" ელემენტი და დააჭირეთ "OK".

8. ეკრანზე გამოჩნდა ანალიზის პარამეტრების ფანჯარა. სტრიქონში "შეყვანის ინტერვალი" აუცილებელია შეიყვანოთ ანალიზში მონაწილე აბსოლუტურად ყველა სვეტის დიაპაზონი. ამ მაგალითში ეს არის სვეტები "გაყიდვის ღირებულება" და "რეკლამის ხარჯები". გამომავალი ეკრანის პარამეტრები თავდაპირველად დაყენებულია New Worksheet-ზე, რაც ნიშნავს, რომ შედეგები გამოჩნდება სხვა ფურცელზე. სურვილისამებრ, შეგიძლიათ შეცვალოთ შედეგის გამომავალი ადგილმდებარეობა. ყველა პარამეტრის გაკეთების შემდეგ დააჭირეთ ღილაკს "OK".

საბოლოო ქულები გამოვიდა. შედეგი იგივეა რაც პირველ მეთოდში - 0,97.

მრავალჯერადი კორელაციის კოეფიციენტის განსაზღვრა და გამოთვლა MS Excel-ში

რამდენიმე სიდიდის დამოკიდებულების დონის დასადგენად გამოიყენება მრავალი კოეფიციენტი. მომავალში, შედეგები შეჯამებულია ცალკე ცხრილში, რომელსაც კორელაციის მატრიცა ეწოდება.

დეტალური სახელმძღვანელო:

1. "მონაცემების" განყოფილებაში ვპოულობთ უკვე ცნობილ "ანალიზს" ბლოკს და დააჭირეთ "მონაცემთა ანალიზს".

2. ფანჯარაში, რომელიც გამოჩნდება, დააჭირეთ "კორელაცია" ელემენტს და დააჭირეთ "OK".
3. სტრიქონში "შეყვანის ინტერვალი" ჩვენ მივდივართ წყაროს ცხრილის სამი ან მეტი სვეტის ინტერვალში. დიაპაზონი შეიძლება შეიტანოთ ხელით ან უბრალოდ აირჩიოთ ის LMB-ით და ის ავტომატურად გამოჩნდება სასურველ ხაზში. "დაჯგუფება"-ში აირჩიეთ შესაბამისი დაჯგუფების მეთოდი. "გამომავალი პარამეტრი" განსაზღვრავს ადგილს, სადაც გამოჩნდება კორელაციის შედეგები. ჩვენ დააჭირეთ "OK".

4. მზადაა! აშენდა კორელაციის მატრიცა.

წყვილი კორელაციის კოეფიციენტი Excel-ში

მოდით გაერკვნენ, თუ როგორ სწორად დავხატოთ წყვილის კორელაციის კოეფიციენტი Excel-ის ცხრილებში.

წყვილის კორელაციის კოეფიციენტის გაანგარიშება Excel-ში

მაგალითად, თქვენ გაქვთ x და y მნიშვნელობები.

X არის დამოკიდებული ცვლადი და y არის დამოუკიდებელი. აუცილებელია ამ მაჩვენებლებს შორის ურთიერთობის მიმართულების და სიძლიერის პოვნა. ნაბიჯ-ნაბიჯ ინსტრუქცია:

1. მოდით ვიპოვოთ საშუალო მნიშვნელობები ფუნქციის გამოყენებით გული.

2. მოდით გამოვთვალოთ თითოეული х и xavg, у и avg «-» ოპერატორის გამოყენებით.

3. ჩვენ ვამრავლებთ გამოთვლილ განსხვავებებს.

4. ჩვენ ვიანგარიშებთ ამ სვეტის ინდიკატორების ჯამს. მრიცხველი არის ნაპოვნი შედეგი.

5. გამოთვალეთ სხვაობის მნიშვნელები х и x-საშუალო, y и y-საშუალო. ამისათვის ჩვენ შევასრულებთ კვადრატს.

6. ფუნქციის გამოყენებით ავტოსუმაიპოვეთ ინდიკატორები მიღებულ სვეტებში. ვაკეთებთ გამრავლებას. ფუნქციის გამოყენებით ROOT კვადრატული შედეგი.

7. ჩვენ ვიანგარიშებთ კოეფიციენტს მნიშვნელისა და მრიცხველის მნიშვნელობების გამოყენებით.

8. CORREL არის ინტეგრირებული ფუნქცია, რომელიც საშუალებას გაძლევთ თავიდან აიცილოთ რთული გამოთვლები. ჩვენ მივდივართ "ფუნქციის ოსტატზე", ვირჩევთ CORREL და მიუთითეთ ინდიკატორების მასივები х и у. ვაშენებთ გრაფიკს, რომელიც აჩვენებს მიღებულ მნიშვნელობებს.

წყვილთა კორელაციის კოეფიციენტების მატრიცა Excel-ში

მოდით გავაანალიზოთ როგორ გამოვთვალოთ დაწყვილებული მატრიცების კოეფიციენტები. მაგალითად, არსებობს ოთხი ცვლადის მატრიცა.

ნაბიჯ-ნაბიჯ ინსტრუქცია:

1. ჩვენ მივდივართ "მონაცემთა ანალიზში", რომელიც მდებარეობს "მონაცემების" ჩანართის "ანალიზის" ბლოკში. აირჩიეთ კორელაცია სიიდან, რომელიც გამოჩნდება.
2. ჩვენ ვაყენებთ ყველა საჭირო პარამეტრს. "შეყვანის ინტერვალი" - ოთხივე სვეტის ინტერვალი. "გამომავალი ინტერვალი" - ადგილი, სადაც გვინდა გამოვაჩინოთ ჯამები. ჩვენ დააჭირეთ ღილაკს "OK".
3. არჩეულ ადგილას აშენდა კორელაციური მატრიცა. მწკრივისა და სვეტის ყოველი გადაკვეთა არის კორელაციის კოეფიციენტი. რიცხვი 1 გამოჩნდება, როდესაც კოორდინატები ემთხვევა.

CORREL ფუნქცია Excel-ში ურთიერთობისა და კორელაციის დასადგენად

CORREL - ფუნქცია, რომელიც გამოიყენება ორ მასივს შორის კორელაციის კოეფიციენტის გამოსათვლელად. მოდით შევხედოთ ამ ფუნქციის ყველა შესაძლებლობის ოთხ მაგალითს.

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;"ძლიერი პირდაპირი ურთიერთობა";"ძლიერი ინვერსიული ურთიერთობა");"სუსტი ან არანაირი კავშირი")' class='formula'>

თუ მიღებული კოეფიციენტი 0,7-ზე მეტია, მაშინ უფრო მიზანშეწონილია გამოიყენოთ ხაზოვანი რეგრესიის ფუნქცია. ამ მაგალითში ჩვენ ვაკეთებთ:

ახლა ჩვენ ვაშენებთ გრაფიკს:

ჩვენ ვიყენებთ ამ განტოლებას 200, 500 და 1000 გაზიარებაზე ნახვების რაოდენობის დასადგენად: =9,2937*D4-206,12. ჩვენ ვიღებთ შემდეგ შედეგებს:

ფუნქცია წინასწარმეტყველება საშუალებას გაძლევთ განსაზღვროთ ნახვების რაოდენობა მომენტში, თუ იყო, მაგალითად, ორას ორმოცდაათი რეპოსტი. ჩვენ მივმართავთ: 0,7;PREDICTION(D7;B3:B8;A3:A8);"მნიშვნელობები არ არის დაკავშირებული")' class='ფორმულა'>. ჩვენ ვიღებთ შემდეგ შედეგებს:

Excel-ში CORREL ფუნქციის გამოყენების თავისებურებები

ამ ფუნქციას აქვს შემდეგი მახასიათებლები:

1. ცარიელი უჯრედები არ არის გათვალისწინებული.
2. ლოგიკური და ტექსტური ტიპის ინფორმაციის შემცველი უჯრედები არ არის გათვალისწინებული.
3. ორმაგი უარყოფა "-" გამოიყენება ლოგიკური მნიშვნელობების აღრიცხვისთვის რიცხვების სახით.
4. შესწავლილ მასივებში უჯრედების რაოდენობა უნდა ემთხვეოდეს, წინააღმდეგ შემთხვევაში გამოჩნდება შეტყობინება #N/A.

კორელაციის კოეფიციენტის სტატისტიკური მნიშვნელოვნების შეფასება

კორელაციის კოეფიციენტის მნიშვნელოვნების ტესტირებისას ნულოვანი ჰიპოთეზაა, რომ ინდიკატორს აქვს მნიშვნელობა 0, ხოლო ალტერნატივას არა. შემოწმებისთვის გამოიყენება შემდეგი ფორმულა:

დასკვნა

ელცხრილში კორელაციის ანალიზი მარტივი და ავტომატიზირებული პროცესია. მის შესასრულებლად, თქვენ მხოლოდ უნდა იცოდეთ სად მდებარეობს საჭირო ხელსაწყოები და როგორ გაააქტიუროთ ისინი პროგრამის პარამეტრების საშუალებით.