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ı olmayan 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ün | Birim Fiyatı | Para Brm | Adet | Toplam Fiyat | Toplam TL |
|---|---|---|---|---|---|---|
| ABC Yazılım | Tasarım Yazılımı | 10.000 | USD | 5 | ||
| CDE Mühendislik | Analiz Yazılımı | 50.000 | EUR | 3 | ||
| FGH Çözümleri | Görselleştirme Yazılımı | 12.000 | EUR | 1 | ||
| SDH Danışmanlık | Teknik Destek | 1.500 | TL | 1 |
Şimdide aşağıdaki tabloyu Excel’de C1:D4 hücrelerine yapıştırın
| Para Brm | Kur |
|---|---|
| EUR | 4,75 |
| USD” | 3,92 |
| TL | 1 |
Ş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.
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.
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.
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.
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.
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.
merhaba aşağıdaki gibi bir tablomuz var, buraya ürünleri giriyoruz.Listede 2000 den fazla ürün bulunmakta.Ben hangi ürüne veri girdiysem yan sayfaya bunların aktarılmasını istiyorum,yan sayfada sadece veri girişi olan ürünleri geçmesi gerekli,yardımınızı rica ederim
Kodu Açıklaması MİKTAR Ana Birim
AMBALAJ 001 KİLİTLİ TORBA 8*10 KOLİ
AMBALAJ 002 KİLİTLİ TORBA 18*15 (600 AD.) PKT
AMBALAJ 003 KİLİTLİ TORBA 11*14 1000’Lİ KOLİ
AMBALAJ 004 KİLİTLİ TORBA 9*11 1000’Lİ 10 KOLİ
AMBALAJ 005 KİLİTLİ TORBA 16*20 600’LÜ KOLİ
AMBALAJ 006 FİLTRE-EKMEK KAĞIDI ADET
AMBALAJ 007 KİLİTLİ TORBA 23*31 300’LÜ KOLİ
AMBALAJ 010 ALÜMİNYUM FOLYO 45 CM ADET
AMBALAJ 011 STRECH FİLM 45*1500-45*1200 10 ADET
AMBALAJ 012 STRECH FİLM 45*300 – 30*300 ADET
AMBALAJ 013 STRECH FİLM 17 MİC MT PALET ADET
Merhaba,
Ne tür bir veriyi nasıl girdiğinizi bilmiyorum ancak mevcut bir hücredeki veriyi değiştiriyorsanız Excel’de değişen hücrelere göre bir veri süzme mümkün değil. En kolay yolu güncelleme tarihi diye bir sütun eklemek, bu sütuna elle güncelleme yapılan tarihi girmek ve o tarihe göre verileri süzmek. Veri süzme için de tüm eşleşenleri çekme ile ilgili bir yazım var, ek olarak yeni Excel sürümlerinden birini kullanıyorsanız filtre işlevini de kullanabilirsiniz.
Yok eğer o satırdaki en son boş sütuna veri giriyorsanuz bu daha karmaşık bir durum çünkü her satırda farklı adetteki sütunlara veri girilmiş olabilir ve bu veri süzme işlemini çok zorlaştırır.
Bunun haricinde ancak makro yazarak bu verileri çekebilirsiniz.
Saygılarımla,