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

पृष्ठभूमि रंग के आधार पर कोशिकाओं की गणना और योग करें

• उपयोगकर्ता परिभाषित फ़ंक्शन का उपयोग करके
• एक शक्तिशाली सुविधा का उपयोग करके - एक्सेल के लिए कुटूल
• फ़िल्टर और सबटोटल का उपयोग करके

फ़ॉन्ट रंग के आधार पर कोशिकाओं की गणना और योग करें

• उपयोगकर्ता परिभाषित फ़ंक्शन का उपयोग करके
• एक आसान सुविधा का उपयोग करके - एक्सेल के लिए कुटूल

सशर्त स्वरूपण रंग के आधार पर कोशिकाओं की गणना और योग करें

• VBA कोड का उपयोग करके
• एक स्मार्ट सुविधा का उपयोग करके - एक्सेल के लिए कुटूल

वीडियो: रंग के आधार पर कोशिकाओं की गणना और योग करें

पृष्ठभूमि रंग के आधार पर कोशिकाओं की गणना और योग करें

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

उपयोगकर्ता परिभाषित फ़ंक्शन के साथ पृष्ठभूमि रंग द्वारा कोशिकाओं की गणना और योग करें

यहां, हम आपको दिखाएंगे कि एक्सेल में इस कार्य को हल करने के लिए ऐसे उपयोगकर्ता परिभाषित फ़ंक्शन को कैसे बनाएं और उपयोग करें। कृपया निम्नलिखित चरणों का पालन करें:

चरण 1: वीबीए मॉड्यूल संपादक खोलें और कोड कॉपी करें

1. दबाएँ ऑल्ट + F11 कुंजी को खोलने के लिए अनुप्रयोगों के लिए माइक्रोसॉफ्ट विज़ुअल बेसिक खिड़की.
2. खुली हुई विंडो में क्लिक करें सम्मिलित करें > मॉड्यूल एक नया रिक्त मॉड्यूल बनाने के लिए।
3. फिर, नीचे दिए गए कोड को कॉपी करके रिक्त मॉड्यूल में पेस्ट करें।
   वीबीए कोड: पृष्ठभूमि रंग के आधार पर कोशिकाओं की गणना और योग करें

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

चरण 2: पृष्ठभूमि रंग के आधार पर कोशिकाओं की गिनती और योग करने के लिए सूत्र बनाएं

उपरोक्त कोड चिपकाने के बाद, मॉड्यूल विंडो बंद करें, फिर निम्नलिखित सूत्र लागू करें:

• विशिष्ट पृष्ठभूमि रंग के आधार पर कोशिकाओं की गणना करें:
  परिणाम के लिए नीचे दिए गए फॉर्मूले को अपने इच्छित सेल में कॉपी या टाइप करें। फिर, अन्य परिणाम प्राप्त करने के लिए भरण हैंडल को नीचे की ओर खींचें। स्क्रीनशॉट देखें:
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  नोट: इस सूत्र में, G2 वह विशिष्ट पृष्ठभूमि रंग वाला संदर्भ कक्ष है जिसका आप मिलान करना चाहते हैं; $B$2:$E$12 वह सीमा है जहां आप G2 के रंग की कोशिकाओं की संख्या गिनना चाहते हैं; असत्य मेल खाते रंग वाली कोशिकाओं की गिनती के लिए उपयोग किया जाता है।
  
• विशिष्ट पृष्ठभूमि रंग के आधार पर कोशिकाओं का योग:
  परिणाम के लिए नीचे दिए गए फॉर्मूले को अपने इच्छित सेल में कॉपी या टाइप करें। फिर, अन्य परिणाम प्राप्त करने के लिए भरण हैंडल को नीचे की ओर खींचें। स्क्रीनशॉट देखें:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  नोट: इस सूत्र में, G2 वह विशिष्ट पृष्ठभूमि रंग वाला संदर्भ कक्ष है जिसका आप मिलान करना चाहते हैं; $B$2:$E$12 वह सीमा है जहां आप G2 के रंग की कोशिकाओं की संख्या गिनना चाहते हैं; जब सही है मेल खाते रंग के साथ कोशिकाओं का योग करने के लिए उपयोग किया जाता है।
  

एक शक्तिशाली सुविधा के साथ पृष्ठभूमि रंग के आधार पर कोशिकाओं की गिनती और योग करें

प्रोग्रामिंग से अपरिचित लोगों के लिए, VBA काफी जटिल लग सकता है। यहां, हम एक शक्तिशाली उपकरण पेश करेंगे - एक्सेल के लिए कुटूल, आईटी इस रंग के अनुसार गिनती करें सुविधा आपको कुछ ही क्लिक में पृष्ठभूमि रंग के आधार पर आसानी से गणना (गणना, योग, औसत, आदि) करने की अनुमति देती है। प्रभावशाली ढंग से, रंग के अनुसार गिनती करें सुविधा केवल पृष्ठभूमि रंगों से परे है - यह फ़ॉन्ट रंगों और सशर्त स्वरूपण के आधार पर अंतर और गणना भी कर सकती है।

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

में रंग के अनुसार गिनती करें संवाद बॉक्स, कृपया संचालन निर्दिष्ट करें:

1. चुनते हैं मानक स्वरूपण से रंग विधि ड्रॉप डाउन सूची;
2. निर्दिष्ट करें पृष्ठभूमि से गणना प्रकार ड्रॉप डाउन सूची, और आप संवाद बॉक्स में प्रत्येक पृष्ठभूमि रंग के लिए सांख्यिकीय परिणामों का पूर्वावलोकन कर सकते हैं;
3. अंत में, क्लिक करें रिपोर्ट बनाओ परिकलित परिणामों को एक नई कार्यपुस्तिका में निर्यात करने के लिए।

रिजल्ट:

अब आपको आंकड़ों के साथ एक नई वर्कबुक मिलेगी। स्क्रीनशॉट देखें:

फ़िल्टर और सबटोटल फ़ंक्शन के साथ पृष्ठभूमि रंग के आधार पर कोशिकाओं की गणना और योग करें

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

चरण 1: सबटोटल फ़ंक्शन लागू करें

सबटोटल फ़ंक्शन में प्रवेश करने के लिए रिक्त कक्षों का चयन करें।

• समान पृष्ठभूमि रंग वाले सभी कक्षों की गणना करने के लिए, कृपया सूत्र दर्ज करें:

  =SUBTOTAL(102, F2:F16)

• सभी कक्षों को समान पृष्ठभूमि रंग के साथ जोड़ने के लिए, कृपया सूत्र दर्ज करें;

  =SUBTOTAL(109, F2:F16)

• नोट: उपरोक्त सूत्रों में, 102 छिपी हुई कोशिकाओं को बाहर करते हुए फ़िल्टर की गई सूची में संख्यात्मक मानों को गिनने का प्रतिनिधित्व करता है; 109 छिपी हुई कोशिकाओं को छोड़कर फ़िल्टर की गई सूची में मानों का योग प्रस्तुत करता है; एफ2:एफ16 वह सीमा है जिस पर गिनती या योग की गणना की जाएगी।
• 

चरण 2: विशिष्ट रंग के आधार पर कोशिकाओं को फ़िल्टर करें

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

रिजल्ट:

फ़िल्टर करने के बाद, SUBTOTAL सूत्र स्वचालित रूप से रंगीन कोशिकाओं की गणना और योग करते हैं मूल्य स्तंभ। स्क्रीनशॉट देखें:

फ़ॉन्ट रंग के आधार पर कोशिकाओं की गणना और योग करें

क्या आप एक्सेल में सेलों को उनके फ़ॉन्ट रंग के आधार पर गिनना या उनका योग करना चाहते हैं? मान लीजिए कि आपके पास डेटा है, जैसे दिए गए स्क्रीनशॉट में, सेल में लाल, नीले, नारंगी और काले रंग के टेक्स्ट हैं। एक्सेल डिफ़ॉल्ट रूप से इसे आसान नहीं बनाता है। लेकिन घबराना नहीं! इस अनुभाग में, हम आपको ऐसा करने के लिए कुछ सरल तरकीबें दिखाएंगे।

उपयोगकर्ता परिभाषित फ़ंक्शन के साथ फ़ॉन्ट रंग के आधार पर कोशिकाओं की गणना और योग करें

विशिष्ट फ़ॉन्ट रंगों के साथ कोशिकाओं की गिनती और योग करने के लिए, निम्नलिखित उपयोगकर्ता परिभाषित फ़ंक्शन आपको इस कार्य को हल करने में मदद कर सकता है। कृपया निम्नलिखित चरणों का पालन करें:

चरण 1: वीबीए मॉड्यूल संपादक खोलें और कोड कॉपी करें

1. दबाएँ ऑल्ट + F11 कुंजी को खोलने के लिए अनुप्रयोगों के लिए माइक्रोसॉफ्ट विज़ुअल बेसिक खिड़की.
2. खुली हुई विंडो में क्लिक करें सम्मिलित करें > मॉड्यूल एक नया रिक्त मॉड्यूल बनाने के लिए।
3. फिर, नीचे दिए गए कोड को कॉपी करके रिक्त मॉड्यूल में पेस्ट करें।
   वीबीए कोड: फ़ॉन्ट रंग के आधार पर कोशिकाओं की गणना और योग करें

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

चरण 2: फ़ॉन्ट रंग के आधार पर कोशिकाओं की गिनती और योग करने के लिए सूत्र बनाएं

उपरोक्त कोड चिपकाने के बाद, मॉड्यूल विंडो बंद करें, फिर निम्नलिखित सूत्र लागू करें:

• विशिष्ट फ़ॉन्ट रंग के आधार पर कोशिकाओं की गणना करें:
  परिणाम के लिए नीचे दिए गए फॉर्मूले को अपने इच्छित सेल में कॉपी या टाइप करें। फिर, अन्य परिणाम प्राप्त करने के लिए भरण हैंडल को नीचे की ओर खींचें। स्क्रीनशॉट देखें:
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  नोट: इस सूत्र में, G2 वह विशिष्ट फ़ॉन्ट रंग वाला संदर्भ कक्ष है जिसका आप मिलान करना चाहते हैं; $B$2:$E$12 वह सीमा है जहां आप G2 के रंग की कोशिकाओं की संख्या गिनना चाहते हैं।
  
• विशिष्ट फ़ॉन्ट रंग के आधार पर कोशिकाओं का योग:
  परिणाम के लिए नीचे दिए गए फॉर्मूले को अपने इच्छित सेल में कॉपी या टाइप करें। फिर, अन्य परिणाम प्राप्त करने के लिए भरण हैंडल को नीचे की ओर खींचें। स्क्रीनशॉट देखें:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  नोट: इस सूत्र में, G2 वह विशिष्ट फ़ॉन्ट रंग वाला संदर्भ कक्ष है जिसका आप मिलान करना चाहते हैं; $B$2:$E$12 वह सीमा है जहां आप G2 के रंग की कोशिकाओं की संख्या गिनना चाहते हैं।
  

एक आसान सुविधा के साथ फ़ॉन्ट रंग के आधार पर कोशिकाओं की गणना और योग करें

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

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

में रंग के अनुसार गिनती करें संवाद बॉक्स, कृपया संचालन निर्दिष्ट करें:

1. चुनते हैं मानक स्वरूपण से रंग विधि ड्रॉप डाउन सूची;
2. निर्दिष्ट करें फॉन्ट से गणना प्रकार ड्रॉप डाउन सूची, और आप संवाद बॉक्स में प्रत्येक फ़ॉन्ट रंग के लिए सांख्यिकीय परिणामों का पूर्वावलोकन कर सकते हैं;
3. अंत में, क्लिक करें रिपोर्ट बनाओ परिकलित परिणामों को एक नई कार्यपुस्तिका में निर्यात करने के लिए।

रिजल्ट:

अब, आपके पास फ़ॉन्ट रंग के आधार पर विस्तृत आँकड़े प्रदर्शित करने वाली एक नई कार्यपुस्तिका है। स्क्रीनशॉट देखें:

सशर्त स्वरूपण रंग के आधार पर कोशिकाओं की गणना और योग करें

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

वीबीए कोड के साथ सशर्त रूप से स्वरूपित कोशिकाओं की गणना और योग करें

एक्सेल में सशर्त रूप से स्वरूपित कोशिकाओं की गिनती और सारांश अंतर्निहित कार्यों का उपयोग करना आसान नहीं है। हालाँकि, आप VBA कोड का उपयोग करके इस कार्य को पूरा कर सकते हैं। आइए देखें कि आप इसके लिए वीबीए का उपयोग कैसे कर सकते हैं:

चरण 1: वीबीए मॉड्यूल संपादक खोलें और कोड कॉपी करें

1. दबाएँ ऑल्ट + F11 कुंजी को खोलने के लिए अनुप्रयोगों के लिए माइक्रोसॉफ्ट विज़ुअल बेसिक खिड़की.
2. खुली हुई विंडो में क्लिक करें सम्मिलित करें > मॉड्यूल एक नया रिक्त मॉड्यूल बनाने के लिए।
3. फिर, नीचे दिए गए कोड को कॉपी करके रिक्त मॉड्यूल में पेस्ट करें।
   वीबीए कोड: सशर्त स्वरूपण रंग के आधार पर कोशिकाओं की गणना और योग करें

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

चरण 2: इस VBA कोड को निष्पादित करें

1. कोड पेस्ट करने के बाद प्रेस करें F5 इस कोड को चलाने के लिए कुंजी, एक प्रॉम्प्ट बॉक्स दिखाई देगा, कृपया उस डेटा श्रेणी का चयन करें जहां आप सशर्त स्वरूपण के आधार पर कोशिकाओं की गणना और योग करना चाहते हैं। तब दबायें OK, स्क्रीनशॉट देखें:
   
2. दूसरे प्रॉम्प्ट बॉक्स में, एक विशिष्ट सशर्त स्वरूपण रंग चुनें जिसे आप गिनना और जोड़ना चाहते हैं, और क्लिक करें OK बटन, स्क्रीनशॉट देखें:
   

रिजल्ट:

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

एक स्मार्ट सुविधा के साथ सशर्त रूप से स्वरूपित कोशिकाओं की गणना और योग करें

यदि आप सशर्त रूप से स्वरूपित कोशिकाओं को गिनने और उनका योग करने के लिए अन्य त्वरित और आसान तरीकों की तलाश कर रहे हैं, एक्सेल के लिए कुटूल आपका समाधान है. इसका रंग के अनुसार गिनती करें यह सुविधा इस कार्य को कुछ ही क्लिक में हल कर सकती है। कुटूल आपके वर्कफ़्लो में जो दक्षता और परिशुद्धता ला सकते हैं, उसकी खोज करें।

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

में रंग के अनुसार गिनती करें संवाद बॉक्स, कृपया संचालन निर्दिष्ट करें:

1. चुनते हैं सशर्त फॉर्मेटिंग से रंग विधि ड्रॉप डाउन सूची;
2. निर्दिष्ट करें पृष्ठभूमि से गणना प्रकार ड्रॉप डाउन सूची, और आप संवाद बॉक्स में प्रत्येक कंडीशनला फ़ॉर्मेटिंग रंग के लिए सांख्यिकीय परिणामों का पूर्वावलोकन कर सकते हैं;
3. अंत में, क्लिक करें रिपोर्ट बनाओ परिकलित परिणामों को एक नई कार्यपुस्तिका में निर्यात करने के लिए।

रिजल्ट:

अब, आपके पास सशर्त स्वरूपण रंग के आधार पर विस्तृत आँकड़े प्रदर्शित करने वाली एक नई कार्यपुस्तिका है। स्क्रीनशॉट देखें:

संबंधित आलेख: