DÜŞEYARA ile Tablolar Arasında Veri Getirmek

Tahmini süre: 13 dk. Düzey: Orta.

Bu derste ne yapacağız?

Bu derste, bir tablodaki anahtar değere (örneğin ürün koduna) bakıp başka bir tablodan ona karşılık gelen bilgiyi (örneğin birim fiyatı) otomatik getirmeyi öğreneceksiniz. Bunu DÜŞEYARA işleviyle yapacağız. İşlevin dört bağımsız değişkenini tek tek inceleyecek, tam eşleşme ile yaklaşık eşleşme farkını göreceksiniz. Sütun indis numarasının nasıl sayıldığını da uygulamada deneyeceksiniz.

Ön koşullar: Excel'de hücre başvurusunu (A2, B5 gibi) tanımanız, sayfalar arasında geçiş yapabilmeniz ve basit bir formül yazabilmeniz yeterlidir. Mutlak başvuruyu ($ işareti) bu derste yeniden hatırlatacağız. Önceki dersteki çalışma dosyanızı kullanabilirsiniz. Yeni bir boş çalışma kitabı açmanız da yeterlidir.

Köprü: Önceki derslerde verilerinizi düzenli bir tablo olarak tutmanın önemini gördünüz. DÜŞEYARA bu düzenin karşılığını verir: Tablolarınız ne kadar düzenliyse işlev o kadar sorunsuz çalışır. İzleyen derste, DÜŞEYARA'nın sınırlarını aşan modern alternatif XLOOKUP işlevine geçeceğiz. Bu dersi iyi kavramanız, o dersi çok kolaylaştıracaktır.

Kavram

Bir kırtasiye dükkânı düşünün. Kasadaki kişi müşterinin aldığı ürünün kodunu okuyor, sonra duvardaki fiyat listesine bakıp o kodun karşısındaki fiyatı buluyor. DÜŞEYARA tam olarak bunu yapar: Aranan kodu listenin en solundaki sütunda arar, bulduğu satırda istediğiniz sütuna gider ve oradaki değeri getirir.

İşlevin yazımı şöyledir (Türkçe Excel'de bağımsız değişkenler noktalı virgülle ayrılır):

=DÜŞEYARA(aranan_değer; tablo_dizisi; sütun_indis_sayısı; [aralık_bakma])

• aranan_değer: Aradığınız şey. Örneğin K-101 kodunun yazılı olduğu hücre.
• tablo_dizisi: İçinde arama yapılacak fiyat listesi. Aranan değerin bulunduğu sütun bu aralığın ilk (en sol) sütunu olmalıdır. DÜŞEYARA yalnızca sağa doğru bakabilir.
• sütun_indis_sayısı: Getirilecek bilginin, seçtiğiniz aralığın kaçıncı sütununda olduğu. Sayım, sayfanın A sütunundan değil, seçtiğiniz aralığın ilk sütunundan başlar ve ilk sütun 1'dir.
• aralık_bakma: Eşleşme türü. YANLIŞ (ya da 0) tam eşleşme, DOĞRU (ya da 1) yaklaşık eşleşme demektir. Bu bağımsız değişken boş bırakılırsa Excel yaklaşık eşleşmeyi varsayar. Yeni başlayanların en sık hata yaptığı yer burasıdır.

Tam eşleşme ile yaklaşık eşleşme

Tam eşleşme, ürün kodu, sicil numarası, e-posta gibi birebir tutması gereken anahtarlarda kullanılır. Kod listede yoksa Excel yanlış bir değer uydurmaz, #YOK hatası verir. Yaklaşık eşleşme ise kademeli tablolarda (iskonto dilimleri, not aralıkları, vergi dilimleri) kullanılır. Bu durumda ilk sütunun küçükten büyüğe sıralı olması şarttır. Excel, aranan değerden büyük olmayan en yakın değeri bulur.

Adım adım uygulama

Arayüz ve menü adları Excel sürümüne ve dil ayarına göre küçük farklılıklar gösterebilir. Aşağıdaki adımlar genel yapıyı anlatır.

Hazırlık: Fiyat listesini oluşturma

• Adım 1: Yeni bir çalışma kitabı açın. Alttaki sayfa sekmesine çift tıklayıp adını Fiyatlar yapın ve Enter'a basın.
• Adım 2: A1, B1 ve C1 hücrelerine sırasıyla Ürün Kodu, Ürün Adı ve Birim Fiyat yazın.
• Adım 3: A2'den C5'e kadar şu satırları girin: K-101, Kalem, 12; D-205, Defter, 45; S-310, Silgi, 8; M-412, Makas, 60.
• Adım 4: Alttaki + düğmesine tıklayarak yeni bir sayfa ekleyin ve adını Siparişler yapın.
• Adım 5: Siparişler sayfasında A1'den D1'e şu başlıkları yazın: Ürün Kodu, Adet, Birim Fiyat, Tutar. A2'ye D-205, A3'e K-101, A4'e M-412 yazın. B2, B3 ve B4'e sırasıyla 3, 10 ve 2 girin.

Ana uygulama: Fiyatı getirme

• Adım 6: Siparişler sayfasında C2 hücresine tıklayın ve =DÜŞEYARA( yazın. Excel bağımsız değişkenleri size küçük bir ipucu kutusunda gösterecektir.
• Adım 7: Birinci bağımsız değişken için A2 hücresine tıklayın, ardından ; yazın. Formül şu an =DÜŞEYARA(A2; görünümündedir.
• Adım 8: Alttaki Fiyatlar sekmesine geçin. Fare ile A2'den C5'e kadar sürükleyerek seçin (başlık satırını seçmeyin). Ardından F4 tuşuna bir kez basın. Başvuru $A$2:$C$5 biçimine dönüşür. Bu, formülü aşağı kopyaladığınızda aralığın kaymasını önler. Bazı dizüstü bilgisayarlarda F4 için Fn tuşuyla birlikte basmanız gerekebilir.
• Adım 9: ; yazın. Fiyat, seçtiğiniz aralığın 3. sütunundadır (Ürün Kodu 1, Ürün Adı 2, Birim Fiyat 3). O hâlde 3 yazın ve tekrar ; koyun.
• Adım 10: Son bağımsız değişken için YANLIŞ yazın ve parantezi kapatın. Tam formül şudur: =DÜŞEYARA(A2;Fiyatlar!$A$2:$C$5;3;YANLIŞ)
• Adım 11: Enter'a basın. C2'de D-205 kodlu Defter'in fiyatı olan 45 görünmelidir.
• Adım 12: C2'yi seçin, hücrenin sağ alt köşesindeki küçük kareye (doldurma tutamacı) çift tıklayın. Formül C4'e kadar kopyalanır ve fiyatlar gelir.
• Adım 13: D2 hücresine =B2*C2 yazın, Enter'a basın ve aynı doldurma tutamacıyla aşağı kopyalayın. Artık her satırın tutarı otomatik hesaplanır.

Sütun indisini değiştirmek

• Adım 14: Ürün adını da getirmek isterseniz E1'e Ürün Adı yazın. E2'ye =DÜŞEYARA(A2;Fiyatlar!$A$2:$C$5;2;YANLIŞ) yazın. Yalnızca sütun indisi 3'ten 2'ye değişti. Aşağı kopyalayın.

Yaklaşık eşleşme uygulaması: İskonto dilimleri

• Adım 15: Yeni bir sayfa ekleyip adını İskonto yapın. A1'e Alt Sınır, B1'e İskonto yazın.
• Adım 16: A2:B5 aralığına şu değerleri girin: 0 ve 0; 50 ve 0,05; 100 ve 0,10; 250 ve 0,15. B2:B5 hücrelerini seçip Giriş sekmesindeki % düğmesine tıklayarak yüzde biçimi verin. A sütunu küçükten büyüğe sıralıdır. Yaklaşık eşleşme için bu şarttır.
• Adım 17: Herhangi bir boş hücreye tutarı, örneğin 120 yazın. Yanındaki hücreye =DÜŞEYARA(A8;$A$2:$B$5;2;DOĞRU) gibi bir formül yazın. Burada A8, 120 yazdığınız hücre olsun. Sonuç %10 çıkar. Çünkü 120, 100 dilimine girer ve 250'ye ulaşmamıştır.

Formülün yapısı, parça parça

• =DÜŞEYARA( işlevi başlatır.
• A2 aranacak kodu gösterir.
• Fiyatlar!$A$2:$C$5 başka sayfadaki sabitlenmiş fiyat listesidir. Sayfa adından sonra ünlem işareti gelir.
• 3 aralığın üçüncü sütunundan değer getirir.
• YANLIŞ tam eşleşme ister.

Sık karşılaşılan sorunlar

Mini görev

Kendi küçük personel listenizi hazırlayın. Personel adlı bir sayfada sicil numarası, ad soyad ve departman sütunları olsun (en az altı kişi). Bordro adlı ikinci bir sayfada yalnızca sicil numaralarını yazın. Bordro sayfasında DÜŞEYARA ile ad soyadı ve departmanı getirin. Ardından, kendi belirleyeceğiniz dilimlerle (örneğin kıdem yılına göre yıllık izin günü) yaklaşık eşleşmeli bir tablo daha kurun.

• Personel tablosunda sicil numarası ilk sütunda mı?
• Tablo aralığını F4 ile sabitlediniz mi?
• Ad soyad için doğru sütun indisini kullandınız mı, departman için indisi değiştirdiniz mi?
• Tam eşleşme formüllerinde son bağımsız değişken YANLIŞ mı?
• Listede olmayan bir sicil numarası yazıp #YOK hatasını gördünüz mü?
• Yaklaşık eşleşme tablonuzun alt sınırları küçükten büyüğe sıralı mı?
• Formülü aşağı kopyaladığınızda tüm satırlar doğru sonuç veriyor mu?

Kendini sına

• Soru 1: DÜŞEYARA işlevinin dört bağımsız değişkenini sırasıyla yazın.
• Soru 2: Tablo dizisi B2:D20 seçildiyse ve getirmek istediğiniz bilgi D sütunundaysa sütun indis sayısı kaç olmalıdır?
• Soru 3: Ürün kodu aramalarında son bağımsız değişken neden YANLIŞ olmalıdır?
• Soru 4: Yaklaşık eşleşme kullanırken ilk sütun için hangi koşul sağlanmalıdır?
• Soru 5: DÜŞEYARA, aranan değerin solundaki bir sütundan bilgi getirebilir mi?

Cevaplar

• Cevap 1: Aranan değer, tablo dizisi, sütun indis sayısı, aralık bakma (YANLIŞ ya da DOĞRU).
• Cevap 2: 3. Sayım seçilen aralığın ilk sütunundan (B) başlar: B 1, C 2, D 3.
• Cevap 3: Çünkü tam eşleşme ister. Kod yoksa yanlış bir fiyat getirmek yerine #YOK hatası verir. Böylece hata gözden kaçmaz.
• Cevap 4: İlk sütun küçükten büyüğe doğru sıralı olmalıdır. Aksi hâlde sonuçlar güvenilmez olur.
• Cevap 5: Hayır. DÜŞEYARA yalnızca aralığın en solundaki sütunda arar ve o sütunun sağındaki sütunlardan değer getirir. Bu sınırlamayı bir sonraki derste göreceğimiz XLOOKUP ortadan kaldırır.

Bir sonraki derste bu sınırlamayı aşan XLOOKUP ile aynı işleri daha esnek biçimde yapacağız.