Listede aynı değerde birden fazla olduğunda bunların kaç adet olduğunu ve sıralanmış ise sadece bu değerleri bir alt tabloya ayırmayı göstermiştim. Peki biz buradaki tüm değerleri sıralama yapmadan başka bir tabloya nasıl çekebiliriz? Bu yazımda bunu nasıl yapabileceğimizi göstereceğim.
1. Hazırlık
Bu uygulamaya başlamadan önce indis, toplama, kaydır formüllerini bildiğinizi varsayıyorum. Eğer bu formüllerin yazılımı ve nasıl çalıştığını bilmiyorsanız önce ilgili yazılarımı okuyun lütfen.
Ben aşağıda verdiğim tablo üzerinden uygulama yapacağım. Siz isterseniz bu tabloyu isterseniz elinizdeki farklı bir tabloyu kullanabilirsiniz. Eğer kendi tablonuzu kullanacaksanız formüllerde kullandığım hücre adreslerini kendi tablonuza göre uyarlayın.
Aradığım değerleri tutmak üzere bana ikinci bir tablo lazım. Bu ikinci tabloyu ben F2 hücresinden itibaren aşağıdaki gibi hazırladım. Siz istediğiniz sayfada istediğiniz yerde bu tabloyu oluşturabilirsiniz. Tabii formül yazarken ilgili referanslara dikkat etmeniz gerek.
2. Uygulama
a. Satır ve Eşleşen Satırları Bulma
İlk aşamada yapmak istediğim şey aradığım değerin verdiğim bir aralıktaki değerlerle karşılaştırmak ve eşleşen satırların numarasını döndürmek. Hücrenin satırını bulmak için kullandığımız formülü yazmakla başlayalım:
=SATIR($B$2:$B$12)
Bu formülün sonucu {2, 3, 4, … } şekliden bir dizin olacaktır. Aradığım değeri bir liste içindeki değerler ile kıyaslama formülü de diğer yazılarımda da olduğu gibi oldukça basit:
=($F$3=$B$2:$B$12)
Bu formül de { YANLIŞ, DOĞRU, YANLIŞ,….} şeklinde bir dizin döndürecektir. Bu iki dizini bölersek:
=(SATIR($B$2:$B$12))/($F$3=$B$2:$B$12)
bu formül ile satır numarası, karşılaştırma sonucu YANLIŞ ise 0, DOĞRU ise 1 değeri ile bölünür ve elimizde {#SAYI/0!, 4, #SAYI/0!, #SAYI/0!, #SAYI/0!…} gibi bir dizin geçer. Dikkat ederseniz bu dizin bizim istediğimiz satır numaralarını ve hata değerlerini barındırıyor.
b. Dizin Oluşturma ve Değeri Çekme
Hata değerini dikkate almadan rakam değerlerini nasıl çekebiliriz? Önce bu formülü dizin olarak işlenebilecek hale getirmemiz gerek. Bunu da İNDİS formülü ile yapıyoruz.
=İNDİS((SATIR($B$2:$B$12))/($F$3=$B$2:$B$12);;)
Artık bu dizinden eşleşme olan satır numaralarını çekebiliriz. Dikkat ederseniz eşleşmenin gerçekleştiği satır değerleri küçükten büyüğe doğru artıyor. Bu desenden küçük olan değerleri, hata değerlerini ele almadan çekmemiz gerek. Bunun için de TOPLAMA formülünü kullanabiliriz. Hangi küçük değerini alacağımız ise tabii ki formülün yazıldığı satırdan gelmeli.
Şimdi isterseniz ilk olarak hangi en küçük değeri çekeceğimizi, formülün içinde bulunduğu satıra göre hesaplayan formülü yazalım:
=SATIR()-SATIR($G$2)
Eğer bu formülü G3 hücresine yazarsak 1, G4 hücresine yazarsak 2 değerini elde ederiz. Bu sayede listemizdeki en küçük 1,2,… değerlerini alt alta olacak şekilde çekebiliriz. Şimdi tüm bu formülleri TOPLAMA formülünde birleştirelim:
=TOPLAMA(15;3;İNDİS((SATIR($B$2:$B$12))/($F$3=$B$2:$B$12);;);SATIR()-SATIR($G$2))
Bu formülde 15 rakamı en küçük değerleri bulmak istediğimizi, 3 ise hatalı değerleri yok sayılması istediğimizi ifade ediyor. Formülü aşağı doğru çoğaltınca sırası ile 4, 9, 12 döndürüyor. Eğer istediğimiz rakam yok ise #SAYI! hatası veriyor. Bununla sonradan ilgileneceğiz.
c. Kaydır Formülü İle Ana Tablodan Değer Çekme
Şimdi önce KAYDIR ile ana tablodan, alt tabloya almak istediğimiz değerleri çekelim:
=KAYDIR($B$1;TOPLAMA(15;3İNDİS((SATIR($B$2:$B$12))/($F$3=$B$2:$B$12);;);SATIR()-SATIR($G$2))-1;1;1;1)
Bu formülü ben G3 hücresine yazarak aşağıya doğru uzattığımda sırası ile Hasan 1, Hasan 2 ve Hasan 3 değerlerini çektim. formül Sonrasında #SAYI! hatası verdi. Bu hatayı da EĞERHATA formülü ile engelleyebiliriz. Formülümüze bunu da ekleyelim:
=EĞERHATA(KAYDIR($B$1;TOPLAMA(15;3;İNDİS((SATIR($B$2:$B$12))/($F$3=$B$2:$B$12);;);SATIR()-SATIR($G$2))-1;1;1;1);””)
Bu formülü aşağıya doğru uzattığımızda artık hata olan değerler yerine bir şey gösterilmeyecek. Formülü bir yan sütunu çekecek şekilde güncelleyerek H sütununa da yazdığımda seçtiğim kişiye ait diğer verileri de yeni tabloya çekebiliyorum.
=EĞERHATA(KAYDIR($B$1;TOPLAMA(15;3;İNDİS((SATIR($B$2:$B$12))/($F$3=$B$2:$B$12);;);SATIR()-SATIR($G$2))-1;2;1;1);””)
Aşağıdaki videoda formüllerin çalışmasını gösterdim.
3. Neler Yapılabilir
Bu örnekte tablo kullanmadım. Sizler tablo üzerinde bu uygulamayı yapabilirsiniz. Ek olarak KAYDIR formülündeki sütun sayısı elle yazdım. Buraya da bir KAÇINCI formülü yazarak yardımcı tablonun başlıklarına göre ana tablodaki sütunu bulan bir formül yazılabilir.
Merhaba,
Elimdeki Excel sayfalarında;
TC ADI_SOYADI DOĞUM_TARİHİ CİNSİYETİ İL İLÇE gibi yaklaşık 250 sütunlardan oluşan ve yaklaşık 10.000 kişilik farklı çalışma sayfalarında listeler var. Bu listedeki sütunların yerleri her sayfada farklı sütunlarda. Örneğin 1. sayfada CİNSİYETİ D Sütununda iken 2. sayfada CİNSİYETİ K sütununda yer almaktadır. Ben bu listeden diğer bir çalışma sayfasına “eğer sütun başlığı ADI SOYADI ise A sütununa, eğer sütun başlığı DOĞUM_TARİHİ ise B sütununa, eğer sütun başlığı İLÇE ise C sütununa …. eğer sütun başlığı CEP_TEL ise AZ sütununa getir” gibi bir formülü nasıl yazabilirim.
Merhaba,
Öncelikle başlıkları verileri getirmek istediğini sütunların ilk satırına yazmanız gerek.Sonrasında
http://excelileharikalar.com/index.php/2018/10/17/satir-ve-sutunda-arama/
http://excelileharikalar.com/index.php/2020/02/23/ileri-arama-teknikleri-1/
yazılarımda anlattığım yöntemleri kullanabilirsiniz.
Saygılarımla,
merhabalar,
öncelikle çok teşekkürler,
bu yaptığımız uygulamada tüm listeyi de görmek istersek formüle neyi eklememiz gerekir?
Merhaba,
E-Posta ile cevap verdim sorunuza.
Erol Sinan Zorlu keşke buradan paylaşsaydınız da bize istifade etseydik.
Merhaba,
Bunun için açılır açılır listeye “Hepsi” seçeneği eklenmeli ve formülün “İNDİS” kısmı aşağıdaki şekilde değiştirilmeli:
=İNDİS((SATIR($B$2:$B$11))/(EĞER($F$3=”HEPSİ”;1;$F$3=$B$2:$B$11));;)
Ek olarak Excel 365 var ise dinamik dizi formülleri ile daha hızlı bir şekilde yapılabilir:
http://excelileharikalar.com/index.php/2020/01/29/filtre-islevi/
Merhaba, öncelikle güzel ve faydalı bilgiler paylaştığınız için teşekkür ederim. Bilgi paylaştıkça çoğalan ve değerlenen birşey. Excel 2016 kullanıyorum. Elimde ürünlerin barkod numaraları, açıklamaları, ürün ölçüleri ( en boy genişlik) ve ürün fiyatlarının olduğu bir listem var. müşteriden gelen barkod numaraları üzerinden kendi asıl dosyamdan sadece müşterinin istediği ürünleri barkod numaraları baz alınarak fiyatlarını ayrı bir tabloya aktarmak istiyorum. Bu konuda yardımcı olursanız sevinirim.
Merhaba,
Bunun için müşteriden gelen verileri alarak Düşeyara, İNDİS, KAÇINCI gibi işlevlerle formüller yazabilirsiniz. Genel olarak aşağıdaki sayfada ileri seviye aramalar ile ilgili bilgi var.
http://excelileharikalar.com/index.php/category/uygulamalar-ve-ornekler/arama-ve-basvuru-2/
Ek olarak Power Query kullanabilirsiniz:
http://excelileharikalar.com/index.php/2020/04/17/power-query-ile-tablolari-birlestirme/
Saygılarımla,
örnek: https://s2.dosya.tc/server16/k396fj/ORNEK.xlsx.html
Hocam bütün derslerinizi denedim diyebilirim fakat tecrübesizliğimden midir, yeni başladığım için algılarım mı yetmedi bilemiyorum fakat yardımınıza ihtiyacım var. Size attığım örnekte “O2” hücresine ne yazarsam “D” hücresinde bulup; “A” hücresinde bom seviyesine göre alt kısımlarını seçtirmek istiyorum fakat tıkandım. İşler karıştıkça ilerleyemedim. Bom seviyeleri başlayıp BOM0>..>BOM9 gibi ilerleyebiliyor. BOM1 (içinde BOM2 VE BOM3 içermesi lazım yani bu örnekte) çıkarsa eğer bir sonraki BOM1 e kadar satırları seçmek istiyorum fakat bir türlü yapamadım. Yardım etme imkanınız var mı acaba ? Anlayacağınız gibi BOM0 a yaklaştıkça ana parça oluyor, sayılar arttıkça alt kısımları oluşuyor.
Merhaba,
Office 365 kullanıyorsanız Filtre işlevi ile “<=BOM3" gibi bir filtreleme kıstası kullanabilirsiniz. Office 365 kullanmıyorsanız formüldeki kıstas yerine "<=BOM3" gibi bir değer girerek deneme yapmanız gerek. Bende eski versiyon Excel olmadığından deneme yapma imkanım olmadı. Saygılarımla,
bu uygulamanın video hali var mı? denedim ama formüller çalışmadı. örneğin satır numaraları artarak gitmedi hep 2 gözüküyor.
Merhaba,
Bu yazı ile ilgili olarak bir videom yok. Ancak ben formülleri test ettiğimde bir sorun yaşamadım. Yazıda anlatıldığı gibi adım adım uygulama mı yaptınız yoksa en sondaki formülü direk alıp mı kullandınız?
Saygılarımla,