एक्सेल में सहसंबंध विश्लेषण। सहसंबंध विश्लेषण करने का एक उदाहरण

सहसंबंध विश्लेषण एक सामान्य शोध पद्धति है जिसका उपयोग 1 पर 2 मान की निर्भरता के स्तर को निर्धारित करने के लिए किया जाता है। स्प्रैडशीट में एक विशेष टूल होता है जो आपको इस प्रकार के शोध को लागू करने की अनुमति देता है।

सहसंबंध विश्लेषण का सार

दो अलग-अलग मात्राओं के बीच संबंध निर्धारित करना आवश्यक है। दूसरे शब्दों में, यह बताता है कि दूसरे में परिवर्तन के आधार पर मूल्य किस दिशा में (छोटा / बड़ा) बदलता है।

सहसंबंध विश्लेषण का उद्देश्य

निर्भरता तब स्थापित होती है जब सहसंबंध गुणांक की पहचान शुरू होती है। यह विधि प्रतिगमन विश्लेषण से अलग है, क्योंकि सहसंबंध का उपयोग करके केवल एक संकेतक की गणना की जाती है। अंतराल +1 से -1 में बदल जाता है। यदि यह सकारात्मक है, तो पहले मान में वृद्धि दूसरे में वृद्धि में योगदान करती है। यदि ऋणात्मक है, तो पहले मान में वृद्धि दूसरे में कमी में योगदान करती है। गुणांक जितना अधिक होगा, उतना ही मजबूत एक मान दूसरे को प्रभावित करेगा।

महत्वपूर्ण! 0वें गुणांक पर, मात्राओं के बीच कोई संबंध नहीं है।

सहसंबंध गुणांक की गणना

आइए कई नमूनों पर गणना का विश्लेषण करें। उदाहरण के लिए, सारणीबद्ध डेटा है, जहां विज्ञापन प्रचार और बिक्री की मात्रा पर खर्च को अलग-अलग कॉलम में महीनों के आधार पर वर्णित किया जाता है। तालिका के आधार पर, हम विज्ञापन प्रचार पर खर्च किए गए धन पर बिक्री की मात्रा की निर्भरता के स्तर का पता लगाएंगे।

विधि 1: फ़ंक्शन विज़ार्ड के माध्यम से सहसंबंध का निर्धारण

CORREL - एक फ़ंक्शन जो आपको सहसंबंध विश्लेषण को लागू करने की अनुमति देता है। सामान्य फ़ॉर्म - कोरल (massiv1; massiv2)। विस्तृत निर्देश:

1. उस सेल का चयन करना आवश्यक है जिसमें गणना के परिणाम को प्रदर्शित करने की योजना है। सूत्र दर्ज करने के लिए टेक्स्ट फ़ील्ड के बाईं ओर स्थित "फ़ंक्शन सम्मिलित करें" पर क्लिक करें।

2. फ़ंक्शन विज़ार्ड खुलता है। यहां आपको खोजने की जरूरत है CORREL, उस पर क्लिक करें, फिर "ओके" पर।

3. तर्क विंडो खुलती है। "Array1" पंक्ति में आपको 1 मानों के अंतराल के निर्देशांक दर्ज करने होंगे। इस उदाहरण में, यह विक्रय मान स्तंभ है। आपको बस उन सभी कक्षों का चयन करना है जो इस कॉलम में हैं। इसी तरह, आपको दूसरे कॉलम के निर्देशांक को "Array2" लाइन में जोड़ना होगा। हमारे उदाहरण में, यह विज्ञापन लागत कॉलम है।

4. सभी श्रेणियों में प्रवेश करने के बाद, "ओके" बटन पर क्लिक करें।

गुणांक उस सेल में प्रदर्शित किया गया था जो हमारे कार्यों की शुरुआत में इंगित किया गया था। प्राप्त परिणाम 0,97 है। यह सूचक दूसरे पर पहले मूल्य की उच्च निर्भरता को दर्शाता है।

विधि 2: विश्लेषण टूलपैक का उपयोग करके सहसंबंध की गणना करें

सहसंबंध निर्धारित करने की एक और विधि है। यहां विश्लेषण पैकेज में पाए जाने वाले कार्यों में से एक का उपयोग किया जाता है। इसका उपयोग करने से पहले, आपको उपकरण को सक्रिय करने की आवश्यकता है। विस्तृत निर्देश:

1. "फ़ाइल" अनुभाग पर जाएं।

