यह एक और सूत्र गति उदाहरण है। यह कहें कि आपको VLOOKUP के 12 कॉलम करने हैं। आप एक MATCH और 12 INDEX फ़ंक्शंस का उपयोग करके इसे तेज़ बना सकते हैं।
निम्नलिखित आंकड़ों में, आपको प्रत्येक खाता संख्या के लिए 12 VLOOKUP फ़ंक्शन करने होंगे। VLOOKUP शक्तिशाली है, लेकिन गणना करने में बहुत समय लगता है।
साथ ही, सूत्र को प्रत्येक सेल में संपादित किया जाना चाहिए जैसा कि आप भर में कॉपी करते हैं। तीसरे तर्क को फरवरी के लिए 2 से 3 तक बदलना है, फिर मार्च के लिए 4, और इसी तरह।
एक वर्कअराउंड कॉलम संख्याओं के साथ एक पंक्ति जोड़ना है। फिर, VLOOKUP का तीसरा तर्क इस पंक्ति को इंगित कर सकता है। कम से कम आप एक ही फॉर्मूले को B4 से कॉपी कर सकते हैं और पूरे सेट को कॉपी करने से पहले C4: M4 पर पेस्ट कर सकते हैं।
लेकिन यहाँ एक बहुत तेजी से दृष्टिकोण है। एक नया कॉलम B कहां से जोड़ें? शीर्षक के रूप में। कॉलम B में MATCH फ़ंक्शन होता है। यह फ़ंक्शन VLOOKUP के समान है: आप स्तंभ P4: P227 में A4 में मूल्य की तलाश कर रहे हैं। अंत में 0 VLOOKUP के अंत में गलत की तरह है। यह निर्दिष्ट करता है कि आप एक सटीक मिलान चाहते हैं। यहां बड़ा अंतर है: MATCH रिटर्न जहां मूल्य पाया जाता है। 208 का उत्तर कहता है कि A308 P4: P227 की रेंज में 208 वां सेल है। एक पुनर्गणना समय के दृष्टिकोण से, MATCH और VLOOKUP समान हैं।
मैं वही सुन सकता हूं जो आप सोच रहे हैं। “यह जानना अच्छा है कि कुछ कहाँ स्थित है? मैंने कभी किसी प्रबंधक को फोन करके यह नहीं पूछा, 'वह कौन सी पंक्ति है जो प्राप्य है?'
जबकि मनुष्य शायद ही कभी पूछते हैं कि कोई चीज किस पंक्ति में है, INDEX फ़ंक्शन उस स्थिति का उपयोग कर सकता है। निम्न सूत्र Excel को Q4: Q227 से 208 वाँ आइटम वापस करने के लिए कहता है।
जैसे ही आप इस सूत्र को कॉपी करते हैं, मानों की सरणी लुकअप टेबल के पार चली जाती है। प्रत्येक पंक्ति के लिए, आप एक MATCH और 12 INDEX फ़ंक्शन कर रहे हैं। INDEX फ़ंक्शन VLOOKUP की तुलना में अविश्वसनीय रूप से तेज़ है। सूत्रों का पूरा सेट VLOOKUP के 12 कॉलमों की तुलना में 85% अधिक तेजी से गणना करेगा।
वीडियो देखेंा
- यह कहें कि आपको VLOOKUP के 12 कॉलम करने हैं
- लुकअप वैल्यू के कॉलम से पहले एक डॉलर के चिन्ह का सावधानी से उपयोग करें
- लुकअप टेबल के लिए चार डॉलर के संकेतों का सावधानी से उपयोग करें
- आप तीसरे कॉलम तर्क को अभी भी हार्ड-कोडिंग कर रहे हैं।
- एक सामान्य समाधान कॉलम संख्या के साथ सहायक कोशिकाओं की एक पंक्ति को जोड़ना है।
- एक और कम कुशल समाधान VLOOKUP सूत्र के अंदर COLUMN (B2) का उपयोग करना है।
- लेकिन, प्रत्येक पंक्ति के लिए 12 VLOOKUP करना बहुत ही अक्षम है
- इसके बजाय, WHERE के शीर्षक के साथ एक सहायक कॉलम जोड़ें और एक ही मिलान करें।
- MATCH जनवरी के लिए VLOOKUP जितना लंबा है।
- फिर आप 12 INDEX फ़ंक्शन का उपयोग कर सकते हैं। ये VLOOKUP की तुलना में अविश्वसनीय रूप से तेज़ हैं।
- INDEX पंक्तियों से पहले $ के साथ उत्तरों के एक एकल कॉलम को इंगित करेगा।
- INDEX एक कॉलम से पहले एक $ के साथ सहायक स्तंभ को इंगित करेगा।
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट से एक्सेल सीखें, एपिसोड 2028 - एक मैच के साथ कई VLOOKUPs को बदलना!
प्
अरे, नेटकास्ट में वापस स्वागत है, मैं बिल जेलन हूँ! वैसे यह एक क्लासिक समस्या है, हमें हर महीने में एक बार VLOOKUP करना होगा, है ना? और आप स्तंभ के नीचे उस लॉक करने के लिए F4 3 बार दबाने के बारे में यहां अविश्वसनीय रूप से सावधान हो सकते हैं, और फिर पूरी पंक्ति को लॉक करने के बाद F4 दबा सकते हैं। लेकिन जब आप इस बिंदु पर पहुंचते हैं, तो 2, FALSE कि 2 हार्ड-कोडेड है, और जैसा कि आप उस पर कॉपी करते हैं, आपको 2 को 3, सही में संपादित करना होगा? अब, ऐसा करने का एक अक्षम तरीका, एक ऐसा तरीका जो मुझे पसंद नहीं है वह है B1 के कॉलम का उपयोग करना। कॉलम B1 बेशक 2 का है, लेकिन जैसा कि आप इसे कॉपी करते हैं, देखते हैं कि यह कॉलम C1 में बदल जाएगा, जो कि 3 है, लेकिन इस बारे में सोचें, यह लगातार कॉलम नंबर को बार-बार समझ रहा है। तो मैं देख रहा हूं कि लोग क्या करते हैं और क्यों करते हैं, आप जानते हैं, कॉलम से अधिक पसंद करते हैं, क्या हम Ctrl-drag करेंगे,हेल्पर सेल में संख्या 2-13 ऊपर रखें, और फिर, जब हम इस बिंदु पर पहुंचते हैं, हम ऊपर जाते हैं और उस कॉलम नंबर को निर्दिष्ट करते हैं। F4 को पंक्ति, FALSE इत्यादि पर लॉक करने के लिए 2 बार दबाएं। लेकिन उस पद्धति के साथ भी, VLOOKUP अविश्वसनीय रूप से अक्षम है, क्योंकि इसे इन सभी वस्तुओं के माध्यम से यहां खोज करना है जब तक कि यह A308 नहीं मिलता है और यह B4 का आंकड़ा है। जब यह फिर C4 पर चला जाता है, तो यह भूल जाता है कि यह बस गया और देखा, और यह फिर से शुरू होता है, ठीक है। इसलिए आपके पास Excel, VLOOKUP, FALSE सभी में एक से अधिक धीमी गति से कार्य किए जा रहे हैं।क्योंकि इसे इन सभी वस्तुओं के माध्यम से यहाँ जाना है, जब तक कि इसे A308 नहीं मिल जाता है और यह B4 है। जब यह फिर C4 पर चला जाता है, तो यह भूल जाता है कि यह बस गया और देखा, और यह फिर से शुरू होता है, ठीक है। इसलिए आपके पास Excel, VLOOKUP, FALSE सभी में एक से अधिक धीमी गति से कार्य किए जा रहे हैं।क्योंकि इसे इन सभी वस्तुओं के माध्यम से यहाँ जाना है, जब तक कि इसे A308 नहीं मिल जाता है और यह B4 है। जब यह फिर C4 पर चला जाता है, तो यह भूल जाता है कि यह बस गया और देखा, और यह फिर से शुरू होता है, ठीक है। इसलिए आपके पास Excel, VLOOKUP, FALSE सभी में एक से अधिक धीमी गति से कार्य किए जा रहे हैं।
तो यहाँ बहुत कुछ है, बहुत तेज़ रास्ता है, हम एक सहायक स्तंभ डालने जा रहे हैं, और यह सहायक स्तंभ मैं इसे कहाँ कहता हूँ? जैसे कि हॉक A308 कहाँ है? हम एक = MATCH का उपयोग करेंगे, तालिका की पहली पंक्ति में A308 की तलाश करें, F4 दबाएं, एक सटीक मिलान के लिए 0, ठीक है, यह हमें बताता है कि "अरे, वह देखो, यह पंक्ति में है, 6, कैसे कमाल है? ” लेकिन जैसा कि हम कॉपी करते हैं, देखते हैं, यह हर समय अलग-अलग जगहों पर है। ठीक है, अब यह मैच तब तक चलता है जब तक जनवरी VLOOKUP लेता है, वहां वे मर चुके हैं, लेकिन यहां आश्चर्यजनक बात है। वहाँ से हमें कभी भी शेष पंक्ति के लिए एक VLOOKUP नहीं करना पड़ता, हम बस कर सकते थे = INDEX, INDEX कहता है "यहाँ उत्तर की एक सरणी है।" मैं जनवरी सेल में जाने वाला हूं, और मैं बहुत सावधानी से यहां जा रहा हूं F4 को 2 बार दबाएं ताकि मैं इसे 4: 227 पर बंद कर दूं,लेकिन क्यू मैं बदलने के लिए अनुमति दी है। कॉमा, और फिर यह जानना चाहता है कि कौन सी पंक्ति, अच्छी तरह से कि बी 4 में जवाब होने जा रहा है, मैं $ 4 को बी से पहले प्राप्त करने के लिए F4 दबाऊंगा, ठीक है, उस पार कॉपी करें।
यह सूत्र, ये INDEX सूत्र, ये 12 उस समय से भी कम समय में होगा, जब इसे फरवरी VLOOKUP करने में समय लगेगा, ठीक है। यदि हम इस पर चार्ल्स विलियम्स का टाइमर लगाते हैं, तो यह पूरी चीज़ 12 VLOOKUP के समय के लगभग 14% की गणना करेगी। आपका प्रबंधक कहाँ देखना नहीं चाहता है? ठीक है, बस उस कॉलम को छिपाएं, सब कुछ काम करता रहता है, ठीक है, यह 12 महीने या 52 सप्ताह के वीएलबुकअप को गति देने का एक सुंदर तरीका है। ठीक है, यह टिप, और बहुत अधिक युक्तियां, इस पुस्तक में हैं। वहां टॉप-राइट हैंड कार्नर पर "i" पर क्लिक करें, आप किताब, $ 10 ई-बुक, प्रिंट बुक के लिए $ 25, ठीक खरीद सकते हैं।
इसलिए आज हमारे सामने एक समस्या थी जहां वीएलबुक के 12 कॉलम, आप ध्यान से $ को अंदर रख सकते हैं, लेकिन फिर उस 3 तर्क को अभी भी हार्डकोड करना होगा। आप कॉलम (बी 2) का उपयोग कर सकते हैं, मैं उसका प्रशंसक नहीं हूं, क्योंकि सैकड़ों पंक्तियां हैं * 12 कॉलम जहां उस पर और बार-बार गणना की जाती है। बस एक पंक्ति में एक हेल्पर सेल का उपयोग करें, संख्या 2-12 डालें और उस पर इंगित करें, यह अभी भी अक्षम है, हालांकि, क्योंकि VLOOKUP जनवरी के आंकड़े के बाद, इसे फरवरी की शुरुआत में वापस शुरू करना होगा। इसलिए मैं "कहाँ?" शीर्षक से एक कॉलम जोड़ने की सलाह देता हूँ। और वहां एक ही MATCH कर रहा है। MATCH को जनवरी के लिए VLOOKUP के रूप में लंबे समय तक लगता है, लेकिन फिर 12 INDEX फ़ंक्शन फरवरी के लिए VLOOKUP की तुलना में कम समय लेंगे, और आपने समय का एक पूरा गुच्छा ट्रिम कर दिया है। फिर, दोनों स्थानों में INDEX फ़ंक्शन में $ से सावधान, पंक्तियों से ठीक पहले,और स्तंभों से पहले दूसरा, उन दोनों में एक मिश्रित संदर्भ।
अरे, मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं, हम अगली बार आपको एक और नेटकास्ट के लिए देखेंगे!
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2028.xlsx
