Excel’de Renge Göre Toplama

Excel’in gerçekten harika işlevleri vardır, ancak hücre rengine dayalı olarak hücre değerlerini toplayabilen bir işlevi yoktur.

Örneğin, aşağıda gösterildiği gibi bir veri setim var ve tüm turuncu ve sarı renkli hücrelerin toplamını almak istiyorum.

Ne yazık ki, bunu yapmak için yerleşik bir işlev yoktur.

Bu derste, Excel’de renge göre toplamak için kullanabileceğiniz üç basit teknik göstereceğim.

Filtre ve ARA TOPLAMI Kullanarak Renge Göre SUM Hücreleri

En kolay olanla başlayalım.

Aşağıda, çalışan adlarına ve satış numaralarına sahip olduğum bir veri kümem var.

Ve bu veri kümesinde, sarı ve turuncu renkli tüm hücrelerin toplamını almak istiyorum.

Excel’de hücre rengine dayalı değerleri toplamak için yerleşik bir işlev bulunmamakla birlikte, hücreleri hücre rengine göre filtreleyebilmenize dayanan basit bir geçici çözüm vardır.

Bu yöntem için aşağıdaki formülü B17 hücresine (veya renkli hücreler veri kümesinin altındaki aynı sütundaki herhangi bir hücreye) girin.

=SUBTOTAL(9,B2:B15)

Yukarıdaki ARA TOPLAM formülünde, fonksiyona ikinci argüman olarak verilen aralığın toplamını almak istediğimi söyleyen ilk argüman olarak 9 kullandım.

Ama neden bunun yerine SUM formülünü kullanmıyorsunuz?

Bunun nedeni, ARA TOPLAM formülüne sahip olduğumda ve hücreleri yalnızca içinde belirli bir renge sahip olan hücreleri gösterecek şekilde filtrelediğimde, ARA TOPLAM formülünün bana yalnızca görünür hücrelerin toplamını göstermesidir.

ARA TOPLAM formülüne sahip olduğunuzda, hücre rengine göre TOPLAM’ı almak için aşağıdaki adımları izleyin.

1. Veri kümesindeki herhangi bir hücreyi seçin
2. Veri sekmesine tıklayın

3. Sırala ve Filtrele grubunda Filtre simgesine tıklayın. Bu, veri kümesine filtre uygulayacak ve başlıklarda filtre simgesini görebileceksiniz.

4. Satış başlığı hücresinde, Filtre simgesine tıklayın

5. Renge Göre Filtrele” seçeneğine gidin
6. Veri kümesini filtrelemek istediğiniz rengi seçin.

Bunu yaptığınız anda, ara toplam sonucunun değiştiğini fark edeceksiniz ve bu şimdi size yalnızca görünen hücrelerin toplamını verecektir (bu, yalnızca veri kümesini filtrelediğiniz renge sahip hücreler olacaktır).

Benzer şekilde, veri kümesindeki başka bir renge göre filtrelerseniz (sarı yerine turuncu söyleyin), ARA TOPLAM işlevi buna göre ayarlanır ve size turuncu renkli tüm hücrelerin toplamını verir.

Profesyonel İpucu : Bir veri kümesine filtre uygulamak için klavye kısayolu Control + Shift + L’dir (Control ve Shift tuşlarını basılı tutun ve ardından L tuşuna basın). Mac kullanıyorsanız Command + Shift + L tuşlarını kullanın

VBA Kullanarak Renge Göre SUM Hücreleri

Hücre rengi değerine göre toplanacak Excel’de yerleşik bir formül bulunmadığından bahsetmiştim. Ancak, VBA kullanarak bunu yapmak için kendi formülünüzü oluşturabilirsiniz.

VBA ile arka uçta tutabileceğiniz özel bir işlev oluşturabilir ve ardından bunu çalışma sayfasındaki diğer normal işlevler gibi kullanabilirsiniz.

Aşağıda, Excel’de renge göre toplamanıza izin veren bu özel işlevi oluşturacak VBA kodu bulunmaktadır.

Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function

Bu VBA özel işlevini kullanmak için önce bu kodu kopyalamanız ve VB düzenleyicide arka uca yapıştırmanız gerekir.

İşiniz bittiğinde, bu işlevi çalışma sayfasında kullanabileceksiniz.

Bu kodu VB editörüne ekleme adımları aşağıdadır.

1. Şeritteki Geliştirici sekmesine tıklayın (Geliştirici sekmesi görünmüyorsa, nasıl edineceğinizi öğrenmek için burayı tıklayın )
2. Visual Basic simgesine tıklayın. Bu, Excel’in Visual Basic düzenleyicisini açacaktır.

3. Menüdeki Ekle seçeneğini tıklayın
4. Modül’e tıklayın. Bu, yeni bir modül ekleyecektir ve onu proje gezgininde (soldaki tüm nesneleri gösteren bir bölme) görebileceksiniz. Proje Gezgini bölmesini görmüyorsanız, Görünüm’e ve ardından Proje Gezgini’ne tıklayın.

5. Yukarıdaki VBA kodunu kopyalayın ve yeni eklenen Modül kodu penceresine yapıştırın

6. VB Düzenleyiciyi Kapatın

Artık Excel’de arka uçta koda sahip olduğunuza göre, çalışma sayfasında oluşturduğumuz işlevi (SumByColor) kullanabileceksiniz.

Bu işlevin çalışması için, toplamını almak istediğim aynı rengi içeren çalışma sayfasında bir hücreye ihtiyacım olacak.

Örneğimizde bunu, D2’nin sarı renge ve D3’ün turuncu renge sahip olduğu D2 ve D3 hücrelerine yaptım.

Şimdi bu hücrelerde aşağıdaki formülü kullanabilirim.

=SumByColor($B$2:$B$15,D2)

Yukarıdaki formül iki argüman alır.

• Eklemek istediğim renge sahip hücre aralığı
• Rengi içeren herhangi bir hücreye başvuru (böylece formül renk indeksini seçebilir ve değerleri eklemek için bunu bir koşul olarak kullanabilir)

Formülün dinamik olduğunu ve veri kümesinde herhangi bir değişiklik yapmanız durumunda (herhangi bir değeri değiştirmek veya bazı hücrelerden renk uygulamak/kaldırmak gibi) otomatik olarak güncelleneceğini unutmayın. Ve formülün güncellenmediğini fark ederseniz, F9 tuşuna basın ve güncellenecektir.

Çalışma kitabında bir VBA kodu kullandığımız için makro etkin bir çalışma kitabı (.XLSM uzantılı) olarak kaydedilmesi gerekiyor.

Profesyonel İpucu : Hücreleri arka plan rengine göre eklemek oldukça sık yapmanız gereken bir şeyse, Kişisel Makro Çalışma Kitabındaki özel formül için bu VBA kodunu kopyalayıp yapıştırmanızı öneririm. Bu şekilde, sisteminizdeki tüm çalışma kitaplarınızda mevcut olacaktır.

GET.CELL Kullanarak Renge Göre Hücreleri TOPLA

Size göstermek istediğim son yöntem, (pek çok kişinin bilmediği) gizli bir Excel formülü içeriyor.

Bu yöntem, bize renkli hücrelerin renk indeks değerini verebilen GET.CELL işlevini kullanır.

Ve her hücrenin renk indeks değerine sahip olduğumuzda, sadece içinde belirli bir renge sahip hücrelerin toplamını elde etmek için basit bir if toplamı formülü kullanabiliriz.

Daha önce bahsettiğim VBA özel işlevi kadar zarif değil, ancak VBA kullanmak istemiyorsanız, bu sizin için yol olabilir.

GET.CELL, uyumluluk nedenleriyle Excel’de saklanan eski bir Makro 4 işlevidir, ancak bununla ilgili çok fazla ayrıntı bulamazsınız (nadiren kullanıldığından).

Aşağıda, toplamak istediğim renkli hücrelere sahip olduğum bir veri setim var.

Bu tekniğin çalışması için öncelikle renkli bir hücrenin renk değerini vermek üzere GET.CELL işlevini kullanacak adlandırılmış bir aralık oluşturmamız gerekir.

İşte bunu yapmak için adımlar.

1. Şeritte Formüller sekmesini tıklayın
2. Tanımlı Adlar grubunda, ‘Ad Yöneticisi’ne tıklayın
3. Ad Yöneticisi iletişim kutusunda, Yeni’ye tıklayın.
4. ‘Yeni Ad iletişim kutusunda, Ad – SumColor’ı girin.
5. Başvuru yeri alanına şu formülü girin: =GET.HÜCRE(38,$B2)
6. Tamam’ı tıklayın
7. Ad Yöneticisi iletişim kutusunu kapatın

Yukarıdaki adımlar, artık çalışma kitabında kullanabileceğimiz adlandırılmış bir aralık oluşturdu.

Not: GET.CELL işlevi iki argüman alır; birincisi, işleve hangi bilgilere ihtiyacımız olduğunu söyleyen bir sayıdır ve ikincisi, o hücrenin kendisinin hücre referansıdır. Bu durumda, bize atıfta bulunulan hücrenin renk indeks değerini verecek olan ilk argüman olarak 38’im var.

Şimdi ikinci adım, B sütunundaki tüm renklerin renk indeksi değerlerini elde etmektir.

Bunu yapmak için gereken adımlar aşağıdadır:

1. C1 hücresine – Renk Dizini başlığını (veya adlandıracağınız herhangi bir şeyi) girin.

2. C2 hücresine aşağıdaki formülü girin: =SumColor

3. C sütunundaki tüm hücreler için aynı formülü uygulayın

Yukarıdaki adımlar size B sütunundaki (soldaki hücre) hücrenin renk indeksini temsil edecek bir değer verecektir.

SumColor, oluşturduğumuz adlandırılmış aralıktır ve soldaki hücrenin renk indeks değerini almak için GET.CELL işlevini kullanır. Bu formülü herhangi bir sütunda kullanabilirsiniz, ancak her zaman o sütundaki ikinci hücreden başlamalıdır. Örneğin, C sütunu yerine bunu H veya J sütununda, ancak bu sütunlardaki ikinci hücrelerden alabilirsiniz.

Artık her renk için benzersiz bir numaramız olduğuna göre, renklerine göre hücrelerin TOPLAMINI almak için bunu kullanabiliriz.

Bunu yapmak için gereken adımlar aşağıdadır:

1. E2 ve E3 hücresinde, hücrelere toplamını almak istediğiniz rengi verin. Benim durumumda, E2 hücresinde sarı rengim ve E3 hücresinde turuncu rengim var.

2. F2 hücresine şu formülü girin: =SUMIF(C2:C15,SumColor,B2:B15)

3. Hücreyi kopyalayın ve F3 hücresine yapıştırın (bu, formülü de kopyalayabilir ve referansları ayarlayabilir).

Yukarıdaki adımlar size bitişik satış sütunu F’de renge sahip olan tüm hücrelerin toplamını verecektir.

Soldaki hücrenin (F sütunundaki) renk indeksi C sütunundakiyle aynıysa, B sütunundaki tüm değerleri toplayan bir SUMIF formülü kullandık.

Bu, hücreleri renge göre toplamanın kesinlikle biraz daha uzun bir yolu olsa da (ARA TOPLAM veya VBA ile karşılaştırıldığında), bu işi halleder ve bu kurulumu yalnızca bir kez yapmanız gerekir.

Formül dinamik olsa da, herhangi bir değişiklik yapmanız durumunda (B sütunundaki renk hücrelerini değiştirmek veya sütundan rengi kaldırmak gibi), değişikliğin SUMIF formülüne anında yansımayabileceğini unutmayın. Bunun için basit bir çözüm, formülü içeren hücreye gitmek, havaalanına girmek için F2 tuşuna basmak ve ardından enter tuşuna basmaktır. Bu, formülü yeniden hesaplamaya zorlar ve güncellenmiş sonucu alırsınız.

Bunlar, Excel’de renge göre toplamak için kullanabileceğiniz üç yöntemdir. SUBTOTAL oldukça kolay ve anlaşılır olsa da, kişisel olarak VBA yöntemini daha çok seviyorum. Umarım bu makaleyi faydalı bulmuşsunuzdur!

Bu yardımcı oldu mu?

EvetHAYIR

0 / 0