सापेक्ष और निरपेक्ष सेल संदर्भ, और स्वरूपण

इस पाठ में हम सेल के संदर्भों पर चर्चा करते हैं, कैसे एक फॉर्मूला, और प्रारूप कोशिकाओं को कॉपी या स्थानांतरित करें। शुरू करने के लिए, आइए स्पष्ट करें कि हम सेल संदर्भों से क्या मतलब है, जो कि सूत्रों और कार्यों की शक्ति और बहुमुखी प्रतिभा को बहुत कम करते हैं। कैसे सेल संदर्भ काम पर एक ठोस समझ आप अपने एक्सेल स्प्रेडशीट के सबसे बाहर निकलने के लिए अनुमति देगा!

स्कूल की मान्यता

1. आपको फॉर्मूले और कार्यों की आवश्यकता क्यों है?
2. एक सूत्र को परिभाषित करना और बनाना
3. सापेक्ष और निरपेक्ष सेल संदर्भ, और स्वरूपण
4. उपयोगी कार्य आपको पता होना चाहिए
5. लुकअप, चार्ट, सांख्यिकी और पिवट टेबल्स

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

एक सेल संदर्भ क्या है?

एक "सेल रेफरेंस" का मतलब उस सेल से है जिसके लिए कोई अन्य सेल संदर्भित करता है। उदाहरण के लिए, यदि सेल A1 में आपके पास = A2 है। तब A1 A2 को संदर्भित करता है.

आइए समीक्षा करें कि हमने पाठ 2 में पंक्तियों और स्तंभों के बारे में क्या कहा ताकि हम आगे के सेल संदर्भों का पता लगा सकें.

स्प्रेडशीट में कोशिकाओं को पंक्तियों और स्तंभों द्वारा संदर्भित किया जाता है। कॉलम लंबवत हैं और अक्षरों के साथ लेबल किए गए हैं। पंक्तियों को क्षैतिज और संख्याओं के साथ लेबल किया जाता है.

स्प्रेडशीट में पहली सेल A1 है, जिसका अर्थ है स्तंभ A, पंक्ति 1, B3 दूसरे स्तंभ पर स्थित सेल को संदर्भित करता है, तीसरी पंक्ति, और इसी तरह.

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

सेल संदर्भ के प्रकार

सेल संदर्भ तीन प्रकार के होते हैं.

निरपेक्ष - इसका मतलब है कि यदि आप किसी अन्य सेल में सेल को कॉपी या स्थानांतरित करते हैं तो सेल संदर्भ समान रहता है। यह पंक्ति और स्तंभ को एंकरिंग करके किया जाता है, इसलिए इसे कॉपी या स्थानांतरित करने पर परिवर्तित नहीं होता है.

सापेक्ष - सापेक्ष संदर्भ का अर्थ है कि जैसे ही आप इसे कॉपी या स्थानांतरित करते हैं, सेल का पता बदल जाता है; यानी सेल संदर्भ इसके स्थान के सापेक्ष है.

मिश्रित - इसका मतलब है कि आप सेल को कॉपी या स्थानांतरित करने के लिए पंक्ति या स्तंभ में से किसी एक का चयन कर सकते हैं, ताकि एक बदल जाए और दूसरा न हो। उदाहरण के लिए, आप पंक्ति संदर्भ को एंकर कर सकते हैं और फिर दो पंक्तियों और चार स्तंभों के नीचे एक सेल को स्थानांतरित कर सकते हैं और पंक्ति संदर्भ समान रहता है। इसे हम आगे बताएंगे.

सापेक्ष संदर्भ

आइए उस पहले उदाहरण का संदर्भ लें - मान लीजिए सेल A1 में हमारे पास एक सूत्र है जो बस = A2 कहता है। इसका मतलब है कि सेल A1 में एक्सेल आउटपुट जो भी सेल A2 में डाला गया है। सेल A2 में हमने "A2" टाइप किया है, इसलिए Excel सेल A1 में "A2" मान प्रदर्शित करता है.

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

जैसे ही आप सेल को दाईं ओर ले जाते हैं, कॉलम संख्या बढ़ जाती है। जैसे-जैसे आप इसे नीचे ले जाते हैं, पंक्ति संख्या बढ़ती जाती है। यह जिस सेल को इंगित करता है, वह सेल संदर्भ, साथ ही बदलता है। यह नीचे सचित्र है:

हमारे उदाहरण के साथ जारी है, और नीचे दिए गए ग्राफिक को देख रहे हैं, अगर आप सेल A1 की सामग्री को दाईं ओर कॉपी करते हैं और चार नीचे आपने इसे सेल C5 में स्थानांतरित कर दिया है.

हमने सेल दो कॉलम को दाईं ओर और चार नीचे कॉपी किया। इसका मतलब है कि हमने सेल को बदल दिया है, यह दो को पार करता है और चार को नीचे। A1 = A2 अब C5 = C6 है। A2 का संदर्भ देने के बजाय, अब सेल C5 सेल C6 को संदर्भित करता है.

दिखाया गया मान 0 है क्योंकि सेल C6 खाली है। सेल C6 में हम "I C6" टाइप करते हैं और अब C5 प्रदर्शित करता है "मैं C6 हूँ।"

उदाहरण: पाठ सूत्र

एक और उदाहरण आजमाते हैं। पाठ 2 से याद रखें कि हमें पहले और अंतिम नाम में पूरा नाम कैसे विभाजित करना था? जब हम इस सूत्र की नकल करते हैं तो क्या होता है?

सूत्र लिखें = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1)) या सेल C3 में पाठ की प्रतिलिपि बनाएँ। वास्तविक सेल को कॉपी न करें, केवल टेक्स्ट, टेक्स्ट को कॉपी करें, अन्यथा यह संदर्भ को अपडेट करेगा.

आप एक बॉक्स में एक स्प्रेडशीट के शीर्ष पर एक सेल की सामग्री को संपादित कर सकते हैं, जहां "fx" कहा जाता है। वह बॉक्स एक सेल की तुलना में लंबा है, इसलिए इसे संपादित करना आसान है.

अब हमारे पास है:

कुछ भी जटिल नहीं है, हमने सेल सी 3 में एक नया सूत्र लिखा है। अब C3 को C2 और C4 कोशिकाओं में कॉपी करें। नीचे दिए गए परिणाम देखें:

अब हमारे पास अलेक्जेंडर हैमिल्टन और थॉमस जेफरसन का पहला नाम है.

C2, C3 और C4 को हाइलाइट करने के लिए कर्सर का उपयोग करें। कर्सर को सेल बी 2 में इंगित करें और सामग्री को पेस्ट करें। देखो क्या हुआ - हमें एक त्रुटि मिली: "#REF।" यह क्यों है?

जब हमने कॉलम C से कॉलम B तक की कोशिकाओं को कॉपी किया तो यह संदर्भ एक कॉलम को बाईं ओर अपडेट किया = RIGHT (A2, LEN (A2) - FIND (",", A2) - 1).

इसने ए के संदर्भ को ए के बाईं ओर के कॉलम में बदल दिया, लेकिन कॉलम ए के बाईं ओर कोई कॉलम नहीं है। इसलिए कंप्यूटर को यह नहीं पता है कि आपका क्या मतलब है.

उदाहरण के लिए B2 में नया सूत्र है, = RIGHT (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1) और परिणाम #REF है:

कक्षों की एक श्रेणी के लिए एक सूत्र की नकल करना

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

"रेंज" से हमारा मतलब एक से अधिक सेल से है। उदाहरण के लिए, (C1: C10) का अर्थ है सेल C1 से सेल C10 तक की सभी कोशिकाएँ। तो यह कोशिकाओं का एक स्तंभ है। एक अन्य उदाहरण (A1: AZ1) स्तंभ A से स्तंभ AZ तक की शीर्ष पंक्ति है.

यदि कोई सीमा पांच स्तंभों और दस पंक्तियों को पार करती है, तो आप शीर्ष-बाएँ कक्ष और नीचे दाईं ओर, उदा।, A1: E10 लिखकर सीमा का संकेत देते हैं। यह एक वर्ग क्षेत्र है जो पंक्तियों और स्तंभों को पार करता है और केवल स्तंभ या पंक्ति का हिस्सा नहीं होता है.

यहां एक उदाहरण है जो दिखाता है कि एक सेल को कई स्थानों पर कैसे कॉपी किया जाए। मान लीजिए हम एक स्प्रेडशीट में महीने के लिए हमारे अनुमानित खर्चों को दिखाना चाहते हैं ताकि हम एक बजट बना सकें। हम इस तरह एक स्प्रेडशीट बनाते हैं:

अब हमारे बजट के लिए शेष राशि देने के लिए सेल C3 (= B3 + C2) के फॉर्मूले को कॉलम के बाकी हिस्सों में कॉपी करें। जैसे ही आप इसे कॉपी करते हैं, Excel सेल संदर्भ को अपडेट करता है। परिणाम नीचे दिखाया गया है:

जैसा कि आप देख सकते हैं, प्रत्येक नए सेल अपडेट सापेक्ष नए स्थान पर, इसलिए सेल C4 अपने सूत्र को = B4 + C3 में अपडेट करता है:

सेल C5 अद्यतन = B5 + C4, और इसी तरह:

निरपेक्ष संदर्भ

जब आप किसी कक्ष को स्थानांतरित या कॉपी करते हैं, तो एक पूर्ण संदर्भ नहीं बदलता है। हम एक पूर्ण संदर्भ बनाने के लिए $ साइन का उपयोग करते हैं - यह याद रखने के लिए, एक एंकर के रूप में एक डॉलर के संकेत के बारे में सोचें.

उदाहरण के लिए, किसी भी सेल में सूत्र = $ A $ 1 दर्ज करें। स्तंभ A के सामने $ का अर्थ स्तंभ को बदलना नहीं है, पंक्ति 1 के सामने $ का अर्थ है कि जब आप किसी अन्य कक्ष पर सेल की प्रतिलिपि बनाएँ या स्थानांतरित करें तो स्तंभ को न बदलें.

जैसा कि आप नीचे दिए गए उदाहरण में देख सकते हैं, सेल B1 में हमारा एक सापेक्ष संदर्भ = A1 है। जब हम B1 को उसके नीचे की चार कोशिकाओं में कॉपी करते हैं, तो संबंधित संदर्भ = A1 सेल में बाईं ओर बदल जाता है, इसलिए B2 A2, B3 बन जाते हैं। A3 बन जाते हैं, आदि उन कोशिकाओं का स्पष्ट रूप से कोई मूल्य नहीं है, इसलिए आउटपुट शून्य है.

हालाँकि, यदि हम = $ A1 $ 1 का उपयोग करते हैं, जैसे कि C1 में और हम इसे नीचे की चार कोशिकाओं में कॉपी करते हैं, तो संदर्भ निरपेक्ष है, इस प्रकार यह कभी नहीं बदलता है और आउटपुट हमेशा सेल A1 में मान के बराबर होता है.

मान लीजिए कि आप अपनी रुचि का हिसाब रख रहे हैं, जैसे नीचे दिए गए उदाहरण में। C4 = B4 * B1 में सूत्र "ब्याज दर" * "शेष" = "प्रति वर्ष ब्याज" है।

अब, आपने अपना बजट बदल दिया है और म्यूचुअल फंड खरीदने के लिए अतिरिक्त $ 2,000 की बचत की है। मान लीजिए कि यह एक फिक्स्ड रेट फंड है और यह उसी ब्याज दर का भुगतान करता है। स्प्रेडशीट में नया खाता और शेष राशि दर्ज करें और फिर सेल C4 से सेल C5 के सूत्र = B4 * B1 की प्रतिलिपि बनाएँ.

नया बजट इस तरह दिखता है:

नया म्यूचुअल फंड प्रति वर्ष ब्याज में $ 0 कमाता है, जो कि सही नहीं है क्योंकि ब्याज दर स्पष्ट रूप से 5 प्रतिशत है.

एक्सेल उन कोशिकाओं पर प्रकाश डालता है जिनके लिए एक सूत्र संदर्भ देता है। आप ऊपर देख सकते हैं कि ब्याज दर (बी 1) का संदर्भ रिक्त सेल बी 2 में ले जाया गया है। हमें पंक्ति और स्तंभ संदर्भ को लंगर देने के लिए डॉलर चिह्न का उपयोग करके $ B $ 1 लिखकर B1 के संदर्भ को पूर्ण बनाना चाहिए.

C4 में पहली गणना को पढ़ने के लिए = B4 * $ B $ 1 को नीचे दिखाए अनुसार पढ़ें:

फिर उस फॉर्मूले को C4 से C5 तक कॉपी करें। स्प्रेडशीट अब इस तरह दिखती है:

चूँकि हमने फॉर्मूला एक सेल को कॉपी किया था, यानी एक-एक करके पंक्ति को बढ़ाया, नया फॉर्मूला है = B5 * $ B $ 1। म्यूचुअल फंड ब्याज दर की गणना अब सही ढंग से की जाती है, क्योंकि ब्याज दर सेल बी 1 के लिए लंगर डाले हुए है.

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

नाम एक सेल या एक सीमा को संदर्भित कर सकते हैं, और आप किसी सूत्र में नाम का उपयोग कर सकते हैं, उदाहरण के लिए = interest_rate * 8 लिखने के लिए एक ही चीज़ है = $ B $ 1 * 8.

मिश्रित संदर्भ

मिश्रित संदर्भ हैं भी झगड़ा या स्तंभ लंगर है.

उदाहरण के लिए, मान लीजिए कि आप बजट बनाने वाले किसान हैं। आप एक फ़ीड स्टोर के मालिक हैं और बीज बेचते हैं। आप मकई, सोयाबीन और अल्फाल्फा लगाने जा रहे हैं। नीचे दी गई स्प्रेडशीट प्रति एकड़ लागत दर्शाती है। "प्रति एकड़ लागत" = "प्रति पाउंड मूल्य" * "प्रति एकड़ बीजों का पाउंड" - यही वह है जो आपको एक एकड़ में रोपण करने के लिए खर्च होगा.

सेल D2 में प्रति एकड़ लागत = $ B2 * C2 दर्ज करें। आप कह रहे हैं कि आप प्रति पाउंड कॉलम में मूल्य का लंगर डालना चाहते हैं। फिर उस सूत्र को उसी कॉलम में अन्य पंक्तियों में कॉपी करें:

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

हम दो कॉलम जोड़ते हैं: "इन्वेंट्री में बीज का पाउंड" और फिर "इन्वेंट्री का मूल्य।" अब, सेल D2 को F4 पर कॉपी करें और ध्यान दें कि मूल सूत्र के पहले भाग में पंक्ति संदर्भ ($ B2) को पंक्ति में अद्यतन किया गया है 4 लेकिन कॉलम स्थिर रहता है क्योंकि $ लंगर इसे "बी" तक ले जाता है

यह एक मिश्रित संदर्भ है क्योंकि स्तंभ निरपेक्ष है और पंक्ति सापेक्ष है.

परिपत्र संदर्भ

एक परिपत्र संदर्भ तब होता है जब कोई सूत्र स्वयं को संदर्भित करता है.

उदाहरण के लिए, आप c3 = c3 + 1 नहीं लिख सकते हैं। इस तरह की गणना को "पुनरावृत्ति" कहा जाता है, जिसका अर्थ है कि यह खुद को दोहराता है। एक्सेल पुनरावृत्ति का समर्थन नहीं करता है क्योंकि यह सब कुछ केवल एक बार की गणना करता है.

यदि आप सेल B5 में SUM (B1: B5) लिखकर ऐसा करने का प्रयास करते हैं:

एक चेतावनी स्क्रीन पॉप अप:

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

यदि आपके पास एक गोलाकार संदर्भ है, तो हर बार जब आप स्प्रेडशीट खोलते हैं, तो एक्सेल आपको उस पॉप-अप विंडो के साथ बताएगा कि आपके पास एक परिपत्र संदर्भ है.

अन्य वर्कशीट का संदर्भ

"वर्कबुक" "वर्कशीट" का एक संग्रह है। सीधे शब्दों में कहें, तो इसका मतलब है कि आपके पास एक ही एक्सेल फाइल (वर्कबुक) में कई स्प्रेडशीट (वर्कशीट) हो सकती हैं। जैसा कि आप नीचे दिए गए उदाहरण में देख सकते हैं, हमारे उदाहरण कार्यपुस्तिका में कई कार्यपत्रक हैं (लाल रंग में).

डिफ़ॉल्ट रूप से वर्कशीट को शीट 1, शीट 2 और आगे नाम दिया गया है। आप Excel स्क्रीन के नीचे "+" पर क्लिक करके एक नया बनाते हैं.

आप एक्सेल प्रोग्राम स्क्रीन के नीचे दिखाए गए वर्कशीट टैब पर राइट-क्लिक करके, नया नाम चुनकर, टाइप करके "वर्कशीट" को "लोन" या "बजट" जैसे कुछ उपयोगी में बदल सकते हैं।.

या आप बस टैब पर डबल-क्लिक कर सकते हैं और उसका नाम बदल सकते हैं.

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

• आज की तारीख
• डॉलर से यूरो तक मुद्रा रूपांतरण दर
• कुछ भी जो कार्यपुस्तिका में सभी वर्कशीट के लिए प्रासंगिक है

नीचे वर्कशीट "ब्याज" का एक उदाहरण है वर्कशीट "ऋण," सेल B1 का संदर्भ.

यदि हम "ऋण" वर्कशीट देखते हैं, तो हम ऋण राशि का संदर्भ देख सकते हैं:

अगला आनेवाला…

हमें उम्मीद है कि अब आपके पास रिश्तेदार, निरपेक्ष, और मिश्रित सहित सेल संदर्भों की एक फर्म पकड़ है। वहाँ निश्चित रूप से बहुत कुछ है.

यह आज के पाठ के लिए है, पाठ 4 में, हम कुछ उपयोगी कार्यों पर चर्चा करेंगे जिन्हें आप दैनिक एक्सेल उपयोग के लिए जानना चाहते हैं.