Функция SMALL в Google Sheets — nth‑минимум и практические приёмы

TL;DR

Функция SMALL возвращает n‑й наименьший элемент в диапазоне. Используйте SMALL, чтобы получить не только минимальное значение, но и 2‑й, 3‑й и т.д. по порядку; её удобно сочетать с ROW для сортировки и с IF/ARRAYFORMULA для отфильтрованных поисков. В статье — синтаксис, примеры, отладка и практические советы.

Важно: SMALL работает только с числовыми значениями. Ненумерические ячейки игнорируются; если в диапазоне нет чисел или n выходит за пределы, появится ошибка #NUM!

Когда использовать SMALL в Google Sheets

Функция SMALL полезна, когда нужно найти не просто наименьшее значение, а n‑й по величине минимум. Типичные задачи:

• найти 2‑е/3‑е/10‑е по величине время, оценку или цену;
• собрать список наименьших результатов (топ‑n минимальных значений);
• сортировать один столбец по возрастанию с помощью формул (без постоянного применения «Данные → Сортировка»);
• выбрать минимальные результаты из фильтрованного набора (в сочетании с IF/ARRAYFORMULA).

Краткое определение: SMALL(data, n) — возвращает n‑й наименьший элемент в массиве data.

Синтаксис функции

=SMALL(data, n)

• data — диапазон или массив, который функция просматривает (A1:A10, A2:B11 и т.п.). Все рассматриваемые значения должны быть одного типа (обычно числа или даты).
• n — порядок (целое число >= 1). Если n = 1, SMALL вернёт наименьшее значение; n = 2 — второй наименьший и т.д. Если n больше количества числовых элементов в data, появится ошибка #NUM!.

Совет: даты в Google Sheets хранятся как числа, поэтому SMALL корректно работает и с датами — вернёт раннюю дату как «малое» значение.

Простые и практические примеры

1) Простейший пример: найти наименьшее

Допустим, набор чисел находится в диапазоне A2:A11. Чтобы получить наименьшее число используйте:

=SMALL(A2:A11, 1)

Чтобы получить 3‑е наименьшее:

=SMALL(A2:A11, 3)

Изменяя параметр n, вы можете последовательно получать 1‑й, 2‑й, 3‑й и т.д. минимумы.

Пошагово (типичный рабочий сценарий):

1. Выделите ячейку, где нужен результат (например, C2).
2. Введите формулу: =SMALL(A2:B11, 1).
3. Нажмите Enter. Результат — наименьшее числовое значение в указанном диапазоне.

2) Сортировка столбца по возрастанию с помощью SMALL + ROW

SMALL можно использовать для создания отсортированного столбца формулами. Пример формулы, которая берёт значения из B2:B11 и выводит их по возрастанию:

=SMALL($B$2:$B$11, ROW()-1)

Объяснение:

• $B$2:$B$11 — абсолютная ссылка, чтобы диапазон не смещался при копировании формулы вниз.
• ROW()-1 возвращает 1 в строке 2, 2 в строке 3 и т.д., то есть n автоматически увеличивается при копировании вниз.

Важно: если в исходном столбце есть пустые ячейки или нечисловые значения, итоговый список может содержать пустые результаты или ошибки; заранее очистите или преобразуйте данные.

3) Нахождение минимума по условию: SMALL + IF + ARRAYFORMULA

Частая задача — найти наименьшее значение в одном столбце, но только для строк, где выполняется условие в другом столбце. Пример: есть классы в столбце B и времена в столбце C; нужно минимальное время для всех водителей класса >= 2.

=ARRAYFORMULA(SMALL(IF(B2:B9>=2, C2:C9), 1))

Объяснение:

• IF(B2:B9>=2, C2:C9) возвращает массив: в тех строках, где условие истинно, подставляются значения из C, где ложно — логические FALSE (которые игнорируются SMALL).
• ARRAYFORMULA позволяет корректно обработать массивы без необходимости вводить формулу построчно.

4) Соединение SMALL с другими функциями

• SMALL + INDEX/MATCH — получить строку с n‑м наименьшим значением.
• SMALL + UNIQUE — найти n‑й уникальный минимум (с дополнительной обработкой дубликатов).
• SMALL + FILTER — современная и более прозрачная альтернатива IF/ARRAYFORMULA в новых таблицах: FILTER(C2:C9, B2:B9>=2) и затем SMALL от результата.

Пример с FILTER:

=SMALL(FILTER(C2:C9, B2:B9>=2), 1)

FILTER возвращает только нужные значения, затем SMALL берёт минимум.

Ошибки и причины их появления

Алфавитно‑числовые значения

SMALL игнорирует ненумерические элементы. Если в диапазоне нет чисел, функция вернёт #NUM!. Если часть диапазона содержит текст, эти ячейки просто не учитываются.

Если нужно принудительно преобразовать строки с числами в числа, используйте VALUE() или умножение на 1.

n выходит за пределы диапазона

Если n больше реального количества числовых элементов в data — результат #NUM!. Например, попытка найти 12‑й минимум в наборе из 10 чисел вызовет ошибку.

Пустые или скрытые значения

Пустые ячейки игнорируются. Если в диапазоне есть формулы, которые возвращают пустую строку (“”), их также игнорируют. Однако некоторые логические значения или ошибки в диапазоне могут прервать вычисление — проверьте на наличие ошибок #N/A, #DIV/0! и т.п.

Альтернативы и сравнение

Коротко:

• MIN — проще для поиска одного минимального значения.
• SMALL — лучше, когда нужен n‑й минимум или последовательность минимумов.
• FILTER + SMALL — современный и наглядный подход для условных задач.
• SORT — удобен, если нужно работать с отсортированными таблицами вручную.

