Dolar, Euro ve TL olarak farklı para cinsleri ile ürün alımı/satımı yapan bir firmada satılan malın birim fiyatını ve/veya toplam satışını sadece TL olarak hesaplamak istersek ne yaparız? Birim fiyatı günlük kur ile çarparız. Peki günlük kur sürekli güncelleniyor ise? Çok fazla ürün içeren listelerde bunu nasıl yaparız? Kurları ayrı bir tablo olarak hazırlar ve çarpma işlemini bu hücre adreslerini kullanarak yaparız. Peki tabloda aralara yeni ürünler eklemek gerektiğinde? Bütün bu işlemleri otomatik olarak daha hızlı yapabilmemizin bir yolu var mıdır? DüşeyAra ve YatayAra formülleri bu işlem için kullanılabilir. İngilizce Office kullanlar için VLookUp ve HLookUp. Bakalım nasıl yapılıyor?

Ö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ını 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 bir kaç zaafları var.

1. Seçilen aralığın ilk satır (YatayAra) veya sütununda (DikeyAra) 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 kullanmalısı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