Excel INDIRECT(): अपनी फ़ॉर्मूलों में डायनेमिक रेफ़रेंस कैसे बनाएँ

Excel का INDIRECT() फ़ंक्शन सबसे शक्तिशाली, फिर भी अक्सर गलत समझे जाने वाले टूल्स में से एक है। वे फ़ंक्शंस जो सीधे गणनाएँ करते हैं उनसे अलग, INDIRECT() टेक्स्ट स्ट्रिंग्स को लाइव सेल रेफ़रेंस में बदलता है। इससे आप ऐसे डायनेमिक और लचीले फ़ॉर्मूले बना सकते हैं जो बदलते इनपुट के अनुसार ढल जाते हैं। चाहे आप एक समरी डैशबोर्ड बना रहे हों, यूज़र चयन के आधार पर अलग-अलग शीट्स को रेफ़रेंस कर रहे हों, या सेल रेफ़रेंस को डायनेमिक रूप से बदल रहे हों—INDIRECT() खेल बदल सकता है।

इस गाइड में, मैं आपको दिखाऊँगा कि सेल रेफ़रेंसिंग के लिए INDIRECT() का उपयोग कैसे करें और आम समस्याओं का समाधान कैसे करें—सहित कब इस फ़ंक्शन का उपयोग नहीं करना चाहिए। यदि आप कुछ बुनियादी बातों से परिचित नहीं हैं, तो मैं सुझाव दूँगा कि आप हमारा Introduction to Excel कोर्स लेकर मजबूत आधार बनाएं। 

INDIRECT() फ़ंक्शन क्या करता है

Excel में INDIRECT() फ़ंक्शन किसी टेक्स्ट स्ट्रिंग के रूप में दिए गए रेफ़रेंस के आधार पर किसी सेल या रेंज का मान प्राप्त करता है। किसी फ़ॉर्मूले में "A1" जैसा सेल पता हार्डकोड करने के बजाय, INDIRECT() आपको टेक्स्ट के जरिए उस पते को डायनेमिक रूप से परिभाषित करने देता है, जिसे Excel लाइव रेफ़रेंस के रूप में समझता है।

INDIRECT() फ़ंक्शन का सिंटैक्स नीचे दिया गया है:

INDIRECT(ref_text, [a1])संचालित द्वारा

जहाँ:

• ref_text (आवश्यक): एक टेक्स्ट स्ट्रिंग जो किसी वैध सेल या रेंज रेफ़रेंस का प्रतिनिधित्व करती है। यह "A1" जैसे किसी सेल, किसी नेम्ड रेंज, या किसी दूसरी शीट पर "Sheet2!B5" जैसे रेफ़रेंस को दर्शा सकती है।

• [a1]: यह एक वैकल्पिक आर्ग्युमेंट है जो ref_text में रेफ़रेंस के प्रकार को निर्दिष्ट करता है। यदि a1 TRUE है या छोड़ा गया है, तो ref_text को A1-स्टाइल रेफ़रेंस माना जाता है। यदि a1 FALSE है, तो ref_text को R1C1-स्टाइल रेफ़रेंस माना जाता है।

INDIRECT() फ़ंक्शन का उपयोग कैसे करें

INDIRECT() फ़ंक्शन टेक्स्ट को डायनेमिक रूप से सेल रेफ़रेंस में कैसे बदलता है, इसका निम्न उदाहरण दिखाता है।

मान लीजिए सेल C2 में टेक्स्ट "A2" है और सेल A2 में मान 100 है। नीचे दिया गया फ़ंक्शन भी 100 लौटाएगा क्योंकि Excel C2 की सामग्री को पढ़ता है और INDIRECT() इसे एक रेफ़रेंस की तरह ट्रीट करता है।

=INDIRECT(C2)संचालित द्वारा

Excel में किसी अन्य सेल के आधार पर रेफ़रेंस करने के लिए INDIRECT() का उपयोग। चित्र: लेखक।

यदि सेल A5 में संख्या 5 है, तो आप नीचे दिए गए फ़ॉर्मूले से एक डायनेमिक रेफ़रेंस बना सकते हैं:

=INDIRECT("B"&A5)संचालित द्वारा

यह फ़ॉर्मूला "B" को A5 के मान (6) के साथ जोड़ता है और "B6" बनाता है। इसके बाद INDIRECT() सेल B6 का मान लौटाता है, जो "Product A" है।

Excel में डायनेमिक रेफ़रेंसिंग के लिए & के साथ INDIRECT() का उपयोग। चित्र: लेखक।

Excel में INDIRECT() के डायनेमिक उपयोग

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

अन्य वर्कशीट्स को रेफ़रेंस करें

आप INDIRECT() का उपयोग किसी सेल के इनपुट के आधार पर अलग-अलग शीट्स से डेटा को डायनेमिक रूप से लेने के लिए कर सकते हैं। उदाहरण के लिए, यदि आपके पास सेल A1 में "Sales" जैसा शीट नाम है। आप उस शीट के सेल B2 से मान लेना चाहते हैं।

नीचे दिया गया फ़ॉर्मूला 'Sales'!B2 जैसा रेफ़रेंस बनाता है और उस सेल का मान लौटाता है।

=INDIRECT("'" & A1 & "'!B2")संचालित द्वारा

Excel में अप्रत्यक्ष रूप से अन्य वर्कशीट्स को रेफ़रेंस करने के लिए INDIRECT() का उपयोग। चित्र: लेखक।

यदि शीट नाम में स्पेस या विशेष वर्ण होने की संभावना हो, तो हमेशा उसे सिंगल कोट ' में रखें, जैसे 'Sales 2023'!A1।

नेम्ड रेंज को रेफ़रेंस करें

आप INDIRECT() का उपयोग नेम्ड रेंज को डायनेमिक रूप से रेफ़रेंस करने के लिए भी कर सकते हैं—ड्रॉपडाउन या यूज़र इनपुट के साथ मिलकर यह बहुत शक्तिशाली हो जाता है।

मान लें कि आपके पास नेम्ड रेंजेज Sales_2023 और Sales_2023 हैं, जहाँ सेल “A1” में "Sales_2023" है।

Excel में सेल रेंज को नाम देना। चित्र: लेखक।

नीचे दिया गया फ़ॉर्मूला नेम्ड रेंज Sales_2023 के मानों का योग करेगा, भले ही इसे सीधे रेफ़रेंस न किया गया हो। यह तरीका यूज़र-ड्रिवन डैशबोर्ड्स या मल्टी-सिनारियो वाले मॉडलों के लिए बढ़िया है।

=SUM(INDIRECT(A1))संचालित द्वारा

Excel में नेम्ड रेंज को रेफ़रेंस करने के लिए INDIRECT() का उपयोग। चित्र: लेखक।

निश्चित (फिक्स्ड) रेफ़रेंस जो नहीं बदलते

आम तौर पर, यदि आप पंक्तियाँ या कॉलम जोड़ते/हटाते हैं, तो फ़ॉर्मूलों में सेल रेफ़रेंस अपने-आप समायोजित हो जाते हैं। लेकिन INDIRECT() ऐसे एबसोल्यूट रेफ़रेंस बनाता है जो शिफ्ट नहीं होते।

उदाहरण के लिए, यदि “A1” सेल में 100 है, तो =A1 और =INDIRECT("A1") दोनों 100 लौटाएँगे। हालाँकि, यदि आप मान को शिफ्ट करें

Excel में डायरेक्ट सेल रेफ़रेंस। चित्र: लेखक।

Excel में सेल रेफ़रेंसिंग के लिए INDIRECT() का उपयोग। चित्र: लेखक।

लेकिन यदि आप पंक्ति 1 के ऊपर एक नई लाइन जोड़ते हैं, तो D3 (पहले B2 था) अब =A3 दिखाता है क्योंकि मान पंक्ति के साथ शिफ्ट हो गया। INDIRECT() वाले सेल E3 में अब भी मूल A2 दिखता है, जो अब खाली हो सकता है या अलग मान (UID) रख सकता है।

Excel में फिक्स्ड रेफ़रेंस के लिए INDIRECT() का उपयोग। चित्र: लेखक।

एडवांस्ड उदाहरण और उपयोग

जब आप बुनियादी बातों में सहज हो जाते हैं, तो INDIRECT() फ़ंक्शन और उभर कर आता है—खासकर जब इसे VLOOKUP(), ROW(), और डायनेमिक एरेज़ जैसी फ़ंक्शंस के साथ जोड़ा जाए। ये संयोजन और अधिक डायनेमिक और लचीले मॉडल खोलते हैं।

VLOOKUP() के साथ INDIRECT() का उपयोग

आप INDIRECT() को VLOOKUP() के साथ जोड़कर यूज़र इनपुट के आधार पर लुकअप टेबल को डायनेमिक रूप से बदल सकते हैं।

मान लें आपके पास दो टेबल हैं, Prices_2022 और Prices_2023। अलग-अलग सेल्स में, G1: Prices_2022 और G2: Apple`। नीचे दिया गया फ़ॉर्मूला A1 के मान के आधार पर चुने गए वर्ष की टेबल में "Apple" खोजता है और उसकी कीमत लौटाता है।

=VLOOKUP(G2, INDIRECT(G1), 2, FALSE)संचालित द्वारा

Excel में VLOOKUP() के साथ INDIRECT() का उपयोग। चित्र: लेखक।

अधिक जानने के लिए हमारा VLOOKUP() from Another Sheet: A How-to in Excel ट्यूटोरियल देखें और अलग-अलग Excel शीट्स से डेटा खोजने व प्राप्त करने की विधि सीखें।

R1C1 रेफ़रेंसिंग के साथ INDIRECT()

INDIRECT() R1C1 नोटेशन का भी उपयोग कर सकता है, जो खासकर तब उपयोगी होता है जब आप मैक्रोज़ में फ़ॉर्मूले डायनेमिक रूप से बनाते हैं या प्रोग्रामेटिक रूप से पोज़ीशंस को रेफ़रेंस करते हैं।

उदाहरण के लिए, नीचे दिया गया फ़ॉर्मूला पंक्ति 2, कॉलम 3 (सेल C2) का मान लौटाता है। दूसरा आर्ग्युमेंट (FALSE) Excel को बताता है कि आप R1C1 स्टाइल का उपयोग कर रहे हैं, A1 का नहीं।

=INDIRECT("R2C3", FALSE)संचालित द्वारा

Excel में R1C1 के साथ INDIRECT() का उपयोग। चित्र: लेखक।

निम्न फ़ॉर्मूला में ROW() और COLUMN() का डायनेमिक उपयोग शामिल है। यह फ़ॉर्मूला हमेशा उसी पंक्ति के कॉलम 3 से मान लौटाएगा, जहाँ यह फ़ॉर्मूला लिखा गया है।

=INDIRECT("R" & ROW() & "C3", FALSE)संचालित द्वारा

Excel में ROW() और COLUMN() के साथ INDIRECT() का उपयोग। चित्र: लेखक।

INDIRECT() और डायनेमिक एरेज़

Excel में डायनेमिक एरेज़ आने से पहले, INDIRECT() का उपयोग एरे-जैसे व्यवहार बनाने के लिए आम था।

उदाहरण के लिए, नीचे दिया गया फ़ॉर्मूला एक एरे लौटाता है: {1; 2; 3; 4; 5}। यह पंक्तियों को अप्रत्यक्ष रूप से रेफ़रेंस करके 1 से 5 तक का एरे सिम्युलेट करता है।

=ROW(INDIRECT("1:5"))संचालित द्वारा

Excel में डायनेमिक एरेज़ के लिए ROW() के साथ INDIRECT() का उपयोग। चित्र: लेखक।

आधुनिक विकल्प SEQUENCE() फ़ंक्शन का उपयोग करना है।

=SEQUENCE(5)संचालित द्वारा

Excel में डायनेमिक एरेज़ के लिए SEQUENCE() का उपयोग। चित्र: लेखक।

डायनेमिक एरेज़ के लिए INDIRECT() का उपयोग अब भी काम करता है, लेकिन यह SEQUENCE(), FILTER(), और अन्य डायनेमिक एरे फ़ंक्शंस की तुलना में कम प्रभावी और कम पठनीय माना जाता है, जो आधुनिक Excel (Office 365 और Excel 2021+) में उपलब्ध हैं।

सावधानियाँ और परफॉर्मेंस संबंधी चिंताएँ

INDIRECT() फ़ंक्शन की कुछ महत्वपूर्ण सीमाएँ हैं, खासकर जब इसे बड़े या जटिल Excel वर्कबुक्स में उपयोग किया जाता है। इन मुद्दों में निम्न शामिल हैं:

• वॉलेटाइल फ़ंक्शन (बार-बार पुनर्गणना): INDIRECT() वॉलेटाइल है, यानी वर्कबुक में कहीं भी कोई बदलाव होते ही यह पुनर्गणना करता है—भले ही बदलाव का फ़ॉर्मूले से कोई लेना-देना न हो। इससे परफॉर्मेंस धीमी हो सकती है।

• डीबग करना चुनौतीपूर्ण हो सकता है: चूँकि INDIRECT() टेक्स्ट से रेफ़रेंस बनाता है, Excel इन्हें साधारण सेल रेफ़रेंस की तरह आसानी से ट्रेस नहीं कर पाता। इससे "Trace Precedents/Dependents" से ऑडिट करना मुश्किल हो जाता है, या बदलाव होने पर टूटे रेफ़रेंस पकड़ना कठिन हो सकता है।

• आधुनिक विकल्पों पर विचार करें: जहाँ संभव हो, नॉन-वॉलेटाइल फ़ंक्शंस का उपयोग करना बेहतर होता है जो बेहतर परफॉर्मेंस और स्पष्टता देते हैं। इनमें डायनेमिक सेल लुकअप के लिए INDEX(), पूर्व-परिभाषित रेंज/विकल्पों में से डायनेमिक चयन के लिए CHOOSE(), और डायनेमिक न्यूमेरिक एरे जनरेट करने के लिए SEQUENCE() शामिल हैं।

कब INDIRECT() का उपयोग न करें

हालाँकि INDIRECT() शक्तिशाली लचीलापन देता है, यह हमेशा सर्वश्रेष्ठ विकल्प नहीं होता। कई बार यह अनावश्यक जटिलता, परफॉर्मेंस समस्याएँ, या मेंटेनबिलिटी मुद्दे लाता है। नीचे कुछ स्थितियाँ हैं जिनसे बचना चाहिए और बेहतर विकल्प।

• उसी शीट के भीतर डायनेमिक सेल रेफ़रेंस: बेहतर परफॉर्मेंस और ट्रेसएबिलिटी के लिए INDIRECT() के बजाय INDEX() का उपयोग करें।

• डायनेमिक एरे या नंबर सीक्वेंस: =ROW(INDIRECT("1:10")) से बचें। इसकी जगह =SEQUENCE(10) उपयोग करें—तेज़, साफ और नॉन-वॉलेटाइल।

• जब ट्रेसएबिलिटी महत्वपूर्ण हो: INDIRECT() वाले फ़ॉर्मूलों को “Trace Precedents” या “Evaluate Formula” से प्रभावी ढंग से ट्रेस नहीं किया जा सकता।

• स्टैटिक सेल या रेंज को रेफ़रेंस करना: यदि आप सीधे =A1 लिख सकते हैं तो INDIRECT("A1") का उपयोग न करें।

• बड़ी स्प्रेडशीट्स में भारी उपयोग: चूँकि INDIRECT() वॉलेटाइल है, यह लगातार पुनर्गणना करता है और परफॉर्मेंस को काफी धीमा कर सकता है।

INDIRECT() के विकल्प

नीचे दी गई तालिका INDIRECT() के सर्वोत्तम विकल्पों का सार देती है।

मुख्य बातें

INDIRECT() टेक्स्ट स्ट्रिंग्स को लाइव रेफ़रेंस में बदलता है, जिससे यह डायनेमिक और लचीली स्प्रेडशीट्स बनाने के लिए एक शक्तिशाली टूल बन जाता है। यह अलग-अलग शीट्स, नेम्ड रेंजेज, या ऐसे फिक्स्ड सेल्स को रेफ़रेंस करने में उपयोगी है जिन्हें बदलावों के साथ शिफ्ट नहीं होना चाहिए। हालाँकि, यह एक वॉलेटाइल फ़ंक्शन है जो अक्सर पुनर्गणना करता है और बड़े वर्कबुक्स को धीमा कर सकता है। यह पारंपरिक फ़ंक्शंस की तुलना में ट्रेस और डीबग करने में भी कठिन है। INDIRECT() का चयनित रूप से उपयोग करें और सुरक्षित, अधिक कुशल विकल्पों पर विचार करें।

B2B डेटा में इनसाइट्स सीखने के लिए मैं हमारा Data Analysis in Excel कोर्स और नवीनतम Excel संस्करणों में उपलब्ध फ़ंक्शंस के बारे में अधिक जानने हेतु Excel Fundamentals स्किल ट्रैक की सिफारिश करता/करती हूँ। यदि आप एडवांस्ड रेफ़रेंसिंग और लुकअप्स एक्सप्लोर कर रहे हैं, तो हमारा Advanced Excel Functions कोर्स उपयुक्त है।