अप्रत्यक्ष सूत्र एक्सेल - टेक्स्ट से एक सेल संदर्भ बनाएं

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

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

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

अप्रत्यक्ष कार्य अवलोकन

अप्रत्यक्ष फ़ंक्शन टेक्स्ट स्ट्रिंग से सेल संदर्भ बनाता है।

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

अप्रत्यक्ष कार्य सिंटैक्स और इनपुट:

1	=अप्रत्यक्ष (ref_text,C1)

ref_text - सेल संदर्भ या श्रेणी संदर्भ का प्रतिनिधित्व करने वाली एक स्ट्रिंग। स्ट्रिंग R1C1 या A1 प्रारूप में हो सकती है, या एक नामित श्रेणी हो सकती है।

ए 1 - वैकल्पिक: इंगित करता है कि संदर्भ R1C1 या A1 प्रारूप में है या नहीं। R1C1 के लिए FALSE या A1 के लिए TRUE / छोड़ा गया।

अप्रत्यक्ष कार्य क्या है?

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

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

एक सेल संदर्भ बनाएं

मान लें कि आप A2 से मान प्राप्त करना चाहते हैं, लेकिन आप यह सुनिश्चित करना चाहते हैं कि आपका सूत्र रिहाइश A2 पर नई पंक्तियों को डालने/हटाए जाने की परवाह किए बिना। आप का सूत्र लिख सकते हैं

1	= अप्रत्यक्ष ("ए 2")

ध्यान दें कि हमारे फ़ंक्शन के अंदर तर्क टेक्स्ट स्ट्रिंग "ए 2" है, न कि सेल संदर्भ। साथ ही, चूंकि यह एक टेक्स्ट स्ट्रिंग है, इसलिए $A$2 जैसे पूर्ण संदर्भ को इंगित करने की कोई आवश्यकता नहीं है। पाठ कभी नहीं बदलेगा, और इस प्रकार यह सूत्र हमेशा A2 को इंगित करेगा, चाहे इसे कहीं भी ले जाया जाए।

अप्रत्यक्ष पंक्ति संख्या

आप टेक्स्ट स्ट्रिंग्स और मानों को सेल से एक साथ जोड़ सकते हैं। "ए2" लिखने के बजाय जैसा हमने पहले किया था, हम सेल बी 2 से एक संख्यात्मक मान प्राप्त कर सकते हैं और इसे हमारे सूत्र में उपयोग कर सकते हैं। हम सूत्र लिखेंगे जैसे

1	= अप्रत्यक्ष ("ए" और बी 2)

टेक्स्ट स्ट्रिंग "ए" को सेल बी 2 से मान के साथ जोड़ने के लिए यहां "&" प्रतीक का उपयोग किया जा रहा है। इसलिए, यदि वर्तमान में B2 का मान 10 था, तो हमारा सूत्र इसे इस प्रकार पढ़ेगा

123	= अप्रत्यक्ष ("ए" और 10)= अप्रत्यक्ष ("ए 10")=ए10

अप्रत्यक्ष स्तंभ मान

आप कॉलम संदर्भ में भी जोड़ सकते हैं। इस बार मान लें कि हम जानते हैं कि हम पंक्ति 10 से एक मान प्राप्त करना चाहते हैं, लेकिन हम यह बदलना चाहते हैं कि किस कॉलम से खींचना है। हम सेल B2 में इच्छित कॉलम लेटर डालेंगे। हमारा सूत्र दिख सकता है

1	=अप्रत्यक्ष (बी2 और "10")

यदि B2 का मान “G” है, तो हमारा सूत्र इस प्रकार मूल्यांकन करता है

123	= अप्रत्यक्ष ("जी" और 10)= अप्रत्यक्ष ("G10")=जी10

अप्रत्यक्ष r1c1 शैली

हमारे पिछले उदाहरण में, हमें कॉलम संदर्भ को इंगित करने के लिए एक अक्षर का उपयोग करना था। ऐसा इसलिए है क्योंकि हम A1 स्टाइल रेफरेंसिंग के रूप में जाने जाने वाले का उपयोग कर रहे थे। A1 शैली में, कॉलम एक अक्षर द्वारा दिए जाते हैं, और पंक्तियाँ संख्याओं द्वारा दी जाती हैं। जिस आइटम को हम निरपेक्ष रखना चाहते हैं, उससे पहले "$" का उपयोग करके निरपेक्ष संदर्भ इंगित किए जाते हैं।

r1c1 में, पंक्तियों और स्तंभों दोनों को संख्या का उपयोग करना शुरू किया जाता है। a1 का पूर्ण सन्दर्भ इस प्रकार लिखा जाएगा

1

=R1C1

आप इसे "पंक्ति 1, कॉलम 1" के रूप में पढ़ सकते हैं। कोष्ठक का उपयोग करके सापेक्ष संदर्भ दिए गए हैं, लेकिन संख्या स्थिति को इंगित करती है सूत्र के साथ सेल के सापेक्ष. इसलिए, यदि हम सेल A10 में एक सूत्र लिख रहे थे और हमें A1 को संदर्भित करने की आवश्यकता है, तो हम सूत्र लिखेंगे

1

=आर[-9]सी

आप इसे "सेल 9 पंक्तियों के रूप में पढ़ सकते हैं, लेकिन एक ही कॉलम में।

इसका कारण यह सहायक हो सकता है कि INDIRECT r1c1 संकेतन के उपयोग का समर्थन कर सकता है। पिछले उदाहरण पर विचार करें जहां हम पंक्ति 10 से एक मान प्राप्त कर रहे थे लेकिन कॉलम को बदलने में सक्षम होना चाहते थे। एक पत्र देने के बजाय, मान लें कि हम सेल बी 2 में एक नंबर डालते हैं। हमारा सूत्र तब दिख सकता है

1	= अप्रत्यक्ष ("R10C" और B2, FALSE)

हम 2 . को छोड़ रहे हैं^रा अब तक तर्क। यदि यह तर्क छोड़ दिया जाता है या सही है, तो फ़ंक्शन A1 शैली का उपयोग करके मूल्यांकन करेगा। चूंकि यह गलत है, इसलिए इसका मूल्यांकन r1c1 में किया जाएगा। मान लेते हैं कि B2 का मान 5 है। हमारा सूत्र इस प्रकार मूल्यांकन करेगा

12	= अप्रत्यक्ष ("R10C5", FALSE)=$ई$10

A1 बनाम r1c1 . के साथ अप्रत्यक्ष अंतर

याद रखें कि हमने पहले दिखाया था कि चूंकि इस सूत्र की सामग्री एक टेक्स्ट स्ट्रिंग थी, यह कभी नहीं बदली?

1	= अप्रत्यक्ष ("ए 2")

यह सूत्र हमेशा कक्ष A2 को देखेगा, चाहे आप सूत्र को कहीं भी ले जाएं। r1c1 में, चूंकि आप कोष्ठकों का उपयोग करके सापेक्ष स्थिति इंगित कर सकते हैं, यह नियम सुसंगत नहीं रहता है। यदि आप इस सूत्र को कक्ष B2 . में रखते हैं

1	= अप्रत्यक्ष ("आरसी [-1]")

यह सेल A2 को देखेगा (चूंकि कॉलम A, कॉलम B के बाईं ओर एक है)। यदि आप इस सूत्र को सेल B3 में कॉपी करते हैं, तो अंदर का टेक्स्ट वही रहेगा, लेकिन INDIRECT अब सेल A3 को देखेगा।

शीट नाम के साथ अप्रत्यक्ष

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

मान लें कि हमारे पास यह सेटअप था, जहां हम अपनी शीट का नाम, पंक्ति और कॉलम बता रहे हैं।

इन सभी को एक संदर्भ में संयोजित करने का हमारा सूत्र इस तरह दिखेगा:

1	= अप्रत्यक्ष ("'" और ए 2 और "'!" और बी 2 और सी 2)

तब हमारे सूत्र का मूल्यांकन इस प्रकार किया जाएगा:

123	= अप्रत्यक्ष ("'" और "शीट 2" और "'!" और "बी" और "5")=अप्रत्यक्ष ("''पत्रक2'! बी5")='शीट2'!बी5

तकनीकी रूप से, चूंकि "शीट 2" शब्द में कोई स्थान नहीं है, इसलिए हम नहीं करते हैं जरुरत एकल उद्धरण चिह्न। कुछ इस तरह लिखना पूरी तरह से मान्य है

1

=शीट२!ए२

हालाँकि, जब आपको उनकी आवश्यकता न हो तो उद्धरण चिह्न लगाने में कोई हर्ज नहीं है। उन्हें शामिल करना सबसे अच्छा अभ्यास है ताकि आपका सूत्र उस उदाहरण को संभाल सके जहां उनकी आवश्यकता हो सकती है।

किसी अन्य कार्यपुस्तिका के लिए अप्रत्यक्ष

हम यह भी उल्लेख करेंगे कि INDIRECT किसी भिन्न कार्यपुस्तिका का संदर्भ बना सकता है। सीमा यह है कि INDIRECT बंद कार्यपुस्तिका से मान प्राप्त नहीं करेगा, इसलिए इस विशेष उपयोग में सीमित व्यावहारिकता है। यदि INDIRECT जिस कार्यपुस्तिका को इंगित कर रहा है, वह खुली नहीं है, तो फ़ंक्शन "#REF!" त्रुटि।

कार्यपुस्तिका का नाम लिखते समय सिंटैक्स यह है कि इसे वर्ग कोष्ठक में होना चाहिए। आइए इस सेटअप का उपयोग करें और सेल C7 से एक मान प्राप्त करने का प्रयास करें।

हमारा सूत्र होगा

1	= अप्रत्यक्ष ("'[" और A2 और "]" और B2 और "'! C7")

फिर से, एकल उद्धरण चिह्नों, कोष्ठकों और विस्मयादिबोधक चिह्नों के स्थान पर ध्यान दें। तब हमारे सूत्र का मूल्यांकन इस प्रकार किया जाएगा:

123	= अप्रत्यक्ष ("'[" और "नमूना। xlsx" और "]" और "सारांश" और "'! C7")= अप्रत्यक्ष ("'[नमूना। xslx] सारांश'! C7")='[नमूना.xlsx]सारांश'!C7

गतिशील रेंज बनाने के लिए अप्रत्यक्ष

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

सेल B2 में, हमने सूत्र रखा है

1	= काउंट ए (ए: ए)

कंप्यूटर के लिए गणना करना COUNTA फ़ंक्शन बहुत आसान है, क्योंकि यह केवल यह जांचता है कि col A में कितने कक्षों का कुछ मान है, जैसा कि कोई तर्क जांच या गणितीय संचालन करने के विपरीत है।

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

1	= योग (अप्रत्यक्ष ("ए 2: ए" और बी 2))

हमारा INDIRECT सेल B2 से नंबर 5 को हथियाने जा रहा है और A2: A5 रेंज के लिए एक संदर्भ तैयार करेगा। SUM तब इस श्रेणी का उपयोग इसकी गणना के लिए कर सकता है। यदि हम सेल A6 में एक और मान जोड़ते हैं, तो B2 में नंबर अपडेट हो जाएगा, और हमारा SUM फॉर्मूला इस नए मान को शामिल करने के लिए ऑटो-अपडेट भी करेगा।

चेतावनी: Office 2007 में तालिकाओं की शुरुआत के साथ, यह आपके डेटा को तालिका में संग्रहीत करने और इस उदाहरण में उपयोग किए गए सूत्र को बनाने के बजाय एक संरचनात्मक संदर्भ का उपयोग करने के लिए अप्रत्यक्ष की अस्थिर प्रकृति के कारण अधिक कुशल है। हालाँकि, वे ऐसे उदाहरण हो सकते हैं जहाँ आपको वस्तुओं की एक सूची बनाने की आवश्यकता होती है और आप किसी तालिका का उपयोग नहीं कर सकते।

अप्रत्यक्ष के साथ गतिशील चार्टिंग

आइए पिछला उदाहरण लें और एक और कदम उठाएं। हमें मानों का योग देने के लिए एक सूत्र लिखने के बजाय, हम एक नामांकित श्रेणी बनाएंगे। हम इस श्रेणी को "माईडाटा" कह सकते हैं और इसे संदर्भित कर सकते हैं

1	= अप्रत्यक्ष ("A2:A" और COUNTA($A:$A))

ध्यान दें कि चूंकि हम इसे नामांकित श्रेणी में रख रहे हैं, इसलिए हमने संदर्भ को B2 में बदल दिया है और इसके बजाय COUNTA फ़ंक्शन को सीधे वहां रखा है।

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

1	=शीट1!माईडाटा

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

अप्रत्यक्ष के साथ गतिशील डेटा सत्यापन

उपयोगकर्ताओं से इनपुट एकत्र करते समय, कभी-कभी पिछली पसंद पर निर्भर से चुनने के लिए एक विकल्प बनाने की आवश्यकता होती है। इस लेआउट पर विचार करें, जहां हमारा पहला कॉलम उपयोगकर्ता को फल, सब्जियां और मीट के बीच चयन करने की अनुमति देता है।

2 . में^रा कॉलम, हम हर संभव विकल्प को दिखाने वाली एक बड़ी सूची नहीं चाहते हैं, क्योंकि हमने पहले ही चीजों को थोड़ा कम कर दिया है। इसलिए, हमने 3 अन्य सूचियां बनाई हैं जो इस तरह दिखती हैं:

इसके बाद, हम इनमें से प्रत्येक को असाइन करेंगे इन एक नामित श्रेणी के लिए सूचियाँ। यानी, सभी फल "फल" नामक श्रेणी में होंगे, और सब्जियां "सब्जियों" आदि में होंगी।

अपनी तालिका में वापस, हम 2 . में डेटा सत्यापन सेट करने के लिए तैयार हैं^रा स्तंभ। हम एक इनपुट के साथ एक सूची प्रकार सत्यापन बनाएंगे:

1	= अप्रत्यक्ष (ए 2)

INDIRECT col A में बने चॉइस को पढ़ेगा और एक कैटेगरी का नाम देखेगा। हमने इन नामों के साथ श्रेणियों को परिभाषित किया है, इसलिए अप्रत्यक्ष उस नाम को लेगा और वांछित सीमा का संदर्भ तैयार करेगा।

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

टेक्स्ट से सेल संदर्भ बनाने के लिए अप्रत्यक्ष फ़ंक्शन का उपयोग करें।

पहले टेक्स्ट की स्ट्रिंग बनाएं जो सेल संदर्भ का प्रतिनिधित्व करती है। स्ट्रिंग या तो सामान्य A1-शैली स्तंभ अक्षर और पंक्ति संख्या (M37) या R1C1-शैली (R37C13) में होनी चाहिए। आप सीधे रेफरेंस टाइप कर सकते हैं, लेकिन आमतौर पर आप उन कोशिकाओं को संदर्भित करेंगे जो पंक्तियों और स्तंभों को परिभाषित करती हैं। अंत में, आप जो सेल संदर्भ प्रारूप चुनते हैं उसे दर्ज करें। A1-शैली संदर्भ के लिए TRUE या छोड़ा गया या R1C1-शैली के लिए FALSE।

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

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

अप्रत्यक्ष Google पत्रक में

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