1. Arama İşlevleri Nedir

Arama işlevleri toplamda 2 tanedir. Yazılımları aynı olan bu iki işlev YatayAra ve DüşeyAra ismine sahiptir. DüşeyAra girdiğiniz bir değeri başka bir aralığın ilk sütununda arar, değeri bulduğu satırda numarasını girdiğiniz sütundaki değeri size geri döndürür.

YatayAra arama işlemini aralığın ilk satırında gerçekleştirir ve değeri bulduğu sütunda, girdiğiniz satır numarasında yer alan hücrenin değerini geri döndürür.

Burada belirtmekte fayda var döndürülen değer bir hücre başvurusu (hücre adresi) değildir. hücrenin içinde yer alan değeri veya hücrede formül var ise formül sonucunu döndürür.

2. Yazılımı ve Parametreleri

Her iki formülün de yazılışı ve parametreleri aynı:

=DÜŞEYARA(aranan_değer;tablo_dizisi;sütun_indis_sayısı;[aralık_bak])
=YATAYARA(aranan_değer;tablo_dizisi;satır_indis_sayısı;[aralık_bak])

İngilizce kullananlar için:

=VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup])
=HLOOKUP(lookup_value;table_array;row_index_num;[range_lookup])

aranan_değer: DüşeyAra’da tablonun ilk sütununda, YatayAra’da ilk satırda bulmak istediğiniz değer. Hücre referansı veya elle girilmiş bir değer olabilir.

tablo_dizini: Aramak ve döndürmek istediğiniz değerleri içeren veri aralığı. Aralık başvurusu olarak girilir.

sütun_indis_sayısı: Aradığınız değerin bulunduğu satır içinde hangi sütun numarasındaki değeri geri döndürmek istiyorsanız o sütunun tablodaki konumu. YatayAra için ise satır numarası.

[aralık_bak]: Girilmesi zorunlu olmayan bir değerdir. 0 girilmesi halinde bire bir aranan değer bulunur, 1 girilmesi halinde en yakın değer.

Aralık bak parametresi dikkat etmeniz gereken en önemli parametre, çünkü bir rakam girilmez ise varsayılan olarak 1 atanır. Bu nedenle sıralı olamayan tablolarda veya metin aramalarında bu değeri girmez iseniz yazdığınız formül çok farklı bir değer döndürebilir. Hatta denemek için yazdığınız ilk formülde doğru değer döndürebilir ve siz formülü diğer hücrelere çoğalttığınızda farkında olmadan hatalı sonuçlar almanıza neden olur. Bu nedenle Excel’e yeni başlayan bir kullanıcı iseniz her formülde bu değeri girmeye alıştırın kendinizi.

3. Uygulama

Bu örnekte Dolar, Euro ve TL olarak farklı para cinsleri ile ürün alımı/satımı yapan bir firmada, tüm ürünlerin aynı tabloda karışık bir biçimde tutulduğunu varsayalım. Bu tabloda her bir ürünün TL cinsinden maliyetini hesaplamak istediğimizi varsayalım. 

Öncelikle aşağıdaki tabloyu Excel’e A6 hücresinden başlayarak kopyalayalım.

Firma AdıÜrünBirim FiyatıPara BrmAdetToplam FiyatToplam TL
ABC YazılımTasarım Yazılımı10.000USD5
CDE MühendislikAnaliz Yazılımı50.000EUR3
FGH ÇözümleriGörselleştirme Yazılımı12.000EUR1
SDH DanışmanlıkTeknik Destek1.500TL1

Şimdide aşağıdaki tabloyu Excel’de C1:D4 hücrelerine yapıştırın

Para BrmKur
EUR4,75
USD”3,92
TL1

Şimdi F7 sütununa toplam fiyatı bulmak için =C7*D7 formülünü yazıp aşağıya doğru çoğaltalım. Toplam TL fiyatı bulmak için de aşağıdaki formülü G7 hücresine aşağıdaki formülü yazalım.

=DüşeyAra(D7;$C$2:$D$7;2;0)*F7

Formülün nasıl çalıştığına gelirsek:

D7: Seçili aralığın ilk sütununda bulmak istediğimiz değer. D7 Hücresinde “USD” ifadesi var.

$C$2:$D$7: Aranacak tablo. Arama işlemi bu tablonun ilk sütununda gerçekleşir, yani arama aralığı C2:C7 hücreleridir. Hücre adresindeki $ işaretinin anlamı için buraya bakabilirsiniz.

2 ifadesi aranan ifade tabloda hangi satırda bulundu ise o tablonun o satırındaki kaçıncı sütuna denk gelen verinin alınacağını gösterir. Bu tabloda 2 sütun var, 2. sütunda kur değeri yer aldığından biz 2. sütundaki değeri almak istediğimizden buraya 2 yazdık. Eğer buraya başvuru yaptığımız tablodaki sütun adedinden büyük bir rakam girersek, örnek olarak burada 3, formül “#BAŞV” (#REF) hatası verecektir çünkü başvuru yapılan tabloda istenilen sütun değeri mevcut değil.

0 ifadesi ilk sütunda arama yapılırken aranacak ifadenin aynısının mı yoksa o değere en yakın olan değerin mi bulunması gerektiğini ayarlar. 0 girerseniz aranan değerin bire bir aynısı bulunacaktır, eğer bulunamaz ise formül hata verir. Eğer 1 girilirse bu değere en yakın ilk bulunan değer alınacağından çoğu zaman formül istenmeyen değerler bulur. Burada kısa bir açıklama yapmakta yarar var. Excel bu tarz yakın değeri bulma işlemi için “ikili arama algoritması” kullanır. Detaylı olarak anlatmak istemiyorum. Kısaca bu algoritmanın çalışması için değerlerin büyükten küçüğe sıralı olması şartı vardır. Yani eğer arama yaptığınız değerler sıralı değil ise bu değerin 0 olması zorunludur. Yine metin aramalarda da bu değerin 0 olmasında fayda var. Burada yine çok dikkat edilmesi gereken önemli bir konu da özellikle metin aramalarında aranan metinin ve arama yapılan tablodaki metinlerin öncesi ve sonrası gözükmeyen karakterler, örek olarak boşluk, olmamasıdır. Yani aranan metin kısmında ” USD “, ” USD”, “USD ” gibi ifadeler var ise ve tablo içinde “USD” yazıldı ise bu durumda formül aranan değeri bulamaz ve “#YOK” (#NA) hatası verir. Buna özellikle dikkat edin.

Formülü tamamladığımızda şu işlemi yapacak, önce satırdaki D sütununa gidecek ve oradaki metini okuyacak, örnek olarak “USD”. Sonra C2:D4 hücrelerine gidecek ve ilk sütunda “USD” ifadesini arayacak ki bu ifade 3. satırda bulunuyor. Sonra bu satırda tablodaki 2. sütuna gidecek ki bu da D sütunu demek. D3’teki değeri alacak, bu da 3,92’dir. Sonra bulduğu değeri F sütunundaki değer ile çarpacak ve sonucu hücreye yazacaktır.

Aşağıdaki örnekte DüşeyAra formülün yazılımı ve çalışması gösterdim.

Düşey Ara Formülü Yazılımı ve Çalışması

Peki bu yazımda gösterdiğim isimlendirmeyi bu formülde nasıl kullanacağız? Bu tarz formüllerde isim kullanımı hem anlaşılırlık hem de yazım kolaylığı açısından avantaj sağlıyor. Bu yüzden burada isim kullanılması oldukça güzel olacaktır. Burada öncelikle aranan değere bir isim verelim, D7:D11 hücrelerini seçelim ve ad yöneticisine girerek yeni isim diyelim. Sütunun üstünde zaten bir ad olduğundan Excel bunu otomatik olarak isim hanesine yazar. Yeni bir ad daha oluşturalım. Bu sefer B1:C4 aralığını seçelim ve buraya da kur tablosu adını verelim. Formülü ise

=DÜŞEYARA(Para_Brm;Kur_Tablosu,2;0)*F8

olarak güncelleyelim. Aşağıda nasıl yapıldığını gösterdim.

 
Düşey Ara Formülü İsimler İle Kullanma

YatayAra formülünün çalışma şekli de DüşeyAra ile aynıdır. Tek fark aranan değeri tablonun ilk satırında arar ve bulduğu sütunda girilen değer kadar aşağıya inerek o satırdaki değeri geri döndürür. Aralarındaki tek fark budur.

Aynı formülü bir de yatay ara ile yazalım. Bu sefer yukarıdaki E1:H2 aralığını değer aranan tablo olarak kullanacağız. Bunun için de bir isim ayarlayalım. Ben burada “Kur_Tablosu_Yty” ismini kullandım. Formülü de aşağıdaki gibi tekrar yazalım.

=YatayAra(Para_Brm, Kur_Tablosu_Yty;2;0)*F5

Aşağıda örnekte formülün çalışması gösteriliyor.

Yatay Ara Formülü Yazılımı ve Çalışması

Bu formüllerde özellikle metin aramalarda joker karakter kullanabilirsiniz. “*” birden fazla karakter için kullanılabilirken “?” işareti tek bir karakter yerine joker olarak kullanılabilir. Aşağıdaki videoda nasıl kullanıldığını gösterdim.

Düşey Ara Formülünde Joker Karakter Kullanımı

Arama formülünde yakın değeri bulma için de bir örnek yapmakta fayda var. Neden derseniz bu şekilde formülün çalışma mantığı “Eğer” formülünde olduğuna benzer sonuçlar elde etmemizi sağlar. Örnek olarak belli satış hacimlerine göre belli indirimler veya belli promosyonlar verildiğini düşünelim. Burada satış hacimleri alt ve üst sınır arasında yer alan iki değere göre belirlenir. Ve farklı satış hacimleri için hangi komisyonun verilebileceği arama formülünün “Yaklaşık Değer” özelliği kullanılarak bulunabilir. Şimdi aşağıdaki videoda bu formülün nasıl çalıştığını gösterdim. Dikkat ederseniz burada arama yaptığımız referans tablosundaki değerler büyükten küçüğe sıralanmış durumda.

Düşey Ara ile Yaklaşık Değer Bulma

Gördüğünüz gibi formülün döndürdüğü değer, aranan 48.000 değeri referans tabloda bire bir yer almıyor. Ancak formülün içinde kullandığımız “Yaklaşık Değer” seçeneği ile aradığımız değerin içinde olduğu aralık için tanımlanmış iskonto oranını elde ettik. Tabii bu formülün çalışma mantığına göre üst sınır dikkate alınmıyor ancak bazen bazı tablolarda daha anlaşılabilir olması açısından üst sınırların da gösterilmesi gerekebilir.

Arama işlevleri oldukça faydalı olan formüller olmalarına rağmen bazı zaafları var.

1. Seçilen aralığın ilk satır (YatayAra) veya sütununda (DüşeyAra) arama yaparlar. Aradığımız veri tablonun farklı bir satırında veya sütununda ise o sütundan başlayarak seçim yapmamız lazım.
2. Tek bir yönde veri bulurlar. Yani tablonun sağındaki veya aşağısındaki verileri bulabilirler. Siz eğer aradığınız sütunun sol veya üst tarafında bulunan bir veriyi bulmak istiyorsanız bu formüle elveda demek zorundasınız.
3. Tek bir kıstasa göre arama yapabilirler. Birden fazla sütunda yer alan verilerde belli kriterlere göre arama yapmanız gerekiyor ise bu formül size istediğinizi sunamaz.
4. Yaklaşık Değeri Bul özelliği sıralanmamış tablolarda kullanılır ise hatalı sonuç döndürür. Döndürülen değer hesaplama formüllerinde kullanılıyor ise elde edilen değerler de hatalı olur ve çoğu zaman hatanın anlaşılması veya bulunması çok zordur.
5. Eğer aranan değer bir rakam ancak metin olarak yazılmış ise ve başvurulan tabloda rakam olarak yer alıyor ise bu formül hata verecektir.
6. Bu formüllerin başvurduğu tablolar her değiştiğinde formüller de yeniden hesaplanır. Büyük tablolarda bu hesaplama zaman kaybına yol açabilir.
7. Formül ilk bulduğu eşleşmeyi geri döndürür. Başvuru yaptığınız tabloda aradığınız değerden birden fazla var ise bu formül ile her zaman istediğiniz değeri bulamayabilirsiniz.
8. Eğer arama yaptığınız tabloya ekleme yaptığınızda geri döndürülecek sütun/satır değerini değiştirmezseniz formülünüz istediğiniz değeri döndürmez. Bu yüzden bu tabloyu güncellediğinizde arama formülünü de güncellemeniz gerekir ya bu formülleri “Kaçıncı” formülü ile beraber kullanmanız lazım.
9. Bu işlevler büyük, küçük harflere duyarlı değildir. Yani metin aramalarında eğer büyük ve küçük harf duyarlı bir arama yapmak istiyorsanız bu işlevleri kullanamazsınız.

Peki Excel yukarıdaki ihtiyaçlar için bize ek işlevler sunuyor mu? Elbette. Ancak bu bir sonraki dersin konusu. Her zaman olduğu gibi soru ve önerileriniz için lütfen iletişime geçin.

Etiketler: , , , , ,

Leave a Reply