Excel İpuçları: Renge göre hücreleri sayma/toplama (arka plan, yazı tipi, koşullu biçimlendirme)

Günlük görevlerde, renk işaretlemesi, önemli verileri hızlıca ayırt etmek ve vurgulamak için popüler bir yöntemdir. Ancak, belirli bir renge (dolgu rengi, yazı tipi rengi, koşullu biçimlendirme) göre hücre verilerini nasıl sayar veya toplarız? Varsayılan olarak, Excel, renge göre sayma veya toplama için doğrudan bir özellik sunmaz. Yine de, bazı püf noktaları ve dolaylı yöntemlerle bunu hala başarabiliriz. Bu makale, verileri renge göre nasıl sayabileceğimizi veya toplayabileceğimizi inceleyecektir.

Arka plan rengine göre hücreleri sayma ve toplama

• Kullanıcı Tanımlı İşlev kullanarak
• Güçlü bir özellik olan Kutools for Excel'i kullanarak
• Filtre ve ALTTOPLAM kullanarak

Yazı tipi rengine göre hücreleri sayma ve toplama

• Kullanıcı Tanımlı İşlev kullanarak
• Kolay bir özellik olan Kutools for Excel'i kullanarak

Koşullu biçimlendirmeye göre hücreleri sayma ve toplama

• VBA kodu kullanarak
• Akıllı bir özellik olan Kutools for Excel'i kullanarak

Video: Renge göre hücreleri sayma ve toplama

Arka plan rengine göre hücreleri sayma ve toplama

Örneğin, aşağıda gösterilen ekran görüntüsünde olduğu gibi farklı arka plan renkleriyle doldurulmuş bir veri aralığınız varsa. Belirli bir renge göre hücreleri saymak veya toplamak için, Excel'in arka plan rengine göre hücreleri saymak veya toplamak için doğrudan bir özelliği bulunmamaktadır. Ancak, biraz yaratıcılık ve bazı pratik tekniklerle bu görevi gerçekleştirebilirsiniz. Bu bölümde bazı yararlı yöntemleri keşfedelim.

Kullanıcı Tanımlı İşlev ile arka plan rengine göre hücreleri sayma ve toplama

Burada, size bu görevi Excel'de çözmek için böyle bir Kullanıcı Tanımlı İşlev oluşturmayı ve kullanmayı göstereceğiz. Lütfen aşağıdaki adımları izleyin:

Adım 1: VBA modül düzenleyiciyi açın ve kodu kopyalayın

1. Microsoft Visual Basic for Applications penceresini açmak için Alt + F11 tuşlarına basın.
2. Açılan pencerede, yeni boş bir modül oluşturmak için Ekle > Modül'e tıklayın.
3. Ardından, aşağıdaki kodu boş modüle kopyalayıp yapıştırın.
   VBA kodu: Arka plan rengine göre hücreleri sayma ve toplama

   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
   

Adım 2: Arka plan rengine göre hücreleri saymak ve toplamak için formüller oluşturun

Yukarıdaki kodu yapıştırdıktan sonra, modül penceresini kapatın ve ardından aşağıdaki formülleri uygulayın:

• Belirli bir arka plan rengine göre hücreleri sayma:
  Sonuç için istediğiniz hücreye aşağıda verilen formülü kopyalayın veya yazın. Ardından, diğer sonuçları almak için doldurma tutamacını aşağı doğru sürükleyin. Ekran görüntüsüne bakın:
  

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

  Not: Bu formülde, G2, eşleştirmek istediğiniz belirli arka plan rengine sahip referans hücredir; $B$2:$E$12, G2'nin rengindeki hücre sayısını saymak istediğiniz aralıktır; FALSE, eşleşen renge sahip hücreleri saymak için kullanılır.
  
• Belirli bir arka plan rengine göre hücreleri toplama:
  Sonuç için istediğiniz hücreye aşağıda verilen formülü kopyalayın veya yazın. Ardından, diğer sonuçları almak için doldurma tutamacını aşağı doğru sürükleyin. Ekran görüntüsüne bakın:
  

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

  Not: Bu formülde, G2, eşleştirmek istediğiniz belirli arka plan rengine sahip referans hücredir; $B$2:$E$12, G2'nin rengindeki hücre sayısını saymak istediğiniz aralıktır; TRUE, eşleşen renge sahip hücreleri toplamak için kullanılır.
  

Güçlü bir özellik ile arka plan rengine göre hücreleri sayma ve toplama

Programlamaya aşina olmayanlar için VBA oldukça karmaşık görünebilir. Burada, güçlü bir araç olan Kutools for Excel'i tanıtacağız. Count by Color özelliği, birkaç tıklamayla arka plan rengine göre hesaplama yapmanızı (sayma, toplama, ortalama alma vb.) sağlar. Etkileyici bir şekilde, Count by Color özelliği yalnızca arka plan renkleriyle sınırlı kalmaz – aynı zamanda yazı tipi renklerine ve koşullu biçimlendirmeye göre de ayırt edebilir ve hesaplayabilir.

Kutools for Excel'i indirip yükledikten sonra, önce belirli bir arka plan rengine göre saymak veya toplamak istediğiniz veri aralığını seçin. Ardından, Kutools Plus'a gidin ve Count by Color'ı seçin.

Count by Color iletişim kutusunda, lütfen işlemleri belirtin:

1. Renk yöntemi açılır listesinden Standart Biçimlendirmeyi seçin;
2. Sayma türü açılır listesinden Arka Plan'ı belirtin ve her bir arka plan rengi için istatistiksel sonuçları diyalog kutusunda önizleyebilirsiniz;
3. Son olarak, hesaplanan sonuçları yeni bir çalışma kitabına aktarmak için Rapor Oluştur'a tıklayın.

Sonuç:

Şimdi istatistiklerle birlikte yeni bir çalışma kitabınız olacaktır. Ekran görüntüsüne bakın:

Filtre ve ALTTOPLAM işlevi ile arka plan rengine göre hücreleri sayma ve toplama

Diyelim ki aşağıdaki ekran görüntüsünde gösterildiği gibi bir meyve satış tablomuz var ve biz miktar sütunundaki renkli hücreleri sayacağız veya toplayacağız. Miktar sütunu.

Adım 1: ALTTOPLAM işlevini uygulayın

ALTTOPLAM işlevini girmek için boş hücreleri seçin.

• Aynı arka plan rengine sahip tüm hücreleri saymak için lütfen şu formülü girin:

  =SUBTOTAL(102, F2:F16)

• Aynı arka plan rengine sahip tüm hücreleri toplamak için lütfen şu formülü girin:

  =SUBTOTAL(109, F2:F16)

• Not: Yukarıdaki formüllerde, 102, gizli hücreler hariç filtrelenmiş listedeki sayısal değerleri saymak için kullanılırken; 109, gizli hücreler hariç filtrelenmiş listedeki değerleri toplamak için kullanılır; F2:F16, ya sayım ya da toplama yapılacağı aralıktır.
• 

Adım 2: Belirli bir renge göre hücreleri filtreleyin

1. Tablonun başlığını seçin ve Veri > Filtre'ye tıklayın. Ekran görüntüsüne bakın:
   
2. Tıklayın Filtre ikonu  başlık hücresindeki Miktar sütununda ve Renk'e göre Filtrele ardından sırayla sayacağınız belirli rengi seçin. Ekran görüntüsüne bakın:
   

Sonuç:

Filtrelemeden sonra, ALTTOPLAM formülleri otomatik olarak Miktar sütunundaki renkli hücreleri sayar ve toplar. Miktar sütunu. Ekran görüntüsüne bakın:

Yazı tipi rengine göre hücreleri sayma ve toplama

