Excel में कोरिलेशन मैट्रिक्स: बनाने और व्याख्या करने का संपूर्ण मार्गदर्शन

Excel में अंतर्निहित फंक्शनों और ऐड-इन्स की उपलब्धता का अर्थ है कि अब सबसे उन्नत विश्लेषण भी Excel में संभव है। एक शुरुआती डेटा साइंस प्रोफेशनल के रूप में, विभिन्न एनालिटिक्स तकनीकों के लिए Excel से परिचित होना अनिवार्य हो गया है।

यह ट्यूटोरियल कोरिलेशन की सांख्यिकीय अवधारणा, इसके विभिन्न प्रकार, और इसके अनुप्रयोगों से परिचय कराएगा। प्रासंगिक अंतर्निहित फंक्शनों और ऐड-इन्स का परिचय देने के बाद, हम नमूना डेटा लेकर Excel कोरिलेशन मैट्रिक्स बनाएंगे, उसे विज़ुअलाइज़ करेंगे और उसका अर्थ समझेंगे।

Correlation Matrix क्या है?

Correlation एक सांख्यिकीय माप है जो बताता है कि दो या अधिक चर (variables) एक-दूसरे से किस हद तक संबंधित हैं। यह चर के बीच संबंध की ताकत और दिशा को दर्शाता है।

जब चर सहसंबद्ध होते हैं, तो इसका अर्थ है कि एक चर में बदलाव दूसरे में बदलाव से जुड़ा होता है—या तो सकारात्मक रूप से या नकारात्मक रूप से:

• सकारात्मक कोरिलेशन: जब दो चर साथ-साथ बढ़ते या घटते हैं, तो वे सकारात्मक रूप से सहसंबद्ध होते हैं। उदाहरण के लिए, कद और वज़न सामान्यतः सकारात्मक रूप से सहसंबद्ध होते हैं; जैसे-जैसे कद बढ़ता है, वज़न भी बढ़ने की प्रवृत्ति होती है।
• नकारात्मक कोरिलेशन: जब एक चर बढ़ता है जबकि दूसरा घटता है। उदाहरण के लिए, वाहन की गति और गंतव्य तक पहुँचने में लगने वाला समय; जैसे-जैसे गति बढ़ती है, समय घटता है।

Correlation coefficients संख्यात्मक माप हैं जो इस संबंध की ताकत और दिशा को मात्रात्मक रूप से व्यक्त करते हैं। इस कोरिलेशन की डिग्री को विभिन्न सांख्यिकीय औज़ारों से मापा जा सकता है, जिनमें Pearson correlation coefficient सबसे आम है।

Pearson coefficient को समझना

Pearson correlation coefficient, जिसे अक्सर संक्षेप में “Pearson’s r” कहा जाता है, दो चर 𝑋 और 𝑌 के बीच रैखिक (linear) सहसंबंध का माप है। यह इस बात को परिमाणित करता है कि इन चर के बीच संबंध को किस हद तक सीधी रेखा से वर्णित किया जा सकता है।

Pearson correlation coefficient दो चर के covariance को उनकी standard deviations के गुणनफल से भाग देकर निकाला जाता है। गणितीय रूप से, इसे इस प्रकार व्यक्त किया जाता है:

ध्यान दें कि 𝑋‾ और 𝑌‾ क्रमशः 𝑋 और 𝑌 चर के माध्य (means) हैं।

गणना किए गए coefficient का मान -1 से +1 के बीच होगा, जहाँ:

• +1 एक परिपूर्ण सकारात्मक रैखिक संबंध को दर्शाता है: जैसे-जैसे एक चर बढ़ता है, दूसरा चर भी पूर्णतः रैखिक ढंग से बढ़ता है।
• -1 एक परिपूर्ण नकारात्मक रैखिक संबंध को दर्शाता है: जैसे-जैसे एक चर बढ़ता है, दूसरा चर पूर्णतः रैखिक ढंग से घटता है।
• 0 का अर्थ है कोई रैखिक कोरिलेशन नहीं: चरों के बीच कोई रैखिक संबंध नहीं है।

दृश्य रूप में, विभिन्न प्रकार के कोरिलेशन कुछ इस तरह दिखते हैं:

कोरिलेशन का दृश्य प्रतिनिधित्व (स्रोत)

अब जबकि हमने कोरिलेशन और कोरिलेशन कोएफ़िशिएंट जैसे शब्द समझ लिए हैं, आइए समझते हैं कि यह सब एक कोरिलेशन मैट्रिक्स में कैसे एकसाथ आता है।

कोरिलेशन मैट्रिक्स एक तालिका है जो कई चरों के बीच कोरिलेशन कोएफ़िशिएंट्स दर्शाती है। मैट्रिक्स की प्रत्येक सेल दो चरों के बीच कोरिलेशन का प्रतिनिधित्व करती है।

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

इनमें से कुछ अनुप्रयोग हैं:

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

अब जब हमने कोरिलेशन, उसकी गणना, और अनुप्रयोगों की अवधारणा समझ ली है, तो आइए Excel में इसके इम्प्लिमेंटेशन में उतरते हैं।

कोरिलेशन मैट्रिक्स: Excel में प्रासंगिक फंक्शंस और ऐड-इन्स

Excel में कोरिलेशन मैट्रिक्स की गणना के लिए हम दो औज़ारों का उपयोग करेंगे: CORREL फ़ंक्शन और Analysis ToolPak ऐड-इन।

CORREL फ़ंक्शन

CORREL फ़ंक्शन Excel में दो डेटा सेट्स के बीच Pearson correlation coefficient की गणना करने का सीधा तरीका प्रदान करता है।

CORREL फ़ंक्शन का सिंटैक्स है:

CORREL(array1, array2)

जहाँ:

• array1: यह उन कोशिकाओं (cells) की रेंज है जिसमें पहला डेटासेट/कॉलम होता है।
• array2: यह उन कोशिकाओं की रेंज है जिसमें दूसरा डेटासेट/कॉलम होता है।

प्रत्येक array में तत्वों की संख्या समान होनी चाहिए। Excel इन arrays के लिए -1 से +1 के बीच का कोरिलेशन कोएफ़िशिएंट लौटाएगा।

Analysis ToolPak

अधिक व्यापक सांख्यिकीय विश्लेषण के लिए, जिनमें कई चरों पर पूर्ण कोरिलेशन मैट्रिक्स बनाना शामिल है, Excel का Analysis ToolPak उपयोगी हो सकता है।

Analysis Toolpak आपके Excel दस्तावेज़ में आवश्यक रूप से सक्षम न हो। नीचे दिखाए अनुसार “Data” टैब के दाएँ कोने में “Data Analysis” आइकन दिखाई देता है या नहीं, जाँचें।

Excel में Analysis Toolpak।

यदि आप इसे नहीं देखते हैं, तो चिंता न करें; नीचे दिए चरणों का पालन करें और आप Analysis ToolPak को सक्षम कर पाएँगे।

• मेनू से File पर क्लिक करें, फिर Options चुनें।

File टैब से Options चुनना।

• Excel Options डायलॉग बॉक्स में, Add-ins चुनें।

Excel Options डायलॉग बॉक्स से Add-ins चुनें।

• दृश्य के नीचे Manage बॉक्स में, Excel Add-ins चुनें और Go पर क्लिक करें।

Excel add-ins प्रबंधित करना।

• Add-Ins बॉक्स में, Analysis ToolPak को चेक करें और फिर OK क्लिक करें।

Analysis Toolpak सक्षम करना।

यदि आपने निर्देशों का सही से पालन कर इसे सक्षम कर लिया है, तो आपको Data टैब के Analysis समूह के अंतर्गत “Data Analysis” आइकन दिखाई देना चाहिए।

जैसा आप आगे देखेंगे, Analysis ToolPak कोरिलेशन मैट्रिक्स की गणना की प्रक्रिया को बहुत आसान और सीधा बना देता है।

Excel में Correlation Matrix कैसे बनाएँ: एक उदाहरण

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

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

आपने विश्लेषण के लिए कोरिलेशन मैट्रिक्स का उपयोग करने का निर्णय लिया है। निम्नलिखित 10 ग्राहकों का नमूना डेटा आपको एक Excel फ़ाइल में दिया गया है:

एक अग्रणी उपभोक्ता वस्तु कंपनी का नमूना डेटा।

कॉलम हैं:

• Customer ID: प्रत्येक ग्राहक के लिए विशिष्ट पहचानकर्ता।
• Age: ग्राहक की उम्र (वर्षों में)।
• Annual Income (K$): ग्राहक की वार्षिक आय (हज़ार डॉलर में)।
• Frequency of Visits (per month): ग्राहक प्रति माह स्टोर कितनी बार विज़िट करता है।
• Spend per Visit ($): ग्राहक द्वारा प्रति विज़िट खर्च की जाने वाली औसत राशि।
• Electronics ($) / Clothing ($) / Groceries ($): प्रति विज़िट विभिन्न प्रोडक्ट श्रेणियों पर खर्च की गई राशि।

CORREL फ़ंक्शन से मैन्युअल रूप से कोरिलेशन्स

Excel में Pearson correlation coefficient की गणना करने के लिए नीचे दिए चरणों का पालन करें:

• कदम 1: उस उपयुक्त सेल का चयन करें जो कोरिलेशन मैट्रिक्स का हिस्सा बनेगी। यहाँ हम B16 चुनते हैं।

कोरिलेशन निकालने के लिए एक सेल का चयन।

• कदम 2: निम्नलिखित फ़ॉर्मूला टाइप करें, जो CORREL फ़ंक्शन का उपयोग करता है। हम पहले Age और Annual Income के बीच कोरिलेशन निकालेंगे।

=CORREL(B2:B11,C2:C11)

फ़ॉर्मूला टाइप करना।

• कदम 3: एंटर दबाएँ और बाकी सभी कॉलम संयोजनों के लिए प्रक्रिया दोहराएँ।

कोरिलेशन की गणना।

जैसा आप देखते हैं, प्रत्येक कॉलम संयोजन के लिए मैन्युअल रूप से कोरिलेशन मान निकालना उबाऊ हो सकता है, खासकर जब डेटा में कॉलमों की संख्या बढ़ जाती है।

हमारे पास OFFSET फ़ंक्शन का उपयोग करने का विकल्प भी है, जिससे टेबल में कोएफ़िशिएंट मान भरने के लिए सेल रेंज में बदलाव को स्वचालित किया जा सकता है।

हालाँकि यह जानना महत्वपूर्ण है कि CORREL फ़ंक्शन मौजूद है और व्यक्तिगत कोरिलेशन्स निकालने में उपयोगी है, लेकिन जब संपूर्ण कोरिलेशन मैट्रिक्स की बात हो, तो Analysis Toolpak कहीं अधिक आसान और तेज़ है, इसलिए इसकी सिफारिश की जाती है।

Analysis Toolpak के साथ कोरिलेशन मैट्रिक्स

कोरिलेशन मैट्रिक्स बनाने के लिए इन चरणों का पालन करें:

• कदम 1: Data टैब के अंतर्गत Data Analysis आइकन पर क्लिक करें।

Data Analysis आइकन पर क्लिक करना।

• कदम 2: “Correlation” विकल्प चुनें और “OK” क्लिक करें।

Correlation विकल्प का चयन।

• कदम 3: इनपुट रेंज दें, जिसमें कॉलम के नाम भी शामिल हों। “Labels in first row” विकल्प को टिक करें। आउटपुट रेंज के लिए, कोई भी सेल चुनें जहाँ आप परिणाम दिखाना चाहते हैं, जैसे यहाँ $A$14। अंत में OK दबाएँ।

Analysis Toolpak ऐड-इन के साथ कोरिलेशन के लिए विवरण भरना।

आप नीचे दिखाए अनुसार कोरिलेशन मैट्रिक्स देख पाएँगे:

डेटा और उसका कोरिलेशन मैट्रिक्स।

किसी भी कॉलम का अपने आप से कोरिलेशन मान 1 होता है, यानी अपने आप से परिपूर्ण कोरिलेशन। मैट्रिक्स में सभी कॉलम पंक्ति में भी उपस्थित हैं, जिससे आपस में सभी संयोजन बनते हैं। मैट्रिक्स का ऊपरी-दायाँ विकर्ण खाली है, क्योंकि वह निचले-बाएँ विकर्ण के संयोजनों का हूबहू प्रतिरूप होगा।

हमने Excel में सफलतापूर्वक कोरिलेशन मैट्रिक्स बना लिया है। अब, समय है यह समझने का कि ये मान क्या दर्शाते हैं और इन संख्याओं को अंतर्दृष्टियों में कैसे बदला जाए।

Excel में Correlation Matrix की व्याख्या कैसे करें

ऊपर देखे गए कोरिलेशन मैट्रिक्स की व्याख्या करना कठिन नहीं है, लेकिन जब कॉलमों की संख्या बढ़ जाती है तो यह नियंत्रण से बाहर हो सकता है। कॉलमों की संख्या अधिक होने पर हर संयोजन से गुजरना असंभव हो जाता है।

हमें एक ऐसी तरकीब चाहिए जिससे कोरिलेशन मैट्रिक्स में सभी संभावित संयोजनों के बीच सबसे महत्वपूर्ण कोरिलेशनों की जल्दी पहचान हो सके।

कोरिलेशन मैट्रिक्स की कोशिकाओं पर कंडीशनल फ़ॉर्मैटिंग लगाने से हमारी नज़र सबसे महत्वपूर्ण कोशिकाओं (गहरे शेड्स) पर जाती है और व्याख्या सरल हो जाती है।

Microsoft Excel में कोरिलेशन मैट्रिक्स को रंग-कोड करने के लिए ये चरण अपनाएँ:

• कदम 1: कोरिलेशन मैट्रिक्स (केवल संख्याएँ) को हाईलाइट करें, और “Home” टैब के अंतर्गत Conditional formatting पर क्लिक करें। नीचे दिखाए अनुसार “New Rule” पर क्लिक करें।

“Conditional Formatting” आइकन पर क्लिक करना।

• कदम 2: कदम 1 के बाद, आपको नीचे दिए गए चित्र जैसा डायलॉग बॉक्स दिखेगा। पहले “Format all cells based on their values” चुनें और फ़ॉर्मैट स्टाइल “3-Color Scale” सेट करें। तीन रंग आदर्श हैं क्योंकि वे क्रमशः सकारात्मक, नकारात्मक और तटस्थ कोरिलेशन दर्शा सकते हैं। अंत में, Type, Value, और Color को नीचे दिए चित्र के अनुसार सेट करें।

नई कंडीशनल फ़ॉर्मैटिंग रूल बनाना।

आपको नीचे दिखाए अनुसार आउटपुट दिखाई देगा:

नमूना डेटा, रंग-कोडेड कोरिलेशन मैट्रिक्स के साथ।

बस! हमने कोरिलेशन मैट्रिक्स को बेहतर ढंग से विज़ुअलाइज़ करने के लिए रंग जोड़ दिए हैं।

एक बार जब हम दृश्य समझ के लिए कोरिलेशन मैट्रिक्स को रंग-कोड कर लेते हैं, तो हम प्राप्त परिणामों से निम्न निष्कर्ष निकाल सकते हैं:

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

कोरिलेशन मैट्रिक्स की व्याख्या की कुंजी है मैट्रिक्स में मान की दिशा और ताकत का अवलोकन करना और उससे जुड़े कॉलमों की ओर लौटकर देखना।

ध्यान दें कि अपनी व्याख्याओं में हमने कभी यह दावा नहीं किया कि अधिक उम्र के कारण ग्राहकों की वार्षिक आय अधिक होती है, या यह कि कपड़ों की अधिक बिक्री ग्रॉसरी की कम बिक्री का कारण बनेगी। ऐसा इसलिए क्योंकि कोरिलेशन कारणता (causation) नहीं है—यह एक आम गलतफ़हमी है।

हम केवल दो चरों के बीच संबंध के अस्तित्व (या अनस्तित्व) की व्याख्या कर सकते हैं, पर यह नहीं कह सकते कि एक चर दूसरे में परिवर्तन का कारण बन रहा है—सिर्फ़ कोरिलेशनल विश्लेषण से। कारणता निर्धारित करने के लिए विशिष्ट प्रयोग करने पड़ते हैं, और इसके बारे में आप अधिक जान सकते हैं हमारी Correlation vs. Causation ट्यूटोरियल से।

अंतिम विचार

इस ट्यूटोरियल में कोरिलेशन, Pearson कोएफ़िशिएंट, उसका गणितीय व्यंजक, कोरिलेशन के विभिन्न प्रकार, और कोरिलेशन कोएफ़िशिएंट्स की दिशा और ताकत के आधार पर उन्हें पहचानने के तरीकों का परिचय दिया गया।

इसके बाद हमने इम्प्लिमेंटेशन की ओर ध्यान केंद्रित किया, जहाँ हमने नमूना डेटा लिया, कंडीशनल फ़ॉर्मैटिंग के साथ उसे विज़ुअलाइज़ किया, और कोरिलेशन मैट्रिक्स की व्याख्या की। हमने कोरिलेशन मैट्रिक्स की व्याख्या करते समय होने वाली गलतफ़हमियों पर भी चर्चा की।

आपकी सीख यहीं समाप्त नहीं होनी चाहिए! यदि आप अन्य प्रोग्रामिंग भाषाओं में कोरिलेशन लागू करने में रुचि रखते हैं, तो निम्न गाइड आपके काम आएँगे:

• R Correlation ट्यूटोरियल
• Python Correlation ट्यूटोरियल
• SQL Correlation कोर्स

अपने संबंधित तकनीकी ज्ञान को मजबूत करने के लिए, हमारे शुरुआती-अनुकूल Probability and Statistics कोर्सेज़ देखें। वैकल्पिक रूप से, यदि आप तकनीकी अवधारणाओं में आश्वस्त हैं लेकिन Excel में व्यावहारिक इम्प्लिमेंटेशन कौशल सुधारना चाहते हैं, तो Excel Fundamentals ट्रैक अवश्य देखें।