DÜŞEYARA (VLOOKUP), bir tablonun ilk sütununda aranan değeri bulup aynı satırdaki başka bir sütundan sonuç getirir. DÜŞEYARA fonksiyonunu daha hızlı kullanmak için arama aralığını daraltın, uçucu işlevlerden kaçının, birden çok sütun getirirken KAÇINCI sonucunu yeniden kullanın ve sıralı verilerde yaklaşık eşleşmeyi doğru yerde seçin. Bu sayfadaki yöntemler, binlerce satırlık tablolarda hesaplama süresini gözle görülür biçimde kısaltır.
Yavaşlığın başlıca nedenleri şunlardır:
KAÇINCI ve İNDİS ikilisiyle aynı sonucu şu formülle alabilirsiniz:
Tek sütun için iki yaklaşım arasındaki fark küçüktür; asıl kazanç, KAÇINCI sonucunu birden çok sütunda paylaştığınız durumlarda ortaya çıkar.
Yaklaşık eşleşme şu tür işler için uygundur:
K-105 kodu için ürün adı Yazıcı, fiyat 4.200,00 olarak gelir. K-101 için Dizüstü Bilgisayar ve 24.500,00, K-108 için Kulaklık ve 1.250,00 sonuçları alınır. Aralık yalnızca dolu satırları kapsadığından Excel boş hücreleri taramaz; formülü aşağı kopyaladığınızda $ işaretleri sayesinde aralık kaymaz.
1005 numaralı sicil, listenin 5. satırında bulunur ve departmanı Lojistik olarak döner. 1002 için satır 2, departman Satış; 1008 için satır 8, departman Satış sonucu alınır. Ad Soyad da gerekirse B sütunu için yalnızca ikinci bir İNDİS formülü yazmanız yeterlidir; arama tekrar yapılmaz.
S-2401 siparişi için iskonto %0, S-2402 için %3, S-2403 için %5 bulunur. S-2404 siparişinde %8, S-2405 siparişinde %10 uygulanır. 9.800 tutarındaki S-2406 siparişi için sonuç %3 olur; çünkü Excel, aranan değerden büyük olmayan en yakın alt limiti seçer. Bu yöntem yalnızca A sütunu artan sırada kaldığı sürece doğru çalışır.
DÜŞEYARA neden yavaş çalışır?
Tam eşleşme aramasında (son bağımsız değişken YANLIŞ) Excel, aranan değeri bulana kadar tablonun ilk sütununu baştan sona tarar. Aralık uzadıkça ve aynı aramayı yapan formül sayısı arttıkça işlem süresi de uzar. Örneğin 10.000 satırlık bir tabloya 10.000 DÜŞEYARA formülü bağlıysa, en kötü durumda 100 milyon karşılaştırma yapılır.Yavaşlığın başlıca nedenleri şunlardır:
- Gereğinden geniş aralık: Kullanılmayan satır ve sütunlar da taramaya girer.
- Çok sayıda tekrar eden arama: Aynı satır için her sütunda ayrı bir DÜŞEYARA çalıştırılır.
- Uçucu işlevler: DOLAYLI, KAYDIR, BUGÜN ve ŞİMDİ her hesaplamada yenilenir; bunlara bağlı DÜŞEYARA formülleri de her seferinde yeniden hesaplanır.
- Sık yeniden hesaplama: Kaynak tabloda yapılan her değişiklik, ona bağlı tüm formülleri tetikler.
DÜŞEYARA formülünü hızlandırma yöntemleri
Aşağıdaki adımlar, büyük dosyalarda en çok zaman kazandıran yöntemlerdir:- Aralığı daraltın: Tabloya yalnızca gerçekten gereken satır ve sütunları ekleyin; aranan sütundan sonuç sütununa kadar olan bölümü almak yeterlidir.
- Aralığı kilitleyin: Formülü aşağı kopyalarken aralığın kaymaması için mutlak başvuru ($A$2:$C$9) kullanın.
- Tablo kullanın: Ekle > Tablo (Insert > Table) ile oluşturulan tablolarda aralık veri eklendikçe kendiliğinden genişler; gereksiz boş satırları baştan hesaba katmak zorunda kalmazsınız.
- Uçucu işlevlerden kaçının: Arama aralığını DOLAYLI ya da KAYDIR ile oluşturmayın; sabit başvuru ya da ad kullanın.
- KAÇINCI sonucunu yeniden kullanın: Aynı satırdan birkaç sütun getirecekseniz satır numarasını bir kez KAÇINCI ile bulun, değerleri İNDİS ile alın.
- Hesaplamayı yönetin: Formüller > Hesaplama > Hesaplama Seçenekleri > El ile (Formulas > Calculation > Calculation Options > Manual) seçeneğiyle toplu düzenleme sırasında yeniden hesaplamayı bekletin; işiniz bitince F9 tuşuna basın.
- Sonucu sabitleyin: Verisi artık değişmeyecek sonuçlar için Giriş > Yapıştır > Değerleri Yapıştır (Home > Paste > Paste Values) kullanın.
- Yeni işlevleri değerlendirin: Excel 365'te ÇAPRAZARA, sıralı veride ikili arama seçeneği de sunar.
KAÇINCI ve İNDİS ikilisiyle aynı sonucu şu formülle alabilirsiniz:
Yaklaşık eşleşme ve sıralı veri ne zaman hız kazandırır?
DÜŞEYARA'nın son bağımsız değişkeni DOĞRU olduğunda (ya da hiç yazılmadığında) Excel yaklaşık eşleşme yapar ve sıralı verilerde ikili arama kullanır. Bu yöntem çok büyük tablolarda tam eşleşmeden belirgin biçimde hızlıdır. Ancak ilk sütunun artan düzende sıralı olması şarttır.Yaklaşık eşleşme şu tür işler için uygundur:
- Kademeli oranlar: İskonto, komisyon ya da prim dilimleri.
- Not ve puan aralıkları: Puanın hangi aralığa düştüğünü bulma.
- Tarih bazlı dilimler: Sıralı başlangıç tarihlerine göre dönem eşleştirme.
1. Dar aralık ve tam eşleşme ile ürün arama
Soldaki tabloda sekiz ürün listelenmiştir. Sağdaki üç kodun ürün adını ve fiyatını, yalnızca A2:C9 aralığını tarayan tam eşleşmeli DÜŞEYARA ile getirin. E2 hücresinde ürün adı için ikinci sütun, F2 hücresinde fiyat için üçüncü sütun kullanılır.DÜŞEYARA Fonksiyonunu Daha Hızlı Kullanma
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ürün Kodu | Ürün Adı | Birim Fiyat | Aranan Kod | Ürün | Fiyat |
| 2 | K-101 | Dizüstü Bilgisayar | 24.500,00 | K-105 | Yazıcı | 4.200,00 |
| 3 | K-102 | Kablosuz Fare | 450,00 | K-101 | Dizüstü Bilgisayar | 24.500,00 |
| 4 | K-103 | Klavye | 780,00 | K-108 | Kulaklık | 1.250,00 |
| 5 | K-104 | Monitör | 6.900,00 | |||
| 6 | K-105 | Yazıcı | 4.200,00 | |||
| 7 | K-106 | Tarayıcı | 3.100,00 | |||
| 8 | K-107 | Web Kamerası | 950,00 | |||
| 9 | K-108 | Kulaklık | 1.250,00 |
Ürün FiyatExcelDepo v1.0.0
2. KAÇINCI sonucunu yeniden kullanarak arama
Sicil numarasına göre departman getirilecek. Satır numarası E sütunundaki KAÇINCI formülüyle yalnızca bir kez bulunur, F sütunundaki İNDİS formülü bu numarayı kullanır. Aynı E sütunu Ad Soyad gibi başka sütunlar için de kullanılabilir.DÜŞEYARA Fonksiyonunu Daha Hızlı Kullanma
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sicil No | Ad Soyad | Departman | Aranan Sicil | Satır | Departman Bilgisi |
| 2 | 1001 | Ayşe Yılmaz | Muhasebe | 1005 | 5 | Lojistik |
| 3 | 1002 | Mehmet Kaya | Satış | 1002 | 2 | Satış |
| 4 | 1003 | Zeynep Demir | İnsan Kaynakları | 1008 | 8 | Satış |
| 5 | 1004 | Can Öztürk | Satın Alma | |||
| 6 | 1005 | Elif Çelik | Lojistik | |||
| 7 | 1006 | Burak Arslan | Bilgi İşlem | |||
| 8 | 1007 | Selin Aydın | Üretim | |||
| 9 | 1008 | Emre Şahin | Satış |
Personel ListesiExcelDepo v1.0.0
3. Sıralı tabloda yaklaşık eşleşme ile iskonto bulma
Soldaki iskonto tablosu alt limitlere göre artan sırada dizilidir. Sipariş tutarının hangi dilime girdiği, son bağımsız değişkeni DOĞRU olan DÜŞEYARA ile bulunur. Tablo sıralı olduğu için Excel ikili arama kullanır.DÜŞEYARA Fonksiyonunu Daha Hızlı Kullanma
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Alt Limit | İskonto Oranı | Sipariş No | Tutar | İskonto | |
| 2 | 0 | 0% | S-2401 | 3.200,00 | 0% | |
| 3 | 5.000 | 3% | S-2402 | 7.800,00 | 3% | |
| 4 | 10.000 | 5% | S-2403 | 12.500,00 | 5% | |
| 5 | 25.000 | 8% | S-2404 | 26.000,00 | 8% | |
| 6 | 50.000 | 10% | S-2405 | 51.000,00 | 10% | |
| 7 | S-2406 | 9.800,00 | 3% |
İskonto TablosuExcelDepo v1.0.0
Sık sorulan sorular
- Tam sütun başvurusu (A:C) DÜŞEYARA'yı yavaşlatır mı? Excel çoğu durumda yalnızca kullanılan aralığı işlediği için fark küçük olabilir. Yine de tablo ya da sınırlı aralık kullanmak hem hızı hem formülün okunabilirliğini korur.
- İNDİS-KAÇINCI her zaman DÜŞEYARA'dan hızlı mıdır? Hayır. Tek sütun getirirken fark küçüktür; KAÇINCI sonucunu birçok sütunda yeniden kullandığınızda kazanç belirginleşir.
- Hesaplamayı el ile yapmak güvenli mi? Evet, ancak sonuçlar F9 tuşuna basana kadar eski kalır. Dosyayı paylaşmadan önce hesaplamayı yenilemeyi unutmayın.
- Aranan değeri bulamazsam ne olur? Tam eşleşmede formül #YOK hatası verir. Bu hatayı EĞERYOKSA ile yakalayabilirsiniz; böylece aramayı iki kez çalıştırmak zorunda kalmazsınız.
Kullanırken dikkat edin
- Sıralı olmayan veride DOĞRU kullanmayın: Yaklaşık eşleşme sıralı veri bekler. Sıralama bozuksa Excel hata vermeden yanlış bir satırı döndürebilir. Tam karşılık gereken aramalarda son bağımsız değişkeni YANLIŞ yapın.
- Sütun indis numarası kayabilir: Tabloya sütun ekler ya da silerseniz sabit yazdığınız sütun numarası yanlış sütunu işaret edebilir. Bu risk için İNDİS ve KAÇINCI birlikte ya da ÇAPRAZARA daha güvenlidir.
- Sayı ve metin uyuşmazlığı: Aranan değer sayı, tablodaki karşılığı metin olarak saklanmışsa (ya da tersi) formül #YOK hatası verir. Hataları inceleyip veri türlerini eşitleyin.
- Aramayı iki kez çalıştırmayın: Hataları yakalamak için DÜŞEYARA'yı bir koşulun içinde iki kez yazmak hesaplama süresini ikiye katlar. Bunun yerine EĞERYOKSA ya da EĞERHATA ile tek aramada hatayı karşılayın.
- Uçucu işlevlere dikkat: DOLAYLI, KAYDIR, BUGÜN ve ŞİMDİ her hesaplamada yenilenir. Bu işlevlere bağlı geniş DÜŞEYARA zincirleri dosyayı yavaşlatır; mümkünse bunları arama aralığından uzak tutun.
- Önce sorunu ölçün: Dosya yavaşsa asıl nedenin DÜŞEYARA olduğundan emin olun. Hesaplama seçeneğini geçici olarak El ile yaparak ve F9 ile yenileyerek hangi bölümün zaman aldığını anlayabilirsiniz.
- İlgili konular: DÜŞEYARA, YATAYARA, İNDİS, KAÇINCI, ÇAPRAZARA, EĞERYOKSA, EĞERHATA, DOLAYLI, KAYDIR, Excel tabloları, Ad Yöneticisi ve Hesaplama Seçenekleri.
Ekli dosyalar
Son düzenleme:
