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

Günlük görevlerde, önemli verileri hızlıca ayırt etmek ve vurgulamak için renk işaretleme yaygın bir yöntemdir. Peki belirli bir renge—Doldurma Rengi, Yazı Tipi Rengi veya Koşullu Biçimlendirme kullanılarak—göre hücre verilerini nasıl sayabilir ya da toplayabiliriz? Excel, renge göre sayma veya toplama için doğrudan bir özellik sunmaz; ancak bazı püf noktaları ve dolaylı yöntemlerle bu işlemi yine de gerçekleştirebiliriz. Bu makalede, verileri renklerine göre nasıl sayacağınızı ve toplayacağınızı adım adım inceleyeceğiz.

Arka Plan Rengi'ye göre hücreleri sayma ve toplama

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

Yazı Tipi Rengi'e göre hücreleri sayma ve toplama

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

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

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

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

Arka Plan Rengi'e göre hücreleri sayma ve toplama

Örneğin, aşağıdaki ekran görüntüsünde gösterildiği gibi değerlerin farklı arka plan renkleriyle doldurulduğu bir veri aralığınız olsun. Excel, hücreleri arka plan renklerine göre doğrudan saymak veya toplamak için yerleşik bir özelliğe sahip değildir. Ancak biraz yaratıcılık ve bazı kullanışlı tekniklerle bu görevi kolayca gerçekleştirebilirsiniz. Bu bölümde, size yardımcı olabilecek bazı etkili yöntemleri inceleyelim.

Kullanıcı Tanımlı İşlev ile Arka Plan Rengi'ye göre hücreleri sayma ve toplama

Burada, Excel’de bu görevi çözmek için bir Kullanıcı Tanımlı İşlev’in nasıl oluşturulup kullanılacağını adım adım göstereceğiz. Lütfen aşağıdaki adımları izleyin:

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

1. Alt + F11 tuşlarına bastıktan sonra Microsoft Visual Basic for Applications penceresi açılır.
2. Açılan pencerede, yeni bir boş modül oluşturmak için Ekle > Modül öğesine tıklayın.
3. Ardından aşağıdaki kodu boş modüle kopyalayıp yapıştırın.
   VBA kodu: Arka Plan Rengi temel alarak 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: Formül Oluştur ile Arka Plan Rengi'ye göre hücreleri sayma ve toplama

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

• Belirli bir arka plan rengine göre hücreleri sayın:
  Sonucu almak için aşağıdaki formülü istediğiniz hücreye kopyalayın veya yazın. Ardından diğer sonuçları elde etmek 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şmesini istediğiniz belirli arka plan rengine sahip başvuru hücresidir; $B$2:$E$12, G2 hücresinin rengindeki hücre sayısını saymak istediğiniz aralıktır; FALSE ise eşleşen renge sahip hücreleri saymak için kullanılır.
  
• Belirli bir arka plan rengine göre hücreleri toplayın:
  Sonucu almak için aşağıdaki formülü istediğiniz hücreye kopyalayın veya yazın. Ardından diğer sonuçları elde etmek 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şmesini istediğiniz belirli arka plan rengine sahip başvuru hücresidir; $B$2:$E$12, G2 hücresinin rengindeki hücreleri saymak istediğiniz aralıktır; TRUE ise eşleşen renge sahip hücrelerin değerlerini toplamak için kullanılır.
  

Güçlü bir özellik ile Arka Plan Rengi'ye göre hücreleri sayma ve toplama

Daha önce programlamayla karşılaşmamış olanlar için VBA oldukça karmaşık görünebilir. İşte size güçlü bir araç tanıtıyoruz: Kutools for Excel. Bu aracın Renge Göre Say özelliği, yalnızca birkaç tıklama ile Arka Plan Rengine göre (sayma, toplama, ortalama alma vb.) kolayca hesaplama yapmanızı sağlar. En etkileyici yanı ise Renge Göre Say özelliğinin sadece Arka Plan Rengiyle sınırlı olmaması; aynı zamanda Yazı Tipi Rengine ve Koşullu Biçimlendirmeye göre de ayırma ve hesaplama yapabilmesidir.

Kutools for Excel'i indirdikten ve yükledikten sonra, öncelikle belirli bir arka plan rengi temelinde hücreleri saymak veya toplamak istediğiniz veri aralığını seçin. Ardından KUTOOLS PLUS sekmesine gidin ve Renge Göre Say seçeneğini seçin.

Şu iletişim kutusunda:Renge Göre Saylütfen işlemleri belirtin:

1. Seçin Standart Biçimlendirmeöğesini Renk Moduaçılır listesinden;
2. Belirtin Arka planseçeneğini İstatistik Türüaçılır listesinden ve iletişim kutusundaki her Arka Plan Rengi için istatistiksel sonuçları önizleyebilirsiniz;
3. Son olarak, hesaplanan sonuçları yeni bir çalışma kitabına aktarmak için Rapor Oluştur düğmesine tıklayın.

Sonuç:

Artık istatistiklerle birlikte bir Yeni Çalışma Kitabı alacaksınız. Ekran görüntüsüne bakın:

Filtre ve ALTTOPLAM işleviyle Arka Plan Rengi göre hücreleri sayma ve toplama

Aşağıdaki ekran görüntüsünde gösterildiği gibi bir meyve satış tablomuz olduğunu varsayalım ve Tutar sütunundaki renkli hücreleri saymak veya toplamak istiyoruz.

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

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

• Aynı Arka Plan Rengi'e sahip tüm hücreleri saymak için lütfen şu formülü girin:

  =SUBTOTAL(102, F2:F16)

• Aynı Arka Plan Rengi'ye 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ücreleri hariç tutarak filtrelenmiş bir listedeki sayısal değerleri saymayı ifade eder; 109, gizli hücreleri hariç tutarak filtrelenmiş bir listedeki değerleri toplamayı ifade eder; F2:F16 ise sayma veya toplama işleminin 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 > Filtrele seçeneğine tıklayın. Ekran görüntüsüne bakın:
   
2. Başlık hücresindeki Filtrele simgesine tıklayın, ardından Tutar sütununda sırasıyla Renge Göre Filtrele seçeneğine ve saymak istediğiniz belirtilen renge tıklayın. Ekran görüntüsüne bakın:
   

Sonuç:

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

Yazı Tipi Rengi temelinde hücreleri sayma ve toplama

Excel'de hücreleri Yazı Tipi Rengi temelinde saymak veya toplamak mı istiyorsunuz? Diyelim ki verileriniz var ve aşağıdaki ekran görüntüsündeki gibi kırmızı, mavi, turuncu ve siyah renkte metin içeren hücreleriniz bulunuyor. Excel bu işlemi varsayılan olarak kolaylaştırmaz. Ama endişelenmeyin! Bu bölümde size bunu yapmanız için bazı basit püf noktalarını göstereceğiz.

Kullanıcı Tanımlı İşlev ile Yazı Tipi Rengi temelinde hücreleri sayma ve toplama

Belirli yazı tipi rengine 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üzenleyicisini açın ve kodu kopyalayın

1. Açmak için Alt + F11tuşlarına basın Microsoft Visual Basic for Applicationspenceresi.
2. Açılan pencerede, yeni bir boş modül oluşturmak için Ekle>Modülöğesine tıklayın.
3. Ardından aşağıdaki kodu boş modüle kopyalayıp yapıştırın.
   VBA kodu: Yazı Tipi Rengi'ye 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: Formül Oluştur kullanarak Yazı Tipi Rengi göre hücreleri sayma ve toplama

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

• Belirli bir Yazı Tipi Rengi'ne göre hücreleri sayın:
  Sonucu almak için aşağıdaki formülü istediğiniz hücreye kopyalayın veya yazın. Ardından diğer sonuçları elde etmek 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şmesini istediğiniz belirli Yazı Tipi Rengi'ne sahip başvuru hücresidir; $B$2:$E$12 ise G2 hücresinin rengindeki hücre sayısını saymak istediğiniz aralıktır.
  
• Belirli bir Yazı Tipi Rengi'ne göre hücreleri toplayın:
  Sonucu almak için aşağıdaki formülü istediğiniz hücreye kopyalayın veya yazın. Ardından diğer sonuçları elde etmek için doldurma tutamacını aşağı doğru sürükleyin. Ekran görüntüsüne göz atın:
  

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

  Not: Bu formülde, G2, eşleşmesini istediğiniz belirli Yazı Tipi Rengi’ne sahip başvuru hücresidir; $B$2:$E$12 ise G2 hücresinin rengindeki değerleri toplamak istediğiniz aralıktır.
  

Kolay bir özellik ile Yazı Tipi Rengi temelinde hücreleri sayma ve toplama

Excel'de yazı tipi rengine göre hücre değerlerini zahmetsizce saymak veya toplamak mı istiyorsunuz? O zaman Kutools for Excel'in Renge Göre Say özelliğine göz atın! Bu akıllı araç sayesinde belirli bir yazı tipi rengine sahip hücreleri saymak ve toplamak çocuk oyuncağı haline gelir. Kutools ile Excel deneyiminizin nasıl dönüştürülebileceğini keşfedin.

Kutools for Excel'i indirdikten ve yükledikten sonra, öncelikle belirli bir Yazı Tipi Rengi temelinde hücreleri saymak veya toplamak istediğiniz veri aralığını seçin. Ardından şuna tıklayın: KUTOOLS PLUS > Renge Göre Say ve Renge Göre Say iletişim kutusunu açın.

Şu iletişim kutusunda:Renge Göre Saylütfen işlemleri belirtin:

1. Seçin Standart Biçimlendirmeöğesini Renk Moduaçılır listesinden;
2. Belirtin Yazı Tipiseçeneğini İstatistik Türüaçılır listesinden ve iletişim kutusundaki her Yazı Tipi Rengi için istatistiksel sonuçları önizleyebilirsiniz;
3. Son olarak, hesaplanan sonuçları bir Yeni Çalışma Kitabı'na aktarmak için Rapor Oluştur düğmesine tıklayın.

Sonuç:

Artık Yazı Tipi Rengi temelinde ayrıntılı istatistikleri gösteren bir Yeni Çalışma Kitabı'niz var. Ekran görüntüsüne bakın:

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

Excel'de genellikle belirli kriterleri karşılayan hücrelere özel renk uygulamak ve veri görselleştirmeyi sezgisel hale getirmek için Koşullu Biçimlendirme Kullan kullanırsınız. Peki ya bu özel biçimlendirilmiş hücreleri saymanız veya toplamanız gerekirse? Excel bunun için doğrudan bir yol sunmaz, ancak bu sınırlamayı aşmanın yolları vardır.

Koşullu biçimlendirilmiş hücreleri VBA koduyla sayma ve toplama

Excel'de koşullu biçimlendirmeyle belirlenmiş hücreleri saymak ve toplamak, yerleşik işlevlerle kolay değildir. Ancak bu işlemi VBA koduyla sorunsuzca gerçekleştirebilirsiniz. VBA'yı bu amaçla nasıl kullanacağınızı aşağıda inceleyelim:

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

1. Açmak için Alt + F11tuşlarına basın Microsoft Visual Basic for Applicationspenceresi.
2. Açılan pencerede, yeni bir boş modül oluşturmak için Ekle>Modülöğesine 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 Kullan 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 görünecektir: "Lütfen Veri Aralığı'nı seçin". Burada, 'Koşullu Biçimlendirme Kullan' seçeneğine göre hücreleri saymak ve toplamak istiyorsunuz. Ardından Tamam düğmesine tıklayın. 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 Kullan rengini seçin ve Tamamdüğmesine tıklayın, ekran görüntüsüne bakın:
   

Sonuç:

Artık belirtilen Koşullu Biçimlendirme Kullan rengine sahip hücrelerin hem sayısını hem de toplamını içeren sonuç, açılan kutuda görüntülenecektir. Ekran görüntüsüne bakın:

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

Koşullu biçimlendirilmiş hücreleri saymak ve toplamak için daha hızlı ve kolay yollar mı arıyorsunuz? Kutools for Excel, çözümünüz olacak! Yalnızca birkaç tıklamayla bu görevi halledebilen Renge Göre Say özelliğini deneyin. Kutools’un iş akışınıza katacağı verimlilik ve hassasiyeti keşfetmek için şimdi derinlemesine inceleyin.

Kutools for Excel'i indirdikten ve yükledikten sonra, öncelikle hücreleri saymak veya toplamak istediğiniz Veri Aralığını seçin—bu işlemi, belirli bir Koşullu Biçimlendirme rengine göre yapacaksınız. Ardından şuna tıklayın: KUTOOLS PLUS > Renge Göre Say ve açılan Renge Göre Say iletişim kutusunu kullanın.

Şu iletişim kutusunda:Renge Göre Saylütfen işlemleri belirtin:

1. Seçin Koşullu Biçimlendirme Kullanöğesini Renk Moduaçılır listesinden;
2. Belirtin Arka planseçeneğini İstatistik Türüaçılır listesinden ve iletişim kutusundaki koşullu biçimlendirme renkleri için istatistiksel sonuçları önizleyebilirsiniz;
3. Son olarak, hesaplanan sonuçları bir Yeni Çalışma Kitabı'e aktarmak için Rapor Oluşturdüğmesine tıklayın.

Sonuç:

Artık Koşullu Biçimlendirme Kullan rengine göre ayrıntılı istatistikleri gösteren bir Yeni Çalışma Kitabı'niz var. Ekran görüntüsüne bakın:

İlgili Makaleler: