Największe i najmniejsze wartości za pomocą LARGE i SMALL

MAX zwraca pojedynczą największą wartość, a MIN — pojedynczą najmniejszą. Co jednak zrobić, jeśli potrzebują Państwo 2. najwyższej albo 3. najniższej wartości?

Do tego właśnie służą funkcje LARGE i SMALL. Pozwalają pobrać n-tą największą lub n-tą najmniejszą wartość z zakresu, co stanowi podstawę raportów Top-N i Bottom-N.

LARGE(range, k) zwraca k-tą największą wartość. Drugi argument, k, określa pozycję: 1 oznacza największą wartość, 2 — drugą co do wielkości itd.

Zatem LARGE(A2:A20,1) jest równe MAX(A2:A20), natomiast LARGE(A2:A20,2) zwraca drugą co do wielkości wartość.

SMALL(range, k) działa analogicznie do LARGE, ale od dołu. SMALL(A2:A20,1) jest równe MIN(A2:A20), a SMALL(A2:A20,3) zwraca trzecią najmniejszą wartość.

Funkcji SMALL można użyć do wskazania najsłabszych wyników, najtańszych opcji lub najwcześniejszych dat w zbiorze danych.

Aby wyświetlić trzy najwyższe wartości, należy umieścić obok siebie trzy formuły LARGE z wartościami k = 1, 2 i 3. Czytelniejszym rozwiązaniem jest odwołanie do pomocniczej liczby, dzięki czemu formułę można skopiować w dół.

Jeśli komórka D2 zawiera 1, D3 zawiera 2, a D4 zawiera 3, ta jedna formuła skopiowana w dół wygeneruje pełną listę trzech najwyższych wartości.

Można automatycznie generować k za pomocą funkcji ROW, więc nie trzeba dodawać kolumny pomocniczej. ROW()-1 zwraca 1 w pierwszym wierszu, 2 w następnym i tak dalej.

Po skopiowaniu w dół formuła tworzy listę uporządkowaną według rangi bez dodatkowej konfiguracji. Należy dostosować przesunięcie do miejsca, w którym zaczyna się lista.

W nowoczesnych wersjach Excel i Google Sheets można zwrócić całą listę Top 5 za pomocą jednej formuły, przekazując SEQUENCE jako argument k. SEQUENCE(5) generuje wartości 1, 2, 3, 4, 5.

LARGE zwraca następnie pięć wartości naraz, automatycznie rozlewając je w dół. Jedna formuła tworzy pełną listę uporządkowaną według rangi.

Częste pytanie biznesowe brzmi: jaki wkład mają moi trzej najlepsi klienci? Należy połączyć LARGE z SUM oraz SEQUENCE (lub stałą tablicową), aby zsumować największe wartości.

Poniższa formuła dodaje trzy największe wartości z zakresu, podając łączny wkład trzech najlepszych wartości w jednej komórce.

Jeśli k jest mniejsze niż 1 albo większe niż liczba wartości liczbowych w zakresie, LARGE i SMALL zwracają błąd #NUM!.

Na przykład żądanie 30. największej wartości na liście zawierającej tylko 19 liczb zakończy się niepowodzeniem. W raportach należy zabezpieczyć się przed tym, opakowując wywołanie w IFERROR.

Sama wartość jest mniej użyteczna niż informacja, kto ją uzyskał. Należy połączyć LARGE z INDEX i MATCH, aby pobrać nazwę powiązaną z największą wartością.

Formuła znajduje największą wartość sprzedaży, ustala jej wiersz i zwraca pasującą nazwę z kolumny A, dzięki czemu raport zawiera informację „Najlepszy sprzedawca: Maria”.

Podobnie jak inne funkcje statystyczne, LARGE i SMALL uwzględniają tylko liczby. Tekst i puste komórki w zakresie są pomijane, więc nie wpływają na to, która wartość jest n. największa.

Oznacza to, że można bezpiecznie wskazać kolumnę zawierającą wiersz nagłówka. Należy jednak uważać na liczby zapisane przypadkowo jako tekst, ponieważ są ignorowane i mogą zmienić wartość zwracaną przez LARGE lub SMALL.

W zakresie B2:B20 znajdują się miesięczne wartości sprzedaży. Kierownictwo chce poznać trzy najlepsze i trzy najgorsze miesiące.

• Trzy najlepsze: =LARGE($B$2:$B$20,{1,2,3})
• Trzy najgorsze: =SMALL($B$2:$B$20,{1,2,3})

Umieść każdą formułę w komórce, z której może się rozlać, a natychmiast uzyskasz zestawienie najlepszych i najgorszych wyników zasilające pulpit nawigacyjny.

Można teraz wyjść poza MAX i MIN:

• LARGE(range,k) zwraca k-tą największą wartość, a SMALL(range,k) — k-tą najmniejszą.
• k = 1 odpowiada MAX / MIN; zwiększenie k pozwala uzyskać kolejne wartości w rankingu.
• Przekazanie SEQUENCE lub tablicy takiej jak {1,2,3} pozwala utworzyć albo zsumować listę Top N za pomocą jednej formuły.
• Połącz funkcje z INDEX-MATCH, aby dodać etykietę do wartości, i owiń je w IFERROR, aby obsłużyć k spoza zakresu.