VBA में एक्सेल सेल बनाम रेंज फ़ंक्शंस को समझना

VBA में एक्सेल सेल बनाम रेंज फ़ंक्शंस को समझना

एक्सेल शक्तिशाली है। यदि आप इसका बहुत अधिक उपयोग करते हैं, तो आप शायद पहले से ही सूत्रों या ऑटोफ़ॉर्मेटिंग का उपयोग करके बहुत सी तरकीबें जानते हैं, लेकिन इसका उपयोग कर रहे हैं प्रकोष्ठों तथा श्रेणी VBA में फ़ंक्शन, आप अपने एक्सेल एनालिटिक्स को एक नए स्तर पर बढ़ा सकते हैं।





VBA में सेल और रेंज फ़ंक्शंस का उपयोग करने में समस्या यह है कि उन्नत स्तरों पर, अधिकांश लोगों को यह समझने में कठिनाई होती है कि ये फ़ंक्शन वास्तव में कैसे काम करते हैं। इनका उपयोग करना बहुत भ्रमित करने वाला हो सकता है। यहां बताया गया है कि आप उनका उपयोग उन तरीकों से कैसे कर सकते हैं जिनकी आपने शायद कभी कल्पना भी नहीं की थी।



सेल फंक्शन

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





प्रकोष्ठों आमतौर पर एक समय में एक ही सेल का संदर्भ देते हैं, जबकि श्रेणी कोशिकाओं के एक समूह को एक साथ संदर्भित करता है। इस फ़ंक्शन का प्रारूप है सेल (पंक्ति, कॉलम) .

यह पूरी शीट में हर एक सेल को संदर्भित करता है। यह एक उदाहरण है जहां सेल फ़ंक्शन किसी एकल कक्ष का संदर्भ नहीं देता है:



Worksheets('Sheet1').Cells

यह शीर्ष पंक्ति के बाईं ओर से तीसरे सेल को संदर्भित करता है। सेल C1:

Worksheets('Sheet1').Cells(3)

निम्नलिखित कोड सेल D15 को संदर्भित करता है:



Worksheets('Sheet1').Cells(15,4)

यदि आप चाहें, तो आप सेल D15 को 'Cells(15,'D')' के साथ भी संदर्भित कर सकते हैं --- आपको कॉलम अक्षर का उपयोग करने की अनुमति है।

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



रेंज फंक्शन

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

