Excel'de Hızlı Veri Analiz ve Formüller

Yıllarca aynı hatayı yaptım: Excel'i bir hesap makinesi gibi kullandım. Hücreye girip topluyor, çıkarıyor, sonra bir satır eklendiğinde her şeyi baştan yazıyordum. Bir ay sonu raporunu üçüncü kez elden geçirdiğimde anladım ki sorun tabloda değil, benim yaklaşımımdaydı. Formülleri "koşul" mantığıyla kurmaya başladığım gün, üç saatlik iş yirmi dakikaya indi. Aşağıda o dönüşümü sağlayan formülleri ve tabloyu formüle hazır hale getirme yöntemimi anlatıyorum.

Not: Burada anlatılanların hepsini Excel 2016 ve sonrası sürümlerde, Google Sheets'te de büyük ölçüde aynı şekilde kullanabilirsiniz. Fonksiyon adlarınız İngilizce ise SUMIF, ETOPLA olarak Türkçeleşiyor; ikisini de yazacağım ki karışıklık olmasın.

Koşullu Toplama ve Sayma: İşin Yüzde Sekseni Burada Bitiyor

Veri analizinde çoğu soru aslında tek cümleye indirgenebilir: "Şu koşulu sağlayan satırların toplamı ne?" Bunu cevaplayan üç fonksiyonu öğrendiğinizde raporların büyük kısmı kendiliğinden çözülüyor.

Tek koşullu toplama: SUMIF'in doğru kurulumu

Diyelim A sütununda şehir, B sütununda satış tutarı var. İstanbul'un toplam satışını istiyorsanız excel sumif formülü tam olarak bunun için tasarlanmış:

=SUMIF(A2:A500; "İstanbul"; B2:B500) — Türkçe Excel'de =ETOPLA(A2:A500; "İstanbul"; B2:B500)

Mantığı üç parçalı: nerede arayacağım, neyi arayacağım, hangi sütunu toplayacağım. Benim en çok hata yaptığım yer üçüncü parçaydı; aralık boylarını farklı tutmak. A2:A500 yazıp toplama aralığına B2:B450 yazarsanız Excel sessizce yanlış sonuç üretir, uyarı vermez. Bu yüzden ben artık ölçütü hücreye yazıyorum ve formülü şöyle kuruyorum:

=SUMIF($A$2:$A$500; D2; $B$2:$B$500)

D sütununa şehir listesini bir kez yazıp formülü aşağı çekiyorum, dolar işaretleri aralıkları sabitliyor. Bir de şu numara işe yarar: ölçüt olarak ">1000" yazarsanız sayısal karşılaştırma yapar, "*kablo*" yazarsanız içinde "kablo" geçen tüm ürünleri toplar.

Birden fazla koşul olunca: SUMIFS

"İstanbul'daki, Mart ayındaki, 500 TL üzeri satışlar" gibi bir soru geldiğinde SUMIF yetersiz kalır. SUMIFS'te sıra değişiyor, dikkat: toplama aralığı en başa geliyor.

=SUMIFS(B2:B500; A2:A500; "İstanbul"; C2:C500; "Mart"; B2:B500; ">500")

Ben artık tek koşulda bile SUMIFS kullanıyorum, çünkü ileride ikinci bir koşul eklemek gerektiğinde formülü baştan yazmak zorunda kalmıyorum. Aynı mantığın kardeşleri de var:

• COUNTIFS (ÇOKEĞERSAY): Koşula uyan satır sayısını verir. Mükerrer kayıt avında paha biçilmez.
• AVERAGEIFS (ÇOKEĞERORTALAMA): Sadece belirli grubun ortalamasını alır, sıfırları hesaba katmamak için idealdir.
• MAXIFS / MINIFS: Belirli kategoride en yüksek ve en düşük değeri bulur.

Sonuç sıfır çıkıyorsa bakılacak üç yer

SUMIF'in sıfır döndürdüğü durumların neredeyse tamamı veri kaynaklıydı, formül kaynaklı değildi. Sırayla kontrol ettiğim liste:

1. Baştaki/sondaki boşluk. "İstanbul " ile "İstanbul" farklı metinlerdir. TRIM (KIRP) ile temizliyorum.
2. Metne dönmüş sayılar. Hücrenin sol üst köşesinde yeşil üçgen varsa toplama katılmıyor demektir. Sütunu seçip "Metni Sütunlara Dönüştür" adımını sonuna kadar tıklamak çoğu zaman düzeltiyor.
3. Görünmez karakterler. Web'den veya muhasebe programından gelen dışa aktarımlarda sık rastlanır; CLEAN fonksiyonu bunları siler.

Tabloyu Analize Hazır Hale Getirmek

Formülleri öğrendikten sonra fark ettim ki gerçek kazanç, tabloyu doğru kurmakta. Dağınık bir tabloda en zarif formül bile tökezliyor.

Aralık yerine "Tablo" kullanmak

Verinin içindeyken Ctrl+T ile aralığı tabloya çeviriyorum. Bunun getirisi şu: yeni satır eklediğimde aralıklar kendiliğinden büyüyor, formülleri güncellemem gerekmiyor. Ayrıca formül şöyle okunur hale geliyor:

=SUMIFS(Satis[Tutar]; Satis[Sehir]; "İstanbul")

Altı ay sonra dosyayı açtığımda B2:B500'ün ne olduğunu hatırlamaya çalışmıyorum. Bu tek alışkanlık, bakım yükümün yarısını götürdü.

Arama fonksiyonlarıyla iki tabloyu birleştirmek

Satış listesinde ürün kodu var, fiyat listesi ayrı sayfada. VLOOKUP (DÜŞEYARA) burada devreye giriyor, ama sürümünüz destekliyorsa XLOOKUP tercih edin; sütun sayısı saymaktan kurtarıyor ve sola doğru da arama yapıyor.

Arama formüllerini her zaman IFERROR ile sarıyorum: =IFERROR(XLOOKU