EXCEL DÜŞEYARA NEDİR?

Excel ile veriler arası sıralama, karşılaştırma, listeleme, süzme ve benzeri işlemler kolayca yapılabilmektedir. Mevcut verileri kullanarak işimize yarayacak yeni veriler oluşturma işlemi de Excel’de yapabileceğimiz işlerin başında gelmektedir.

Yol, yöntem bilindiğinde Excel’de iş yapmanın çok kolay ve basit olduğunu göreceksiniz. Bütün işlerde olduğu gibi Excel ’de de iyi işler yapmak için biraz çaba sarf edip bol bol örnek yapmak gerekmektedir.

Bilmediğimiz, ama yapmak zorunda olduğumuz işler her zaman olmuştur. Böyle anlarda birilerinden yardım alarak bize anlatmasını, göstermesini isteriz ya da o konu ile ilgili videolar seyrederek öğrenme olayına tek başımıza kendimiz girişiriz. Birilerinden yardım alma işi uygun durum, zaman ayarlama gibi sorunlardan dolayı her zaman gerçekleşmeye bilir.

Kendi başına Excel fonksiyonlarını öğrenmek isteyenler için hazırlanmış olan bu yazımızda DÜŞEYARA() fonksiyonu üzerinde duracağız.

Bu yazımızda bilenlerin hiç bırakmadığı her zaman başvurduğu, bilmeyenlerin ise uzak durduğu fonksiyon olan düşeyara (vlookup)’dan bahsedeceğim.

Excel Düşyara Kullanımı

Excel düşeyara kullanımı , referans aldığınız bir bilgiyi aynı sayfada ya da başka sayfalarda aratarak o bilgiyle alakalı diğer verilere ulaşmayı sağlar.

En iyi öğrenme yöntemi uygulayarak öğrenme olduğundan dolayı örnek veriler üzerinde yaparak göstermek istiyorum.

Elimizde olanlar: Elimizde öğrencilerimize ait öğrencilerin girmiş oldukları sınavlara ait notları var. Her bir sınav sonucu sınav1, sınav2, sınav3 diye farklı Excel sayfalarında kayıtlı. Her bir sınava ait sayfada öğrenci no, Adı, Soyadı, Sınav1 notu bilgilerinin olduğu sayfa mevcut.

Dosyamız ise aşağıda Resim1'de olduğu gibi.

İstenenler: Her bir öğrencinin girmiş olduğu sınavlarda aldığı notu öğrenci bazlı listelemek. Her öğrenci için bu şekilde bir liste hazırlamamız lazım.

Aşağıdaki Resim2 de olduğu gibi her öğrenciye ait sınav notları yan yana listelenmesi gerekiyor.

Excel düşeyara komutu nu bilmez isek ve bu formülü kullanmadan bu işi çözmeye çalışırsak işi yapabiliriz. Biraz zorlanırız. Öğrenci sayımızın sayısına bağlı olarak zamanımızı alacak şekilde işçilik yapmamız gerekecektir.

Yoğun bir işçilik ve tempolu çalışma sonunda bu gerekli liste hazırlana bilir.

Excel Düşeyara Formülü

Excel düşeyara formülü ile 3 dakikada yapılacak işlem düşeyara kullanılmadığında ele alınacak verinin çokluğuna göre beklide saatler hatta günler sürmektedir. El ile hazırlanan listenin doğru olup olmaması da ayrıca muamma olarak kalacaktır.

Excel formülleri ni öğrenip, neyi nasıl yapacağımızı bilirsek, formülleri aktif kullanırsak Excel ile çoğu şeyi hızlıca, kolay ve hatasız yapabiliriz.

Düşeyara kullanımı: Excel’de her komutun yaptığı iş farklı olduğu için kullanımları da istedikleri girilmesi, gösterilmesi gereken bilgilerde farklı farklıdır.

Düşeyara kullanımı şu şekildedir.

DÜŞEYARA (bakılan_değer, tablo_dizisi, sütun_indis_sayısı, aralık_bak):

Bakılan değer: listenizde ve diğer sayfalarda ortak olan değerlerdir. Bu genellikle her kişi için tek olarak kullanılan tc kimlik, öğrenci no, vb bilgiler olur.

Tablo dizisi: Aratmak istediğiniz bilgiyi diğer sayfalarda nerelerde arayacak onu belirliyoruz.

Sütun İndisi: Aranan bilgiyi diğer sayfalarda bulduktan sonra o satırdaki hangi bilgi bize lazım onun sütun sırasını yazıyoruz.

Aralık bak: Aranan bilgiyi aradığımız yer sıralı şekilde mi değil mi onu belirtiyoruz.

Not : bakılan değere göre tablo kontrol edileceği için bakılan değer her zaman için sol başlangıç noktası kabul edilmektedir. Ayrıca bakılan değer tek bilgidir. Yani kişiye özeldir. TC kimlik gibi, sınıf numarası gibi, öğrenci kodu gibi, mamül kodu gibi. Asla bu değerin tekrar etmemesi lazımdır. Tekrarlı durumlarda ilk bulduğu satırdaki değeri gönderecektir. Daha aşağıdaki değerler dikkate alınmayacaktır. Resim3'de görüldüğü gibi sol tarafta her zaman için bakılacak bilgi olmalı.

Excel Formülleri

Sıra bu formülü nasıl yazacağımıza geldi.

Örneğe göre yazacak olursak.

Örneğimizde D3 hücresine sınav1 in bilgilerini getireceğimiz D3’ün üzerine tıklayıp daha sonra Excel’de formülleri yazmak ve yazılan formülleri değiştirmek için Excel ana sayfada fx düğmesi tıklatıyoruz.

Çıkan ekrandan düşeyara yazıyoruz. Karşımıza aşağıdaki ekrana benzer bir ekranın çıkması lazım.

Aranan değer bölümüne hangi değeri arayacaksak ona tıklıyoruz. Örnekte A3 değerini arayacağız. O nedenle A3 hücresine tıkladığımızda aranan değer olarak A3 yazacaktır. Bu örnekte aranacak bilginin olduğu hücre seçilmiştir. Hücre adı yerine farklı farklı şeylerde aranabilmektedir. Kelime, sayı, ya da başka şeylerde aranabilmektedir. O nedenle burası yapacağınız işle alakalıdır her işte farklı farklı değer alabilir, değişken bir durumdur.

Şimdi aranacak bilgi olan A3 hangi sayfada aranacak onu belirlememiz lazım. Bizim sınav bilgilerimiz sınav1 sayfasında olduğu için Tablo dizisini seçmek için sayfanın altında olan sınav1 sayfasına tıklıyoruz. Sonra sarı renkli alan olan A3:D15 hücrelerini seçiyoruz.

Düşeyara Fonksiyonu

Düşeyara fonksiyonu nun kullanımında en can alıcı noktaların başında gelen sütun indis sayısı meselesidir. O nedenle aşağıdaki resimde neyin ne olduğu sütun indis sayısında kullandığımız 4 rakamının niye 4 olarak kullanıldığıyla alakalı açıklayıcı bilgileri oklarla gösterdim.

Bu örnekte sütun indis sayısı 4 olarak alındı ama başka örneklerde özellikle satırda veri çoksa alacağınız değerin bulunduğu sütun numarası kaç ise onu yazmanız gerekiyor. Bu örnekte numarasına göre adını getirmek istese idik, sütun_indis_sayısı 2 olacaktı. Aynı şekilde soyadını getirmek istese idik sütun_indis_sayısı 3 olacaktı.

Aralık bak yerinede Doğru ya da Yanlış olarak değer giriliyor. Tarama yapılacak listenin sıralı olup olmadığına göre burası değişiyor. Genellikle yanlış olarak bırakın daha kesin ve doğru bilgileri arıyor.

Aynı formülün diğer satırlar içinde geçerli olması lazım. Yani 2. kişi içinde aynı sonuçların gelmesi gerekiyor. O nedenle aynı formülün ikinci kişi içinde yapılması gerekiyor. Aynı formülü yazmak yerine formül yazılı hücrenin (D3 hücresi) altından tutup aşağı doğru çektiğinizde diğer satırlara da aynı formül kopyalanmış olacaktır.

Formülü diğer satırlar içinde uyguladığımızda aşağıda (Resim 5) olduğu gibi bütün öğrencilerin 1. sınavına ait notlar listemizde olması gereken yerde gözükecektir.

Excel’de Formül Kopyalamada Hata Oluşumu

Burada dikkat edilecek bir konu var o çok önemlidir. Excel’de hücrelerin adları görecelidir. Yani siz D3 hücresinin altından tutup da aşağı kopyaladığınızda;

D3 hücresinde olan formül şu şekildeydi DÜŞEYARA(A3,Sınav1!A3:D15,4,YANLIŞ)

D4 hücresine şu şekilde kopyalanacaktır DÜŞEYARA(A4,Sınav1!A4:D16,4,YANLIŞ)

Peki iki formülde yanlış giden bir şey yok mu

D3 hücresi nden kopyalayarak aşağı çektiğimizde D16 hücresinde olan formül şu şekilde olacaktır. Öyle olunca kırmızı renkli alanda sıkıntı var. DÜŞEYARA(A16,Sınav1!A16:D28,4,YANLIŞ)

Kırmızı ile renklendirilen yer olan bizim için önemli alanı işaret ediyor. İlk satırda (D3)’de burada yazılan kısım doğru ama aşağılara indikçe her şeyi artırarak inme mantığı olan Excel bizim listemizin hücre numaralarını da otomatik artırmış durumdadır. Böyle olunca aşağı inildikçe bulunamayan veriler olacaktır.

Bu tür hataların önüne geçebilmek için formül alanında sabitlemek istenilen alanın önüne $ işareti koyarak sabitlenmelidir.

D3 hücresi ne yazdığımız ve aşağı doğru çekerek kopyaladığımız formül aşağıdaki şekilde yazılmış olursa aşağı doğru çekerek kopyalamada hata çıkmayacaktır.

DÜŞEYARA(A3,Sınav1!A$:3D$15,4,YANLIŞ)

Yukarıda yaptığımız örnek çalışmada her şey yerli yerinde idi. Öğrenciler az ve her öğrenci her sınava girmiş şekilde senaryo oluşturmuştuk.

Düşeyara Fonksiyonu

Düşeyara fonksiyonu ile bundan başka neler yapılabilmekte ona bakalım.

Aynı örnek üzerinden verileri değiştirerek gidecek olursak. Öğrenci sınava girmedi ise ne olacak. Düşeyara fonksiyonu ile iki liste arasında kontrol yaptırıp birinde olup diğerinde olmayan verilerde buluna bilmektedir.

Aşağıdaki resim 8 de olduğu gibi kırmızı renkli olan öğrenciler Sınav1‘e girmemiş oldukları için onların sınav notu bölümden #YOK yazmaktadır. Girmeyen öğrencileri buradan kolayca bulabilirsiniz.

Yazılan formüllerin sonuçlarında #YOK hatası almamak için formülü başka bir formül olan eğerhata içerisine yazarsak #YOK yerine istediğiniz mesajı yazdırabilirsiniz

=EĞERHATA (DÜŞEYARA(A3,Sınav1!A$3:D$15,4,YANLIŞ),0) formülümüz kırmızı renkli olan yer. Dışında kalan kısım ise Eğerhata kullanımının nasıl olacağını gösteriyor.

Formülümüzü değiştirdiğimizde aşağıda Resim 9'da olduğu gibi bir sonuç alırız.