एक्सेल में संरचित संदर्भ - उदाहरण के लिए स्टेप बाय स्टेप गाइड

एक्सेल में संरचित संदर्भ कैसे बनाएं?

संरचित संदर्भ एक्सेल टेबल के साथ शुरू होता है। जैसे ही टेबल एक्सेल में बनाई जाती हैं, यह स्वचालित रूप से आपके लिए संरचित संदर्भ बनाता है।

अब नीचे की छवि पर एक नज़र डालें।

• चरण 1: मैंने सेल B3 के लिए एक लिंक दिया था। लिंक को B2 के रूप में दिखाने के बजाय, इसे Table1 (@Sales) के रूप में दिखाया जा रहा है । यहां Table1 तालिका का नाम है, और @Sales वह कॉलम है जिसका हम उल्लेख कर रहे हैं। इस कॉलम की सभी कोशिकाएँ तालिका नाम से और उसके बाद कॉलम शीर्षक नाम से संदर्भित होती हैं।
• चरण 2: अब, मैं तालिका नाम को Data_Table में बदल दूंगा और कॉलम हेडिंग को अमाउंट में बदल दूंगा ।
• चरण 3: तालिका का नाम बदलने के लिए, तालिका के अंदर एक कर्सर रखें> डिज़ाइन> तालिका नाम पर जाएं।

• चरण 4: तालिका का नाम Data_Table के रूप में रखें।

• चरण 5: अब, परिवर्तन B3 सेल का संदर्भ देता है।

तो हम समझ गए हैं कि संरचित संदर्भ के दो भाग हैं तालिका का नाम और कॉलम नाम।

उदाहरण

उदाहरण 1

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

मुझे सामान्य श्रेणी और एक्सेल तालिका दोनों के लिए SUM सूत्र लागू करने दें।

सामान्य श्रेणी के लिए एसयूएम फॉर्मूला।

एक्सेल टेबल के लिए फॉर्मूला।

मुझे सामान्य और एक्सेल टेबल दोनों के डेटा में कुछ लाइनें जोड़ने दें। मैंने डेटा में 2 लाइन आइटम जोड़े हैं, अब अंतर देखें।

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

उदाहरण # 2

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

विक्रय मूल्य प्राप्त करने के लिए, सूत्र Qty * मूल्य है । आइए इस सूत्र को तालिका में लागू करें।

सूत्र कहता है (@QTY) * (@PRICE)। यह बी 2 * सी 2 के सामान्य संदर्भ की तुलना में अधिक समझ में आता है । यदि हम सूत्र को तालिका के अंदर रख रहे हैं तो हमें तालिका का नाम नहीं मिलता है।

एक्सेल संरचित संदर्भ के साथ समस्याएं

संरचित संदर्भों का उपयोग करते समय, हम कुछ समस्याओं का सामना करते हैं, जिन्हें नीचे सूचीबद्ध किया गया है।

समस्या # 1

संरचित संदर्भों की अपनी समस्याएं भी हैं। हम सभी एक्सेल फॉर्मूला को लागू करने और अन्य शेष कोशिकाओं की नकल करने या इसे खींचने से परिचित हैं। संरचित सन्दर्भों में यह समान प्रक्रिया नहीं है। यह थोड़ा अलग तरीके से काम करता है।

अब, नीचे दिए गए उदाहरण को देखें। मैंने सामान्य श्रेणी के लिए एक्सेल में SUM सूत्र लागू किया है।

यदि मुझे मूल्य और बिक्री मूल्य की गणना करनी है, तो मैं बस वर्तमान फॉर्मूले को कॉपी और पेस्ट करूंगा या अन्य दो कक्षों में वर्तमान सूत्र को खींचूंगा, और यह मुझे मूल्य और बिक्री मूल्य का सारांश मूल्य प्रदान करेगा।

अब Qty कॉलम के लिए एक्सेल टेबल के लिए समान फॉर्मूला लागू करें।

अब हमें Qty कॉलम का योग मिला। सामान्य श्रेणी की तरह, सूत्र वर्तमान सूत्र की प्रतिलिपि बनाता है और इसे कुल मूल्य प्राप्त करने के लिए मूल्य कॉलम में पेस्ट करता है।

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

संदर्भ बदलने के लिए सूत्र खींचें

अब हम इसकी सीमा जानते हैं। हम संरचित संदर्भों के साथ अब कॉपी-पेस्ट का काम नहीं कर सकते। फिर हम इस सीमा को कैसे पार करते हैं?

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

अब हमने संबंधित योगों को प्राप्त करने के लिए सूत्र अपडेट किए हैं।

समस्या # 2

हमने संरचना संदर्भों के साथ एक समस्या देखी है, और हमने इसका समाधान भी ढूंढ लिया है, लेकिन हमारे यहां एक और समस्या है, हम कॉल को पूर्ण संदर्भ के रूप में नहीं बना सकते हैं यदि हम सूत्र को अन्य कक्षों में खींच रहे हैं।

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

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

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

ओह !! मुझे फरवरी और मार्च कॉलम में कोई मान नहीं मिला। क्या समस्या होगी ??? बारीकी से सूत्र देखें।

हमने जनवरी महीने से फॉर्मूला निकाला है। SUMIF फ़ंक्शन में प्रथम तर्क क्राइटेरिया रेंज Sales_Table (उत्पाद) है क्योंकि हमने सूत्र को खींच लिया है। यह बदलकर सेल्स _टेबल (जनवरी) हो गया है।

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

समाधान हमें संदर्भित कॉलम को डुप्लिकेट करने की आवश्यकता है, जैसा कि नीचे की छवि में दिखाया गया है।

अब हम अन्य स्तंभों को दो स्तंभों में सूत्र खींच सकते हैं। मानदंड रेंज स्थिर होगी, और अन्य कॉलम संदर्भ तदनुसार बदलेंगे।

प्रो टिप: ROW को एक पूर्ण संदर्भ के रूप में बनाने के लिए, हमें डबल ROW प्रविष्टि बनाने की आवश्यकता है, लेकिन हमें ROW नाम से पहले @ प्रतीक रखना होगा।

= Sales_Table (@ (उत्पाद) :( उत्पाद))

एक्सेल में संरचित संदर्भ कैसे बंद करें?

यदि आप संरचित संदर्भों के प्रशंसक नहीं हैं, तो आप नीचे दिए गए चरणों का पालन करके बंद कर सकते हैं।

• चरण 1: फ़ाइल> विकल्प पर जाएं।
• चरण 2: सूत्र> सूत्र में तालिका नामों का उपयोग अनचेक करें।

याद रखने वाली चीज़ें

• संरचित संदर्भ में पूर्ण संदर्भ बनाने के लिए, हमें कॉलम नाम को दोगुना करना होगा।
• हम संरचित संदर्भ के फार्मूले की नकल नहीं कर सकते हैं; इसके बजाय, हमें सूत्र को खींचने की आवश्यकता है।
• हम ठीक से यह नहीं देख सकते हैं कि हम किस कोशिका को संरचित संदर्भों में संदर्भित कर रहे हैं।
• यदि आप संरचित संदर्भों में रुचि नहीं रखते हैं, तो आप उन्हें बंद कर सकते हैं।