Excel'de hücreleri yazı tipi rengine göre saymak veya toplamak istiyor musunuz? Diyelim ki verileriniz var, verilen ekran görüntüsünde olduğu gibi, hücrelerde kırmızı, mavi, turuncu ve siyah renkte metinler içeriyor. Excel bu işlemi varsayılan olarak kolaylaştırmaz. Ama endişelenmeyin! Bu bölümde, tam olarak bunu yapmak için size bazı basit püf noktaları göstereceğiz.

Kullanıcı Tanımlı İşlev ile yazı tipi rengine göre hücreleri sayma ve toplama

Belirli yazı tipi renklerine sahip hücreleri saymak ve toplamak için aşağıdaki Kullanıcı Tanımlı İşlev bu görevi çözmenize yardımcı olabilir. Lütfen aşağıdaki adımları izleyin:

Adım 1: VBA modül düzenleyiciyi açın ve kodu kopyalayın

1. Microsoft Visual Basic for Applications penceresini açmak için Alt + F11 tuşlarına basın.
2. Açılan pencerede, yeni boş bir modül oluşturmak için Ekle > Modül'e tıklayın.
3. Ardından, aşağıdaki kodu boş modüle kopyalayıp yapıştırın.
   VBA kodu: Yazı tipi rengine göre hücreleri sayma ve toplama

   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
   

Adım 2: Yazı tipi rengine göre hücreleri saymak ve toplamak için formüller oluşturun

Yukarıdaki kodu yapıştırdıktan sonra, modül penceresini kapatın ve ardından aşağıdaki formülleri uygulayın:

• Belirli bir yazı tipi rengine göre hücreleri sayma:
  Sonuç için istediğiniz hücreye aşağıda verilen formülü kopyalayın veya yazın. Ardından, diğer sonuçları almak için doldurma tutamacını aşağı doğru sürükleyin. Ekran görüntüsüne bakın:
  

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

  Not: Bu formülde, G2, eşleştirmek istediğiniz belirli yazı tipi rengine sahip referans hücredir; $B$2:$E$12, G2'nin rengindeki hücre sayısını saymak istediğiniz aralıktır.
  
• Belirli bir yazı tipi rengine göre hücreleri toplama:
  Sonuç için istediğiniz hücreye aşağıda verilen formülü kopyalayın veya yazın. Ardından, diğer sonuçları almak için doldurma tutamacını aşağı doğru sürükleyin. Ekran görüntüsüne bakın:
  

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

  Not: Bu formülde, G2, eşleştirmek istediğiniz belirli yazı tipi rengine sahip referans hücredir; $B$2:$E$12, G2'nin rengindeki hücre sayısını saymak istediğiniz aralıktır.
  

Kolay bir özellik ile yazı tipi rengine göre hücreleri sayma ve toplama

Excel'de yazı tipi rengine göre hücre değerlerini kolayca saymak veya toplamak mı istiyorsunuz? Kutools for Excel'in Count by Color özelliğini keşfedin! Bu akıllı araçla, belirli bir yazı tipi rengine göre hücreleri saymak ve toplamak çok kolaylaşır. Kutools'un Excel deneyiminizi nasıl dönüştürebileceğini keşfedin.

Kutools for Excel'i indirip yükledikten sonra, önce belirli bir yazı tipi rengine göre saymak veya toplamak istediğiniz veri aralığını seçin. Ardından, Kutools Plus > Count by Color'a tıklayarak Count by Color iletişim kutusunu açın.

Count by Color iletişim kutusunda, lütfen işlemleri belirtin:

1. Renk yöntemi açılır listesinden Standart Biçimlendirmeyi seçin;
2. Sayma türü açılır listesinden Yazı Tipi'ni belirtin ve her bir yazı tipi rengi için istatistiksel sonuçları diyalog kutusunda önizleyebilirsiniz;
3. Son olarak, hesaplanan sonuçları yeni bir çalışma kitabına aktarmak için Rapor Oluştur'a tıklayın.

Sonuç:

Şimdi, yazı tipi rengine göre ayrıntılı istatistikler içeren yeni bir çalışma kitabınız olacaktır. Ekran görüntüsüne bakın:

Koşullu biçimlendirme rengine göre hücreleri sayma ve toplama

Excel'de, belirli ölçütleri karşılayan hücrelere belirli bir renk uygulamak için Koşullu Biçimlendirmeyi yaygın olarak kullanabilirsiniz, bu da veri görselleştirmesini sezgisel hale getirir. Ancak, bu özel biçimlendirilmiş hücreleri saymanız veya toplamanız gerekiyorsa ne yaparsınız? Excel bu işlem için doğrudan bir yol sunmasa da, bu sınırlamayı aşmak için yöntemler bulunmaktadır.

VBA kodu ile koşullu biçimlendirilmiş hücreleri sayma ve toplama

Excel'de koşullu biçimlendirilmiş hücreleri sayma ve toplama, yerleşik işlevler kullanılarak kolay değildir. Ancak, bu görevi VBA kodu kullanarak gerçekleştirebilirsiniz. Şimdi size VBA'yı nasıl kullanabileceğinizi göstereceğiz:

Adım 1: VBA modül düzenleyiciyi açın ve kodu kopyalayın

1. Microsoft Visual Basic for Applications penceresini açmak için Alt + F11 tuşlarına basın.
2. Açılan pencerede, yeni boş bir modül oluşturmak için Ekle > Modül'e tıklayın.
3. Ardından, aşağıdaki kodu boş modüle kopyalayıp yapıştırın.
   VBA kodu: Koşullu biçimlendirme rengine göre hücreleri sayma ve toplama

   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
   

Adım 2: Bu VBA kodunu çalıştırın

1. Kodu yapıştırdıktan sonra, bu kodu çalıştırmak için F5 tuşuna basın, bir uyarı kutusu açılacak, lütfen koşullu biçimlendirmeye göre hücreleri saymak ve toplamak istediğiniz veri aralığını seçin. Ardından Tamam, Ekran görüntüsüne bakın:
   
2. Başka bir uyarı kutusunda, saymak ve toplamak istediğiniz belirli bir koşullu biçimlendirme rengini seçin ve Tamam düğmesine tıklayın, ekran görüntüsüne bakın:
   

Sonuç:

Şimdi, belirtilen koşullu biçimlendirme rengine sahip hücrelerin hem sayımı hem de toplamını içeren sonuç, açılan kutuda görüntülenecektir. Ekran görüntüsüne bakın:

Akıllı bir özellik ile koşullu biçimlendirilmiş hücreleri sayma ve toplama

Koşullu biçimlendirilmiş hücreleri saymak ve toplamak için başka hızlı ve kolay yöntemler arıyorsanız, Kutools for Excel sizin çözümünüz. Count by Color özelliği, bu görevi birkaç tıklamayla çözebilir. Kutools'un iş akışınıza getirebileceği verimlilik ve hassasiyeti keşfedin.

Kutools for Excel'i indirip yükledikten sonra, önce belirli bir koşullu biçimlendirme rengine göre saymak veya toplamak istediğiniz veri aralığını seçin. Ardından, Kutools Plus > Count by Color'a tıklayarak Count by Color iletişim kutusunu açın.

Count by Color iletişim kutusunda, lütfen işlemleri belirtin:

1. Renk yöntemi açılır listesinden Koşullu Biçimlendirme'yi seçin;
2. Sayma türü açılır listesinden Arka Plan'ı belirtin ve her bir koşullu biçimlendirme rengi için istatistiksel sonuçları diyalog kutusunda önizleyebilirsiniz;
3. Son olarak, hesaplanan sonuçları yeni bir çalışma kitabına aktarmak için Rapor Oluştur'a tıklayın.

Sonuç:

Şimdi, koşullu biçimlendirme rengine göre ayrıntılı istatistikler içeren yeni bir çalışma kitabınız olacaktır. Ekran görüntüsüne bakın:

İlgili Makaleler: