1. Amaç
Bazı durumlarda filtre uygulanmış bir listede, gizli olan hücrelerdeki değerler hariç belli bazı kıstaslara göre toplama işlemi yapmak gerekebilir.
Örnek olarak aşağıdaki gibi bir tablomuz olsun:
| Ürün | Sipariş Kodu | Öncelik | Sipariş Tarihi | Sevk Tarihi | Miktar | Birim Fiyat | Toplam Maliyet | Fatura Tutarı | Kar |
| Tahıl | 107-12-30 | A | 30.12.2020 | 03.01.2021 | 8.060 | 2,38 | 19.182,80 | 22.252,05 | 3.069,25 |
| Meyve | 116-01-07 | A | 07.01.2021 | 14.01.2021 | 6.496 | 6,42 | 41.704,32 | 48.377,01 | 6.672,69 |
| Sebze | 121-01-08 | Y | 08.01.2021 | 16.01.2021 | 3.659 | 5,56 | 20.344,04 | 25.599,09 | 3.255,05 |
| Sebze | 134-01-25 | Y | 05.01.2021 | 28.01.2021 | 10.486 | 5,56 | 58.302,16 | 67.630,51 | 9.382,35 |
| Meyve | 169-01-27 | Y | 27.01.2021 | 04.02.2021 | 5.807 | 6,42 | 37.280,94 | 43.245,89 | 5.964,95 |
| Meyve | 187-02-02 | D | 02.02.2021 | 09.02.2021 | 8.618 | 6,42 | 55.327,56 | 64.179,97 | 8.852,41 |
| Tahıl | 194-02-05 | O | 05.02.2021 | 11.02.2021 | 4.487 | 2,38 | 10.679,06 | 12.387,71 | 1.708,65 |
| Sebze | 202-02-13 | Y | 13.02.2021 | 16.02.2021 | 7.730 | 5,56 | 42.978,80 | 49.855,41 | 6.876,61 |
| Tahıl | 219-02-23 | A | 23.02.2021 | 03.03.2021 | 1.598 | 2,38 | 3.803,24 | 4.411,76 | 608,52 |
| Meyve | 219-02-24 | Y | 24.02.2021 | 27.02.2021 | 10.767 | 6,42 | 69.124,14 | 80.184,00 | 11.059,86 |
| Tahıl | 239-03-09 | Y | 09.03.2021 | 12.03.2021 | 3.199 | 2,38 | 7.613,62 | 8.831,80 | 1.218,18 |
| Sebze | 246-03-13 | A | 13.03.2021 | 18.03.2021 | 9.066 | 5,56 | 50.406,96 | 58.472,07 | 8.065,11 |
| Sebze | 246-03-13 | D | 13.03.2021 | 21.03.2021 | 9.807 | 5,56 | 54.526,92 | 63.251,23 | 8.724,31 |
| Meyve | 253-03-14 | O | 14.03.2021 | 21.03.2021 | 2.449 | 6,42 | 15.722,58 | 18.238,19 | 2.515,61 |
| Sebze | 271-03-27 | A | 27.03.2021 | 04.04.2021 | 8.095 | 5,56 | 45.008,20 | 52.209,51 | 7.201,31 |
Bu listede Ürün sütununu süzdüğümüzde gösterilen maddelerden Önceliği A olan siparişlerden elde edilen toplam kar ne kadar bulmak isteyelim. Bu yazıda bunu nasıl yapabileceğimizi göstereceğim.
2. Uygulama
İstediğim sonucu elde etmek için öncelikle toplanacak tüm değerlerin bir listesini elde etmem gerek. Sonra bu listede hangi değerlerin görünür olduğunu tespit etmeliyim. Daha sonra bu görünür değerlerden hangisi istediğim kıstasa uygun olduğunu bularak çıkan değerleri toplamam gerek.
Ben yukarıdaki tabloyu B2 hücresinden başlayarak yapıştırdım. Sonrasında bu listeye filtreleme uyguladım. Tablodaki Ürünleri (B sütunu) filtreleyip Öncelik (D sütunu) değeri “A” olan ürünlerin Kar (K sütunundaki) değerlerini toplayacağım. Eğer siz tabloyu farklı bir hücreden itibaren yapıştırdı iseniz veya kendi değerlerinizi kullanıyorsanız lütfen formüllerdeki aralıkları kendi tablonuza göre değiştirin.
a.Toplanacak Değerlerin Listesini Elde Etme
İlk yapmam gereken iş toplanacak değerleri ayrı ayrı bir dizi haline getirmem gerek. Bunu KAYDIR işlevi ile yapacağım. Ancak bana KAYDIR işlevinde kullanacağım satır numaralarınını listesi lazım. Bunu da SATIR işlevi ile yapacağım:
=SATIR($K$3:$K$17)
Bu formül bize K3:K17 hücrelerinin satır değerleri olan 3 ile 17 arasında rakamları bir liste olarak verecektir. Bu değerlerden aralığın ilk hücre satırını çıkaralım:
=SATIR($K$3:$K$17)-SATIR($K$3)
Bu sayede elimizde 0 ile 14 arasında rakamlar var. Bu rakamları kullanarak her bir satırdaki değerleri teker teker elde edebiliriz. Şimdi bunu KAYDIR işlevi ile birleştirelim:
=KAYDIR($K$3; SATIR($K$3:$K$17)-SATIR($K$3);;;)
Bu formül ile aralıktaki tüm değerleri içeren bir liste elde ediyoruz. Bu aşamada yazdığınız formül “#DEĞER” hatası verecektir.
b. Gizli Olmayan Hücre Değerlerini Bulmak
Bu değerlerden gizli olmayanları elde etmek için de ALTTOPLAM işlevini kullanacağız.
=ALTTOPLAM(109; KAYDIR($K$3; SATIR($K$3:$K$17)-SATIR($K$3);;;))
ALTTOPLAM işlevindeki 109 değeri, aralıktaki gizli olmayan hücrelerdeki değerleri toplayacaktır. Burada KAYDIR işlevi ile birden fazla değer döndürüldüğünden her bir değer tek bir aralık olarak alınır ve elimizde gizli olmayan hücrelerdeki Kar değerlerinden oluşan bir liste geçer.
c. Kıstasa Göre Değerleri Bulma ve Toplam
Benim kıstasım D sütununda değeri “A” olan satırlar. Bunun için:
=($D$3:$D$17=”A”)
Formülünü yazıyorum ve elimde Sütun değeri A olan satırlar için DOĞRU, olmayan satırlar için “YANLIŞ” değerlerini içeren bir liste oluşuyor.
Bir önceki adımdaki formül ile bu formülü birleştirirsem görünür hücrelerde istediğim kıstasa ait değerlerin olduğu bir liste elde ederim.
= ALTTOPLAM(109; KAYDIR($K$3; SATIR($K$3:$K$17)-SATIR($K$3);;;))* ($D$3:$D$17=”A”)
Bu değerlerin toplamını aldığımda istediğim sonucu elde etmiş olurum:
=TOPLA(ALTTOPLAM(109; KAYDIR($K$3; SATIR($K$3:$K$17)-SATIR($K$3);;;))* ($D$3:$D$17=”A”))
Eğer Office 365 kullanıyor iseniz direk Enter’a basarak daha eski sürümleri kullanıyor iseniz CTRL+SHIFT+ENTER tuşlarına basarak formülü dizi formülü haline getirerek sonucu elde edebilirsiniz. Dizi formülü kullanmak istemiyorsanız TOPLA yerine TOPLA.ÇARPIM işlevini de kullanabilirsiniz:
=TOPLA.ÇARPIM(ALTTOPLAM(109; KAYDIR($K$3; SATIR($K$3:$K$17)-SATIR($K$3);;;))* ($D$3:$D$17=”A”))
Formül çalışmıyor, acaba uygularken benmi hata yapıyorum yoksa formülü yapıştırıken sizmi?
=TOPLA.ÇARPIM(ALTTOPLAM(109;KAYDIR($K$3;SATIR($K$3:$K$17)-SATIR($K$3);;;))*($D$3:$D$17=”A”))
Merhaba,
Formülü direk kopyalayıp Excel’e yapıştırdı iseniz çift tırnak işaretlerini kontrol edin. Web sitesinde farklı bir karakter kullanıldığından Excel’e aktarıldığında formül çalışmıyor.
Saygılarımla,