Сортировка данных в Excel 2007/2010/2013/2016. Как отсортировать данные в Excel по строкам и по столбцам?
Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» или «от Я до А»). Если данные числовые, их можно отсортировать в порядке возрастания или убывания. Если в диапазоне данных есть строка или столбец, в которых содержатся данные типа время или дата, их можно отсортировать в прямом или обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.
Сортировать данные можно по одному условию (например, сортировка списка сотрудников по фамилии) или нескольким (например, сортировка списка сотрудников по занимаемой должности, а внутри каждой должности фамилии отсортировать в алфавитном порядке). Данные можно сортировать по столбцу (или нескольким столбцам) или по строке.
Как отсортировать по алфавиту
Поскольку в большинстве таблиц числовые параметры привязаны к какому-либо тексту – фамилии или названию товара то удобно расположить по алфавиту. Рассмотрим особенности работы с этим инструментом в редакторе Еxcel на примере таблицы «Содержание питательных веществ во фруктах и овощах».
Для того чтобы отсортировать названия овощей и фруктов по алфавиту необходимо выделить первый столбец, кликнув мышью по его заголовку. Далее требуется перейти на панель инструментов «Редактирование» и открыть вкладку «Главная», на которой расположена специальная кнопка «Сортировка и фильтр». Для упорядочивания названий в первом столбце от А до Я или наоборот, достаточно выбрать соответствующую команду сортировки в выпадающем меню. Если в таблицу Еxcel будут добавлены данные о других продуктах сработает автоматическая сортировка.
Как в Еxcel отсортировать по возрастанию значений
Простое распределение по возрастанию в программе Эксель осуществляется аналогичным образом, что и по алфавиту. После выделения нужного столбца в верхней части главного окна на панели задач «Главная» в разделе «редактирование» выбирается кнопка «Сортировка и фильтр», имеющая дополнительное меню. В открывшемся списке необходимо выбрать подходящий вариант. Когда данные в таблице Эксель нужно упорядочить от большего к меньшему, их следует отсортировать по убыванию, в противном случае выбирается пункт «Сортировка по возрастанию».
Если диапазон данных состоит из двух и более столбцов, при сортировке на экране должно появиться диалоговое окно для выбора дальнейших действий. Если пользователю необходимо отсортировать по возрастанию данные во всей таблице, то следует указать «автоматически расширить выделенный диапазон», во втором случае данные будут упорядочены только в выбранном столбце.
Решение1
Если числовой столбец гарантировано не содержит повторяющихся значений, то задача решается легко:
- Числовой столбец отсортировать функцией НАИБОЛЬШИЙ() (см. статью Сортированный список (ЧИСЛОвые значения) );
- Функцией ВПР() или связкой функций ИНДЕКС()+ПОИСКПОЗ() выбрать значения из текстового столбца по соответствующему ему числовому значению.
Однако, в реальных задачах числовой столбец может содержать повторы, а так как функция ВПР() в случае наличия повторов всегда выбирает только первое значение сверху (см. статью Функция ВПР() в MS EXCEL ), то этот подход не годится (названия Фруктов будут выведены неправильно).
Поэтому механизм сортировки придется реализовывать по другому.
Создадим для удобства 2 Динамических диапазона Фрукты и Продажи , которые будут ссылаться на диапазоны ячеек, содержащие значения в соответствующих столбцах исходной таблицы. При добавлении новых строк в таблицу, границы этих динамических диапазонов будут автоматически расширяться.
В столбцах D и E разместим таблицу, которая будет динамически сортироваться,
В ячейке Е7 запишем зубодробительную формулу массива :
=ИНДЕКС(Продажи; ОКРУГЛ(ОСТАТ(НАИБОЛЬШИЙ( —(СЧЁТЕСЛИ(Продажи;»
Данная формула сортирует столбец Объем продаж (динамический диапазон Продажи ) по убыванию. Пропуски в исходной таблице не допускаются. Количество строк в исходной таблице должно быть меньше 1000.
Разберем формулу подробнее:
- Формула СЧЁТЕСЛИ(Продажи;» возвращает массив <4:5:0:2:7:1:3:5>. Это означает, что число 64 (из ячейки B7 исходной таблицы, т.е. первое число из диапазона Продажи ) больше 4-х значений из того же диапазона; число 74 (из ячейки B8 исходной таблицы, т.е. второе число из диапазона Продажи ) больше 5-и значений из того же диапазона; следующее число 23 — самое маленькое (оно никого не больше) и т.д.
- Теперь вышеуказанный массив целых чисел превратим в массив чисел с дробной частью, где в качестве дробной части будет содержаться номер позиции числа в массиве: <4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008>. Это реализовано выражением &»,»&ПОВТОР(«0»;3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6)) Именно в этой части формулы заложено ограничение о не более 1000 строк в исходной таблице (см. выше). При желании его можно легко изменить, но это бессмысленно (см. ниже раздел о скорости вычислений).
- Функция НАИБОЛЬШИЙ() сортирует вышеуказанный массив.
- Функция ОСТАТ() возвращает дробную часть числа, представляющую собой номера позиций/1000, например 0,005.
- Функция ОКРУГЛ() , после умножения на 1000, округляет до целого и возвращает номер позиции. Теперь все номера позиций соответствуют числам столбца Объемы продаж, отсортированных по убыванию.
- Функция ИНДЕКС() по номеру позиции возвращает соответствующее ему число.
Аналогичную формулу можно написать для вывода значений в столбец Фрукты =ИНДЕКС(Фрукты;ОКРУГЛ(…))
В файле примера , из-за соображений скорости вычислений (см. ниже), однотипная часть формулы, т.е. все, что внутри функции ОКРУГЛ() , вынесена в отдельный столбец J . Поэтому итоговые формулы в сортированной таблице выглядят так: =ИНДЕКС(Фрукты;J7) и =ИНДЕКС(Продажи;J7)
Также, изменив в формуле массива функцию НАИБОЛЬШИЙ() на НАИМЕНЬШИЙ() получим сортировку по возрастанию.
Для наглядности, величины значений в столбце Объем Продаж выделены с помощью Условного форматирования ( Главная/ Стили/ Условное форматирование/ Гистограммы ). Как видно, сортировка работает.
Как сделать сортировку по дате
В таблицах, где в хронологическом порядке отражаются определенные операции, их часто требуется отсортировать по дате проведения. Поскольку дата это сочетание целых чисел, представленных определенным образом, для правильного осуществления сортировки необходимо, чтобы был выбран соответствующий формат ячеек. Если формат указан неправильно, программа не сможет распознать значения дат, поэтому упорядочить их не получится.
Чтобы рассортировать строки таблицы по дате в редакторе Эксель нужно выполнить следующую последовательность действий:
- Выделить любую ячейку в столбце с датами, которые требуется упорядочить.
- Во вкладке «Главная» найти кнопку «Сортировка и фильтр», навести на нее курсор и выбрать одно из предложенных действий в выпадающем списке. Это может быть распределение от новых к старым, когда в начале списка будут находится наиболее поздние даты и привязанные к ним значения, либо наоборот от старых к новым.
Еще одна формула массива (+дополнительный столбец). Задача1.1
Сортировку таблицы можно сделать с помощью другой, более простой формулы массива , но нам понадобится дополнительный (служебный) столбец D (см. файл примера лист Пример2 ):
В столбце F содержится отсортированный столбец В (объем продаж). Формула возвращает позицию значения объема продаж. Например, число 86 находится в 5-й строке таблицы.
Для повторов выражение ЕСЛИ(F8=$B$7:$B$14;СТРОКА($B$7:$B$14)-СТРОКА($B$6);0) будет возвращать несколько значений: <0:2:0:0:0:0:0:8>, т.е. число 74 находится в строках 2 и 8.
С помощью функции НАИБОЛЬШИЙ() сначала выводится 2, затем 8 (в разных строках).
Эта формула более наглядна, чем рассмотренная выше в начале статьи, но требует наличия дополнительного столбца.
Сортировка по цвету ячейки и по шрифту
В случае если определенный диапазон табличных данных отформатирован с использованием шрифта различных цветов или заливки, пользователь может рассортировать строки по цвету, в который окрашена часть ячеек. Упорядочить данные можно также по набору значков, при создании которых применялось условное форматирование. В любом случае это можно сделать следующим образом:
- Курсором мыши нужно выбрать одну из ячеек с данными в нужном столбце.
- На вкладке «Главная» в функциональной группе «Редактирование» следует найти кнопку «Сортировка и фильтрация» и выбрать в дополнительном меню команду «Настраиваемая сортировка».
- В открывшемся окне сначала указать столбец, в котором нужно упорядочить данные, а потом тип сортировки. Это может быть цвет ячейки, цвет шрифта или значок условного форматирования.
- В зависимости от выбранного вида сортировки в группе порядок необходимо отметить нужный значок либо оттенок заливки или шрифта.
- Последний параметр, который нужно указать – это последовательность расположения (сверху или снизу).
Поскольку определенный порядок значков или цветов в редакторе не предусмотрен, его нужно создать самостоятельно. Для этого следует необходимо нажать кнопку «Добавить уровень» и затем повторить указанные шаги для каждого цвета или значка отдельно, исключая те, которые не нужно включать в сортировку,
Сортировка в Excel по нескольким столбцам
Если возникла необходимость отсортировать данные в редакторе Эксель по двум или более столбцам, следует так же, как и в предыдущем случае выбрать диапазон данных и открыть окно «Настраиваемая сортировка». Далее в первой группе следует отметить заголовок столбца, данные в котором нужно упорядочить в первую очередь. Вторая группа остается без изменений, а в третьей необходимо указать желаемый тип сортировки.
Для указания критериев сортировки для второго столбца нужно добавить еще один уровень. В итоге количество уровней будет соответствовать числу столбцов, по которым необходимо отсортировать данные.
Сортировать в случайном порядке
И так тоже можно. Методика похожа на предыдущую
- Назовите новый столбец «Порядок»
- Заполните его случайными значениями с помощью функции СЛЧИС
Это всё, что я хотел рассказать о том, как сделать сортировку в Excel . Пишите комментарии с вопросами, будем разбираться вместе. Если материал вам помог – поделитесь им с друзьями!
Динамическая сортировка таблицы в MS Excel
При выполнении некоторых задач в Excel, требуется установить автоматическую сортировку, которая предполагает наличие формул. В зависимости от типа данных в используемом диапазоне динамическая сортировка может быть задана тремя способами:
- Если информация в ячейках столбца представлена числами, используются функции НАИМЕНЬШИЙ и СТРОКА. Первая находит наименьший элемент из массива, а вторая определяет порядковый номер строки. Таким образом формируется последовательность. Формула записывается следующим образом: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)).
- Когда в ячейках содержаться текст, первая формула не сработает. Для сортировки в этом случае целесообразно применять формулу: =СЧЁТЕСЛИ(A:A;”<�“&A1)+ СЧЁТЕСЛИ ($A$1:A1;”=”&A1).
- Для автоматической сортировки текстовой информации текстовой информации также используется формула массива: =ИНДЕКС(List; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(List; “<�“&List); СТРОКА(1:1)); СЧЁТЕСЛИ(List; “<�“&List); 0)). Здесь List – это заданный диапазон.
Сортировка промежуточных итогов
В Excel есть такой инструмент, как Промежуточные итоги. Вкратце, он нужен для того, чтобы автоматически создавать промежуточные итоги под группой однородных по некоторому признаку ячеек.
Это отличная функция, но как сделать, чтобы вверху были группы с наибольшей суммой? Ручной труд, конечно, в почете, но есть и другой вариант. Достаточно применить обычную сортировку к сгруппированным данным.
Внутреннее содержимое групп не изменится. Общий итог останется внизу.
Как убрать сортировку в Excel
Для отмены единичной сортировки диапазона данных достаточно нажать кнопку «Отменить ввод» в левом углу экрана. Бывает так, что изменения в файле были сохранены и отменить действие невозможно. Как убрать сортировку в этом случае?
Если после сложных манипуляций с таблицей, ее нужно будет вернуть в первоначальный вид, перед выполнением сортировки следует специально создать дополнительный столбец, в котором будет отражена нумерация строк. После завершения сложного анализа числовых и текстовых данных, представленных в таблице, чтобы отменить все проделанные операции достаточно будет установить сортировку по созданному столбцу.
Сортировка данных в Excel по строкам и столбцам с помощью формул
Сортировка данных в Excel – инструмент для представления информации в удобном для пользователя виде.
Числовые значения можно отсортировать по возрастанию и убыванию, текстовые – по алфавиту и в обратном порядке. Доступны варианты – по цвету и шрифту, в произвольном порядке, по нескольким условиям. Сортируются столбцы и строки.
Порядок сортировки в Excel
Существует два способа открыть меню сортировки:
- Щелкнуть правой кнопкой мыши по таблице. Выбрать «Сортировку» и способ.
- Открыть вкладку «Данные» — диалоговое окно «Сортировка».
Часто используемые методы сортировки представлены одной кнопкой на панели задач:
Сортировка таблицы по отдельному столбцу:
- Чтобы программа правильно выполнила задачу, выделяем нужный столбец в диапазоне данных.
- Далее действуем в зависимости от поставленной задачи. Если нужно выполнить простую сортировку по возрастанию/убыванию (алфавиту или обратно), то достаточно нажать соответствующую кнопку на панели задач. Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида:
Чтобы сохранилось соответствие значений в строках, выбираем действие «автоматически расширить выделенный диапазон». В противном случае отсортируется только выделенный столбец – структура таблицы нарушится.
Если выделить всю таблицу и выполнить сортировку, то отсортируется первый столбец. Данные в строках станут в соответствии с положением значений в первом столбце.
Сортировка по цвету ячейки и по шрифту
Программа Excel предоставляет пользователю богатые возможности форматирования. Следовательно, можно оперировать разными форматами.
Сделаем в учебной таблице столбец «Итог» и «зальем» ячейки со значениями разными оттенками. Выполним сортировку по цвету:
- Выделяем столбец – правая кнопка мыши – «Сортировка».
- Из предложенного списка выбираем «Сначала ячейки с выделенным цветом».
- Соглашаемся «автоматически расширить диапазон».
Программа отсортировала ячейки по акцентам. Пользователь может самостоятельно выбрать порядок сортировки цвета. Для этого в списке возможностей инструмента выбираем «Настраиваемую сортировку».
В открывшемся окне вводим необходимые параметры:
Здесь можно выбрать порядок представления разных по цвету ячеек.
По такому же принципу сортируются данные по шрифту.
Сортировка в Excel по нескольким столбцам
Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.
- Открываем меню «Настраиваемая сортировка». Назначаем первый критерий.
- Нажимаем кнопку «Добавить уровень».
- Появляются окошки для введения данных следующего условия сортировки. Заполняем их.
Программа позволяет добавить сразу несколько критериев чтобы выполнить сортировку в особом порядке.
Сортировка строк в Excel
По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:
- В диалоговом окне «Настраиваемой сортировки» нажать кнопку «Параметры».
- В открывшемся меню выбрать «Столбцы диапазона».
- Нажать ОК. В окне «Сортировки» появятся поля для заполнения условий по строкам.
Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.
Случайная сортировка в Excel
Встроенные параметры сортировки не позволяют расположить данные в столбце случайным образом. С этой задачей справится функция СЛЧИС.
Например, нужно расположить в случайном порядке набор неких чисел.
Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.
Теперь отсортируем полученный столбец по возрастанию /убыванию – значения в исходном диапазоне автоматически расположатся в случайном порядке.
Динамическая сортировка таблицы в MS Excel
Если применить к таблице стандартную сортировку, то при изменении данных она не будет актуальной. Нужно сделать так, чтобы значения сортировались автоматически. Используем формулы.
- Есть набор простых чисел, которые нужно отсортировать по возрастанию.
- Ставим курсор в соседнюю ячейку и вводим формулу: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Именно так. В качестве диапазона указываем весь столбец. А в качестве коэффициента – функцию СТРОКА со ссылкой на первую ячейку.
- Изменим в исходном диапазоне цифру 7 на 25 – «сортировка» по возрастанию тоже изменится.
Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.
Для динамической сортировки текстовых значений понадобятся формулы массива.
- Исходные данные – перечень неких названий в произвольном порядке. В нашем примере – список фруктов.
- Выделяем столбец и даем ему имя «Фрукты». Для этого в поле имен, что находится возле строки формул вводим нужное нам имя для присвоения его к выделенному диапазону ячеек.
- В соседней ячейке (в примере – в В5) пишем формулу: Так как перед нами формула массива, нажимаем сочетание Ctrl + Shift + Enter. Размножаем формулу на весь столбец.
- Если в исходный столбец будут добавляться строки, то вводим чуть модифицированную формулу: Добавим в диапазон «фрукты» еще одно значение «помело» и проверим:
Впоследствии при добавлении данных в таблицу процесс сортирования будет выполняться автоматически.
Способы сортировки данных в Microsoft Excel
Важно! Сортировка данных в Microsoft Excel — это базовая функция табличного редактора, доступная во всех версиях программы. Но в статье будут отдельно рассматриваться редакции 2007 – 2021 и 2003 годов, так как у них наблюдаются существенные различия в интерфейсе.
Читайте также: Функции сортировки и фильтрации данных в Microsoft Excel
Вариант 1: Microsoft Excel 2007 – 2021
Начиная с 2007 года, кампанией Microsoft было разработано множество версий табличного процессора Excel. Нововведения в основном заключались в изменении визуального оформления программы и добавлении новых функций, поэтому сортировка данных во всех редакциях выполняется одинаково.
Примечание! Все действия, представленные в статье, будут производиться в Microsoft Excel 2021. Это следует учитывать, так как в других версиях есть отличия в наименовании и месторасположении некоторых элементов интерфейса. В остальном же описываемые способы решения поставленной задачи являются актуальными.
Способ 1: Сортировка по алфавиту
Если необходимо отсортировать значения в таблице по возрастанию или убыванию, проще всего воспользоваться инструментами контекстного меню. Для этого необходимо иметь уже заполненный диапазон данных, логично поделенный на столбцы и строки. В качестве примера будет использоваться список товаров с расценками, который можно наблюдать на изображении ниже.
Допустим, есть необходимость выполнить сортировку продуктов по алфавиту. В таком случае, зажав левую кнопку мыши, выделите информацию в первом столбце, после чего вызовите контекстное меню, кликнув по диапазону правой. В появившемся списке опций наведите курсор на пункт «Сортировка», а затем щелкните «Сортировка от А до Я» или «Сортировка от Я до А» (в зависимости от поставленной задачи).
Появится диалоговое окно, в котором необходимо определиться, нужно ли сортировать всю таблицу целиком или требуется изменить значения только в выделенном диапазоне. В первом случае следует установить переключатель в положение «автоматически расширить выделенный диапазон» и кликнуть по кнопке «Сортировка», а во втором — выбрать опцию «сортировать в пределах указанного выделения».
Если использовать первый вариант, подразумевающий сортировку всех данных таблицы, относительно алфавитному порядку позиций в столбце «Список продуктов», то результат будет таким, который продемонстрирован на изображении ниже.
Если не расширять диапазон данных для сортировки, тогда только первый столбец будет подвергнут изменениям.
Это только один способ быстрой сортировки данных в алфавитном порядке, дополнительные решения представлены в отдельной статье на нашем сайте. Также рекомендуем обратить внимание на материал, посвященный теме сортировки чисел, если такие манипуляции необходимо будет проводить.
Способ 2: Настраиваемая сортировка
Если данные необходимо отсортировать с применением дополнительных аргументов, а не только по алфавиту, потребуется воспользоваться специальным инструментом. Запустить его меню можно несколькими способами, первый подразумевает переход на вкладку «Данные» с последующим нажатием по пункту «Сортировка».
В качестве альтернативы поставленную задачу можно выполнить через контекстное меню, вызываемое правой кнопкой мыши. Для этого кликните по любой ячейке таблицы и в появившемся списке опций наведите курсор на строку «Сортировка», а затем щелкните по пункту «Настраиваемая сортировка».
После проделанных действий откроется дополнительное окно, в котором производится ввод параметров для сортировки данных таблицы.
Важно! Если требуется изменить порядок строк или столбцов только определенной части таблицы, перед выполнением ранее рассмотренных инструкций необходимо выделить нужную область данных и при запросе расширения диапазона выбрать параметр «сортировать в пределах указанного выделения».
Использование предустановленных шаблонов
Откройте окно настраиваемой сортировки и задайте предварительные параметры. Если редактируемая таблица содержит заголовки, установите отметку у строки «Мои данные содержат заголовки», которая находится в верхнем правом углу.
Затем кликните по кнопке «Параметры», расположенной рядом, и в открывшемся меню выберите тип сортировки: «строки диапазона» или «столбцы диапазона». Если обработка данных предполагает четкое соответствие регистру, поставьте галочку рядом с этим пунктом. По завершении щелкните по кнопке «ОК», чтобы применить заданные параметры.
Настраиваемая сортировка позволяет редактировать таблицу с использованием нескольких параметров одновременно. Таким образом можно обработать большой объем информации, не только выровняв в алфавитном порядке, но и применив фильтр по цветам. Всего есть четыре параметра сортировки:
- Значения ячеек. Позволяет изменить расположение данных в таблице по содержимому. По умолчанию доступно две опции — «От А до Я» и «От Я до А», но при необходимости можно настроить список, тем самым разместив данные по дням недели, месяцам и так далее.
- Цвет ячейки. Если в таблице есть цветовое выделение данных, всю информацию можно отсортировать с учетом этой особенности. Программа автоматически проверит наличие использующихся цветов и предложит выбрать нужный в соответствующем выпадающем списке.
- Цвет шрифта. Аналогичным образом можно выполнить сортировку, взяв за основу цвет используемого шрифта.
- Значок условного форматирования. Если при заполнении таблицы было применено условное форматирование, редактирование можно произвести с учетом этой особенности. Обратите внимание, что на нашем сайте есть отдельная статья, в которой демонстрируются все возможности такой опции.
Зная все параметры настраиваемой сортировки, можно перейти непосредственно к обработке данных. В последующей инструкции продемонстрирован пример с использованием всех шаблонов:
-
Если необходимо выполнить сортировку по нескольким столбцам, параметрам и порядковым данным, щелкните по кнопке «Добавить уровень», чтобы дополнить список в основной области окна еще одной строкой. Рядом располагается опция «Удалить уровень», предназначенная для очистки списка.
Примечание! При одновременном использовании нескольких уровней сортировки обработка будет происходить поочередно. Таким образом сначала применятся параметры, указанные в первой строке, затем во второй, третьей и так далее.
Имеются также некоторые дополнительные возможности настройки сортировки, которые позволяют редактировать список задаваемых параметров. В верхней части открытого окна есть кнопка «Копировать уровень», она будет полезна при необходимости добавить уже имеющуюся строку. Стрелки, расположенные правее, служат для перемещения параметров вверх или вниз, соответственно. Перед их использованием важно выделить нужную строку, кликнув по ней левой кнопкой мыши.
Когда все настройки будут завершены, нажмите по кнопке «ОК» для их применения. Но прежде рекомендуем внимательно перепроверить выбранные параметры.
Создание собственного списка
Как уже упоминалось ранее, сортировать данные в таблице можно не только в алфавитном порядке, но и по дням неделям, месяцам и так далее. Для этого необходимо в окне «Сортировка» выбрать необходимый для изменения столбец, указать параметр «Значения ячеек», а затем в выпадающем списке «Порядок» щелкнуть по пункту «Настраиваемый список».
Обратите внимание! Откроется дополнительное меню, в котором уже есть предустановленные списки, озвученные ранее. Заметьте, что каждый из них имеет разное форматирование, что следует учитывать на этапе заполнения таблицы данными.
Если для более точной сортировки необходимо использовать индивидуальный список, создать его можно самостоятельно. Для этого следует в левой рабочей области выбрать пункт «НОВЫЙ СПИСОК», после чего в поле «Элементы списка» начать вводить текст, разделяя каждую позицию новым абзацем с помощью клавиши Enter. Вписав все необходимые данные, нажмите по кнопке «Добавить», чтобы изменения зарегистрировались в программе.
После этого можно переходить в основное меню настраиваемой сортировки для изменения порядка в таблице в соответствии с только что созданным новым списком.
Способ 3: Применение функций
В случае обработки чисел для сортировки данных можно воспользоваться специальными формулами, которые доступны в Microsoft Excel по умолчанию. Они позволяют изменять порядок значений в отдельно взятом массиве, но результат будет выводиться в другом диапазоне таблицы. Стоит также отметить, что при редактировании исходных данных вычисления, произведенные с помощью формул, будут тоже корректироваться автоматически.
Сортировка посредством формул выполняется с помощью двух функций — НАИМЕНЬШИЙ и НАИБОЛЬШИЙ . Логика их работы аналогична друг другу, отличается только результат. В первом случае будет выводиться диапазон исходных данных отсортированный от меньшего числа к большему, во втором — наоборот.
Установите курсор в ячейку, с которой будет начинаться создание нового списка. Кликните по строке формул и начните вводить функцию следующего образца:
=НАИМЕНЬШИЙ(X:X;СТРОКА(X1)) или =НАИБОЛЬШИЙ(X:X;СТРОКА(X1))
Вместо X впишите букву столбца, в котором находится информация для обработки, остальную часть формулы не изменяйте. Затем нажмите по клавише Enter, чтобы произошел расчет первого наименьшего или наибольшего значения в заданном диапазоне. Чтобы продолжить расчет остальных данных, наведите курсор в нижний правый угол ячейки и протяните его вниз.
Вариант 2: Microsoft Excel 2003
Табличный процессор 2003 года имеет все вышеперечисленные функции для сортировки данных, только отличается интерфейсом и месторасположением некоторых опций.
Примечание! Инструкция по работе функций НАИМЕНЬШИЙ и НАИБОЛЬШИЙ не будет рассмотрена, так как ничем не отличается от ранее представленной в статье.
Стандартная сортировка в алфавитном порядке или от меньшего к большему производится похожим образом, но только посредством панели инструментов — в контекстном меню соответствующих опций нет. Для этого выделите столбец данных, порядок которых нужно изменить, после чего щелкните по пиктограмме «Сортировка по возрастанию» или «Сортировка по убыванию».
Настраиваемая сортировка в Microsoft Excel 2003 тоже есть, но она имеет меньший набор возможностей, позволяет только менять порядок исходных данных по возрастанию или убыванию. Чтобы этим воспользоваться, нужно выделить таблицу, раскрыть меню «Данные» и кликнуть по пункту «Сортировка».
Затем необходимо настроить последовательность сортировки. Для этого в каждом выпадающем списке выберите столбец, который будет изменяться, а следом установите параметр «по возрастанию» или «по убыванию». Щелкните по кнопке «ОК», чтобы применить заданные настройки.
Также можно задать дополнительные параметры сортировки, нажав по кнопке «Параметры». В открывшемся окне из выпадающего списка «Сортировка по первому ключу» выберите подходящее значение, после чего при необходимости установите отметку «Учитывать регистр».
Примечание! В отличие от Microsoft Excel 2007 – 2021, добавлять собственные списки ключей в программе 2003 года нельзя, поэтому доступны лишь предустановленные — дни недели и месяцы.
Excel SORT function — automatically sort with formula
by Svetlana Cheusheva , updated on March 14, 2023
The tutorial shows how to use the SORT function to sort data arrays dynamically. You will learn a formula to sort alphabetically in Excel, arrange numbers in ascending or descending order, sort by multiple columns, and more.
The Sort functionality has been around for a long time. But with the introduction of dynamic arrays in Excel 365, there appeared an amazingly simple way to sort with formulas. The beauty of this method is that the results update automatically when the source data changes.
Excel SORT function
The SORT function in Excel sorts the contents of an array or range by columns or rows, in ascending or descending order.
SORT belongs to the group of Dynamic array functions. The result is a dynamic array that automatically spills to neighboring cells vertically or horizontally, depending on the shape of the source array.
The syntax of the SORT function is as follows:
Array (required) — is an array of values or a range of cells to sort. These can be any values including text, numbers, dates, times, etc.
Sort_index (optional) — an integer that indicates which column or row to sort by. If omitted, the default index 1 is used.
Sort_order (optional) — defines the sort order:
- 1 or omitted (default) — ascending order, i.e. from smallest to largest
- -1 — descending order, i.e. from largest to smallest
By_col (optional) — a logical value that indicates the direction of sorting:
- FALSE or omitted (default) — sort by row. You’ll use this option most of the time.
- TRUE — sort by column. Use this option if your data is organized horizontally in columns like in this example.
Excel SORT function — tips and notes
SORT is a new dynamic array function and as such it has a couple of specificities that you should be aware of:
- Currently the SORT function is only available in Microsoft 365 and Excel 2021. Excel 2019, Excel 2016 do not support dynamic array formulas, so the SORT function is not available in these versions.
- If the array returned by a SORT formula is the final result (i.e. not passed to another function), Excel dynamically creates an appropriately sized range and populates it with the sorted values. So, be sure you always have enough empty cells down or/and to the right of the cell where you enter the formula, otherwise a #SPILL error occurs.
- The results update dynamically as the source data changes. However, the array supplied to the formula does not extend automatically to include new entries that are added outside of the referenced array. To include such items, you need to either update the array reference in your formula, or convert the source range to an table as shown in this example, or create a dynamic named range.
Basic Excel SORT formula
This example shows a basic formula for sorting data in Excel in ascending and descending order.
Supposing your data is arranged alphabetically as shown in the screenshot below. You are looking to sort numbers in column B without breaking or mixing data.
Formula to sort in ascending order
To sort values in column B from smallest to largest, here’s the formula to use:
- A2:B8 is the source array
- 2 is the column number to sort by
- 1 is the ascending sort order
Since our data is organized in rows, the last argument can be omitted to default to FALSE — sort by rows.
Just enter the formula in any empty cell (D2 in our case), press Enter , and the results will spill automatically to D2:E8.
Formula to sort in descending order
To sort data descending, i.e. from largest to smallest, set the sort_order argument to -1 like this:
Enter the formula in the top left cell of the destination range and you will get this result:
In a similar manner, you can sort text values in alphabetical order from A to Z or from Z to A.
How to sort data in Excel using formula
The below examples show a few typical uses of the SORT function in Excel and a couple of non-trivial ones.
Excel SORT by column
When you sort data in Excel, for the most part you change the order of rows. But when your data is organized horizontally with rows containing labels and columns containing records, you might need to sort from left to right, rather than from top to bottom.
To sort by column in Excel, set the by_col argument to TRUE. In this case, sort_index will represent a row, not a column.
For example, to sort the below data by Qty. from highest to lowest, use this formula:
=SORT(B1:H2, 2, 1, TRUE)
- B1:H2 is the source data to sort
- 2 is the sort index, since we are sorting numbers in the second row
- -1 indicates the descending sort order
- TRUE means to sort columns, not rows
Sort by multiple columns in different order (multi-level sort)
When working with complex data models, you may often need a multi-level sort. Can that be done with a formula? Yep, easily! What you do is to supply array constants for the sort_index and sort_order arguments.
For example, to sort the below data first by Region (column A) from A to Z, and then by Qty. (column C) from smallest to largest, set the following arguments:
- Array is the data in A2:C13.
- Sort_index is the array constant <1,3>, since we first sort by Region (1 st column), and then by Qty. (3 rd column).
- Sort_order is the array constant <1,-1>, since the 1st column is to be sorted in ascending order and the 3 rd column in descending order.
- By_col is omitted because we sort rows, which is default.
Putting the arguments together, we get this formula:
And it works perfectly! The text values in the first column are sorted alphabetically and the numbers in the third column from largest to smallest:
Sort and filter in Excel
In case when you are looking to filter data with some criteria and put the output in order, use the SORT and FILTER functions together:
The FILTER function gets an array of values based on the criteria you define and passes that array to the first argument of SORT.
The best thing about this formula is that it also outputs the results as a dynamic spill range, without you having to press Ctrl + Shift + Enter or guess at how many cells to copy it to. As usual, you type a formula in the upper most cell and hit the Enter key.
As an example, we are going to extract items with quantity equal to or greater than 30 (>=30) from the source data in A2:B9 and arrange the results in ascending order.
For this, we first set up the condition, say, in cell E2 as shown in the image below. And then, build our Excel SORT formula in this way:
=SORT(FILTER(A2:B9, B2:B9>=E2), 2)
Apart from array generated by the FILTER function, we only specify the sort_index argument (column 2). The remaining two arguments are omitted because the defaults work exactly as we need (sort ascending, by row).
Get N largest or smallest values and sort the results
When analyzing huge bulks if information, there is often a need to extract a certain number of top values. Maybe not just extract, but also arrange them in the desired order. And ideally, choose which columns to include in the results. Sounds tricky? Not with the new dynamic array functions!
Here is a generic formula:
Where n is the number of the values you want to return.
From the below data set, assume you want to get a top 3 list based on the numbers in column C.
To have it done, you first sort the array A2:C13 by the 3 rd column in descending order:
And then, nest the above formula in the first (array) argument of the INDEX function to have the array sorted from highest to smallest.
For the second (row_num) argument, which indicates how many rows to return, generate the required sequential numbers by using the SEQUENCE function. As we need 3 top values, we use SEQUENCE(3), which is the same as supplying a vertical array constant <1;2;3>directly in the formula.
For the third (col_num) argument, which defines how many columns to return, supply the column numbers in the form of a horizontal array constant. We want to return columns B and C, so we use the array <2,3>.
Eventually, we get the following formula:
=INDEX(SORT(A2:C13, 3, -1), SEQUENCE(3), <2,3>)
And it produces exactly the results we want:
To return 3 bottom values, simply sort the original data from smallest to largest. For this, change the sort_order argument from -1 to 1:
=INDEX(SORT(A2:C13, 3, 1), SEQUENCE(3), <2,3>)
Return a sorted value in a specific position
Looking from another angle, what if you only want to return a specific sort position? Say, only the 1st , only the 2nd, or only the 3rd record from the sorted list? To have it done, use the simplified version of the INDEX SORT formula discussed above:
Where n is the position of interest.
For example, to get a particular position from top (i.e. from the data sorted descending), use this formula:
=INDEX(SORT(A2:C13, 3, -1), F1, <2,3>)
To get a specific position from bottom (i.e. from the data sorted ascending), use this one:
=INDEX(SORT(A2:C13, 3, 1), I1, <2,3>)
Where A2:C13 is source data, F1 is the position from top, I1 is the position from bottom, and <2,3>are the columns to be returned.
Use Excel table to get sort array to expand automatically
As you already know, the sorted array updates automatically when you make any changes to the original data. This is the standard behavior of all dynamic array functions, including SORT. However, when you add new entries outside the referenced array, they are not automatically included in a formula. If you’d like your formula to respond to such changes, convert the source range to a fully-functional Excel table and use structured references in your formula.
To see how it works in practice, please consider the following example.
Supposing you use the below Excel SORT formula to arrange values in the range A2:B8 in alphabetical order:
Then, you input a new entry in row 9… and are disappointed to see that the newly added entry is left out of the spill range:
Now, convert the source range to a table. For this, simply select your range including the column headers (A1:B8) and press Ctrl + T . When building your formula, select the source range using the mouse, and the table name will be inserted in the formula automatically (this is called a structured reference):
When you type a new entry right below the last row, the table will expand automatically, and the new data will be included in the spill range of the SORT formula:
Excel SORT function not working
If your SORT formula results in an error, it’s most likely because of the following reasons.
#NAME error: older Excel version
SORT is a new function and works only in Excel 365 and Excel 2021. In older versions where this function is not supported, a #NAME? error occurs.
#SPILL error: something blocks spill range
If one or more cells in the spill range are not completely blank or merged, a #SPILL! error is displayed. To fix it, just remove the blockage. For more information, please see Excel #SPILL! error — what it means and how to fix.
#VALUE error: invalid arguments
Whenever you run into a #VALUE! error, check the sort_index and sort_order arguments. Sort_index should not exceed the number of columns is array, and sort_order should be either 1 (ascending) or -1 (descending).
#REF error: source workbook is closed
Since dynamic arrays have limited support for references between workbooks, the SORT function requires both files to be open. If the source workbook is closed, a formula will throw a #REF! error. To fix it, just open the referenced file.
That’s how to sort data in Excel using formula. I thank you for reading and hope to see you on our blog next week!