VLOOKUP एक्सेल के सबसे उपयोगी कार्यों में से एक है, और यह कम से कम समझ में से एक है। इस लेख में, हम वास्तविक जीवन उदाहरण के माध्यम से VLOOKUP को नष्ट कर देते हैं। हम एक प्रयोग योग्य बना देंगे बील का नमूना या आकार एक कल्पित कंपनी के लिए।
VLOOKUP एक एक्सेल है समारोह। यह आलेख मान लेगा कि पाठक के पास पहले से ही एक्सेल फ़ंक्शंस की गुजरती समझ है, और एसयूएम, औसत और आज के बुनियादी कार्यों का उपयोग कर सकते हैं। अपने सबसे आम उपयोग में, VLOOKUP एक है डेटाबेस फ़ंक्शन, जिसका अर्थ है कि यह डेटाबेस तालिकाओं के साथ काम करता है - या अधिक सरलता से, सूचियों एक्सेल वर्कशीट में चीजों का। किस तरह की चीजें? कुंआ, कोई भी चिज की तरह। आपके पास वर्कशीट हो सकती है जिसमें कर्मचारियों, या उत्पादों, या ग्राहकों, या सीडी संग्रह में सीडी संग्रह, या रात के आकाश में सितारों की एक सूची हो। यह वास्तव में कोई फर्क नहीं पड़ता।
यहां एक सूची, या डेटाबेस का एक उदाहरण दिया गया है। इस मामले में यह उन उत्पादों की एक सूची है जो हमारी कल्पित कंपनी बेचती है:
आमतौर पर इस तरह की सूचियों में सूची में प्रत्येक आइटम के लिए कुछ प्रकार का अद्वितीय पहचानकर्ता होता है। इस मामले में, अद्वितीय पहचानकर्ता "आइटम कोड" कॉलम में है। नोट: VLOOKUP फ़ंक्शन के लिए किसी डेटाबेस / सूची के साथ काम करने के लिए, उस सूची में एक अद्वितीय पहचानकर्ता (या "कुंजी", या "आईडी") वाला कॉलम होना चाहिए, और वह कॉलम तालिका में पहला स्तंभ होना चाहिए। ऊपर हमारा नमूना डेटाबेस इस मानदंड को पूरा करता है।
VLOOKUP का उपयोग करने का सबसे कठिन हिस्सा यह समझ रहा है कि यह वास्तव में क्या है। तो देखते हैं कि क्या हम पहले यह स्पष्ट कर सकते हैं:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
उपर्युक्त उदाहरण में, आप एक आइटम कोड के साथ VLOOKUP फ़ंक्शन को किसी अन्य स्प्रेडशीट में डालेंगे, और यह आपके मूल में वर्णित अनुसार आइटम के विवरण, इसकी कीमत या इसकी उपलब्धता (इसकी "स्टॉक" मात्रा) पर वापस आ जाएगा। सूची। जानकारी के इन टुकड़ों में से कौन सा आपको वापस भेज देगा? खैर, जब आप फॉर्मूला बना रहे हों तो आप इसे तय कर लेंगे।
यदि आपको केवल डेटाबेस से जानकारी का एक टुकड़ा चाहिए, तो इसमें एक VLOOKUP फ़ंक्शन के साथ फ़ॉर्मूला बनाने के लिए बहुत परेशानी होगी। आम तौर पर आप टेम्पलेट जैसे पुन: प्रयोज्य स्प्रेडशीट में इस प्रकार की कार्यक्षमता का उपयोग करेंगे। प्रत्येक बार जब कोई वैध आइटम कोड में प्रवेश करता है, तो सिस्टम संबंधित आइटम के बारे में सभी आवश्यक जानकारी पुनर्प्राप्त करेगा।
आइए इसका एक उदाहरण बनाएं: ए बील का नमूना या आकार कि हम अपने कल्पित कंपनी में और अधिक उपयोग कर सकते हैं।
सबसे पहले हम एक्सेल शुरू करते हैं, और हम खुद को एक खाली चालान बनाते हैं:
इस उदाहरण को सरल रखने के प्रयोजनों के लिए, हम उसी कार्यपुस्तिका में एक अलग चादर पर उत्पाद डेटाबेस का पता लगाएंगे:
इसलिए, हमने अपना उत्पाद डेटाबेस बनाया है, जो इस तरह दिखता है:
हमें एक्सेल द्वारा प्रदान किए जाने वाले सभी उपलब्ध कार्यों की सूची का पता लगाने की आवश्यकता है, ताकि हम VLOOKUP चुन सकें और फॉर्मूला को पूरा करने में कुछ सहायता प्राप्त कर सकें। यह पहले क्लिक करके पाया जाता है सूत्र टैब, और उसके बाद क्लिक करें समारोह सम्मिलित करें:
जिसे हम ढूंढ रहे हैं उसे ढूंढने के लिए, हम "लुकअप" जैसे खोज शब्द टाइप कर सकते हैं (क्योंकि जिस फ़ंक्शन में हम रुचि रखते हैं वह एक है देखो समारोह)। सिस्टम हमें एक्सेल में सभी लुकअप-संबंधित कार्यों की एक सूची वापस कर देगा। VLOOKUP सूची में दूसरा स्थान है। इसे एक क्लिक का चयन करें ठीक.
फंक्शन तर्क बॉक्स प्रकट होता है, जो हमें सभी के लिए प्रेरित करता है बहस (या मापदंडों) VLOOKUP फ़ंक्शन को पूरा करने के लिए आवश्यक है। आप इस बॉक्स के बारे में सोच सकते हैं क्योंकि फ़ंक्शन हमें निम्नलिखित प्रश्न पूछ रहा है:
- आप डेटाबेस में क्या अद्वितीय पहचानकर्ता देख रहे हैं?
- डेटाबेस कहां है?
- अद्वितीय पहचानकर्ता से जुड़े डेटाबेस से जानकारी का कौन सा टुकड़ा, क्या आप अपने लिए पुनर्प्राप्त करना चाहते हैं?
पहले तीन तर्क दिखाए जाते हैं बोल्ड में, यह दर्शाता है कि वे हैं अनिवार्य तर्क (VLOOKUP फ़ंक्शन उनके बिना अधूरा है और वैध मान वापस नहीं करेगा)। चौथा तर्क बोल्ड नहीं है, जिसका अर्थ है कि यह वैकल्पिक है:
पहला तर्क हमें पूरा करने की आवश्यकता है पता लगाने का मूल्य तर्क। फ़ंक्शन को हमें यह बताने की आवश्यकता है कि अद्वितीय पहचानकर्ता कहां खोजें ( आइटम कोड इस मामले में) कि यह विवरण वापस लौटना चाहिए। हमें पहले दर्ज किए गए आइटम कोड का चयन करना होगा (ए 11 में)।
पहले तर्क के दाईं ओर चयनकर्ता आइकन पर क्लिक करें:
फिर आइटम कोड (ए 11) युक्त सेल पर एक बार क्लिक करें, और दबाएं दर्ज:
अब हमें इसके लिए एक मूल्य दर्ज करना होगा तालिका सरणी तर्क। दूसरे शब्दों में, हमें VLOOKUP को बताना होगा कि डेटाबेस / सूची कहां मिलें। दूसरे तर्क के बगल में चयनकर्ता आइकन पर क्लिक करें:
… और दबाएं दर्ज । डेटाबेस का प्रतिनिधित्व करने वाले कक्षों की श्रेणी (इस मामले में "उत्पाद डेटाबेस '! ए 2: डी 7") दूसरे तर्क में हमारे लिए स्वचालित रूप से दर्ज की जाती है।
अब हमें तीसरे तर्क में प्रवेश करने की जरूरत है, Col_index_num । हम इस तर्क का उपयोग VLOOKUP को निर्दिष्ट करने के लिए करते हैं जो डाटाबेस से जानकारी का टुकड़ा है, ए 11 में हमारे आइटम कोड से संबद्ध है, हम चाहते हैं कि हम वापस आएं। इस विशेष उदाहरण में, हम आइटम की इच्छा रखते हैं विवरण हमारे पास लौट आया यदि आप डेटाबेस वर्कशीट को देखते हैं, तो आप देखेंगे कि "विवरण" कॉलम है दूसरा डेटाबेस में कॉलम। इसका मतलब है कि हमें "2" का मान दर्ज करना होगा Col_index_num डिब्बा:
यह ध्यान रखना महत्वपूर्ण है कि हम यहां "2" दर्ज नहीं कर रहे हैं क्योंकि "विवरण" कॉलम है बी उस वर्कशीट पर कॉलम। यदि डेटाबेस कॉलम में शुरू हुआ था कश्मीर वर्कशीट के, हम अभी भी इस क्षेत्र में "2" दर्ज करेंगे क्योंकि "टेबल" एरे "निर्दिष्ट" कॉलम उन कक्षों के सेट में दूसरा स्तंभ है जिसे हमने "टेबल_एरे" निर्दिष्ट करते समय चुना था।
अंत में, हमें यह तय करने की आवश्यकता है कि अंतिम VLOOKUP तर्क में कोई मान दर्ज करना है या नहीं, रेंज देखना । इस तर्क के लिए या तो एक की आवश्यकता है सच या असत्य मूल्य, या इसे खाली छोड़ दिया जाना चाहिए। डेटाबेस के साथ VLOOKUP का उपयोग करते समय (जैसा कि सही समय का 9 0% है), इस तर्क में क्या डालना है, यह तय करने का तरीका निम्नानुसार विचार किया जा सकता है:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
हमारे डेटाबेस का पहला कॉलम है नहीं क्रमबद्ध, हम प्रवेश करते हैं असत्य इस तर्क में:
बस! हमने जिस मूल्य की आवश्यकता है उसे वापस करने के लिए हमने VLOOKUP के लिए आवश्यक सभी जानकारी दर्ज की है। दबाएं ठीक बटन और ध्यान दें कि आइटम कोड "R99245" से संबंधित विवरण सेल बी 11 में सही ढंग से दर्ज किया गया है:
अगर हम एक दर्ज करते हैं विभिन्न सेल ए 11 में आइटम कोड, हम VLOOKUP फ़ंक्शन की शक्ति देखना शुरू कर देंगे: विवरण सेल नए आइटम कोड से मेल खाने के लिए बदलता है:
हम आइटम प्राप्त करने के लिए चरणों का एक समान सेट कर सकते हैं मूल्य सेल ई 11 में लौट आया। ध्यान दें कि नया सूत्र सेल E11 में बनाया जाना चाहिए। नतीजा इस तरह दिखेगा:
ध्यान दें कि दो सूत्रों के बीच एकमात्र अंतर तीसरा तर्क है (Col_index_num) एक "2" से "3" में बदल गया है (क्योंकि हम डेटाबेस में तीसरे कॉलम से डेटा पुनर्प्राप्त करना चाहते हैं)।
अगर हमने इन वस्तुओं में से 2 खरीदने का फैसला किया है, तो हम सेल डी 11 में "2" दर्ज करेंगे। फिर हम लाइन कुल प्राप्त करने के लिए सेल F11 में एक साधारण सूत्र दर्ज करेंगे:
=D11*E11
… जो इस तरह दिखता है …
चालान टेम्पलेट को पूरा करना
हमने अभी तक VLOOKUP के बारे में बहुत कुछ सीखा है। असल में, हमने यह सब कुछ सीखा है जिसे हम इस लेख में सीखने जा रहे हैं। यह ध्यान रखना महत्वपूर्ण है कि VLOOKUP डेटाबेस के अलावा अन्य परिस्थितियों में उपयोग किया जा सकता है। यह कम आम है, और भविष्य में कैसे हो सकता है लेखों को कैसे करें।
हमारा चालान टेम्पलेट अभी तक पूरा नहीं हुआ है। इसे पूरा करने के लिए, हम निम्नलिखित करेंगे:
-
हम सेल ए 11 से नमूना आइटम कोड और सेल डी 11 से "2" हटा देंगे। यह हमारे नए बनाए गए VLOOKUP सूत्रों को त्रुटि संदेशों को प्रदर्शित करने का कारण बन जाएगा:
Image हम एक्सेल के न्यायसंगत उपयोग से इसका समाधान कर सकते हैं अगर() तथा खाली है() कार्य करता है। हम इस से अपना फॉर्मूला बदलते हैं … = VLOOKUP (ए 11, 'उत्पाद डेटाबेस'! ए 2: डी 7,2, गलत) …इसके लिए… = अगर (ISBLANK (ए 11), "", VLOOKUP (ए 11, 'उत्पाद डेटाबेस'! ए 2: डी 7,2, गलत)
- हम इनवॉइस की आइटम पंक्तियों के शेष हिस्सों को बी 11, ई 11 और एफ 11 में सूत्रों की प्रतिलिपि बना देंगे। ध्यान दें कि यदि हम ऐसा करते हैं, तो परिणामी सूत्र अब डेटाबेस तालिका को सही ढंग से संदर्भित नहीं करेंगे। हम डेटाबेस के लिए सेल संदर्भों को बदलकर इसे ठीक कर सकते हैं पूर्ण सेल संदर्भ। वैकल्पिक रूप से - और इससे भी बेहतर - हम एक बना सकते हैं श्रेणी का नाम पूरे उत्पाद डेटाबेस (जैसे "उत्पाद") के लिए, और सेल संदर्भों के बजाय इस श्रेणी का नाम उपयोग करें। सूत्र इस से बदल जाएगा … = अगर (ISBLANK (ए 11), "", VLOOKUP (ए 11, 'उत्पाद डेटाबेस'! ए 2: डी 7,2, गलत) …इसके लिए… = IF (ISBLANK (A11), "", VLOOKUP (A11, उत्पाद, 2, FALSE)) …तथा फिर इनवॉइस आइटम पंक्तियों के सूत्रों को नीचे कॉपी करें।
- हम संभवतः उन कोशिकाओं को "लॉक" करेंगे जिनमें हमारे सूत्र शामिल हैं (या बल्कि अनलॉक अन्य कोशिकाओं), और उसके बाद वर्कशीट की रक्षा करें ताकि यह सुनिश्चित किया जा सके कि जब कोई चालान भरने के लिए आता है तो हमारे सावधानीपूर्वक निर्मित सूत्रों को गलती से अधिलेखित नहीं किया जाता है।
- हम फ़ाइल को एक के रूप में सहेज लेंगे टेम्पलेट, ताकि हमारी कंपनी में हर किसी द्वारा इसका पुन: उपयोग किया जा सके
अगर हम महसूस कर रहे थे वास्तव में चालाक, हम अपने सभी ग्राहकों का डेटाबेस किसी अन्य वर्कशीट में बनाएंगे, और उसके बाद कोशिका बी 5, बी 7 और बी 8 में स्वचालित रूप से ग्राहक के नाम और पते को भरने के लिए सेल F5 में दर्ज ग्राहक आईडी का उपयोग करें।
