एक्सेल में ऑफ़सेट फ़ंक्शन - ऑफ़सेटिंग द्वारा एक संदर्भ बनाएं

उदाहरण कार्यपुस्तिका डाउनलोड करें

उदाहरण कार्यपुस्तिका डाउनलोड करें

यह ट्यूटोरियल दर्शाता है कि कैसे उपयोग करना है एक्सेल ऑफ़सेट फ़ंक्शन एक्सेल में एक प्रारंभिक सेल से एक संदर्भ ऑफसेट बनाने के लिए।

ऑफ़सेट फ़ंक्शन अवलोकन

ऑफ़सेट फ़ंक्शन एक परिभाषित सेल संदर्भ के साथ शुरू होता है और एक सेल संदर्भ देता है जो मूल संदर्भ से एक विशिष्ट संख्या में पंक्तियों और स्तंभों को ऑफसेट करता है। संदर्भ एक सेल या कई सेल हो सकते हैं। ऑफसेट आपको दी गई पंक्तियों/स्तंभों की संख्या के संदर्भ का आकार बदलने की भी अनुमति देता है।

(ध्यान दें कि सूत्र इनपुट कैसे दिखाई देते हैं)

IFERROR फ़ंक्शन सिंटैक्स और इनपुट:

1	= ऑफ़सेट (संदर्भ, पंक्तियाँ, कॉलम, ऊँचाई, चौड़ाई)

संदर्भ - प्रारंभिक सेल संदर्भ जिससे आप ऑफसेट करना चाहते हैं।

पंक्तियों - ऑफसेट करने के लिए पंक्तियों की संख्या।

कॉलम - ऑफसेट करने के लिए कॉलम की संख्या।

ऊंचाई - वैकल्पिक: संदर्भ में पंक्तियों की संख्या समायोजित करें।

चौड़ाई - वैकल्पिक: संदर्भ में स्तंभों की संख्या समायोजित करें।

ऑफ़सेट फ़ंक्शन क्या है?

ऑफ़सेट फ़ंक्शन अधिक शक्तिशाली स्प्रेडशीट फ़ंक्शंस में से एक है क्योंकि यह जो भी बनाता है उसमें काफी बहुमुखी हो सकता है। यह उपयोगकर्ता को विभिन्न स्थिति और आकारों में सेल या श्रेणी को परिभाषित करने की क्षमता देता है।

चेतावनी: ऑफ़सेट फ़ंक्शन अस्थिर कार्यों में से एक है। अधिकांश समय जब आप अपनी स्प्रैडशीट में काम कर रहे होते हैं, तो कंप्यूटर केवल एक सूत्र की पुनर्गणना करेगा यदि इनपुट ने अपने मान बदल दिए हों। एक अस्थिर कार्य, हालांकि, पुनर्गणना करता है प्रत्येक जब आप किसी सेल में बदलाव करते हैं। यह सुनिश्चित करने के लिए सावधानी बरती जानी चाहिए कि अस्थिर फ़ंक्शन के अत्यधिक उपयोग या अस्थिर फ़ंक्शन के परिणाम पर निर्भर कई सेल होने के कारण आप बड़े पुनर्गणना समय का कारण नहीं बनते हैं।

मूल पंक्ति उदाहरण

OFFSET फ़ंक्शन के प्रत्येक उपयोग में, आपको एक प्रारंभिक बिंदु, या एंकर देने की आवश्यकता होती है। आइए इसे समझने में सहायता के लिए इस तालिका को देखें:

हम अपने एंकर पॉइंट के रूप में सेल B3 में "बॉब" का उपयोग करेंगे। यदि हम नीचे (चार्ली) के मान को हथियाना चाहते हैं, तो हम कहेंगे कि हम पंक्ति को 1 से स्थानांतरित करना चाहते हैं। हमारा सूत्र इस तरह दिखेगा

1	=ऑफसेट(बी3, 1)

अगर हम ऊपर जाना चाहते हैं, तो यह एक नकारात्मक बदलाव होगा। आप ऐसा सोच सकते हैं क्योंकि पंक्ति संख्या घट रही है, इसलिए हमें घटाना होगा। इस प्रकार, उपरोक्त मान (एडम) प्राप्त करने के लिए, हम लिखेंगे

1	=ऑफसेट(बी2, -1)

मूल स्तंभ उदाहरण

पिछले उदाहरण के विचार को जारी रखते हुए, हम अपनी तालिका में एक और कॉलम जोड़ेंगे।

यदि हम बॉब के लिए शिक्षक को पकड़ना चाहते हैं, तो हम सूत्र का उपयोग कर सकते हैं

1	=ऑफसेट(बी2, 0, 1)

इस उदाहरण में, हमने कहा कि हम शून्य पंक्तियों को ऑफसेट करना चाहते हैं (उर्फ एक ही पंक्ति पर रहें) लेकिन हम 1 कॉलम को ऑफसेट करना चाहते हैं। स्तंभों के लिए, धनात्मक संख्या का अर्थ है दाईं ओर ऑफ़सेट करना, और ऋणात्मक संख्याओं का अर्थ बाईं ओर ऑफ़सेट करना है।

ऑफसेट और मैच

मान लीजिए कि आपके पास डेटा के कई कॉलम हैं, और आप उपयोगकर्ता को यह चुनने की क्षमता देना चाहते हैं कि किस कॉलम से परिणाम प्राप्त करना है। आप इंडेक्स फ़ंक्शन का उपयोग कर सकते हैं, या आप ऑफ़सेट का उपयोग कर सकते हैं। चूंकि MATCH किसी मान की सापेक्ष स्थिति लौटाएगा, इसलिए हमें यह सुनिश्चित करना होगा कि एंकर बिंदु हमारे पहले संभावित मान के बाईं ओर है। निम्नलिखित लेआउट पर विचार करें:

B2 में, हम यह सूत्र लिखेंगे:

1	=ऑफसेट(B2, 0, MATCH(A2, $C$1:$F$1, 0))

MATCH C1:F1 रेंज में "फरवरी" देखने वाला है और इसे 2 . में ढूंढेगा^रा कक्ष। OFFSET तब 1 कॉलम को B2 के दाईं ओर शिफ्ट करेगा और 9 के वांछित मान को पकड़ लेगा। ध्यान दें कि OFFSET को उसी सेल का उपयोग करने में कोई समस्या नहीं है जिसमें सूत्र को एंकर पॉइंट के रूप में शामिल किया गया है।

नोट: इस तकनीक का उपयोग VLOOKUP या HLOOKUP के प्रतिस्थापन के रूप में किया जा सकता है जब आप अपनी लुकअप श्रेणी के बाईं ओर/ऊपर से कोई मान वापस करना चाहते हैं। ऐसा इसलिए है क्योंकि ऑफ़सेट नकारात्मक ऑफ़सेट कर सकता है।

एक सीमा प्राप्त करने के लिए ऑफसेट

आप 4 . का उपयोग कर सकते हैं^वां और 5^वां OFFSET फ़ंक्शन में तर्क केवल एक सेल के बजाय एक श्रेणी वापस करने के लिए। मान लीजिए कि आप इस तालिका में 3 कॉलम जोड़ना चाहते हैं।

1	= औसत (ऑफसेट (ए 1, मैच (एफ 2, ए 2: ए 5,0), 1,1,3))

F2 में, हमने उस छात्र का नाम चुना है जिसके लिए हम उनके औसत टेस्ट स्कोर प्राप्त करना चाहते हैं। ऐसा करने के लिए, हम सूत्र का उपयोग करेंगे

1	= औसत (ऑफसेट (ए 1, मैच (एफ 2, ए 2: ए 5,0), 1,1,3))

MATCH हमारे नाम के लिए कॉलम A के माध्यम से खोज करेगा और सापेक्ष स्थिति लौटाएगा, जो हमारे उदाहरण में 3 है। आइए देखें कि इसका मूल्यांकन कैसे किया जाएगा। सबसे पहले, ऑफ़सेट जाने वाला है नीचे A1 से 3 पंक्तियाँ, और 1 स्तंभ से . तक अधिकार ए1 से यह हमें सेल B3 में रखता है।

1	= औसत (ऑफसेट (ए 1, 3, 1, 1, 3))

अगला, हम सीमा का आकार बदलने जा रहे हैं। नई रेंज में टॉप लेफ्ट सेल के तौर पर B3 होगा। यह 1 पंक्ति ऊँचा और 3 स्तंभ ऊँचा होगा, जो हमें B4:D4 श्रेणी प्रदान करेगा।

1	= औसत (ऑफसेट (ए 1,3, 1, 1, 3))

ध्यान दें कि जब आप वैध रूप से ऑफ़सेट तर्कों में नकारात्मक मान डाल सकते हैं, तो आप आकार देने वाले तर्कों में केवल गैर-ऋणात्मक मानों का उपयोग कर सकते हैं।

