Skip to main content
Microsoft Excel: Metinsel Fonksiyonlar

Microsoft Excel: Arama ve Başvuru Fonksiyonları

Hepinize merhaba, serimizin 6. yazısı olan Microsoft Excel: Arama ve Başvuru Fonksiyonları konusuyla karşınızdayım. Anlatacağım bu konu için anlatmış olduğum diğer konuların aksine biraz daha zor ve karmaşık olan excel fonksiyon grubu diyebiliriz. Microsoft Excel: Arama ve Başvuru Fonksiyonları konusu altında işleyeceğimiz formüller; Eleman (Choose), İndis (Index), Kaçıncı (Match), SatırSay (Rows), SütunSay (Columns), Ara (LookUp), DüşeyAra (VLookUp) ve YatayAra (HLookUp) şeklinde olacaktır. Fonksiyon grubu içerisindeki Eleman formülünü kullanırken Excel sekmelerinden “Veri” alanındaki “Veri Doğrulama” kısmında bazı işlemler yapacağımızı belirtmeliyim. Arama ve Başvuru fonksiyonları basit şekilde değil daha çok kompleks şekilde kullanılan yapılar olduğu için bu özelliği kullanmamız, fonksiyon kullanımını öğrenmemiz açısından faydalı olacaktır. Şimdi başlıklar halinde anlatma işlemine geçelim.

Microsoft Excel: Arama ve Başvuru Fonksiyonları – Eleman (Choose) & İndis (Index) & Kaçıncı (Match) & SatırSay (Rows) & SütunSay (Columns)

Microsoft Excel: Arama ve Başvuru Fonksiyonları içerisindeki ilk 5 formülü bu başlık altında, aşağıda paylaşmış olduğum excel tablosu üzerinden incelemeye başlayalım. 

ELEMAN (CHOOSE): En fazla 254 Bağımsız değişkenin bulunduğu bir listeden, seçilecek olan değerin karşılığında istenen bilginin dönmesini sağlayan fonksiyondur. Kullanım biçimi şu şekildedir;

=ELEMAN(Dizin-Liste sayısı;değer1;değer2;değer3;….)

Bu formülümüzün kullanımı için ilgili excel tablomuzda bulunan “Seçilen Numara” isimli kolonumuzun D2 hücresi üzerindeyken “Veri Doğrulama” işlemi yaptık. Öncelikle yaptığımız doğrulama işlemini açıklayalım. Veri sekmesinden “Veri Doğrulama” alanına geldikten sonra açılan pencerede “Doğrulama Ölçütü” – “İzin Verilen” yolundan Liste (Numara kolonumuzdan veri çekeceğimiz için) kısmını seçiyoruz. Bu seçimin ardından alt kısımda “Kaynak” isimli bir kutucuk aktif hale gelecektir. Bizim kaynak alanımız excel tablomuzda yer alan Numara kolonunun A2 ile A6 hücreleri arasında kalan kısım olduğundan kaynak kısmı için bu alanı seçiyoruz. Seçim işleminin ardından tamam diyoruz. Paylaştığım görselden yaptığım işlemlere bakacak olursak;
Yapılan işlemin ardından Seçilen Numara kolununun D2 hücresinde “↓” simgesinin oluştuğunu ve bu simgeye tıklandığında da “Numara” kolonunda yer alan değerlerin aynılarını görmekteyiz. Bu değerlerden hangisini seçersek, değere karşılık gelen ismin “Eleman” kolonunun E2 hücresinde yazmasını istiyoruz. Bu işlem için eleman fonksiyonundan faydalanmamız gerekiyor. E2 hücresi üzerinde yazacağımız formül şu şekilde olacak;

=ELEMAN(D2;B2;B3;B4;B5;B6)

Excel tablomuza baktığımızda “İsim” kolonunda yer alan isimlerin B2 ile B6 hücreleri arasında olduğunu görüyoruz. Yazdığımız formül ile D2 hücresinde yer alan numaralardan hangisini seçersek o numaraya karşılık gelen ismin, Eleman kolonunun E2 hücresine gelmesini sağladık. Eleman fonksiyonunun ikinci parametresi olan kısımda seçim yaparken deger1:deger10 şeklinde bir aralık seçmemiz mümkün değildir. Bu sebeple fonksiyon içerisindeki değerleri tek tek yazdığımızı hatırlatmak isterim. Bu işlemin ardından eğer 1 numara seçilirse “Ali”, “2” numara seçilirse “Ahmet” şeklinde dönüşler karşımıza çıkacaktır.

İNDİS (INDEX): İlgili tablo üzerinde satır ve sütunların kesiştiği noktadaki değeri döndüren fonksiyondur. Bu fonksiyonda unutulmaması gereken nokta satır önceliğinin olduğudur. Kullanım biçimine bakacak olursak;

=İNDİS(İlgili başvuru alanı ya da dizi;satır numarası;sütun numarası)

Excel tablomuzda yer alan “İndis Alanı” isimli kısımda 4 adet (5,8,6,9) değerimizin olduğunu görüyoruz. Bu değerlerin bulunduğu alanı seçerek “İndis” alanında 6 ve 8 değerlerinin nasıl yer aldığına bakacak olursak;

F5 Hücresi için || =İNDİS(D5:E6;2;1) — sonuç 6

D5 ve E6 aralığındaki değerlerden 2.satır 1.sütunda olan değeri getirdi. 

F6 Hücresi için || =İNDİS(D5:E6;1;2) — sonuç 8

D5 ve E6 aralığındaki değerlerden 1.satır 2.sütunda olan değeri getirdi.

KAÇINCI (MATCH): Bir değerin belirlenen tek satırlık ya da tek sütunluk bir bölgedeki kaçıncı hücrede olduğunu tespit etmek için kullanılır. Kullanım biçimi şu şekildedir;

=KAÇINCI(Aranan değer; değerin aranacağı sütun, satır ya da dizi; eşleşme türü (opsiyonel))

Yukarıda yer alan fonksiyonumuzda 1.parametre kısmı aradığımız değeri yazdığımız alan bu “Tayfun” olabileceği gibi 34 sayısı da olabilir. İkinci parametre alanı aranacak değeri hangi sütunda ya da satırda arayacağımızı belirlediğimiz kısımdır. Bu kısma eğer b sütununun tamamını aratacaksak B:B şeklinde bir bilgi yazabiliriz ya da b sütununu mouse yardımıyla seçerek yapabiliriz. Üçüncü parametre alanı zorunlu olmayan ancak kullanılması gereken bir alandır. Bu kısımda üç değer vermek mümkündür. Bunlar; 1 , 0 ve -1 şeklindedir. 

Parametre değeri “1”: Seçilen sütunun küçükten büyüğe doğru sıralandığını, eğer aranan değer bulunamazsa bu değerden küçük ve bu değere en yakın olan değerin kaçıncı hücrede olduğunu söyler.

Parametre değeri “0”: Yazılan değere eşit olan bir değer varsa kaçıncı hücrede olduğunu söyler, eşleşme durumu söz konusu değilse #YOK hata değerini döndürür.

Parametre değeri “-1”: İlgili sütundaki değerlerin büyükten küçüğe doğru sıralandığını, eğer aranan değer bulunamazsa bu değerden büyük ve bu değere en yakın olan değerin kaçıncı hücrede olduğunu söyler.

Genel olarak “0” değeri yazılarak kullanılan bir fonksiyon olan Kaçıncı fonksiyonunda eğer son parametre alanına bir değer girmezseniz excel varsayılan olarak “1” değerini kabul ederek sonuç üretmektedir. Excel tablomuzda bu 3 değer için yaptığımız örneklere bakacak olursak;

Tam Eşleşme kolonu içinde yazan fonksiyon: =KAÇINCI(5;H:H;0)

Küçük kolonu içinde yazan fonksiyon: =KAÇINCI(6,5;H:H;1)

Büyük kolonu içinde yazan fonksiyon: =KAÇINCI(7,5;I:I;-1)

Tam eşleşme için yazdığımız fonksiyonda 5 değerinin “H” kolonunda arandığını görüyoruz. Aradığımız değer 2.satırda olduğundan değerimiz 2 olarak döndü. Eğer aranan değer bulunamasaydı #YOK değeri dönecekti.

Küçük kolonunda yer alan fonksiyonumuzda 6,5 değerinin “H” kolonunda (Küçükten-Büyüğe olduğu için) arandığını görüyoruz. Ancak gördüğünüz gibi kolonumuzda 6,5 değeri bulunmuyor. Bu yüzden fonksiyon 6,5 sayısından küçük olan ve bu değere en yakın olan değerin kaçıncı hücrede olduğunu arayacaktır. Aradığımız 6,5 sayısına en yakın küçük değer 6,2 ve bu değer 4.satırda yer aldığından sonuç 4 olarak döndü. 

Büyük kolonunda yer alan fonksiyonumuzda ise 7,5 değerinin “I” kolonunda (Büyükten-Küçüğe olduğu için) arandığını görüyoruz. I kolonuna baktığımızda aradığımız değerin bulunmadığını, 7,5 değerinden büyük ve bu değere en yakın sayının ise 3.satırdaki “8” değeri olmasından dolayı sonuç 3 olarak döndü.

SATIRSAY (ROWS): Seçilen tablo alanı ya da dizideki satır sayısını dönen fonksiyondur. Kullanım biçimi;

=SATIRSAY(Seçilecek olan alan ya da dizi değeri)

Tablomuzda yer alan Satırsay alanında 5 değerinin yazdığını görmekteyiz. Bu alanda yazan formüle baktığımızda;

=SATIRSAY(A2:A6) — A2 ve A6 aralığındaki satırlar seçildiğinden sonuç 5 satır olarak döndü.

SÜTUNSAY (COLUMNS): Seçilen tablo alanı ya da dizideki sütun sayısını dönen fonksiyondur. Kullanım biçimi;

=SÜTUNSAY(Seçilecek olan alan ya da dizi değeri)

Tablomuzda yer alan Sütunsay alanında 2 değerinin yazdığını görmekteyiz. Bu alanda yazan formüle baktığımızda ise;

=SÜTUNSAY(A:B) — A ve B sütunları seçildiği için sonuç 2 sütun olarak döndü.

Microsoft Excel: Arama ve Başvuru Fonksiyonları – Ara (LookUp) & DüşeyAra (VLookUp) & YatayAra (HLookUp)

Microsoft Excel: Arama ve Başvuru Fonksiyonları konusu içerisindeki son 3 fonksiyonumuz birbirlerine benzer ancak aralarında bazı farklar olan fonksiyon grubudur. 

ARA (LOOKUP): Aranacak olan değerin ilk sütunda olmasına gerek olmayan, bir satır ya da sütundaki değeri bulmamızı sağlayan fonksiyondur. Kullanım biçimi şu şekildedir;

=ARA(Aranan değer; arandığı satır ya da sütun aralığı; dönecek sonuç(opsiyonel))

Paylaşmış olduğum excel tablosunun Ara kolonunda yer alan değerlerin nasıl bulunduğuna bakacak olursak;

=ARA(B2;B2:B5;A2)

B2 hücresindeki değeri B2:B5 alanında ara ve aranan değeri bulursan A2 hücresinde yer alan değeri yaz. Hücre numaraları sabitlenmediği için artan sırada hücreler yine ilgili alanda aranacak ve kendi değerlerine uygun değerleri döndürmüş olacaktır. örneğin; A2= Tayfun iken A3=Ahmet gibi.

DÜŞEYARA (VLOOKUP): Düşey listelerde arama yapmak için kullanılan fonksiyondur. Arama işlemi gerçekleştirilmeden önce mutlaka aradığımız değerleri içeren sütunun tablonun en solunda bulunması gerekmektedir. Microsoft Excel: Arama ve Başvuru Fonksiyonları içerisinde bulunan düşeyara fonksiyonu, bir datayı başka bir alana almak için veya kontrol amacıyla sıklıkla kullanılan yöntemlerin başında gelmektedir. Kullanım biçimi şu şekildedir;

=DÜŞEYARA(Aranan değer;tablo dizisi ya da aranacak alan;sütun indis sayısı (bulma işleminin ardından kaçıncı sütundaki değer gelecek); eşleşme türü (opsiyonel))

Yukarıda yazan formülümüze uygun olarak yapılan işlem neticesinde oluşturduğum excel tablosuyla fonksiyonu açıklamaya çalışalım.

Tablomuzda yer alan Düşeyara Fonksiyon Alanında sütun olarak hazırlanmış 3 alan görmektesiniz. Bu alanlardaki bir bilgiden yola çıkarak istenilen bilgiyi düşeyara sütununa nasıl çağırdığımızı yazdığımız fonksiyona bakarak inceleyelim. Örneğin Düşeyara alanının D3 hücresinde yer alan “Tayfun” değerini nasıl getirdiğimize bakalım;

=DÜŞEYARA(A3;A2:C7;2;YANLIŞ)

Yazılan fonksiyonda aranan değerin A3 hücresinden alınması gerektiğini, ikinci kısımda aranan değerin A2:C7 aralığında A sütununda aranacağını ve üçüncü kısımda ise A2:C7 aralığında A3 hücresinde bulunan değer sonucunda, hangi sütündaki bilginin dönmesini istediğimizi belirtiyoruz. Parametre alanına “2” yazarak, eğer aradığımız değere karşılık gelen bir arama yaptıysak isim sütunundaki değerler ile ilgili bilgiler dönmüş olacaktır. Son parametre alanı opsiyonel olan ancak önemli bir durumu belirtmeye yarayan alanımızdır. Burada yazan “YANLIŞ” ifadesi, aradığımız değerin aynısının ilgili alanda olması durumunda sonuç getir anlamına gelmektedir. Eğer biz bu alana “DOĞRU” yazmış olsaydık aranan değer bulunmadığı takdirde o değere çok yakın bir değer ilgili hücreye dönmüş olacaktı. Bu kısma YANLIŞ yazılabildiği gibi “0” değeri de yazılabilmektedir. Sonuç olarak baktığımızda A3 hücresinde yer alan “1” değeri, A2:C7 aralığının ilk sütununda arandı ve ilk sütunda bulunduğu zaman ise “1” değerinin karşısında 2.sütunda yer alan “Tayfun” ifadesi sonuç olarak karşımıza çıkmış oldu.

YATAYARA (HLOOKUP): Dikeyara işleminin aynı mantıkla ancak satır baz alınarak çalışan halidir diyebiliriz. Kullanım biçimi şu şekildedir;

=YATAYARA(Aranan değer;tablo dizisi ya da aranacak alan;satır indis sayısı (bulma işleminin ardından kaçıncı satırdaki değer gelecek); eşleşme türü (opsiyonel))

İlgili tablomuzda yer alan Yatayara Fonksiyon Alanına baktığımızda Numara, İsim ve Yaş bilgilerinin Yatay şekilde tabloda konumlandığını görmekteyiz. Yatayara sonuç alanımızda ise birbirinden farklı 5 değerin bulunduğunu görüyoruz. Bu alanda yer alan H9 hücresindeki “26” değerini nasıl çağırdığımıza bakacak olursak;

=YATAYARA(B9;A9:F11;3;0)

Fonksiyonumuzun ilk parametre alanında B9 hücresi içindeki değerin aranacağını belirtirken, ikinci kısımda bu değerin A9:F11 alanında yer alan A9 satırında aranacağını belirttik. Üçüncü kısımda aranan değer bulunduğu takdirde “3.” satırdaki bilginin dönmesini istedik. Neticede B9 hücresindeki “1” değerinin A9:F11 aralığında A9 satırında bulunması sonucunda ilgili alanın 3.satırında “1” değerine karşılık gelen hücre değerinin “26” olarak döndüğünü görmüş olduk.

Microsoft Excel: Arama ve Başvuru Fonksiyonları konusunu da an itibari ile bitirmiş bulunmaktayız. Her zaman ki gibi elimden geldiği kadar sade bir anlatım ile konuyu aktarmaya çalıştım. Umarım kafa karışıklığına yol açacak bir cümle kurmamışımdır. Okuduğunuz için teşekkürler kendinize iyi bakın.

Stay hungry stay foolish

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

This site uses Akismet to reduce spam. Learn how your comment data is processed.