Excel ÇarprazAra (İngilizce Excel için XLookUp) işlevi, eski YatayAra ve DüşeyAra işlevleri yerine kullanılmak için geliştirilmiş çok daha fazla yeteneğe sahip yeni ve esnek bir işlev. Excel 365 1912 sürümünden itibaren kullanıma açılmıştır. Tek bir işlev ile tam, yaklaşık eşleşme ve joker karakter ile arama yapabilirsiniz. Hatta çoklu değer döndürme imkanınız da vardır.
1. Yazılımı ve Parametreleri
Formülün yazılışı:
=ÇAPRAZARA(aranan_değer; arama_dizini; döndürülen_dizin;[bulunamıyorsa]; [eşleştirme_türü]; [arama_modu])
Parametreler:
aranan_değer: Bulunmasını istediğimiz değerdir.
arama_dizini: bulmak istediğimiz değerin yer aldığı aralık. Satır veya sütun olabilir.
döndürülen_dizin: Aranan değere karşılık döndürülmek istenen değerin yer aldığı aralık.
bulunamıyorsa: Girilmesi zorunlu değildir. Formül aranan değeri bulamaz ise hücre içinde gösterilecek metin ifadesidir.
eşleştirme_türü: Girilmesi zorunlu değildir. 0 girilirse birebir aynı değer bulunur. 1 girilirse, tam eşleşme yoksa sonraki büyük değer. -1 girilirse tam eşleşme yoksa sonraki küçük değer döndürülür. 2 girilirse joker karakter kullanımı ile arama gerçekleştirilir. Varsayılan a
arama_modu: Girilmesi zorunlu değildir. İşlevin kullanacağı arama yöntemini belirler. 1 girilirse sıralı arama yapar, -1 tersten sıralı aram anlamına gelir. 2 artan sırada ikili arama, -2 ise azalan sırada ikili arama algoritmasının kullanımasını sağlar. Bu algoritmalar sıralı verilerde arama hızını çok arttırır.
Bu işlev ile bir başvuru döndürülür. Eğer başvuru bir aralık ise, Excel’in dinamik dizi özelliği ile birlikte tüm aralığın içindeki dikey bir dizi olarak formülün yazıldığı hücrenin altında dizilir. Başvuru döndürdüğü için işlevlerin başvuru isteyen kısımlarında kullanılabilir.
2. Kullanımı
Kullanım örneklerini göstermek üzere daha önce kullandığım tabloyu kullanacağım. Aşağıda tabloyu bulabilirsiniz:
| Ad Soyad | Giriş Tarihi | Sicil | Bölüm | Görev |
|---|---|---|---|---|
| Hande Kılıç | 14.04.2010 | 2730 | Üretim | Müdür |
| Necmiye Çalışkan | 09.05.2010 | 3143 | Satış | Müdür |
| Basri Dalgıç | 13.05.2010 | 3662 | Finans | Müdür |
| Mehmet Kiraz | 14.04.2011 | 2532 | retim | Mühendis |
| Ahmet Özhan | 15.04.2011 | 2249 | Finans | Muhasebeci |
| Hasan Topçu | 24.03.2012 | 4967 | Planlama | Mühendis |
| Niyazi Yıldız | 11.06.2013 | 3953 | Bakım | Bakım Şefi |
| Kenan Demirci | 13.07.2013 | 4386 | Muhasabe | Müdür |
| Merve Meşhur | 24.07.2013 | 4205 | Üretim | Mühendis |
| Zeynep Çığırtkan | 16.01.2015 | 2562 | Bakım | Mühendis |
| Emin Nizami | 26.01.2015 | 3204 | Planlama | Mühendis |
| Berra Karaküp | 07.12.2015 | 2914 | Satış | Mühendis |
| Mustafa Demir | 24.10.2016 | 2855 | Muhasabe | Muhasebeci |
Ben tabloyu B2 hücresinden itibaren yapıştırdım. H2-I3 aralığına da aşağıdaki gibi bir alt tablo hazırladım.
a. Ara-YatayAra-Düşeyara Yerine
Bu işlev ile başlıkta verdiğim 3 işlevin döndüreceği değeri elde edebilirsiniz. Her ne kadar 3 işlevin adını verdiysem de yazılım açısından ARA işlevine benziyor ancak ARA işlevi gibi sıralı bir diziye gereksinim duymuyor.
Şimdi isterseniz SİCİL numarasına göre Personel Ad-Soyadını bulalım:
=ÇAPRAZARA($H$3;$D$3:$D$15;$B$3:$B$15;;0;1)
Formül ile H3 hücreside yer alan sicil değerini D3:D15 aralığında arıyoruz ve B3:B15 aralığında aradığımız değerin bulunduğu satıra denk gelen hücre değeri geri döndürülüyor.
b. Birden Fazla Değer Döndürme
Bir aralık döndürmek için yapmamız gereken tek şey, geri döndürülecek dizinde birden fazla satır ve sütun içeren bir aralık seçmek. İşlev aradığımız değeri arama yaptığımız dizinde bulacak ve aralığın ilgili satırını olduğu gibi geri döndürecektir.
=ÇAPRAZARA($H$3;$D$3:$D$15;$B$3:$F$15;;0;1)
c. Değer Bulunamadıysa Parametresi
Eğer aradığımız değer aradığımız dizinde yoksa, işlevin istediğimiz bir değeri döndürmesini sağlayabiliriz.
=ÇAPRAZARA($H$3;$D$3:$D$15;$B$3:$B$15;”PERSONEL YOK”;0;1)
d. Yakın Düşük veya Büyük Değeri Bulma
Misal aşağıdaki gibi bir sırasız basamak ve oran değerlerinin olduğu bir listemiz var. Aradığımız değer bu listede yer alıyor mu bilmiyoruz, zaten bizim için önemli olan en yakın değeri bulmak. Aşağıdaki formüller ile listeden istediğiniz değerleri çekebilirsiniz:
=ÇAPRAZARA($F$3;$B$3:$B$12;$C$3:$C$12;;-1)
=ÇAPRAZARA($F$3;$B$3:$B$12;$C$3:$C$12;;1)
En son kısımda görmüş olduğunuz -1 değeri yakın küçük, 1 değeri ise yakın büyük değerleri bulmak içindir.
e. Yatay ve Dikeyde Arama
Daha önce İleri arama teknikleri yazılarımda anlattığım gibi alt tablo başlıklarında yer alan bilgilere göre ana tablodan otamatik olarak veri çekmek için ise önce alt tablo başlığı, ana tabloda aratacağız:
=ÇAPRAZARA($I2;$B$2:$F$2;$B$2:$F$15)
Sonrasında bu alanı sicil aramasında geri döndürülen aralık olarak kullanacağız:
=ÇAPRAZARA($H$3;$D$2:$D$15;ÇAPRAZARA($I2;$B$2:$F$2;$B$2:$F$15))
Gördüğünüz gibi bu işlev Excel’in dinamik dizi özelliği ile birlikte İNDİS ve KAÇINCI gibi ek işlevleri kullanmamıza da gerek bırakmıyor.
f. Aranan İki Değerden Aralık Bulma
Yazının başında bu işlevin değer değil, bir başvuru döndürdüğünü söylemiştim. Bu sayede bir tabloda yer alan iki değeri aratarak, bu iki değer arasındaki tüm satırları geri döndüren bir formül yazabiliriz. Ve bu aralığı TOPLA gibi bir işlevde kullanabiliriz.
3. DüşeyAra ve Ara Gibi Formüller İle Fark
DüşeyAra, YatayAra ve Ara ile temel farkı bu işlevler bize sadece değer döndürür. Hücre başvurusu değil. Bu nedenle en son örnekte gördüğünüz bir uygulamayı bu işlevlerle elde edemezsiniz. Bu tarz bir kullanımda İNDİS ve KAÇINCI gibi iki işlev yerine tek bir işlev kullanımı ile işlerimizi çok daha hızlandırmakta.
DüşeyAra ve YatayAra gibi işlevler tek bir değer döndürebilir, tabii siz statik bir dizi girerek birden fazla değer de döndürebilirsiniz ancak bu işlev ile çok daha hızlı bir şekilde çoklu değer döndürmeniz mümkün.
ARA işlevinden farkı ise, bu işlevin sıralı dizilere ihtiyaç duymaması. Bunu en kolay bir örnek ile anlatabiliriz:
Örnekte gördüğünüz üzere ARA işlevi tamamen alakasız bir değer döndürürken ÇAPRAZARA işlevi istediğimiz değeri buldu. Bunun nedeni, ARA formülünü kullanabilmek için tablonun sicil sütuna göre sıralı olması gerektiğidir.
YATAYARA ve DÜŞEYARA gibi işlevlerden farkı ise hem her iki işlev yerine kullanılabilmesi hem de bu işlevlerin sahip olmadığı aranan sütunun solunda veya satırın üstünde yer alan değerleri de döndürebilmesidir. Ek olarak bu işlevler döndürmek istediğimiz değerlerin yer aldığı satır ve sütun numaralarının el ile girilmesi nedeni ile ana tablonuza satır veya sütun eklendiğinde bu işlevlerin hatalı değer döndürür. Ancak ÇAPRAZARA işlevinde bu alanlar aralık olarak girildiğinden ana tablonuzdaki bir değişiklikten etkilenmez.