अंत में, हमारा AVERAGE फ़ंक्शन देखता है:

1	= औसत (बी 4: डी 4)

इस प्रकार, हमें 86.67 . का हमारा हल मिलता है

गतिशील योग के साथ ऑफसेट

क्योंकि OFFSET का उपयोग संदर्भ खोजने के लिए किया जाता है, सीधे सेल को इंगित करने के बजाय, यह सबसे अधिक सहायक होता है जब आप डेटा के साथ काम कर रहे होते हैं जिसमें पंक्तियों को जोड़ा या हटा दिया जाता है। नीचे दी गई तालिका पर कुल मिलाकर विचार करें

1	= एसयूएम (बी 2: बी 4)

यदि हमने यहां "=SUM(B2:B4)" के मूल SUM सूत्र का उपयोग किया होता और फिर बिल के लिए एक रिकॉर्ड जोड़ने के लिए एक नई पंक्ति सम्मिलित की होती, तो हमारे पास गलत उत्तर होता

इसके बजाय, आइए सोचें कि इसे टोटल के दृष्टिकोण से कैसे हल किया जाए। हम वास्तव में सेल B2 से सेल तक सब कुछ हथियाना चाहते हैं हमारे कुल के ठीक ऊपर. जिस तरह से हम इसे सूत्र में लिख सकते हैं वह है -1 की एक पंक्ति ऑफसेट करना। इस प्रकार, हम इसे सेल B5 में हमारे कुल के लिए सूत्र के रूप में उपयोग करते हैं:

1	= एसयूएम (बी 2: ऑफसेट (बी 5, -1,0))

यह सूत्र वही करता है जिसका हमने अभी वर्णन किया है: B2 से शुरू करें और हमारे कुल सेल के ऊपर 1 सेल पर जाएं। आप देख सकते हैं कि बिल का डेटा जोड़ने के बाद, हमारा कुल कैसे सही तरीके से अपडेट हो जाता है।

अंतिम N आइटम प्राप्त करने के लिए OFFSET

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

हमने पहले ही दिखाया है कि आप कई प्रकार की कोशिकाओं को हथियाने के लिए OFFSET का उपयोग कैसे कर सकते हैं। यह निर्धारित करने के लिए कि हमें कितने कक्षों को स्थानांतरित करने की आवश्यकता है, हम कितने का पता लगाने के लिए COUNT का उपयोग करेंगे नंबर कॉलम बी में हैं। आइए हमारी नमूना तालिका देखें।

1	=SUM(ऑफसेट($B$1,COUNT(B:B)-$E$1+1,0,$E$1,1))

यदि हम B1 से शुरू करते हैं और 4 पंक्तियों (स्तंभ B में संख्याओं की गिनती) को ऑफसेट करते हैं, तो हम अपनी सीमा, B5 के निचले भाग पर समाप्त होते हैं। हालाँकि, चूंकि OFFSET एक नकारात्मक मान के साथ आकार नहीं बदल सकता है, इसलिए हमें कुछ समायोजन करने की आवश्यकता है ताकि हम B3 में समाप्त हो जाएं। इसके लिए सामान्य समीकरण होने जा रहा है

1	COUNT(… ) - एन + 1

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

यहां आप देख सकते हैं कि हमने पिछले N महीनों का योग, औसत और अधिकतम प्राप्त करने के लिए एक सीमा निर्धारित की है। E1 में, हमने 3 का मान दर्ज किया है। E2 में, हमारा सूत्र है

1	=SUM(ऑफसेट($B$1,COUNT(B:B)-$E$1+1,0,$E$1,1))

हाइलाइट किया गया खंड हमारा सामान्य समीकरण है जिस पर हमने अभी चर्चा की है। हमें किसी कॉलम को ऑफसेट करने की आवश्यकता नहीं है। फिर हम 3 सेल लंबा (E1 में मान द्वारा निर्धारित) और 1 कॉलम चौड़ा होने के लिए सीमा का आकार बदलने जा रहे हैं। हमारा SUM तब यह सीमा लेता है और हमें $1,850 का परिणाम देता है। हमने यह भी दिखाया है कि आप केवल बाहरी फ़ंक्शन को SUM से किसी भी स्थिति में स्विच करके इसी श्रेणी के अधिकतम औसत की गणना कर सकते हैं।

ऑफ़सेट डायनेमिक सत्यापन सूचियाँ

पिछले उदाहरण में दिखाई गई तकनीक का उपयोग करके, हम नामांकित श्रेणियां भी बना सकते हैं जिनका उपयोग डेटा सत्यापन या चार्ट में किया जा सकता है। यह तब मददगार हो सकता है जब आप एक स्प्रैडशीट सेटअप करना चाहते हैं लेकिन हमारी सूचियों/डेटा के आकार बदलने की अपेक्षा कर रहे हैं। मान लें कि हमारा स्टोर फल बेचना शुरू कर रहा है, और वर्तमान में हमारे पास 3 विकल्प हैं।

डेटा सत्यापन ड्रॉपडाउन बनाने के लिए जिसे हम कहीं और उपयोग कर सकते हैं, हम नामित श्रेणी MyFruit को इस प्रकार परिभाषित करेंगे

1	=$A$2:OFFSET($A$1, COUNTA($A:$A)-1, 0)

COUNT के बजाय, हम COUNTA का उपयोग कर रहे हैं क्योंकि हम टेक्स्ट मानों के साथ काम कर रहे हैं। हालांकि, इस वजह से, हमारा COUNTA एक ​​उच्च होने जा रहा है क्योंकि यह A1 में हेडर सेल की गणना करने जा रहा है और 4 का मान देगा। यदि हम 4 पंक्तियों से ऑफसेट करते हैं, तो हम सेल A5 में समाप्त हो जाएंगे जो खाली है। इसके लिए समायोजित करने के लिए, हम 1 घटाते हैं।

अब जब हमें अपना नामांकित श्रेणी सेटअप मिल गया है, तो हम स्रोत के साथ सूची प्रकार का उपयोग करके सेल C4 में कुछ डेटा सत्यापन सेट कर सकते हैं:

1

=माईफ्रूट

ध्यान दें कि ड्रॉपडाउन केवल हमारे तीन वर्तमान आइटम दिखाता है। यदि हम अपनी सूची में और आइटम जोड़ते हैं और ड्रॉपडाउन पर वापस जाते हैं, तो सूची बिना किसी सूत्र को बदले सभी नए आइटम दिखाती है।

OFFSET का उपयोग करने में सावधानियां

जैसा कि इस लेख की शुरुआत में बताया गया है, OFFSET एक अस्थिर कार्य है। यदि आप इसे केवल कुछ कक्षों में उपयोग कर रहे हैं, तो आप इसे नोटिस नहीं करेंगे, लेकिन यदि आप इसे सैकड़ों गणनाओं में शामिल करना शुरू करते हैं और आप जल्दी से देखेंगे कि आपका कंप्यूटर हर बार कोई भी परिवर्तन करने पर पुनर्गणना करने में ध्यान देने योग्य समय खर्च कर रहा है। .

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

इसके बजाय, टेबल्स (कार्यालय 2007 में प्रस्तुत) का उपयोग करना उचित होगा जो संरचनात्मक संदर्भों की अनुमति देता है। इससे उपयोगकर्ताओं को एक एकल संदर्भ देने में मदद मिली जो स्वचालित रूप से आकार में समायोजित हो गया क्योंकि नया डेटा जोड़ा या हटा दिया गया था।

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

अतिरिक्त नोट्स

प्रारंभिक संदर्भ से दी गई पंक्तियों और स्तंभों की संख्या को ऑफसेट करके सेल मान (या कक्षों की श्रेणी) को वापस करने के लिए ऑफ़सेट फ़ंक्शन का उपयोग करें। जब केवल एक सेल की तलाश की जाती है, तो OFFSET फ़ार्मुलों को थोड़ा अलग तकनीक का उपयोग करके INDEX फ़ार्मुलों के समान उद्देश्य प्राप्त होता है। ऑफ़सेट फ़ंक्शन की वास्तविक शक्ति किसी अन्य सूत्र में उपयोग की जाने वाली कक्षों की श्रेणी का चयन करने की क्षमता में निहित है।

ऑफ़सेट फ़ंक्शन का उपयोग करते समय, आप प्रारंभिक प्रारंभिक सेल या कक्षों की श्रेणी को परिभाषित करते हैं। फिर आप उस प्रारंभिक सेल से ऑफसेट करने के लिए पंक्तियों और स्तंभों की संख्या इंगित करते हैं। आप सीमा का आकार भी बदल सकते हैं; पंक्तियों या स्तंभों को जोड़ना या घटाना।

एक्सेल में सभी फंक्शन की सूची पर लौटें

Google पत्रक में ऑफ़सेट

ऑफ़सेट फ़ंक्शन Google शीट्स में ठीक उसी तरह काम करता है जैसे एक्सेल में: