EXCEL MUCİZELERİ - DÜŞEYARA

Excel‘de herkesin en çok duyduğu, öğrenmek istediği ve kullandığı işlevdir. Tabloda bir değeri bulmak için DÜŞEYARA işlevini kullanırız.

Bunu biraz açalım: aradığımız herhangi bir veriyi bir tabloda/veri aralığında arayıp, eğer o tabloda aradığımız veri varsa karşılığındaki bir sütundan veri almak istediğimizde DÜŞEYARA işlevini kullanırız.

Örneğin; bir personel maaş tablonuz olduğunu varsayalım, bir hücrede de personel adı yer alıyor, o personel adını tablomuzda arayıp maaşını kolaylıkla bulabiliriz.

En basit ifadeyle DÜŞEYARA işlevi şu mantıkta çalışır;

Bu işlevin bizden istediği argümanlara bir göz atalım.

ÇALIŞMA KOŞULLARI

• Gerekli olarak belirtilen argümanlar girilmediğinde işlev çalışmaz.
• aralık_bak opsiyonel argümanı girilmez ise DOĞRU – Yaklaşık Eşleşme varsayılan olarak kabul edilir.
• aranan_değer her zaman seçilen/belirtilen tablo dizisinin en solunda (ilk sütununda) olması gerekir. Fakat bu durum, DÜŞEYARA içine başka işlevler yazılarak aşılabilir. (Aşağıda bunu göreceğiz)
• aranan_değer belirtilen tablo_dizisinin en solunda bulunmaz ise #YOK hatası geri döner.
• aranan_değer argümanına yazılan veriden, tablo_dizisi argümanında belirtilen aralıkta birden fazla olsa da bu işlev ilk bulduğu veriye ait olan kaydı getirir.
• aranan_değer argümanına yazılan metinsel verinin büyük/küçük harf olmasına bakılmaz.
• aranan_değer argümanına sayısal bir değer yazıldığında, tablo_dizisi olarak belirtilen ilk sütundaki veri türü metin ise #YOK hatası geri döner, ters durumda da aynı koşul geçerli olur.
• sütun_indis_sayısı argümanına 1‘den küçük veya belirtilen tablo aralığındaki sütun sayısından büyük bir sayı ya da bir metinsel ifade yazılırsa #DEĞER! hatası geri döner.
• tablo_dizisi olarak belirtilen alandan bir sütun silindiğinde #BAŞV! hatası geri döner.
• aralık_bak opsiyonel argümanındaki DOĞRU ve YANLIŞ yerine bunların sayısal karşılıkları olan 1 veya 0 yazılabilir.
• DOĞRU – Yaklaşık Eşleşme seçilir ise; tablodaki ilk sütunun sayısal veya alfabetik olarak sıralanacağını varsayar ve tabandaki en yakın değeri arar. Yöntem belirtmezseniz, varsayılan yöntem olarak bu kullanılır.
• YANLIŞ – Tam Eşleşme seçilir ise; tablo_dizisinin ilk sütununda, aranan_değer argümanında belirtmiş olduğunuz veri ile birebir aynı olan varsa o değeri arar.

DÜŞEYARA İŞLEVİNİN TEMEL KULLANIMI

Aşağıdaki görselde, DÜŞEYARA İşlevinin temel yazım ve çalışma koşullarına uygun bir arama örneğini görüyorsunuz.

Sami Önder ismini B3:D9 hücre aralığındaki ilk sütun olan B sütununda bulursa, seçilen aralıktaki 3. sütundaki veriyi getir demiş olduk.
Son argümandaki YANLIŞ ifadesi yerine 0 yazılabileceğinden bahsetmiştik. Bu YANLIŞ ifadesiyle B sütununda birebir Sami Önder olup olmadığına bak denilir..

JOKER KARAKTER KULLANIMI

Aynı tablo üzerinde aradığımız ismin tamamını yazmadan, sadece ismini yazıp devamına joker karakter kullanarak aynı sonucu elde edebiliyoruz. Excel‘de kullanabileceğimiz Joker Karakterleri kısaca bir hatırlayalım.

Üç farklı Joker karakterimiz mevcut.

Yıldız ve soru işareti herhangi bir metnin önüne ve sonun gelerek DÜŞEYARA işlevinde aranan_değer argümanında tamamlayıcı görevi görürler.

~ işareti ise sadece joker karakterlerin önüne gelerek o karakterleri kısıtlama işlevi görür.

Eğer Joker karakteri metnin önüne gelirse, sonu yazılan metin ile bitiyorsa denir. Örnek; *ali (ali ile bitiyorsa)
Eğer Joker karakteri metnin sonuna gelirse, sonu yazılan metin ile başlıyorsa denir. Örnek; ali* (ali ile başlıyorsa)
Eğer Joker karakteri metnin hem önüne, hem de arkasına gelirse, yazılan metin içinde geçiyorsa denir. Örnek; *ali* (içinde ali geçiyorsa)
soru işareti için de şöyle bir örnek verelim; ?ali yazıldığında ali kelimesinden önce herhangi 1 karakter (metinsel ifade, harfler veya işaretler) var denir.
Bahsettiğim duruma örnek olarak bu görseli inceleyebilirsiniz. ‘=F4&”*”

BİRDEN FAZLA SÜTUNDAKİ VERİLERİ GETİRME YÖNTEMLERİ

Excel‘de yeni eklenen dinamik dizi işlevleri haricinde diğer tüm işlevler/fonksiyonlar geriye tek bir sonuç döndürürler.

DÜŞEYARA formülü yazarak bir sonuç elde ettiğimizde, tablodaki diğer verileri de formülü sağa doğru çekerek elde etmek isteyebiliriz.
Her sütun için ayrı ayrı formül yazmak yerine, değişen sütun_indis_sayısı argümanına dinamik olarak değişmesini sağlayacak bir işlev dahil ederek formülü yazdığımız hücreyi sağa doğru çektiğimizde, tablo_dizisi olarak belirttiğimiz alandaki diğer sütunlardan da veri almasını sağlayabiliyoruz.

Bu işlemi birkaç farklı yolla yapabiliriz, şimdi bu yolları inceleyelim..

İLK YÖNTEMİMİZ..

Buradaki gibi bir tablomuz olduğunu varsayalım.

Aranan Personel adını yazıp, sağa doğru çektiğimizde T.C. Kimlik No ve Maaşı alanındaki verilerin de gelmesini sağlayacağız.

Bu tarz bir işlem yapabilmek için öncelikle formülümüzü doğru yazmamız gerekecek.

Doğru formülden kasıt; hücre başvuru stillerini doğru kullanmaktan bahsediyoruz.

Yani bir hücreyi veya hücre aralığını seçerken o hücreleri sabitleyecek miyiz, sabitlemeyecek miyiz, yoksa sadece satırı veya sadece sütunu mu sabitleyeceğiz?.. gibi.. soru işaretlerine cevap olarak, hücreyi seçince $ (dolar) işaretlerini olması gereken yerlere doğru biçimde yazmalıyız.

Sonrasında sütun_indis_sayısı kısmını dinamik, yani değişken yapmamız sağlayacak bir işlev/fonksiyon kullanmamız gerekecek.

Öncelikle $ (dolar) işaretlerini doğru kullanarak tek bir hücre için formülümüzü oluşturalım. Sonra diğerleri için de formülün nasıl olması gerektiğine bakacağız.

Evet.. Hadi başlayalım.

Formülde bir hücreyi seçerken ilk etapta yukarıda belirttiğimiz sabitleme olup olmayacağı konusunu düşünmeliyiz. Bizim Aranan Personel adını yazdığımız hücrenin yeri sabit olduğu için, o hücreyi seçtiğimizde F4‘e basıp sabitliyoruz.

tablo_dizisi argümanı için seçtiğimiz hücre aralığının da yeri sabit olduğu için, o hücre aralığını da F4‘e basıp sabitliyoruz. Ve formülümüz resimde de göreceğiniz üzere bu hâle gelecektir. =DÜŞEYARA($G$2;$B$1:$E$7;2;0)

Şimdi formülümüzü sağa doğru çektiğimizde sütun_indis_sayısı argümanına elle 2 yazdığımız için o kısım hep 2 olarak kalacaktır.
Bizim buradaki amacımız; formülü sağa doğru çektiğimizde,

sütun_indis_sayısı argümanının 2,3,4 şeklinde artarak değişmesini ve o sütunlara denk gelen verilerin alınmasını sağlamak. İşte tam da burada SÜTUN fonksiyonu imdâdımıza yetişiyor.

SÜTUN FONKSİYONU NE İŞE YARAR, NASIL KULLANILIR?

Bu fonksiyon mevcut sütun veya belirtilen başvuruya ait sütun numarasını döndürür. İki şekilde kullanılır.

=SÜTUN() şeklinde yazıldığında, hangi hücreye yazılırsa o hücrenin sütun numarasını verir.
=SÜTUN(başvuru adresi) şeklinde yazıldığında, başvuru adresi olarak belirtilen hücre adresindeki sütun numarasını verir.

Eğer E10 hücresine =SÜTUN() formülünü yazarsanız, E sütunu Excel’de 5. sütun olduğu için formül size 5 sayısını döndürür.
Ayrın şekilde, eğer E10 hücresine =SÜTUN(B1) forlümün yazarsanız, B sütunu Excel’de 2. sütun olduğu için formül size 2 sayısını döndürür.

B1 hücre adresinde hiçbir sabitleme yapmadığımız için formülü sağa doğru çektiğimizde

C1, D1, E1 olarak değişecektir, bu da bize 2,3,4,5 sayılarını elde etmemizi sağlar.

Tam da bu sayede, tablo_dizisi aralığındaki diğer sütunlardan da veriyi alabilmiş olacağız.

Olmasını istediğimiz durum için örnek formülü aşağıda görebilirsiniz.

İKİNCİ YÖNTEMİMİZ..

Bu yöntemde yardımcı bir fonksiyon kullanmayacağız ama yine değişen argümanımız sadece sütun_indis_sayısı olacağı için o kısımda dizi ile sonuç almaya çalışacağız.

sütun_indis_sayısı olacağı için o kısımda dizi ile sonuç almaya çalışacağız.

Yani almak istediğimiz sütun indis sayılarını {2;3;4} şeklinde yazıp dizi formülü hâline dönüştürerek bu sayıların her bir sütuna aktarılmasını sağlayacağız.

Bu işlemi nasıl yapacağımızı belirtelim;

• H2 hücresindeki formülümüzü resimdeki gibi düzenleyelim.
• Sonrasında formülün sonuçlarını aktaracağımız alanı seçelim. (H2:J2)
• F2 tuşuna basıp hücrenin içerisine girelim.
• Ctrl + Shift + Enter tuşlarına basarak formülün girişini tamamlayalım.

sütun_indis_sayısı argümanını bu şekilde {…..} küme parantezi içerisinde istediğimiz sütun_indis_sayısı‘nı yazabilmemiz, istediğimiz sütunlardaki verileri almamızı sağlayarak bize esneklik kazandıracaktır.

SAĞDAN DÜŞEYARA

Bildiğiniz üzere, Excel’de aramalar her zaman soldan sağa doğru yapılır. Aramalar sütunlarda veya bir metnin karakterlerinde soldan sağa doğru olabilir.

Birçok fonksiyon da, arka plânda bu mantıkla hazırlanmış ve kullanıma sunulmuştur. DÜŞEYARA fonksiyonu da bu fonksiyonlardan biridir.

Yukarıdaki Çalışma Koşullarında da belirttiğimiz üzere, DÜŞEYARA fonksiyonunda aranan_değer her zaman belirtilen tablo_dizisinin ilk sütununda bulunması gerekir, bulunmaz ise formül geriye #YOK hatası döndürür.

Bu arada, aslında bu bizim düşündüğümüz gibi bir “hata” değil, sadece aranan verinin tabloda olmadığını gösteren bir uyarıdır.

Aradığımız bir verinin, belirttiğimiz aralığın ilk sütununda yoksa hata vermesi durumu DÜŞEYARA fonksiyonunun kodlamsal bir sonucudur.

Bu duruma bir örnek verelim isterseniz.

Eğer Murat OSMA isimli personeli ararken, tablo_dizisi argümanı için seçilen hücre aralığı A sütunundan başlarsa, hâliyle seçtiğimiz alanın ilk sütununda personel ismini bulamayacağı için geriye hata döndürecektir. Bu gayet normaldir.

DÜŞEYARA işlevinin hata almamıza sebep olan bu çalışma şeklini istersek kendi isteğimize göre düzenleyebiliriz. Bunun da yine birkaç farklı yöntemi var, fakat sizin için kolay olanı izah etmeye çalışalım.

ELEMAN adında bir fonksiyonumuz var. Bu fonksiyon bizden bir dizin_sayısı ister, bir de dizinler, yani veri kümeleri belirtmemizi/seçmemizi ister.

Aşağıdaki görselde yazılan formül şudur; =ELEMAN(1;C1:C7;B1:B7)

Bu formülde, dizin_sayısı olarak 1 yazdık, sonrasında her bir veri aralığını (dizinleri) seçtik, önce C1:C7 aralığını, sonra da B1:B7 aralığını. dizin_sayısına 1 yazdığımız ve 1. veri aralığı olarak C1:C7 aralığını seçtiğimiz için bu formül geriye C1:C7 aralığındaki verileri döndürecektir.

dizin_sayısına 2 yazmış olsaydık, B1:B7 aralığındaki verileri geri döndürmüş olacaktık.

İşte biz bu dizin_sayısı ve dizinleri belirttiğimiz anda kendi istediğimiz sütundaki veriyi getirmiş oluruz.

Aşağıda, hatanın oluşmasını engellemek ve istediğimiz veriyi alabilmek adına kullanabileceğiniz formülü görüyorsunuz.

DÜŞEYARA fonkiyonu içerisindeki tablo_dizisi argümanında ELEMAN fonksiyonunu kullanarak, dizin_sayısı argümanında { … } içerisinde sütun_indis_sayısı kısmına ne yazarsak, ona göre hangi aralıktaki sonucu getirmesini istediğimizi belirtiyoruz. Bu sayede, sütun_indis_sayısı argümanına 1 yazarsanız B2:B7 aralığındaki sonucunu, 2 yazarsanız da A2:A7 aralığındaki sonucu alacağınızı göreceksiniz.

ARALIK BAK ARGÜMANINI [DOĞRU – YAKLAŞIK EŞLEŞME] ( 1 ) KULLANMAK

DÜŞEYARA fonksiyonunu, genellikle aradığımız değerle birebir eşleşene ait verileri almak için kullanıyoruz. Bu fonksiyonu kullananların %95‘i de [aralık_bak] argümanını YANLIŞ – Tam Eşleşme yani 0 olarak kullanıyor. Peki nedir bu DOĞRU – Yaklaşık Eşleşme?

Argümana baktığınızda açıklama olarak; “tablo_dizisi ilk sütunundaki veriler artan sırada sıralanmalıdır” der.

O yüzden [aralık_bak] değeri DOĞRU seçildiğinde, DÜŞEYARA’yı kullanmadan önce tablo_dizisi değerinin ilk sütununu sıralayın.
Sıralamazsanız yanlış bir veriyi getirme ihtimali, tesadüf olmadığı sürece oldukça yüksektir.

tablo_dizisi alanındaki verilerimiz sıralı değilse, birebir eşleşen olsa dahî #YOK hatası geri döner.

NE ZAMAN DOĞRU – YAKLAŞIK EŞLEŞMEYİ KULLANMALIYIZ?

• tablo_dizisi alanındaki verilerimiz sıralıysa DOĞRU – Yaklaşık Eşleşmeyi kullanabiliriz.
• Sayısal bir değer arıyorsak ve onunla eşleşen bir sayı varsa, veya eşleşen bir sayı yoksa ona en yakın tabandaki sayıyı getirmek için de kullanabiliriz.

Şimdi bu duruma bir örnek verelim.

Aşağıdaki görselde yazılan formülde yapmak istediğimiz;

Maaşı 3300 olan veya ona en yakın olan sayılar içerisinde tabandaki bulduğu Maaşa ait Personel Adını bulmak istedik. Bu işlemin doğru sonuç vermesi için tablo_dizisi argümanının ilk sütunundaki verilerin sıralı olması gerektiğini unutmayalım.

Yazdığımız formül bize, tablomuzda Maaşı 3300 olan olmadığı için, ona en yakın olan 3250‘ye ait Personel Adını, yani Sami Önder sonucunu verecektir.

İKİ VE DAHA FAZLA KRİTERE GÖRE DÜŞEYARA

DÜŞEYARA fonksiyonunu, şimdiye kadar bir veriyi arayıp tabloda bulduktan sonra o tablodaki istenen sütundaki veriyi almak için kullandık.

İstersek birden fazla kritere göre de sonucu alabiliriz.

Aşağıdaki görselde şunu almak istediğimizi belirttik; Maaşı 2500 ise ve Doğum Yeri İzmir ise ona uygun olan satırdaki Personel Adını al.

Onun için de bu formülü kullandık; =YERİNEKOY(DÜŞEYARA(G2&H2&”*”;B1:B7&D1:D7&C1:C7;1;0);G2&H2;””)

Burada şöyle bir teknik uyguladık; aranan_değer argümanına Maaşı ile Doğum Yerini birleştirip  (G2&H2) sonuna da &”*” ekleyerek, şartlarımızın bunlarla başladığını belirttik.

tablo_dizisi argümanında da Maaşı, Doğum Yeri ve sonuç olarak almak istediğimiz Personel Adı aralıklarını birleştirdik.

sütun_indis_sayısı argümanında, almak istediğimiz ve aradığımız aralıkları & ile tek bir alanda birleştirdiğimiz için 1 yazdık.

aralık_bak argümanı Tam Eşleşme için 0 yazdık..

Sonuç olarak bize 2500İzmirSami Önder metnini geri dönderdi.

Son olarak da, YERİNEKOY fonksiyonu ile geri dönen metinden,  aranan_değer argümanına yazdığımız veriyi boş bırak dediğimizde, geriye sadece Personel Adı kalacaktır. Bu sayede birden fazla kritere göre aradığımız veriyi bulabildik.

ARANAN VERİ TÜRÜ İLE BAKILAN VERİ TÜRÜ FARKLI OLDUĞUNDA NE YAPMALI?

Bazen hücre biçimlerinden dolayı, sayısal olması gereken verilerin hücre biçimleri metin olarak belirlenebiliyor. Bu durumda Exce’in sizi o hücrelerin sol üst köşelerinde yeşil üçgenlerle ve sarı uyarı simgesiyle uyardığını fark etmişsinizdir.

O uyarı simgesini tıkladığınızda açıklama olarak; Metin Olarak Saklanan Sayı ibaresini göreceksiniz. O açıklama bize; hücreye yazılan veri türü sayısal ama hücre biçimi metin olduğundan ben yine de sizin yerinize yazdığınız bu sayıyı metin olarak saklıyorum der..

Eğer aranan_değer olarak belirtilen hücredeki veri türü metinsel bir ifade ise, tablo_dizisi olarak belirttiğimiz hücre aralığının ilk sütundaki veri türleri de metin olmak zorunda.

Ya da tam tersi bir durumu de düşünebilirsiniz. Özetle; aranan ve bakılan verilerin türleri aynı olmalı.

Eğer aynı veri türüne sahip değil ise #YOK hatası geri döndürmesine sebep olmakta.

Aşağıdaki görselde bu durumla ilgili örneği görebilirsiniz. Kullanılan formül; =DÜŞEYARA(G2;B1:E7;2;0)

aranan_değer veri türü metin, belirtilen tablo_dizisinin ilk sütunundaki veriler de sayısal olduğunda eşleşme sağlanamıyor ve

geriye #YOK hatası dönüyor

Peki bu durumu nasıl çözebiliriz? Çok basit!

aranan_değer olarak belirtilen metinsel ifadeyi sayıya dönüştürecek bir matematiksel işlem uygulayarak durumu çözebiliriz..

Uygulanacak bu matematiksel işlemler mevcuttaki sayıyı etkilemeyecek bir işlem olmalı.

Örneğin; +0, *1, /1, ^1 ya da — (artı 1, çarpı 1, bölü 1, üssü 1, tire tire) gibi herhangi bir matematiksel işleme sokarsak aşağıda göreceğiniz sonucu elde edebiliriz.

Kullanılan formül; =DÜŞEYARA(G2+0;B1:E7;2;0)

İÇ İÇE EĞERLER YAZMAK YERİNE DÜŞEYARA’YI KULLANIN

Bazı tablolarımızda, örneğin; hak edilen yıllık izin hesaplama işlemi yapılmak istendiğinde, birçok EĞER fonksiyonu iç içe yazılarak çözüm bulunabilir.

Fakat DÜŞEYARA size bu işlemi çok daha kısa yoldan çözme imkânı tanıyor.

Haydi gelin aynı işlemleri bir İÇ İÇE EĞER yazarak, bir de DÜŞEYARA fonksiyonu kullanarak yapalım.. farkı göreceksiniz.

Aşağıdaki gibi bir tablomuz olduğunu varsayalım. Farklı süreçlere gere farklı oranlar belirleyeceğiz. Eğer, iç içe Eğer fonksiyonları kullanarak yapmak istersek formülümü bu şekilde oldukça uzun yazmamız gerekecek.

=EĞER(C2=”CV Search”;10%;EĞER(C2=”Bizde Mülakat Aşamasında”;20%;
EĞER(C2=”Aday Paylaşıldı”;40%;EĞER(C2=”Müşteride 1.Mülakatta”;50%;
EĞER(C2=”Müşteride 2. Mülakatta”;70%;EĞER(C2=”Müşteri Assessment Aşamasında”;80%))))))

Halbuki tüm süreçlere ve oranlara ait bir tablomuz olsa, DÜŞEYARA fonksiyonu ile yüzde oranını bulmak oldukça kolay olurdu.

PAYLAŞ :