2. एक नई विंडो खुलेगी, जिसमें आपको "सेटिंग" अनुभाग पर क्लिक करना होगा।
3. "ऐड-ऑन" पर क्लिक करें।
4. हम नीचे "प्रबंधन" तत्व पाते हैं। यहां आपको संदर्भ मेनू से "एक्सेल ऐड-इन्स" का चयन करना होगा और "ओके" पर क्लिक करना होगा।

5. एक विशेष ऐड-ऑन विंडो खुल गई है। "विश्लेषण पैकेज" तत्व के आगे एक चेकमार्क रखें। हम "ओके" पर क्लिक करते हैं।
6. सक्रियण सफल रहा। अब डेटा पर चलते हैं। "विश्लेषण" ब्लॉक दिखाई दिया, जिसमें आपको "डेटा विश्लेषण" पर क्लिक करने की आवश्यकता है।
7. दिखाई देने वाली नई विंडो में, "सहसंबंध" तत्व का चयन करें और "ओके" पर क्लिक करें।

8. विश्लेषण सेटिंग्स विंडो स्क्रीन पर दिखाई दी। "इनपुट अंतराल" लाइन में विश्लेषण में भाग लेने वाले बिल्कुल सभी कॉलम की सीमा दर्ज करना आवश्यक है। इस उदाहरण में, ये "बिक्री मूल्य" और "विज्ञापन लागत" कॉलम हैं। आउटपुट डिस्प्ले सेटिंग्स को शुरू में न्यू वर्कशीट पर सेट किया गया है, जिसका अर्थ है कि परिणाम एक अलग शीट पर प्रदर्शित होंगे। वैकल्पिक रूप से, आप परिणाम के आउटपुट स्थान को बदल सकते हैं। सभी सेटिंग्स करने के बाद, "ओके" पर क्लिक करें।

फाइनल स्कोर खत्म हो गया है। परिणाम पहली विधि के समान है - 0,97।

एमएस एक्सेल में बहु सहसंबंध गुणांक की परिभाषा और गणना

कई मात्राओं की निर्भरता के स्तर की पहचान करने के लिए, कई गुणांक का उपयोग किया जाता है। भविष्य में, परिणामों को एक अलग तालिका में संक्षेपित किया जाता है, जिसे सहसंबंध मैट्रिक्स कहा जाता है।

विस्तृत गाइड:

1. "डेटा" अनुभाग में, हम पहले से ज्ञात "विश्लेषण" ब्लॉक ढूंढते हैं और "डेटा विश्लेषण" पर क्लिक करते हैं।

2. दिखाई देने वाली विंडो में, "सहसंबंध" तत्व पर क्लिक करें और "ओके" पर क्लिक करें।
3. "इनपुट अंतराल" लाइन में हम स्रोत तालिका के तीन या अधिक स्तंभों के लिए अंतराल में ड्राइव करते हैं। सीमा को मैन्युअल रूप से दर्ज किया जा सकता है या बस इसे एलएमबी के साथ चुन सकते हैं, और यह स्वचालित रूप से वांछित पंक्ति में दिखाई देगा। "ग्रुपिंग" में उपयुक्त ग्रुपिंग विधि का चयन करें। "आउटपुट पैरामीटर" में उस स्थान को निर्दिष्ट करता है जहां सहसंबंध परिणाम प्रदर्शित किए जाएंगे। हम "ओके" पर क्लिक करते हैं।

4. तैयार! सहसंबंध मैट्रिक्स बनाया गया था।

एक्सेल में जोड़ी सहसंबंध गुणांक

आइए जानें कि एक्सेल स्प्रेडशीट में युग्म सहसंबंध गुणांक को सही ढंग से कैसे आकर्षित किया जाए।

एक्सेल में जोड़ी सहसंबंध गुणांक की गणना

उदाहरण के लिए, आपके पास x और y मान हैं।

X आश्रित चर है और y स्वतंत्र है। इन संकेतकों के बीच संबंधों की दिशा और मजबूती का पता लगाना आवश्यक है। चरण-दर-चरण निर्देश:

1. आइए फ़ंक्शन का उपयोग करके औसत मान ज्ञात करें दिल।

2. आइए प्रत्येक की गणना करें х и xavg, у и औसत «-» ऑपरेटर का उपयोग करना।

3. हम गणना किए गए अंतरों को गुणा करते हैं।

4. हम इस कॉलम में संकेतकों के योग की गणना करते हैं। अंश पाया गया परिणाम है।

5. अंतर के हरों की गणना करें х и एक्स-औसत, वाई и वाई-माध्यम. ऐसा करने के लिए, हम स्क्वायरिंग करेंगे।

6. फ़ंक्शन का उपयोग करना ऑटोसुम्मा, परिणामी कॉलम में संकेतक खोजें। हम गुणा करते हैं। फ़ंक्शन का उपयोग करना जड़ परिणाम को चौकोर करें।

7. हम हर और अंश के मानों का उपयोग करके भागफल की गणना करते हैं।

8. CORREL एक एकीकृत फ़ंक्शन है जो आपको जटिल गणनाओं को रोकने की अनुमति देता है। हम "फ़ंक्शन विज़ार्ड" पर जाते हैं, CORREL का चयन करते हैं और संकेतकों के सरणियों को निर्दिष्ट करते हैं х и у. हम एक ग्राफ बनाते हैं जो प्राप्त मूल्यों को प्रदर्शित करता है।

एक्सेल में जोड़ीदार सहसंबंध गुणांक का मैट्रिक्स

आइए विश्लेषण करें कि युग्मित मैट्रिक्स के गुणांक की गणना कैसे करें। उदाहरण के लिए, चार चरों का एक मैट्रिक्स है।

चरण-दर-चरण निर्देश:

1. हम "डेटा विश्लेषण" पर जाते हैं, जो "डेटा" टैब के "विश्लेषण" ब्लॉक में स्थित है। दिखाई देने वाली सूची से सहसंबंध का चयन करें।
2. हम सभी आवश्यक सेटिंग्स सेट करते हैं। "इनपुट अंतराल" - सभी चार स्तंभों का अंतराल। "आउटपुट अंतराल" - वह स्थान जहाँ हम योग प्रदर्शित करना चाहते हैं। हम "ओके" बटन पर क्लिक करते हैं।
3. चुने हुए स्थान पर एक सहसंबंध मैट्रिक्स बनाया गया था। एक पंक्ति और एक स्तंभ का प्रत्येक प्रतिच्छेदन एक सहसंबंध गुणांक है। निर्देशांक मेल खाने पर नंबर 1 प्रदर्शित होता है।

एक्सेल में संबंध और सहसंबंध निर्धारित करने के लिए कोरल फ़ंक्शन

CORREL - 2 सरणियों के बीच सहसंबंध गुणांक की गणना करने के लिए उपयोग किया जाने वाला फ़ंक्शन। आइए इस फ़ंक्शन की सभी क्षमताओं के चार उदाहरण देखें।

Excel में CORREL फ़ंक्शन का उपयोग करने के उदाहरण

पहला उदाहरण। ग्यारह वर्षों के दौरान कंपनी के कर्मचारियों के औसत वेतन और $ की विनिमय दर के बारे में जानकारी के साथ एक प्लेट है। इन दो राशियों के बीच संबंध की पहचान करना आवश्यक है। तालिका इस तरह दिखती है:

गणना एल्गोरिथ्म इस तरह दिखता है:

प्रदर्शित स्कोर 1 के करीब है। परिणाम:

परिणाम पर क्रियाओं के प्रभाव के सहसंबंध गुणांक का निर्धारण

दूसरा उदाहरण। दो बोलीदाताओं ने पन्द्रह दिन की पदोन्नति में मदद के लिए दो अलग-अलग एजेंसियों से संपर्क किया। हर दिन एक सामाजिक सर्वेक्षण किया जाता था, जो प्रत्येक आवेदक के लिए समर्थन की डिग्री निर्धारित करता था। कोई भी साक्षात्कारकर्ता दो आवेदकों में से किसी एक को चुन सकता है या सभी का विरोध कर सकता है। यह निर्धारित करना आवश्यक है कि प्रत्येक विज्ञापन प्रचार ने आवेदकों के समर्थन की डिग्री को कितना प्रभावित किया, कौन सी कंपनी अधिक कुशल है।

नीचे दिए गए सूत्रों का उपयोग करके, हम सहसंबंध गुणांक की गणना करते हैं:

• =कोरल (ए 3: ए 17; बी 3: बी 17)।
• = कोरल (ए 3: ए 17; सी 3: सी 17)।

परिणाम:

प्राप्त परिणामों से, यह स्पष्ट हो जाता है कि विज्ञापन प्रचार के प्रत्येक दिन के साथ पहले आवेदक के लिए समर्थन की डिग्री में वृद्धि हुई है, इसलिए, सहसंबंध गुणांक दृष्टिकोण 1. जब विज्ञापन शुरू किया गया था, तो अन्य आवेदक के पास बड़ी संख्या में विश्वास था, और के लिए 1 दिन सकारात्मक रुझान रहा। फिर भरोसे का स्तर कम हुआ और पंद्रहवें दिन तक यह शुरुआती संकेतकों से नीचे चला गया। कम स्कोर बताते हैं कि पदोन्नति ने समर्थन को नकारात्मक रूप से प्रभावित किया है। यह मत भूलो कि अन्य सहवर्ती कारक जिन्हें सारणीबद्ध रूप में नहीं माना जाता है, वे भी संकेतकों को प्रभावित कर सकते हैं।

वीडियो व्यू और रेपोस्ट के सहसंबंध द्वारा सामग्री की लोकप्रियता का विश्लेषण

तीसरा उदाहरण। YouTube वीडियो होस्टिंग पर अपने स्वयं के वीडियो को बढ़ावा देने के लिए एक व्यक्ति चैनल का विज्ञापन करने के लिए सामाजिक नेटवर्क का उपयोग करता है। उन्होंने नोटिस किया कि सोशल नेटवर्क में रेपोस्ट की संख्या और चैनल पर देखे जाने की संख्या के बीच कुछ संबंध है। क्या स्प्रेडशीट टूल का उपयोग करके भविष्य के प्रदर्शन की भविष्यवाणी करना संभव है? रेपोस्ट की संख्या के आधार पर वीडियो दृश्यों की संख्या का अनुमान लगाने के लिए रेखीय प्रतिगमन समीकरण को लागू करने की तर्कसंगतता की पहचान करना आवश्यक है। मूल्यों के साथ तालिका:

अब नीचे दिए गए सूत्र के अनुसार 2 संकेतकों के बीच संबंध की उपस्थिति निर्धारित करना आवश्यक है:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"मजबूत सीधा संबंध";"मजबूत उलटा संबंध"); "कमजोर या कोई संबंध नहीं")' वर्ग = 'सूत्र'>

यदि परिणामी गुणांक 0,7 से अधिक है, तो रैखिक प्रतिगमन फ़ंक्शन का उपयोग करना अधिक उपयुक्त है। इस उदाहरण में, हम करते हैं:

अब हम एक ग्राफ बना रहे हैं:

200, 500 और 1000 शेयरों पर विचारों की संख्या निर्धारित करने के लिए हम इस समीकरण को लागू करते हैं: =9,2937*D4-206,12। हमें निम्नलिखित परिणाम मिलते हैं:

समारोह FORECAST आपको इस समय विचारों की संख्या निर्धारित करने की अनुमति देता है, यदि वहाँ थे, उदाहरण के लिए, दो सौ पचास रेपोस्ट। हम आवेदन करते हैं: 0,7; PREDICTION(D7;B3:B8;A3:A8);"मान संबंधित नहीं हैं")' वर्ग = 'सूत्र'>। हमें निम्नलिखित परिणाम मिलते हैं:

एक्सेल में कोरल फ़ंक्शन का उपयोग करने की विशेषताएं

इस फ़ंक्शन में निम्नलिखित विशेषताएं हैं:

1. खाली कोशिकाओं को ध्यान में नहीं रखा जाता है।
2. बूलियन और टेक्स्ट प्रकार की जानकारी वाले सेल को ध्यान में नहीं रखा जाता है।
3. दोहरे निषेध "-" का उपयोग संख्याओं के रूप में तार्किक मूल्यों के लिए किया जाता है।
4. अध्ययन की गई सरणियों में कक्षों की संख्या का मिलान होना चाहिए, अन्यथा #N/A संदेश प्रदर्शित किया जाएगा।

सहसंबंध गुणांक के सांख्यिकीय महत्व का आकलन

सहसंबंध गुणांक के महत्व का परीक्षण करते समय, शून्य परिकल्पना यह है कि संकेतक का मान 0 है, जबकि विकल्प नहीं है। सत्यापन के लिए निम्न सूत्र का उपयोग किया जाता है:

निष्कर्ष

स्प्रेडशीट में सहसंबंध विश्लेषण एक सरल और स्वचालित प्रक्रिया है। इसे करने के लिए, आपको केवल यह जानना होगा कि आवश्यक उपकरण कहाँ स्थित हैं और प्रोग्राम सेटिंग्स के माध्यम से उन्हें कैसे सक्रिय किया जाए।