Когда SMALL не сработает: контрпримеры

• В диапазоне только текст или пустые строки → #NUM!
• n нецелое или меньше 1 → #NUM! (n должно быть положительным целым).
• Диапазон содержит ошибки (#N/A, #DIV/0!) → SMALL вернёт ошибку.
• Нужно учесть порядок ввода или связать n с другой логикой (например, учесть привязку к дате создания) — тогда SMALL недостаточен сам по себе.

Практическая методология (мини‑SOP)

1. Подготовьте данные: удалите текстовые артефакты, преобразуйте даты и числа к корректным типам.
2. Решите, нужен ли условный фильтр. Если да — используйте FILTER() или IF()+ARRAYFORMULA.
3. Тестируйте формулу на небольшом поднаборе данных (5–10 строк).
4. Если вывод будет копироваться вниз для создания списка, используйте абсолютные ссылки для диапазона и вычисляемый n (ROW() или SEQUENCE()).
5. Обработайте возможные ошибки с помощью IFERROR(…, “сообщение”) или IF(COUNT(…) < n, “нет данных”, …).

Пример проверки перед вызовом SMALL:

=IF(COUNT(A2:A11) < 3, "Мало данных", SMALL(A2:A11, 3))

Чеклист по ролям

• Аналитик данных:

  • Проверить типы данных и пропуски.
  • Добавить защиту от ошибок (IFERROR/COUNT).
  • Документировать порядок n и используемые диапазоны.

• Сотрудник ввода данных:

  • Убедиться, что числа вводятся без лишних пробелов и знаков (в нужном формате).
  • Не смешивать числа и текст в одном столбце.

• Разработчик формул / автоматизации:

  • Если формула копируется, использовать абсолютные ссылки ($).
  • Предпочесть FILTER для ясности и производительности при больших массивах.

Тестовые сценарии и критерии приёмки

Критерии приёмки для формулы, использующей SMALL:

• Формула корректно возвращает 1‑й, 2‑й и 3‑й минимум для заранее известного набора чисел.
• При отсутствии достаточного количества чисел формула возвращает дружелюбное сообщение (не необработанную ошибку).
• Формула корректно работает после вставки/удаления строк в пределах диапазона (при использовании абсолютных ссылок).

Примеры тестов:

1. Нормальный набор: A2:A6 = [5, 2, 9, 2, 7]. SMALL(A2:A6,2) -> 2 (второй наименьший — 2, т.к. дубликаты считаются).
2. Нет чисел: A2:A6 = [“a”, “b”]. SMALL -> #NUM! или обработанное сообщение.
3. n > count: A2:A4 = [1,2,3], n=5 -> выдача сообщения об ошибке или “Мало данных”.

Шпаргалка / Cheat sheet

• Наименьшее: =SMALL(range,1)
• k‑й наименьший: =SMALL(range,k)
• Сортировка вниз: =SMALL($A$2:$A$11, ROW()-1) затем копировать вниз
• Условный минимум: =SMALL(FILTER(values, condition), 1)
• Защита от ошибок: =IF(COUNT(range) < k, “Нет данных”, SMALL(range,k))

Дополнительные советы и рекомендации

• Для больших диапазонов и частых пересчётов FILTER + SMALL чаще оказывается эффективнее и понятнее, чем сложные IF + ARRAYFORMULA.
• Если нужно игнорировать нули как «отсутствие данных», используйте FILTER(range, range<>0).
• Для работы с уникальными значениями сначала примените UNIQUE(), затем SMALL от результата.

Модель мышления: как думать о SMALL

• Представляйте диапазон как упорядоченный список по возрастанию. SMALL — это способ «вытащить» элемент с позиции n в этом упорядочении.
• SMALL не изменяет исходные данные; он лишь читает и вычисляет по позиции.
• При комбинировании с функциями, которые возвращают массивы (FILTER, IF, UNIQUE), SMALL действует как «агрегатор» по результату фильтра.

Безопасность и приватность

SMALL сам по себе не отправляет данные куда-либо — это локальная формула в Google Sheets. Тем не менее, при работе с персональными данными соблюдайте общие правила конфиденциальности: минимизируйте видимость листов, используйте ограниченный доступ и маскирование при необходимости.

Решающее дерево: выбрать SMALL или альтернативу

flowchart TD
  A[Нужно найти минимум?] --> B{Нужен ли n‑й минимум?}
  B -- Да --> C[Используйте SMALL]
  B -- Нет --> D[MIN проще]
  C --> E{Есть условие фильтрации?}
  E -- Да --> F[Используйте FILTER + SMALL]
  E -- Нет --> G[SMALL'range,n']
  D --> H[MIN'range']
  F --> I[Добавьте IFERROR/COUNT для надёжности]

Заключение

SMALL — простая, но гибкая функция для работы с минимальными значениями в Google Sheets. Она особенно полезна, когда вам нужен не только абсолютный минимум, но и последовательность минимальных значений или условные минимумы. Комбинация SMALL с ROW, FILTER, IF и ARRAYFORMULA расширяет её применимость и позволяет строить динамические сортировки и выборки.

Короткое резюме

• SMALL(data, n) возвращает n‑й наименьший элемент диапазона.
• Используйте FILTER или IF+ARRAYFORMULA для условных выборок.
• Для сортировки — SMALL + ROW или лучше SORT/INDEX по обстоятельствам.
• Всегда проверяйте типы данных и защищайте формулы от ошибок через COUNT/IFERROR.