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 SoyadGiriş TarihiSicilBölümGörev
Hande Kılıç14.04.20102730ÜretimMüdür
Necmiye Çalışkan09.05.20103143SatışMüdür
Basri Dalgıç13.05.20103662FinansMüdür
Mehmet Kiraz14.04.20112532šretimMühendis
Ahmet Özhan15.04.20112249FinansMuhasebeci
Hasan Topçu24.03.20124967PlanlamaMühendis
Niyazi Yıldız11.06.20133953BakımBakım Şefi
Kenan Demirci13.07.20134386MuhasabeMüdür
Merve Meşhur24.07.20134205ÜretimMühendis
Zeynep Çığırtkan16.01.20152562BakımMühendis
Emin Nizami26.01.20153204PlanlamaMühendis
Berra Karaküp07.12.20152914SatışMühendis
Mustafa Demir24.10.20162855MuhasabeMuhasebeci

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.

İlginizi Çekebilecek Diğer Yazılar
Etiketler: , ,

Leave a Reply