Farklı İşlevleri İç İçe Kullanarak Formül Oluşturma

İç içe formül (nested formula), bir işlevin sonucunu başka bir işlevin bağımsız değişkeni olarak kullanarak tek hücrede çok adımlı hesap yapmanızı sağlar. Örneğin EĞER işlevinin koşulunu VE ile genişletebilir, DÜŞEYARA sonucunu EĞERHATA ile güvenceye alabilir ya da ETOPLA sonucunu başka bir işlemin içine yerleştirebilirsiniz. Excel formülü içten dışa doğru hesaplar; bu yüzden doğru sırayı ve doğru referans türünü seçmek sonucun doğruluğunu belirler.

İç içe işlev nedir ve nasıl çalışır​

İç içe işlev, bir işlevin bağımsız değişkeni olarak başka bir işlevin yazılmasıdır. Excel önce en içteki işlevi hesaplar, çıkan sonucu bir üstteki işleve verir ve bu zincir en dıştaki işleve kadar sürer. Böylece ara sonuçları ayrı sütunlara yazmadan tek hücrede karar verebilirsiniz.

Aşağıdaki formülde önce VE işlevi iki koşulu sınar, sonuca göre EĞER işlevi metni döndürür:
İç içe kullanımda bilmeniz gereken sınırlar şunlardır:

• Düzey sınırı: Excel 2007 ve sonraki sürümlerde bir formülde 64 düzeye kadar iç içe işlev kullanılabilir.
• Uzunluk sınırı: Bir formül en fazla 8192 karakter olabilir.
• Parantez düzeni: Her açılan parantez kapanmalıdır; eksik ya da fazla parantez formülün kabul edilmemesine neden olur.
• Ayırıcı: Türkçe Excel'de bağımsız değişkenler noktalı virgülle ayrılır, ondalık ayırıcı virgüldür. İngilizce Excel'de ayırıcı virgül, ondalık ayırıcı noktadır.

İç içe formülde mutlak ve göreli referans kullanımı​

İç içe formülü aşağı ya da yana kopyalayacaksanız hangi başvurunun kayacağına, hangisinin sabit kalacağına baştan karar vermeniz gerekir. Göreli referans (A1) kopyalandığı yere göre değişir, mutlak referans ($A$1) hiç değişmez, karma referanslar ($A1 veya A$1) ise yalnızca sütunu ya da yalnızca satırı sabitler.

Bir formül yazarken başvuruyu seçip F4 tuşuna basarak referans türleri arasında dönebilirsiniz. Genel kural şudur:

• Arama tabloları: DÜŞEYARA ve ETOPLA gibi işlevlerdeki tablo ya da ölçüt aralığını mutlak yazın, böylece kopyalamada aralık kaymaz.
• Satır bazlı değerler: Her satırda değişmesi gereken hücreleri (B2, D2 gibi) göreli bırakın.
• Çapraz tablolar: Hem satır hem sütun başlığına başvuruyorsanız karma referans kullanın.

Aralıklar veri eklendikçe büyüyecekse aralığı Excel tablosuna (Ekle > Tablo) dönüştürmek, başvuruların veriyle birlikte genişlemesini sağlar.

İç içe formülleri adım adım kurma ve hata ayıklama​

Uzun bir formülü tek seferde yazmak yerine en içteki işlevi ayrı bir hücrede deneyin, doğru çalıştığını gördükten sonra bir üst işlevin içine taşıyın. Bu yöntem hatayı bulmayı çok kolaylaştırır.

Hata ayıklamak için şu araçları kullanabilirsiniz:

• Formülü Değerlendir: Formüller > Formül Denetimi > Formülü Değerlendir (Formulas > Formula Auditing > Evaluate Formula) komutu formülü içten dışa adım adım hesaplayıp her ara sonucu gösterir.
• F9 tuşu: Formül çubuğunda formülün bir bölümünü seçip F9 tuşuna basarsanız o bölümün sonucunu görürsünüz. İşlemden sonra Esc tuşuyla formülü bozmadan geri dönün; Enter basarsanız seçili bölüm sonuç değeriyle değişir.
• Satır başı: Formül çubuğunda Alt+Enter ile satır atlayarak uzun formülü okunur hale getirebilirsiniz.
• Hata yakalama: Arama gibi hata verebilecek işlemleri EĞERHATA ile sarmalayın; ancak her hatayı gizlemek gerçek sorunları da saklayabilir.

1. EĞER ile VE işlevini iç içe kullanma​

Satış temsilcilerinin satış tutarı, hedefi ve müşteri memnuniyet oranı listelenmiştir. Prim için hem hedefin tutturulması hem de memnuniyetin en az %80 olması gerekir; hedefin %90'ına ulaşanlar ise takip listesine alınır. E sütunundaki formül EĞER işlevinin içinde VE işlevini ve bir ikinci EĞER işlevini kullanır. E2 hücresine şu formül yazılıp aşağı kopyalanır: EĞER(VE(B2>=C2;D2>=0,8);"Prim Hak Etti";EĞER(B2>=C2*0,9;"Takip";"Prim Yok")).
Ayşe Yılmaz hem hedefi hem memnuniyet şartını sağladığı için E2 hücresinde Prim Hak Etti sonucu görünür. Ali Çelik hedefi aşmasına rağmen memnuniyeti düşük olduğu için ilk koşulu geçemez ve ikinci EĞER devreye girerek E5 hücresinde Takip sonucunu verir. Hasan Şahin için ise E7 hücresi Prim Yok döner.

2. DÜŞEYARA sonucunu EĞERHATA ile güvenceye alma​

A:C sütunlarında ürün fiyat listesi, D sütununda sipariş verilen ürün kodları, E sütununda adetler vardır. F sütunundaki formül DÜŞEYARA ile birim fiyatı bulur, adetle çarpar ve kod listede yoksa hata yerine bir uyarı metni verir. F2 hücresine şu formül yazılıp aşağı kopyalanır: EĞERHATA(DÜŞEYARA(D2;$A$2:$C$7;3;YANLIŞ)*E2;"Kod bulunamadı"). Fiyat tablosu mutlak referansla sabitlenmiştir.
K103 kodlu ürün için F2 hücresi 24.000,00 sonucunu verir. Listede olmayan K110 kodu normalde DÜŞEYARA işlevinde bir hata üretirdi; EĞERHATA sayesinde F5 hücresinde Kod bulunamadı metni görünür. Tablo aralığı $A$2:$C$7 olarak sabitlendiği için formül aşağı kopyalansa da aralık kaymaz.

3. YUVARLA, ETOPLA ve TOPLA ile bölge payı hesaplama​

A:C sütunlarında bölge ve aya göre satışlar yer alır. E sütununda bölge adları, F sütununda her bölgenin toplam satıştaki payı hesaplanır. F2 hücresine şu formül yazılıp aşağı kopyalanır: YUVARLA(ETOPLA($A$2:$A$9;E2;$C$2:$C$9)/TOPLA($C$2:$C$9);2). Burada ETOPLA bölgenin toplamını, TOPLA genel toplamı verir; bölme sonucu YUVARLA ile iki ondalığa yuvarlanır ve yüzde olarak biçimlendirilir.
Marmara bölgesinin payı F2 hücresinde %39, Ege bölgesinin payı F3 hücresinde %20 olarak görünür. Bölge adı E sütunundan göreli, veri aralıkları ise mutlak referansla alındığı için formül F5 hücresine kadar kopyalandığında her bölge kendi toplamını hesaplar. Akdeniz bölgesinin payı F5 hücresinde %16 sonucunu verir.

Sık sorulan sorular​

Bir formülde en fazla kaç işlev iç içe kullanılabilir? Excel 2007 ve sonrasında 64 düzeye kadar. Pratikte 3-4 düzeyden fazlası formülü okunmaz hale getirir.

İç içe EĞER yerine ne kullanabilirim? Çok sayıda koşul varsa Excel 2019 ve Microsoft 365'teki ÇOKEĞER (IFS) işlevi ya da bir aralıkta arama yapan DÜŞEYARA daha sade bir çözüm sunar.

Formülüm neden Excel'de kabul edilmiyor? En sık nedenler eksik parantez, yanlış ayırıcı (noktalı virgül yerine virgül gibi) ve tırnaksız yazılmış metinlerdir.

İç içe formülü kopyalayınca sonuç neden bozuluyor? Çoğunlukla tablo aralığı mutlak yazılmadığı için aralık kayar; aralık başvurularını F4 ile sabitleyin.

Kullanırken dikkat edin​

• Önce içteki işlevi sınayın: Uzun bir formülü yazmadan önce en içteki işlevi ayrı bir hücrede deneyin. Doğru sonucu verdiğini gördükten sonra dıştaki işleve taşıyın; hata yaptığınızda nerede olduğunu bulmak çok kolaylaşır.
• Parantezleri ve ayırıcıları kontrol edin: Türkçe Excel'de bağımsız değişkenler noktalı virgülle ayrılır ve ondalık ayırıcı virgüldür. Formül çubuğunda bir parantezin üzerine geldiğinizde eşleşen parantez renkli görünür; bu, eksik ya da fazla parantezi yakalamanıza yardımcı olur.
• Referansları sabitlemeyi unutmayın: Arama tabloları ve ölçüt aralıkları kopyalamada kayabilir. Bu aralıkları F4 tuşuyla mutlak referansa çevirin; satır bazlı hücreleri ise göreli bırakın.
• EĞERHATA her sorunu çözmez: EĞERHATA tüm hataları aynı şekilde gizler. Yanlış yazılmış bir aralık ya da bozuk bir başvuru da mesaj arkasında kalabilir. Formülü kurarken önce hatasız çalıştığından emin olun, EĞERHATA'yı en son ekleyin.
• Okunabilirliği koruyun: Çok düzeyli EĞER yapıları zamanla yönetilemez hale gelir. Çok sayıda koşulunuz varsa ÇOKEĞER işlevini, başka bir tabloda eşleştirme mümkünse DÜŞEYARA'yı tercih edin; ara hesapları ayrı sütunlara bölmek de formülü sadeleştirir.
• İlgili konular:
  • EĞER, VE, VEYA ve ÇOKEĞER işlevleri
  • DÜŞEYARA, YATAYARA ve EĞERHATA işlevleri
  • ETOPLA, ÇOKETOPLA, EĞERSAY ve TOPLA işlevleri
  • YUVARLA ve SEÇ işlevleri
  • Mutlak, göreli ve karma referanslar
  • Formüller > Formül Denetimi > Formülü Değerlendir (Formulas > Formula Auditing > Evaluate Formula)

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.