СОРТ и СОРТПО в Excel (эксель): сортировка формулой

=СОРТ(A2:C7;3;-1) возвращает таблицу A2:C7, отсортированную по её третьему столбцу, продажам, от большего к меньшему. Это функция СОРТ (по-английски SORT). Исходная таблица остаётся как есть; отсортированная копия разливается от ячейки с формулой и пересортировывается сама, когда меняется число. Замените C7 на 300, и Finn поднимется наверх. В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой, как здесь.

ФормулыСбросить

E2fx

=SORT(A2:C7,3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Dan	East	150
4	Cara	North	200		Ann	North	120
5	Dan	East	150		Eve	South	95
6	Eve	South	95		Ben	South	80
7	Finn	North	60		Finn	North	60

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СОРТ(A2:C7;3;-1)

Синтаксис СОРТ

=SORT(array, [sort_index], [sort_order], [by_col])

• array (массив) это диапазон, который нужно отсортировать и вернуть.
• sort_index (индекс_сортировки) это номер столбца внутри array, по которому идёт сортировка. По умолчанию 1.
• sort_order (порядок_сортировки) равен 1 для возрастания (по умолчанию) или -1 для убывания.
• by_col (по_столбцу) равен ЛОЖЬ (по умолчанию), чтобы сортировать строки, и ИСТИНА, чтобы сортировать столбцы слева направо.

СОРТ и СОРТПО требуют Excel 2021, Excel 2024 или Microsoft 365; старые версии показывают #ИМЯ? (по-английски #NAME?). В Google Таблицах есть обе, хотя там SORT принимает номер столбца и ИСТИНА или ЛОЖЬ парами.

По возрастанию сначала идут числа, затем текст от А до Я, затем ЛОЖЬ и ИСТИНА. Текст сравнивается без учёта регистра. Пустые ячейки в array возвращаются в результате как 0.

Как отсортировать по алфавиту в Excel

Чтобы упорядочить список на месте, щёлкните любую его ячейку и выберите Данные > Сортировка от А до Я (или от Я до А). Если у списка есть соседние столбцы, Excel отсортирует всю таблицу, и каждая строка останется целой. Для нескольких столбцов или своего списка используйте Данные > Сортировка.

Чтобы сохранить исходный порядок и получить копию по алфавиту, которая обновляется сама, используйте СОРТ без других аргументов:

ФормулыСбросить

C2fx

=SORT(A2:A8)

	A	B	C
1	Name		A to Z
2	Mia		Ann
3	Ben		Ben
4	Zoe		Cara
5	Ann		Dan
6	Leo		Leo
7	Cara		Mia
8	Dan		Zoe

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СОРТ(A2:A8)

Введите Abe в A3, и он окажется первым. =СОРТ(A2:A8;;-1) даёт обратный порядок; два разделителя подряд пропускают sort_index. Чтобы отсортировать список без повторов, сортируйте результат УНИК: =СОРТ(УНИК(A2:A8)) (см. УНИК).

СОРТПО: сортировка по столбцу, который не возвращается

СОРТ может сортировать только по столбцу возвращаемого диапазона. СОРТПО (по-английски SORTBY) принимает ключ сортировки отдельным диапазоном, поэтому можно вернуть одни имена, упорядоченные по продажам:

ФормулыСбросить

E2fx

=SORTBY(A2:A7,C2:C7,-1)

	A	B	C	D	E
1	Name	Region	Sales		By sales
2	Ann	North	120		Cara
3	Ben	South	80		Dan
4	Cara	North	200		Ann
5	Dan	East	150		Eve
6	Eve	South	95		Ben
7	Finn	North	60		Finn

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СОРТПО(A2:A7;C2:C7;-1)

Результат: Cara, Dan, Ann, Eve, Ben, Finn. Синтаксис: =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...). В каждом by_array должно быть столько же строк, сколько в array, иначе СОРТПО возвращает #ЗНАЧ! (по-английски #VALUE!). Таблицы на этой странице показывают ошибки под английскими именами.

Для тройки лучших возьмите первые строки результата: =ВЗЯТЬ(СОРТПО(A2:A7;C2:C7;-1);3) возвращает Cara, Dan и Ann. ВЗЯТЬ требует Microsoft 365 или Excel 2024; в Excel 2021 используйте =ИНДЕКС(СОРТПО(A2:A7;C2:C7;-1);ПОСЛЕД(3)).

Сортировка по нескольким столбцам

Передайте СОРТПО по одной паре ключа и порядка на каждый уровень. Эта формула сортирует по региону от A до Z, а внутри каждого региона по продажам от большего к меньшему:

ФормулыСбросить

E2fx

=SORTBY(A2:C7,B2:B7,1,C2:C7,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Dan	East	150
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Ann	North	120
5	Dan	East	150		Finn	North	60
6	Eve	South	95		Eve	South	95
7	Finn	North	60		Ben	South	80

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СОРТПО(A2:C7;B2:B7;1;C2:C7;-1)

Первым идёт East, затем три строки North в порядке Cara (200), Ann (120) и Finn (60), затем South. СОРТ может сделать то же с массивами номеров столбцов и порядков, хотя СОРТПО читается проще:

=SORT(A2:C7,{2,3},{1,-1})

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

Сортировка в своём порядке

Алфавитный порядок нужен не всегда. Чтобы отсортировать регионы как North, East, South, превратите каждый регион в его позицию в этом списке функцией ПОИСКПОЗ и сортируйте по позициям:

ФормулыСбросить

E2fx

=SORTBY(A2:C7,MATCH(B2:B7,{"North","East","South"},0))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150		Dan	East	150
6	Eve	South	95		Ben	South	80
7	Finn	North	60		Eve	South	95

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

ПОИСКПОЗ возвращает 1 для North, 2 для East и 3 для South, а СОРТПО сортирует по этим числам. Строки с одинаковым ключом сохраняют исходный порядок, поэтому строки North остаются в порядке Ann, Cara, Finn. Тем же приёмом можно сортировать месяцы или дни недели по названию: перечислите их внутри ПОИСКПОЗ в календарном порядке. Регион, которого нет в списке, получает от ПОИСКПОЗ #Н/Д (по-английски #N/A); чтобы такие строки шли последними, используйте как ключ IFERROR(MATCH(B2:B7,{"North","East","South"},0),99) (английская форма, из-за фигурных скобок).

Практика: имена по продажам

ФормулыСбросить

E2fx

	A	B	C	D	E
1	Name	Region	Sales		By sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

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

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

ПроверитьПоказать ответ

Функция СОРТ или кнопка «Сортировка»

Сортируют обе, но задачи у них разные:

Кнопкой удобно один раз привести таблицу в порядок. Функция нужна для отчёта или дашборда, который должен оставаться отсортированным, пока данные меняются. Если ячейки ниже или правее формулы не пусты, функция показывает #ПЕРЕНОС! (по-английски #SPILL!) вместо отсортированного списка; очистите их, и список появится (см. ошибка #ПЕРЕНОС!).