Birçok blogumda belirttiğim gibi, SUMPRODUCT çok yönlü bir işlevdir ve çok amaçlı kullanılabilir. Bu yazımızda, çoklu veya ölçütlü değerleri saymak için bu fonksiyonu nasıl kullanabileceğimizi göreceğiz.
Çoklu veya Kriterlerle Sayılacak Genel SUMPRODUCT Formülü
| =TOPLA(--(((kriter1)+(kriter2)+… )>0) |
Kriter1: Bu, DOĞRU ve YANLIŞ dizisini döndüren herhangi bir ölçüttür.
Kriter2: Bu, kontrol etmek istediğiniz bir sonraki kriterdir. Benzer şekilde istediğiniz kadar kritere sahip olabilirsiniz.
Yukarıdaki jenerik formül, çoklu OR kriterleri ile sayma gereksinimlerine uyacak şekilde sıklıkla değiştirilir. Ama temel formül şu. Önce bunun nasıl çalıştığını bir örnek üzerinden göreceğiz ve ardından bu formülü biraz değiştirmeniz gereken diğer senaryoları tartışacağız.
Sorun:
Bir hücredeki fiyat, doğru bir şekilde 5 benzer ödemeye bölünmelidir. 88.04$'ı 5'e bölüp sonucu iki ondalık basamağa yuvarladığımızda, 17.61$'lık bireysel ödemeler alıyoruz. Ancak bu, toplamda 88.05 $'lık bir ödeme yapar ve bu, başladığımız tam miktar değildir. Bu nedenle, toplam tutarı toplayan 5 benzer sayıyı hesaplamak istiyoruz.
Çözüm:
Önce Fiyatı 5'e bölerek ve sonucu yuvarlayarak ilk ödemeyi hesaplayın.
YUVARLAK işlevini aşağıdaki formülde kullanın:
=Yuvarlak(B1/5,2)
Adım 2:
Kalan her ödemeyi hesaplayın.
YUVARLAK, TOPLA ve SATIR işlevlerini aşağıdaki formülde kullanın:
=YUVARLAK(($B$1-TOPLA($B$2:B2))/(5-SATIR()+SATIR($B$2)),2)
Örnek:
SUMPRODUCT Kullanarak Bayi Kodu veya Yıl Eşleşirse Kullanıcıları Sayma
Yani burada bir satış elemanı veri setimiz var. Veriler birçok sütun içerir. Yapmamız gereken, "INKA" veya yıl koduna sahip kullanıcı sayısını "2016" olarak saymaktır. Birinin her ikisine de sahipse ("inka" ve 2016 yılı olarak kod) 1 olarak sayıldığından emin olun.
Öyleyse, iki kriterimiz olduğunu duyun. Yukarıda belirtilen SUMPRODUCT formülünü kullanıyoruz:
| =TOPLAÇA(--(((Kod=I3)+(Yıl=K3))>0)) |
Burada kod ve yıl aralıklar olarak adlandırılır. Bu 7 döndürür.
Verilerde İNKA koduna ait 5 adet, 2016 yılına ait 4 adet kayıt bulunmaktadır. Ancak 2 adet kayıtta sırasıyla kod ve yıl olarak hem “İNKA” hem de 2016 bulunmaktadır. Ve bu kayıtlar 1 olarak sayılır ve bu şekilde 7 elde ederiz.
Bakalım nasıl çalışıyor?
Şimdi formülün adım adım nasıl çözüldüğüne bir bakalım, sonra nasıl çalıştığını tartışacağım.
-
- =TOPLAÇARPIM(--((((Kod=I3)+(Yıl=K3))>0))
- SUMPRODUCT(--(({DOĞRU;YANLIŞ;DOĞRU;DOĞRU;DOĞRU;DOĞRU;… }+{YANLIŞ;YANLIŞ;YANLIŞ;DOĞRU;DOĞRU;… })>0))
- SUMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODUCT(--({DOĞRU;YANLIŞ;DOĞRU;DOĞRU;DOĞRU;DOĞRU;DOĞRU;… })
- SUMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Çift negatif (--) işaretleri, boole değerlerini 1'lere ve 0'lara dönüştürmek için kullanılır. Böylece dizideki her TRUE değeri 1'e ve FALSE değeri 0'a dönüştürülür.
SUMPRODUCT Kullanarak Saymak İçin Daha Fazla Veya Ölçüt Ekleme, Yani saymak için daha fazla veya ölçüt eklemeniz gerekiyorsa, işleve + işaretini kullanarak ölçüt ekleyebilirsiniz.
Örneğin, yukarıdaki formüle 5'ten fazla ürün satan çalışan sayısını eklemek için başka bir kriter eklemek istiyorsanız. SUMPRODUCT formülü basitçe şöyle görünecektir:
| =TOPLAÇ(--(((Kod=I3)+(Yıl=K3)+(Satış>5))>0)) |
Basit! değil mi?
Ama diyelim ki Code aralığından iki kriteriniz olsun istiyorsunuz. Diyelim ki "INKB" saymak istiyorsunuz. O zaman bunu nasıl yaparsın? Bir yöntem, yukarıdaki tekniği kullanmaktır, ancak bu tekrarlayıcı olacaktır. Diyelim ki aynı aralıktan 10 kriter daha eklemek istiyorum. Bu gibi durumlarda bu teknik, SUMPRODUCT ile saymak için o kadar akıllı değildir.
Diyelim ki böyle düzenlenmiş verilerimiz var.
Kriter kodları bir satırda I2:J2'dir. Verilerin düzenlenmesi burada önemlidir. 3 VEYA ölçüt sayısı ayarı için SUMPRODUCT formülü şöyle olacaktır:
| =TOPLAÇA(--(((Kod=I2:J2)+(Yıl=I3:J3))>0)) |
Bu, bir satırda birden çok ölçüt yazıldığında birden çok ölçütle saymak için SUMPRODUCT formülüdür.
Yukarıdaki formül, 10 olan doğru cevabı döndürür.
J3'e herhangi bir yıl yazarsanız, formül bu sayıyı da ekleyecektir.
Bu, ölçütler bir satırda olduğunda kullanılır. Aynı aralık için bir sütundaki kriterler işe yarayacak mı? Hayır. Olmayacak.
Bu örnekte sayılacak birden fazla kodumuz var ama bu tip kodları bir sütuna yazılıyor. Yukarıdaki SUMPRODUCT formülünü kullandığımızda #YOK hatası alıyoruz. Bu makaleyi çok uzun sürdüğü için bu hatanın nasıl geldiğine girmeyeceğiz. Bu işi nasıl yapabileceğimize bir bakalım.
Bu formülün çalışması için kod kriterlerini TRANSPOSE işlevine sarmanız gerekir. Bu, formülün çalışmasını sağlayacaktır.
| =TOPLAÇA(--(((Kod=TRANSPOZ(H3:H4))+(Yıl=TRANSPOZ(I3:I4)))>0)) |
Bu, ölçütler bir sütunda listelendiğinde aynı aralıkta birden çok veya koşulla sayma formülüdür.
Formülün kullanımıyla ilgili tüm gözlem notları burada.
Notlar:
- Formül hem metin hem de sayılar için çalışır.
- Arama değerleri, hücre referansı olarak veya doğrudan formülde tırnak işareti ( " ) kullanılarak argüman olarak verilebilir.
Excel'de Bölme / Yuvarlama Hatalarından Kaçınırken Bir Tutarı Eşit Ödemelere Nasıl Böleceğinizi anladığınızı umarız. Excel arama değeri ve Excel 2019 işlevleri hakkında daha fazla makaleyi buradan keşfedin. Bloglarımızı beğendiyseniz, Facebook'ta arkadaşlarınızla paylaşın. Ayrıca bizi Twitter ve Facebook'ta da takip edebilirsiniz. Sizden haber almayı çok isteriz, işimizi nasıl iyileştirebileceğimizi, tamamlayabileceğimizi veya yenileyebileceğimizi ve sizin için daha iyi hale getirebileceğimizi bize bildirin. E-posta sitesinde bize yazın.
Değeri Aramak için INDEX ve MATCH kullanın : Gerektiğinde değeri aramak için INDEX & MATCH işlevi.
Excel'de INDEX ile SUM aralığı : Değerlerin TOPLAMINI gerektiği gibi bulmak için INDEX işlevini kullanın.
Excel'de INDEX işlevi nasıl kullanılır? : Bir örnekle açıklanan INDEX işlevini kullanarak bir dizinin INDEX'ini bulun.
Excel'de KAÇINCI işlevi nasıl kullanılır? : Örnekle açıklanan KAÇINCI işlevinin içindeki INDEX değerini kullanarak dizideki KAÇINCI'yı bulun.
Excel'de ARA işlevi nasıl kullanılır? : Bir örnekle açıklanan ARA işlevini kullanarak dizideki arama değerini bulun.
Popüler Makaleler :
Excel'de EĞER İşlevi nasıl kullanılır? : Excel'deki EĞER ifadesi koşulu kontrol eder ve koşul DOĞRU ise belirli bir değer veya YANLIŞ ise başka bir belirli değer döndürür.
Excel'de DÜŞEYARA İşlevi nasıl kullanılır? : Bu, farklı aralık ve sayfalardan değer aramak için kullanılan excel'in en çok kullanılan ve popüler işlevlerinden biridir.
Excel'de SUMIF İşlevi Nasıl Kullanılır : Bu, başka bir gösterge panosu temel işlevidir. Bu, belirli koşullardaki değerleri özetlemenize yardımcı olur.
Excel'de COUNTIF İşlevi nasıl kullanılır? : Bu şaşırtıcı işlevi kullanarak değerleri koşullarla sayın. Belirli değerleri saymak için verilerinizi filtrelemeniz gerekmez. Gösterge tablonuzu hazırlamak için Countif işlevi gereklidir.