Lookup(Arama) Fonksiyonları

Giriş

Vlookup, bana göre ortalama bir Excel kullancısı için en önemli fonksiyondur. Gerçi zaman geçtikçe bunun da eksikliklerini görecek ve alternatif yollar aramanız gerekecek. Bu kısımada öncelikle Vlookup'ı daha etkin nasıl kullanırız, ona bakacağız; sonrasında da kendisinin yetersiz kaldığı durumlarda neler yapabileceğimize.

Vlookup(aranan,aramayeri,kaçıncıkolon,[eşleşmetipi]) şeklinde bir syntaxa sahip olan Vlookup'ta arama işlemi "arama yeri"ndeki ilk kolonda yapılır. Aranan değer bulunduğununda, buna karşılık gelen esas istediğimiz sonuç, ilgili alanın belirtilen kolonunda bulunur ve getirilir. Eğer aranan değer ilk kolonda bulunamazsa formül sonucu #N/A döner.

Arama alanı

Arama alanı olarak, tüm kolon seçilebileceği gibi $B$1:$C$10 şeklinde sadece ilgili alanın seçimi şeklinde de olabilir. Ayrıca bir Table'a da lookup işlemi yapılabilir(Table detayları için buraya bakabilirsiniz.) Yine Named Range kullanımı da okuma kolaylığı sağlayacaktır.

Aşağıda çeşitli vlookup işlemleri görülmektedir

Kolon indexi ve sağa doğru kaydırma

Vlookup formülünüzü yazarken genelde sabit bir kolon indeksi gireriz. Formülümüzü kaydırarak kopayalasak bile bu indeks sabit olduğu için hep aynı kalır. Ancak, bu formül içeren hücreyi sağa doğru kaydırırken kolon indeksinin de artarak gitmesini istediğimiz durumlar olacaktır. Aşağıdaki tabloda olduğu gibi; en sağ hücredeyim ama indeks hala 2.

Böyle durumlarda genelde arama alanının üstüne yardımcı bi satır açılır ve kolon indeksi oradan alınır.

Bu bir çözümdür ancak şık bir çözüm değildir. Ana sayfada ne demiştik, işleri sadece doğru yapmak yetmez, hızlı ve zarif de yapmalıyız. Gerçi  bu yöntem hızlıdır, ama zarif değildir. Eğer acil ve/veya geçici bir bilgiye ihtiyacınız varsa bu tür yöntemler kabul edilebilir. Ancak daha kalıcı bir çözüm istiyorsak böyle yöntemler uygun değildir. Zira bu sayfayı print almak istediğimizde bu yardımcı satır da basılır ve bu da çok hoş olmaz.

Böyle bir durumda COLUMN veya daha iyisi MATCH formülünden faydalanabiliriz. Önce COLUMN yöntemine bakalım. (Bu fonksiyonu daha detaylıca aşağıda ele alacağız.)

=VLOOKUP(aranan,alan,COLUMN()-/+x,0)

Bu örnekte x yerine birşey yazmak gerekmedi ancak bazen kolonun yerine göre bir değer girmek gerekebiliyor. Column(M2)-5 gibi.

İkinci çözüm MATCH fonksiyonu ile sağlanır. Üstelik bunda COLUMN'da olduğunun aksine +/- bir diğer girme zorunluluğu yoktur.

=VLOOKUP($A10,$A$1:$M$5,MATCH(B9;$B$1:$M$1;0),0)

Burda MATCH, Ocak ayının B1:M1 içindeki yerine bakıyor, kaçıncı kolonda olduğunu bulup onu getiriyor, yani 2'yi. Formülü kaydırdığımızda da diğer ayların kolon sırasını da ona göre getiriyor.

MATCH'in COLUMN veya yardımcı satır yöntemine göre bir üstünlüğü de  kolonların yeri değişse bile doğru sonuç getirmesidir. Diğer iki yöntemde ise kolonların yeri değişirse, mesela aylar alfabetik sıralı gelse veya Aralıktan Ocak'a doğru sıralanmış olsa, formül hatalı sonuç getirir.

Bir diğer alternatif de aşağıdaki gibi kayarak ilerlemeyi sağlayan bir UDF yazmaktır. UDF açıklamlarını fonksiyonlar sayfasında detaylıca ele  alacağımız için burada tekrar yapmak istemedim.

Süperlookup UDF'i için tıklayınız

Function süperlookup(alan As Range, sütun As Range, aranan As Range)
'paramterlerin sırası klasik vlookupa göre farklıdır
On Error GoTo hata
    süperlookup = alan.Columns(1).Find(aranan, lookat:=xlWhole).Offset(0, sütun.Column - alan.Columns(1).Column).Value
    Exit Function
hata:
    süperlookup = "Bulunamadı"
End Function

Eşleşme tipi

Vlookup'ın son parametresi genellikle tam eşleşmeyi sağlamak için 0 (veya False) şeklinde girilir. Ancak bize tam eşleşme değil de en yakın eşleşme lazımsa bu değer 1 (veya True) olarak girilebilir ya da bu parametrenin varsayılan değeri zaten 1 olduğu için hiç girilmeyebilir. En yakın eşleşme olarak kendisinden en küçük değere bakar. Böyle bir duruma ne zaman ihtiyacımız olur bunu birazdan göreceğiz.

Yanlız burada dikkat edilmesi gereken bir nokta var, o da eğer parametre olarak 1 kullanılacaksa arama alanının sıralı olmasına dikkat edilmelidir. Aksi halde aşağıdaki gibi beklenmeyen sonuçlar çıkabilir. Hatta işin pis tarafı, bazen çıkan sonuç bir hata değil, gayet normal beklenen değerler olabilir ama yanlıştır(Aşağıdaki örnekte 500 çıkması gerekirken 105 çıkması gibi).

=VLOOKUP(A2;D:E;2) //B2'deki formül

Bu yöntem şu şekilde işler. Listede en yukardan aramaya başlar, kendinden küçük bir değer görünce bunu bi kenara yazar, sonra bi aşağı satıra bakar, hala kendinden küçük ama bi öncekinden büyükse bu sefer yeni satırdaki değeri alır, ta ki kendinden büyük bir değere denk gelinceye kadar. O anda durur ve o ana kadar kendisinden küçük olup ona en yakın değer hangisiyse onu baz alır. Bu yukardaki örnekte 2'yi arıyoruz, 2 aşağılarda kalmış. En yukarda 1'i görür, sonraki değer 10 olup 2den büyük olduğu için aramayı durdurur. Gördüğünüz gibi son parametrenin 1 olarak kullanımı tehlikelidir ve çok dikkat gerektirir.

İçiçe IF yerine Vlookup

Vlookup'ın son parametresinin 1 olarak kullanılması tehlikelidir tehlikeli olmasına ama doğru kullanıldığında bizi bir sürü dertten kurtarır. Bunlardan biri de içiçe IF formülü yazmaktan kurtarması durumudur. Aşağıdaki örnek üzerinden gidelim.

//bu formül yerine
=IF(A2<2,7;2,73;IF(A2<2,85;2,877;IF(A2<3;3,024;IF(A2<3,15;3,171;IF(A2<3,3;3,318;IF(A2<3,45;3,465;IF(A2<3,6;3,612;IF(A2<3,75;3,759;IF(A2<3,9;3,906;IF(A2<4,05;4,053;4,054))))))))))

//bu formül çok daha kısa ve dolayısıyla verimlidir
=VLOOKUP(A2;F:G;2;1)

Dikkat ettiyseniz F kolonundaki arama alanı sıralıdır. Bunu bir önceki kısımda özellikle vurgulamıştık.

Arama alanında araya kolon ekleme

Hali hazırda Vlookup uyguladığınız bir sayfada, arama alanında araya bir yere yeni kolon eklenirse ve bu yeni kolonun sağında kalan kolonlardan bir data çekiyorsanız bunlar patlar. Çünkü formülün içindeki kolon index numarası değişmez.

=VLOOKUP(A2;E:F;2;0)

Şimdi E:F arasına 3 kolon ekleyelim, E:F'nin E:I odluğunu ama 2'nin değişmediğini görüyoruz.

=VLOOKUP(A2;E:I;2;0)

Bunu engellemek için MATCH fonksiyonunu formülün içine eklemekte fayda var. Formülü MATCH kullanarak yazalım:

=VLOOKUP(A2;E:F;MATCH($B$1;$E$1:$F$1;0);0)

Şimdi E:F arasına 3 kolon ekleyelim. Formül hala doğru çalışıyordur, zira MATCH'li formül otomatikman genişledi.

=VLOOKUP(A2;E:I;MATCH($B$1;$E$1:$I$1;0);0)

Bir diğer seçenek de INDEX-MATCH ikilisini kullanmaktır, ki bunu aşağıda göreceğiz.

Hızlı Vlookup için optimizasyon çalışması

Vlookup çok faydalı bir fonksiyon olmakla birlikte, özellikle büyük data kümelerinde kullanımı dikkat gerektirir. Bunun için bazı tüyolarımız olacak.

• Arama listesi mümkünse aynı dosyada, hatta mümkünse aynı sayfada olsun. Özellikle geçici bir lookup işlemi yapacaksanız geçici veriyi aynı sayfaya alabilir ve sorasında silersiniz.
• Arama alanı olarak her ne kadar tüm kolon seçimi(B:E gibi) daha pratik olsa da performans kaygınız varsa sadece ilgili alanın seçimi (B2:E10 gibi) daha verimli olacaktır. Özellikle birkaç onbin satırdan fazla bir data kümesine lookup çekiyorsanız sadece ilgili alan eşleşmesi yapın.
• Arama listesi sıralı olsun.
• Aşağıdaki hızlı lookup formülünü kullanın
.

