एक्सेल का OFFSET फ़ंक्शन आपकी कार्यपुस्तिका की गणना को धीमा कर देगा। एक बेहतर विकल्प है: INDEX का एक असामान्य वाक्य-विन्यास।
यह एक आला टिप है। एक आश्चर्यजनक रूप से लचीला कार्य है जिसे OFFSET कहा जाता है। यह लचीला है क्योंकि यह एक अलग-अलग आकार की सीमा को इंगित कर सकता है जो कि ऑन-द-फ्लाई की गणना की जाती है। नीचे दी गई छवि में, यदि कोई H1 में # Qtrs ड्रॉपडाउन को 3 से 4 में बदलता है, तो OFFSET का चौथा तर्क यह सुनिश्चित करेगा कि सीमा चार स्तंभों को शामिल करने का विस्तार करती है।
स्प्रेडशीट गुरु OFFSET से नफरत करते हैं क्योंकि यह एक अस्थिर कार्य है। यदि आप पूरी तरह से असंबंधित सेल में जाते हैं और एक नंबर दर्ज करते हैं, तो सभी OFFSET फ़ंक्शन गणना करेंगे। भले ही उस सेल का एच 1 या बी 2 से कोई लेना-देना न हो। अधिकांश समय, एक्सेल केवल उन कोशिकाओं की गणना करने में बहुत समय बर्बाद करता है जिनकी गणना करने की आवश्यकता होती है। लेकिन एक बार जब आप OFFSET का परिचय देते हैं, तो सभी OFFSET कोशिकाओं में से, और OFFSET से सब कुछ डाउनलाइन हो जाता है, कार्यपत्रक में हर बदलाव के बाद गणना शुरू कर देता है।
मैं न्यू यॉर्क सिटी में 2013 के मॉडलऑफ फाइनल का निर्णय ले रहा था जब ऑस्ट्रेलिया के मेरे कुछ दोस्तों ने एक विचित्र वर्कअराउंड बताया। नीचे दिए गए सूत्र में, INDEX फ़ंक्शन से पहले एक बृहदान्त्र है। आम तौर पर, नीचे दिखाया गया INDEX फ़ंक्शन सेल D2 से 1403 लौटाएगा। लेकिन जब आप INDEX फ़ंक्शन के दोनों ओर कॉलन लगाते हैं, तो यह D2 की सामग्री के बजाय सेल एड्रेस D2 को वापस करना शुरू कर देता है। यह जंगली है कि यह काम करता है।
यह बात क्यों है? INDEX अस्थिर नहीं है। आपको ओएफएफएसईटी की लचीली भलाई के सभी समय-समय पर बार-बार होने वाले पुनरावृत्तियों के बिना मिलते हैं।
मैंने पहली बार फिन मेइग में डैन मेयोह से यह टिप सीखी। इस सुविधा का सुझाव देने के लिए ऐक्सेस एनालिटिक्स का धन्यवाद।
वीडियो देखेंा
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट से एक्सेल सीखें, एपिसोड 2048 -: INDEX एक अस्थिर OFFSET की जगह लेगा!
अरे, मैं इस पुस्तक से अपने सभी सुझावों को पॉडकास्ट कर रहा हूं, प्लेलिस्ट में आने के लिए शीर्ष-दाएं हाथ के कोने में "i" पर क्लिक करें!
ठीक है, OFFSET एक अद्भुत समारोह है! OFFSET हमें एक शीर्ष बाएँ कोने के सेल को निर्दिष्ट करने की अनुमति देता है, और फिर वहाँ से परिभाषित करने के लिए चर का उपयोग करता है कि कितनी पंक्तियाँ नीचे हैं, कितनी पंक्तियाँ हैं, और फिर एक आकृति, ठीक परिभाषित करें। तो यहाँ, अगर मैं जोड़ना चाहता हूँ, या औसतन, 3/4 कहता हूँ, तो यह सूत्र यहाँ सूत्र पर ध्यान देगा। OFFSET का कहना है कि हम B2 से शुरू कर रहे हैं, Q1 से शुरू कर रहे हैं, हम 0 से नीचे जा रहे हैं, 0 से अधिक है, आकार 1 पंक्ति लंबा होगा, और यह H1 होगा, दूसरे शब्दों में 3 कोशिकाओं चौड़ा, ठीक है। तो इस मामले में हम वास्तव में कर रहे हैं बदल रहा है कि हम कितने सेल जोड़ रहे हैं, हम हमेशा बी 2 या बी 3 या बी 4 में वापस शुरू कर रहे हैं क्योंकि मैं नीचे कॉपी करता हूं, और फिर यह तय करता है कि कितने सेल चौड़े हैं। ठीक है, OFFSET यह शांत चीज है, यह सभी प्रकार के आश्चर्यजनक कार्य करता है, लेकिन यहां परेशानी है, यह अस्थिर है!जिसका मतलब है कि भले ही कुछ गणना श्रृंखला में नहीं है, एक्सेल, इसे पुनर्गणना करने के लिए समय लेने जा रहा है, जो चीजों को धीमा करने जा रहा है, ठीक है।
INDEX का यह अद्भुत संस्करण, ठीक है, आम तौर पर अगर मैं इन 4 कोशिकाओं के INDEX के लिए पूछूंगा, 3, यह संख्या 1403 वापस करने जा रहा है। हालांकि, जब मैं INDEX से पहले या बाद में कोई कॉलन लगाता हूं, तो कुछ बहुत अलग होता है, हम इस सूत्र पर एक नज़र डालेंगे। तो 4 कोशिकाओं का सूचकांक, जो मैं चाहता हूं, मैं 3 जी चाहता हूं, लेकिन आप देखते हैं कि वहां: सही है। तो हम हमेशा बी 2: ई 2 से जाने वाले हैं, और अगर फार्मूले पर जाएं, फॉर्मूला का मूल्यांकन करें, तो मैं आपको दिखाता हूं, ठीक है, इसलिए यहां यह संख्या 3 की गणना करने जा रहा है। और यहां, इसके साथ INDEX: अगला इसके बजाय, हमें 1403 बताने के बजाय, जो कि INDEX सामान्य रूप से कैसे गणना करेगा, यह $ D2 वापस करने जा रहा है, और फिर AVERAGE उन लोगों का औसत करेगा 3. बिल्कुल आश्चर्यजनक, जिस तरह से यह काम करता है, और अतिरिक्त लाभ, आदि। यह अस्थिर नहीं है, ठीक है,और इसका उपयोग अविश्वसनीय रूप से जटिल OFFSET को बदलने के लिए भी किया जा सकता है।
तो यहाँ इस OFFSET के साथ हम इन मूल्यों पर आधारित हैं। अगर मैं Q2 और सेंट्रल का चयन करता हूं, तो ठीक है, ये सूत्र MATCH और COUNTIF का उपयोग यह पता लगाने के लिए कर रहे हैं कि कितनी पंक्तियाँ नीचे हैं, कितने स्तंभ हैं, कितने लम्बे, कितने चौड़े हैं। और फिर OFFSET यहां उन सभी मानों का उपयोग कर रहा है जो कि औसत का पता लगा सकते हैं। हम सिर्फ यह सुनिश्चित करने के लिए एक परीक्षण करेंगे कि यह काम कर रहा है, इसलिए = उस नीले रंग की सीमा पर वहां के मेडियन, बेहतर 71, ठीक हैं, और हम यहां, Q3 और पश्चिम में कुछ और चुनेंगे, इसलिए। F2 दबाएं, मैं बस इसे खींचने जा रहा हूं और इसे उस सूत्र को फिर से लिखने के लिए आकार बदलें, Enter दबाएं, और यह OFFSET के साथ काम कर रहा है।
अच्छी तरह से यहाँ, एक INDEX का उपयोग करते हुए, मेरे पास वास्तव में बाईं ओर एक INDEX और दाईं ओर एक INDEX के साथ एक बृहदान्त्र है, इस बात को मूल्यांकन फॉर्मूला में गणना करें देखें। इसलिए यह शुरू होता है, मूल्यांकन करेगा, मूल्यांकन करेगा, और यहीं कि INDEX इसे एक सेल पते में बदलने वाला है। तो H16 1, पश्चिम, Q3 है, और दायीं ओर से अधिक का मूल्यांकन करेंगे, युगल अधिक, और फिर सही उसे I20 में बदल देता है। तो INDEX फ़ंक्शन का उपयोग करके एक OFFSET को बदलने के लिए शांत, शांत गैर-वाष्पशील। ठीक है, यह टिप और इस पुस्तक में कई और, पुस्तक खरीदने के लिए ऊपरी-दाएँ हाथ के कोने में "i" पर क्लिक करें।
ठीक से पुनर्कथन: OFFSET, भयानक, लचीला कार्य, एक बार जब आप मास्टर हो जाते हैं, तो आप सभी प्रकार की चीजें कर सकते हैं। एक वैरिएबल टॉप-लेफ्ट सेल को इंगित करें, एक ऐसी श्रेणी को इंगित करें, जिसमें एक वैरिएबल आकार हो, लेकिन यह अस्थिर है, और इसलिए वे हर गणना पर गणना करते हैं। एक्सेल आमतौर पर एक स्मार्ट गणना करता है, या यह उन कोशिकाओं को पुन: गणना करता है जिनकी गणना करने की आवश्यकता होती है, लेकिन OFFSET के साथ यह हमेशा गणना करेगा। OFFSET के बजाय आप INDEX: या: INDEX या यहां तक कि INDEX: INDEX का उपयोग कर सकते हैं, कभी भी INDEX के बगल में एक बृहदान्त्र होता है, यह उस सेल के मूल्य के बजाय एक सेल पता लौटा देगा। और लाभ यह है कि INDEX अस्थिर नहीं है!
ठीक है, मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं, हम अगली बार आपको एक और नेटकास्ट के लिए देखेंगे!
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2048.xlsm
