Formuła ogólna
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Podsumowanie
Aby uzyskać nazwę n-tej największej wartości, możesz użyć INDEKSU i PODAJ.POZYCJĘ z funkcją LARGE. W przedstawionym przykładzie formuła w komórce H5 to:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
gdzie nazwa (B5: B16) i wynik (D5: D16) to nazwane zakresy.
Wyjaśnienie
W skrócie, ta formuła wykorzystuje funkcję LARGE do znalezienia n-tej największej wartości w zbiorze danych. Gdy już mamy tę wartość, podłączamy ją do standardowej formuły INDEKS i PODAJ.POZYCJĘ, aby pobrać skojarzoną nazwę. Innymi słowy, używamy n-tej największej wartości jako „klucza” do pobierania powiązanych informacji.
Funkcja LARGE to prosty sposób na uzyskanie n-tej największej wartości w zakresie. Po prostu podaj zakres dla pierwszego argumentu (tablica) i wartość n jako drugi argument (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Pracując od wewnątrz, pierwszym krokiem jest uzyskanie „pierwszej” największej wartości w danych za pomocą funkcji LARGE:
LARGE(score,F5) // returns 93
W tym przypadku wartość w F5 to 1, więc prosimy o 1. największy wynik (tj. Najwyższy wynik), czyli 93. Możemy teraz uprościć wzór do:
=INDEX(name,MATCH(93,score,0))
Wewnątrz funkcji INDEKS funkcja PODAJ.POZYCJĘ jest ustawiona tak, aby zlokalizować pozycję 93 w nazwanym wyniku zakresu (D5: D16):
MATCH(93,score,0) // returns 3
Ponieważ 93 pojawia się w trzecim wierszu, PODAJ.POZYCJĘ zwraca 3 bezpośrednio do INDEKSU jako numer wiersza, z nazwą jako tablicą:
=INDEX(name,3) // Hannah
Na koniec funkcja INDEKS zwraca nazwę w trzecim wierszu „Hannah”.
Zauważ, że pobieramy wartości n z zakresu F5: F7, aby uzyskać pierwszy, drugi i trzeci najwyższy wynik w miarę kopiowania formuły.
Pobierz grupę
Ta sama podstawowa formuła będzie działać przy pobieraniu wszelkich powiązanych informacji. Aby uzyskać grupę dla największych wartości, możesz po prostu zmienić dostarczoną tablicę na INDEKS z nazwaną grupą zakresu :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Przy wartości 1 w F5 LARGE uzyska najwyższy wynik, a formuła zwróci „A”.
Uwaga: w programie Excel 365 można użyć funkcji FILTER, aby dynamicznie wyświetlać górne lub dolne wyniki.
Dzięki XLOOKUP
Funkcji XLOOKUP można również użyć do zwrócenia nazwy n-tej największej wartości w następujący sposób:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE zwraca największą wartość, 93, bezpośrednio do XLOOKUP jako wartość wyszukiwania:
=XLOOKUP(93,score,name) // Hannah
Z nazwanym wynikiem zakresu (D5: D16) jako tablicą przeglądową i nazwą (B5: B16) jako tablicą zwracaną, XLOOKUP zwraca „Hannah” jak poprzednio.
Obsługa krawatów
Zduplikowane wartości w danych liczbowych spowodują „remis”. Jeśli w ocenianych wartościach wystąpi remis, na przykład jeśli pierwsza i druga co do wielkości wartość są takie same, LARGE zwróci tę samą wartość dla każdej z nich. Gdy ta wartość zostanie przekazana do funkcji PODAJ.POZYCJĘ, PODAJ.POZYCJĘ zwróci pozycję pierwszego dopasowania, więc zobaczysz zwróconą tę samą (pierwszą) nazwę.
Jeśli istnieje możliwość remisu, możesz zechcieć zastosować jakąś strategię rozstrzygania remisów. Jednym ze sposobów jest utworzenie nowej kolumny pomocniczej zawierającej wartości, które zostały dostosowane w celu zrywania więzi. Następnie użyj wartości kolumny pomocnika, aby ocenić i pobrać informacje. To sprawia, że logika używana do zrywania więzi jest jasna i wyraźna.
Innym podejściem jest rozwiązywanie remisów wyłącznie na podstawie pozycji (tj. Pierwszy remis „wygrywa”). Oto formuła, która przyjmuje takie podejście:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter, z wyjątkiem Excel 365.
Tutaj używamy funkcji MATCH, aby znaleźć liczbę 1, i tworzymy tablicę wyszukiwania przy użyciu logiki boolowskiej, która (1) porównuje wszystkie wyniki z wartością zwracaną przez LARGE:
score=LARGE(score,F5)
i (2) używa rozszerzonego zakresu sprawdzania, czy nazwa jest już na liście rankingowej:
COUNTIF(H$4:H4,name)=0
Gdy nazwa znajduje się już na liście, jest ona „anulowana” przez logikę i dopasowywana jest następna (zduplikowana) wartość. Zwróć uwagę, że rozszerzający się zakres zaczyna się w poprzednim wierszu, aby uniknąć cyklicznego odniesienia.
Ta metoda działa w tym przykładzie, ponieważ w kolumnie nazwy nie ma zduplikowanych nazw. Jeśli jednak zduplikowane nazwy występują w wartościach rankingowych, należy dostosować podejście. Najłatwiejszym rozwiązaniem jest upewnienie się, że nazwy są niepowtarzalne.
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.
