Как использовать функцию LARGE в Google Sheets

Вы уже знаете, что функция MAX находит наибольшее значение в диапазоне. Но что делать, если нужно найти 5‑е, 10‑е, 4‑е или 2‑е по величине значение? Для этого предназначена функция LARGE. В этом руководстве объясняю синтаксис, практические приёмы, типичные ошибки и готовые приёмы для повседневных задач в Google Sheets.

Что такое функция LARGE

Функция LARGE возвращает n‑ое по величине значение из набора данных, заданного как диапазон или массив. Проще: если n = 1, LARGE возвращает максимум; если n = 2 — второй по величине и т.д.

Краткое определение: LARGE(dataset, number) — dataset = диапазон или массив, number = ранг (целое положительное число).

Синтаксис

=LARGE(dataset, number)

Параметры:

• dataset — диапазон ячеек или массив значений (например, A2:A16 или {68,40,59}).
• number — целое положительное число, указывающее ранг (1 = наибольшее, 2 = второе по величине и т. д.).

Важно: если number больше числа числовых значений в dataset, результат будет ошибкой #NUM!. Если в диапазоне есть текстовые значения, они игнорируются при использовании корректных приёмов, но при прямом вызове это может привести к ошибке.

Примеры использования

1. В одномерном диапазоне

Простейший случай — столбец или строка с числами. Чтобы найти 3‑е по величине значение в A2:A16:

=LARGE(A2:A16, 3)

Шаги для новичков:

1. Выберите ячейку для результата.
2. Введите =LARGE(.
3. Укажите диапазон, затем запятую и число ранга.
4. Закройте скобку и нажмите Enter.

2. В двумерном диапазоне

Диапазон может занимать несколько столбцов. LARGE работает с любым прямоугольным диапазоном:

=LARGE(A2:B10, 2)

Это вернёт второе по величине значение в объединённом наборе ячеек A2:B10.

3. Массив в одной ячейке (литерал массива)

Вы можете указать массив прямо в формуле, используя фигурные скобки:

=LARGE({68,40,59,51,55,25}, 3)

Это найдёт третье по величине значение среди перечисленных.

4. Получить несколько лучших значений (топ N)

Чтобы вывести сразу топ‑3 значений из диапазона A2:A16, используйте SEQUENCE вместе с LARGE (Google Sheets поддерживает возврат массивов):

=LARGE(A2:A16, SEQUENCE(3))

Или через ARRAYFORMULA и ROW:

=ARRAYFORMULA(LARGE(A2:A16, ROW(INDIRECT("1:3"))))

Первый пример (SEQUENCE) более читабелен и современен.

5. Пропускать текст и пустые ячейки

Если в диапазоне есть текст или записи — фильтруйте только числа:

=LARGE(FILTER(A2:A16, ISNUMBER(A2:A16)), 2)

FILTER отфильтрует только числовые значения, а LARGE вернёт 2‑е по величине среди них.

6. Игнорировать дубликаты (только уникальные значения)

Иногда нужно найти n‑ое по величине уникальное значение. Для этого сочетайте UNIQUE и LARGE:

=LARGE(UNIQUE(FILTER(A2:A16, ISNUMBER(A2:A16))), 2)

UNIQUE удалит повторяющиеся числа.

7. Безопасный вызов с обработкой ошибок

Чтобы вернуть пустую строку или сообщение вместо ошибки #NUM!, используйте IFERROR:

=IFERROR(LARGE(A2:A16, 5), "Нет данных")

Частые задачи и готовые приёмы

• Топ‑N с учётом только положительных значений:

=LARGE(FILTER(A2:A100, A2:A100>0), SEQUENCE(5))

• N‑ое по величине для каждой категории (с использованием FILTER + UNIQUE по категориям):

=ARRAYFORMULA(LARGE(FILTER(values_range, category_range=G2), H2))

(где G2 = нужная категория, H2 = ранг)

• Получение строки с данными, соответствующей n‑ому по величине значению (с INDEX + MATCH):

=INDEX(A2:C100, MATCH(LARGE(A2:A100, 1), A2:A100, 0), 0)

Это вернёт всю строку, где найден максимальный элемент в колонке A.

Когда не стоит использовать LARGE

• Для вычисления медианы в чётном наборе значений: медиана — это среднее двух центральных значений; LARGE возвращает отдельный элемент, поэтому для медианы лучше применять встроенную функцию MEDIAN.
• Если в диапазоне есть текстовые записи, и вы не фильтруете их, функция может вернуть ошибку. В таких случаях чаще используют MAX после фильтрации чисел или MAX с VALUE/IFERROR в специальных схемах.
• Если нужен учёт веса записей (взвешенная «топ‑ранжировка»), то LARGE не применим напрямую — используйте сортировку по весу или вычисляйте взвешенное значение перед ранжированием.

Похожие и заменяющие функции

• SMALL — находит n‑ое наименьшее значение.
• MIN — минимальное значение в наборе.
• MAX — максимальное значение (то же, что LARGE с n = 1).
• MEDIAN — медиана (удобнее для вычисления среднего центрального значения).

Контрпримеры и когда приём не работает

• Диапазон содержит текст и логические значения: LARGE без предобработки может вернуть ошибку. Решение: FILTER(A2:A, ISNUMBER(A2:A)).
• Нужен уникальный n‑ый максимум, но просто LARGE даст повторяющиеся величины, если данные дублируются. Решение: использовать UNIQUE.
• Диапазон динамически формируется формулой, которая возвращает ошибку при пустых данных — оборачивать IFERROR.

Практическая методика: как внедрять LARGE в шаблоны отчётов (мини‑методология)

1. Определите набор данных и колонку(ы) с числами.
2. Решите, нужно ли исключать текст/пустые/негативные значения.
3. Решите, нужны ли уникальные значения или допустимы дубликаты.
4. Сформируйте формулу с FILTER и/или UNIQUE, затем примените LARGE.
5. Обработайте возможные ошибки через IFERROR.
6. Тестируйте на граничных данных (мало элементов, только текст, дубликаты).

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

Для аналитика:

• Проверить, что диапазон содержит только нужные числовые столбцы.
• Добавить FILTER(ISNUMBER()) при необходимости.
• Сравнить результат с SORT(,FALSE) вручную для валидации.

Для бухгалтера:

• Убедиться, что валюты и форматы единообразны.
• Применить ROUND при необходимости для сравнения похожих значений.

Для менеджера отчёта:

• Проверить, что формулы защищены от удаления диапазонов.
• Добавить поясняющие примечания рядом с результатом.

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

• Формула возвращает правильное n‑ое значение на контрольных наборах (включая повторы).
• При вводе n > числа чисел возвращается понятное сообщение или пустая строка (IFERROR реализован).
• Формула корректно игнорирует текст и пустые ячейки (если это требование).
• Для уникального ранга повторяющиеся значения корректно исключаются (UNIQUE при необходимости).

Тестовые случаи и критерии проверки

1. Нормальный набор: 10 уникальных чисел, n = 3 → совпадает с третьим по величине после сортировки.
2. Повторы: [5,5,4,3], n = 2 → если ожидается второе уникальное значение — должен применяться UNIQUE.
3. Только текст: [‘a’,’b’] и n = 1 → формула через FILTER вернёт ошибку #NUM! или IFERROR вернёт значение по умолчанию.
4. Мало элементов: 2 числа и n = 5 → IFERROR даёт сообщение ‘Нет данных’.

Короткий глоссарий (одна строка каждый)

• LARGE — функция для поиска n‑го по величине значения.
• SEQUENCE — создаёт последовательность чисел (удобно для топ N).
• FILTER — фильтрует диапазон по условию.
• UNIQUE — убирает повторяющиеся значения.
• IFERROR — обрабатывает ошибки и возвращает запасное значение.

Примеры готовых шаблонов

Шаблон 1 — топ‑5 положительных значений из колонки B:

=LARGE(FILTER(B2:B100, B2:B100>0, ISNUMBER(B2:B100)), SEQUENCE(5))

Шаблон 2 — второе уникальное по величине в колонке C, с сообщением при отсутствии:

=IFERROR(LARGE(UNIQUE(FILTER(C2:C100, ISNUMBER(C2:C100))), 2), "Нет второго уникального значения")

Шаблон 3 — вернуть строку с данными, где значение в колонке A — третье по величине:

=INDEX(A2:E100, MATCH(LARGE(A2:A100, 3), A2:A100, 0), 0)

Советы по производительности

• Избегайте применения LARGE к очень большим диапазонам (например, всю колонку A:A) без необходимости — используйте ограниченный диапазон.
• По возможности используйте встроенные функции (FILTER, UNIQUE) для уменьшения количества вычислений.
• Если формулы медленно вычисляются, проверьте зависимые формулы и массивы, которые могут пересчитываться при каждом изменении.

Риски и способы их смягчения

• Ошибки из‑за текстовых значений: применяйте ISNUMBER + FILTER.
• Неправильные ожидания при дубликатах: документируйте, нужны ли уникальные значения.
• Пустые диапазоны → предусмотреть IFERROR с понятным сообщением.

Быстрые подсказки (cheat sheet)

• Получить 1‑е: =LARGE(range,1) или =MAX(range)
• Топ‑N: =LARGE(range, SEQUENCE(N))
• Уникальные топы: =LARGE(UNIQUE(range), n)
• Игнорировать текст: =LARGE(FILTER(range, ISNUMBER(range)), n)

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

LARGE — простая и гибкая функция для поиска n‑го по величине значения. Её сила раскрывается в сочетании с FILTER, UNIQUE, SEQUENCE и IFERROR. Используйте фильтрацию, чтобы избежать ошибок, и UNIQUE, чтобы работать с уникальными значениями.

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