Orta Ders 3: DÜŞEYARA ile İki Tabloyu Birleştirme

İki tablo neden ayrı tutulur?

Ürün bilgisini ayrı bir tabloda tutmak, aynı bilgiyi her siparişte yeniden yazma zorunluluğunu ortadan kaldırır. Sipariş listesinde yalnız ürün kodu ve adet bulunur. Ürünün adı ve fiyatı ise tek bir yerde, ürün tablosunda durur.

Fiyat değiştiğinde yalnız ürün tablosunu güncellersiniz. Siparişlerdeki tutarlar, kodu ürün tablosunda arayan formül sayesinde kendiliğinden yenilenir. İki tabloyu birbirine bağlayan şey ortak sütundur; bu derste o sütun ürün kodudur.

DÜŞEYARA'nın dört argümanı

Excel DÜŞEYARA formülü bir değeri tablonun ilk sütununda arar ve bulduğu satırdan istediğiniz sütunu döndürür. Söz dizimi =DÜŞEYARA(aranan_değer; tablo_dizisi; sütun_indis_sayısı; [aralık_bak]) biçimindedir.

Örnek dosyada siparişler A:C sütunlarında, ürün tablosu F:H sütunlarında. İlk siparişin fiyatını bulmak için B2'deki kodu F2:H6 aralığında aratırsınız.

=DÜŞEYARA(B2;$F$2:$H$6;3;YANLIŞ)

=VLOOKUP(B2,$F$2:$H$6,3,FALSE)

K-01 kodu ürün tablosunun ilk satırında bulunur ve aralığın 3. sütunundaki 85 döner. Sütun numarası çalışma sayfasındaki sütun harfine göre değil, seçtiğiniz aralığın içindeki sıraya göre sayılır. F sütunu 1, G 2, H 3'tür.

Son argüman neden YANLIŞ olmalı?

Kod, sicil numarası ya da e-posta gibi anahtarlarla arama yaparken son argüman her zaman YANLIŞ olmalıdır. YANLIŞ tam eşleşme demektir. Bu argümanı boş bırakırsanız Excel yaklaşık eşleşme yapar ve listeniz sıralı değilse yanlış bir satırı sessizce getirebilir.

=DÜŞEYARA("K-03";F2:H6;2;YANLIŞ)

=VLOOKUP("K-03",F2:H6,2,FALSE)

Aynı formülle ürün adını almak için yalnız sütun numarasını 2 yaparsınız. K-03 için "Masa lambası" döner. Yaklaşık eşleşme ise gelir dilimi ya da prim baremi gibi sıralı eşik tablolarında işe yarar; bu derste ona ihtiyaç yok.

Aralığı sabitlemeyi unutmayın

Formülü sipariş listesinin tamamına kopyalayacaksanız ürün tablosunun adresini $ ile sabitlersiniz: $F$2:$H$6. Sabitlemezseniz ikinci satırda aralık F3:H7'ye kayar, ilk ürün aramanın dışında kalır. Bu hata hemen fark edilmez çünkü yalnız bazı satırlar #YOK verir.

Tutarı tek adımda bulmak için DÜŞEYARA sonucunu adetle çarparsınız: =C2*DÜŞEYARA(B2;$F$2:$H$6;3;YANLIŞ). S-101 siparişinde 3 adet kalem kutusu 255 TL eder.

#YOK hatası ne anlatır?

#YOK, aranan değerin tablonun ilk sütununda bulunamadığını anlatır. Nedenlerin başında yazım farkları gelir: "K-01 " gibi sonda boşluk kalması ya da bir tarafta sayı, diğer tarafta metin olarak saklanan kodlar. Önce iki hücreyi =B2=F2 ile karşılaştırın; YANLIŞ dönüyorsa fark verinin kendisindedir.

Kod gerçekten listede yoksa hata yerine açıklayıcı bir metin göstermek tabloyu okunur kılar. EĞERHATA, formül hata verdiğinde ikinci argümandaki değeri döndürür.

=EĞERHATA(DÜŞEYARA("K-09";F2:H6;3;YANLIŞ);"Kod yok")

=IFERROR(VLOOKUP("K-09",F2:H6,3,FALSE),"Kod yok")

Ürün listesinde K-09 olmadığı için "Kod yok" görünür. EĞERHATA'yı veriyi düzelttikten sonra eklemek daha güvenlidir. Aksi halde yazım hatası yüzünden bulunamayan kodlar da "Kod yok" olarak görünür ve gerçek sorun gizlenir.

DÜŞEYARA'nın sınırları

DÜŞEYARA yalnız sağa doğru bakar: aranan sütun tablonun en solunda olmak zorundadır. Kod sütunu ürün adının sağındaysa formül çalışmaz. Sütun numarasının sabit olması da bir risktir; tabloya araya yeni bir sütun eklediğinizde 3 yazan formül artık yanlış sütunu getirir.

Bu iki sınırın çözümü bir sonraki derste göreceğiniz İNDİS ve KAÇINCI ikilisidir. Microsoft 365 kullanıyorsanız ÇAPRAZARA da aynı sorunları tek fonksiyonla çözer.

Başka sayfadaki listeden veri çekmek

İşte ürün listesi çoğu zaman aynı sayfada değil, ayrı bir sayfada ya da ayrı bir dosyada tutulur. Formül mantığı değişmez, yalnız aralığın önüne sayfa adı gelir: =DÜŞEYARA(B2;Ürünler!$A$2:$C$300;3;YANLIŞ). Aralığı seçerken diğer sayfanın sekmesine tıklarsanız Excel adresi kendisi yazar.

Aylık fiyat listesi gönderen bir tedarikçiyle çalıştığınızı düşünün. Yeni listeyi Ürünler sayfasına yapıştırdığınızda bütün siparişlerin tutarı güncellenir. Listenin satır sayısı her ay değişiyorsa aralığı geniş tutmak ya da ürün listesini Excel tablosuna çevirmek, yeni ürünlerin aramanın dışında kalmasını önler.

Ürün listesi ayrı bir dosyadaysa iki dosyanın da açık olması en sorunsuz yoldur. Kaynak dosyanın adı ya da klasörü değişirse bağlantı kopar ve formüller eski değerleri göstermeye devam edebilir.

Bağlantılı dosyalarla çalışırken Veri sekmesindeki Bağlantıları Düzenle penceresi, dosyanın hangi kaynaklara bağlı olduğunu ve en son ne zaman güncellendiğini gösterir. Bir dosyayı başkasına göndermeden önce bu listeye bakmak, karşı tarafta açılmayan bağlantı uyarılarını önler.

Sıradaki adım

Alıştırmada beş siparişin tutarını, fiyatı ürün tablosundan getirerek hesaplayacaksınız. Formülü D2'ye yazdıktan sonra D6'ya kadar kopyalayın ve her satırın kendi kodunu aradığından emin olun.