Функции СОРТ с СОРТПО в Excel

Функции СОРТ с СОРТПО

Для динамической сортировки данных Excel предлагает использовать функции СОРТ и СОРТПО. Они доступны начиная с версии Office 365.

Скачайте файл с примером

Скачать

Функция СОРТ

«В 2018 году компания Microsoft выпустила обновление Excel, в котором появились динамические массивы и новые функции для работы с ними. В этой статье я научу Вас работать с функциями СОРТ (SORT) и СОРТПО (SORTBY).

Шлычков Константин

Эксперт и преподаватель Excel

Функция СОРТ возвращает отсортированный массив данных. Если исходные данные будут меняться (добавляться/удаляться/редактироваться), то отсортированный массив будет учитывать это. Давайте изучим синтаксис функции и ее работу на практическом примере.

Синтаксис функции СОРТ

=СОРТ(исходный массив, [индекс сортировки], [порядок сортировки], [по столбцу])

Исходный массив - диапазон данных для сортировки (обязательный параметр).

Индекс сортировки - номер строки или столбца, по которому выполняется сортировка. Если параметр не указан, то сортировка идет по 1 столбцу/строке

Порядок сортировки - число, указывающее порядок сортировки: 1 - по возрастанию (по умолчанию), -1 для сортировки по убыванию

По столбцу - логический параметр, который принимает значение ЛОЖЬ для сортировки по строкам (по умолчанию), ИСТИНА - для сортировки по столбцам.

Обязательным параметром функции СОРТ является только исходный массив. Это может быть строка, столбец или диапазон данных. В ячейку E3 я ввожу формулу =сорт(C3:C12). В результате в столбце E выводятся данные из столбца C отсортированные по возрастанию.

Порядок сортировки задается 3-м параметром функции. По умолчанию он равен 1 ( сортировку данных идет по возрастанию). Если же указать значение -1, то данные будут отсортированы по убыванию. Для этого я меняю формулу в ячейке E3 на =СОРТ(B3:B12,,-1). Обратите внимание, что 2-ой и 4-ый параметры я не указал.

Чтобы выбрать столбец для сортировки, нужно во 2-ом параметре задать номер столбца. Посмотрите в формуле =СОРТ(J2:N14,3,-1) в качестве столбца для сортировки указан 3-ий столбец (в моем примере - это количество товара), а 3-ий параметр (порядок сортировки) равен -1, что означает сортировку по убыванию.

Если Вам нужно сортировать исходные данные по строкам, а не по столбцам, тогда измените 4-ый параметр функции на значение ИСТИНА. По умолчанию значение этого параметра - ЛОЖЬ, что подразумевает сортировку по столбцам.

Функция СОРТПО

Вы обратили внимание, что функция СОРТ сортирует данные только по одному столбцу. Функция СОРТПО расширяет возможности сортировки. Она позволяет сортировать данные по нескольким столбцам.

Синтаксис функции СОРТПО

=СОРТПО(исходный массив, массив сортировки, [порядок сортировки], [массив сортировки 2, порядок сортировки 2],...)

Исходный массив - диапазон данных для сортировки (обязательный параметр).

Массив сортировки - столбец, по которому будет проходить сортировка (обязательный параметр).

Порядок сортировки- число, указывающее порядок сортировки: 1 - по возрастанию (по умолчанию), -1 для сортировки по убыванию

Обязательным параметром функции СОРТПО являются первые 2 параметра: массив данных для сортировки и столбец, по которому сортировку нужно производить. В примере ниже я указал 2-ым параметром диапазон данных C2:C14. Сама формула выглядит так =СОРТПО(B2:F14,C2:C14).

Таким же образом можно продолжить запись аргументов функции, указав другие столбцы и заодно порядок сортировки. Я поменяю формулу =СОРТПО(B2:F14,C2:C14,1, E2:E14,-1). Таким образом данные сначала отсортируются по столбцу С по возрастанию (Дата привоза - вначале будут более ранние даты), а затем по 4-ому столбцу E по убыванию (в пример - это объем продаж от большего к меньшему).

Разбор ошибок

В качестве массива данных нельзя указывать значение нескольких строк или столбцов. Например, указав массив значений А:C в качестве 1-го параметра функции СОРТ, функция выдаст ошибку #ПЕРЕНОС! с текстом: "Слишком большой диапазон для переноса данных" . В правильной записи массива олжно ыть указаны координаты верхней левой и нижней правой ячейки =СОРТ(A2:C10).

Также функция выдаст ошибку, если одна или несколько ячеек, в которых должен появиться отсортированный массив, будут не пустыми. На иллюстрации ниже ячейка F10 не пустая. Ошибка исчезнет, если очистить ячейку столбца F.