Как использовать функции SMALL и LARGE в Excel

Быстрые ссылки

• Use the SMALL Function

• Use the LARGE Function

• A Limitation to Note

Краткое описание

Функция SMALL возвращает n‑й наименьший элемент в заданном диапазоне. Функция LARGE возвращает n‑й наибольший элемент. Обе принимают два аргумента: диапазон и позицию (1 — первый, 2 — второй и т.д.). Эти функции полезны, когда нужно быстро найти минимумы/максимумы и их порядковые значения в больших таблицах.

Если вы ищете не только самый маленький или самый большой номер, но и второй, третий или пятый по порядку — используйте SMALL или LARGE с соответствующим аргументом позиции.

Используйте функцию SMALL

Синтаксис:

SMALL(range, position)

Оба аргумента обязательны. В аргументе range укажите диапазон или массив значений. В аргументе position введите 1, чтобы получить первый (наименьший) элемент, 2 — второй, 3 — третий и т.д.

Пример: чтобы найти наименьшее число в диапазоне B2:E13, используйте:

=SMALL(B2:E13,1)

Чтобы найти второй наименьший элемент:

=SMALL(B2:E13,2)

Используйте функцию LARGE

Функция LARGE работает аналогично SMALL, но возвращает n‑й по величине (самый большой) элемент.

Синтаксис:

LARGE(range, position)

Пример — найти максимальное значение в диапазоне B2:E13:

=LARGE(B2:E13,1)

Чтобы найти третий по величине элемент:

=LARGE(B2:E13,3)

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

Ограничение: повторяющиеся значения

Если в данных есть одинаковые значения, функции вернут их отдельно для разных позиций. Например, если 1 800 встречается дважды, то и первый, и второй по величине результат будут 1 800.

Рассмотрите способы обработки дубликатов, если вам нужны n‑е отличные друг от друга значения. Ниже — подходы для разных версий Excel.

Как получить n‑й уникальный минимум/максимум

Опции в зависимости от версии Excel:

• Excel 365 / Excel 2021 и новее:
  • Используйте комбинацию UNIQUE и SORT.
  • Пример n‑го по величине уникального элемента (n‑й максимальный):

=INDEX(UNIQUE(SORT(B2:B13,1,-1)), n)

• Пример n‑го уникального по возрастанию (n‑й минимальный):

=INDEX(UNIQUE(SORT(B2:B13,1,1)), n)

• Старые версии Excel (без UNIQUE/SORT):
  • Создайте вспомогательный столбец с рангом или порядком и затем используйте INDEX/MATCH.
  • Или используйте массивные формулы с IF и COUNTIF (требуют Ctrl+Shift+Enter в старых версиях).

Простой приём через вспомогательный столбец:

1. В столбце рядом (например, C2) вычислите ранг с учётом повторов: =RANK.EQ(B2,$B$2:$B$13)+COUNTIF($B$2:B2,B2)-1
2. Затем найдите n‑й по рангу через INDEX+MATCH.

Альтернативные подходы

• SORT / FILTER (Excel 365): быстро сортируют и фильтруют диапазон; затем берёте n‑й элемент через INDEX.
• AGGREGATE: полезна для игнорирования ошибок и скрытых строк, не напрямую для «n‑го», но в сочетании с SMALL/LARGE или формулами может помочь.
• INDEX + MATCH: удобно, когда нужен элемент, связанный с позицией другого поля (например, имя товара с n‑й продажей).

Шпаргалка по формулам (быстрое руководство)

• Первый наименьший: =SMALL(диапазон,1)
• Второй наименьший: =SMALL(диапазон,2)
• Первый наибольший: =LARGE(диапазон,1)
• Третий наибольший: =LARGE(диапазон,3)
• n‑й уникальный максимум (365): =INDEX(UNIQUE(SORT(диапазон,1,-1)), n)
• n‑й уникальный минимум (365): =INDEX(UNIQUE(SORT(диапазон,1,1)), n)

Краткая методология: как подойти к задаче

1. Определите, нужны ли уникальные значения или допускаются повторы.
2. Проверьте версию Excel (есть ли UNIQUE/SORT/SEQUENCE).
3. Выберите простой путь: SMALL/LARGE → если подходят, используйте их.
4. Если нужны уникальные значения, применяйте UNIQUE/SORT (365) или вспомогательный столбец (старые версии).
5. Протестируйте на примере с дубликатами и пустыми ячейками.

Роль‑ориентированные чек‑листы

Аналитику:

• Проверьте типы данных (числа/текст).
• Удалите или пометьте пустые/ошибочные строки.
• Используйте UNIQUE, если нужны отличающиеся значения.

Менеджеру отчетности:

• Убедитесь, что формула динамически ссылается на диапазон таблицы (Excel Table).
• Добавьте пояснение в ячейке или комментарий о поведении при дубликатах.

Студенту/ученику:

• Попробуйте SMALL и LARGE на простом наборе чисел.
• Сравните результаты при наличии повторов.

Тесты и критерии приёмки

Критерии приёмки:

• Для набора без повторов функция SMALL(…,1) возвращает минимум.
• Для набора без повторов функция LARGE(…,1) возвращает максимум.
• При наличии повторов поведение соответствует ожиданиям: дубликаты занимают несколько позиций.
• Для n‑го уникального элемента проверка (если применимо) возвращает разные значения для разных n.

Тестовые случаи:

• Набор [1,2,3,4,5] — SMALL(…,1)=1, LARGE(…,1)=5
• Набор [5,5,3,2] — LARGE(…,1)=5, LARGE(…,2)=5 (повторы)
• Набор с пустыми ячейками — формулы игнорируют пустые (если это числа), проверяйте типы данных

Когда SMALL или LARGE не подходят

• Нужны только уникальные значения — стандартные SMALL/LARGE вернут дубликаты.
• Требуется учитывать дополнительные условия (например, только продажи за конкретный месяц) — используйте комбинацию IF/FILTER + SMALL/LARGE.
• Данные хранятся нечисловые (текстовые представления чисел) — предварительно приведите к числу.

Быстрые примеры фильтрации по условию (Excel 365)

Найти 1‑й по величине элемент только для группы “Север“:

=LARGE(FILTER(B2:B100, A2:A100="Север"),1)

Найти 2‑й уникальный максимум среди отфильтрованных значений:

=INDEX(UNIQUE(SORT(FILTER(B2:B100, A2:A100="Север"),1,-1)),2)

Визуальная подсказка: когда использовать какую функцию

flowchart TD
  A[Нужен n-й по порядку элемент?] --> B{Нужны уникальные значения?}
  B -- Да --> C{Excel 365/2021+?}
  B -- Нет --> D[Использовать SMALL/LARGE]
  C -- Да --> E[Использовать UNIQUE+SORT + INDEX]
  C -- Нет --> F[Использовать вспомогательный столбец / RANK]
  E --> G[Готово]
  F --> G
  D --> G

Итог

Функции SMALL и LARGE — простые и быстрые инструменты для поиска n‑го по порядку значения в диапазоне. Они отлично подходят, когда повторы допустимы. Если вам нужны уникальные значения или фильтрация по условиям, используйте возможности SORT/UNIQUE/FILTER (в современных версиях) или вспомогательные столбцы в старых версиях Excel.

Коротко:

• SMALL — n‑й наименьший; LARGE — n‑й наибольший.
• Повторы считаются отдельными позициями.
• Для уникальных n‑х используйте UNIQUE+SORT или вспомогательные столбцы.

Если нужно, могу подготовить готовый шаблон Excel с примерами формул для вашего файла или помочь адаптировать формулы под конкретный набор данных.