एक्सेल के OFFSET फ़ंक्शन में महारत: डायनामिक रेंज और मैच कॉम्बो के लिए व्यावहारिक उदाहरण

आज मैं आपको 3 वास्तविक जीवन के उदाहरणों के साथ एक्सेल के OFFSET फ़ंक्शन से परिचित कराना चाहूंगा।

सबसे पहले, मैं सूत्र सिंटैक्स का वर्णन करूंगा और फिर मैं बात करूंगा कि वास्तविक जीवन में समस्याओं को हल करने के लिए OFFSET फ़ंक्शन का उपयोग कैसे किया जा सकता है।

परिचय

OFFSET फ़ंक्शन एक सेल (इसे लक्ष्य सेल कहें) या रेंज (लक्ष्य रेंज) का संदर्भ लौटा सकता है जो किसी अन्य सेल (संदर्भ सेल) या रेंज (संदर्भ रेंज) से निर्दिष्ट संख्या में पंक्तियों और कॉलम दूर है।

नीचे दिया गया चित्र दर्शाता है कि सेल (बायां भाग) या रेंज (दायां भाग) के संदर्भ को लौटाने के लिए OFFSET फ़ंक्शन का उपयोग कैसे करें।

यह आपको एक सहज प्रभाव देगा कि लक्ष्य सेल क्या है और संदर्भ सेल क्या है।

हरे रंग में हाइलाइट किया गया सेल एक लक्ष्य सेल है जबकि पीले रंग में हाइलाइट किए गए सेल एक लक्ष्य रेंज से मिलकर बने हैं।

नीले रंग में हाइलाइट किए गए सेल संदर्भ सेल हैं।

एक्सेल के OFFSET फ़ंक्शन में महारत: डायनामिक रेंज और मैच कॉम्बो के लिए व्यावहारिक उदाहरण

चित्र 1

एक्सेल में OFFSET का क्या अर्थ है (सिंटैक्स)?

यहां OFFSET फ़ंक्शन का सिंटैक्स है: OFFSET (संदर्भ, पंक्तियाँ, कॉलम, [ऊंचाई], [चौड़ाई])

संदर्भ	आवश्यक। संदर्भ वह सेल या सेल की रेंज है जिससे ऑफसेट शुरू होता है। कृपया ध्यान दें कि यदि आप सेल की एक रेंज निर्दिष्ट करते हैं तो सेल एक दूसरे से सटे होने चाहिए।
पंक्तियाँ	आवश्यक. संदर्भ सेल या संदर्भ रेंज के ऊपरी-बाएं सेल से ऊपर या नीचे पंक्तियों की संख्या। पंक्तियाँ या तो सकारात्मक या नकारात्मक हो सकती हैं। चित्र 1 के बाएं भाग को देखें, यदि मैं फ़ंक्शन को OFFSET (C3, -1, -1) के रूप में बदलता हूं तो लक्ष्य सेल B2 होगा। B2, C3 से एक पंक्ति ऊपर है।
कॉलम	आवश्यक। संदर्भ सेल या संदर्भ रेंज के ऊपरी-बाएं सेल के बाएं या दाएं कॉलम की संख्या। पंक्तियाँ तर्क के साथ, कॉलम के मान भी सकारात्मक और नकारात्मक दोनों हो सकते हैं। यदि हम B4 को संदर्भ सेल और C3 को लक्ष्य सेल के रूप में सेट करते हैं तो OFFSET फ़ंक्शन कैसे लिख सकते हैं? उत्तर है OFFSET (B4, -1, 1)। यहां आप देख सकते हैं कि कॉलम सकारात्मक है और C3, B4 के दाएं एक कॉलम है।
ऊंचाई	वैकल्पिक। केवल ऊंचाई तर्क का उपयोग करें यदि लक्ष्य एक रेंज है। यह बताता है कि लक्ष्य रेंज में कितनी पंक्तियां शामिल हैं। ऊंचाई एक सकारात्मक संख्या होनी चाहिए। आप चित्र 1 के दाएं भाग से देख सकते हैं कि लक्ष्य रेंज में दो पंक्तियां हैं। इसलिए, हम उस मामले में ऊंचाई को 2 पर सेट करते हैं।
चौड़ाई	वैकल्पिक। केवल चौड़ाई तर्क का उपयोग करें यदि लक्ष्य एक रेंज है (चित्र 1 का दायां भाग देखें)। यह इंगित करता है कि लक्ष्य रेंज में कितने कॉलम हैं। चौड़ाई एक सकारात्मक संख्या होनी चाहिए।

अब, मैं आपको दिखाता हूं कि वास्तविक जीवन में समस्याओं को हल करने के लिए OFFSET फ़ंक्शन का उपयोग कैसे करें।

केस 1: OFFSET और MATCH फ़ंक्शन को मिलाकर दाएं-से-बाएं लुकअप

यह सर्वविदित है कि आप VLOOKUP फ़ंक्शन के साथ केवल बाएं-से-दाएं लुकअप कर सकते हैं।

खोजा जाने वाला मान आपकी तालिका सरणी के पहले कॉलम में रखा जाना चाहिए।

यदि आप एक नया लुकअप मान जोड़ना चाहते हैं या किसी अन्य कॉलम को लुकअप मान के रूप में उपयोग करना चाहते हैं तो आपको अपनी पूरी तालिका रेंज को एक कॉलम दाएं शिफ्ट करना होगा या अपनी डेटा संरचना बदलनी होगी।

लेकिन OFFSET को MATCH फ़ंक्शन के साथ मिलाकर, VLOOKUP फ़ंक्शन की सीमा को हटाया जा सकता है।

MATCH फ़ंक्शन क्या है और हम OFFSET फ़ंक्शन को MATCH फ़ंक्शन के साथ कैसे मिला सकते हैं ताकि लुकअप किया जा सके?

खैर, MATCH फ़ंक्शन सेल की एक रेंज में एक निर्दिष्ट आइटम को खोजता है और फिर उस आइटम की रेंज में सापेक्ष स्थिति लौटाता है।

आइए चित्र 2.1 से रेंज B3:B8 को एक उदाहरण के रूप में लें (जो विभिन्न वर्षों में विभिन्न देशों के राजस्व को दर्शाता है)।

सूत्र "=MATCH ("USA", B3:B8, 0)" 1 लौटाएगा क्योंकि USA रेंज में पहला आइटम है (सेल B10 और C10 देखें)।

एक अन्य रेंज C2:F2 के लिए, सूत्र "=MATCH (2015, C2:F2, 0)" 3 लौटाता है क्योंकि 2015 रेंज में तीसरा आइटम है (सेल B11 और C11 देखें)।

OFFSET फ़ंक्शन पर वापस जा रहे हैं।

यदि हम सेल B2 को संदर्भ सेल और सेल E3 को लक्ष्य सेल के रूप में सेट करते हैं, तो हम OFFSET सूत्र कैसे लिख सकते हैं?

E3, B2 से 1 पंक्ति नीचे और 3 कॉलम दाएं है।

इसलिए, सूत्र को "=OFFSET(B2, 1, 3)" के रूप में लिखा जा सकता है। लाल रंग में संख्याओं को ध्यान से देखें, क्या आप पा सकते हैं कि वे मेल खाते हैं?

यह इस प्रश्न का उत्तर है - OFFSET फ़ंक्शन को MATCH फ़ंक्शन के साथ कैसे मिलाएं - MATCH फ़ंक्शन को OFFSET फ़ंक्शन के दूसरे या तीसरे तर्क के रूप में सेवा करने के लिए लागू किया जा सकता है (सेल C13 देखें)।

सेल C14 दर्शाता है कि उसी डेटा को पुनः प्राप्त करने के लिए VLOOKUP फ़ंक्शन का उपयोग कैसे करें।

VLOOKUP फ़ंक्शन लिखने से पहले हमें पता होना चाहिए कि 2015 में राजस्व तालिका सरणी B2:F8 के 4^वें कॉलम में दर्ज है।

इसका मतलब है कि VLOOKUP फ़ंक्शन का उपयोग करते समय हमें डेटा संरचना के बारे में बहुत अच्छी तरह से पता होना चाहिए।

यह VLOOKUP के लिए एक और सीमा है। हालांकि, MATCH फ़ंक्शन को OFFSET फ़ंक्शन के तर्क के रूप में उपयोग करके, हमें कॉलम इंडेक्स जानने की आवश्यकता नहीं है।

यह बहुत उपयोगी है यदि बहुत सारे कॉलम हैं।

चित्र 2.1

अब आइए आगे बढ़ें और एक अधिक जटिल उदाहरण देखें।

मान लीजिए हमारे पास एक तालिका है जिसमें विभिन्न कंपनियों के लिए कंपनी नाम, संपर्क नाम और ईमेल पता है।

