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”))

İlginizi Çekebilecek Diğer Yazılar
Etiketler: , , ,

Leave a Reply