DÜŞEYARA (VLOOKUP), bir tablodaki ortak anahtarı (ürün kodu, sicil numarası gibi) başka bir tabloda arayıp ilgili bilgiyi getiren işlevdir ve DÜŞEYARA ile tablo birleştirme işinin en yaygın yoludur. Elle kopyala-yapıştır yerine tek bir formül kullanmak hem zaman kazandırır hem de yanlış eşleştirme riskini azaltır. Bu sayfada söz dizimini, hata yönetimini ve verimliliği artıran uygulamaları örneklerle bulacaksınız.
Bu formülde KAÇINCI anahtarın satır sırasını bulur, İNDİS ise o sıradaki değeri getirir. Anahtar sütunu ile getirilen sütun ayrı olduğu için tablo sütunlarının sırası önemli değildir. Excel 2021 ve Microsoft 365'te bulunan ÇAPRAZARA (XLOOKUP) işlevi de sola bakabilir ve bulunamayan durum için varsayılan sonuç belirleyebilir; ancak DÜŞEYARA eski sürümlerle uyumlu olduğu için hala yaygındır.
KLV-01 kodunun liste fiyatı 450,00 olarak gelir, adetle çarpılmış tutar ise 2.250,00 olur. Aralık $E$2:$F$6 şeklinde sabitlendiği için formülü aşağı kopyaladığınızda arama tablosu kaymaz; örneğin EKR-03 kodunun 3.850,00 olan fiyatı aynı tablodan gelir. Fiyat listesinde bir değişiklik yaparsanız tüm tutarlar kendiliğinden yeniden hesaplanır.
Kayıtlarda bulunan sicil numaralarında C sütunu departmanı getirir; örneğin Ayşe Yılmaz için kayıttaki departman olan Satış görünür. 1004 ve 1007 numaralı satırlarda Kayıt yok görünür, çünkü bu sicil numaraları kayıt tablosunda yoktur. D sütunundaki Kontrol edin sonuçları hangi personelin kayıtlarının kontrol edilmesi gerektiğini hemen gösterir.
Her satış tutarı için, kendisinden küçük ya da eşit olan en büyük alt sınır bulunur ve o kademenin oranı getirilir. Ayşe Yılmaz'ın oranı %0, prim tutarı 0,00 olur. Mustafa Şahin'in satışı 50000 ile alt sınıra tam eşit olduğundan o kademenin oranı olan %1 gelir. Yaklaşık eşleşmede alt sınır sütunu küçükten büyüğe sıralı olmalıdır; sıralama bozulursa sonuçlar yanlış çıkar.
DÜŞEYARA ile iki tablo nasıl birleştirilir
DÜŞEYARA, aranan değeri seçtiğiniz aralığın ilk sütununda arar ve bulduğu satırdaki istediğiniz sütunun değerini getirir. İki tabloyu birleştirmek için her iki tabloda da aynı değeri taşıyan bir anahtar sütun bulunmalıdır. Söz dizimi şöyledir:- aranan_değer: Eşleştirilecek anahtar. Genellikle formülün bulunduğu satırdaki hücreye başvurulur.
- tablo_dizisi: Anahtarın ilk sütunda, getirilecek bilginin ise aynı aralıkta yer aldığı tablo.
- sütun_indis_sayısı: Getirilecek sütunun, aralığın ilk sütununa göre kaçıncı sütun olduğu.
- aralık_bak: YANLIŞ (FALSE) tam eşleşme arar, DOĞRU (TRUE) yaklaşık eşleşme arar. Kod, numara gibi anahtarlarda YANLIŞ kullanın.
Tablo birleştirmede hata riskini ve süreyi azaltma
Elle eşleştirme, satır sayısı arttıkça hem yavaşlar hem de kayma ve yanlış kopyalama hatalarına açık hale gelir. DÜŞEYARA ile eşleştirmeyi kuralı bir kez yazarak yaparsınız ve kaynak tablo değiştiğinde sonuçlar kendiliğinden güncellenir. Verimlilik için şu uygulamalara dikkat edin:- Anahtar sütunu temizleyin: Baştaki ve sondaki boşluklar ya da metin olarak saklanmış sayılar eşleşmeyi bozar. Gerekirse KIRP işleviyle boşlukları temizleyin.
- Aralığı sabitleyin: Tablo aralığını $E$2:$F$6 gibi mutlak başvuruyla yazın; aksi halde formülü aşağı kopyaladığınızda aralık kayar.
- Aralığı gereksiz büyütmeyin: Gerçek veri kadar bir aralık seçin. Kaynak veriyi Ekle > Tablo (Insert > Table) ile Excel tablosuna çevirirseniz, yapısal başvurular yeni satırlarla birlikte otomatik genişler.
- Bulunamayan kayıtları yönetin: Anahtar kaynak tabloda yoksa DÜŞEYARA #YOK hatası verir. EĞERHATA ile bu durumu okunur bir metne çevirebilirsiniz.
- Formülü adım adım izleyin: Sonuç beklediğiniz gibi değilse Formüller > Formül Denetleme > Formülü Değerlendir (Formulas > Formula Auditing > Evaluate Formula) ile hesabın hangi adımda saptığını görebilirsiniz.
- Çok sütun getirirken sütun numarasını sabit yazmayın: Kaynak tabloya sütun eklenirse sabit numara yanlış sütunu getirir. Sütun numarasını KAÇINCI ile başlık adından bulmak bu riski ortadan kaldırır.
DÜŞEYARA mı, İNDİS ve KAÇINCI mı
DÜŞEYARA yalnızca aralığın ilk sütunundan sağa doğru bakabilir; anahtarınız getirmek istediğiniz sütunun sağındaysa çalışmaz. Aynı anahtar birden çok kez geçiyorsa yalnızca ilk eşleşen satırı döndürür ve büyük küçük harf ayrımı yapmaz. Bu durumlarda İNDİS (INDEX) ile KAÇINCI (MATCH) birleşimi daha esnektir:Bu formülde KAÇINCI anahtarın satır sırasını bulur, İNDİS ise o sıradaki değeri getirir. Anahtar sütunu ile getirilen sütun ayrı olduğu için tablo sütunlarının sırası önemli değildir. Excel 2021 ve Microsoft 365'te bulunan ÇAPRAZARA (XLOOKUP) işlevi de sola bakabilir ve bulunamayan durum için varsayılan sonuç belirleyebilir; ancak DÜŞEYARA eski sürümlerle uyumlu olduğu için hala yaygındır.
1. Sipariş listesine fiyat listesinden birim fiyat çekme
A ve B sütunlarında sipariş edilen ürün kodları ve adetler, E ve F sütunlarında ise ürün fiyat listesi yer alıyor (KLV klavye, MOU fare, EKR ekran, HDD disk, USB bellek anlamındadır). Birim fiyatı listeden getirip tutarı hesaplayacaksınız. C2 hücresine DÜŞEYARA formülünü, D2 hücresine adet ile fiyatın çarpımını yazıp ikisini aşağı kopyalayın.DÜŞEYARA ile Tablo Birleştirme Verimliliğini Değerlendirme
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ürün Kodu | Adet | Birim Fiyat | Tutar | Liste Kodu | Liste Fiyatı |
| 2 | KLV-01 | 5 | 450,00 | 2.250,00 | KLV-01 | 450,00 |
| 3 | MOU-02 | 12 | 220,00 | 2.640,00 | MOU-02 | 220,00 |
| 4 | EKR-03 | 3 | 3.850,00 | 11.550,00 | EKR-03 | 3.850,00 |
| 5 | KLV-01 | 8 | 450,00 | 3.600,00 | HDD-04 | 1.750,00 |
| 6 | HDD-04 | 2 | 1.750,00 | 3.500,00 | USB-05 | 180,00 |
| 7 | MOU-02 | 6 | 220,00 | 1.320,00 | ||
| 8 | USB-05 | 20 | 180,00 | 3.600,00 | ||
| 9 | EKR-03 | 1 | 3.850,00 | 3.850,00 |
Sipariş FiyatExcelDepo v1.0.0
2. Eşleşmeyen kayıtları EĞERHATA ile yönetme
A ve B sütunlarında personel listesi, E ve F sütunlarında ise İnsan Kaynakları kayıtlarındaki sicil numaraları ve departmanlar bulunuyor. Kayıtlarda bulunmayan sicil numaraları #YOK hatası yerine okunur bir metinle görünsün ve D sütunu hangi satırların kontrol edilmesi gerektiğini göstersin istiyorsunuz.DÜŞEYARA ile Tablo Birleştirme Verimliliğini Değerlendirme
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sicil No | Ad Soyad | Departman | Durum | Kayıt Sicil No | Kayıt Departman |
| 2 | 1001 | Ayşe Yılmaz | Satış | Tamam | 1001 | Satış |
| 3 | 1002 | Mehmet Demir | Muhasebe | Tamam | 1002 | Muhasebe |
| 4 | 1003 | Fatma Kaya | Lojistik | Tamam | 1003 | Lojistik |
| 5 | 1004 | Ahmet Çelik | Kayıt yok | Kontrol edin | 1005 | İnsan Kaynakları |
| 6 | 1005 | Zeynep Arslan | İnsan Kaynakları | Tamam | 1006 | Satış |
| 7 | 1006 | Mustafa Şahin | Satış | Tamam | 1008 | Üretim |
| 8 | 1007 | Elif Aydın | Kayıt yok | Kontrol edin | ||
| 9 | 1008 | Can Özkan | Üretim | Tamam |
Personel EşleştirmeExcelDepo v1.0.0
3. Satış tutarına göre prim oranı bulma
B sütunundaki satış tutarlarına göre prim oranını E ve F sütunlarındaki kademe tablosundan bulacaksınız. Tablo alt sınırlara göre küçükten büyüğe sıralıdır ve son bağımsız değişken DOĞRU olduğu için DÜŞEYARA tam eşleşme yerine tutarın ait olduğu kademeyi bulur.DÜŞEYARA ile Tablo Birleştirme Verimliliğini Değerlendirme
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Temsilci | Satış Tutarı | Prim Oranı | Prim Tutarı | Alt Sınır | Oran |
| 2 | Ayşe Yılmaz | 38.500,00 | 0% | 0,00 | 0 | 0% |
| 3 | Mehmet Demir | 72.400,00 | 1% | 724,00 | 50.000 | 1% |
| 4 | Fatma Kaya | 125.000,00 | 2% | 2.500,00 | 100.000 | 2% |
| 5 | Ahmet Çelik | 99.000,00 | 1% | 990,00 | 150.000 | 3% |
| 6 | Zeynep Arslan | 210.500,00 | 4% | 8.420,00 | 200.000 | 4% |
| 7 | Mustafa Şahin | 50.000,00 | 1% | 500,00 | ||
| 8 | Elif Aydın | 15.200,00 | 0% | 0,00 | ||
| 9 | Can Özkan | 148.300,00 | 2% | 2.966,00 |
Prim KademeleriExcelDepo v1.0.0
Sık sorulan sorular
- DÜŞEYARA neden #YOK hatası verir? En sık nedenler şunlardır: anahtar kaynak tabloda yoktur, anahtarlarda fazladan boşluk vardır, biri sayı diğeri metin olarak saklanmıştır ya da tam eşleşme gerekirken son bağımsız değişken yanlış girilmiştir.
- Başka bir çalışma kitabındaki tabloyla birleştirebilir miyim? Evet, formülü yazarken tablo aralığını diğer çalışma kitabında seçmeniz yeterlidir. Kaynak dosyanın adını veya yerini değiştirirseniz bağlantı kopabilir.
- Aynı anahtar birden fazla satırda varsa ne olur? DÜŞEYARA yalnızca ilk eşleşen satırın değerini getirir. Tüm eşleşmeleri toplamak için ÇOKETOPLA gibi işlevler kullanılır.
- Birden fazla sütunu tek seferde getirebilir miyim? DÜŞEYARA her formülde tek sütun döndürür; her sütun için formülü sütun numarasını değiştirerek kopyalamanız gerekir. Microsoft 365'te ÇAPRAZARA birden çok sütunu tek formülle döndürebilir.
Kullanırken dikkat edin
- Anahtar sütun ilk sütunda olmalı: DÜŞEYARA yalnızca tablo aralığının en soldaki sütununda arama yapar. Anahtar, getirmek istediğiniz sütunun sağında kalıyorsa İNDİS ve KAÇINCI birleşimini ya da ÇAPRAZARA işlevini kullanın.
- Metin ve sayı uyuşmazlığı: Bir tabloda sayı, diğerinde metin olarak saklanan kodlar eşleşmez ve #YOK hatası üretir. Hücrelerin sol üst köşesindeki yeşil üçgeni ve hücre hizalamasını kontrol edin; metin biçimli sayıları gerçek sayıya çevirin.
- Son bağımsız değişkeni boş bırakmayın: Son bağımsız değişken atlanırsa DÜŞEYARA yaklaşık eşleşme yapar ve sıralı olmayan tablolarda sessizce yanlış sonuç getirebilir. Tam eşleşme gerekiyorsa her zaman YANLIŞ yazın.
- Sütun numarası sabittir: Kaynak tabloya sütun eklediğinizde ya da sildiğinizde, formüldeki sütun numarası otomatik uyarlanmaz. Tablo yapısı sık değişiyorsa sütun numarasını KAÇINCI ile üretin.
- EĞERHATA her hatayı gizler: EĞERHATA yalnızca bulunamayan kayıtları değil, yanlış aralık gibi diğer hataları da maskeler. Formülü kurarken önce hatalı hali görün, sonra EĞERHATA ile sarmalayın.
- Birden fazla eşleşme: Anahtar kaynak tabloda tekrar ediyorsa DÜŞEYARA ilk bulduğu satırı getirir ve diğerlerini görmezden gelir. Birleştirme öncesinde anahtar sütununun benzersiz olduğundan emin olmak için EĞERSAY ile tekrarları denetleyin.
- Çok büyük tablolar: Yüz binlerce satırlık tabloları formüllerle eşleştirmek çalışma kitabını yavaşlatabilir. Bu durumda verileri Power Query ile birleştirmeyi değerlendirin.
- İlgili konular:
- İNDİS ve KAÇINCI: Sola doğru arama ve esnek sütun seçimi için.
- ÇAPRAZARA: Excel 2021 ve Microsoft 365'te DÜŞEYARA'nın daha esnek halefi.
- YATAYARA: Başlıkları ilk satırda olan tablolarda yatay arama için.
- EĞERHATA: Bulunamayan kayıtlarda hata yerine okunur sonuç göstermek için.
- KIRP: Anahtar sütunlardaki fazladan boşlukları temizlemek için.
- EĞERSAY: Anahtar değerlerin tekrarını denetlemek için.
- ÇOKETOPLA: Aynı anahtara ait birden fazla satırı toplamak için.
Ekli dosyalar
Son düzenleme:
