उदाहरण कार्यपुस्तिका डाउनलोड करें
हमने अन्य लेखों में चर्चा की है कि OFFSET और INDIRECT जैसे कार्य कैसे अस्थिर हैं। यदि आप स्प्रेडशीट में इनमें से कई का उपयोग करना शुरू करते हैं या कई सेल अस्थिर फ़ंक्शन पर निर्भर हैं, तो आप अपने कंप्यूटर को हर बार जब आप सेल बदलने का प्रयास करते हैं तो पुनर्गणना करने में ध्यान देने योग्य समय व्यतीत कर सकते हैं। आपका कंप्यूटर कैसे पर्याप्त तेज़ नहीं है, इस पर निराश होने के बजाय, यह आलेख उन सामान्य परिस्थितियों को हल करने के वैकल्पिक तरीकों का पता लगाएगा जो लोग ऑफ़सेट और अप्रत्यक्ष का उपयोग करते हैं।
गतिशील सूची बनाने के लिए OFFSET को बदलना
ऑफ़सेट फ़ंक्शन के बारे में जानने के बाद, यह एक आम ग़लतफ़हमी है कि अंतिम दो तर्कों का उपयोग करके गतिशील आकार के साथ परिणाम वापस करने का यही एकमात्र तरीका है। आइए कॉलम ए में एक सूची देखें जहां हमारा उपयोगकर्ता बाद में अतिरिक्त आइटम जोड़ने का निर्णय ले सकता है।
सेल C2 में एक ड्रॉपडाउन बनाने के लिए, आप एक नामांकित श्रेणी को एक अस्थिर सूत्र के साथ परिभाषित कर सकते हैं जैसे
=ऑफसेट($A$2, 0, 0, COUNTA($A:$A)-1, 1)
वर्तमान सेटअप के साथ, यह निश्चित रूप से श्रेणी A2:A5 का संदर्भ देगा। हालाँकि, गैर-वाष्पशील INDEX का उपयोग करने का एक और तरीका है। ऐसा करने के लिए, सोचें कि हम A2 से A5 तक जाने वाली श्रेणी का संदर्भ लिखते हैं। जब आप "A2:A5" लिखते हैं, तो इसे डेटा के एक टुकड़े के रूप में न समझें, बल्कि एक "स्टार्टिंगपॉइंट" और "एंडिंगपॉइंट" के रूप में एक कोलन द्वारा अलग किया जाता है (जैसे, स्टार्टिंगपॉइंट: एंडिंगपॉइंट)। एक सूत्र में, स्टार्टिंगपॉइंट और एंडिंगपॉइंट दोनों अन्य कार्यों के परिणाम हो सकते हैं।
यहां वह सूत्र है जिसका उपयोग हम INDEX फ़ंक्शन का उपयोग करके डायनामिक रेंज बनाने के लिए करेंगे:
=$A$2:INDEX($A:$A, COUNTA($A:$A))
ध्यान दें कि हमने कहा है कि इस श्रेणी के लिए शुरुआती बिंदु हमेशा A2 होगा। बृहदान्त्र के दूसरी ओर, हम यह निर्धारित करने के लिए INDEX का उपयोग कर रहे हैं कि EndingPoint कहाँ होगा। COUNTA यह निर्धारित करेगा कि कॉलम A में डेटा के साथ 5 सेल हैं, और इसलिए हमारा INDEX A5 के लिए एक संदर्भ बनाएगा। इस प्रकार सूत्र का मूल्यांकन इस प्रकार किया जाता है:
=$A$2:INDEX($A:$A, COUNTA($A:$A)) =$A$2:INDEX($A:$A, 5) =$A$2:$A5
इस तकनीक का उपयोग करके, आप INDEX फ़ंक्शन का उपयोग करके किसी भी सूची, या यहां तक कि द्वि-आयामी तालिका के लिए गतिशील रूप से एक संदर्भ बना सकते हैं। ऑफ़सेट फ़ंक्शंस की बहुतायत वाली स्प्रेडशीट में, ऑफ़सेट को INDEX से बदलने से आपका कंप्यूटर बहुत तेज़ी से चलना शुरू कर देगा।
शीट नामों के लिए अप्रत्यक्ष को बदलना
अप्रत्यक्ष फ़ंक्शन को अक्सर तब कहा जाता है जब कार्यपुस्तिकाओं को कई कार्यपत्रकों में बिखरे हुए डेटा के साथ डिज़ाइन किया गया हो। यदि आप एक ही शीट पर सभी डेटा प्राप्त नहीं कर सकते हैं, लेकिन एक अस्थिर फ़ंक्शन का उपयोग नहीं करना चाहते हैं, तो आप CHOOSE का उपयोग करने में सक्षम हो सकते हैं।
निम्नलिखित लेआउट पर विचार करें, जहां हमारे पास 3 अलग-अलग कार्यपत्रकों में बिक्री डेटा है। हमारी सारांश शीट पर, हमने चुना है कि हम किस तिमाही से डेटा देखना चाहते हैं।
B3 में हमारा सूत्र है:
=चुनें(मैच(बी2, डी2:डी4, 0), फॉल! ए2, विंटर! ए2, स्प्रिंग! ए2)
इस सूत्र में, MATCH फ़ंक्शन यह निर्धारित करने वाला है कि हम किस क्षेत्र में लौटना चाहते हैं। यह तब CHOOSE फ़ंक्शन को बताता है कि निम्न में से कौन सी श्रेणी परिणाम के रूप में वापस लौटना है।
आप बड़ी रेंज वापस करने के लिए CHOOSE फ़ंक्शन का भी उपयोग कर सकते हैं। इस उदाहरण में, हमारे पास हमारे तीन कार्यपत्रकों में से प्रत्येक पर बिक्री डेटा की एक तालिका है।
शीट नाम बनाने के लिए एक अप्रत्यक्ष फ़ंक्शन लिखने के बजाय, आप CHOOSE को यह निर्धारित करने दे सकते हैं कि किस तालिका पर खोज करनी है। मेरे उदाहरण में, मैंने पहले से ही तीन टेबलों को tbFall, tbWinter, और tbSpring नाम दिया है। B4 में सूत्र है:
=VLOOKUP(B3, CHOOSE(MATCH(B2, D2:D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
इस सूत्र में, MATCH यह निर्धारित करने जा रहा है कि हम 2 . चाहते हैंरा हमारी सूची से आइटम। CHOOSE फिर वह 2 लेगा और tbWinter का संदर्भ लौटाएगा। अंत में, हमारा VLOOKUP दी गई तालिका में खोज को पूरा करने में सक्षम होगा, और यह पाएगा कि सर्दियों में केले की कुल बिक्री $6000 थी।
=VLOOKUP(B3, CHOOSE(MATCH(B2, D2:D4, 0), tbFall, tbWinter, tbSpring), 2, 0) =VLOOKUP(B3, CHOOSE(2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP(B3, tbविंटर, 2, 0) =6000
यह तकनीक इस तथ्य से सीमित है कि आपको उन सभी क्षेत्रों के साथ CHOOSE फ़ंक्शन भरना होगा, जिनसे आप मूल्य प्राप्त करना चाहते हैं, लेकिन यह आपको एक अस्थिर सूत्र से बचने का लाभ देता है। आपको कितनी गणनाओं को पूरा करने की आवश्यकता है, इस पर निर्भर करते हुए, यह क्षमता काफी मूल्यवान साबित हो सकती है।
