बड़ी तालिकाओं का त्वरित विलय (VLOOKUP2D)

विवरण

आइए विचार करें कि कॉलम और पंक्तियों के साथ दो बड़ी तालिकाओं को जल्दी से कैसे संयोजित किया जाए, यानी, एक पैरामीटर (फ़ंक्शन VLOOKUP या HLOOKUP के रूप में) के आधार पर चयन न करें, लेकिन एक साथ दो (फ़ंक्शन INDEX और MATCH का उपयोग करके)।

यदि आप फ़ंक्शन VLOOKUP या इसके क्षैतिज एनालॉग HLOOKUP से परिचित हैं, तो आपको याद रखना चाहिए कि ये अद्भुत फ़ंक्शन केवल एक पैरामीटर द्वारा, यानी एक-आयामी सरणी में - पंक्ति या स्तंभ द्वारा जानकारी ढूंढते हैं। और यदि हमें एक ही समय में दो मापदंडों - पंक्ति और स्तंभ दोनों के संयोग से द्वि-आयामी तालिका से डेटा का चयन करने की आवश्यकता है? आइए तालिकाओं के संयोजन के लिए कई विकल्पों पर विचार करें।

1. INDEX और MATCH का उपयोग करके तालिकाओं का संयोजन

मान लीजिए कि हमें दो तालिकाओं को संयोजित करने की आवश्यकता है जो ऋण पोर्टफोलियो और संपार्श्विक पोर्टफोलियो प्रदर्शित करती हैं:

हम प्रत्येक व्यक्तिगत कॉलम को संयोजित करने के लिए VLOOKUP फ़ंक्शन का उपयोग शुरू कर सकते हैं, लेकिन यदि हमारी तालिकाओं में बहुत बड़ी संख्या में पंक्तियाँ और कॉलम हैं, तो यह अभ्यास एक वास्तविक परेशानी में बदल सकता है। हालाँकि, इस स्थिति से बाहर निकलने का एक काफी सरल तरीका है, क्योंकि Excel में दो उत्कृष्ट कार्य हैं, INDEX और MATCH श्रेणी से सन्दर्भ और सरणियाँ (Lookup and Reference), जो एक जोड़ी में 2D VLOOKUP के रूप में कार्य करता है।

ठीक है, तो दोनों तालिकाओं को शीघ्रता से जोड़ने के लिए हमें क्या करने की आवश्यकता है? आइए दूसरी तालिका (जिसे हम शामिल करेंगे) के हेडर को कॉपी करके शुरू करें और इसे पहली तालिका के हेडर के बगल में पेस्ट करें। कैप के नाम के अनुसार, MATCH फ़ंक्शन हमें कॉलम का सीरियल नंबर देगा, और एग्रीमेंट नंबर के अनुसार - हमें जिस लाइन की ज़रूरत है उसका सीरियल नंबर देगा।

वास्तव में, हम किसी तालिका में किसी विशेष पंक्ति और स्तंभ के प्रतिच्छेदन से किसी सेल का मान ज्ञात करना चाहते हैं। स्पष्टता के लिए, आइए कार्य को तीन चरणों में विभाजित करें:

सबसे पहले, हमें चयनित क्रेडिट समझौते के अनुरूप लाइन नंबर निर्धारित करने की आवश्यकता है। इससे श्रेणी से MATCH फ़ंक्शन बनाने में मदद मिलेगी सन्दर्भ और सरणियाँ (Lookup and Reference). विशेष रूप से, सूत्र MATCH(A14; K2:K18; 0) हमें वांछित परिणाम देगा (लेन-देन के लिए)। "नंबर5398" यह क्रमिक संख्या 13 होगी)। इस फ़ंक्शन का पहला तर्क खोजा गया मान (समझौता) है "नंबर5398" सेल A14 से), दूसरा सेल की श्रेणी है जहां हम एक समझौते की तलाश कर रहे हैं (तालिका में समझौते की संख्या वाला कॉलम - K2:K18), तीसरा तर्क खोज के प्रकार को निर्दिष्ट करता है (0 - सटीक नाम मिलान, अनुमानित खोज निषिद्ध है)।
दूसरे, बिल्कुल इसी तरह, हमें अपनी आवश्यक सामग्री के साथ तालिका में कॉलम की क्रम संख्या निर्धारित करनी होगी। समारोह MATCH(F1; J1:M1; 0) ऐसा करेगा और जारी करेगा, उदाहरण के लिए, सेल F1 में उपयोगकर्ता द्वारा चुने गए कोलैटरलनाम के लिए मान 3।
और अंत में, तीसरे, हमें एक ऐसे फ़ंक्शन की आवश्यकता है जो किसी तालिका से पंक्ति और स्तंभ संख्या द्वारा सेल की सामग्री को आउटपुट कर सके - उसी श्रेणी से INDEX फ़ंक्शन सन्दर्भ और सरणियाँ (Lookup and Reference). इस फ़ंक्शन का पहला तर्क कोशिकाओं की श्रेणी है (हमारे मामले में, यह संपूर्ण तालिका है, अर्थात J2:M18), दूसरा पंक्ति संख्या है, तीसरा स्तंभ संख्या है (और हम उन्हें MATCH का उपयोग करके निर्धारित करेंगे) फ़ंक्शंस)।

तो, उपरोक्त सभी को एक सूत्र में संयोजित करने पर, हमें सेल F14 के लिए निम्नलिखित सूत्र प्राप्त होता है:

=INDEX(J2:M18; MATCH(A14; K2:K18; 0); MATCH(F1; J1:M1; 0))

इस तरह, अपने सूत्र को संपूर्ण श्रेणी में फैलाकर, हम अतिरिक्त फ़ील्ड को सही ढंग से जोड़ देंगे। यह भी ध्यान देना आवश्यक है कि सूत्र को खींचते समय, हमें डॉलर चिह्न ($) के साथ श्रेणियों को ठीक करने की आवश्यकता होती है (फिक्सिंग के लिए, आप F4 कुंजी का उपयोग कर सकते हैं), कैप द्वारा खोज करने के लिए, हम केवल लाइन को ठीक करते हैं (एफ$1 ), लेन-देन संख्याओं द्वारा खोज के लिए - केवल कॉलम ($ए14 ).

2. VLOOKUP का उपयोग करके तालिकाओं का संयोजन

आइए पिछले उदाहरण को थोड़ा संशोधित करें और मान लें कि हमारे पास समान तालिकाएं हैं, लेकिन उनके हेडर संयुक्त हैं, इसलिए MATCH फ़ंक्शन हमें कॉलम की क्रम संख्या को सही ढंग से निर्धारित करने में मदद नहीं करेगा।

इस मामले में, हम तालिका के ऊपर एक तकनीकी फ़ील्ड बना सकते हैं, और कॉलम संख्याएँ मैन्युअल रूप से सम्मिलित कर सकते हैं। फिर, हम पहले से परिचित VLOOKUP फ़ंक्शन का उपयोग कर सकते हैं।

तो, हमारे दूसरे विकल्प का सूत्र इस प्रकार होगा:

=VLOOKUP($A3; $J$3:$M$19; E$1; 0)

फिर, $ चिह्न के साथ श्रेणी और संबंधित खोज फ़ील्ड को ठीक करना न भूलें ताकि सूत्र सही ढंग से काम करे और श्रेणियों को स्थानांतरित करते समय कोई त्रुटि न दे।

विषय पर लेख: