Excel Formülleri: En Çok Kullanılan 30+ Formül Rehberi

Bordro tablosunda 40 satır maaş verisi varsa toplamı tek tek elle toplamazsınız, formül yazarsınız. En çok kullanılan Excel formülleri aslında 25-30 formülden ibaret, geri kalanı nadiren işe yarar. Bu yazıda o çekirdek listeyi kategori kategori, gerçek sözdizimiyle ve gerçek senaryolarla anlatıyorum.

Formül yazarken en çok zaman, hangi formülün ne işe yaradığını hatırlamaya çalışırken kaybedilir. Aşağıdaki başlıklar sırasıyla temel matematikten koşullu hesaplamaya, aramadan metin temizliğine kadar gerçek masabaşı senaryolarını takip eder.

SUM, ORTALAMA ve sayma formülleriyle işe başlamak

Bir stok tablosunda B sütununda 200 ürünün adedi, C sütununda birim fiyatı varsa toplam ciroyu bulmak için tek satır yeter.

=SUM(C2:C201)
=AVERAGE(C2:C201)
=MAX(C2:C201)
=MIN(C2:C201)

SUM negatif sayıları da toplar. AVERAGE boş hücreleri hesaba katmaz ama sıfır içeren hücreleri hesaba katar; bu ikisi sık karıştırılan bir ayrımdır. MAX ve MIN kısa ama pratik formüllerdir, en yüksek satışın yapıldığı günü ya da en düşük stok seviyesini anında gösterir. Bu iki formülü koşullu biçimlendirmeyle birleştirmek, dikkat çekmesi gereken satırı gözle taramaktan çok daha hızlı sonuç verir.

Sayma tarafında üç formül var, her biri farklı bir şeyi sayar. COUNT yalnızca sayısal hücreleri sayar. COUNTA metin dahil her dolu hücreyi sayar. COUNTBLANK ise boş bırakılan hücreleri bulur.

=COUNT(B2:B201)
=COUNTA(A2:A201)
=COUNTBLANK(D2:D201)

Bu üçünü aynı tabloda yan yana kullanmak veri kalitesini hızlı kontrol etmenin pratik bir yoludur. COUNTA ile COUNT arasındaki fark, kaç hücrenin metin ya da hatalı veri içerdiğini anında gösterir. Ondalıklı sonuçları temizlemek için ROUND kullanılır. =ROUND(A2,2) sonucu iki basamağa yuvarlar, =ROUND(A2,0) ise tam sayıya indirir.

Fiyat hesaplarında ROUND kullanılmadan bırakılan formüller, toplam alındığında kuruş farkları yüzünden tutmayan bir tablo üretir. Muhasebe dosyalarında bu küçük fark, ay sonunda saatler süren bir hata avına dönüşebilir. ROUND'u formülün en dışına sarmak bu riski baştan kapatır.

Koşullu hesaplama: IF, IFS, SUMIF, COUNTIF

IF formülü tek koşul için yeterlidir ama iç içe IF yazmak okunaksız hale gelir. Üç ve üzeri koşulda IFS daha temiz çalışır.

=IF(C2>100,"Stokta Var","Stokta Yok")
=IFS(B2>=85,"AA",B2>=70,"BA",B2>=50,"CB",TRUE,"FF")

Öğrenci not tablosunda harf notu hesaplarken IFS her koşulu sırayla dener ve ilk doğru olanı döndürür. Sona eklenen TRUE koşulu, hiçbir koşul tutmadığında devreye giren bir güvenlik ağı gibi çalışır. AND ve OR bu koşulları birleştirmek için kullanılır. =IF(AND(B2>50,C2="Devam Etti"),"Geçti","Kaldı") örneğinde iki şart aynı anda sağlanmadıkça sonuç "Kaldı" döner.

Formül hata verdiğinde tabloyu #DIV/0! ya da #N/A ile doldurmak yerine IFERROR ile temiz bir mesaj gösterilebilir. Paylaşılan bir dosyada kırmızı hata kodları yerine "Hesaplanamadı" gibi anlaşılır bir metin görmek, dosyayı açan kişinin telaşlanmasını önler.

=IFERROR(A2/B2,"Hesaplanamadı")

Koşullu toplama ve sayma, gerçek iş hayatında en sık kullanılan formül grubudur, çünkü çoğu rapor zaten bir filtre mantığı üzerine kuruludur.

=SUMIF(B:B,">100",C:C)
=COUNTIF(A:A,"İstanbul")
=SUMIFS(D:D,B:B,"İstanbul",C:C,">500")
=AVERAGEIF(B:B,"Satış",C:C)

SUMIF tek koşulla toplar, SUMIFS ise birden fazla koşulu aynı anda uygular. Yukarıdaki SUMIFS örneği İstanbul şubesinde 500 TL üzeri olan tüm satışları toplar. Şube, bölge, tarih aralığı gibi çok kriterli raporlarda bu formül olmadan iş bitmez. COUNTIF de benzer mantıkla çalışır, sadece toplama yerine sayma yapar; örneğin kaç siparişin İstanbul'dan geldiğini tek satırda verir.

Arama formülleri: VLOOKUP'tan XLOOKUP'a

Ürün kodundan fiyat çekmek, kimlik numarasından personel bulmak gibi işlerde arama formülleri devreye girer. VLOOKUP en eski ve en tanıdık olanıdır ama tek yönlü çalışır, yalnızca sağa doğru arar.

=VLOOKUP(A2,Tablo1,3,YANLIŞ)
=INDEX(C:C,MATCH(A2,A:A,0))
=XLOOKUP(A2,Tablo1[Ürün Kodu],Tablo1[Fiyat])

VLOOKUP'taki dördüncü parametre kritiktir. YANLIŞ (veya 0) tam eşleşme ister; boş bırakılırsa Excel en yakın değeri döndürür ve bu genelde yanlış sonuç üretir. Pek çok "VLOOKUP çalışmıyor" şikayeti aslında bu parametrenin unutulmasından kaynaklanır.

INDEX/MATCH ikilisi VLOOKUP'ın sola arama yapamama sorununu çözer, çünkü MATCH sütun konumunu bulur, INDEX de o konumdaki değeri getirir. Bu ikili ayrıca yeni bir sütun eklendiğinde VLOOKUP gibi hata vermez, çünkü MATCH konumu yeniden hesaplar.

XLOOKUP, Microsoft 365 ve Excel 2021 ile gelen daha yeni bir formüldür. VLOOKUP'ın iki zayıf noktasını birden kapatır, hem sola arama yapar hem de bulunamayan değer için varsayılan bir mesaj tanımlamaya izin verir. Ayrıca üçüncü parametreden sonra yaklaşık eşleşme, sıralama gibi ek seçenekler sunar; bu yüzden büyük tablolarda VLOOKUP'a göre daha az hataya açık kabul edilir.

=XLOOKUP(A2,A:A,B:B,"Bulunamadı")

Eski dosyalarla uyumluluk gerekmiyorsa yeni tablolarda doğrudan XLOOKUP'a geçmek zaman kazandırır. HLOOKUP ise nadiren kullanılır ama veri yatay dizildiğinde, yani başlıklar satırda değil sütunda sıralandığında işe yarar. Mantığı VLOOKUP ile birebir aynıdır, sadece arama yönü aşağıya doğrudur.

Metin formülleriyle veri temizleme

Dışarıdan aktarılan veri neredeyse hiç temiz gelmez; fazladan boşluk, tutarsız büyük-küçük harf, birleşik hücreler yaygın sorunlardır. Metin formülleri tam da bu sorunları çözmek için vardır.

=LEFT(A2,3)
=RIGHT(A2,4)
=MID(A2,4,5)
=TRIM(A2)
=TEXTJOIN(" ",TRUE,B2:D2)

LEFT, RIGHT ve MID bir metnin belirli bir bölümünü keser. Örneğin ürün kodunun ilk üç karakterinden kategori çıkarmak =LEFT(A2,3) ile yapılır. MID ise ortadan bir parça almak istendiğinde devreye girer; ikinci parametre başlangıç konumunu, üçüncü parametre kaç karakter alınacağını belirtir.

TRIM metnin başındaki, sonundaki ve kelimeler arasındaki fazla boşlukları temizler. Dış kaynaklı Excel dosyalarında neredeyse her sütuna uygulanması gereken bir formüldür. Bir web formundan indirilen isim listesinde görünmeyen fazladan boşluklar yüzünden aynı kişi iki farklı satırmış gibi görünebilir; TRIM bu sorunu tek seferde çözer.

Sık kullanılan diğer metin formülleri şunlardır:

• =LEN(A2): hücredeki karakter sayısını verir, kısıtlı alan kontrolünde kullanılır
• =UPPER(A2) / =LOWER(A2) / =PROPER(A2): sırasıyla büyük harf, küçük harf, baş harfleri büyük yapar
• =SUBSTITUTE(A2,"-","/"): bir karakteri başka bir karakterle değiştirir
• =TEXTJOIN(" ",TRUE,B2:D2): birden fazla hücreyi tek bir metinde birleştirir, boş hücreleri atlar

TEXTJOIN, eski CONCATENATE formülünün yerini aldı; çünkü aralarına ayraç koyma ve boş hücreleri otomatik atlama özelliği var. Ad, soyad ve şehir sütunlarını tek bir adres satırında birleştirirken CONCATENATE her boş hücrede fazladan boşluk bırakır, TEXTJOIN bırakmaz. SUBSTITUTE de küçümsenmemesi gereken bir formüldür; telefon numaralarındaki tireleri kaldırmak ya da eski bir ürün kodu formatını yeniye çevirmek gibi toplu düzeltmelerde tek satırda işi bitirir.

Tarih formülleriyle süre ve yaş hesaplama

İnsan kaynakları tablolarında kıdem hesaplama, öğrenci tablolarında yaş hesaplama gibi işler tarih formülleriyle çözülür. DATEDIF resmi olarak belgelenmemiş ama hâlâ çalışan, oldukça kullanışlı bir formüldür.

=TODAY()
=DATEDIF(B2,TODAY(),"Y")
=DATEDIF(B2,TODAY(),"YM")
=YEAR(B2)
=WEEKDAY(B2)

DATEDIF'in üçüncü parametresi ölçü birimini belirler. "Y" tam yıl, "M" tam ay, "D" tam gün sayısını verir. İşe giriş tarihi B2'de duran bir çalışanın kıdemini yıl ve ay olarak göstermek için iki DATEDIF formülü aynı hücrede TEXTJOIN ile birleştirilebilir; sonuç "3 yıl 7 ay" gibi okunabilir bir metin olur.

TODAY() ve NOW() dosya her açıldığında güncellenir; bu yüzden rapor tarihini sabitlemek gerekiyorsa formül yerine değerin kendisi yapıştırılmalıdır. Aksi halde bir ay önce gönderilen raporu açan kişi, formülün o gün yeniden hesaplandığını fark etmeden yanlış bir tarihle karşılaşır.

YEAR, MONTH, DAY bir tarihten tek bir parça çekmek içindir; doğum tarihinden sadece yılı ayırmak ya da fatura tarihinden ayı gruplamak için kullanılır. WEEKDAY ise bir tarihin haftanın hangi günü olduğunu sayı olarak döndürür; vardiya planlamasında ya da hafta sonu satışlarını ayıklarken işe yarar.

Dinamik dizi formülleri: FILTER, SORT, UNIQUE

Microsoft 365 abonelik sürümüyle gelen dinamik dizi formülleri, klasik filtre ve gelişmiş filtre menülerinin çoğu işini tek satır formüle indirdi. Sonuç otomatik olarak komşu hücrelere taşar; ayrı bir alan seçmeye gerek kalmaz.

=FILTER(A2:D200,C2:C200>500)
=UNIQUE(A2:A200)
=SORT(A2:B200,2,-1)

FILTER belirtilen koşula uyan satırları ayrı bir alana döker; yukarıdaki örnek 500 TL üzeri tüm satışları listeler ve kaynak veri değiştikçe otomatik güncellenir. Klasik filtre menüsünden farkı, sonucun ayrı bir hücre bloğunda canlı kalmasıdır.

UNIQUE bir sütundaki tekrar eden değerleri ayıklayıp benzersiz listeyi çıkarır; müşteri listesinden mükerrer kayıtları bulmak için pivot tabloya gerek bırakmaz. SORT ise belirtilen sütuna göre sıralama yapar. Üçüncü parametredeki -1 azalan sırayı, 1 ise artan sırayı ifade eder.

Bu üç formül iç içe de kullanılabilir; gerçek raporlarda genellikle bu şekilde kullanılır.

=SORT(FILTER(A2:D200,C2:C200>500),3,-1)

Bu tek satır önce filtreler, sonra sıralar; 500 TL üzeri satışları alır ve üçüncü sütuna göre büyükten küçüğe dizer. FILTER, SORT ve UNIQUE yalnızca Microsoft 365 abonelik sürümünde çalışır. Eski sabit lisanslı Excel 2016 veya 2019 dosyalarında bu formüller tanınmaz ve #AD? hatası verir.

Bu üç formül, klasik gelişmiş filtre ve pivot tablo kombinasyonunun yerini büyük ölçüde alıyor. Stok listesi, öğrenci not tablosu veya satış raporu gibi düzenli güncellenen dosyalarda tek satırlık dinamik formül, filtreyi her seferinde yeniden kurmaktan çok daha az zaman alır.

Formülleri öğrenirken en çok zaman kazandıran alışkanlık, hataya karşı savunma eklemektir. XLOOKUP veya VLOOKUP'ı doğrudan yazmak yerine =IFERROR(XLOOKUP(A2,A:A,B:B),"Kod Hatalı") şeklinde IFERROR içine sarmak, boş veya hatalı hücrelerin tüm tabloyu #YOK hatasıyla doldurmasını engeller ve tabloyu ilk bakışta güvenilir gösterir.

Yazan Celil Uyanikoglu

Bilgisayar mühendisiyim; 25 yılı aşkın süredir bilgi işlem sektörünün içindeyim. Bu blogu 2020'de, işimde her gün karşılaştığım sorunların çözümlerini bir yere yazmak için açtım: Linux, güvenlik, tarayıcılar, yapay zeka araçları. Yazdığım her rehberi önce kendi bilgisayarımda ya da sunucumda deniyorum; çalıştığını görmediğim adımı yayınlamam. Hatalı ya da eskimiş bir şey görürsen iletişim sayfasından yaz — düzeltirim.