Հարաբերակցության վերլուծություն Excel-ում: Հարաբերակցության վերլուծության կատարման օրինակ

Հարաբերակցության վերլուծությունը ընդհանուր հետազոտական ​​մեթոդ է, որն օգտագործվում է 1-ին արժեքի 2-րդից կախվածության մակարդակը որոշելու համար: Աղյուսակն ունի հատուկ գործիք, որը թույլ է տալիս իրականացնել այս տեսակի հետազոտություն:

Հարաբերակցության վերլուծության էությունը

Անհրաժեշտ է որոշել երկու տարբեր քանակությունների փոխհարաբերությունները: Այլ կերպ ասած, այն բացահայտում է, թե որ ուղղությամբ (փոքր / ավելի մեծ) արժեքը փոխվում է կախված երկրորդի փոփոխություններից:

Հարաբերակցության վերլուծության նպատակը

Կախվածությունը հաստատվում է, երբ սկսվում է հարաբերակցության գործակիցի նույնականացումը: Այս մեթոդը տարբերվում է ռեգրեսիոն վերլուծությունից, քանի որ կա միայն մեկ ցուցանիշ, որը հաշվարկվում է հարաբերակցության միջոցով: Ընդմիջումը փոխվում է +1-ից մինչև -1: Եթե ​​դրական է, ապա առաջին արժեքի աճը նպաստում է 2-րդի ավելացմանը։ Եթե ​​բացասական է, ապա 1-ին արժեքի աճը նպաստում է 2-րդի նվազմանը: Որքան բարձր է գործակիցը, այնքան ուժեղ է մեկ արժեքն ազդում երկրորդի վրա:

Կարեւոր. 0-րդ գործակցի դեպքում մեծությունների միջև կապ չկա։

Հարաբերակցության գործակցի հաշվարկ

Եկեք վերլուծենք հաշվարկը մի քանի նմուշների վրա։ Օրինակ, կան աղյուսակային տվյալներ, որտեղ գովազդի առաջմղման ծախսերը և վաճառքի ծավալը նկարագրված են ամիսներով առանձին սյունակներում: Աղյուսակի հիման վրա մենք պարզելու ենք վաճառքի ծավալի կախվածության մակարդակը գովազդի առաջմղման վրա ծախսված գումարից։

Մեթոդ 1. Ֆունկցիայի հրաշագործի միջոցով հարաբերակցության որոշում

CORREL – ֆունկցիա, որը թույլ է տալիս իրականացնել հարաբերակցության վերլուծություն: Ընդհանուր ձև - CORREL (massiv1; massiv2): Մանրամասն հրահանգներ.

1. Անհրաժեշտ է ընտրել այն բջիջը, որտեղ նախատեսվում է ցուցադրել հաշվարկի արդյունքը։ Բանաձևը մուտքագրելու համար սեղմեք «Տեղադրեք գործառույթը», որը գտնվում է տեքստային դաշտի ձախ կողմում:

2. Բացվում է Function Wizard-ը: Այստեղ դուք պետք է գտնեք ԱՆԿԱԽ, սեղմեք դրա վրա, այնուհետև «OK»:

3. Բացվում է փաստարկների պատուհանը: «Array1» տողում պետք է մուտքագրեք արժեքներից 1-ի միջակայքերի կոորդինատները: Այս օրինակում սա Sales Value սյունակն է: Պարզապես պետք է ընտրել բոլոր բջիջները, որոնք գտնվում են այս սյունակում: Նմանապես, դուք պետք է ավելացնեք երկրորդ սյունակի կոորդինատները «Array2» տողում: Մեր օրինակում սա Գովազդային ծախսերի սյունակն է:

4. Բոլոր միջակայքերը մուտքագրելուց հետո սեղմեք «OK» կոճակը:

Գործակիցը ցուցադրվում էր այն բջիջում, որը նշված էր մեր գործողությունների սկզբում: Ստացված արդյունքը 0,97 է։ Այս ցուցանիշը արտացոլում է առաջին արժեքի բարձր կախվածությունը երկրորդից:

Մեթոդ 2. Հաշվարկել հարաբերակցությունը՝ օգտագործելով Analysis ToolPak

Հարաբերակցությունը որոշելու մեկ այլ մեթոդ կա. Այստեղ օգտագործվում է վերլուծության փաթեթում հայտնաբերված գործառույթներից մեկը: Նախքան այն օգտագործելը, դուք պետք է ակտիվացնեք գործիքը: Մանրամասն հրահանգներ.

1. Գնացեք «Ֆայլ» բաժին:

2. Կբացվի նոր պատուհան, որում պետք է սեղմել «Կարգավորումներ» բաժինը:
3. Կտտացրեք «Հավելումներ»:
4. Ներքևում մենք գտնում ենք «Կառավարում» տարրը: Այստեղ դուք պետք է ընտրեք «Excel Add-ins» համատեքստի ընտրացանկից և սեղմեք «OK»:

5. Բացվել է հատուկ հավելումների պատուհան։ «Վերլուծական փաթեթ» տարրի կողքին դրեք ստուգանիշ: Մենք սեղմում ենք «OK»:
6. Ակտիվացումը հաջող էր: Հիմա անցնենք տվյալներին։ Հայտնվեց «Վերլուծություն» բլոկը, որում պետք է սեղմել «Տվյալների վերլուծություն»:
7. Նոր պատուհանում, որը երևում է, ընտրեք «Correlation» տարրը և սեղմեք «OK»:

8. Էկրանի վրա հայտնվեց վերլուծության կարգավորումների պատուհանը: «Մուտքի ինտերվալ» տողում անհրաժեշտ է մուտքագրել վերլուծությանը մասնակցող բացարձակապես բոլոր սյունակների տիրույթը: Այս օրինակում դրանք «Վաճառքի արժեք» և «Գովազդային ծախսեր» սյունակներն են: Արդյունքների ցուցադրման կարգավորումները սկզբում դրված են Նոր աշխատաթերթի վրա, ինչը նշանակում է, որ արդյունքները կցուցադրվեն մեկ այլ թերթիկի վրա: Ընտրովի, դուք կարող եք փոխել արդյունքի ելքային վայրը: Բոլոր պարամետրերը կատարելուց հետո սեղմեք «OK»:

Վերջնական միավորները դուրս են եկել. Արդյունքը նույնն է, ինչ առաջին մեթոդով` 0,97:

Բազմակի հարաբերակցության գործակիցի սահմանում և հաշվարկ MS Excel-ում

Մի քանի մեծությունների կախվածության մակարդակը պարզելու համար օգտագործվում են մի քանի գործակիցներ: Հետագայում արդյունքներն ամփոփվում են առանձին աղյուսակում, որը կոչվում է հարաբերակցության մատրիցա։

Մանրամասն ուղեցույց.

1. «Տվյալներ» բաժնում մենք գտնում ենք արդեն հայտնի «Վերլուծություն» բլոկը և սեղմում «Տվյալների վերլուծություն»:

2. Պատուհանում, որը երևում է, կտտացրեք «Correlation» տարրին և սեղմեք «OK»:
3. «Input interval» տողում մենք շարժվում ենք աղբյուրի աղյուսակի երեք կամ ավելի սյունակների միջակայքում: Շրջանակը կարելի է ձեռքով մուտքագրել կամ պարզապես ընտրել այն LMB-ով, և այն ինքնաբերաբար կհայտնվի ցանկալի տողում: «Խմբավորում» բաժնում ընտրեք համապատասխան խմբավորման մեթոդը: «Ելքի պարամետր»-ում նշվում է այն վայրը, որտեղ կցուցադրվեն հարաբերակցության արդյունքները: Մենք սեղմում ենք «OK»:

4. Պատրաստ. Կառուցվել է հարաբերակցության մատրիցա։

Զույգի հարաբերակցության գործակիցը Excel-ում

Եկեք պարզենք, թե ինչպես ճիշտ նկարել զույգի հարաբերակցության գործակիցը Excel աղյուսակում:

Զույգերի հարաբերակցության գործակիցի հաշվարկ Excel-ում

Օրինակ, դուք ունեք x և y արժեքներ:

X-ը կախված փոփոխականն է, իսկ y-ը անկախ փոփոխականն է: Պետք է գտնել այս ցուցանիշների փոխհարաբերությունների ուղղությունն ու ուժը։ Քայլ առ քայլ հրահանգ.

1. Եկեք գտնենք միջին արժեքները՝ օգտագործելով ֆունկցիան ՍԻՐՏ

2. Եկեք հաշվարկենք յուրաքանչյուրը х и xavg, у и ավգ օգտագործելով «-» օպերատորը:

3. Մենք բազմապատկում ենք հաշվարկված տարբերությունները:

4. Մենք հաշվարկում ենք այս սյունակի ցուցանիշների գումարը: Համարիչը գտնված արդյունքն է:

5. Հաշվիր տարբերության հայտարարները х и x-միջին, y и y-միջին. Դա անելու համար մենք կկատարենք քառակուսի:

6. Օգտագործելով գործառույթը ԱՎՏՈՍՈՒՄՄԱ, գտեք ցուցիչները ստացված սյունակներում: Մենք բազմապատկում ենք անում. Օգտագործելով գործառույթը ROOT արդյունքը քառակուսի։

7. Մենք հաշվում ենք գործակիցը` օգտագործելով հայտարարի և համարիչի արժեքները:

8. CORREL-ը ինտեգրված ֆունկցիա է, որը թույլ է տալիս կանխել բարդ հաշվարկները: Մենք գնում ենք «Function Wizard», ընտրում ենք CORREL և նշում ենք ցուցիչների զանգվածները х и у. Մենք կառուցում ենք գրաֆիկ, որը ցույց է տալիս ստացված արժեքները։

Զույգ հարաբերակցության գործակիցների մատրիցա Excel-ում

Եկեք վերլուծենք, թե ինչպես կարելի է հաշվարկել զուգակցված մատրիցների գործակիցները: Օրինակ, կա չորս փոփոխականների մատրիցա:

Քայլ առ քայլ հրահանգ.

1. Մենք գնում ենք «Տվյալների վերլուծություն», որը գտնվում է «Տվյալներ» ներդիրի «Վերլուծություն» բլոկում: Ընտրեք Հարաբերակցությունը երևացող ցանկից:
2. Մենք սահմանել ենք բոլոր անհրաժեշտ պարամետրերը: «Մուտքային միջակայք» - բոլոր չորս սյունակների միջակայքը: «Ելքի միջակայք» - այն վայրը, որտեղ մենք ցանկանում ենք ցուցադրել ընդհանուր գումարները: Մենք սեղմում ենք «OK» կոճակը:
3. Ընտրված վայրում կառուցվել է հարաբերակցության մատրիցա։ Տողի և սյունակի յուրաքանչյուր հատում հարաբերակցության գործակից է: 1 թիվը ցուցադրվում է, երբ կոորդինատները համընկնում են:

CORREL ֆունկցիա Excel-ում հարաբերությունները և հարաբերակցությունը որոշելու համար

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;"Ուժեղ ուղիղ հարաբերություն";"Ուժեղ հակադարձ հարաբերություն");"Թույլ կամ առանց հարաբերությունների")' class='formula'>

Եթե ​​ստացված գործակիցը 0,7-ից բարձր է, ապա ավելի նպատակահարմար է օգտագործել գծային ռեգրեսիոն ֆունկցիան։ Այս օրինակում մենք անում ենք.

Այժմ մենք կառուցում ենք գրաֆիկ.

Մենք կիրառում ենք այս հավասարումը 200, 500 և 1000 բաժնետոմսերի դիտումների քանակը որոշելու համար. =9,2937 * D4-206,12. Մենք ստանում ենք հետևյալ արդյունքները.

ֆունկցիա Կանխատեսում թույլ է տալիս որոշել տվյալ պահին դիտումների քանակը, եթե եղել են, օրինակ, երկու հարյուր հիսուն ռեփոստ։ Մենք դիմում ենք. 0,7; PREDICTION (D7; B3: B8; A3: A8); «Արժեքները կապված չեն»)' class='formula'>: Մենք ստանում ենք հետևյալ արդյունքները.

Excel-ում CORREL ֆունկցիայի օգտագործման առանձնահատկությունները

Այս ֆունկցիան ունի հետևյալ հատկանիշները.

1. Դատարկ բջիջները հաշվի չեն առնվում։
2. Բուլյան և տեքստային տիպի տեղեկատվություն պարունակող բջիջները հաշվի չեն առնվում:
3. Կրկնակի ժխտումը «-» օգտագործվում է թվերի տեսքով տրամաբանական արժեքները հաշվի առնելու համար:
4. Ուսումնասիրված զանգվածների բջիջների թիվը պետք է համապատասխանի, հակառակ դեպքում կցուցադրվի #N/A հաղորդագրությունը:

Հարաբերակցության գործակցի վիճակագրական նշանակության գնահատում

Հարաբերակցության գործակցի նշանակությունը ստուգելիս զրոյական վարկածն այն է, որ ցուցանիշն ունի 0 արժեք, իսկ այլընտրանքը՝ ոչ: Ստուգման համար օգտագործվում է հետևյալ բանաձևը.

Եզրափակում

Հարաբերակցության վերլուծությունը աղյուսակում պարզ և ավտոմատացված գործընթաց է: Այն իրականացնելու համար անհրաժեշտ է միայն իմանալ, թե որտեղ են գտնվում անհրաժեշտ գործիքները և ինչպես ակտիվացնել դրանք ծրագրի կարգավորումների միջոցով: