Sumif(S) Ve Averageif(S) fonksiyonları kullanımı. Özet: Bu makalede SUMIF(s) ve AVERAGEIF(S) fonksiyonlarında farklı durumlara göre koşulların nasıl belirtilmesi gerektiğinden bahsedilmiştir. Metinsel Koşul, Sayısal Koşul, Tarihsel Koşul, “Ya da” Mantığı ile Koşul belirtme (Sumif with Or Criteria), WildCart Kullanımı ve Çoklu Koşullar anlatılmıştır.
Giriş:
Bir veri seti içerisinde, bir koşula bağlı olarak herhangi bir alanın toplanmasını sağlamak için SUMIF(), Ortalaması almak için AVERAGEIF() fonksiyonları kullanılabilir.
Her iki fonksiyonda, üç parametre alarak çalışır.
SUMIF fonksiyonunda Range ve Sum_Range alanları, bir dizi seklinde seçilebilir. Kritik nokta, istenilen durumları Criteria parametresinde doğru bir şekilde belirtebilmektir. Criteria; Manuel olarak yazılabilir, hücreden seçilebilir, başka bir fonksiyondan dönen değer aranabilir, Mantıksal operatörler (“<“,”>”,”<>” vb.) ve Wildcard’lar(“*”,”?”) kullanılabilir.
Aşağıdaki Veri setinde SUMIF fonksiyonun farklı kullanımlarına göre bir takım hesaplamalar gerçekleştirilmiştir.
1. Durum: Ürün Adı “Fren Disk” olan satışların Toplamı ve Ortalaması
Fonksiyonun Criteria Parametresinde, tabloda aranan koşulu manuel olarak yazabilirsiniz. Koşul metinsel bir veri ise çift tırnak (” “) içerisinde yazılmalıdır. Koşul yazılırken, küçük büyük harf hassasiyetine dikkat edilmeyebilir, ancak doğru yazılmalıdır.
Bir tablodan fonksiyonları kullanarak yeni bir tablo oluşturmak isteniyorsa, Bütün ürünlerin ayrı ayrı toplamları hesaplanmak isteniyorsa örneğin, oluşturulan yeni tablodan ilgili parametreyi hücre seçimi şeklinde de gerçekleştirebilirsiniz.
Eğer Ürün Adı Fren Disk olan ürünlerin Ortalama Satış toplamı hesaplanması istenseydi;
Şeklinde yazılabilirdi.
SUMIF için geçerli olan her türlü durum, AVERAGEIF için de geçerlidir.
2.
Durum: Satış Adedi 1 olan Ürünlerin Toplam ve Ortalama Satışları
Belirtilen durumda koşul, Satış Adedi alanında 1 yazan verilerin toplanması ve ortalaması alınması şeklindedir. Birinci örnekte olduğu gibi, ilgili koşul manuel olarak yazılabilir. Ya da bir hücreden referans olarak da verilebilir. Eğer koşul sayısal bir veri ise, çift tırnak (“”) kullanılmaz.
3. Durum: 15.8.2016 tarihinden Yapılan Satışların Toplamı ve Ortalaması
Koşul, Tarihsel bir veri ve hücre seçimi yerine manuel olarak yazılacak ise, tarih metinsel bir veri gibi çift tırnak (“”) içerisinde yazılmalıdır. Ya da bir hücreden referans gösterilerek seçilebilir.
4.
Durum 17.8.2016 tarihinden sonra yapılan satışların Toplamı ve Ortalaması
Koşul olarak aranan bir veri için de ayrıca bir kriter söz konusu ise örneğin belli bir tarihten öncesi, sonrası sayısal bir veriden küçük ya da büyük olma durumları aranıyorsa, mantıksal operatörler kullanılabilir. Önemli olan nokta, mantıksal operatörün çift tırnak içinde yazılıp koşulla (&) karakteri ile birleştirilmesidir.
Dikkat edildiği üzere, Tarih veri setinde gözüktüğü formatta yazılmayabilir.
5. Durum Ortalama Tutarın Üzerindeki Satışların Toplamı
Koşul olarak, eğer herhangi bir formül ya da fonksiyondan dönen değere denk gelip bu değerin altında ve ya üstünde olma durumuna bakılacaksa, formül ya da fonksiyon normal bir şekilde yazılır ve yine bir önceki örnekte olduğu gibi mantıksal bir operatör (“>”,”<“) ve (“&”) karakteri ile birleştirilir.
6.
Durum İçinde “Fren” Kelimesi Geçen Ürünlerin Satış Toplamı ve Ortalaması
Koşul olarak aranan kriter veri tablosunda her zaman doğru bir şekilde yazılmayabilir. Ya da veri setinde bir ürün kodunun, parça numarasının, satış bilgisinin içinde geçen bir takım harf ve ya kelime grubundan oluşabilir. Bu harf ya da kelime grubu, ilgili alanın başında, içinde ya da sonunda bulunabilir. Bu durumda WildCardlar (“*”, “?”) kullanılarak arama yapılması gerekir.
Örneğin Yukarıdaki veri setinde içinde “Fren” kelimesi geçen ürünlerin toplamı hesaplamak istendiğinde, “Fren” kelimesi, hem “Fren Disk” hem de “Fren Balatası” ürünlerin içinde bulunmaktadır.
- Fren ile başlayan Ürünler: Fren*
- İçinde Fren geçen Ürünler: *Fren*
- Fren ile biten Ürünler: *Fren
Şeklinde aratılabilir.
Wildcard’larda (*) her şey, (?) herhangi tek bir karakter demektir. Örneğin Ürün Adı altı (6) karakter uzunluğunda olan kayıtların toplamı alınmak istendiğinde, koşul olarak “??????” yazılabilir.
7. Durum “Fren Disk” ve “Debriyaj Seti” Ürünlerinin Satış Toplamı ve Ortalaması
SUMIF ve AVERAGEIF fonksiyonlarını OR ( Ya da) koşulu ile kullanmak fonksiyonun normal kriterleri ile mümkün değildir. Aynı sütundan birden fazla koşula bağlı olarak alanların toplanması ya da ortalaması istendiğinde, koşullar dizi işlemlerinden bilinen küme parantezleri ({}) içerisinde yazılmalıdır. Ancak bu sayede ilgili koşullara ait verilerin değerleri döndürülebilir. SUMIF fonksiyonu dönen bu değerleri ne toplayabilir ne de ortalamasını alabilir. Elde edilen bu değerler toplam alınacaksa bir SUM(), ortalama alınacaksa da bir AVERAGE() Fonksiyonu içinde yazılmalıdır.
Yukarıdaki örnekte, ürün adı “Fren Disk” olan ürünlerle, “Debriyaj Seti” olan ürünlerinin toplamı ve ortalaması hesaplanmıştır.
İki ayrı SUMIF() fonksiyonu yazmak yerine tek bir SUMIF içinde ilgili koşullar yukarıdaki örnekte olduğu gibi verilebilir.
Ancak SUMIF fonksiyonu yazıldıktan sonra fonksiyonun 
şeklinde bir sonuç döndürdüğü gözlemlenir. Bu değerler, Fren Disk isimli ürünlerin ve Debriyaj Seti isimli ürünlerin ayrı ayrı toplam tutarladır. SUMIF fonksiyonu bu değerleri toplayamaz. Bunun işlem için de SUM fonksiyonu kullanılmalıdır.
Sonuç olarak da istenilen toplam elde edilmiş olur.
Aynı durum, AVERAGEIF fonksiyonu için de geçerlidir. Bütün adımlar, AVERAGEIF fonksiyonunda da tekrarlanabilir.
8. Durum Kubilay Taştutar’ın Yapmış olduğu “Fren Disk” Satışlarının Toplamı ve Ortalaması
SUMIF ve AVERAGEIF fonksiyonları, tek bir koşul verilmek istendiği zaman kullanılır. Birden fazla koşullu Toplama ve Ortalama işlemleri gerçekleştirilmek istendiği zaman, bu iki fonksiyonun çoklu kriter alarak çalışan alternatifleri SUMIFS ve AVERAGEIFS fonksiyonları kullanılır.
SUMIFS ve AVERAGEIFS fonksiyonlarında her parametre seçimi gerçekleştiği zaman yeni parametre penceresini açar. Yukarıdaki Screenshot örneklerinde devam edilecek olsaydı, Criteria_Range3 parametresi açılıp fonksiyona devam edilebilirdi. Her iki fonksiyonda 127 faklı koşula göre toplam ve ortalama alma işlemleri gerçekleştirebilir.
NOT: İlk yedi durumda anlatılan tüm koşul yazma kuralları SUMIFS ve AVERAGEIFS fonksiyonları için de geçerlidir.
9. Durum 22.8.2016 ve 31.8.2016 tarihleri arasında yapılan satışların Toplamı ve Ortalaması
İki tarih arasında yapılan satışların toplamını ya da ortalamasını hesaplamak için farklı yöntemler kullanılabilir. Ancak kullanılan bu yöntemler çoğu zaman esnek ve estetik ya da pratik olmayabilir. İki tarih arasındaki veriler üzerinden hesaplama işlemleri gerçekleştirilmek istendiği zaman, sıklıkla yapılan işlem; Tablo filtrelenir, tablonun altına bir SUM fonksiyonu yazılır ya da ilgili aralık taranıp, durum çubuğundan sonuçların tabili gerçekleştirilir.
İki tarih arasındaki verilerin toplamı ya da ortalamasının alınması;
Şeklinde gerçekleştirilebilir. Sonuçların gözlemlendiği hücrelerin sol üst köşesindeki yeşil ifadeler bir hata değildir. Kriterler verilirken, eğer ana veride gözüken formatta yazılmazsa formatı düzeltme uyarısı verir. Ancak hesaplamada bir değişiklik olmaz.
Sonuç:
Bu makalede bir ve daha fazla koşulda toplama ve ortalama alma işlemlerinde kullanılan SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS fonksiyonlarının parametreleri, farklı durumlara göre nasıl kullanılabileceği, koşulları belirlerken nelere dikkat edilmesi gerektiği örnekler ve ekran görüntüleri ile ayrıntılı bir biçimde açıklanmıştır.
Bu konuyla ilgili sorularınızı alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.
Referanslar
