एक्सेल VLOOKUP के सभी रहस्य एक तालिका में डेटा खोजने और इसे दूसरे में निकालने के लिए कार्य करते हैं

2024 लेखक: Malcolm Clapton | [email protected]. अंतिम बार संशोधित: 2023-12-17 03:57

लेख को पढ़ने के बाद, आप न केवल एक्सेल स्प्रेडशीट में डेटा ढूंढना और इसे दूसरे में निकालना सीखेंगे, बल्कि ऐसी तकनीकें भी सीखेंगे जिनका उपयोग VLOOKUP फ़ंक्शन के संयोजन में किया जा सकता है।

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

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

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

वाक्य - विन्यास

VLOOKUP फ़ंक्शन में चार पैरामीटर हैं:

= वीलुकअप (;; [;]), यहां:

- वांछित मूल्य (शायद ही कभी) या वांछित मूल्य वाले सेल का संदर्भ (अधिकांश मामलों में);

- पहले (!) कॉलम में कोशिकाओं की एक श्रेणी (द्वि-आयामी सरणी) का संदर्भ, जिसके पैरामीटर मान की खोज की जाएगी;

- उस श्रेणी में कॉलम संख्या जिससे मूल्य वापस किया जाएगा;

- यह एक बहुत ही महत्वपूर्ण पैरामीटर है जो इस प्रश्न का उत्तर देता है कि क्या श्रेणी के पहले कॉलम को आरोही क्रम में क्रमबद्ध किया गया है। यदि सरणी को सॉर्ट किया जाता है, तो हम मान TRUE या 1 निर्दिष्ट करते हैं, अन्यथा - FALSE या 0. यदि यह पैरामीटर छोड़ दिया जाता है, तो यह डिफ़ॉल्ट रूप से 1 हो जाता है।

मैं शर्त लगाता हूं कि जो लोग VLOOKUP फ़ंक्शन को परतदार के रूप में जानते हैं, चौथे पैरामीटर के विवरण को पढ़ने के बाद, वे असहज महसूस कर सकते हैं, क्योंकि वे इसे थोड़े अलग रूप में देखने के आदी हैं: आमतौर पर वे सटीक मिलान के बारे में बात कर रहे होते हैं जब खोज (गलत या 0) या एक श्रेणी स्कैन (सत्य या 1)।

अब आपको अगले पैराग्राफ को कई बार तब तक पढ़ने और पढ़ने की जरूरत है जब तक कि आप अंत तक कही गई बात का अर्थ महसूस न कर लें। वहां हर शब्द महत्वपूर्ण है। उदाहरण आपको यह पता लगाने में मदद करेंगे।

VLOOKUP फॉर्मूला वास्तव में कैसे काम करता है?

• सूत्र प्रकार I. यदि अंतिम पैरामीटर को छोड़ दिया जाता है या 1 के बराबर निर्दिष्ट किया जाता है, तो VLOOKUP मानता है कि पहले कॉलम को आरोही क्रम में क्रमबद्ध किया गया है, इसलिए खोज उस पंक्ति पर रुक जाती है वांछित से अधिक मान वाली रेखा से तुरंत पहले … यदि ऐसी कोई स्ट्रिंग नहीं मिलती है, तो श्रेणी की अंतिम पंक्ति वापस कर दी जाती है।

  

• फॉर्मूला II। यदि अंतिम पैरामीटर 0 के रूप में निर्दिष्ट है, तो VLOOKUP सरणी के पहले कॉलम को क्रमिक रूप से स्कैन करता है और पैरामीटर के साथ पहला सटीक मिलान मिलने पर तुरंत खोज बंद कर देता है, अन्यथा # N / A (# N / A) त्रुटि कोड वापस किया जाता है।

  

सूत्र कार्यप्रवाह

वीपीआर प्रकार I

वीपीआर प्रकार II

फॉर्म I. के सूत्रों के लिए परिणाम

1. मूल्यों को श्रेणियों में वितरित करने के लिए सूत्रों का उपयोग किया जा सकता है।
2. यदि पहले कॉलम में डुप्लिकेट मान हैं और इसे सही ढंग से सॉर्ट किया गया है, तो डुप्लिकेट मानों वाली अंतिम पंक्तियों को वापस कर दिया जाएगा।
3. यदि आप किसी ऐसे मान की खोज करते हैं जो स्पष्ट रूप से पहले कॉलम से बड़ा हो सकता है, तो आप आसानी से तालिका की अंतिम पंक्ति पा सकते हैं, जो काफी मूल्यवान हो सकती है।
4. यह दृश्य # N / A त्रुटि तभी लौटाएगा जब उसे वांछित से कम या उसके बराबर मान नहीं मिलेगा।
5. यह समझना मुश्किल है कि यदि आपकी सरणी को क्रमबद्ध नहीं किया गया है तो सूत्र गलत मान लौटाता है।

प्रकार II. के सूत्रों के लिए परिणाम

यदि सरणी के पहले कॉलम में वांछित मान कई बार आता है, तो सूत्र बाद की डेटा पुनर्प्राप्ति के लिए पहली पंक्ति का चयन करेगा।

वीलुकअप प्रदर्शन

आप लेख के चरमोत्कर्ष पर पहुँच गए हैं। ऐसा लगता है, ठीक है, अगर मैं शून्य या एक को अंतिम पैरामीटर के रूप में निर्दिष्ट करता हूं तो क्या अंतर है? मूल रूप से, हर कोई इंगित करता है, निश्चित रूप से, शून्य, क्योंकि यह काफी व्यावहारिक है: आपको सरणी के पहले कॉलम को छाँटने के बारे में चिंता करने की आवश्यकता नहीं है, आप तुरंत देख सकते हैं कि मान पाया गया था या नहीं। लेकिन अगर आपकी शीट पर कई हज़ार VLOOKUP सूत्र हैं, तो आप देखेंगे कि VLOOKUP II धीमा है। उसी समय, आमतौर पर हर कोई सोचने लगता है:

• मुझे एक अधिक शक्तिशाली कंप्यूटर चाहिए;
• मुझे एक तेज़ फ़ॉर्मूला चाहिए, उदाहरण के लिए, बहुत से लोग INDEX + MATCH के बारे में जानते हैं, जो माना जाता है कि यह 5-10% से अधिक तेज़ है।

और कम ही लोग सोचते हैं कि जैसे ही आप I प्रकार के VLOOKUP का उपयोग करना शुरू करते हैं और सुनिश्चित करते हैं कि पहला कॉलम किसी भी तरह से सॉर्ट किया गया है, VLOOKUP की गति 57 गुना बढ़ जाएगी। मैं शब्दों में लिख रहा हूँ - पचहत्तर बार! 57% नहीं, बल्कि 5,700%। मैंने इस तथ्य को काफी विश्वसनीय तरीके से जांचा।

इस तरह के तेजी से काम का रहस्य इस तथ्य में निहित है कि एक अत्यंत कुशल खोज एल्गोरिथ्म को एक क्रमबद्ध सरणी पर लागू किया जा सकता है, जिसे द्विआधारी खोज (आधा विधि, द्विभाजन विधि) कहा जाता है। तो I प्रकार का VLOOKUP इसे लागू करता है, और प्रकार II का VLOOKUP बिना किसी अनुकूलन के खोज करता है। MATCH फ़ंक्शन के लिए भी यही सच है, जिसमें एक समान पैरामीटर शामिल है, और LOOKUP फ़ंक्शन, जो केवल सॉर्ट किए गए सरणियों पर काम करता है और लोटस 1-2-3 के साथ संगतता के लिए एक्सेल में शामिल है।

सूत्र के नुकसान

VLOOKUP के नुकसान स्पष्ट हैं: सबसे पहले, यह केवल निर्दिष्ट सरणी के पहले कॉलम में खोज करता है, और दूसरा, केवल इस कॉलम के दाईं ओर। और जैसा कि आप समझते हैं, ऐसा हो सकता है कि आवश्यक जानकारी वाला कॉलम उस कॉलम के बाईं ओर होगा जिसमें हम देख रहे होंगे। सूत्रों का पहले ही उल्लेख किया गया संयोजन INDEX + MATCH इस कमी से रहित है, जो इसे VLOOKUP (VLOOKUP) की तुलना में तालिकाओं से डेटा निकालने का सबसे लचीला समाधान बनाता है।

वास्तविक जीवन में सूत्र को लागू करने के कुछ पहलू

रेंज खोज

श्रेणी खोज का एक उत्कृष्ट उदाहरण ऑर्डर आकार के आधार पर छूट का निर्धारण करने का कार्य है।

टेक्स्ट स्ट्रिंग्स खोजें

बेशक, VLOOKUP न केवल संख्याओं के लिए, बल्कि पाठ के लिए भी दिखता है। यह ध्यान में रखा जाना चाहिए कि सूत्र पात्रों के मामले के बीच अंतर नहीं करता है। यदि आप वाइल्डकार्ड का उपयोग करते हैं, तो आप एक अस्पष्ट खोज व्यवस्थित कर सकते हैं। दो वाइल्डकार्ड हैं: "?" - टेक्स्ट स्ट्रिंग में किसी एक वर्ण को प्रतिस्थापित करता है, "*" - किसी भी वर्ण की किसी भी संख्या को प्रतिस्थापित करता है।

फाइटिंग स्पेस

यह प्रश्न अक्सर उठाया जाता है कि खोज करते समय अतिरिक्त रिक्त स्थान की समस्या को कैसे हल किया जाए। यदि लुकअप तालिका अभी भी उनमें से साफ़ की जा सकती है, तो VLOOKUP सूत्र का पहला पैरामीटर हमेशा आपके ऊपर नहीं होता है। इसलिए, यदि अतिरिक्त रिक्त स्थान वाले कक्षों के बंद होने का जोखिम मौजूद है, तो आप इसे साफ़ करने के लिए TRIM फ़ंक्शन का उपयोग कर सकते हैं।

अलग डेटा प्रारूप

यदि VLOOKUP फ़ंक्शन का पहला पैरामीटर उस सेल को संदर्भित करता है जिसमें एक संख्या है, लेकिन जो सेल में टेक्स्ट के रूप में संग्रहीत है, और सरणी के पहले कॉलम में सही प्रारूप में संख्याएं हैं, तो खोज विफल हो जाएगी। विपरीत स्थिति भी संभव है। पैरामीटर 1 को आवश्यक प्रारूप में अनुवाद करके समस्या को आसानी से हल किया जा सकता है:

= VLOOKUP (−− D7; उत्पाद! $ A $ 2: $ C $ 5; 3; 0) - यदि D7 में टेक्स्ट है और तालिका में संख्याएँ हैं;

= वीलुकअप (डी7 और ""); उत्पाद! $ ए $ 2: $ सी $ 5; 3; 0) - और इसके विपरीत।

वैसे, आप टेक्स्ट का एक साथ कई तरीकों से एक नंबर में अनुवाद कर सकते हैं, चुनें:

• दोहरा निषेध -D7.
• एक D7 * 1 से गुणा करें।
• शून्य जोड़ D7 + 0।
• पहली शक्ति D7 ^ 1 तक बढ़ाना।

किसी संख्या को टेक्स्ट में कनवर्ट करना एक खाली स्ट्रिंग के साथ संयोजन के माध्यम से किया जाता है, जो एक्सेल को डेटा प्रकार को परिवर्तित करने के लिए मजबूर करता है।

#N/A. को कैसे दबाये

IFERROR फ़ंक्शन के साथ ऐसा करना बहुत सुविधाजनक है।

उदाहरण के लिए: = IFERROR (VLOOKUP (D7; उत्पाद! $ A $ 2: $ C $ 5; 3; 0); "")।

यदि VLOOKUP त्रुटि कोड # N / A देता है, तो IFERROR इसे इंटरसेप्ट करेगा और पैरामीटर 2 (इस मामले में, एक खाली स्ट्रिंग) को प्रतिस्थापित करेगा, और यदि कोई त्रुटि नहीं होती है, तो यह फ़ंक्शन दिखावा करेगा कि यह बिल्कुल भी मौजूद नहीं है, लेकिन केवल VLOOKUP है जो सामान्य परिणाम देता है।

सरणी

अक्सर वे सरणी के संदर्भ को निरपेक्ष बनाना भूल जाते हैं, और सरणी को खींचते समय "फ्लोट" करते हैं। A2: C5 के बजाय $ A $ 2: $ C $ 5 का उपयोग करना याद रखें।

संदर्भ सरणी को कार्यपुस्तिका की एक अलग शीट पर रखना एक अच्छा विचार है। यह पैरों के नीचे नहीं आता है, और यह सुरक्षित रहेगा।

इस सरणी को नामित श्रेणी के रूप में घोषित करना एक बेहतर विचार होगा।

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

खैर, प्रतिभा के कगार पर - रूप में सरणी की व्यवस्था करने के लिए।

कॉलम को निकालने के लिए निर्दिष्ट करने के लिए कॉलम फ़ंक्शन का उपयोग करना

यदि जिस तालिका में आप VLOOKUP का उपयोग करके डेटा पुनर्प्राप्त कर रहे हैं, उसकी संरचना लुकअप तालिका के समान है, लेकिन इसमें केवल कम पंक्तियाँ हैं, तो आप पुनर्प्राप्त किए जाने वाले स्तंभों की संख्या की स्वचालित रूप से गणना करने के लिए VLOOKUP में COLUMN () फ़ंक्शन का उपयोग कर सकते हैं। इस मामले में, सभी VLOOKUP सूत्र समान होंगे (पहले पैरामीटर के लिए समायोजित, जो स्वचालित रूप से बदलता है)! ध्यान दें कि पहले पैरामीटर में एक निरपेक्ष स्तंभ निर्देशांक होता है।

& "|" & के साथ एक समग्र कुंजी बनाना

यदि एक ही समय में कई स्तंभों द्वारा खोजना आवश्यक हो जाता है, तो खोज के लिए एक समग्र कुंजी बनाना आवश्यक है। यदि वापसी मान पाठ्य नहीं था (जैसा कि "कोड" फ़ील्ड के मामले में है), लेकिन संख्यात्मक, तो अधिक सुविधाजनक SUMIFS सूत्र इसके लिए उपयुक्त होगा और समग्र कॉलम कुंजी की बिल्कुल भी आवश्यकता नहीं होगी।

लाइफहाकर के लिए यह मेरा पहला लेख है। यदि आप इसे पसंद करते हैं, तो मैं आपको यात्रा करने के लिए आमंत्रित करता हूं, और टिप्पणियों में VLOOKUP फ़ंक्शन और इसी तरह के उपयोग के आपके रहस्यों के बारे में भी खुशी से पढ़ता हूं। धन्यवाद।:)