Excel'de Renkli Hücreleri Sayma ve Renge Göre Toplama

Excel renkli hücreleri sayabilir mi?

Doğrudan sayamaz: Excel'in hücre dolgu rengine bakan yerleşik bir fonksiyonu yoktur. Renk bir biçimdir, değer değildir; EĞERSAY ya da ETOPLA yalnız hücre içeriğine bakar. Yine de üç pratik yol var: renge göre filtreleyip görünen hücreleri saymak, rengin dayandığı kuralı saymak ve isterseniz küçük bir VBA fonksiyonu yazmak.

Hangisini seçeceğiniz rengin nereden geldiğine bağlıdır. Renkleri elle boyadıysanız filtre yöntemi en hızlısıdır. Renk koşullu biçimlendirmeyle geliyorsa kuralı formülle saymak her zaman daha doğrudur.

Renge göre filtre ve ALTTOPLAM

Önce sonucun görüneceği hücreye ALTTOPLAM yazın. =ALTTOPLAM(103;C2:C7) görünen dolu hücreleri sayar, =ALTTOPLAM(109;C2:C7) görünen sayıları toplar. ALTTOPLAM filtreyle gizlenen satırları her zaman hesap dışı bırakır; 100'lü kodlar ayrıca elle gizlenen satırları da atlar.

Sonra listede bir hücre seçip Veri sekmesinde Filtre'ye tıklayın. Rengi içeren sütunun başlığındaki oka tıklayın, Renge göre menüsünde Hücre Rengi altından istediğiniz rengi seçin. Liste yalnız o renkteki satırlara iner ve ALTTOPLAM sonucu anında değişir.

Örneğin altı faturadan gecikenleri kırmızıyla boyadıysanız kırmızıya göre filtreledikten sonra ALTTOPLAM(103) 3, ALTTOPLAM(109) 5.350 TL gösterir. Filtreyi kaldırdığınızda sonuç bütün listenin toplamına döner.

=ALTTOPLAM(109;C2:C7)

=SUBTOTAL(109,C2:C7)

Bu yöntemin zayıf yanı her renk için filtreyi yeniden değiştirmeniz gerekmesidir. Bir de şu ayrıntıya dikkat edin: ALTTOPLAM formülünü filtrelediğiniz listenin içine, aynı satırlara yazarsanız formülün kendisi de filtreyle gizlenebilir. Sonuç hücresini listenin üstüne ya da yanındaki boş bir sütuna koymak bu sorunu önler. Ayrıntılar için filtrelenmiş hücreleri toplama rehberine bakabilirsiniz.

Rengin arkasındaki kuralı saymak

Çoğu listede renk bir anlam taşır: kırmızı geciken, yeşil ödenen fatura gibi. Bu bilgiyi renk yerine bir Durum sütununda yazı olarak tutarsanız sayım ve toplam sıradan formüllerle yapılır. Renk de bu sütuna bağlı bir koşullu biçimlendirme kuralıyla kendiliğinden gelir.

=EĞERSAY(B2:B7;"Gecikti")

=COUNTIF(B2:B7,"Gecikti")

Aynı sütunla tutar toplamı da tek formüldür. Geciken üç faturanın toplamı ETOPLA ile 5.350 TL çıkar; filtre yöntemindeki sonuçla aynıdır ama hiçbir filtreye dokunmanız gerekmez.

=ETOPLA(B2:B7;"Gecikti";C2:C7)

=SUMIF(B2:B7,"Gecikti",C2:C7)

Renk koşullu biçimlendirmeyle geliyorsa, kuralda kullandığınız koşulu EĞERSAY ya da ÇOKEĞERSAY'a vermeniz yeter. Örneğin 30 günden eski kayıtları kırmızıya boyayan bir kuralınız varsa, aynı tarih koşuluyla sayım yaparsınız. Kuralları formülle kurmak için formülle koşullu biçimlendirme rehberi iyi bir başlangıç noktasıdır.

İsteğe bağlı: VBA ile renk sayma

Renkleri elle boyamak zorundaysanız ve her seferinde filtre değiştirmek istemiyorsanız küçük bir VBA fonksiyonu yazabilirsiniz. Aşağıdaki kod, bir aralıkta örnek hücreyle aynı dolgu rengine sahip hücreleri sayar:

Function RenkSay(aralik As Range, ornek As Range) As Long
    Dim h As Range
    For Each h In aralik
        If h.Interior.Color = ornek.Interior.Color Then RenkSay = RenkSay + 1
    Next h
End Function

Kodu bir modüle ekledikten sonra hücreye =RenkSay(C2:C7;E1) yazarsınız. Bu yolun üç sınırı var: dosyayı makro içerebilen .xlsm biçiminde kaydetmeniz gerekir, renk değiştirmek formülü kendiliğinden yeniden hesaplatmaz ve koşullu biçimlendirmeyle gelen renkleri bu kod görmez. Bu yüzden VBA'yı yalnız diğer yollar uymadığında düşünün.

Yazı rengine göre saymak

Aynı yöntem yazı tipi rengi için de geçerlidir. Filtre menüsünde Renge göre altında Hücre Rengi'nin yanında Yazı Tipi Rengi seçeneği de bulunur. Kırmızı yazıyla işaretlenmiş tutarları ayırmak için bu seçeneği kullanın; ALTTOPLAM formülünü değiştirmenize gerek yoktur.

Renge göre sıralama da işinizi kolaylaştırabilir. Aynı renkteki satırları listenin başına topladığınızda kaç tane olduklarını durum çubuğundaki sayımdan da görebilirsiniz. Birkaç hücre seçtiğinizde Excel pencerenin altında seçili hücrelerin sayısını ve toplamını gösterir.

Hangi yolu seçmeli?

Tek seferlik bir kontrol için filtre ve ALTTOPLAM yeterlidir. Düzenli tuttuğunuz bir listede ise rengi değil anlamı saklayın: Durum sütunu ve ona bağlı koşullu biçimlendirme. Bu düzende sayım, toplam ve pivot tablo kendiliğinden çalışır, renkler de tutarlı kalır.