कोर्स
Google Sheets में Statistics परिचय
4 घंटा
48.3K
आम तौर पर, जब पहली बार Excel सिखाया जाता है, तो अक्सर VLOOKUP को डेटा रैंगलिंग की प्राथमिक विधि के रूप में पेश किया जाता है। VLOOKUP वास्तव में एक शक्तिशाली फ़ंक्शन है जो डेटा हैंडलिंग से संबंधित कई कार्यों को संभाल सकता है, जैसा कि स्प्रेडशीट्स में VLOOKUP के साथ डेटा रैंगलिंग में दिखाया गया है। उसी ट्यूटोरियल में, हमने INDEX-MATCH संयोजन की बुनियादी बातें भी कवर की थीं, जो वर्टिकल टेबल लुकअप करने की एक अन्य विधि है और अधिक जटिल फ़ॉर्मूला की कीमत पर VLOOKUP पर कई लाभ देती है।
इन लाभों में निम्नलिखित विशेषताएँ शामिल हैं:
- डायनेमिक कॉलम संदर्भ: ये आपको अपने डेटासेट से कॉलम सुरक्षित रूप से जोड़ने और हटाने की अनुमति देते हैं, बिना लुकअप फ़ंक्शन को प्रभावित किए।
- बड़े डेटासेट में उच्च प्रोसेसिंग गति: यदि आपकी टेबलें बड़ी हैं, तो आप संभवतः देखेंगे कि INDEX-MATCH, VLOOKUP की तुलना में काफी तेज है क्योंकि INDEX-MATCH केवल उस कॉलम और लुकअप कॉलम की परवाह करता है जिसकी आपको आवश्यकता है।
- लुकअप मानों का स्थान: VLOOKUP टेबल रेंज में आपके द्वारा चुने गए पहले कॉलम के बाएँ स्थित किसी भी मान को नहीं देख सकता, लेकिन INDEX-MATCH क्षैतिज रूप से भी देख सकता है, इसलिए उसमें यह सीमा नहीं है।
- लुकअप मान का सीमित आकार: VLOOKUP 255 वर्णों से लंबे मान नहीं देख सकता, लेकिन INDEX-MATCH कर सकता है।
इसके अलावा, INDEX-MATCH कई कॉलमों के मान के आधार पर मिलान करने और केस-सेंसिटिव मिलान करने की भी अनुमति देता है, जो तब बहुत उपयोगी होता है जब आपको ऐसे कुछ IDs से निपटना पड़ता है जो छोटे-बड़े अक्षरों के आधार पर अलग हो सकते हैं। यही कारण है कि यह ट्यूटोरियल INDEX-MATCH संयोजन की उन्नत विशेषताओं में और गहराई से जाने और यह दिखाने का लक्ष्य रखेगा कि ये विशेषताएँ कैसे काम आती हैं।
INDEX-MATCH की बुनियादी बातें
अपने सबसे सरल रूप में, INDEX-MATCH का उपयोग VLOOKUP की तरह ही किया जा सकता है—समान कुंजी के आधार पर साधारण वर्टिकल टेबल लुकअप करने के लिए। फ़ॉर्मूला की बुनियादी संरचना इस प्रकार देखी जा सकती है:
=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))
- column_range: वह कॉलम रेंज जिसका मान आप प्राप्त करना चाहते हैं।
- lookup_value: वह मान जिसे आप ढूंढना चाहते हैं (कुंजी)।
- lookup_column_range: वह कॉलम रेंज जिसमें वे कुंजियाँ हैं जिन्हें आप मिलाना चाहते हैं।
- match_type: 0 या 1 में से एक। 0 का अर्थ सटीक मिलान और 1 का अर्थ लगभग मिलान। यह VLOOKUP में FALSE/TRUE फ्लैग के समान है।
अब, चलिए दो छोटे उदाहरणी टेबलों का उपयोग करके देखते हैं कि INDEX-MATCH कैसे काम करता है। पहली टेबल में कुछ नाम और कुछ यूनिक कुंजियाँ हैं। दूसरी टेबल में वही कुंजियाँ हैं (ज़रूरी नहीं कि उसी क्रम में) और कुछ विवरण हैं। इसलिए, कार्य यह है कि दूसरी टेबल के विवरणों को INDEX-MATCH का उपयोग करके पहली टेबल के नामों के साथ जोड़ा जाए। टेबलें नीचे दिखाई गई हैं:
 |
 |
