Table of Contents
- INTRODUCTION
- EXCEL में VLOOKUP की आवश्यकता
- PREREQUISITES TO LEARN VLOOKUP
- SYNTAX: VLOOKUP(फार्मूला लिखने का तरीका )
- उदाहरण 1:VLOOKUP IN EXCEL(डाटा में से एक सिंगल वैल्यू को निकलना )
- EXAMPLE 2:VLOOKUP IN EXCEL( एक से ज्यादा डाटा निकालने के लिए )
- स्पष्टीकरण
INTRODUCTION
VLOOKUP , EXCEL के बहुत से महत्वपूर्ण एवं प्रसिद्ध FUNCTIONS में से एक है . यह एक एडवांस लेवल का फंक्शन माना जाता है. परन्तु इसका अर्थ यह बिलकुल भी नहीं है की यह कठिन है या आसानी से प्रयोग नहीं किया जा सकता. इस आर्टिकल में हम देखेंगे कि इस फंक्शन को कैसे इस्तेमाल किया जाता है तथा इसके प्रयोग क्या क्या हैं .
जो लोग प्रतिदिन एक्सेल में काम करते हैं, वे जानते हैं की हम कितने तरह की रिपोर्टें प्रतिदिन बनाते हैं तथा इसमें कितना समय लगता है. परन्तु यदि हमें यह पता हो की किस समस्या को हल करने के लिए कौन से फंक्शन का प्रयोग करना है तो हमारी यह मुश्किल काफी आसान हो सकती है.
बहुत सी बार हमें दो या उससे अधिक रिपोर्टों को जोड़ना पड़ता है ताकि हम एक अंतिम रिपोर्ट बना सकें, यह हम इस फंक्शन के प्रयोग से आसानी से कर सकते हैं. यदि हम इस फंक्शन का प्रयोग न करें तो हमें एक एक करके सभी ROWS को एक एक करके जोड़ना होगा.
एक अन्य तरह का कार्य जहाँ हमें इस फंक्शन की आवश्यकता पड़ती है वह हैं जब हमें एक बड़े टेबल में से किसी वैल्यू ढूंढना तथा उस टेबल से निकालना पड़ता है, यद्यपि यह काम आसान प्रतीत होता है जब हम यह सोचें की यह कार्य हमें केवल एक वैल्यू के साथ करना हो, परन्तु यदि हमें हजारों ROWS में यही प्रक्रिया दोहरानी हो तो बहुत से समय की ज़रूरत होगी तथा यह काम लगभग असंभव लगेगा. परन्तु इस फंक्शन की मदद से हम इस कार्य को भी चुटकियों में कर सकते हैं.
तो आइये देखते हैं कैसे हम इस फंक्शन का प्रयोग कर सकते हैं.
EXCEL में VLOOKUP की आवश्यकता
VLOOKUP हमें किसी टेबल से कोई खास वैल्यू ढूँढने में मदद करता है. VLOOKUP, EXCEL में दिए किसी डाटा में से पहले COLUMN की किसी वैल्यू को मैच करके उसी लाइन(रो) की कोई भी वैल्यू निकाल सकता है.
PREREQUISITES TO LEARN VLOOKUP
यदि हमें कुछ जानकारियां पहले से हों तो हम इस फंक्शन को आसानी एवं और बेहतर तरीके से समझ पायेंगे. जैसे की
- फार्मूला एवं फंक्शन इस्तेमाल करने का तरीका
- ROWS एवं COLUMNS की समझ
- EXCEL SOFTWARE
एक सहायक लिंक जिसमे फार्मूला कैसे इस्तेमाल किया जाता है नीचे बताया गया है.What Excel does? How to use formula in Excel?
SYNTAX: VLOOKUP(फार्मूला लिखने का तरीका )
VLOOKUP फार्मूला लिखने का तरीका नीचे दर्शाया गया है :
=VLOOKUP(जिस वैल्यू को ढूंढना है उसका सेल नंबर, जिस डाटा में से ढूंढना है उसकी रेंज, जो वैल्यू प्राप्त करनी है उसका कॉलम नंबर, मैचिंग लगभग या सटीक (TRUE/FALSE) )
उदाहरण के लिए
मान लीजिये एक सेल H13 में कोई वैल्यू पड़ी है जिसे हमें एक डाटा टेबल जिसका पहला तथा अंतिम सेल I12:K25 (3 columns) हैं, मैचिंग सटीक होनी चाहिए. इस प्रकार की वैल्यू को ढूँढने के लिए हमें निम्नलिखित फार्मूला लिखना पड़ेगा
=vlookup(H13,I12:K25,3,false)
यह फार्मूला H13 की वैल्यू को I12:K25 रेंज के टेबल में ढूंढेगा तथा तीसरे COLUMN की वैल्यू को वापिस करेगा. यहाँ यह ध्यान रखना ज़रूरी है की H13 की वैल्यू की मैचिंग केवल सिलेक्टेड रेंज के पहले कॉलम से ही की जाएगी.
उदाहरण 1:VLOOKUP IN EXCEL(डाटा में से एक सिंगल वैल्यू को निकलना )
DATA SAMPLE
नीचे दो टेबल दिए गए हैं जिसमे अलग अलग विद्यार्थिओं द्वारा अलग अलग विषयों में प्राप्त अंकों को दर्शाया गया है.
ध्यान से देखेंगे तो हम जानेंगे की दोनों टेबल में अलग अलग विषय हैं.
हम इन दोनों टेबल को मिलकर एक फाइनल टेबल बनाना चाहते हैं जिसमे सभी विषयों के नंबर सम्मिलित हों.
आइये देखते हैं की vlookup की मदद से हम कैसे ये कर सकते हैं.
सबसे पहले देखते हैं की हमें इस प्रक्रिया को पूरा करने के लिए कौन सा फंक्शन प्रयोग करना पड़ेगा.
जैसा की syntax दिखाया गया था
=VLOOKUP(जिस वैल्यू को ढूंढना है उसका सेल नंबर , जिस डाटा में से ढूंढना है उसकी रेंज , जो वैल्यू प्राप्त करनी है उसका कॉलम नंबर(इसे हम कॉलम इंडेक्स भी बोलते हैं) , मैचिंग लगभग या सटीक (TRUE/FALSE) )
लक्ष्य
हमारे उदहारण का लक्ष्य ऊपर दिए गए दोनों तालिकाओं को जोड़कर नीचे दर्शाए गए तालिका की तरह बनाना है .
VLOOKUP प्रयोग करने की प्रक्रिया
सबसे पहले पहले टेबल या तालिका को कॉपी करके कहीं दूसरी जगह पेस्ट कर लीजिये.
- अन्य तीन विषयों के नंबर जोड़ने के लिए तीन कॉलम बना लें जिसमे हैडिंग SST, ART AND ENGLISH दे दें .
- जैसा की ऊपर दिए गए syntax के अनुसार फार्मूला इस प्रकार है –
=VLOOKUP(जिस वैल्यू को ढूंढना है उसका सेल नंबर, जिस डाटा में से ढूंढना है उसकी रेंज, जो वैल्यू प्राप्त करनी है उसका कॉलम नंबर, मैचिंग लगभग या सटीक (TRUE/FALSE) )
जिसे हम सेल O11 में इस प्रकार से डालेंगे. फार्मूला नीचे दर्शाया गया है.
- “=vlookup( J21,O9:S14,3,FALSE)” (“” लिखने की आवश्यकता नहीं है “)
- ENTER दबाइए .
- उत्तर के तौर पर O21 सेल में “45” आएगा जैसा की हम टेबल में देख सकते हैं जो की सही उत्तर है.
- उत्तर दर्शाती हुई स्क्रीन नीचे दिखाई गयी है.
*नीचे दी गयी पिक्चर के बाद चरणबद्ध तरीके से फार्मूला प्रयोग करने का विवरण दिया गया है.
STEP WISE STEP EXPLANATION
आइये देखते हैं की VLOOKUP ने कैसे हमारे लिखे फार्मूला को समझा.
=vlookup( J21,09:S14,3,FALSE)
फार्मूला में प्रयोग शब्दों का अर्थ
| EXPLANATION OF THE FORMULA USED | |
|---|---|
| = | प्रत्येक फार्मूला “=” से शुरू होता है. |
| VLOOKUP | फंक्शन का नाम |
| J21 | यह सेल उस वैल्यू को दर्शाता है जिसे हम डाटा में ढूँढना चाहते हैं. जैसा की हम पिक्चर में देख सकते हैं ROLL NO एक UNIQUE (सबसे अलग) वैल्यू है तथा दोनों टेबल में उपलब्ध है. इस UNIQUE वैल्यू से हम कोई भी वैल्यू दुसरे टेबल से ले सकते हैं. which is present in both the tables. With the help of this UNIQUE VALUE we’ll help Excel to search out our desired value and return it to us. |
| O9:S14 | रेंज, जिसमे से वैल्यू को ढूँढा जाना है. यह रेंज एक टेबल के रूप में होगा. पहला सेल यानि O9 टेबल के पहले सेल को दर्शाता है जबकि दूसरा सेल S14 टेबल के अंतिम सेल को दर्शाता है. टेबल की रेंज को ऊपर के बाएँ सेल एवं नीचे के दायें सेल को लिखकर दर्शाया जाता है. |
| FALSE | FALSE सही एवं सटीक मैचिंग के लिए प्रयोग होता है. |
EXAMPLE 2:VLOOKUP IN EXCEL( एक से ज्यादा डाटा निकालने के लिए )
WHAT IF WE NEED THE VALUE FOR A LONG LIST.
यदि हम एक से अधिक लाइन्स के लिए डाटा निकलना चाहते हैं, तो ऊपर दिए गए फार्मूला को सेल के दायें नीचे की तरफ दिख रहे एक छोटे से वर्ग से खींचें. फार्मूला कॉपी होता चला जायेगा तथा नीचे दी गयी तस्वीर दिखेगी.
जैसे ही हम फार्मूला को नीचे की तरफ खींचेंगे हम देख सकते हैं की ऊपर के चार लाइन्स में तो फार्मूला ने सही काम किया परन्तु अंतिम दो लाइन्स में #NA एरर आया. But we can see that it worked fine for the first 04 rows but not for the last two rows and a #N/A(VALUE NOT AVAILABLE) error showed up.
पहले जानते हैं की यह एरर क्यूँ आया. जब
हम किसी फार्मूला को ड्रैग करते हैं तो फार्मूला के सेल्स रिलेटिव तरीके से बदल जाते हैं. इस उदहारण में यही समस्या देखि जा सकती है.
आइये देखते हैं की जब बहुत सी लाइन्स को इकठे फार्मूला ड्रैग करके लगाना है तो किस प्रकार से इस समस्या का निवारण करना है.
DO YOU KNOW
EXCEL FORMULAS WORKS RELATIVELY. IF WE DRAG ANY FORMULA, IT’LL CHANGE THE CELL ADDRESSES RELATIVELY
SOLVING THE #N/A PROBLEM IN VLOOKUP
#न/अ की समस्या का समाधान करने के लिए हम प्रतेक रो और कॉलम नंबर के आगे $ का चिन्ह लगा देंगे. $ का चिन्ह ABSOLUTE तरीके से सेल का एड्रेस देने के काम आता है. $ लगाने के बाद यदि हम किसी फोर्मुले को ड्रैग करेंगे तो एड्रेस नहीं बदलेगा. अब नया फार्मूला इस प्रकार से होगा.
=vlookup( J21,$O$9:$S$14,3,FALSE)
बदलाव को लाल रंग से दिखाया गया है.
$ चिन्ह के प्रयोग के बाद LOOKUP TABLE नहीं बदलेगा तथा हम फार्मूला को जितना चाहे उतनी लाइन्स के लिए खींच सकते हैं.
फार्मूला बदलकर फिर से फार्मूला लगाने के बाद हमारी समस्या का समाधान हो गया है जो की हम नीचे दी गयी पिक्चर में देख सकते हैं.
SST के सभी नंबर डालने के बाद, हम ART तथा ENGLISH के नंबर भी डालेंगे जिसका तरीका उसी प्रकार से होगा जैसे SST के लिए था.
P21 में ART कल इए फार्मूला “=VLOOKUP( J21,$O$9:$S$14,4,FALSE)”
Q21 में ENGLISH के लिए फार्मूला = “VLOOKUP( J21,$O$9:$S$14,5,FALSE)”
हम देख सकते हैं की जैसा टेबल हम बनाना चाहते थे, बन चूका है.
स्पष्टीकरण
इनपुट नंबर 3, कॉलम इंडेक्स नंबर.
इसे समझाना बहुत ही आसन है.यहाँ हम फार्मूला में डाले गए चार वैल्यूज में से तीसरे नंबर की वैल्यू के बारे में कर रहे हैं. जो रेंज हम वैल्यू को ढूँढने के लिए सेलेक्ट करते हैं, वह रेंज एक्सेल द्वारा एक टेबल की तरह मानी जाती है तथा उस टेबल के कॉलम को बाएँ से गिनकर कॉलम गिने जाते हैं.नीचे दिए गए उदाहरन से यह स्पष्ट हो जायेगा.Its very easy to understand. The Range, which has been selected by us to find our desired value from, is considered a table by the excel and its columns will be counted from the left to right. Just put a number of the column, the value which you need to retrieve.
EXAMPLE:
Checkout the illustration given in the picture.
The Range selection area is colored in Green.
The table has four columns but for vlookup, the columns will be counted from the first column of selected range as shown by orange arrows.
