DüşeyAra formülü ile bir alt tabloya birden fazla değer çekebiliriz. Örnek olarak dinamik tablolarda kullanabileceğiniz alt tabloları gösterebilirim. Özellikle Office 365 1912 sürümünden itibaren tüm tabloyu tek bir komutla oluşturabilirsiniz.

Not: Burada gösterdiğim örnek sadece bilgi amaçlıdır, aynı sonuca DüşeyAra içinde aranan değer kısmında alt tablodaki sütun başlıklarının adresini kullanarak çok hızlı bir şekilde elde edebilirsiniz. Hatta alt tablo başlıklarını ana tablodan otomatik olarak çekmek daha da avantajlı olacaktır. Ancak Excel 365 1912 ve üstü sürümler ile bu yöntemi kullanmak daha avantajlı olabilir.

1. Statik Dizi İle Çoklu Değer Döndürme

DüşeyAra ile tek bir formül ile birden fazla değer döndürebiliriz. İlginç gelecektir, işin içine birden fazla değer girince konu dizi formülleri oluyor, biz de DüşeyAra ile bir dizi formülü oluşturacağız. Hatta Excel’in yeni sürümünde gelen ve dinamik dizi formülü olan SIRALI işlevini de kullanacağım.

Örnek olarak ben Ana Tablodaki 2 ve 5. Sütunlarda yer alan tüm verileri çekmek istiyorum, bunu tek bir farkla daha önce kullandığım düşey ara ile yapacağım, o fark ta çekmek istediğim sütun numarası yerine bir sabit dizi gireceğim:

=DÜŞEYARA($B$11;AnaTablo;{2;3;4;5};0)

CTRL+SHIFT+ENTER tuşlarına basarak bu formülü dizi formülü haline getirdiğimide tablodaki 2. ila 5. sütunlar arasındaki tüm değerleri çekebilirim.

Eğer elinizde Excel 365 güncel sürümü var ise formülün döndürdüğü tüm değerler dinamik dizi olarak gösterilecektir. Daha önceki Excel sürümlerinde ise formülün döndürdüğü ilk değer gösterilir. Burada Excel Dinamik Dizilerin işleri ne kadar hızlandırdığını da görebilirsiniz.

Excel 2019 ve öncesi için ise hücrede gösterilecek değeri değiştirmek adına İNDİS formülünü kullanacağım:

=İNDİS(DÜŞEYARA($B$11;AnaTablo;{2;3;4;5};0);;1)

Formülün sonundaki 1 değerini değiştirerek farklı verileri hücre içinde gösterebilirim. Formül sonucunu görmek için aşağıdaki videoya bakabilirsiniz.

2. Formül Sütununa Göre Otomatik Değer Çektirme

Videoda gördüğünüz gibi bu el ile müdahale gerektiriyor. Bunun yerine formülü sütunlarda çoğalttığımda otomatik olarak bu sayının da artmasını istersek bu durumda bana her sütunda sürekli artan bir değer gerekir. Bunu SÜTUNSAY ile yapabilirim:

=SÜTUNSAY($B$3:B$3)

Bu formülü yana doğru çoğalttığımda I3:J3, I3:K3 gibi aralıklar elde edeceğim ve sütun toplamları da 1,2… şeklinde gidecek. Şimdi bu yeni formülü bir önceki fomüle ekleyelim:

=İNDİS(DÜŞEYARA($B$11;AnaTablo;{2;3;4;5};0);;SÜTUNSAY($B$3:B$3))

Aşağıdaki videoda formülün nasıl çalıştığını gösterdim.

3. Çekilecek Diziyi Otomatik Belirleme

Not: Eski Excel sürümlerini kullananlar için bu aşamadan sonrası gereksiz yere formülü uzatacak ve anlaşılabilirliğini azaltacaktır. Ancak Excel 365 1912 ve daha güncel sürümü kullananlar için dinamik dizi işlevinin etkisi ile kullanımı daha mantıklı olabilir.

Formül gayet güzel oldu ancak sabit bir dizi kullanarak çekilecek verileri seçiyorum. Bu da tabloda güncelleme olursa yazdığım formüldeki sabit diziyi el ile değiştirmem gerek demek. Bunu otomatiğe bağlamak çok güzel olurdu tabii. Bunun için öncelikle bana ana tabloda yer alan tüm sütunların adedi lazım:

=SÜTUNSAY(AnaTablo[#Üst Bilgiler])

Bu formül bana ana taboda yer alan tüm sütunların sayısı olan 5 değerini döndürecek. Sonrasında ilk sütundaki verileri istemediğim için 2 ila bu değer arasında sıralı bir dizi elde etmem gerek. Bunu Excel 365 güncel sürümü kullananlar için SIRALI işlevi ile yapabilirim:

=SIRALI(SÜTUNSAY(AnaTablo[#Üst Bilgiler]);;2;)

Ancak burada elde ettiğimiz değerler satırlarda, biz sütunlara yayılmış bir dizi istiyoruz. Bunun için devrik_dönüşüm işlevini kullanacağım:

=DEVRİK_DÖNÜŞÜM(SIRALI(SÜTUNSAY(AnaTablo[#Üst Bilgiler])-1;;2;))

Ve tabii bu sürümde çoklu değer döndüren formüllerde, tüm değerler dinamik dizi ile otomatik olarak gösterildiğinden bu formülü ilk başta kullandığım DüşeyAra formülüne eklemem yeterli:

=DÜŞEYARA($B$11;AnaTablo;DEVRİK_DÖNÜŞÜM(SIRALI(SÜTUNSAY(AnaTablo[#Üst Bilgiler])-1;1;2;));0)

İlginizi Çekebilecek Diğer Yazılar
Etiketler: , , ,

Leave a Reply