जैसा कि आप देखते हैं, पहली टेबल में विवरण नहीं है। अब INDEX-MATCH का उपयोग करके नाम को उसके विवरण के साथ जोड़ते हैं:
आसान है, है न? जैसा कि वीडियो में दिखाया गया है, पहला चरण है INDEX फ़ंक्शन के अंदर वह column_range चुनना जिसमें वह डेटा है जिसे हमें प्राप्त करना है। इस मामले में, वह Description (M2:M8) है। दूसरा चरण है MATCH फ़ंक्शन के अंदर lookup_value चुनना, यानी सेल B2। अंत में, हम MATCH फ़ंक्शन के अंदर lookup_column_range (L2:L8) जोड़ते हैं, और match_type के रूप में 0 चुनते हैं क्योंकि हमें lookup_value का सटीक मिलान चाहिए। बाकी बस ENTER दबाना और फ़ॉर्मूला को नीचे की ओर ड्रैग करना है ताकि अंततः नाम को विवरणों के साथ जोड़ा जा सके।
INDEX-MATCH का उपयोग VLOOKUP की तरह अलग शीट्स पर मौजूद टेबलों को जोड़ने के लिए भी किया जा सकता है। एक उदाहरण वीडियो नीचे देखा जा सकता है:
नोट: आपने देखा होगा कि वीडियो में मैं जिन कॉलम रेंजों का चयन करता हूँ, वे $ चिह्नों से घिरी होती हैं। यह इसलिए किया जाता है ताकि फिक्स्ड रेंज चुनी जा सके जो फ़ॉर्मूला को वर्टिकल या हॉरिज़ॉन्टल ड्रैग करने पर बदलें नहीं। मैं इस ट्यूटोरियल में इसी संकेतांकन का उपयोग करता रहूँगा।
अब तक यह सब कुछ हद तक साधारण VLOOKUP जैसा ही व्यवहार करता है, बस थोड़ी अधिक मेहनत के साथ। फिर भी, जैसा कि इस ट्यूटोरियल की शुरुआत में बताया गया, यदि आप INDEX-MATCH का रास्ता अपनाते हैं तो कई फायदे हैं। इनमें से सबसे उपयोगी है डायनेमिक कॉलम संदर्भ। यानी, उस टेबल रेंज में कॉलम जोड़ने की अनुमति देना जहाँ आप मान देख रहे हैं, बिना स्वयं लुकअप को प्रभावित किए।
यदि हम अपने उदाहरण में एक नया कॉलम जोड़ते और हम VLOOKUP का उपयोग कर रहे होते, तो हमें बाएँ टेबल में फ़ॉर्मूला फिर से लिखना पड़ता ताकि विवरण वाले कॉलम को संदर्भित किया जा सके, जो अब कॉलम 3 बन जाता। लेकिन यदि आप INDEX-MATCH का उपयोग करते हैं, जिसमें डायनेमिक कॉलम संदर्भ होते हैं, तो यह आवश्यक नहीं है। यह तुलना नीचे दिए गए वीडियो में देखी जा सकती है:
उपरोक्त उदाहरण दिखाता है कि जब नया कॉलम, जिसमें स्पेसशिप के प्रकार थे, जोड़ा गया तो INDEX-MATCH का उपयोग करके लुकअप करने वाला कॉलम अपने आप समायोजित हो गया, जबकि VLOOKUP वाला कॉलम नहीं हुआ। बड़े Excel टेबलों—जैसे किसी बिज़नेस सेल्स रिपोर्ट—में लुकअप वाले कार्यों में INDEX-MATCH का यह लाभ वास्तव में चमकता है। दूसरे शब्दों में, मान लीजिए आपके पास Excel में कई बिज़नेस डैशबोर्ड हैं जो सभी एक ही डेटा टेबल को संदर्भित करते हैं। यदि आप VLOOKUP इस्तेमाल कर रहे हैं और एक दिन बीच में कोई नया कॉलम जोड़ना आवश्यक हो जाता है, तो आपको उन सभी डैशबोर्ड में VLOOKUP फ़ंक्शन कॉल बदलने होंगे जो उस टेबल पर निर्भर हैं। जबकि, यदि आपने डैशबोर्ड INDEX-MATCH को लुकअप विधि के रूप में उपयोग करके बनाए हैं, तो आपको ऐसी समस्या का सामना नहीं करना पड़ेगा।
एडवांस्ड INDEX-MATCH: केस-सेंसिटिव मिलान
डायनेमिक कॉलम संदर्भ उपयोगी हैं, लेकिन INDEX-MATCH की खूबियाँ यहीं तक सीमित नहीं हैं। उदाहरण के लिए, INDEX-MATCH की मूल सिंटैक्स को बढ़ाकर केस-सेंसिटिव लुकअप करना संभव है। खासकर, हम यह हासिल करने के लिए MATCH फ़ंक्शन के अंदर EXACT फ़ंक्शन जोड़ेंगे। विस्तृत सिंटैक्स नीचे देखी जा सकती है:
{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}
- data_range: रुचि का टेबल रेंज, जिसमें लुकअप कॉलम और इच्छित कॉलम शामिल हों जिन्हें आप प्राप्त करना चाहते हैं।
- lookup_value: वह मान जिसे आप ढूंढना चाहते हैं (कुंजी)
- lookup_column_range: वह कॉलम रेंज जिसमें वे कुंजियाँ हैं जिन्हें आप मिलाना चाहते हैं
- match_type: 0 या 1 में से एक। 0 का अर्थ सटीक मिलान और 1 का अर्थ लगभग मिलान। यह VLOOKUP में FALSE/TRUE फ्लैग के समान है
- desired_column_number: VLOOKUP में कॉलम इंडेक्स नंबर जैसा ही। यह उस कॉलम का इंडेक्स है जिसमें वह डेटा है जिसे आप पाना चाहते हैं।
इस संशोधित INDEX-MATCH संयोजन का उपयोग करके केस-सेंसिटिव मिलान तब बहुत उपयोगी होता है जब उन कुंजियों का मिलान करना हो जिनमें अक्षर होते हैं और जिनका अंतर छोटे-बड़े अक्षरों से होता है (उदा. आपके पास दो अलग कुंजियाँ हैं—"00567UUp" और "00567UUP")। ऐसी स्थितियाँ, उदाहरण के लिए, व्यापक रूप से उपयोग किए जाने वाले Salesforce CRM में Account IDs या Sales Opportunities IDs का मिलान करते समय मिल सकती हैं। अभी के लिए, हम कोई Salesforce डेटासेट नहीं इस्तेमाल करेंगे, बल्कि ऊपर दिए फ़ॉर्मूले के काम करने को दिखाने के लिए नीचे दिए वीडियो में अपने छोटे उदाहरणी टेबलों का संशोधित संस्करण इस्तेमाल करेंगे।
नोट: ऊपर दिए फ़ंक्शन संयोजन को घुंघराले कोष्ठकों में रखा गया है। इसका अर्थ है कि यह एक array formula है और इसे दर्ज करने के लिए Cntr+Shift+Enter दबाना आवश्यक है।
जैसा कि आप देखते हैं, इस फ़ॉर्मूले की सिंटैक्स लिखने में लंबी हो सकती है, लेकिन परिणाम वही आते हैं जिनकी आप अपेक्षा करते हैं। यह सामान्य INDEX-MATCH की तरह ही मानों को देखता है, लेकिन अक्षरों के केस को ध्यान में रखते हुए। यहाँ जादू EXACT फ़ंक्शन करता है जो TRUE और FALSE की एक array लौटाता है। जहाँ भी इस array में TRUE है, आप कह सकते हैं कि वहाँ बिल्कुल वही केस वाला मान मौजूद है। इसके बाद, MATCH फ़ंक्शन पहले TRUE के स्थान को प्राप्त कर लेता है।
कृपया ध्यान दें कि जब आप फ़ॉर्मूला लिखना पूरा करें तो Cntrl+Shift+Enter दबाना अत्यंत महत्वपूर्ण है, नहीं तो आपको एक #N/A त्रुटि मिलेगी जिसका निदान करना कठिन हो सकता है।
एडवांस्ड INDEX-MATCH: अनेक मानदंडों के आधार पर मिलान
Excel में केस-सेंसिटिव लुकअप काफ़ी बढ़िया है, लेकिन एक सुविधा इससे भी बेहतर है; ऐसे लुकअप जो अनेक मानदंडों को ध्यान में रखते हैं। अनेक मानदंडों को ध्यान में रखना आमतौर पर COUNTIFS या SUMIFS जैसी कंडीशनल काउंट या सम फ़ंक्शनों में किया जाता है, लेकिन Excel में लुकअप फ़ंक्शनों का हिस्सा के रूप में इसे देखना थोड़ा दुर्लभ है। फिर भी, यह कुछ स्थितियों में फ़ायदेमंद हो सकता है। मसलन, यदि आपके पास HR का Excel फ़ाइल है और आप किसी कर्मचारी का वेतन उसके पहले और आखिरी नाम—दोनों—के आधार पर ढूंढना चाहते हैं। यदि आप केवल एक कॉलम का ध्यान रखते हैं, तो संभव है कि आपको गलत कर्मचारी का वेतन मिल जाए क्योंकि एक ही पहले नाम या आखिरी नाम वाले कई लोग हो सकते हैं। लेकिन पहले और आखिरी दोनों नामों के आधार पर मान प्राप्त करने पर सही व्यक्ति मिलने की संभावना कहीं अधिक होती है।
अब, कई मानदंडों को ध्यान में रखते हुए INDEX-MATCH की संरचना देखते हैं:
{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}
अधिकांशतः, फ़ॉर्मूला के घटक सामान्य INDEX-MATCH जैसे ही हैं, लेकिन अब यह अपनी संबंधित संख्या के लुकअप रेंजों के साथ $n$ लुकअप मानों को ध्यान में रख सकता है। यदि आपको इन घटकों का पुनर्स्मरण चाहिए, तो आप ऊपर मूल INDEX-MATCH सिंटैक्स तक स्क्रॉल कर सकते हैं। MATCH फ़ंक्शन में "1" होने का कारण यह है कि यह फ़ॉर्मूला 1 और 0 की ऐसी arrays बनाता है जो प्रत्येक वांछित मानदंड के मिलान का प्रतिनिधित्व करती हैं। फिर यह MATCH फ़ंक्शन का उपयोग करके पहले 1 के स्थान को प्राप्त करता है।
केस-सेंसिटिव फ़ॉर्मूले की तरह, यह भी घुंघराले कोष्ठकों में है, जिसका अर्थ है कि इसे लिखकर समाप्त करते समय आपको Cntr+Shift+Enter दबाना होगा क्योंकि यह एक array formula है।
अब नीचे दिए वीडियो में यह दिखाने के लिए एक उदाहरण देखते हैं कि यह फ़ॉर्मूला व्यवहार में कैसे काम करता है—किसी दी गई रेस के और दिए गए अधिकतम वॉर्प स्पीड वाले स्पेसशिप्स का चयन करके:
Voilà! अब आपके पास ऐसे तर्कों का मिलान करने के औज़ार हैं जो कई कुंजियों पर आधारित हों। ध्यान रखें कि इस छोटे से उदाहरण में मैंने केवल दो—कुंजी और अधिकतम वॉर्प स्पीड—का उपयोग किया है, लेकिन सिद्धांततः, आप चाहें तो मानदंड जोड़ते जा सकते हैं। हमारे HR उदाहरण पर लौटें तो आप गलत कर्मचारी का वेतन मिलने की संभावना कम करने के लिए पहले और आखिरी नाम के साथ-साथ जॉब टाइटल के आधार पर भी मिलान कर सकते हैं।
निष्कर्ष
बधाई हो! अब आप INDEX-MATCH और उससे आगे—एक साधारण वर्टिकल लुकअप से भी अधिक—किए जा सकने वाले विभिन्न कार्यों से परिचित हैं। यदि किसी समय आपको जटिल Excel रिपोर्ट्स संभालने का काम सौंपा जाता है, तो यह फ़ंक्शन संयोजन संभवतः आपका सबसे अच्छा साथी बन जाएगा। बहुतों की तरह, मुझे स्कूल में VLOOKUP सिखाया गया था, लेकिन जब मैंने सच में Excel का उपयोग करना शुरू किया—जैसे Salesforce अकाउंट IDs के केस-सेंसिटिव लुकअप जैसे कार्यों के लिए—तब मुझे एहसास हुआ कि लंबी अवधि में INDEX-MATCH कहीं बेहतर विकल्प है, भले ही फ़ॉर्मूला लिखना कभी-कभी झंझटभरा और त्रुटिप्रवण हो। मैं आपको प्रोत्साहित करता हूँ कि अगली बार जब भी आपको Excel में वर्टिकल लुकअप करना हो, तो इसे आज़माएँ। कौन जाने, हो सकता है आपको यह उतना ही पसंद आ जाए जितना मुझे आया।
यदि आप और सीखना चाहते हैं, तो DataCamp का Data Analysis with Spreadsheets कोर्स देख सकते हैं।