Excel'de Koşula Göre Toplama ve Sayma Fonksiyonları

Koşullu toplama ve sayma neden temel bir beceridir?

Ham veri tablosundan anlamlı bir özet çıkarmanın en kısa yolu, belirli bir koşula uyan kayıtları toplamak ya da saymaktır. Bölgeye göre satış toplamı, bir müşterinin bu yılki sipariş adedi, belirli bir durumdaki açık talep sayısı, bir tedarikçiden gelen fatura tutarı gibi sorular bu işlevlerin alanıdır.

Temel işler toplamak ve saymaktır. Toplamak için ETOPLA ve ÇOKETOPLA, saymak için EĞERSAY ve ÇOKEĞERSAY kullanılır. Tek koşul varsa baştaki, birden çok koşul varsa ÇOKETOPLA ve ÇOKEĞERSAY gibi çoklu ölçütlü sürümleri tercih edilir.

Bu işlevleri öğrenmek, pivot tabloya her zaman gerek duymadan özet tablolar ve dashboard göstergeleri kurmanızı sağlar. Pivot tablo esnek ve hızlıdır; koşullu toplama işlevleri ise sonucu sabit bir hücreye, belirli bir mantıkla yerleştirmek istediğinizde daha kontrollüdür.

ETOPLA ve EĞERSAY nasıl çalışır?

Microsoft'un belgesinde ETOPLA'nın söz dizimi ETOPLA(aralık; ölçüt; [toplam_aralığı]) şeklindedir.

• aralık: Ölçüte göre değerlendirilecek hücrelerdir.
• ölçüt: Hangi hücrelerin toplanacağını belirler. Sayı, ifade, hücre başvurusu, metin ya da işlev olabilir. Metin ve karşılaştırma ifadeleri çift tırnak içine alınır.
• toplam_aralığı: Toplanacak asıl hücrelerdir. Atlanırsa ölçütün uygulandığı aralığın kendisi toplanır. Boyutu ve şekli aralıkla aynı olmalıdır.

Örneğin bölge sütunu aralık, Marmara ölçüt, satış tutarı sütunu toplam aralığı olursa Marmara bölgesinin satış toplamını alırsınız.

EĞERSAY ise tek bir ölçüte uyan hücreleri sayar. Microsoft sayfasına göre EĞERSAY yalnızca tek ölçüt kullanır; birden çok ölçüt için ÇOKEĞERSAY'a geçilir. Müşteri sütununda belirli bir müşterinin kaç siparişi olduğunu saymak tipik kullanımdır.

Ölçüt yazımı işin en önemli ayrıntısıdır. Eşitlik için değeri doğrudan yazarsınız. Karşılaştırma için işleci tırnak içinde yazarsınız: büyüktür, küçüktür, büyük eşit ya da eşit değildir gibi. Bir hücredeki sayıyla karşılaştırma yapacaksanız işleci tırnak içinde yazıp hücre başvurusunu et işaretiyle ona bağlarsınız. Joker karakterler metin ölçütlerinde kullanılabilir: yıldız herhangi bir karakter dizisini, soru işareti tek karakteri temsil eder.

ÇOKETOPLA ile birden çok koşulu birleştirmek

ÇOKETOPLA, tüm ölçütleri aynı anda sağlayan kayıtları toplar. Microsoft belgesinin vurguladığı kritik ayrıntı, bağımsız değişken sırasının ETOPLA'dan farklı olmasıdır: ÇOKETOPLA'da toplam aralığı ilk bağımsız değişkendir; ardından her biri bir aralık ve onun ölçütünden oluşan çiftler gelir. Çift sayısı geniş bir sınıra kadar artırılabilir.

Örnek senaryo: Satış tablosunda bölge, ürün kategorisi, ay ve tutar sütunları var. Marmara bölgesindeki Yazılım kategorisinin Mart ayı satışını bulmak için toplam aralığı olarak tutar sütununu, ardından bölge aralığı ile Marmara ölçütünü, kategori aralığı ile Yazılım ölçütünü, ay aralığı ile Mart ölçütünü verirsiniz. Ölçütlerin hepsi sağlanmayan satırlar toplamdan dışlanır.

ÇOKEĞERSAY da aynı yapıyla çalışır, ancak toplam aralığı yoktur: yalnızca aralık ve ölçüt çiftleri verilir ve tüm ölçütleri karşılayan satırların sayısını döndürür. Belgeye göre ölçüt olarak boş bir hücreye başvuru verirseniz işlev bu boş hücreyi sıfır olarak ele alır; boş değerleri aramak istiyorsanız bunu dikkate alın.

Bir koşulun "ya da" mantığıyla birleşmesini istiyorsanız (örneğin Marmara veya Ege), ÇOKETOPLA tek başına yetmez; her seçenek için ayrı ÇOKETOPLA yazıp toplamak ya da ÇOKETOPLA'yı bir dizi sabiti içinde kullanmak gerekir. Bu, bilinen bir mantık tuzağıdır.

Tarih aralığına göre toplama ve çapraz tablo kurma

En yaygın iş senaryolarından biri belirli bir tarih aralığındaki toplamı almaktır. ÇOKETOPLA'da aynı tarih sütunu birden çok ölçütle kullanılabilir: biri başlangıçtan büyük eşit, diğeri bitişten küçük eşit. Başlangıç ve bitiş tarihlerini ayrı hücrelere yazıp ölçütte bu hücrelere başvurursanız tarih aralığı değiştiğinde formüller kendiliğinden güncellenir. Bu, formülü elle düzenlemekten çok daha güvenlidir.

Çapraz tablo kurmak için satır etiketlerini sol sütuna, sütun etiketlerini üst satıra yazın ve içteki hücreye ÇOKETOPLA formülünü bir kez yazıp tüm tabloya çoğaltın. Bunun çalışması için ölçüt başvurularındaki satır ve sütun kilitlerini doğru yapmak gerekir: satır etiketi başvurusunda sütun sabit, sütun etiketi başvurusunda satır sabit olmalıdır. Karma başvuru adı verilen bu teknik, tabloyu tek formülle doldurmayı sağlar.

Aylık özetler için tarih sütununu ay bilgisine çeviren bir yardımcı sütun eklemek, ölçütleri sadeleştirir. Böylece formül yazımı da kısalır, hata ayıklama da kolaylaşır.

Sık yapılan hatalar ve çözümleri

• Aralıkların farklı boyutta olması. ÇOKETOPLA'da tüm aralık bağımsız değişkenleri aynı boyutta olmalıdır; aksi halde #DEĞER! hatası alırsınız. Microsoft'un hata düzeltme sayfası da aralıkların uyumuna dikkat çeker.
• Sayı ile metin uyuşmazlığı. Sayı gibi görünen ama metin olarak saklanan değerler ölçütle eşleşmez. Çözüm: veri türünü düzeltmek.
• Fazladan boşluklar. Marmara ile Marmara boşluk farklı metindir; toplam eksik çıkar. Çözüm: önce veriyi temizlemek.
• Ölçütü tırnaksız yazmak. Karşılaştırma ifadeleri çift tırnak içinde yazılmalıdır.
• Uzun metin ölçütleri. ÇOKEĞERSAY sayfasına göre ölçüt dizesi belirli bir uzunluğu aşarsa #DEĞER! hatası gelir; dizeyi kısaltmak ya da BİRLEŞTİR işlevi veya et işaretiyle bölmek önerilir.
• Aralığı kaydırmak. Formülü aşağı çekerken aralıkların kaymaması için mutlak başvuru ya da Excel tablosu kullanın.
• Tarihi metin olarak yazmak. Ölçütte tarih kullanıyorsanız hücre başvurusu üzerinden vermek, biçim kaynaklı sorunları azaltır.

Çok koşullu ve büyük veri tablolarında formül sayısı artarsa çalışma kitabı yavaşlayabilir. Bu durumda özet için pivot tabloya geçmek akıllıca olur.

Bu beceriyi nerede geliştirirsiniz?

Koşullu toplama ve sayma, EĞER mantığının özetleme boyutudur. Temel için Excel EĞER fonksiyonu, birleştirme ve eşleştirme için DÜŞEYARA ve XLOOKUP, hata ayıklamak için Excel formül hataları sayfalarına bakabilirsiniz. Daha genel formül çerçevesi Excel formülleri ve fonksiyonları sayfasında bulunur.

Mindset kataloğunda Excel Orta Düzey programının müfredatı Koşullu Fonksiyonlar ve Çok Katmanlı Hesaplamalar, Pivot Tablo Ustalığı ve İleri Arama ve Basit Referans Formülleri modüllerini içerir. Microsoft Excel ile Veri Analizi Eğitimi - İleri Excel (Uygulamalı) ise Veri Analizi ve İleri Formüller ile Veri Tabloları ve PivotTabloları modüllerini kapsar.