Наибольшие и наименьшие значения с LARGE и SMALL

MAX возвращает единственное наибольшее значение, а MIN — единственное наименьшее. Но что делать, если Вам нужно 2-е по величине или 3-е по малости значение?

Именно это делают LARGE и SMALL. Они позволяют извлечь из диапазона n-е по величине или n-е по малости значение, что служит основой для отчётов по первым и последним N результатам.

LARGE(range, k) возвращает k-е по величине значение. Второй аргумент k задаёт позицию: 1 — наибольшее значение, 2 — второе по величине и так далее.

Таким образом, LARGE(A2:A20,1) равно MAX(A2:A20), а LARGE(A2:A20,2) возвращает результат, занявший второе место.

SMALL(range, k) работает аналогично LARGE, если считать с конца. SMALL(A2:A20,1) равно MIN(A2:A20), а SMALL(A2:A20,3) возвращает третье по малости значение.

Используйте SMALL, чтобы выявить худшие результаты, самые дешёвые варианты или самые ранние даты в наборе данных.

Чтобы составить список трёх наибольших значений, последовательно используйте три формулы LARGE с k = 1, 2, 3. Более удобный способ — ссылаться на вспомогательное число, чтобы можно было заполнить формулу вниз.

Если в ячейке D2 находится 1, в D3 — 2, а в D4 — 3, эта одна формула, скопированная вниз, создаст полный список трёх лучших результатов.

С помощью функции ROW можно автоматически вычислять k, поэтому вспомогательный столбец не понадобится. ROW()-1 возвращает 1 в первой строке, 2 в следующей и так далее.

Если протянуть формулу вниз, получится упорядоченный список без дополнительной настройки. Измените смещение в соответствии с началом списка.

В современных версиях Excel и Google Sheets можно вывести весь топ-5 одной формулой, передав SEQUENCE в качестве аргумента k. SEQUENCE(5) создаёт последовательность 1, 2, 3, 4, 5.

LARGE затем сразу возвращает пять значений, автоматически распространяя их вниз. Одна формула — полный упорядоченный список.

Распространённый вопрос в бизнесе: какой вклад вносят мои 3 крупнейших клиента? Оберните LARGE в SUM и передайте SEQUENCE (или константу массива), чтобы сложить первые значения.

Приведённая ниже формула складывает три наибольших значения в диапазоне и возвращает их общий вклад в одной ячейке.

Если k меньше 1 или больше количества чисел в диапазоне, LARGE и SMALL возвращают #NUM! ошибку.

Например, запрос 30-го по величине значения в списке всего из 19 чисел завершится ошибкой. В отчётах предусмотрите такую ситуацию, обернув вызов в IFERROR.

Одно значение менее полезно, чем информация о том, кто его получил. Объедините LARGE с INDEX и MATCH, чтобы получить имя, связанное с наибольшим значением.

Эта формула находит наибольшую сумму продаж, определяет её строку и возвращает соответствующее имя из столбца A, поэтому в отчёте отображается «Лучший продавец: Maria».

Как и другие статистические функции, LARGE и SMALL учитывают только числа. Текстовые и пустые ячейки в диапазоне пропускаются, поэтому они не влияют на то, какое значение является n-м по величине.

Это означает, что можно безопасно указать столбец, включающий строку заголовков. Однако следите за числами, случайно сохранёнными как текст: они игнорируются и могут изменить значение, которое возвращает LARGE или SMALL.

У Вас есть ежемесячные данные о продажах в диапазоне B2:B20. Руководству нужны три лучших и три худших месяца.

• Три лучших: =LARGE($B$2:$B$20,{1,2,3})
• Три худших: =SMALL($B$2:$B$20,{1,2,3})

Поместите каждую формулу в ячейку, из которой результат может распространяться, и сразу получите сводную картину лучших и худших показателей для панели мониторинга.

Проверьте, насколько хорошо Вы поняли LARGE и SMALL.

Теперь Вы можете выйти за пределы MAX и MIN:

• LARGE(range,k) возвращает k-е по величине значение, а SMALL(range,k) — k-е по малости.
• k = 1 соответствует MAX / MIN; увеличивайте k, чтобы получить следующие значения в рейтинге.
• Передайте SEQUENCE или массив вроде {1,2,3}, чтобы построить или суммировать список первых N значений одной формулой.
• Объединяйте эти функции с INDEX-MATCH, чтобы добавить к значению подпись, и оборачивайте их в IFERROR для обработки k, выходящего за границы.