डेटा मैप करना Excel . का एक अनिवार्य हिस्सा है . इसलिए, डेटा मैप करने . के लिए कुछ आसान तरीकों को जानना बहुत समय बचा सकता है और वर्कफ़्लो में सुधार कर सकता है। इसे ध्यान में रखते हुए, यह लेख 4 . प्रदर्शित करता है डेटा मैप करने के उपयोगी तरीके एक्सेल में VLOOKUP . इसके अलावा, हम यह भी चर्चा करेंगे कि किसी मान की Nth आवृत्ति कैसे प्राप्त करें, और VLOOKUP का उपयोग करके त्रुटियों को छिपाएं समारोह।
आप नीचे दिए गए लिंक से अभ्यास कार्यपुस्तिका डाउनलोड कर सकते हैं।
एक्सेल में VLOOKUP का उपयोग करके डेटा मैप करने के 4 तरीके
इस लेख में, हम VLOOKUP . को जोड़ेंगे MATCH . के साथ कार्य करें , COUNTIF , अप्रत्यक्ष , और IF कार्य करता है डेटा मैप करने के लिए . तो, बिना किसी देरी के, आइए इसमें गोता लगाएँ!
यहाँ, हमने Microsoft Excel 365 . का उपयोग किया है संस्करण, आप अपनी सुविधा के अनुसार किसी अन्य संस्करण का उपयोग कर सकते हैं।
विधि-1 :एक्सेल में डेटा मैप करने के लिए VLOOKUP फ़ंक्शन का उपयोग करना
आइए चीजों को सबसे स्पष्ट तरीके से शुरू करें यानी VLOOKUP फ़ंक्शन का उपयोग करके एक्सेल में डेटा मैप करने के लिए। तो, चलिए शुरू करते हैं।
B4:D14 . में दिखाए गए डेटासेट को ध्यान में रखते हुए कोशिकाएं। यहां, डेटासेट कर्मचारी ID . की सूची दिखाता है s, उनके नाम , और विभाग जहां वे काम करते हैं।
कदम :
- शुरुआत में, G5 . पर नेविगेट करें सेल और नीचे अभिव्यक्ति दर्ज करें।
=VLOOKUP(G4,B5:D14,3,FALSE)
इस सूत्र में, G4 सेल आईडी . को संदर्भित करता है 1008 और B5:D14 कक्षों की श्रेणी आईडी . का प्रतिनिधित्व करती है , नाम , और विभाग कॉलम।
फॉर्मूला ब्रेकडाउन:
- VLOOKUP(G4,B5:D14,3,FALSE) → किसी तालिका के सबसे बाएं कॉलम में एक मान ढूंढता है, और फिर आपके द्वारा निर्दिष्ट कॉलम से उसी पंक्ति में एक मान देता है। यहां, G4 ( lookup_value तर्क) को B5:D14 . से मैप किया गया है (table_array तर्क) सरणी। अगला, 3 (col_index_num तर्क) लुकअप मान के स्तंभ संख्या का प्रतिनिधित्व करता है। अंत में, गलत (range_lookup तर्क) सटीक मिलान . को संदर्भित करता है लुकअप मान का।
- आउटपुट → मार्केटिंग
अंत में, परिणाम नीचे दी गई छवि की तरह दिखना चाहिए।
विधि-2 :VLOOKUP और MATCH फंक्शन के साथ डेटा मैपिंग (दो-तरफा VLOOKUP)
हमारी दूसरी विधि में, हम VLOOKUP . को जोड़ेंगे और मिलान किसी विशेष पंक्ति और स्तंभ के प्रतिच्छेदन पर मान को मैप करने के लिए कार्य करता है। इसे टू-वे वीलुकअप . के नाम से भी जाना जाता है . तो, आइए इसे कार्रवाई में देखें।
मान लें कि हमारे पास बिक्री सूची है B4:E12 . में दिखाया गया डेटासेट कोशिकाएं। यहां, हमारे पास आइटम . की सूची है और बेची गई इकाइयों की संख्या जनवरी . में , फरवरी , और मार्च ।
कदम :
- शुरुआत में, आइटम का चयन करें और माह , उदाहरण के लिए, हमने टेलीविज़न . चुना है और मार्च क्रमशः।
- अगला, H6 . पर जाएं सेल और नीचे दिया गया सूत्र दर्ज करें।
=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)
यहां, H4 और H5 सेल आइटम . का संदर्भ देते हैं और माह जबकि B5:E5 कॉलम हेडर का प्रतिनिधित्व करता है।
फॉर्मूला ब्रेकडाउन:
- MATCH(H5, B5:E5, 0) → दिए गए मान से मेल खाने वाले सरणी में किसी आइटम की सापेक्ष स्थिति देता है। यहां, H5 lookup_value है तर्क जो मार्च के महीने . को संदर्भित करता है . निम्नलिखित, B5:E5 lookup_array का प्रतिनिधित्व करता है तर्क जहां से मूल्य का मिलान किया जाता है। हाल ही में, 0 वैकल्पिक है match_type तर्क जो सटीक मिलान . को इंगित करता है मानदंड।
- आउटपुट → 4
- VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE) → बन जाता है
- VLOOKUP(H4, B6:E10, 4, FALSE) → यहां, H4 ( lookup_value तर्क) को B6:E10 . से मैप किया गया है (table_array तर्क) सरणी। अगला, 4 (col_index_num तर्क) लुकअप मान के स्तंभ संख्या का प्रतिनिधित्व करता है। अंत में, गलत (range_lookup तर्क) सटीक मिलान . को संदर्भित करता है लुकअप मूल्य का।
- आउटपुट → 243
अंत में, आपका परिणाम नीचे दी गई तस्वीर जैसा दिखना चाहिए।
विधि-3 :डेटा मैप करने के लिए VLOOKUP और COUNTIF फ़ंक्शंस का उपयोग करना
डेटा मैप करने . का दूसरा तरीका Excel में COUNIF फ़ंक्शन शामिल करना है VLOOKUP . के भीतर समारोह। यह आसान और आसान है, बस इन चरणों का पालन करें।
आइए सर्वश्रेष्ठ बिकने वाली पुस्तकों . पर विचार करें B4:C11 . में दिखाया गया डेटासेट कोशिकाएं। इस डेटासेट में, हमारे पास सर्वश्रेष्ठ विक्रेता . के नाम हैं और इसकी कीमत क्रमशः USD है।
कदम :
- शुरू करने के लिए, F5 . पर जाएं सेल और नीचे अभिव्यक्ति दर्ज करें।
=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)
इस अभिव्यक्ति में, B5:C9 सेल की श्रेणी सर्वश्रेष्ठ विक्रेता पुस्तक . का प्रतिनिधित्व करती है और कीमत स्तंभ। इसके विपरीत, F4 सेल सर्वश्रेष्ठ विक्रेता . को संदर्भित करता है (यहाँ, यह है बुद्धि का घर )
फॉर्मूला ब्रेकडाउन:
- COUNTIF(B5:B9,F4) → एक श्रेणी के भीतर उन कक्षों की संख्या की गणना करता है जो दी गई शर्त को पूरा करते हैं। यहां, B5:B9 श्रेणी है तर्क जो सर्वश्रेष्ठ विक्रेता पुस्तकें . को संदर्भित करता है . निम्नलिखित, F4 मानदंड . का प्रतिनिधित्व करता है तर्क जो मिलान किए गए मान की गिनती लौटाता है।
- आउटपुट → 1
- VLOOKUP(F4,B5:C9,2,TRUE) → यहां, F4 ( lookup_value तर्क) B5:C9 . से मैप किया गया है (table_array तर्क) सरणी। अगला, 2 (col_index_num तर्क) लुकअप मान के स्तंभ संख्या का प्रतिनिधित्व करता है। अंत में, सत्य (range_lookup तर्क) अनुमानित मिलान . को संदर्भित करता है लुकअप मान का।
- आउटपुट → 25
- IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0) → बन जाता है
- IF(1,25,0) → जाँचता है कि क्या कोई शर्त पूरी हुई है और एक मान लौटाता है यदि TRUE और दूसरा मान अगर गलत . यहां, 1 तार्किक_परीक्षा है तर्क जो IF . को संकेत देता है 25 return लौटने के लिए कार्य करें (value_if_true तर्क) अन्यथा यह 0 returns लौटाता है (value_if_false तर्क)।
- आउटपुट → $25
नतीजतन, परिणाम नीचे दिखाए गए स्क्रीनशॉट की तरह दिखना चाहिए।
विधि-4 :एक्सेल में VLOOKUP और INDIRECT फंक्शन्स का उपयोग करके डेटा मैप करना
आप डेटा मैप . भी कर सकते हैं एक्सेल में अप्रत्यक्ष . को मिलाकर और वीलुकअप कार्य। अब, चरणों के माध्यम से चलते हैं।
मान लें कि हमारे पास किराने की सूची है B4:I10 . में दिखाया गया डेटासेट कोशिकाएं। डेटासेट कीमत . दिखाता है एक ही आइटम . के 3 . में अमेरिका . के शहर ।
कदम :
- सबसे पहले, B5 . पर जाएं सेल>> डेटा क्लिक करें टैब>> फिर डेटा सत्यापन . दबाएं ड्रॉप-डाउन.
यह डेटा सत्यापन खोलता है डायलॉग बॉक्स।
- दूसरा, अनुमति दें . में ड्रॉप-डाउन सूची चुनें विकल्प>> स्रोत . में फ़ील्ड में, B6:B10 . चुनें सेल>> ठीक . क्लिक करें बटन।
- तीसरा, B6:C10 . चुनें सेल>> सूत्रों . पर जाएं टैब>> नाम परिभाषित करें . पर डबल-क्लिक करें विकल्प।
इससे नाम संपादित करें खुल जाता है जादूगर।
- यहां, एक उपयुक्त नाम दर्ज करें (इस मामले में, बोस्टन ) डेटा श्रेणी के लिए>> ठीक . क्लिक करें बटन।
इसी तरह, नामित श्रेणी को परिभाषित करें अटलांटा . के लिए ।
इसी तरह, नामित श्रेणी को परिभाषित करें डेनवर . के लिए ।
- चौथा, C14 . पर नेविगेट करें सेल>> डेटा टैब . पर , और डेटा सत्यापन . क्लिक करें बटन।
- इसी तरह, सूची . चुनें विकल्प>> नामित श्रेणी दर्ज करें जैसा कि नीचे स्क्रीनशॉट में दिखाया गया है।
अब, आइटम . चुनें और स्थान ड्रॉप-डाउन से। उदाहरण के लिए, हमने टमाटर . चुना है और अटलांटा क्रमशः।
- बाद में, D14 . में निम्नलिखित अभिव्यक्ति में सेल प्रकार।
=VLOOKUP(B14,INDIRECT(C14),2,FALSE)
यहां, B14 और C14 सेल आइटम . की ओर इशारा करते हैं और स्थान क्रमशः।
फॉर्मूला ब्रेकडाउन:
- अप्रत्यक्ष(C14) → टेक्स्ट स्ट्रिंग द्वारा निर्दिष्ट संदर्भ देता है। यहां, C14 ref_text है तर्क जो नामित श्रेणी . को संदर्भित करता है बोस्टन ।
- VLOOKUP(B14,INDIRECT(C14),2,FALSE) → यहां, B14 ( lookup_value तर्क) को नामित श्रेणी, . से मैप किया गया है अप्रत्यक्ष(C14) वह है table_array बहस। अगला, 2 (col_index_num तर्क) लुकअप मान के स्तंभ संख्या का प्रतिनिधित्व करता है। अंत में, गलत (range_lookup तर्क) सटीक मिलान . को संदर्भित करता है लुकअप मूल्य का।
- आउटपुट → $1.2
इसके बाद, उपरोक्त चरणों को पूरा करने के बाद परिणाम नीचे दिए गए चित्र जैसा दिखना चाहिए।
इसके अलावा, यदि आपके पास एकाधिक कार्यपत्रकों . में डेटा है, तो भी आप इस पद्धति का उपयोग कर सकते हैं और आप मानचित्र . करना चाहते हैं यह लुकअप . के आधार पर विशिष्ट कार्यपत्रकों से मूल्य।
Nth घटना प्राप्त करने के लिए VLOOKUP फ़ंक्शन लागू करना
मान लीजिए कि आपके पास स्टेशनरी बिक्री . है सूची जैसा कि B4:D13 . में दिखाया गया है नीचे की कोशिकाएँ। अब, आप उसी ग्राहक द्वारा खरीदी गई दूसरी या तीसरी वस्तु जानना चाहते हैं। इस समस्या को हल करने के लिए, हम COUNTIF . को नियोजित कर सकते हैं और VLOOKUP परिणाम प्राप्त करने के लिए कार्य करता है। तो, आइए प्रक्रिया को विस्तार से देखें।
कदम :
- सबसे पहले, कॉलम B . में एक कॉलम डालें और इसके शीर्षलेख का नाम बदलकर सहायक स्तंभ . कर दें>> B5 . में सेल नीचे दिए गए सूत्र को दर्ज करें।
=C5&COUNTIF($C$5:C5, C5)
📄 नोट: कृपया डेटासेट के सबसे बाईं ओर हेल्पर कॉलम डालना सुनिश्चित करें, क्योंकि डिफ़ॉल्ट रूप से VLOOKUP फ़ंक्शन बाएं से दाएं दिखता है।
यहां, C5 सेल नाम . को संदर्भित करता है जॉन और COUNTIF फ़ंक्शन जॉन . की घटना की गणना करता है दी गई सीमा से $C$5:C5 . अंत में, एम्परसेंड (&) ऑपरेटर टेक्स्ट और नंबर को जोड़ता है।
- अगला, नाम दर्ज करें और उदाहरण , उदाहरण के लिए, यह यहाँ है जूली , और 3>> H6 . पर जाएं सेल और नीचे दिए गए एक्सप्रेशन में टाइप करें।
=VLOOKUP(H4&H5, B5:E13, 3, FALSE)
इस सूत्र में, H4 और H5 सेल नाम . इंगित करते हैं और उदाहरण ।
उपरोक्त चरणों को पूरा करने के बाद, परिणाम नीचे दिखाए गए स्क्रीनशॉट जैसा दिखना चाहिए।
#N/A त्रुटि छिपाने के लिए VLOOKUP और IF फ़ंक्शन का उपयोग करना
आप ISNA . को नियोजित कर सकते हैं और VLOOKUP IF फ़ंक्शन . में कार्य करता है छिपाने के लिए #N/A त्रुटियाँ जब एक अमान्य लुकअप मान दिया हुआ है। अब, मुझे नीचे दी गई प्रक्रिया को प्रदर्शित करने की अनुमति दें।
कदम :
- शुरुआत में, G5 . पर नेविगेट करें सेल और नीचे अभिव्यक्ति दर्ज करें।
=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))
इस सूत्र में, G4 सेल आईडी . को संदर्भित करता है 1008 और B5:D14 कक्षों की श्रेणी आईडी . का प्रतिनिधित्व करती है , नाम , और विभाग कॉलम।
फॉर्मूला ब्रेकडाउन:
- ISNA(VLOOKUP(G4, B5:D14,3,FALSE)) → जांचें कि क्या कोई मान #NA है , और TRUE . लौटाता है या गलत . यहां, G4 ( lookup_value तर्क) को B5:D14 . से मैप किया गया है (table_array तर्क) सरणी। अगला, 3 (col_index_num तर्क) लुकअप मान के स्तंभ संख्या का प्रतिनिधित्व करता है। अंत में, गलत (range_lookup तर्क) सटीक मिलान . को संदर्भित करता है लुकअप मान का।
- आउटपुट → FALSE
- IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE)) → बन जाता है
- IF(FALSE, "",VLOOKUP(G4, B5:D14,3,FALSE)) → जाँचता है कि क्या कोई शर्त पूरी हुई है और एक मान लौटाता है यदि TRUE और दूसरा मूल्य अगर। यहां, गलत तार्किक_परीक्षा है तर्क जो IF . को संकेत देता है VLOOKUP . से मान वापस करने के लिए कार्य करें फ़ंक्शन (value_if_true तर्क) अन्यथा यह खाली हो जाता है “” (value_if_false तर्क)।
- आउटपुट → एचआर
अंत में, आपको नीचे दी गई तस्वीर में दिखाए गए परिणाम प्राप्त करने चाहिए।
इसके अतिरिक्त, यदि हम कोई अमान्य ID . दर्ज करते हैं नंबर (आईडी 1012 ), सूत्र #N/A . के बजाय एक रिक्त मान देता है त्रुटि जो नीचे दिए गए स्क्रीनशॉट में दिखाई गई है।
अभ्यास अनुभाग
हमने एक अभ्यास . प्रदान किया है प्रत्येक शीट के दाईं ओर अनुभाग ताकि आप स्वयं अभ्यास कर सकें। कृपया इसे स्वयं करना सुनिश्चित करें।
निष्कर्ष
मुझे आशा है कि एक्सेल में डेटा कैसे मैप करें पर सभी विधियां VLOOKUP . का उपयोग करके अब आपको उन्हें अपने एक्सेल स्प्रेडशीट में अधिक प्रभावी ढंग से लागू करने के लिए प्रेरित करेगा। यदि आपका कोई प्रश्न या प्रतिक्रिया है, तो कृपया मुझे टिप्पणी अनुभाग में बताएं। या आप इस वेबसाइट पर एक्सेल फंक्शन्स से संबंधित हमारे अन्य लेख देख सकते हैं।