इस फ़ंक्शन का प्रारूप है रेंज (सेल # 1, सेल # 2) . प्रत्येक सेल को एक अक्षर-संख्या द्वारा निर्दिष्ट किया जा सकता है।

आइए कुछ उदाहरण देखें।

प्लेस्टेशन नेटवर्क पासवर्ड रीसेट काम नहीं कर रहा

यहाँ, रेंज फ़ंक्शन सेल A5 को संदर्भित कर रहा है:

Worksheets('Sheet1').Range('A5')

यहां, रेंज फ़ंक्शन ए 1 से ई 20 के बीच सभी कोशिकाओं को संदर्भित कर रहा है:

Worksheets('Sheet1').Range('A1:E20')

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


With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With

उपरोक्त कोड उसी श्रेणी को संदर्भित करता है जैसा कि रेंज ('ए 1: ई 20') फ़ंक्शन करता है। इसका उपयोग करने में मूल्य यह है कि यह आपको कोड लिखने की अनुमति देगा जो गतिशील रूप से लूप का उपयोग करके श्रेणियों के साथ काम करता है।

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

सेल फ़ंक्शन के साथ प्रोसेसिंग डेटा

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

आइए एक साधारण उदाहरण लेते हैं। मान लें कि आप 11 लोगों की बिक्री टीम का प्रबंधन करते हैं, और हर महीने आप उनके प्रदर्शन को देखना चाहते हैं।

आपके पास हो सकता है पत्रक 1 जो उनकी बिक्री की संख्या और उनकी बिक्री की मात्रा को ट्रैक करता है।

पर पत्रक2 वह जगह है जहां आप अपनी कंपनी के ग्राहकों से पिछले 30 दिनों के लिए उनकी प्रतिक्रिया रेटिंग ट्रैक करते हैं।

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

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

तो क्यों न उस समय उसी स्क्रिप्ट में बोनस कॉलम के लिए गणना को ट्रिगर किया जाए?

सेल फंक्शन इन एक्शन

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

अब, जब आप क्लिक करें ठीक है और मुख्य शीट पर वापस जाएं, आपको डेवलपर मेनू विकल्प दिखाई देगा।

आप का उपयोग कर सकते हैं डालने कमांड बटन डालने के लिए मेनू, या बस क्लिक करें कोड देखें कोडिंग शुरू करने के लिए।

इस उदाहरण में हम हर बार कार्यपुस्तिका खोले जाने पर स्क्रिप्ट चलाएंगे। ऐसा करने के लिए, बस क्लिक करें कोड देखें डेवलपर मेनू से, और निम्न नए फ़ंक्शन को कोड विंडो में पेस्ट करें।

Private Sub Workbook_Open()
End Sub

आपकी कोड विंडो कुछ इस तरह दिखेगी।

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

याद रखें कि सेल फ़ंक्शन में प्रत्येक व्यक्तिगत सेल की पहचान करने के लिए पैरामीटर के रूप में पंक्ति और स्तंभ होते हैं। हम पंक्ति को 'x' बनाएंगे, प्रत्येक कॉलम के डेटा का अनुरोध करने के लिए एक संख्या का उपयोग करेंगे। पंक्तियों की संख्या कर्मचारियों की संख्या है, इसलिए यह 1 से 11 तक होगी। कॉलम पहचानकर्ता बिक्री गणना के लिए 2, बिक्री मात्रा के लिए 3 और फीडबैक स्कोर के लिए शीट 2 से 2 होगा।

अंतिम गणना कुल बोनस स्कोर के 100 प्रतिशत तक जोड़ने के लिए निम्नलिखित प्रतिशत का उपयोग करती है। यह एक आदर्श बिक्री गणना 50, बिक्री मात्रा ,000 और फीडबैक स्कोर 10 पर आधारित है।

  • (बिक्री गणना/50) x 0.4
  • (बिक्री मात्रा/50,000) x 0.5
  • (फीडबैक स्कोर/10) x 0.1

यह सरल दृष्टिकोण बिक्री कर्मचारियों को एक भारित बोनस देता है। ५० की गिनती के लिए, ५०,००० डॉलर की मात्रा, और १० के स्कोर के लिए --- उन्हें महीने के लिए संपूर्ण अधिकतम बोनस मिलता है। हालांकि किसी भी कारक पर सही के तहत कुछ भी बोनस को कम करता है। आदर्श से बेहतर कुछ भी बोनस को बढ़ाता है।

अब देखते हैं कि कैसे एक बहुत ही सरल, संक्षिप्त VBA स्क्रिप्ट में उस सारे तर्क को निकाला जा सकता है:

Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub

इस स्क्रिप्ट का आउटपुट इस तरह दिखेगा।

बिना अकाउंट के इंस्टाग्राम कैसे देखें

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

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

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

आगे आप जो कुछ भी कर सकते हैं, उसका पता लगाने के लिए, देखें माइक्रोसॉफ्ट एमएसडीएन पेज सेल ऑब्जेक्ट के लिए।

रेंज फंक्शन के साथ फ़ॉर्मेटिंग सेल

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

इसके लिए एक उपयोग मामला स्क्रिप्ट का उपयोग करके कोशिकाओं की एक श्रृंखला को प्रारूपित करना हो सकता है, यदि कुछ शर्तें पूरी होती हैं।

उदाहरण के लिए, मान लें कि यदि सभी बिक्री कर्मचारियों में बिक्री की मात्रा कुल मिलाकर 0,000 से अधिक है, तो आप यह दर्शाने के लिए कि टीम ने एक अतिरिक्त टीम बोनस अर्जित किया है, आप बोनस कॉलम में सभी सेल को हरे रंग में हाइलाइट करना चाहते हैं।

आइए देखें कि आप इसके साथ कैसे कर सकते हैं एक आईएफ स्टेटमेंट .

Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Sub

जब यह चलता है, यदि सेल टीम के लक्ष्य के ऊपर है, तो श्रेणी के सभी सेल हरे रंग से भर जाएंगे।

यह रेंज फ़ंक्शन का उपयोग करके कोशिकाओं के समूहों पर आपके द्वारा की जाने वाली कई क्रियाओं का सिर्फ एक सरल उदाहरण है। अन्य चीजें जो आप कर सकते हैं उनमें शामिल हैं:

  • समूह के चारों ओर एक रूपरेखा लागू करें
  • कक्षों की श्रेणी के अंदर पाठ की वर्तनी की जाँच करें
  • सेल साफ़ करें, कॉपी करें या काटें
  • 'ढूंढें' विधि के साथ एक श्रेणी के माध्यम से खोजें
  • बहुत अधिक

पढ़ना सुनिश्चित करें माइक्रोसॉफ्ट एमएसडीएन पेज सभी संभावनाओं को देखने के लिए रेंज ऑब्जेक्ट के लिए।

एक्सेल को नेक्स्ट लेवल पर ले जाएं

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

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

साझा करना साझा करना कलरव ईमेल कैनन बनाम निकॉन: कौन सा कैमरा ब्रांड बेहतर है?

कैनन और निकॉन कैमरा इंडस्ट्री के दो सबसे बड़े नाम हैं। लेकिन कौन सा ब्रांड कैमरा और लेंस की बेहतर लाइनअप प्रदान करता है?

आगे पढ़िए संबंधित विषय
  • उत्पादकता
  • प्रोग्रामिंग
  • विजुअल बेसिक प्रोग्रामिंग
  • माइक्रोसॉफ्ट एक्सेल
लेखक के बारे में रयान दुबे(९४२ लेख प्रकाशित)

रयान के पास इलेक्ट्रिकल इंजीनियरिंग में बीएससी की डिग्री है। उन्होंने ऑटोमेशन इंजीनियरिंग में १३ साल, आईटी में ५ साल काम किया है, और अब एक ऐप इंजीनियर हैं। MakeUseOf के एक पूर्व प्रबंध संपादक, उन्होंने डेटा विज़ुअलाइज़ेशन पर राष्ट्रीय सम्मेलनों में बात की है और उन्हें राष्ट्रीय टीवी और रेडियो पर चित्रित किया गया है।

रयान दुबे की अन्य फ़िल्में-टीवी शो

हमारे न्यूज़लेटर की सदस्यता लें

तकनीकी युक्तियों, समीक्षाओं, निःशुल्क ई-पुस्तकों और अनन्य सौदों के लिए हमारे न्यूज़लेटर से जुड़ें!

सब्सक्राइब करने के लिए यहां क्लिक करें