एक्सेल में ऑफ़सेट फ़ंक्शन का उपयोग [ऑफ़सेट

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

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

परिचय

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

नीचे दिया गया आंकड़ा दिखाता है कि सेल (बाएं भाग) या श्रेणी (दाएं भाग) के संदर्भ को वापस करने के लिए ऑफ़सेट फ़ंक्शन का उपयोग कैसे करें।

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

हरे रंग में हाइलाइट किया गया सेल एक टारगेट सेल है, जबकि पीले रंग में हाइलाइट किए गए सेल में टारगेट रेंज होती है।

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

एक्सेल में ऑफ़सेट फ़ंक्शन का उपयोग [ऑफ़सेट - मैच कॉम्बो, डायनेमिक रेंज]

चित्र 1

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

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

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

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

केस 1:OFFSET और MATCH फंक्शन को मिलाकर राइट-टू-लेफ्ट लुकअप

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

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

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

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

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

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

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

फ़ॉर्मूला “=MATCH (“USA”, B3:B8, 0)” वापस आएगा 1 चूंकि यूएसए इस श्रेणी में पहला आइटम है (सेल B10 और C10 देखें)।

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

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

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

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

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

यह प्रश्न का उत्तर है - ऑफ़सेट फ़ंक्शन को मिलान फ़ंक्शन के साथ कैसे संयोजित करें - मैच फ़ंक्शन को ऑफ़सेट फ़ंक्शन के दूसरे या तीसरे तर्क के रूप में कार्य करने के लिए लागू किया जा सकता है (सेल सी13 देखें)।

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

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

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

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

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

चित्र 2.1

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

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

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

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

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

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

चित्र 2.2

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

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

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

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

जब तक हम इस नंबर को स्वचालित रूप से वापस कर सकते हैं, हम स्वचालित रूप से कॉलम में अंतिम संख्या का पता लगाने में सक्षम हो सकते हैं।

9 केवल उन कक्षों की संख्या है जिनमें स्तंभ C में संख्याएँ हैं।

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

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

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

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

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

यह जो संख्या देता है वह 87000 है जो कॉलम सी में बिल्कुल अंतिम संख्या है।

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

सूत्र होगा “=SUM (ऑफसेट (C2, 1, 0, 9 .) , 1))" अगर हम OFFSET के साथ SUM का उपयोग करते हैं।

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

इसलिए, हम सूत्र को नए तरीके से लिख सकते हैं जैसे "=SUM (ऑफसेट (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 देखें)।

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

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

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

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

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

चित्र 3

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

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

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

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

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

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

चित्र 4.1

एक गतिशील श्रेणी बनाने के लिए, सूत्र . पर क्लिक करें टैब करें और फिर नाम प्रबंधक . चुनें या नाम परिभाषित करें ।

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

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

चित्र 4.2

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

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

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

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

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

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

चित्र 4.3

संकेत में डेटा चुनें स्रोत, श्रृंखला1 . चुनें और फिर संपादित करें।

चित्र 4.4

और फिर “=चित्र4!इकाइयाँ” टाइप करें जैसा कि चित्र 4.5 में दिखाया गया है।

चित्र 4.5

अंत में, आइए सेल C13 में एक कोशिश करें और 11 टाइप करें। आप देख सकते हैं कि चार्ट बदल गया है और मान 11 शामिल कर लिया गया है।

नया डेटा जोड़े जाने पर चार्ट अपने आप बदल जाएगा।

चित्र 4.6

कार्यशील फ़ाइलें डाउनलोड करें

नीचे दिए गए लिंक से कार्यशील फ़ाइलें डाउनलोड करें।

एक्सेल-ऑफ़सेट-फ़ंक्शन.rar