Genel formül
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Özet
N'inci en büyük değerin adını almak için, BÜYÜK işlevi ile İNDİS ve KAÇINCI kullanabilirsiniz. Gösterilen örnekte, H5 hücresindeki formül şöyledir:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
burada ad (B5: B16) ve puan (D5: D16) adlandırılmış aralıklardır.
Açıklama
Özetle, bu formül bir veri kümesindeki n'inci en büyük değeri bulmak için BÜYÜK işlevini kullanır. Bu değere sahip olduğumuzda, ilişkili adı almak için onu standart bir INDEX ve MATCH formülüne koyarız. Başka bir deyişle, ilişkili bilgileri almak için n'inci en büyük değeri bir "anahtar" gibi kullanırız.
BÜYÜK işlevi, bir aralıktaki n'inci en büyük değeri elde etmenin basit bir yoludur. İlk bağımsız değişken (dizi) için bir aralık ve ikinci bağımsız değişken (k) olarak n için bir değer sağlamanız yeterlidir:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
İçten dışa doğru çalışarak, ilk adım, LARGE işleviyle verilerdeki "1." en büyük değeri elde etmektir:
LARGE(score,F5) // returns 93
Bu durumda, F5'teki değer 1'dir, bu yüzden 93 olan 1. en büyük puanı (yani en yüksek puan) istiyoruz. Şimdi formülü şu şekilde basitleştirebiliriz:
=INDEX(name,MATCH(93,score,0))
INDEX işlevinin içinde, MATCH işlevi, adlandırılmış aralık skorunda (D5: D16) 93 konumunu bulmak için ayarlanır :
MATCH(93,score,0) // returns 3
93 3. satırda göründüğünden, MATCH, dizi olarak ad ile satır numarası olarak 3'ü doğrudan INDEX'e verir:
=INDEX(name,3) // Hannah
Son olarak, INDEX işlevi 3. satırdaki "Hannah" adını döndürür.
Formül kopyalandıkça 1., 2. ve 3. en yüksek puanları elde etmek için F5: F7 aralığından n değerlerini aldığımıza dikkat edin .
Grubu al
Aynı temel formül, ilişkili herhangi bir bilgiyi almak için çalışacaktır. En büyük değerleri elde etmek için, INDEX'e sağlanan diziyi adlandırılmış aralık grubuyla değiştirebilirsiniz :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
F5'te 1 değeriyle, BÜYÜK en yüksek puanı alır ve formül "A" değerini döndürür.
Not: Excel 365 ile, üst veya alt sonuçları dinamik olarak listelemek için FİLTRE işlevini kullanabilirsiniz.
XLOOKUP ile
XLOOKUP işlevi, aşağıdaki gibi n'inci en büyük değerin adını döndürmek için de kullanılabilir:
=XLOOKUP(LARGE(score,F5),score,name)
BÜYÜK, en büyük değer olan 93'ü, arama değeri olarak doğrudan XLOOKUP'a döndürür:
=XLOOKUP(93,score,name) // Hannah
Arama dizisi olarak adlandırılmış aralık puanı (D5: D16) ve dönüş dizisi olarak ad (B5: B16) ile, XLOOKUP daha önce olduğu gibi "Hannah" değerini döndürür.
Bağları kullanma
Sayısal verilerdeki yinelenen değerler bir "bağ" oluşturacaktır. Sıralanan değerlerde bir bağ oluşursa, örneğin birinci ve ikinci en büyük değerler aynıysa, BÜYÜK her biri için aynı değeri döndürür. Bu değer MATCH işlevine aktarıldığında, MATCH ilk eşleşmenin konumunu döndürür, böylece aynı (ilk) adın döndürüldüğünü görürsünüz.
İlişki olasılığı varsa, bir tür bağ bozma stratejisi uygulamak isteyebilirsiniz. Bir yaklaşım, bağları koparmak için ayarlanmış yeni bir yardımcı değerler sütunu oluşturmaktır. Ardından bilgileri sıralamak ve almak için yardımcı sütun değerlerini kullanın. Bu, bağları koparmak için kullanılan mantığı net ve açık hale getirir.
Diğer bir yaklaşım ise sadece pozisyona göre beraberliği bozmaktır (yani ilk beraberlik "kazanır"). İşte bu yaklaşımı benimseyen bir formül:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Not: Bu bir dizi formülüdür ve Excel 365 dışında control + shift + enter ile girilmelidir.
Burada, 1 sayısını bulmak için MATCH kullanıyoruz ve (1) tüm puanları LARGE tarafından döndürülen değerle karşılaştıran boole mantığını kullanarak bir arama dizisi oluşturuyoruz:
score=LARGE(score,F5)
ve (2) ad zaten sıralanmış listede olup olmadığını genişleyen bir aralık kontrolü kullanır:
COUNTIF(H$4:H4,name)=0
Zaten listede bir ad varsa, mantık tarafından "iptal edilir" ve sonraki (yinelenen) değer eşleştirilir. Dairesel bir referanstan kaçınmak için genişleyen aralığın önceki satırda başladığına dikkat edin.
Bu yaklaşım bu örnekte işe yarar çünkü ad sütununda yinelenen adlar yoktur. Bununla birlikte, sıralanmış değerlerde yinelenen adlar ortaya çıkarsa, yaklaşımın ayarlanması gerekir. En kolay çözüm, adların benzersiz olduğundan emin olmaktı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.
