एक्सेल फॉर्मूला में सेल को फ्रीज कैसे करें

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

इस मामले में, आप एक्सेल में सेल संदर्भ को ठीक कर सकते हैं। और यह एक साथ कई तरीकों से किया जा सकता है। आइए इस लक्ष्य को प्राप्त करने के तरीके पर करीब से नज़र डालें।

एक्सेल लिंक क्या है

शीट कोशिकाओं से बनी होती है। उनमें से प्रत्येक में विशिष्ट जानकारी होती है। अन्य सेल इसका उपयोग गणना में कर सकते हैं। लेकिन वे कैसे समझते हैं कि डेटा कहां से प्राप्त करें? यह उन्हें लिंक बनाने में मदद करता है।

प्रत्येक लिंक एक सेल को एक अक्षर और एक नंबर के साथ नामित करता है। एक अक्षर एक कॉलम का प्रतिनिधित्व करता है और एक संख्या एक पंक्ति का प्रतिनिधित्व करती है। 

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

1 विधि

कॉलम और रो दोनों पतों को सहेजने के लिए, इन चरणों का पालन करें:

1. सूत्र वाले सेल पर क्लिक करें।
2. हमें जिस सेल की जरूरत है उसके लिए फॉर्मूला बार पर क्लिक करें।
3. F4 दबाएं।

परिणामस्वरूप, सेल संदर्भ निरपेक्ष में बदल जाएगा। इसे विशेषता डॉलर चिह्न द्वारा पहचाना जा सकता है। उदाहरण के लिए, यदि आप सेल B2 पर क्लिक करते हैं और फिर F4 पर क्लिक करते हैं, तो लिंक इस तरह दिखेगा: $B$2।

प्रति सेल पते के भाग से पहले डॉलर के चिह्न का क्या अर्थ है?

1. यदि इसे किसी अक्षर के सामने रखा जाता है, तो यह इंगित करता है कि स्तंभ संदर्भ वही रहता है, चाहे सूत्र को कहीं भी स्थानांतरित किया गया हो।
2. यदि संख्या के सामने डॉलर का चिह्न है, तो यह इंगित करता है कि स्ट्रिंग पिन की गई है। 

2 विधि

यह विधि लगभग पिछले वाले के समान ही है, केवल आपको F4 को दो बार दबाने की आवश्यकता है। उदाहरण के लिए, यदि हमारे पास सेल B2 था, तो उसके बाद यह B$2 हो जाएगा। आसान शब्दों में कहें तो इस तरह हम लाइन को ठीक करने में कामयाब रहे। इस मामले में, कॉलम का अक्षर बदल जाएगा।

यह बहुत सुविधाजनक है, उदाहरण के लिए, उन तालिकाओं में जहां आपको नीचे के सेल में ऊपर से दूसरे सेल की सामग्री को प्रदर्शित करने की आवश्यकता होती है। ऐसे सूत्र को कई बार करने के बजाय, पंक्ति को ठीक करना और स्तंभ को बदलने देना पर्याप्त है।

3 विधि

यह पूरी तरह से पिछली विधि की तरह ही है, केवल आपको F4 कुंजी को तीन बार दबाने की आवश्यकता है। तब केवल स्तंभ का संदर्भ निरपेक्ष होगा, और पंक्ति स्थिर रहेगी।

4 विधि

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

बड़ी रेंज के लिए सेल पिन करना

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

दुर्भाग्य से, मानक एक्सेल विधियाँ इस लक्ष्य को प्राप्त नहीं करेंगी। ऐसा करने के लिए, आपको VBA-Excel नामक एक विशेष ऐडऑन का उपयोग करने की आवश्यकता है। इसमें कई अतिरिक्त विशेषताएं हैं जो आपको एक्सेल के साथ सामान्य कार्यों को बहुत तेजी से करने की अनुमति देती हैं।

