COLUMN फ़ंक्शन के साथ Excel के VLOOKUP फ़ंक्शन को संयोजित करके हम एक लुकअप फॉर्मूला बना सकते हैं जो आपको डेटाबेस या डेटा की तालिका की एक पंक्ति से एकाधिक मान वापस करने की अनुमति देता है।
उपरोक्त छवि में दिखाए गए उदाहरण में, लुकअप फॉर्मूला हार्डवेयर के विभिन्न टुकड़ों से संबंधित सभी मूल्यों जैसे मूल्य, भाग संख्या, और आपूर्तिकर्ता को वापस करना आसान बनाता है।
10 में से 01
Excel VLOOKUP के साथ एकाधिक मान लौटें
नीचे सूचीबद्ध चरणों के बाद उपर्युक्त छवि में देखा गया लुकअप फॉर्मूला बनाता है जो एक ही डेटा रिकॉर्ड से कई मान वापस कर देगा।
लुकअप फॉर्मूला की आवश्यकता है कि COLUMN फ़ंक्शन VLOOKUP के अंदर घोंसला हो।
किसी फ़ंक्शन को Nesting में दूसरे फ़ंक्शन को पहले फ़ंक्शन के लिए तर्कों में से एक के रूप में दर्ज करना शामिल है।
इस ट्यूटोरियल में, COLUMN फ़ंक्शन VLOOKUP के लिए कॉलम अनुक्रमणिका संख्या तर्क के रूप में दर्ज किया जाएगा।
ट्यूटोरियल में अंतिम चरण में चयनित भाग के लिए अतिरिक्त मान पुनर्प्राप्त करने के लिए लुकअप फॉर्मूला को अतिरिक्त कॉलम में कॉपी करना शामिल है।
ट्यूटोरियल सामग्री
- ट्यूटोरियल डेटा दर्ज करना
- डेटा तालिका के लिए नामित रेंज बनाना
- VLOOKUP फ़ंक्शन शुरू करना
- निरपेक्ष सेल संदर्भों का उपयोग कर लुकअप वैल्यू तर्क दर्ज करना
- तालिका ऐरे तर्क दर्ज करना
- नेस्टेड COLUMN फ़ंक्शन दर्ज करना
- VLOOKUP फ़ंक्शन को पूरा करना
- भरने के साथ लुकअप फॉर्मूला की प्रतिलिपि बनाएँ
- लुकअप फॉर्मूला के साथ डेटा पुनर्प्राप्त करना
10 में से 02
ट्यूटोरियल डेटा दर्ज करें
ट्यूटोरियल में पहला चरण डेटा वर्कशीट में डेटा दर्ज करना है।
ट्यूटोरियल में चरणों का पालन करने के लिए उपरोक्त छवि में उपरोक्त डेटा को निम्न कक्षों में दर्ज करें ।
- डेटा डी 1 से जी 1 में डेटा की शीर्ष सीमा दर्ज करें
- जी 10 में कोशिकाओं डी 4 में दूसरी रेंज दर्ज करें
इस ट्यूटोरियल के दौरान बनाए गए खोज मानदंड और लुकअप फॉर्मूला को वर्कशीट की पंक्ति 2 में दर्ज किया जाएगा।
ट्यूटोरियल में छवि में स्वरूपित स्वरूपण शामिल नहीं है, लेकिन यह इस बात को प्रभावित नहीं करेगा कि लुकअप फॉर्मूला कैसे काम करता है।
ऊपर दिए गए लोगों के समान प्रारूपण विकल्पों पर जानकारी इस मूल एक्सेल स्वरूपण ट्यूटोरियल में उपलब्ध है।
ट्यूटोरियल कदम
- उपरोक्त छवि में उपरोक्त छवि को डी 1 से जी 10 में दर्ज करें
10 में से 03
डेटा तालिका के लिए नामित रेंज बनाना
एक नामित श्रेणी सूत्र में डेटा की एक श्रृंखला को संदर्भित करने का एक आसान तरीका है। डेटा के लिए सेल संदर्भों में टाइप करने के बजाय, आप केवल श्रेणी का नाम टाइप कर सकते हैं।
नामित श्रेणी का उपयोग करने के लिए दूसरा लाभ यह है कि इस श्रेणी के लिए सेल संदर्भ वर्कशीट में अन्य कक्षों में सूत्र की प्रतिलिपि बनाते समय भी कभी नहीं बदलते हैं।
इसलिए, रेंज नाम सूत्रों की प्रतिलिपि करते समय त्रुटियों को रोकने के लिए पूर्ण सेल संदर्भों का उपयोग करने का विकल्प हैं।
नोट: श्रेणी नाम में डेटा (पंक्ति 4) के लिए शीर्षक या फ़ील्ड नाम शामिल नहीं हैं बल्कि केवल डेटा ही शामिल है।
ट्यूटोरियल कदम
- वर्कशीट में उन्हें चुनने के लिए कक्ष डी 5 से G10 को हाइलाइट करें
- कॉलम ए के ऊपर स्थित नाम बॉक्स पर क्लिक करें
- नाम बॉक्स में "तालिका" (कोई उद्धरण) टाइप करें
- कीबोर्ड पर ENTER कुंजी दबाएं
- जी 10 से कोशिकाओं डी 5 में अब "टेबल" का नाम है। हम बाद में ट्यूटोरियल में VLOOKUP तालिका सरणी तर्क के लिए नाम का उपयोग करेंगे
10 में से 04
VLOOKUP संवाद बॉक्स खोलना
यद्यपि वर्कशीट में सीधे एक सेल में हमारे लुकअप फॉर्मूला को टाइप करना संभव है, लेकिन कई लोगों को सिंटैक्स को सीधे रखना मुश्किल लगता है - खासतौर पर एक जटिल सूत्र के लिए जैसे कि हम इस ट्यूटोरियल में उपयोग कर रहे हैं।
एक विकल्प, इस मामले में, VLOOKUP संवाद बॉक्स का उपयोग करना है। लगभग सभी एक्सेल के कार्यों में एक संवाद बॉक्स होता है जो आपको प्रत्येक फ़ंक्शन के तर्कों को एक अलग पंक्ति पर दर्ज करने की अनुमति देता है।
ट्यूटोरियल कदम
- वर्कशीट के सेल ई 2 पर क्लिक करें - वह स्थान जहां दो आयामी लुकअप फॉर्मूला के परिणाम प्रदर्शित किए जाएंगे
- रिबन के सूत्र टैब पर क्लिक करें
- फ़ंक्शन ड्रॉप डाउन सूची खोलने के लिए रिबन में लुकअप और संदर्भ विकल्प पर क्लिक करें
- फ़ंक्शन के संवाद बॉक्स को खोलने के लिए सूची में VLOOKUP पर क्लिक करें
10 में से 05
निरपेक्ष सेल संदर्भों का उपयोग कर लुकअप वैल्यू तर्क दर्ज करना
आम तौर पर, लुकअप मान डेटा तालिका के पहले कॉलम में डेटा के क्षेत्र से मेल खाता है।
हमारे उदाहरण में, लुकअप मान उस हार्डवेयर हिस्से के नाम को संदर्भित करता है जिसके बारे में हम जानकारी प्राप्त करना चाहते हैं।
लुकअप मान के लिए स्वीकार्य प्रकार के डेटा हैं:
- पाठ डेटा
- एक तार्किक मूल्य (केवल सत्य या गलत)
- एक संख्या
- वर्कशीट में एक मान के लिए एक सेल संदर्भ
इस उदाहरण में, हम कक्ष संदर्भ दर्ज करेंगे जहां भाग नाम स्थित होगा - सेल डी 2।
पूर्ण सेल संदर्भ
ट्यूटोरियल में बाद के चरण में, हम कक्ष E2 में कक्ष F2 और G2 में लुकअप फॉर्मूला कॉपी करेंगे।
आम तौर पर, जब Excel में सूत्रों की प्रतिलिपि बनाई जाती है, तो सेल संदर्भ उनके नए स्थान को प्रतिबिंबित करने के लिए बदल जाते हैं।
यदि ऐसा होता है, तो डी 2 - लुकअप वैल्यू के लिए सेल संदर्भ - सूत्रों की प्रतिलिपि बनाई जाएगी क्योंकि कोशिकाओं F2 और G2 में त्रुटियों को बनाते हैं।
त्रुटियों को रोकने के लिए, हम सेल संदर्भ डी 2 को एक पूर्ण सेल संदर्भ में परिवर्तित कर देंगे।
फॉर्मूला की प्रतिलिपि बनाते समय पूर्ण सेल संदर्भ नहीं बदलते हैं।
कुंजीपटल पर F4 कुंजी दबाने से पूर्ण सेल संदर्भ बनाए जाते हैं। ऐसा करने से सेल संदर्भ के चारों ओर डॉलर के संकेत जैसे $ D $ 2 जोड़ते हैं
ट्यूटोरियल कदम
- संवाद बॉक्स में lookup_value लाइन पर क्लिक करें
- Lookup_value लाइन में इस सेल संदर्भ को जोड़ने के लिए सेल D2 पर क्लिक करें। यह वह कक्ष है जहां हम उस भाग का नाम टाइप करेंगे जिसके बारे में हम जानकारी चाहते हैं
- सम्मिलन बिंदु को स्थानांतरित किए बिना, डी 2 को पूर्ण सेल संदर्भ $ D $ 2 में परिवर्तित करने के लिए कीबोर्ड पर F4 कुंजी दबाएं
- ट्यूटोरियल में अगले चरण के लिए VLOOKUP फ़ंक्शन संवाद बॉक्स खोलें
10 में से 06
तालिका ऐरे तर्क दर्ज करना
तालिका सरणी डेटा की तालिका है जो लुकअप फॉर्मूला हमारी इच्छित जानकारी को खोजने के लिए खोज करती है।
तालिका सरणी में कम से कम दो कॉलम डेटा होना चाहिए।
- पहले कॉलम में लुकअप वैल्यू तर्क (ट्यूटोरियल में पिछला चरण) शामिल है
- दूसरा, और कोई अतिरिक्त कॉलम, हमारे द्वारा निर्दिष्ट जानकारी को ढूंढने के लिए लुकअप फॉर्मूला द्वारा खोजा जाएगा।
तालिका सरणी तर्क को किसी भी श्रेणी के रूप में दर्ज किया जाना चाहिए जिसमें डेटा तालिका के लिए कक्ष संदर्भ या श्रेणी नाम के रूप में होना चाहिए।
इस उदाहरण के लिए, हम ट्यूटोरियल के चरण 3 में बनाए गए रेंज नाम का उपयोग करेंगे।
ट्यूटोरियल कदम
- संवाद बॉक्स में table_array लाइन पर क्लिक करें
- इस तर्क के लिए श्रेणी नाम दर्ज करने के लिए "तालिका" (कोई उद्धरण) टाइप करें
- ट्यूटोरियल में अगले चरण के लिए VLOOKUP फ़ंक्शन संवाद बॉक्स खोलें
10 में से 07
COLUMN फ़ंक्शन को Nesting
आम तौर पर VLOOKUP केवल डेटा तालिका के एक कॉलम से डेटा लौटाता है और यह कॉलम कॉलम अनुक्रमणिका संख्या तर्क द्वारा सेट किया जाता है।
इस उदाहरण में, हमारे पास तीन कॉलम हैं जिन्हें हम डेटा वापस करना चाहते हैं, इसलिए हमें हमारे लुकअप फॉर्मूला को संपादित किए बिना कॉलम इंडेक्स नंबर को आसानी से बदलने का एक तरीका चाहिए।
यह वह जगह है जहां COLUMN फ़ंक्शन आता है। इसे कॉलम इंडेक्स नंबर तर्क के रूप में दर्ज करके, यह बदल जाएगा क्योंकि लुकअप फॉर्मूला को बाद में ट्यूटोरियल में सेल डी 2 से सेल E2 और F2 में कॉपी किया गया है।
घोंसले कार्य
इसलिए COLUMN फ़ंक्शन VLOOKUP के कॉलम इंडेक्स नंबर तर्क के रूप में कार्य करता है ।
यह संवाद बॉक्स के Col_index_num लाइन में VLOOKUP के अंदर COLUMN फ़ंक्शन को घोंसले से पूरा किया जाता है।
मैन्युअल रूप से COLUMN फ़ंक्शन दर्ज करना
कार्यों को घोंसले करते समय, एक्सेल हमें अपने तर्क दर्ज करने के लिए दूसरे फ़ंक्शन के संवाद बॉक्स को खोलने की अनुमति नहीं देता है।
ColUMN फ़ंक्शन, इसलिए, Col_index_num लाइन में मैन्युअल रूप से दर्ज किया जाना चाहिए।
COLUMN फ़ंक्शन में केवल एक तर्क है - संदर्भ तर्क जो एक सेल संदर्भ है।
COLUMN फ़ंक्शन के संदर्भ तर्क का चयन करना
COLUMN फ़ंक्शन का कार्य संदर्भ तर्क के रूप में दिए गए कॉलम की संख्या को वापस करना है।
दूसरे शब्दों में, यह कॉलम अक्षर को संख्या में परिवर्तित करता है जिसमें कॉलम ए पहला कॉलम होता है, कॉलम बी दूसरा होता है और इसी तरह।
चूंकि हम डेटा के पहले क्षेत्र को वापस लौटना चाहते हैं, वह आइटम की कीमत है - जो डेटा तालिका के कॉलम दो में है - हम नंबर 2 प्राप्त करने के लिए संदर्भ तर्क के रूप में कॉलम बी में किसी भी सेल के लिए सेल संदर्भ चुन सकते हैं। Col_index_num तर्क।
ट्यूटोरियल कदम
- VLOOKUP फ़ंक्शन संवाद बॉक्स में, Col_index_num लाइन पर क्लिक करें
- फंक्शन नाम कॉलम टाइप करें जिसके बाद ओपन राउंड ब्रैकेट " ( "
- संदर्भ तर्क के रूप में उस सेल संदर्भ को दर्ज करने के लिए वर्कशीट में सेल बी 1 पर क्लिक करें
- COLUMN फ़ंक्शन को पूरा करने के लिए एक समापन राउंड ब्रैकेट टाइप करें " ) "
- ट्यूटोरियल में अगले चरण के लिए VLOOKUP फ़ंक्शन संवाद बॉक्स खोलें
10 में से 08
VLOOKUP रेंज लुकअप तर्क दर्ज करना
VLOOKUP का Range_lookup तर्क एक तार्किक मान (केवल सत्य या गलत) है जो इंगित करता है कि क्या आप VLOOKUP को Lookup_value पर सटीक या अनुमानित मिलान ढूंढना चाहते हैं।
- यदि सही है या यदि यह तर्क छोड़ा गया है, तो VLOOKUP या तो Lookup_value के लिए सटीक मिलान देता है, या यदि कोई सटीक मिलान नहीं मिलता है, तो VLOOKUP अगले सबसे बड़े मान देता है। सूत्र को ऐसा करने के लिए, Table_array के पहले कॉलम में डेटा आरोही क्रम में क्रमबद्ध किया जाना चाहिए।
- यदि गलत है, तो VLOOKUP केवल Lookup_value के सटीक मिलान का उपयोग करेगा। यदि तालिका_एरे के पहले कॉलम में दो या दो से अधिक मान हैं जो लुकअप मान से मेल खाते हैं, तो पहले मान का उपयोग किया जाता है। यदि कोई सटीक मिलान नहीं मिला है, तो # एन / ए त्रुटि लौटा दी जाती है।
इस ट्यूटोरियल में, चूंकि हम किसी विशेष हार्डवेयर आइटम के बारे में विशिष्ट जानकारी की तलाश में हैं, इसलिए हम Range_lookup को गलत के बराबर सेट करेंगे।
ट्यूटोरियल कदम
- संवाद बॉक्स में Range_lookup लाइन पर क्लिक करें
- इस लाइन में झूठी शब्द टाइप करें यह इंगित करने के लिए कि हम चाहते हैं कि हम जिस डेटा को खोज रहे हैं उसके लिए एक सटीक मिलान वापस करने के लिए VLOOKUP
- लुकअप फॉर्मूला और बंद संवाद बॉक्स को पूरा करने के लिए ठीक क्लिक करें
- चूंकि हमने अभी तक सेल डी 2 में लुकअप मानदंड दर्ज नहीं किया है, इसलिए सेल ई 2 में एक # एन / ए त्रुटि मौजूद होगी
- यह त्रुटि ठीक हो जाएगी जब हम ट्यूटोरियल के अंतिम चरण में लुकअप मानदंड जोड़ देंगे
10 में से 09
भरने के साथ लुकअप फॉर्मूला की प्रतिलिपि बनाएँ
लुकअप फॉर्मूला का उद्देश्य डेटा तालिका के कई स्तंभों से डेटा को एक बार में पुनर्प्राप्त करना है।
ऐसा करने के लिए, लुकअप फॉर्मूला उन सभी फ़ील्ड में रहना चाहिए जिनसे हम जानकारी चाहते हैं।
इस ट्यूटोरियल में हम डेटा तालिका के कॉलम 2, 3, और 4 से डेटा पुनर्प्राप्त करना चाहते हैं - यह मूल्य, भाग संख्या, और आपूर्तिकर्ता का नाम है जब हम Lookup_value के रूप में भाग नाम दर्ज करते हैं।
चूंकि वर्कशीट में नियमित पैटर्न में डेटा डाला गया है , इसलिए हम सेल ई 2 में लुकअप फॉर्मूला को सेल एफ 2 और जी 2 में कॉपी कर सकते हैं।
जैसा कि सूत्र की प्रतिलिपि बनाई गई है, एक्सेल सूत्र के नए स्थान को प्रतिबिंबित करने के लिए COLUMN फ़ंक्शन (बी 1) में सापेक्ष सेल संदर्भ अपडेट करेगा।
साथ ही, Excel पूर्ण सेल संदर्भ $ D $ 2 और नामित श्रेणी तालिका को रूपांतरित नहीं करता है क्योंकि सूत्र की प्रतिलिपि बनाई गई है।
एक्सेल में डेटा कॉपी करने के एक से अधिक तरीके हैं, लेकिन संभवतः फिल हैंडल का उपयोग करके सबसे आसान तरीका है।
ट्यूटोरियल कदम
- सेल ई 2 पर क्लिक करें - जहां लुकअप फॉर्मूला स्थित है - इसे सक्रिय सेल बनाने के लिए
- माउस पॉइंटर को नीचे दाएं कोने में काले वर्ग पर रखें। पॉइंटर एक प्लस साइन " + " में बदल जाएगा - यह भरने वाला हैंडल है
- बाएं माउस बटन पर क्लिक करें और भरें हैंडल को सेल जी 2 पर खींचें
- माउस बटन और सेल F3 को छोड़ दो दो आयामी लुकअप फॉर्मूला होना चाहिए
- यदि सही तरीके से किया जाता है, तो कक्ष F2 और G2 में अब सेल E2 में मौजूद # एन / ए त्रुटि भी होनी चाहिए
10 में से 10
लुकअप मानदंड दर्ज करना
एक बार लुकअप फॉर्मूला को आवश्यक कक्षों में कॉपी करने के बाद इसका उपयोग डेटा तालिका से जानकारी पुनर्प्राप्त करने के लिए किया जा सकता है।
ऐसा करने के लिए, उस आइटम का नाम टाइप करें जिसे आप Lookup_value सेल (D2) में पुनर्प्राप्त करना चाहते हैं और कीबोर्ड पर ENTER कुंजी दबाएं।
एक बार हो जाने पर, लुकअप फॉर्मूला वाले प्रत्येक सेल में आपके द्वारा खोजे जा रहे हार्डवेयर आइटम के बारे में डेटा का एक अलग टुकड़ा होना चाहिए।
ट्यूटोरियल कदम
- वर्कशीट में सेल डी 2 पर क्लिक करें
- सेल डी 2 में विजेट टाइप करें और कीबोर्ड पर एंटर कुंजी दबाएं
- निम्न जानकारी कोशिकाओं E2 से G2 में प्रदर्शित की जानी चाहिए:
- ई 2 - $ 14.76 - एक विजेट की कीमत
- एफ 2 - पीएन -9876 9 - विजेट के लिए भाग संख्या
- जी 2 - विजेट इंक - विजेट्स के लिए आपूर्तिकर्ता का नाम
- सेल डी 2 में अन्य भागों का नाम टाइप करके और सेल E2 से G2 में परिणामों को देखकर VLOOKUP सरणी सूत्र का परीक्षण करें
अगर #REF जैसे त्रुटि संदेश ! कोशिकाओं E2, F2, या G2 में प्रकट होता है, VLOOKUP त्रुटि संदेशों की यह सूची आपको यह निर्धारित करने में सहायता कर सकती है कि समस्या कहां है।