Genel formül
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Özet
Ölçütle birlikte n'inci en büyük değerin adını almak için, DİZİN ve KAÇINCI, BÜYÜK işlevi ve EĞER işlevi ile oluşturulmuş bir filtre kullanabilirsiniz. Gösterilen örnekte, kopyalanan G5 hücresindeki formül şöyledir:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
burada ad (B5: B16), grup (C5: C16) ve puan (D5: D16) adlandırılmış aralıklardır. Formül, A Grubundaki 1., 2. ve 3. en yüksek değerlerle ilişkili adı döndürür.
Not: Bu, Excel 365 dışında control + shift + enter ile girilmesi gereken bir dizi formülüdür.
Açıklama
BÜYÜK işlevi, bir aralıktaki n'inci en büyük değeri elde etmenin kolay bir yoludur:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Bu örnekte, en yüksek puanı almak için LARGE işlevini kullanabiliriz, ardından puanı INDEX ve MATCH ile ilişkili adı almak için bir "anahtar" gibi kullanabiliriz. 1., 2. ve 3. en yüksek puanları almak için F5: F7 aralığından n değerlerini aldığımıza dikkat edin .
Bununla birlikte, bu durumda bükülme, grup A ve grup B'deki puanları ayırt etmemiz gerektiğidir. Diğer bir deyişle, kriterleri uygulamamız gerekir. Bunu, LARGE ile değerlendirilmeden önce değerleri "filtrelemek" için kullanılan IF işlevi ile yapıyoruz. Genel bir örnek olarak, aralık 1 = "A" olan aralık2'deki en büyük değeri (yani 1. değer) elde etmek için, aşağıdaki gibi bir formül kullanabilirsiniz:
LARGE(IF(range="A",range2),1)
Not: IF'yi bu şekilde kullanmak, bunu bir dizi formülü yapar.
İçten dışa doğru çalışarak, ilk adım, BÜYÜK işlevi ile Grup A ile ilişkili verilerde "1." en büyük değeri elde etmektir:
LARGE(IF(group="A",score),F5)
Bu durumda, F5'teki değer 1'dir, bu nedenle Grup A'daki en yüksek puanı istiyoruz. IF işlevi değerlendirildiğinde, adlandırılmış aralık grubundaki her bir değeri test eder . Değer_if_true için adlandırılmış aralık puanı sağlanır. Bu, doğrudan LARGE işlevine döndürülen yeni bir dizi oluşturur:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Filtreden sağ kalan puanların A Grubundan olduğuna dikkat edin. LARGE daha sonra kalan en yüksek skor olan 93'ü arama değeri olarak doğrudan MATCH işlevine döndürür. Şimdi formülü şu şekilde basitleştirebiliriz:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Şimdi MATCH fonksiyonunun yukarıda gördüğümüz aynı filtrelenmiş diziyi kullanarak yapılandırıldığını görebiliriz. EĞER işlevi, istenmeyen değerleri yeniden filtreler ve formülün KAÇINCI bölümü şu şekilde çözümlenir:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
93 3. pozisyonda göründüğünden, MATCH 3'ü doğrudan INDEX işlevine döndürür:
=INDEX(name,3) // Hannah
Son olarak, INDEX işlevi 3. satırdaki "Hannah" adını döndürür.
XLOOKUP ile
XLOOKUP işlevi, yukarıda açıklanan aynı yaklaşım kullanılarak bu sorunu çözmek için de kullanılabilir:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Yukarıdaki gibi, LARGE, IF tarafından filtrelenen bir diziyle çalışacak şekilde yapılandırılmıştır ve arama değeri olarak XLOOKUP'a 93 sonucunu döndürür:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Arama dizisi, Grup A'dan alınan puanlar üzerinde bir filtre olarak IF kullanılarak da oluşturulur. Dönüş dizisi ad olarak sağlanır (B5: B16). XLOOKUP, nihai sonuç olarak "Hannah" değerini döndürür.
Notlar
- Kriterlerle birlikte n'inci değerin adını elde etmek için (yani sonuçları A veya B grubuyla sınırlandırmak), ek mantık kullanmak için formülü genişletmeniz gerekir.
- Excel 365'te FİLTRE işlevi, en üstteki veya en alttaki sonuçları dinamik olarak listelemenin daha iyi bir yoludur. Bu yaklaşım, bağları otomatik olarak halledecektir.