इसमें सौ से अधिक उपयोगकर्ता-परिभाषित कार्य और 25 विभिन्न मैक्रो शामिल हैं, और इसे नियमित रूप से अपडेट भी किया जाता है। यह आपको लगभग किसी भी पहलू के साथ काम में सुधार करने की अनुमति देता है:

1. कोशिकाएं।
2. मैक्रो।
3. विभिन्न प्रकार के कार्य।
4. लिंक और सरणी।

विशेष रूप से, यह ऐड-इन आपको बड़ी संख्या में सूत्रों में लिंक को एक साथ ठीक करने की अनुमति देता है। ऐसा करने के लिए, आपको निम्न चरणों का पालन करना होगा:

1. एक श्रेणी चुनें.
2. स्थापना के बाद दिखाई देने वाला VBA-Excel टैब खोलें। 
3. "फ़ंक्शन" मेनू खोलें, जहाँ "लॉक सूत्र" विकल्प स्थित है।
   

   

4. अगला, एक संवाद बॉक्स दिखाई देगा जिसमें आपको आवश्यक पैरामीटर निर्दिष्ट करने की आवश्यकता है। यह ऐडऑन आपको एक कॉलम और एक कॉलम को अलग-अलग पिन करने की अनुमति देता है, और एक पैकेज के साथ पहले से मौजूद पिनिंग को भी हटाता है। संबंधित रेडियो बटन का उपयोग करके आवश्यक पैरामीटर का चयन करने के बाद, आपको "ओके" पर क्लिक करके अपने कार्यों की पुष्टि करनी होगी।

उदाहरण

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

हमारे उदाहरण में, इसके लिए आपको सूत्र दर्ज करना होगा =बी 2 * सी 2। यह काफी सरल है, जैसा कि आप देख सकते हैं। यह वर्णन करने के लिए उसके उदाहरण का उपयोग करना बहुत आसान है कि आप किसी सेल या उसके अलग-अलग कॉलम या पंक्ति के पते को कैसे ठीक कर सकते हैं। 

बेशक, इस उदाहरण में, आप ऑटोफिल मार्कर का उपयोग करके फॉर्मूला को नीचे खींचने की कोशिश कर सकते हैं, लेकिन इस मामले में, सेल अपने आप बदल जाएंगे। तो, सेल डी 3 में एक और सूत्र होगा, जहां संख्याओं को क्रमशः 3 से बदल दिया जाएगा। इसके अलावा, योजना के अनुसार - डी 4 - सूत्र रूप लेगा = बी 4 * सी 4, डी 5 - इसी तरह, लेकिन के साथ नंबर 5 और इतने पर।

यदि यह आवश्यक है (ज्यादातर मामलों में यह पता चला है), तो कोई समस्या नहीं है। लेकिन अगर आपको एक सेल में सूत्र को ठीक करने की आवश्यकता है ताकि खींचते समय यह न बदले, तो यह कुछ अधिक कठिन होगा। 

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

यहाँ यह हमारे उदाहरण में कैसा दिखता है।

यदि हम, पिछले संस्करण की तरह, एक सूत्र निर्धारित करने का प्रयास करते हैं, तो हम असफल होंगे। इसी तरह, सूत्र उपयुक्त में बदल जाएगा। हमारे उदाहरण में, यह इस प्रकार होगा: =ई3*बी8. यहाँ से हम देख सकते हैं। कि सूत्र का पहला भाग E3 में बदल गया है, और हमने स्वयं को यह कार्य निर्धारित किया है, लेकिन हमें सूत्र के दूसरे भाग को B8 में बदलने की आवश्यकता नहीं है। इसलिए, हमें संदर्भ को निरपेक्ष में बदलने की आवश्यकता है। आप इसे F4 कुंजी दबाए बिना, केवल एक डॉलर का चिह्न लगाकर कर सकते हैं।

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

सूत्र स्वयं इस तरह दिखेगा:

=डी2/$बी$7

सावधान! हमने दो डॉलर के संकेत दिए हैं। इस तरह, हम प्रोग्राम को दिखाते हैं कि कॉलम और रो दोनों को ठीक करने की जरूरत है।

मैक्रोज़ में सेल संदर्भ

मैक्रो एक सबरूटीन है जो आपको क्रियाओं को स्वचालित करने की अनुमति देता है। एक्सेल की मानक कार्यक्षमता के विपरीत, एक मैक्रो आपको तुरंत एक विशिष्ट सेल सेट करने और कोड की कुछ पंक्तियों में कुछ क्रियाएं करने की अनुमति देता है। सूचना के बैच प्रसंस्करण के लिए उपयोगी, उदाहरण के लिए, यदि ऐड-ऑन स्थापित करने का कोई तरीका नहीं है (उदाहरण के लिए, एक कंपनी कंप्यूटर का उपयोग किया जाता है, व्यक्तिगत नहीं)।

सबसे पहले आपको यह समझने की जरूरत है कि मैक्रो की मुख्य अवधारणा ऐसी वस्तुएं हैं जिनमें अन्य ऑब्जेक्ट हो सकते हैं। कार्यपुस्तिका ऑब्जेक्ट इलेक्ट्रॉनिक पुस्तक (अर्थात, दस्तावेज़) के लिए ज़िम्मेदार है। इसमें शीट्स ऑब्जेक्ट शामिल है, जो एक खुले दस्तावेज़ की सभी शीटों का संग्रह है। 

तदनुसार, कोशिकाएँ एक कोशिका वस्तु हैं। इसमें एक विशेष शीट के सभी सेल होते हैं।

प्रत्येक वस्तु कोष्ठकित तर्कों के साथ योग्य है। कोशिकाओं के मामले में, उन्हें इस क्रम में संदर्भित किया जाता है। पंक्ति संख्या को पहले सूचीबद्ध किया जाता है, उसके बाद स्तंभ संख्या या अक्षर (दोनों प्रारूप स्वीकार्य हैं)।

उदाहरण के लिए, सेल C5 के संदर्भ में कोड की एक पंक्ति इस तरह दिखेगी:

कार्यपुस्तिकाएं(“Book2.xlsm”)। शीट्स(“List2”)। सेल(5, 3)

कार्यपुस्तिकाएं ("Book2.xlsm")। पत्रक ("सूची 2")। कक्ष (5, "सी")

आप किसी ऑब्जेक्ट का उपयोग करके सेल तक भी पहुंच सकते हैं साफ। सामान्य तौर पर, इसका उद्देश्य किसी श्रेणी का संदर्भ देना होता है (जिसके तत्व, वैसे, पूर्ण या सापेक्ष भी हो सकते हैं), लेकिन आप बस एक सेल नाम दे सकते हैं, उसी प्रारूप में जैसे एक्सेल दस्तावेज़ में।

इस मामले में, रेखा इस तरह दिखेगी।

कार्यपुस्तिकाएं ("Book2.xlsm")। पत्रक ("सूची 2")। रेंज ("सी 5")

ऐसा लग सकता है कि यह विकल्प अधिक सुविधाजनक है, लेकिन पहले दो विकल्पों का लाभ यह है कि आप कोष्ठक में चर का उपयोग कर सकते हैं और एक लिंक दे सकते हैं जो अब निरपेक्ष नहीं है, लेकिन एक रिश्तेदार जैसा कुछ है, जो परिणामों पर निर्भर करेगा गणना।

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

निष्कर्ष

हमने पता लगाया कि सेल संदर्भ क्या है, यह कैसे काम करता है, इसके लिए क्या है। हमने निरपेक्ष और सापेक्ष संदर्भों के बीच के अंतर को समझा और यह पता लगाया कि एक प्रकार को दूसरे में बदलने के लिए क्या करने की आवश्यकता है (सरल शब्दों में, पते को ठीक करें या इसे अनपिन करें)। हमें पता चला कि आप इसे बड़ी संख्या में मूल्यों के साथ तुरंत कैसे कर सकते हैं। अब आपके पास इस सुविधा का सही परिस्थितियों में उपयोग करने की सुविधा है।