Bu formülde önce aranan yeri sıralamamız gerekir. Sıralanmış listeye, yakın eşleşme moduyla baktığımızda eşleşme sağlanırsa bu eşleşme sonucunu getir, sağlanmazsa NA getir diyoruz. Tam eşleşme modu tüm listeye baktığı için çok daha uzun sürer.

=IF(VLOOKUP(A2;Sheet1!A:A;1;1)=A2;VLOOKUP(A2;Sheet1!A:B;2;1);NA())

• Yukarıdaki formül karışık geldiyse aşağıdaki UDF olarak hazırlanmış fonksiyonu da kullanabilirsiniz. Ancak Excel'in yerel fonksiyonları kullanıldığı için üstteki yöntem daha hızlıdır. Aşağıda bi hız karşılaştırması var zaten, orda en hızlı yöntemin hemen üstteki formül olduğunu görürsünüz.

  Hızlılookup UDF'i için tıklayınız

  Function hızlılookup(aranan As Range, alan As Range, kolon As Integer)
  
  If WorksheetFunction.VLookup(aranan, alan, 1, 1) = aranan Then
      hızlılookup = WorksheetFunction.VLookup(aranan, alan, kolon, 1)
  Else
      hızlılookup = "NA"
  End If
  End Function
  
  

Deneme olması adına 500bin satırlık sırasız bir listede çeşitli lookup işlemleri yaptım. Veriler şöyle:

PC konfigürasyonuna göre hızların değişeceği aşikardır, ama oranlar üç aşağı beş yukarı aynı kalır.

NOT: VBA ile UDF yazmak yerine XLL ile UDF yazarak da performansı artırabilirsiniz. İlginizi çekerse buradan bakabilirsiniz.

Eksiklikler

Gördüğünüz ve/veya bildiğiniz üzere, Vlookup sola doğru arama işlemi yapmıyor. Her zaman pozitif bir değer veriyorsunuz ve sağa doğru arama yapıyorsunuz. Bu sorunu aşmak için ya INDEX-MATCH ikilisi kullanılır, ki bunları bir aşağıdaki bölümde göreceğiz, veya bir UDF tanımlamanız gerekir. Benim bunun için hazırladığım bir fonksiyon var. VBA bilenler bu kodu inceleyebilirler.

Terslookup UDF'i için tıklayınız

Function terslookup(aranan As Variant, hedefalan As Range, kaçıncı_kolon)
Dim index1 As String, match2 As String
Dim aynıwb As Boolean, aynıws As Boolean

aynıwb = IIf(hedefalan.Parent.Parent.Name = ActiveWorkbook.Name, True, False)
aynıws = IIf(hedefalan.Parent.Name = ActiveSheet.Name, True, False)


If aynıwb Then
    If aynıws Then
        match2 = hedefalan.Columns(hedefalan.Columns.Count).Address
        index1 = hedefalan.Columns(hedefalan.Columns.Count + 1 - Abs(kaçıncı_kolon)).Address 'niye abs, olur da negatif girmezler diye önce
    Else
        match2 = "'" & hedefalan.Parent.Name & "'!" & hedefalan.Columns(hedefalan.Columns.Count).Address
        index1 = "'" & hedefalan.Parent.Name & "'!" & hedefalan.Columns(hedefalan.Columns.Count + 1 - Abs(kaçıncı_kolon)).Address
    End If
Else
    match2 = "'[" & hedefalan.Parent.Parent.Name & "]" & hedefalan.Parent.Name & "'!" & hedefalan.Columns(hedefalan.Columns.Count).Address
    index1 = "'[" & hedefalan.Parent.Parent.Name & "]" & hedefalan.Parent.Name & "'!" & hedefalan.Columns(hedefalan.Columns.Count + 1 - Abs(kaçıncı_kolon)).Address
End If


If IsNumeric(aranan) Then
    strx = "INDEX(" & index1 & ",Match(" & aranan & ", " & match2 & ", 0))"
Else
    strx = "INDEX(" & index1 & ",Match(""" & aranan & """, " & match2 & ", 0))"
End If
terslookup = Evaluate(strx)

    
End Function

İkinci olarak, çoklu kritere göre Vlookup yapmak için yardımıcı kolon gerekiyor, bu da tek seferde hedefe ulaşmamızı engelleyen ve şık olmayan bir yöntem anlamına geliyor. Bunun üstesinden zarif ve etkin bir biçimde gelmek için yine INDEX-MATCH ikilisini kullanırız.

Zarif olmayan yönteme bi bakalım, aşağıda INDEX-MATCH yöntemiyle siz bilahare karşılaştırma yaparsınız.

Aşağıdaki örnekte Bölge1'in Toplam ürün satışına göre 1. şubesinin Ürün1 rakamını getirelim.

Önce N kolonuna yardımcı kolonu açalım,

Formülümüz şöyle olacaktır:

=VLOOKUP("Bölge1_1";N:R;2;0) //Aranan değeri elle girdim ama bunu bir hücreden de okutabilirdik

Dediğim gibi bu yöntem şık değildir, üstelik tablo yapınızı değiştirdiği için tehlikeli de olabilir, zira başka yerlerdeki formülleriniz de bu tablodan data çekiyorsa sıkıntılar oluşabilir.

Üçüncü olarak Vlookup her zaman ilk gördüğü eşleşme sonucunu getirir, ancak siz özellikle ilk eşleşme değil de sonraki eşleşme veya tüm eşlemelerin sonucunu yanyana görmek isterseniz başka birşeyler yapmanız gerekir. Bunu da aşağıdaki kısımlarda çok eşleşmeli lookup başlığı altında görebilirsiniz.

Özet

• Vlookup her zaman sağa doğru arama yapar. Sola doğru arama için Index-Match kullanılmalı.
• Vlookup her zaman tek kritere göre arama yapar. Çok kolon için ya Index-Match kulanılmalı, ya da yardımcı kolonda birleştirme yapılmalı(önerilmez)
• Vlookup her zaman ilk değeri getirir, ikinci değer için yardımcı kolon kullanmak gerekir. Bu yardımcı kolonda da aşağı kayan bir COUNTIF kullanılır. Bu yöntem zahmetlidir. Aşağıda gösterilecek olan UDF kullanımı çok daha basittir.
• Vlookup küçük büyük harf ayrımı gözetmez.
• Vlookup'ın tam ve yaklaşık olmak üzere iki arama şekli vardır.
• Vlookup joker karekterlerini kullanmaya izin verir.

HLOOKUP

Eğer lookup işlemini kolonda değil de satırda yapma durumu varsa o zaman HLOOKUP kullanırız. VLOOKUP'ta ilk kolona bakılırken bunda ilk satıra bakılarak lookup işlemi yapılır. Genel mantık Vlookup ile aynıdır.

=HLOOKUP(B5;$A$1:$H$2;2;0)

LOOKUP

VLOOKUP ve HLOOKUP'ın hem satırda hem sütunda çalışan bir formudur. Diğer ikisine göre eksikliği sadece sıralı datada çalışıyor olması. Artıları ise daha fazla. İlave bir parametre girmeden, bulabilyorsa tam eşleşmeyi yapar, bulamazsa aranan değerden en küçük değeri getirir. Ayrıca vlookup sadece sağa, hlookup sadece aşağı giderken LOOKUP ise her yöne doğru arama yapabilmektedir.

İki formu vardır; array ve vektör. Birçok yerde sadece vektör formu gösterildiği için ben de buna sadık kalıyorum.

=LOOKUP(C20;$B$16:$B$18) //varmı diye bakar, varsa kendisini getirir, yoksa N/A
=LOOKUP(C20;$B$16:$B$18;$C$16:$C$18) //varsa belirtilen kolondaki eşleşen veya en yakın datayı getirir

Bu arada bir dezavantaj da şudur ki, çok kolonlu lookup işlemi yapılamaz. Bunun için yine meşhur INDEX-MATCH kombinasyonu kullanılmalıdır.

MATCH

Bir veri dizisi içinde bir elemanın kaçıncı olduğunu MATCH fonksiyonu ile buluruz. Mesela aşağıdaki tabloda hem kolonda(F1:H1) hem de satırda(E2:E4) dizilmiş a,b,c değerleri var. Bunların sırasını buluyoruz.

=MATCH(A2;$E$2:$E$4;0) //Satır
=MATCH(A2;$F$1:$H$1;0) //Sütun

Match, illa bir hücre dizisiyle kullanılmak zorunda değil. { } içindeki dizilerle de kullanılabilir. Diziler ve dizi formülleri için buraya bakabilirsiniz.

=MATCH(2;{5;7;2;6;4};0) //3 döner

Son parametresi eşleşme tipini gösterir ve genelde tam eşleşmeyi temsilen 0/False girilir. 1 girilirse ve tam eşleşme yoksa aranandan küçük, -1 girilise aranandan büyük değeri getirir.

MATCH'in dizilerle olan bu kullanımı, onu dizi formüllerinin aranan fonksiyonu yapmaktadır.

INDEX

Index'in olayı, bir veri dizesinde belirtilen indeksteki elemanı getirir. Eğer tek parametre varsa, tek kolonluk bir veri vardır ve satırda arama yapar, diğer durumlarda satır ve sütun kesişimine bakar. Yani bu fonksiyonla aklınızda kalması gereken en önemli kelime kesişimdir.

=INDEX(B2:D9;2;3) //2.satır 3.kolon-->486
=INDEX(B2:B9;2) //2.satır-->1915

Formülümüzü illa data kümesini seçerek değil, seçime başlıkları da dahil ederek oluşturabiliriz.

=INDEX(A1:D9;3;4) //486

INDEX-MATCH

Index ve Match bir arada kullanıldığında etkisi müthiş olur. Bu şekilde hem Vlookup'ın alternatifidir, hem de Vlookup'ın yapamadığı sola gitme ve çoklu kritere göre lookup yapma imkanı verir.

Sola giden vlookup

Aşağıdaki örnekte Şube2'nin bölgesini bulalım.

=INDEX(J1:J12;MATCH("Şube2";K1:K12;0))

Formülün yaptığı iş şu:Önce MATCH ile, Şube2'nin K1:K12 içindeki sırasını buluyoruz. Sonra bu sıra numarasını ,INDEX ile J1:J12 içinde arıyoruz.

Çok kriterli vlookup

Aşağıdaki örnekte Bölge2'nin Toplam ürün satışıan göre 1.şubesinin adını ve Ürün1 rakamını getirelim.

=INDEX(K:K;MATCH("Bölge2"&1;J:J&M:M;0)) //Şube6

Çok kriterli aramalarımızda kullandığımız formül, bir dizi formülü olmak durumunda. Zira Match ile iki ayrı değer(önce Bölge değerini sonra da sıra değerini) arıyoruz. O yüzden formülü bitirince normal Enter yapmak yerine Ctrl+Shift+Enter yapıyoruz. Bunun detaylarını dizi formülleri sayfasında göreceğiz. Formülü doğru girdiğinizden emin olmak için formülün başında ve sonunda süslü parantezleri "{ ve }" görüp görmediğinize bakabilirsiniz.

{=INDEX(K:K;MATCH("Bölge2"&1;J:J&M:M;0))}

Büyük listelerde çok kriterli lookup

Yanlız veri kümemiz çok büyükse ne yardımcı kolonlar açmak ne de INDEX/MATCH kullanmak işe yarar. Belki yardımcı kolonlar aracılığı ile yukarıda gördüğümüz optimizasyon yöntemini kullanmak çzüm olabilir. Ancak bu çözümün de uygulanmadığı durumlarda Access'e başvurmanız gerekebilir. Bununla ilgili örnek bir videoyu Udemy'deki ücretsiz eğitimimde anlattım(10.video: Büyük veri kümeleriyle çalışmak).

Çok Eşleşmeli Vlookup

Bazen, lookup yaptığımız alanda birden fazla eşleşme varsa hepsinin sonucunun gelmesini isteriz. Normal Excel fonksiyonları ile bunu yapmak çok olası değil. Aslında oldukça dolambaçlı yollardan bunu yapanlar var ama ben buna girmek istemiyorum. Bunun yerine aşağıdaki UDF'i kullanmak çok daha kolaydır.

Function çok_eşleşmeli_vlookup(aranan As Range, alan As Range, kaçıncıkolon As Integer)
Dim a As Range
Dim dict As New Scripting.Dictionary 'Reference olarak eklenmiş olmalı, ekli değilse Late binding olarak yaratılabilir
 
If alan.Columns(1).Rows.Count = 1048576 Then
    Set alan = Range(alan(1, 1), alan(1, 1).End(xlDown))
End If
 
    For Each a In alan.Resize(, 1)
        If Not dict.Exists(a.Value) Then
            dict.Add a.Value, a.Offset(0, kaçıncıkolon - 1).Value
        Else
            geçici = dict(a.Value)
            dict.Remove (a.Value)
            dict.Add a.Value, geçici & ";" & a.Offset(0, kaçıncıkolon - 1).Value 'x
        End If
    Next a
 
çok_eşleşmeli_vlookup = dict(aranan.Value)
End Function

NOT: Yukarıda sonunda x bulunan satırı kaldırıp aşağıdaki 2 satırı eklersek, eşleşen değerleri bize distinct(benzersiz) olarak getirir.

 x = a.Offset(0, kaçıncıkolon - 1).Value
dict.Add a.Value, geçici & IIf(InStr(1, geçici, x, vbTextCompare) > 0, "", ";" & x)

Aslında kodu daha kullanışlı hale getirebiliriz, bunun için bi parametre daha ekleyelim.

Function çok_eşleşmeli_vlookup(aranan As Range, alan As Range, kaçıncıkolon As Integer, Optional distinctmi As Boolean = True)
Dim a As Range
Dim dict As New Scripting.Dictionary 'Reference olarak eklenmiş olmalı, ekli değilse Late binding olarak yaratılabilir
 
If alan.Columns(1).Rows.Count = 1048576 Then
    Set alan = Range(alan(1, 1), alan(1, 1).End(xlDown))
End If
 
    For Each a In alan.Resize(, 1)
        If Not dict.Exists(a.Value) Then
            dict.Add a.Value, a.Offset(0, kaçıncıkolon - 1).Value
        Else
            geçici = dict(a.Value)
            dict.Remove (a.Value)
            x = a.Offset(0, kaçıncıkolon - 1).Value
            If distinctmi = True Then
                dict.Add a.Value, geçici & IIf(InStr(1, geçici, x, vbTextCompare) > 0, "", ";" & x)
            Else
                dict.Add a.Value, geçici & ";" & a.Offset(0, kaçıncıkolon - 1).Value
            End If
        End If
    Next a
 
çok_eşleşmeli_vlookup = dict(aranan.Value)
End Function	
	

OFFSET

Bir referansa(başlangıç noktasına) göre X satır altında/üstünde ve Y sütun sağında/solunda hücreye başvurmak istediğimizde OFFSET fonksiyonunu kullanırız. Pozitif rakamlar sağı ve aşağıyı gösterirken, negatif rakamlar solu ve yukarıyı gösterir.

Yükseklik ve genişlik olmak üzere iki de opsiyonel parametresi vardır. Bunlar belirtilmezse, referansın yükseklik ve genişliği baz alınır. Özetle syntax şöyle: OFFSET (Referans, satır, sütun, [yükseklik], [genişlik])

=OFFSET(A2;2;1) //B4
=OFFSET(C2;2;-1) //B4
=OFFSET(C2;-2;1) //D1

Kayarak ilerleyen alanlardaki formüller

Yukardaki tabloda D ve E kolonları arasında Nisan eklense bile formülün kendisi de içeriği de aynı kalır;E2 için konuşacak olursak formül D2-C2'dir, yeni kolon eklenince de aynı kalır.

Araya Nisan eklenince, istediğimiz sonucu elde etmek için formülü değiştirmemiz yani E2-D2 yapmamız gerekir. Amma ve lakin, her ay yeni kolon eklendikçe de bununla uğraşamayız. Şimdi aynı şeyi OFFSET ile yapalım ve "2 kolon soldaki rakamdan 1 kolon soldaki rakamı çıkar" diyelim.

Araya kolon girince formülün sonucu farklılaşır. Bundan sonra E kolonuna Nisan rakamlarını koymak yeterli olacaktır.

Böyle, sürekli değişen satır/sütun işlemlerinde OFFSET kullanmak idealdir. Son 3 ayın ortalaması, son 5 ayın maksimumu v.s gibi.

=AVERAGE(OFFSET(OFFSET(H2;0;-1);0;0;1;-3))

Formül açıklaması şöyle:Önce OFFSET(H2;0;-1) formülü ile bulunduğumuz hücrenin bir soluna gidiyoruz. Bu sefer bunu referans verip -3 genişlik diyoruz, bu şu demek oluyor. Bu hücre dahil, sola doğru toplam 3 hücre genişliğinde bir hücre grubu döndür, sonra da bunların ortalamasını al.

Komşu hücreye giderek referans bulmak kolay, komşu olmayan hücrede nasıl yapacağımıza da bakalım. Hem bu sefer de sütunda bu işlem nasıl yapılır onu görelim.

=AVERAGE(OFFSET(C1;COUNT(C:C);0;-3))

Buradaki açıklama da şöyle:C1 hücresini referans al, C kolonundaki numerik içerikli hücre sayısı kadar yani 6 satır aşağı in, yani C7 hücresine, ve sonra da C7 dahil olmak üzere önceki yukarı doğru 3 hücre seç, yani C5,C6,C7, bunların da ortalamasını al.

OFFSET'e referans olarak kolon da verilebilir

Mesela belli bir kolonun hep bir sağındaki kolondaki rakamları toplamak istersek aşağıdaki formülü yazarız.

=SUM(OFFSET(E:E;0;1))

Dinamik Named Range

Normalde data kaynağımızın dinamik olmasını istediğimizde Table kullanmayı tercih ederiz. Böylece datamıza yeni alan eklendiğinde bu data kümemize erişen başka formülleri güncellemek zorunda kalmayız. Ancak bazı durumlarda (bir nedenden ötürü) tablomuzu Table yapamayız. Txt dosyalarının otomatik refresh olduğu durumlar gibi. Böyle durumlarda Dinamik Named Range kullanmamız gerekir. Böylece data kümemiz her yeni data geldikçe otomatik genişler, data silindikçe de otomatik daralır.

Bunun için Named Rangelerden, OFFSET ve COUNTA formüllerinden faydalacağız. Genel formülümüz şöyledir

=OFFSET(başlangıç_hücresi,0,0,COUNTA(tüm_kolon),COUNTA(teksatırlık_range))

Formülün mantığı şu. Bşalnagıç hücresinden itibaren satır ve üstun olarka ilerleme, yerinde kal, ama yüksekliğin tüm kolonda seçtiğin dolu hücre sayısı kadar olsun, genişliğin de belirttiğin kolon sayısı kadar olsun. Bu son parametreye duruma göre 1 de girilebilir.

Aşağıdaki örnek üzerinden bakacak olursak, listeye sürekli siciller ekleniyor ve biz A kolonundaki tüm sicillerin olduğu alana (şu an için A2:A9) "Siciler" ismini vermek istiyoruz. Name ekleme işlemini yaparken şu formülü yazacağız.

=OFFSET('dinamik named range'!$A$2;0;0;COUNTA('dinamik named range'!$A:$A);1)

Name içine yazılmış hali de aşağıdadır.

Yeni bir kayıt eklendiğinde F1 hücresinin hemen 9 olduğunu görüyoruz, zira Siciler Name'i de otomatik genişlemiş ve A2:A10 olmuştur.

Diğer Named Rangelerde olduğunun aksine Dinamik Range'ler NameBox içinde görünmezer. Aşağıdaki ilk resimden görüleceği üzere önceki örneklerde tanımladığımız diğer 3 Name var, ama Siciler yok. Fakat Formulas menüsünden Name'e girip baktığımızda görünmektedir ve onun formülüne tıkladığımızda 2.resimde olduğu gibi ilgili alanın çevrelendiğini görebiliriz.

Bu alandaki uniqe sicillleri bir data validation listesi içinde de kullanmak isteyebilirsiniz. Bunun için Çeşitli Örnekler kısmındaki 3.örneğe bakabilirsiniz.

Sadece Office 365 kullanıcılarında aktif olan bu fonksiyon, yukarıdaki birçok işi bünyesinde barındırıyor. Bakalım bu fonksiyonla neler yapabiliyoruz:

• Hem sola giden vlookup yapabiliyoruz, ki bu hem UDF hem de INDEX-MATCH alternatifidir
• Joker karekterleri destekler(match type)
• Aramaya baştan veya sondan başlayabilir(search mode).
• Bu fonksiyon aynı zamanda bir dinamik dizi fonksiyonu olduğu için birden fazla sonuç da döndürebilir ve bunları sağa doğru "döker"(dök[ül]me kavramının ne olduğunu linkten okuyabilirsiniz)
• İçiçe iki Xlookup kullanarak kolaylıkla kesişim buldurabilirsiniz(Aşağıda bahsettiğim kesişim yöntemlerine alternatif)
• Çoklu kriter verebilirsiniz

Ben detay örnekler yapana kadar bu sayfadan çeşitli örneklere bakabilirsiniz.

ADDRESS(satır,sütun,[tip],[stil],[sayfa]):Verilen satır ve sütun için A1 veya $A$1 gibi hücre adresi döndürür. Seçimli parametre olan "tip" için 1,2,3,4 değerleri girilebilir. Default değer 1'dir, yani mutlak adres($A$1) döndürür. 4 ise göreceli demektir. 2 ve 3 ise yarı mutlak yarı göreceli. Stil parametresini çok kullanmayacağız. Sayfa olarak da istersek bu adresi başka bir sayfanın adresi olarak döndürmemizi sağlayan sayfa ismi girilebilir.

Bu fonksiyonu tek başına kullanmak pek birşey ifade etmez. Bunu diğer fonksiyonlarla kullandığımızda ne işe yarayacağını daha iyi anlayacaksınız. Bu kullanım şekillerini aşağıdaki çeşitli örnekler bölümünde görebilirsiniz.

ROW/COLUMN([referans]):Belirtilen referansın satır/sütun numarasını döndürür. Hücre belirtilmezse formülün girildiği hücrenin kendisi baz alınır. Referans olarak bir hücre grubu girilirse ilk hücre baz alınır.

=ROW(C2) //2
=COLUMN(D4:F6) //4
=ROW() //8.satırda diyelim, 8

Bunlar da tek başına kullanılmak yerine bir fonksiyon kombinasyonu olarak kullanılır. Aşağıda örnekleri göreceğiz.

ROWS/COLUMNS([referans]):Belirtilen referansta kaç adet satır/sütun olduğunu döndürür.

=ROWS(C2) //1
=COLUMNS(D4:F6) //3

Bunlar da tek başına kullanılmak yerine bir fonksiyon kombinasyonu olarak kullanılır. Aşağıda örnekleri göreceğiz.

Yine de ROWS'un güzel bir kullanımını burada örneklendirmek istiyorum. Aşağıdaki tabloda bir filtreleme işlemi yaptım ve toplam kaç kayıttan ne kadarının gösterildiğini en tepeye yazdırım. (Normalde bir alana filtre uyguladığınızda durum çubuğunun sol köşesinde ne kadar kaydın gösterildiği yazar, ancak bu alandan çıkıp tekrar geri geldiğinizde göstermez)

Formüllerimiz şöyle:

=ROWS(Table1) //tabloda kaç kayıt var
=SUBTOTAL(3;Table1[Hacim]) //B1'deki formül.Filtrelenmiş alanda kaç satırın gösterildiğini verir

C1'de ise bu ikisini birleştiriyorum.(Aslında tabiki sadece C1'i göstermek lazım ama ben size parça parça göstermek istediğim için ayrı ayrı yazdım)

CHOOSE(indeks,değer1,değer2...değer254):1-254 arasında verilen indeks numaralarına denk gelen değerleri döndürür. Özellikle Ay no veya gün noya göre ay adı/gün adı yazdırmada çok faydalıdır. Böylece içiçe if yapmaktan veya bir Vlookup bölgesi oluşturmaktan kurtulmuş olursunuz. Tabiki liste çok uzunsa işlemi Vlookup ile yapmak çok daha makul olacaktır.

=CHOOSE(A2;"Ocak";"Şubat";"Mart")

Bir başka örnek de belirli kişilere rasgele bölge kodu/doğum yılı v.s atamak olabilir.

=CHOOSE(RANDBETWEEN(1;3);1979;1981;1992)

Choose'a parametre olarak belirli hücre grupları da verebilir ve bu dönen hücre alanlarını başka fonksiyonlarla birlikte kullanabiliriz. Mesela aşağıdaki örnekte, yapılan seçime göre SUM edilen hücre alanı değişmektedir.

=SUM(CHOOSE(A2; D2:D10; E2:E10;F2:F10)) 

NOT: Yukardaki Combobox kullanımının detaylarını buradan öğrenebilrsiniz.

Choose fonksiyonunun dinamik grafik yapımında nasıl kullanıldığını görmek için bu sayfaya göz atmak isteyebilirsiniz.

INDIRECT:Metin formundaki bir referansı gerçek bir referansa dönüştümeye yarar. Aşağıdaki örnekte A4 hücresine INDIRECT(A3) formülünü girdim. A3'te A1 metni var, bunu bir referans olarak algılayıp, A1 hücresindeki değeri getirdi.

Bu fonksiyon bize Data Validation(Veri Doğrulama)'ın da desteğiyle birbirine bağımlı Comboboxlar yapmamızı sağlar. Bunu bir örnekle açklayalım.

Diyelim ki Data Validation ile kullancıya bir hücrede il isimlerini seçtiriyoruz. Kullanıcı İstanbul seçince, alttaki comboboxta yine Data Validaiton ile İstanbul ilçeleri gelsin istiyoruz. Bunu şöyle yaparız.

• İl ve ilçeleri bi yere aşağıdaki gibi altalta yazarız.
• İlçe isimlerini seçip bunları aşağıdaki mavi dairedeki gibi Named Range yaparız.

• Sonra Data Validation ile B1 hücresinin kaynağını aşağıdaki gibi belirleriz

• Son olarak da B2 hücresinin kaynağını aşağıdaki gibi belirleriz.

B1'de İstanbulu seçince B2'deki formül İstanbul Name'indeki değerleri içine yükler.

Excelde iki boyutlu bir veri kümesinde belirli satır ve sütunların kesiştiği  hücreyi bulmanın birkaç yolu vardır. Hepsine bakacağız. Siz, o anki ihtiyacınıza hangisi uyuyorsa onu kullanabilirsiniz.

Şimdi öncelikle, aşağıdaki matrise bakalım. Burada C Grubunun 2.seviyesine denk gelen rakamı yani 11000i bulmak istiyoruz diyelim.

1.Yöntem:Offset ve Match kombinasyonu

=OFFSET(A3;MATCH(C9;$A$4:$A$7;0);MATCH(C10;$B$3:$D$3;0))

Yaptığımız işlemin açıklaması şöyle:Offset ile A3 hücresini referans alıyoruz, ikinci parametre olarak Match ile 2'nin kaçıncı satırda olduğunu bulup onu veriyoruz, üçüncü parametre olarak da yine Match ile C'nin kaçıncı sütünda olduğunu öğrenip onu veriyoruz. Sonuç olarak A2'nin 2 satır aşağısı ve 3 sütun sağ tarafına bak diyoruz, yani D5 hücresine.

2.Yöntem:Vlookup ve Match kombinasyonu

=VLOOKUP(C9;$A$3:$D$7;MATCH(C10;$B$3:$D$3;0)+1;0)

Burda yaptığmız işlem ise Vlookup'a aranan değer olarak 2'yi vermek, aranan alan olarak A-D kolonlarını vermek. Kaçıncı kolona bakması gerektiğini ise C10'daki değeri B3:D3 arasında nerde buluyorsa onun 1 fazlası olacak şekilde buluyor.

3.Yöntem:Index-Match kombinasyonu

=INDEX($B$4:$D$7;MATCH(C9;$A$4:$A$7;0);MATCH(C10;$B$3:$D$3;0))

Bu yöntem, kesişim deyince akla gelen ilk yöntemdir aslında. Yazımı öncekilere göre biraz daha uzundur ama esas varoluş sebebi bakımından Index-Match tam bir kesişim bulma yöntemidir. Bunun kullanım şeklini zaten yukarıda gördüğümüz için ayrıca açıklamaya gerek görmüyorum.

Bir alandaki kolon sayısına göre ortalama almak

		=SUM(B2:G2)/COLUMNS(B2:G2)	 

Bir alandaki son hücrenin adresi

=ADDRESS(ROW(Table1)+ROWS(Table1)-1;COLUMN(Table1)+COLUMNS(Table1)-1)

Bir alandaki uniqe değerleri listelemek