और हम किसी ज्ञात संपर्क नाम से कंपनी नाम पुनः प्राप्त करना चाहते हैं या किसी ज्ञात ईमेल पते से संपर्क नाम प्राप्त करना चाहते हैं। हम क्या कर सकते हैं？

चित्र 2.2 देखें, रेंज B5:E8 में कंपनी की जानकारी शामिल है। सेल C2 और सेल B3 में इनपुट डालकर, लाल वर्ग में सूत्र की मदद से, मैं कंपनी नाम पुनः प्राप्त कर सकता हूं यदि मुझे संपर्क नाम पता है।

रेंज D2:E4 दिखाता है कि ज्ञात ईमेल पते के साथ संपर्क नाम कैसे प्राप्त करें।

संक्षेप में, ये दो उदाहरण दर्शाते हैं कि हम दाएं-से-बाएं लुकअप कर सकते हैं और खोज मान को सबसे दाएं कॉलम में रखने की आवश्यकता नहीं है। तालिका सरणी में कोई भी कॉलम खोज मान रख सकता है।

चित्र 2.2

केस 2: OFFSET और COUNT फ़ंक्शन को मिलाकर गणना स्वचालित करें

कैसे एक नई संख्या को कॉलम में जोड़ने पर गणना को स्वचालित किया जाए, इस पर चर्चा करने से पहले, आइए शुरू करें कि कैसे एक कॉलम में अंतिम संख्या को स्वचालित रूप से लौटाया जाए।

नीचे दिए गए चित्र को देखें जो मानव संसाधन से प्रविष्टियों को दर्शाता है। मान लीजिए हम कॉलम B में अंतिम संख्या प्राप्त करना चाहते हैं, यदि हम OFFSET फ़ंक्शन लागू करते हैं तो सूत्र "=OFFSET (C2, 9, 0)" होगा।

सूत्र से, हम जान सकते हैं कि 9 मुख्य संख्या है।

जब तक हम इस संख्या को स्वचालित रूप से लौटा सकते हैं, हम एक कॉलम में अंतिम संख्या को स्वचालित रूप से ढूंढ सकते हैं।

9 कॉलम C में संख्या वाले सेल की संख्या है।

यदि आप COUNT फ़ंक्शन से परिचित हैं, तो आप जानेंगे कि COUNT फ़ंक्शन एक रेंज में संख्या वाले सेल की संख्या गिन सकता है।

उदाहरण के लिए, सूत्र "=COUNT (C3:C11)" सेल C3 से C11 तक संख्या वाले सेल की संख्या गिनेगा।

हमारे मामले में, हम पूरे कॉलम में कितनी संख्याएं हैं यह जानना चाहेंगे, इसलिए C:C जैसे संदर्भ का उपयोग किया जाना चाहिए जिसमें कॉलम C की सभी पंक्तियां शामिल हों।

कृपया सेल G4 और H4 को देखें, "=COUNT(C:C)" द्वारा लौटाई गई संख्या ठीक 9 के बराबर है।

इस प्रकार, उपरोक्त OFFSET फ़ंक्शन में 9 को COUNT(C:C) से बदलकर, हम एक नया सूत्र "=OFFSET (C2, COUNT(C:C), 0)" प्राप्त कर सकते हैं (सेल H5 में)।

यह जो संख्या लौटाता है वह 87000 है जो कॉलम C में अंतिम संख्या है।

अब स्वचालित गणना पर चलते हैं। मान लीजिए हम कॉलम C में सभी संख्याओं का कुल चाहते हैं।

यदि हम SUM का उपयोग OFFSET के साथ करते हैं तो सूत्र "=SUM (OFFSET (C2, 1, 0, 9, 1))" होगा।

9 रेंज C3:C11 में पंक्तियों की कुल संख्या है और कॉलम C में संख्या वाले सेल की कुल संख्या भी है।

इसलिए, हम सूत्र को नए तरीके से लिख सकते हैं जैसे "=SUM (OFFSET (C2,1, 0, COUNT (C:C), 1))"।

सेल G10 और H10 को देखें, इन 9 कर्मचारियों के वेतन की कुल संख्या $521,700 है।

अब यदि आप सेल C12 में $34,000 जैसी संख्या डालते हैं, तो सेल G5 और G10 में संख्या क्रमशः $34,000 और $555,700 में बदल जाएगी।

इसे मैं स्वचालन कहता हूं क्योंकि आपको सेल G5 या G10 में सूत्र अपडेट नहीं करने पड़ते।

जब आप COUNT फ़ंक्शन का उपयोग करते हैं तो आपको सावधान रहना होगा क्योंकि COUNT फ़ंक्शन केवल संख्या वाले सेल की संख्या लौटाता है।

उदाहरण के लिए, "=COUNT (B: B)" 9 के बजाय 0 लौटाता है क्योंकि कॉलम B में कोई सेल नहीं है जिसमें संख्या हो (सेल G3 और H3 देखें)।

कॉलम D में संख्या वाले 10 सेल शामिल हैं और "COUNT (D: D)" द्वारा लौटाई गई संख्या भी 10 है।

लेकिन अगर हम कॉलम D में अंतिम संख्या को पुनः प्राप्त करना चाहते हैं जैसा हमने कॉलम C के लिए किया था, तो हमें संख्या 0 मिलेगी (सेल G8 और H8 देखें)।

जाहिर है, 0 वह नहीं है जो हम चाहते हैं। क्या गलत है? सेल D13, सेल D2 से 11 पंक्तियों दूर है बजाय 10 पंक्तियों के।

यह सूत्र "=OFFSET (D2, COUNT (D: D) + 1, 0)" द्वारा भी प्रदर्शित किया जा सकता है (सेल G7 में)।

संक्षेप में, यदि हम गणना के स्वचालन को सक्षम करने के लिए COUNT फ़ंक्शन का उपयोग OFFSET फ़ंक्शन के साथ करना चाहते हैं तो संख्याओं को एक दूसरे से सटा हुआ होना चाहिए।

चित्र 3

केस 3: डायनामिक रेंज बनाने के लिए OFFSET फ़ंक्शन का उपयोग करें

मान लीजिए हम किसी कंपनी की मासिक यूनिट बिक्री का चार्ट बनाना चाहते हैं और चित्र 4.1 वर्तमान डेटा और वर्तमान डेटा पर आधारित एक चार्ट दिखाता है।

हर महीने, सबसे हाल के महीने की यूनिट बिक्री को कॉलम C में अंतिम संख्या के नीचे जोड़ा जाएगा।

क्या चार्ट को स्वचालित रूप से अपडेट करने का कोई आसान तरीका है?

चार्ट को अपडेट करने की कुंजी यूनिट बेची गई कॉलम के लिए डायनामिक रेंज नाम बनाने के लिए OFFSET फ़ंक्शन का उपयोग करना है।

यूनिट बिक्री के लिए डायनामिक रेंज नए डेटा दर्ज होने पर स्वचालित रूप से सभी बिक्री डेटा को शामिल करेगा।

चित्र 4.1

डायनामिक रेंज बनाने के लिए, फॉर्मूला टैब पर क्लिक करें और फिर नाम प्रबंधक या नाम परिभाषित करें चुनें।

यदि आप नाम परिभाषित करें पर क्लिक करते हैं तो नीचे नया नाम डायलॉग बॉक्स दिखाई देगा।

यदि आप नाम प्रबंधक चुनते हैं, तो आपको नीचे नया नाम डायलॉग बॉक्स दिखाने के लिए नया पर क्लिक करना होगा।

चित्र 4.2

"नाम:" इनपुट बॉक्स में, डायनामिक रेंज नाम भरा जाना चाहिए। और "संदर्भित करता है:" इनपुट बॉक्स में, हमें OFFSET सूत्र "=OFFSET (Figure4!$C$2, 1, 0, COUNT (!$C: $C), 1)" टाइप करना होगा जो कॉलम C में टाइप किए गए यूनिट बेचे गए मूल्यों के आधार पर मूल्यों की एक डायनामिक रेंज उत्पन्न करेगा।

डिफ़ॉल्ट रूप से, एक नाम पूरी वर्कबुक पर लागू होगा और वर्कबुक के भीतर अद्वितीय होना चाहिए।

हालांकि, हम दायरे को एक विशेष शीट तक सीमित करना चाहते हैं।

इसलिए, हम "दायरा:" इनपुट बॉक्स में Figure4 चुनते हैं। ओके पर क्लिक करने के बाद, डायनामिक रेंज बनाई जाती है।

यह नए डेटा दर्ज होने पर स्वचालित रूप से सभी बिक्री डेटा को शामिल करेगा।

अब चार्ट में किसी भी बिंदु पर राइट-क्लिक करें और फिर "डेटा चुनें" चुनें।