Bu makale, bir tablodan eşleşen tüm değerlerin nasıl alınacağı ve farklı hücrelerde nasıl alınacağı hakkında konuşacaktır. Bu, birden çok değer aramaya benzer.
Genel Formül
{=INDEX(adlar,KÜÇÜK(EĞER(gruplar=grup_adı,SATIR(adlar)-MIN(SATIR(adlar))+1),SÜTUN(genişleyen aralıklar))), “--Liste Bitiyor--”)}
Çok fazla fonksiyon ve değişken!!!. Bakalım bu değişkenler nelermiş.
İsimler: Bu isim listesi.
Gruplar: Bu isimlerin de ait olduğu grup listesi.
Grup ismi: grup adının referansı.
Genişleyen aralıklar: bu, sağa kopyalandığında artan sayı elde etmek için kullanılan genişleyen bir aralıktır.
Örnek: Çalışanların adlarını şirketlerine göre farklı sütunlara çıkarın.
Diyelim ki şirketlerine göre gruplandırılmış bir çalışan tablonuz var. İlk sütun çalışan adlarını, ikinci sütun ise şirketin adını içerir.
Şimdi her çalışanın adını şirketlerine göre farklı sütunlarda almamız gerekiyor. Başka bir deyişle, onları gruplandırmamız gerekiyor.
Burada A2:A10'u Çalışan ve B2:B10'u Şirket olarak adlandırdım, böylece bu formülün okunması kolay.
Bu dizi formülünü F2'ye yazın. Bu formülü girmek için CTRL+SHIFT+ENTER tuşlarını kullanın.
{=INDEX(Çalışan,KÜÇÜK(EĞER(Şirket=$E2,SATIR(Çalışan)-MIN(SATIR(Çalışan))+1),SÜTUN($E$1:E1))), “--Liste Bitiyor--” )}
Bu formülü tüm hücrelere kopyalayın. Gruplarına göre farklı sütunlardaki her bir adı çıkaracaktır.
Yukarıdaki resimde de görebileceğiniz gibi, her çalışan farklı hücrelere ayrılmıştır.
Peki, bu formül nasıl çalışır?
Formülü anlamak için G2'deki formüle bakalım
Hangisi =EĞERHATA(INDEX(Çalışan,KÜÇÜK(EĞER(Şirket=$E3,SIRA(Çalışan)-MIN(SATIR(Çalışan))+1),SÜTUNLAR($E$1:F2)))"--Liste Bitiyor--")
Mekaniği basittir ve çoklu DÜŞEYARA formülüyle hemen hemen aynıdır. İşin püf noktası, farklı gruplardan her çalışanın indeks numarasını alıp INDEX formülüne geçirmektir. Bu, formülün bu kısmı tarafından yapılır.
EĞER(Şirket=$E3,SIRA(Çalışan)-MIN(SATIR(Çalışan))+1):
Bu bölüm, $E3'te "Rankwatch" içeren şirket adı için bir dizi dizin ve false döndürür.
{YANLIŞ;2;YANLIŞ;4;YANLIŞ;YANLIŞ;7;YANLIŞ;9}.
Nasıl? Onu içeriden yıkalım.
Burada, $E3'teki şirket adını, içindeki her bir değerle eşleştiriyoruz. Şirket aralığı (Şirket=$E3).
Bu, true ve false dizisini döndürür.{FALSE;NS;YANLIŞ;NS;YANLIŞ;YANLIŞ;NS;YANLIŞ;NS}.
Şimdi EĞER işlevi, SATIR(Çalışan)-DAK(SATIR() olan DOĞRU için DOĞRU deyimlerini çalıştırır.Çalışan))+1. Bu kısım, bu kısmı döndürür, 1'den sayıya kadar bir dizi dizin döndürür. çalışanlar {1;2;3;4;5;6;7;8;9}. if işlevi yalnızca TRUE için değerleri gösterir ve bu da bize {FALSE;2;FALSE;4;FALSE;FALSE;7;FALSE;9} değerini verir.
Mevcut formül basitleştirilmiştir
=EĞERHATA(INDEX(Çalışan,KÜÇÜK( {YANLIŞ;2;YANLIŞ;4;YANLIŞ;YANLIŞ;7;YANLIŞ;9},SÜTUNLAR($E$1:F2))),"--Liste Bitiyor--"). Bildiğimiz gibi küçük fonksiyon bir diziden en küçük n. değeri döndürür. SÜTUNLAR($E$1:F2) bu, 2 değerini döndürür. SMALL işlevi, yukarıdaki diziden ikinci en küçük değeri, yani 4'ü döndürür.
Şimdi formül basitleştirildi =EĞERHATA(İNDEKS(Çalışan,4),"--Liste Bitiyor--"). Şimdi, INDEX işlevi, yalnızca dördüncü adı döndürür. çalışan bize veren dizi "Sam”.
Evet, bu, INDEX, SMALL, ROW, COLUMNS ve IF işlevini kullanarak farklı sütunlardaki gruplardan adların nasıl çıkarılacağıdır. Bu işlevle ilgili herhangi bir şüpheniz varsa veya sizin için çalışmıyorsa, aşağıdaki yorumlar bölümünü bana bildirin.
Dosyayı indir:
İlgili Makaleler:
DÜŞEYARA Birden Çok Değer
Değeri Aramak için INDEX ve MATCH kullanın
Birden Çok Kriterli Arama Değeri
Popüler Makaleler:
Excel'de DÜŞEYARA İşlevi
Excel 2016'da EĞERSAY
Excel'de SUMIF İşlevi Nasıl Kullanılır