Formuła ogólna
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Podsumowanie
Aby uzyskać nazwę n-tej największej wartości z kryteriami, można użyć funkcji INDEKS i PODAJ.POZYCJĘ, funkcji LARGE oraz filtru utworzonego za pomocą funkcji JEŻELI. W przedstawionym przykładzie formuła w komórce G5 skopiowana w dół to:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
gdzie nazwa (B5: B16), grupa (C5: C16) i wynik (D5: D16) to nazwane zakresy. Formuła zwraca nazwę skojarzoną z pierwszą, drugą i trzecią najwyższą wartością w grupie A.
Uwagi: To formuła tablicowa, którą należy wprowadzić za pomocą Ctrl + Shift + Enter, z wyjątkiem Excel 365.
Wyjaśnienie
Funkcja LARGE to łatwy sposób na uzyskanie n-tej największej wartości w zakresie:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
W tym przykładzie możemy użyć funkcji LARGE, aby uzyskać najwyższy wynik, a następnie użyć wyniku jako „klucza”, aby pobrać skojarzoną nazwę z INDEKSEM i PODAJNIKIEM. Zauważ, że pobieramy wartości n z zakresu F5: F7, aby uzyskać 1., 2. i 3. najwyższy wynik.
Jednak w tym przypadku różnica polega na tym, że musimy rozróżnić wyniki w grupie A i grupie B. Innymi słowy, musimy zastosować kryteria. Robimy to za pomocą funkcji JEŻELI, która służy do „filtrowania” wartości, zanim zostaną ocenione przez LARGE. Jako ogólny przykład, aby uzyskać największą wartość (tj. Pierwszą wartość) w zakresie 2, gdzie zakres 1 = „A”, można użyć następującego wzoru:
LARGE(IF(range="A",range2),1)
Uwaga: użycie funkcji JEŻELI w ten sposób powoduje, że jest to formuła tablicowa.
Pracując od wewnątrz, pierwszym krokiem jest uzyskanie „pierwszej” największej wartości w danych powiązanych z grupą A za pomocą funkcji LARGE:
LARGE(IF(group="A",score),F5)
W tym przypadku wartością w F5 jest 1, więc prosimy o najwyższy wynik w grupie A. Kiedy obliczana jest funkcja JEŻELI, testuje ona każdą wartość w podanej grupie zakresu . Nazwany wynik zakresu jest podany dla value_if_true. To generuje nową tablicę, która jest zwracana bezpośrednio do funkcji LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Zwróć uwagę, że jedyne wyniki, które przetrwały filtr, pochodzą z grupy A. LARGE, a następnie zwraca najwyższy pozostały wynik, 93, bezpośrednio do funkcji PODAJ.POZYCJĘ jako wartość wyszukiwania. Możemy teraz uprościć formułę, aby:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Teraz widzimy, że funkcja MATCH jest skonfigurowana przy użyciu tej samej przefiltrowanej tablicy, którą widzieliśmy powyżej. Funkcja JEŻELI ponownie odfiltrowuje niechciane wartości, a część PODAJ.POZYCJĘ formuły powoduje:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Ponieważ 93 znajduje się na trzeciej pozycji, PODAJ.POZYCJĘ zwraca 3 bezpośrednio do funkcji INDEKS:
=INDEX(name,3) // Hannah
Na koniec funkcja INDEKS zwraca nazwę w trzecim wierszu „Hannah”.
Dzięki XLOOKUP
Funkcji XLOOKUP można również użyć do rozwiązania tego problemu, stosując to samo podejście wyjaśnione powyżej:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Jak wyżej, LARGE jest skonfigurowany do pracy z tablicą przefiltrowaną przez IF i zwraca wynik 93 do XLOOKUP jako wartość wyszukiwania:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Tablica przeglądowa jest również tworzona przy użyciu funkcji JEŻELI jako filtru wyników z grupy A. Z tablicą wyników podaną jako nazwa (B5: B16). XLOOKUP zwraca „Hannah” jako wynik końcowy.
Uwagi
- Aby uzyskać nazwę n-tej wartości z kryteriami (tj. Ograniczyć wyniki do grupy A lub B), będziesz musiał rozszerzyć formułę o dodatkową logikę.
- W programie Excel 365 funkcja FILTR to lepszy sposób na dynamiczne wyświetlanie najwyższych lub najniższych wyników. Takie podejście automatycznie obsłuży remisy.
