DÜŞEYARA işlevinde, genellikle col_index_no statikini tanımlarız. DÜŞEYARA formülünde, DÜŞEYARA(id,veri,3,0). Sorun, verilerin içine bir sütun eklediğimizde veya sildiğimizde ortaya çıkıyor. 3. sütundan önce veya sonra bir sütun çıkarırsak veya eklersek, 3. sütun artık istenen sütuna atıfta bulunmayacaktır. Bu bir sorun. Diğer, aranacak birden çok sütununuz olduğunda. Her formülde sütun dizinini düzenlemeniz gerekir. Basit kopyala-yapıştır yardımcı olmaz.
Peki ya DÜŞEYARA'ya başlıklara bakmasını ve yalnızca eşleşen başlıklar değerini döndürmesini söyleyebilirseniz. Buna iki yönlü DÜŞEYARA denir.
Örneğin, DÜŞEYARA formülüm varsaişaretler sütun, ardından DÜŞEYARA aramalı işaretler veri sütunu ve bu sütundan değer döndürür. Bu sorunumuzu çözecektir.
Hmm… Peki, bunu nasıl yapacağız? DÜŞEYARA işlevi içinde Eşleştirme İşlevini kullanarak.
Genel Formül
=DÜŞEYARA(arama_değeri,tablo_dizisi,MATCH(bakan_başlık,tablo_başlıkları,0),0)
Arama_değeri: tablo_dizisinin ilk sütunundaki arama değeri.
Masa dizisi: arama yapmak istediğiniz aralık. Örneğin, A2, D10.
Lookup_heading: tablo_dizisinin başlıklarında aramak istediğiniz başlık.
Tablo başlıkları: Tablo dizisindeki başlıkların referansı. Örneğin. tablo A2, D10 ise ve başlıklar her sütunun başındaysa, A1:D1.
Artık dinamik col_index için neye ihtiyacımız olduğunu biliyoruz, hadi her şeyi bir örnekle açıklığa kavuşturalım.
Dinamik DÜŞEYARA Örneği
Bu örnek için, A4:E16 aralığındaki öğrencilerin verilerini içeren bu tablomuz var.
No ve başlığı kullanarak bu tablodan veri almak istiyorum. Bu örnek için, H4 hücresinde, G4 hücresinde yazılı rulo no ve H3 hücresinde başlık verilerini almak istiyorum. Başlığı değiştirirsem, ilgili aralıktaki veriler H4 hücresinde alınmalıdır.
Bu formülü H4 hücresine yazın
=DÜŞEYARA(G4,B4:E16,MATCH(H3,B3:E3,0),0)
Tablo dizimiz B4:E16 olduğundan, başlık dizimiz B3:E3 olur.
Not: Verileriniz iyi yapılandırılmışsa, sütun başlıkları aynı sayıda sütuna sahip olacaktır ve bu, tablodaki ilk satırdır.
Nasıl çalışır:
Bu nedenle, ana kısım sütun dizin numarasını otomatik olarak değerlendirmektir. Bunun için MATCH fonksiyonunu kullandık.
MAÇ(H3,B3:E3,0): H3 “öğrenci” içerdiğinden, MAÇ 2 döndürür. DÜŞEYARA formülü sonunda col_index_num değerine sahip olacaktır.
=DÜŞEYARA(G4,B4:E16,2,0)
Bildiğimiz gibi, KAÇINCI işlevi, sağlanan tek boyutlu aralıktaki belirli bir değerin dizin numarasını döndürür. Bu nedenle, MATCH, B3:E3 aralığında H3'te yazılan herhangi bir değeri arayacak ve indeks numarasını döndürecektir.
Şimdi ne zaman H3'te başlığı değiştirecekseniz, eğer başlıklarda ise, bu formül ilgili sütundan bir değer döndürecektir. Aksi takdirde, bir #YOK hatası alırsınız.
DÜŞEYARA Hızla Birden Çok Sütunda
Yukarıdaki örnekte, bir sütun değerinden cevaba ihtiyacımız vardı. Ama ya aynı anda birden çok sütun almak istiyorsanız. Yukarıdaki formülü kopyalarsanız, hata döndürür. Taşınabilir hale getirmek için bazı küçük değişiklikler yapmamız gerekiyor.
DÜŞEYARA ile Mutlak Referansları Kullanma
Aşağıdaki formülü H2 hücresine yazın.
=DÜŞEYARA($G2,$B$2:$E$14,MATCH(H$1,$B$1:$E$1,0),0)
Şimdi, verileri doldurmak için H2:J6 aralığındaki tüm hücrelere H2'yi kopyalayın.
Nasıl çalışır:
işte ben verdim mutlak referans DÜŞEYARA ($G2) ve KAÇINCI için arama_değerindeki sütun (1$).
$G2: Bu, aşağı doğru kopyalarken satırın DÜŞEYARA işlevi için arama değerini değiştirmesine izin verir, ancak sağa kopyalandığında sütunun değişmesini kısıtlar. Bu, DÜŞEYARA'nın yalnızca ilgili satırla G sütunundan Kimliği aramasını sağlar.
Benzer şekilde, 1$ yatay olarak kopyalandığında sütunun değişmesine izin verir ve aşağı doğru kopyalandığında satırı kısıtlar.
Adlandırılmış Aralıkları Kullanma
Yukarıdaki örnek iyi çalışıyor ancak bu formülü okumak ve yazmak zorlaşıyor. Ve bu hiç taşınabilir değil. Bu kullanılarak basitleştirilebilir adlandırılmış aralıklar.
İlk önce burada biraz isimlendirme yapacağız. Bu örnek için, adını verdim
$B$2:$E$14 : Veri olarak
$B$1:$E$1 : Başlıklar olarak
1 $ : Başlık olarak adlandırın. Sütunları göreceli yapın. Bunu yapmak için H1'i seçin. CTRL+F3'e basın, yeni'ye tıklayın, Referans bölümünde H'nin önünden '$' işaretini kaldırın.
$G2: Benzer şekilde, RollNo olarak adlandırın. Bu sefer, 2'nin önünden '$' kaldırılarak satırı göreceli hale getirir.
Şimdi, sayfadaki tüm isimlere sahip olduğunuzda, bu formülü excel dosyasının herhangi bir yerine yazın. Her zaman doğru cevabı alacaktır.
=DÜŞEYARA(DönerNo,Veri, MATCH(Başlık, Başlıklar,0),0)
Bakın, herkes bunu okuyabilir ve anlayabilir.
Bu yöntemleri kullanarak col_index_num'u dinamik hale getirebilirsiniz. Bunun aşağıdaki yorumlar bölümünde yardımcı olup olmadığını bana bildirin.
nasıl kullanılırExcel'de DÜŞEYARA İşlevi
Excel'de Göreli ve Mutlak Başvuru
Excel'de Adlandırılmış Aralıklar
Farklı Excel Sayfasından DÜŞEYARA Nasıl Yapılır
DÜŞEYARA Birden Çok Değer
Popüler Makaleler
Verimliliğinizi Arttıracak 50 Excel Kısayolu : Görevinizde daha hızlı olun. Bu 50 kısayol, Excel'de daha da hızlı çalışmanızı sağlayacaktır.
nasıl kullanılırExcel'de DÜŞEYARA İşlevi : Bu, farklı aralık ve sayfalardan değer aramak için kullanılan excel'in en çok kullanılan ve popüler işlevlerinden biridir.
Excel'de COUNTIF işlevi nasıl kullanılır? : Bu şaşırtıcı işlevi kullanarak değerleri koşullarla sayın. Belirli değerleri saymak için verilerinizi filtrelemeniz gerekmez. Gösterge tablonuzu hazırlamak için Countif işlevi gereklidir.
Excel'de SUMIF İşlevi nasıl kullanılır? : Bu, başka bir gösterge panosu temel işlevidir. Bu, belirli koşullardaki değerleri özetlemenize yardımcı olur.