{count, plural, one {कोर्स} other {कोर्स}}
इंटरमीडिएट Google Sheets
{hours} घंटा
57.9K
कभी-कभी डेटा साइंस का मतलब डीप लर्निंग, बिग डेटा या रिकमेंडेशन सिस्टम नहीं होता। कई बार, खासकर अगर आप वित्त जैसे क्षेत्र में काम करते हैं या ऐसी कंपनी में जहां अतीत में Excel पर बहुत अधिक निर्भरता रही है, तो संभव है कि आपको अक्सर Excel स्प्रेडशीट्स से काम करना पड़े। मैं स्वीकार करता हूँ कि मैं भी इसी श्रेणी में आता हूँ। मेरे नियोक्ता का Excel पर काफी भरोसा था, और इस वजह से, मुझे अपनी कुछ ज़िम्मेदारियों के लिए इसकी विभिन्न क्षमताओं के बारे में बहुत कुछ सीखना पड़ा।
भले ही R और Python कई मायनों में Excel पर स्पष्ट बढ़त रखते हैं, फिर भी Excel कुछ कार्यों, जैसे व्यावसायिक रिपोर्ट बनाना, के लिए एक शक्तिशाली उपकरण है। हालांकि, इस ट्यूटोरियल में मैं Excel के एक बिल्ट-इन फ़ंक्शन पर ध्यान केंद्रित करने जा रहा हूँ जो एक ही समय में विभिन्न Excel तालिकाओं के साथ काम करते समय बहुत मदद करता है।
जिस फ़ंक्शन की मैं बात कर रहा हूँ वह है VLOOKUP। VLOOKUP उपयोगकर्ता को एक ही शीट में या उसी वर्कबुक की अलग-अलग शीट्स के बीच अलग-अलग कॉलम्स को आपस में जोड़ने देता है। मूल रूप से, यह किसी एक कॉलम से कोई मान ढूंढने और किसी दूसरे कॉलम से उसका संबद्ध मान लौटाने में सक्षम बनाता है। यह सुविधा तब बहुत उपयोगी हो जाती है जब आपके पास बड़े डेटा टेबल हों जो सॉर्टेड न हों और आपको उन्हें किसी कॉलम (आदर्श रूप से जिसमें एक यूनिक कुंजी हो) के आधार पर जोड़ना हो।
आइए एक साधारण डेटा टेबल के साथ दिखाते हैं कि एक ही शीट के कॉलम्स के बीच VLOOKUP फ़ंक्शन का उपयोग कैसे होता है:
ऊपर के स्क्रीनशॉट में आप एक सामान्य VLOOKUP अभिव्यक्ति देख सकते हैं, जैसा कि आप किसी व्यावसायिक रिपोर्ट के लिए लिखेंगे। इसके घटक इस प्रकार हैं:
- IFERROR फ़ंक्शन: यदि कुंजी खाली हो या डेटा टेबल में मौजूद न हो, तो त्रुटि को हाइफ़न (-) से बदलने के लिए उपयोग किया जाता है। यह त्रुटि होने पर शीट को साफ-सुथरा रखने के लिए अच्छा एरर हैंडलिंग अभ्यास है, लेकिन VLOOKUP को काम कराने के लिए यह आवश्यक नहीं है।
- VLOOKUP आर्ग्युमेंट्स:
- E2 लुकअप वैल्यू है (इस मामले में हमने उदाहरण के तौर पर KOG का उपयोग किया है, लेकिन यह किसी भी कुंजी में से हो सकता है)। यह मान उस चुने हुए टेबल रेंज के बाएँ छोर के कॉलम में होना चाहिए, जिसमें आप खोज करना चाहते हैं।
- B1:C18 टेबल रेंज/ऐरे है जिसमें मान ढूंढने हैं। बाएँ छोर का कॉलम इंडेक्स 1 होता है जैसा दिखाया गया है। इस कॉलम में भी लुकअप वैल्यूज़ होनी चाहिए ताकि फ़ंक्शन काम कर सके।
- 2 चुने हुए टेबल रेंज का कॉलम इंडेक्स नंबर है, जिससे आप मान प्राप्त करना चाहते हैं। इस उदाहरण में हमें विवरण (description) चाहिए, इसलिए हम टेबल ऐरे के कॉलम 2 को चुनते हैं।
- FALSE एक फ़्लैग है जो दर्शाता है कि आपको लुकअप वैल्यू का सटीक मिलान चाहिए। यहाँ हमने FALSE चुना क्योंकि हमें KOG कुंजी का सटीक मिलान चाहिए। दूसरा विकल्प TRUE है, जो लुकअप वैल्यू का लगभग मिलान खोजता है और यदि आप यह आर्ग्युमेंट निर्दिष्ट नहीं करते तो यह डिफ़ॉल्ट होता है। सामान्यतः, कुंजियों या स्ट्रिंग्स का मिलान करते समय TRUE चुनने की सलाह नहीं दी जाती क्योंकि यह अजीब परिणाम दे सकता है।
अब, वह VLOOKUP स्टेटमेंट लिखने के बाद, अगर आप Excel में ENTER दबाएँगे, तो आपको निम्नलिखित परिणाम मिलेगा:
जैसा कि आप देख सकते हैं, VLOOKUP फ़ंक्शन ने ठीक वही लौटाया जिसकी आप अपेक्षा करते हैं—"King of Gondor", जो KOG कुंजी से जुड़ा विवरण है। यह अब उस किसी भी कुंजी के लिए सही होगा जिसे आप सेल E2 में दर्ज करेंगे। उदाहरण के लिए, देखते हैं कि अगर हम कुंजी KIN इनपुट करें तो क्या होता है।
द किंग इन द नॉर्थ! बिलकुल सही—अगर हम KIN कुंजी दर्ज करें तो हमें यही मिलना चाहिए, क्योंकि यह हमारे प्रिय Jon (Snow) के लिए दिया गया विवरण है।
हालाँकि, VLOOKUP का उपयोग केवल किसी कुंजी के आधार पर एक मान प्राप्त करने तक सीमित नहीं है। मान लीजिए कि हम उस टेबल को दो भागों में बाँट दें और दोनों को उसी वर्कबुक की अलग-अलग शीट्स पर रखें।
तालिकाओं को जोड़ना
 |
 |
जैसा कि आप देख सकते हैं, अब डेटा दो अलग-अलग शीट्स (S1 और S2) में बँटा हुआ है और दूसरी शीट (S2) की टेबल में कुंजियाँ अलग क्रम में हैं। सौभाग्य से, कुंजी वाला कॉलम क्रम की परवाह किए बिना दोनों शीट्स में समान है, इसलिए हम VLOOKUP का उपयोग करके S1 में डेटा को एक साथ जोड़ सकते हैं (इसे आप एक सिंगल-कॉलम लेफ्ट जॉइन की तरह समझ सकते हैं)। इसे हासिल करने का सिंटैक्स इस प्रकार है:
इस बार, पहला आर्ग्युमेंट, यानी लुकअप वैल्यू, B2 हो जाता है क्योंकि हम S2 की टेबल में कुंजी B2 का मिलान करना चाहते हैं। टेबल रेंज अब S2 की पूरी डेटा टेबल हो जाती है, जिसमें पहला कॉलम समान कुंजियाँ रखता है। आप देखेंगे कि टेबल रेंज में डॉलर साइन लगे हैं। यह रेंज चुनने के बाद F4 दबाकर किया जाता है और इसका उपयोग रेंज को लॉक करने के लिए होता है, ताकि जब आप S1 में फॉर्मूला नीचे ड्रैग करें, तो VLOOKUP फ़ंक्शन मूल रेंज को ही देखता रहे, वह साथ में नीचे न खिसके। कॉलम इंडेक्स नंबर 2 ही रहता है क्योंकि हमें विवरण चाहिए, जो S2 में दूसरा कॉलम है। अंत में, हमें अंतिम आर्ग्युमेंट FALSE चाहिए क्योंकि हमें कुंजियों का सटीक मिलान चाहिए।
अंतिम चरण बस इतना है कि फॉर्मूला को तालिका की आखिरी एंट्री तक नीचे की ओर ड्रैग कर दें। S1 में बनी परिणामी तालिका इस प्रकार है:
हो गया! इसी तरह, हमने VLOOKUP का उपयोग करके डेटा को फिर से जोड़ दिया। इस तरह तालिकाओं को जोड़ना व्यावसायिक सेटिंग में बहुत आम है, खासकर जब SQL, R या Python वे उपकरण नहीं हैं जिनके आदी वरिष्ठ कार्यकारी होते हैं। उदाहरण के लिए, आपके पास दो फाइलें हो सकती हैं जिनमें क्वालिफाइड सेल्स अवसरों के बारे में अलग-अलग विवरण हों, जिन्हें एक यूनिक अवसर ID का उपयोग करके एक मासिक सेल्स Excel रिपोर्ट के लिए आपस में जोड़ा जा सकता है (मेरे मामले में Excel के उपयोग का सबसे अच्छा उदाहरण)।
लगभग मिलान (Approximate Matches) का उपयोग
अब तक आपने देखा होगा कि हमने अपने किसी भी VLOOKUP फ़ंक्शन में अंतिम आर्ग्युमेंट के रूप में TRUE (यानी लगभग मिलान) का उपयोग नहीं किया। इसका कारण यह है कि हमने यूनिक कुंजियों के सटीक मिलान पर ध्यान केंद्रित किया है। हालाँकि, संख्यात्मक डेटा के साथ काम करते समय आप VLOOKUP में अंतिम आर्ग्युमेंट के रूप में TRUE का सुरक्षित रूप से उपयोग कर सकते हैं।
उदाहरण के लिए, मान लीजिए हम मछली बेचते हैं, और हम ग्राहकों को निम्न छूट देते हैं जब वे कुछ निश्चित मात्रा खरीदते हैं (अगर कोई पूछे कि मछली क्यों, तो सरल है—मछलियाँ बेहतरीन होती हैं!).
इस बार सेल F2 के अंदर VLOOKUP फ़ंक्शन है:
एक बार जब आप वह फॉर्मूला लगा देते हैं, तो छूट तालिका के थ्रेसहोल्ड्स के सबसे नज़दीकी मान के अनुसार तय हो जाएगी। उदाहरण के लिए, अगर हम 25 मछलियाँ खरीदते हैं तो हमारी छूट 5% होगी, अगर हम 70 मछलियाँ खरीदते हैं तो हमारी छूट 10% होगी, और अगर हम 120 खरीदते हैं, तो हमारी छूट 15% होगी। ये सभी उदाहरण नीचे देखे जा सकते हैं:
 |
 |
 |
INDEX-MATCH का परिचय
VLOOKUP अलग-अलग कॉलम्स को जोड़ने के लिए एक शक्तिशाली फ़ंक्शन है। हालाँकि, इसे करने का एक वैकल्पिक तरीका भी है: INDEX-MATCH संयोजन। VLOOKUP एक एकल फ़ंक्शन है, जबकि INDEX-MATCH दो फ़ंक्शनों का संयोजन है, और लिखने में थोड़ा अधिक जटिल होने के बावजूद, इसके मानक VLOOKUP पर कई फायदे हैं।
इस ट्यूटोरियल के उद्देश्य से, हम एक सरल उदाहरण देखेंगे जहाँ हम INDEX-MATCH का उपयोग करके दो तालिकाओं को एक सामान्य कुंजी के आधार पर जोड़ते हैं, जैसा कि हमने ऊपर VLOOKUP के साथ किया, और इसके सिंटैक्स की व्याख्या करेंगे (नीचे देखें)।
INDEX-MATCH संयोजन के घटक इस प्रकार हैं:
- INDEX आर्ग्युमेंट्स:
- M2:M18: यह वह कॉलम रेंज है जिनके मान आप प्राप्त करना चाहते हैं। यहाँ हमें विवरण (description) कॉलम चाहिए, इसलिए हम वही रेंज चुनते हैं। ध्यान दें कि हमने रेंज चुनने के बाद F4 का उपयोग किया है ताकि इसे लॉक कर दें और फॉर्मूला ड्रैग करने पर यह न हिले।
- MATCH आर्ग्युमेंट्स:
- B2: यह वह कुंजी है जिसे हम खोजना चाहते हैं। यह VLOOKUP में लुकअप वैल्यू के समतुल्य है।
- L2:L18: यह वह कॉलम रेंज है जिसमें कुंजियाँ हैं, जहाँ लुकअप वैल्यू स्थित होनी चाहिए। ध्यान दें कि यहाँ भी हमने F4 का उपयोग किया है।
- 0: यह VLOOKUP में FALSE के समान है। यानी सटीक मिलान के लिए फ़्लैग।
सिंटैक्स को देखते हुए, कोई पहले ही INDEX-MATCH का VLOOKUP पर एक मुख्य लाभ समझ सकता है। INDEX-MATCH के साथ हमें कॉलम इम्यूनिटी मिलती है। यानी, अगर हम दाएँ वाली तालिका में Key और Description के बीच एक अतिरिक्त कॉलम जोड़ने का निर्णय लें, तो कोई फर्क नहीं पड़ेगा क्योंकि फॉर्मूला स्वयं समायोजित होकर अब भी Description कॉलम का ही संदर्भ देगा। आप इसे नीचे देख सकते हैं:
VLOOKUP के साथ यह संभव नहीं है जब तक आप फॉर्मूले में वापस जाकर बदलाव न करें, क्योंकि इसमें कॉलम का संदर्भ स्थिर होता है। दूसरे शब्दों में, ऊपर के उदाहरण में, शुरू में आप Description प्राप्त करने के लिए कॉलम इंडेक्स नंबर के रूप में 2 रखते। हालाँकि, Fish कॉलम जोड़ने के बाद, VLOOKUP अब Fish कॉलम का मान लौटाएगा क्योंकि अब वह टेबल रेंज में कॉलम 2 हो गया है। आपको VLOOKUP फ़ंक्शन में जाकर मैन्युअली कॉलम इंडेक्स नंबर को 3 में बदलना होगा ताकि फ़ंक्शन फिर से सही ढंग से काम करे।
यह दिखाने के बाद, बाएँ वाली तालिका में नाम के साथ विवरण जोड़ना INDEX-MATCH का उपयोग करके VLOOKUP की ही तरह होता है। हम बस फॉर्मूला को नीचे की ओर ड्रैग कर देते हैं, जैसा कि नीचे दिखाया गया है:
ध्यान दें कि एक ही वर्कबुक की अलग-अलग शीट्स के बीच तालिकाओं को जोड़ना भी बिल्कुल यही प्रक्रिया है। फर्क केवल इतना है कि आपको दूसरी शीट से सामान्य कुंजियों वाली कॉलम रेंज के साथ-साथ उन मानों वाली कॉलम रेंज भी चुननी होगी जिन्हें आप प्राप्त करना चाहते हैं।
निष्कर्ष
आशा है आपको यह ट्यूटोरियल पसंद आया होगा। ये फ़ंक्शंस—VLOOKUP और INDEX-MATCH—किसी भी व्यक्ति के लिए, जो व्यावसायिक सेटिंग में Excel के साथ काम करता है, लगभग अनिवार्य हैं। मैं आपको INDEX-MATCH में यहाँ कवर की गई बातों से आगे गहराई तक जाने के लिए प्रोत्साहित करता हूँ, क्योंकि इसमें कई बढ़िया क्षमताएँ हैं, जैसे केस-सेंसिटिव मिलान (हाँ, डिफ़ॉल्ट रूप से INDEX-MATCH और VLOOKUP केस-इनसेंसिटिव होते हैं) या एक से अधिक कॉलम्स के आधार पर मिलान। सीखते रहें; आसमान ही सीमा है!
यदि आप VLOOKUP के बारे में और सीखना चाहते हैं, तो DataCamp का Data Analysis with Spreadsheets कोर्स लें।