VLOOKUP कमाल है और मेरा पसंदीदा कार्य है
इतना ही नहीं इन तालिकाओं से डेटा को ताज़ा करना आसान हो जाता है, वे पढ़ने के फॉर्मूले को भी आसान बना देते हैं! सूत्र लिखने से पहले केवल एक चीज जो आपको करनी है वह है Ctrl + T दबाने की।
चलो ऊपर से VLOOKUP सूत्र पर वापस जाएं। इस बार, शुरू से ही अपनी आइटम तालिका और अपनी खरीद तालिका को Ctrl + T के साथ एक्सेल तालिका में बदलें! चीजों को आसान बनाने के लिए, टेबल टूल टैब का उपयोग करके प्रत्येक तालिका को एक अनुकूल नाम दें:
अब अलग से कुछ भी किए बिना फिर से VLOOKUP में टाइप करें, सामान्य रूप से C2 में आपका फॉर्मूला अब =VLOOKUP((@Item),Items,2,0)इसके बजाय है =VLOOKUP(B2,$E$5:$F$10,2,0)!
भले ही आइटम तालिका अलग कार्यपत्रक पर हो, लेकिन सूत्र कम पठनीय के बजाय समान है =VLOOKUP(B2,Items!$A$2:$B$7,2,0)।
सूत्र में (@Item) इस तालिका के आइटम स्तंभ में सेल को संदर्भित करता है (सूत्र के रूप में एक ही पंक्ति में) और इसलिए पूरे स्तंभ में समान है। और आइटम पूरे आइटम तालिका (हेडर के बिना) को संदर्भित करता है। सबसे अच्छा, आपको इसमें से कोई भी टाइप करने की आवश्यकता नहीं है। एक बार जब यह एक तालिका होती है, तो एक्सेल आपके फार्मूले / रेंज का चयन करते हुए इन नामों को आपके फॉर्मूले में रख देगा!
आइए इसे एक कदम आगे बढ़ाएं। सूत्र के साथ राजस्व की गणना करने के लिए बिक्री तालिका में एक और कॉलम जोड़ें =(@Price)*(@Qty)। यदि आप अब कुल राजस्व की गणना करना चाहते हैं, तो सूत्र है =SUM(Sales(Revenue)); जो वास्तव में समझने में आसान है, कोई फर्क नहीं पड़ता कि डेटा कहाँ है या कितनी पंक्तियों को कवर करता है!
वीडियो देखेंा
- VLOOKUP कमाल है और मेरा पसंदीदा कार्य है
- VLOOKUP नफरत करने वालों की शिकायत है कि यह 3 तर्क के कारण नाजुक है
- यदि आपकी लुकअप तालिका का आकार बदलता है तो उत्तर बदल सकते हैं
- एक समाधान को तीसरे तर्क को MATCH से बदलना है
- लेकिन VLOOKUP की 1000 पंक्तियों के लिए MATCH करने की कल्पना करें
- VLOOKUP करने से पहले अपने लुकअप टेबल को टेबल में बना लें
- यदि तालिका आकार बदलता है तो संरचित तालिका संदर्भ संभाल लेगा
- साथ ही इसे बार-बार MATCH करने की आवश्यकता नहीं होती है
- पीटर अल्बर्ट ने इस टिप को प्रस्तुत किया
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट के लिए एक्सेल जानें, एपिसोड 2003 - पठनीय संदर्भ
एक्स्ट्रा लार्ज प्लेलिस्ट की सदस्यता के लिए मत भूलना। मैं इस पूरी किताब को पॉडकास्ट करूंगा।
पीटर अल्बर्ट से ठीक आज की टिप। पीटर अल्बर्ट। अब बात करते हैं VLOOKUP की। मैं एक विशाल VLOOKUP प्रशंसक हूं। मेरे लिए VLOOKUP विभाजन रेखा है। यदि आप VLOOKUPs कर सकते हैं, तो Excel में बाकी सब कुछ आपके लिए आसान होने वाला है। तो VLOOKUP हमें उस तालिका से कीमत देखने देता है और हम बाद में VLOOKUP के बारे में बात करेंगे।
तो इसे कॉपी करें और सब कुछ ठीक काम करता है लेकिन मुझे आपको बताना होगा। मैंने उन्हें देखा है। मैंने उनसे बात की है। मैं उनसे मिल चुका हूं। वहाँ VLOOKUP सलामी बल्लेबाज हैं। जो लोग नफरत करते हैं यदि आप देखते हैं और क्या अन्य शिकायतें हैं कि यह बहुत नाजुक है, तीसरा तर्क, जहां हमने कहा कि हम तीसरा स्तंभ चाहते हैं, कि अगर कोई बाद में तय करेगा कि हमें यहां एक नया क्षेत्र चाहिए, जैसे, आकार । ठीक है, पहली बार, ऐसा प्रतीत होता है कि बग किसी प्रकार का है जो कि एक्सेल उस पूरी चीज़ को पुनर्गणना नहीं कर रहा है। मुझे पूर्ववत करें, पूर्ववत करें और फिर फिर से करें। हम वहाँ चलें। यह अजीब है, मुझे लगता है कि एक्सेल टीम को रिपोर्ट किया गया था, लेकिन आप देखते हैं कि जहां हमें कीमत मिल रही थी, अब यह रंग हो रहा है, क्योंकि यह कहना मुश्किल है कि उन्हें तीसरा कॉलम चाहिए। ठीक है और लोग इसके चारों ओर काम करने के लिए क्या करते हैं = MATCH के साथ यह पागल बात है।तालिका की पहली पंक्ति F4,0 में मूल्य शब्द के लिए जाएँ और हमें बताएगा कि इस बिंदु पर मूल्य चौथा स्तंभ है। तो वे वास्तव में = VLOOKUP करेंगे। हम इस तालिका में A104 को देख रहे हैं। F4 और फिर नंबर चार पर हार्ड-कोडिंग के बजाय वे एक MATCH करते हैं और MATCH कीमत पर लॉक होने वाला है। तो F4, 1 से पहले $ लगाने के लिए दो बार और यह तालिका की पहली पंक्ति के माध्यम से देखने जा रहा है। उफ़, एफ 4 दो बार, अल्पविराम, अल्पविराम से चूक गया। मैच के लिए एक सटीक मैच के लिए यहाँ ठीक F4 प्रेस अल्पविराम 0 और फिर अल्पविराम एक सटीक मैच के लिए VLOOKUP पर आता है। हाँ और हे यह महान काम करता है और यहाँ मेरे पास केवल छह हैं इसलिए यह कोई बड़ी बात नहीं है।इस तालिका में। F4 और फिर नंबर चार पर हार्ड-कोडिंग के बजाय वे एक MATCH करते हैं और MATCH कीमत पर लॉक होने वाला है। तो F4, 1 से पहले $ लगाने के लिए दो बार और यह तालिका की पहली पंक्ति के माध्यम से देखने जा रहा है। उफ़, एफ 4 दो बार, अल्पविराम, अल्पविराम से चूक गया। मैच के लिए एक सटीक मैच के लिए यहाँ ठीक F4 प्रेस अल्पविराम 0 और फिर अल्पविराम एक सटीक मैच के लिए VLOOKUP पर आता है। हाँ और हे यह महान काम करता है और यहाँ मेरे पास केवल छह हैं इसलिए यह कोई बड़ी बात नहीं है।इस तालिका में। F4 और फिर नंबर चार पर हार्ड-कोडिंग के बजाय वे एक MATCH करते हैं और MATCH कीमत पर लॉक होने वाला है। तो F4, 1 से पहले $ लगाने के लिए दो बार और यह तालिका की पहली पंक्ति के माध्यम से देखने जा रहा है। उफ़, एफ 4 दो बार, अल्पविराम, अल्पविराम से चूक गया। मैच के लिए एक सटीक मैच के लिए यहाँ ठीक F4 प्रेस अल्पविराम 0 और फिर अल्पविराम एक सटीक मैच के लिए VLOOKUP पर आता है। हाँ और हे यह महान काम करता है और यहाँ मेरे पास केवल छह हैं इसलिए यह कोई बड़ी बात नहीं है।मैच के लिए एक सटीक मैच के लिए यहाँ ठीक F4 प्रेस अल्पविराम 0 और फिर अल्पविराम एक सटीक मैच के लिए VLOOKUP पर आता है। हाँ और हे यह महान काम करता है और यहाँ मेरे पास केवल छह हैं इसलिए यह कोई बड़ी बात नहीं है।मैच के लिए एक सटीक मैच के लिए यहाँ ठीक F4 प्रेस अल्पविराम 0 और फिर अल्पविराम एक सटीक मैच के लिए VLOOKUP पर आता है। हाँ और हे यह महान काम करता है और यहाँ मेरे पास केवल छह हैं इसलिए यह कोई बड़ी बात नहीं है।
देखें कि क्या मैं एक नया इन्सर्ट करता हूँ या नहीं, यह अपने आप एडजस्ट हो जाएगा और कीमत मिलती रहेगी लेकिन जरा सोचिए अगर आपके पास एक हजार VLOOKUPs हों और हर एक VLOOKUP उस मैच को फिर से लॉन्च करने जा रहा हो तो उस कीमत को पांचवें कॉलम या चौथे कॉलम में पता करें। यह भयानक है। तालिकाएँ बस इस समस्या को हल करती हैं। तो यहाँ मेरी VLOOKUP तालिका है, कुछ भी करने से पहले यह बहुत लंबा है, मैं यहाँ जाने के लिए जा रहा हूँ और इसे असली तालिका में बनाने के लिए CTRL T। वे इसे तालिका 1 कहने जा रहे हैं, लेकिन मैं इसे ProductTable, सभी एक शब्द, कोई स्थान नहीं कहूंगा: ProductTable। तो अब इसका एक नाम है। ठीक है, इसलिए अब हमारे पास ProductTable नाम की एक तालिका है। फिर हम यहां आते हैं और कहते हैं कि हम उन कीमतों के बारे में जानने वाले हैं। हमें कौन सी कीमत चाहिए? हम इन मदों में A104 के मैच से परिणाम चाहते हैं। सटीक मैच, इंडेक्स के लिए करीब कोष्ठक।यह केवल एक ही मैच कर रहा है। यह एक मैच और एक VLOOKUP नहीं कर रहा है। तरह, बहुत तेज हो जाएगा। नीचे कॉपी करें। ठीक है और फिर बाद में अगर हम आकार सम्मिलित करते हैं, तो स्तंभ सम्मिलित करें, आकार सब कुछ काम करना जारी रखता है क्योंकि यह मूल्य नामक स्तंभ की तलाश में है और मान लीजिए कि यदि हम इसे सूची मूल्य में बदलते हैं, तो वह सूत्र फिर से लिखा जाता है। ठीक है, बहुत, बहुत सुरक्षित, सुरक्षित रास्ता तय करना।
ठीक है, तालिकाओं में बहुत सारे शांत चालें। एक्सेल टेबल्स पर केविन जोन्स और ज़ैच बार्सरे की इस पुस्तक को देखें। वहाँ और अगस्त और सितंबर में पॉडकास्टिंग कर रहे हैं कि सब कुछ चालें इस जाम से भरी किताब में है। साथ ही बहुत सारी मस्ती। एक्सेल चुटकुले। एक्सेल कॉकटेल। एक्सेल ट्वीट। एक्सेल रोमांच। जाम भरे-पूरे रंग में। इसे देखें, इस पुस्तक को खरीदें। मुझे वास्तव में इसकी प्रशंसा करनी होगी।
ठीक है आज का एपिसोड। VLOOKUP बहुत बढ़िया है और यह मेरा पसंदीदा कार्य है लेकिन वहाँ VLOOKUP नफरत करता है जो शिकायत करता है कि यह उस तीसरे तर्क के कारण नाजुक है, यदि आपकी तालिका का आकार VLOOKUP तालिका बदलता है, तो उत्तर बदल जाएंगे। एक वर्कअराउंड उस तीसरे तर्क को MATCH से बदलना है, लेकिन jeez, VLOOKUP की एक हजार पंक्तियों के लिए MATCH करने की कल्पना करता है। तो VLOOKUP करने से पहले अपने VLOOKUP को एक टेबल में बना लें। अगर टेबल शेप में बदलाव होता है तो स्ट्रक्चर टेबल रिफरेंस हैंडल करेगा। इसके अलावा आप एक VLOOKUP और एक मैच नहीं कर रहे हैं। INDEX और INDEX के साथ बस एक ही मैच बिजली की तेजी से चमक रहा है।
इस टिप के लिए पीटर रॉबर्ट को धन्यवाद और द्वारा रोकने के लिए धन्यवाद। हम आपको अगली बार, एक और नेटकास्ट से देखेंगे।
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2003.xlsx
