Выбор уникальных: SORTN. Функция, которую нужно уложить в голове

Выбор уникальных: SORTN. Функция, которую нужно уложить в голове
Содержимое страницы

Когда я впервые столкнулся с формулой SORTN, то у меня было ощущение какой-то недосказанности и непонятости, я старался редко ее использовать в Google Таблицах.

Но со временем все очень сильно изменилось. Я понял, что она может быть мощным инструментм в комбинации с другими функциями для решения многих задач. Считаю, что SORTN недостаточно популярна. Серьезно сомневаюсь, что большинство пользователей Google Sheets вообще знают об этой функции. Однако, она хранит в себе отличный потенциал, который раскрывается, когда узнаешь, как это работает.

Все примеры доступны в Таблице, см. раздел Ссылки .

Где может быть использована формула SORTN

Чтобы представить, что умеет SORTN необходимо взглянуть на то, чем ее можно заменить. Например, QUERY или комбинация UNIQUE и SORT часто могут дать нужный результат, но не всегда. SORTN также похожа на работу UNIQUE и ARRAY_CONSTRAIN.

SORTN предназначена для выбора основанного на сортировке значений и удаления дубликатов.

В общем смысле, SORTN предназначена для возврата первых N строк в наборе данных после выполнения сортировки. Сортировка может быть в порядке возрастания или убывания. Таким образом, можно выбирать наибольшие или наименьшие элементы из диапазона. Кроме того, если в данных есть повторяющиеся записи, можно решить, включать или исключать дубликаты.

Как использовать SORTN

Синтаксис кажется немного нагроможденным. Но к нему достаточно просто привыкнуть.

Ниже приводится немного интерпретированная SORTN - Cправка

Синтаксис

SORTN(диапазон; [n]; [режим_показа_совпадений]; [столбец_для_сортировки1, по_возрастанию]; ...)

  • диапазон – данные для сортировки, после которой необходимо выбрать первые n элементов.
  • n – (НЕОБЯЗАТЕЛЬНО, по умолчанию 1) количество возвращаемых элементов. Должно быть больше 0. Чтобы вывести весь результат, достаточно ввести очень большое число, такое как 9^9. Этот аргумент необязательно использовать только в случае вызова с единственным аргументом диапазон, например, =SORTN(диапазон).
  • режим_показа_совпадений – (НЕОБЯЗАТЕЛЬНО, по умолчанию 0) число, определяющее способ показа совпадений.
    • 0: показать не более нескольких первых строк (n) в отсортированном диапазоне (простой вывод);
    • 1: показать не более нескольких первых строк (n), а также все дополнительные строки, идентичные строке с порядковым номером n (выбор всех подходящих);
    • 2: показать не более нескольких первых строк (n) после удаления повторяющихся строк (выбор уникальных);
    • 3: показать не более нескольких первых уникальных строк (n) со всеми их точными копиями (выбор всех уникальных).
  • столбец_для_сортировки1 – (НЕОБЯЗАТЕЛЬНО) номер столбца в диапазоне или набор ячеек за пределами диапазона, на основании которых выполняется сортировка. Диапазон ячеек, определенный как столбец_для_сортировки1, должен представлять собой один столбец с таким же количеством строк, как и в указанном диапазоне.
  • по_возрастанию1 – (НЕОБЯЗАТЕЛЬНО) указывает на способ сортировки столбца_для_сортировки1. Значения: ИСТИНА и ЛОЖЬ, или 1 и 0, или любые значения, которые могут интерпретироваться как булевы. ИСТИНА сортирует данные по возрастанию, а ЛОЖЬ – по убыванию.
  • столбец_для_сортировки2; по_возрастанию2; ... – (НЕОБЯЗАТЕЛЬНО) дополнительные столбцы и флаги порядка сортировки, использующиеся в случае совпадений, применяются в порядке старшинства.

Примечания

  • диапазон сортируется только в области указанных столбцов, остальные столбцы возвращаются в своем изначальном порядке.
  • Если аргументы столбец_для_сортировки1 и по_возрастанию1 отсутствуют, сортировка выполняется по столбцу с наименьшим порядковым номером в диапазоне, а следующие столбцы используются для поиска совпадений.

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

В данном разделе все примеры отталкиваются от следующего набора данных, его легенда: равное количество участников проходят несколько туров, за которые получают очки и дополнительные баллы

Набор данных для примеров SORTN

Получение двух первых из сортировки по возрастанию

Формула выводит первых двух участников в первом туре в порядке вхождения в массив

SORTN - режим 0 или простой вывод

=SORTN(B4:E23;2;0;1;1)

Пояснение: из диапазона B4:E23 взять первые две строки, диапазон сортировать по первой колонке по возрастанию, т.е. по туру. режим_показа_совпадений не применять.

Получение двух первых из сортировки по убыванию

Формула выводит первых двух участников в последнем туре в порядке вхождения в массив

SORTN - режим 0 или простой вывод

=SORTN(B4:E23;2;0;1;0)

Выбрать все, но не менее определенного количества

Положим, что нам нужно взять всех участников с минимальными очками (в данном случае это 1), но их должно быть не менее пяти. Т.е. если участников с самыми минимальными очками 1 окажется меньше пяти, то необходимо выбрать остальных со следующим минимальным значением 2. Всего будет выведено шесть значений.

SORTN - режим 1 или выбор всех подходящих

=SORTN(B4:E23;5;1;3;1)

Пояснение: из диапазона B4:E23 взять не менее пяти строк, диапазон сортировать по третьей колонке по возрастанию, т.е. по очкам. режим_показа_совпадений - получить все значения, если их окажется меньше пяти, то взять следующее исходя из сортировки.

Выбрать все равные первому, определенному сортировкой

Положим, что нам нужно взять всех участников с максимальными дополнительными баллами (в данном случае это 2). Найдем только первое значение сортировки и выведем все похожие на него.

SORTN - режим 1 или выбор всех подходящих

=SORTN(B4:E23;1;1;4;0)

Пояснение: из диапазона B4:E23 взять первую строку, диапазон сортировать по четвертой колонке по убыванию, т.е. по баллам. режим_показа_совпадений - получить первое значение и все на него похожие.

Выбрать все уникальные записи

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

SORTN - режим 2, выбор уникальных

=SORTN(B4:E23;9^9;2)

Пояснение: из диапазона B4:E23 взять все уникальные записи, сортировать по всем колонкам в основном порядке.

Этот пример не является характерным. Следующий пример комбинирует возможности режима 1 и 2.

Выбрать все уникальные записи по условию сортировки

Требуется найти первые три уникальные максимальные значения очков (это 11, 9 и 8) и выбрать все записи с ними.

SORTN - режим 3, выбор всех уникальных

=SORTN(B4:E23;9^9;2)

Пояснение: из диапазона B4:E23 взять все уникальные записи, сортировать по всем колонкам в основном порядке.

Выводы и рекомендации

Возможности SORTN при первом знакомстве сложно переоценить. Формула кажется неэффективной и запутанной. Но стоит приблизится к ней, рассмотреть, изучить внимательнее, и она становится мощным и полезным инструментом.

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

Ссылки