Перед тем как осуществлять упорядочивание (сортировку) чисел в Эксель, нужно убедиться, что все они записаны в нужном формате. Иначе результат может получиться либо некорректным, либо будет недоступна команда, позволяющая совершить упорядочивание.

Форматы, которые позволяют совершать упорядочивание по возрастанию и убыванию: общий, числовой, финансовый, денежный.

Проверить формат ячеек можно так: щёлкнуть на нужном диапазоне правой кнопкой мыши и в появившемся контекстном меню выбрать команду "Формат ячеек".

Первый способ упорядочить числа по возрастанию в Excel

Исходная таблица содержит: ФИО сотрудника, его должность и стаж.

Требуется сделать упорядочивание данных согласно стажу работы - от меньшего к большему.

Для этого нужно выделить диапазон чисел, которые нужно упорядочить. В нашем случае это будет диапазон D3:D8.

Может быть выдано предупреждение о наличии данных рядом с указанным диапазоном. Среди предлагаемых действий нужно выбрать "Сортировать в пределах указанного выделения" и нажать на кнопку "Сортировка".

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

Второй способ упорядочить числа по возрастанию в Excel

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

Затем на панели инструментов в разделе "Главная" нажмите на кнопку "Сортировка и фильтр". Появится подменю, в котором нужно выбрать команду "Сортировка от минимального к максимальному".

Эта команда позволит вам осуществить упорядочивание чисел по возрастанию.

Для упорядочения и анализа данных в списке применяется команда "Сортировка". "Сортировка" позволяет переставлять записи в определенном порядке на основании значений одного или нескольких столбцов или переставлять столбцы на основании значений в строках.

Существуют три типа сортировки:

• в возрастающем порядке
• в убывающем порядке
• в пользовательском порядке

Сортировка списка по возрастанию означает упорядочение списка в порядке: от 0 до 9, пробелы, символы, буквы от А до Z или от А до Я, а по убыванию - в обратном порядке. Пользовательский порядок сортировки задается пользователем в окне диалога "Параметры" на вкладке "Списки", которое открывается командой "Параметры" в меню "Сервис", а отображается этот порядок сортировки в окне диалога "Параметры сортировки" (Рис. 1).

Рис. 1.

Для отображения окон диалога "Сортировка диапазона", "Параметры сортировки" и выбора порядка сортировки столбцов или строк необходимо выделить ячейку списка и выполнить команду "Данные" - "Сортировка". На экране появится окно "Сортировка диапазона" при нажатии в нем кнопки "Параметры" появится окно "Параметры сортировки".

В окне "Параметры сортировки" (Рис. 2) из списка "Сортировка по первичному ключу" можно выбрать пользовательский порядок сортировки, а также установить переключатель "Сортировать" в положение: "строки диапазона" или "столбцы диапазона".

Рис. 2.

В окне "Сортировка диапазона" (Рис. 3) можно установить переключатель в положение: "по возрастанию" или "по убыванию", а также выбрать положение переключателя идентификации диапазона данных.

Рис. 3.

Если подписи отформатированы в соответствии с вышеизложенными требованиями, то переключатель по умолчанию устанавливается в положение "подписям". Кроме того, в списках: "Сортировать по", "Затем по" и "В последнюю очередь, по" можно выбрать заголовки столбцов, по которым осуществляется сортировка. Таким образом, сортировку записей можно осуществлять по одному, двум или трем столбцам.

Мгновенная сортировка записей по одному столбцу осуществляется при выделении в нем ячейки нажатием кнопки "Сортировка по возрастанию" или "Сортировка по убыванию" на стандартной панели инструментов. В результате редактор переставит записи в соответствии с выбранным порядком.

Алгоритм сортировки записей по одному столбцу следующий

• Выделите ячейку в списке, который требуется отсортировать;
• Выполните команду "Данные" - "Сортировка", открывается окно диалога "Сортировка диапазона";
• В списке "Сортировать по" выберите заголовок того столбца, по которому будете осуществлять сортировку;
• Выберите тип сортировки "По возрастанию" или "По убыванию";

На рисунках 4 и 5 представлены фрагменты списка до сортировки, и после сортировки "по возрастанию" по одному столбцу "№ склада".

Рис. 4.

Рис. 5.

При сортировке списка по нескольким столбцам, строки с одинаковыми значениями в первом сортируемом столбце ("Сортировать по"), сортируются в порядке, который определяется заголовком, указанным в списке "Затем по", а строки с одинаковыми значениями в первых двух сортируемых столбцах сортируются по заголовку, указанному в списке "В последнюю очередь, по". Другими словами столбцы, указанные в списках "Затем по" и "В последнюю очередь по" не влияют на порядок сортировки в первом сортируемом столбце, а устраняют неоднозначность в отсортированном списке.

Алгоритм сортировки записей по двум или более столбцам следующий

• Выделите ячейку в списке;
• Выберите заголовок для сортировки в списке "Сортировать по" и установите порядок сортировку "по возрастанию" или "по убыванию";
• Откройте список "Затем по", установите заголовок другого столбца для сортировки и задайте сортировку "по возрастанию" или "по убыванию";
• Раскройте список "В последнюю очередь по" и выберите заголовок третьего столбца для сортировки и укажите сортировку "по возрастанию" или "по убыванию";
• Нажмите кнопку ОК для выполнения сортировки.

Алгоритм сортировки данных по строкам

Для перестановки столбцов в списке необходимо выполнить сортировку на основе значений в строках:

• Укажите ячейку в сортируемом списке;
• В меню "Данные" выберите команду "Сортировка";
• В окне "Сортировка диапазона" нажмите кнопку "Параметры";
• Установите переключатель "Сортировать" в положение "столбцы диапазона" и нажмите кнопку OK;
• В окне "Сортировка диапазона" выберите строки, по которым требуется отсортировать столбцы в списках "Сортировать по", "Затем по", "В последнюю очередь, по".
• Нажмите кнопку ОК для выполнения сортировки

В некоторых случаях требуется нестандартный порядок сортировки списков, например сортировка данных по месяцам в календарном порядке или сортировка записей в порядке, созданном пользователем. Создание пользовательского порядка осуществляется в окне "Параметры" на вкладке "Списки", которое можно вызвать командой "Сервис" - "Параметры". Пользовательский порядок сортировки можно применить только для заголовка столбца, указанного в раскрывающем списке "Сортировать по"

Алгоритм сортировки в пользовательском порядке

• Укажите ячейку в списке;
• В меню "Данные" выберите команду "Сортировка";
• Выберите в списке "Сортировать по" заголовок столбца для сортировки;
• Установите переключатель в положение "по возрастанию" или "по убыванию";
• Нажмите кнопку "Параметры";
• В раскрывающем списке "Порядок сортировки по первому ключу" выберите необходимый порядок сортировки и нажмите кнопку OK;
• Нажмите кнопку ОК в окне "Сортировка диапазона" для завершения сортировки.

В этой статье я покажу Вам, как в Excel выполнить сортировку данных по нескольким столбцам, по заголовкам столбцов в алфавитном порядке и по значениям в любой строке. Вы также научитесь осуществлять сортировку данных нестандартными способами, когда сортировка в алфавитном порядке или по значению чисел не применима.

Думаю, всем известно, как выполнить сортировку по столбцу в алфавитном порядке или по возрастанию / убыванию. Это делается одним нажатием кнопки А-Я (A-Z) и Я-А (Z-A) в разделе Редактирование (Editing) на вкладке Главная (Home) либо в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data):

Однако, сортировка в Excel имеет гораздо больше настраиваемых параметров и режимов работы, которые не так очевидны, но могут оказаться очень удобны:

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

Я покажу Вам, как в Excel сортировать данные по двум или более столбцам. Работа инструмента показана на примере Excel 2010 – именно эта версия установлена на моём компьютере. Если Вы работаете в другой версии приложения, никаких затруднений возникнуть не должно, поскольку сортировка в Excel 2007 и Excel 2013 работает практически так же. Разницу можно заметить только в расцветке диалоговых окон и форме кнопок. Итак, приступим…

Сортировать данные по нескольким столбцам в Excel оказалось совсем не сложно, правда? Однако, в диалоговом окне Сортировка (Sort) кроется значительно больше возможностей. Далее в этой статье я покажу, как сортировать по строке, а не по столбцу, и как упорядочить данные на листе в алфавитном порядке по заголовкам столбцов. Вы также научитесь выполнять сортировку данных нестандартными способами, когда сортировка в алфавитном порядке или по значению чисел не применима.

Сортировка данных в Excel по заголовкам строк и столбцов

Я полагаю, что в 90% случаев сортировка данных в Excel выполняется по значению в одном или нескольких столбцах. Однако, иногда встречаются не такие простые наборы данных, которые нужно упорядочить по строке (горизонтально), то есть изменить порядок столбцов слева направо, основываясь на заголовках столбцов или на значениях в определённой строке.

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

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

В результате сортировки у Вас должно получиться что-то вроде этого:

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

Наша задача – повторить описанные выше шаги 1 – 3. Затем на шаге 4 вместо строки 1 выбираем строку 4 , в которой указаны розничные цены (Retail Price). В результате сортировки таблица будет выглядеть вот так:

Обратите внимание, что отсортированы оказались данные не только в выбранной строке. Целые столбцы меняются местами, но данные не перемешиваются. Другими словами, на снимке экрана выше представлен список фотокамер, расставленный в порядке от самых дешёвых до самых дорогих.

Надеюсь, теперь стало ясно, как работает сортировка по строке в Excel. Но что если наши данные должны быть упорядочены не по алфавиту и не по возрастанию / убыванию?

Сортировка в произвольном порядке (по настраиваемому списку)

Если нужно упорядочить данные в каком-то особом порядке (не по алфавиту), то можно воспользоваться встроенными в Excel настраиваемыми списками или создать свой собственный. При помощи встроенных настраиваемых списков Вы можете сортировать, к примеру, дни недели или месяцы в году. Microsoft Excel предлагает два типа таких готовых списков – с сокращёнными и с полными названиями.

Предположим, у нас есть список еженедельных дел по дому, и мы хотим упорядочить их по дню недели или по важности.

Готово! Теперь домашние дела упорядочены по дням недели:

Замечание: Если Вы планируете вносить изменения в эти данные, помните о том, что добавленные новые или изменённые существующие данные не будут отсортированы автоматически. Чтобы повторить сортировку, нажмите кнопку Повторить (Reapply) в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data).

Как видите, сортировка данных в Excel по настраиваемому списку – задача вовсе не сложная. Ещё один приём, которому мы должны научиться – сортировка данных по собственному настраиваемому списку.

Сортировка данных по собственному настраиваемому списку

В нашей таблице есть столбец Priority – в нём указаны приоритеты задач. Чтобы упорядочить с его помощью еженедельные задачи от более важных к менее важным, выполним следующие действия.

Повторите шаги 1 и 2 из предыдущего примера. Когда откроется диалоговое окно Списки (Custom Lists), в одноимённом столбце слева нажмите НОВЫЙ СПИСОК (NEW LIST) и заполните нужными значениями поле Элементы списка (List entries). Внимательно введите элементы Вашего списка именно в том порядке, в котором они должны быть расположены в результате сортировки.

Нажмите Добавить (Add), и созданный Вами список будет добавлен к уже существующим. Далее нажмите ОК .

Вот так выглядит наш список домашних дел, упорядоченных по важности:

Отсортируем формулами таблицу, состоящую из 2-х столбцов. Сортировку будем производить по одному из столбцов таблицы (решим 2 задачи: сортировка таблицы по числовому и сортировка по текстовому столбцу). Формулы сортировки настроим так, чтобы при добавлении новых данных в исходную таблицу, сортированная таблица изменялась динамически. Это позволит всегда иметь отсортированную таблицу без вмешательства пользователя. Также сделаем двухуровневую сортировку: сначала по числовому, затем (для повторяющихся чисел) - по текстовому столбцу.

Пусть имеется таблица, состоящая из 2-х столбцов. Один столбец – текстовый: Список фруктов ; а второй - числовой Объем Продаж (см. файл примера ).

Задача1 (Сортировка таблицы по числовому столбцу)

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

Для наглядности величины значений в столбце Объем Продаж выделены с помощью (). Также желтым выделены повторяющиеся значения.

Примечание : Задача сортировки отдельного столбца (списка) решена в статьях и .

Решение1

Если числовой столбец гарантировано не содержит значений, то задача решается легко:

• Числовой столбец отсортировать функцией НАИБОЛЬШИЙ() (см. статью );
• Функцией ВПР() или связкой функций ИНДЕКС()+ПОИСКПОЗ() выбрать значения из текстового столбца по соответствующему ему числовому значению.

Однако, в реальных задачах числовой столбец может содержать повторы, а так как функция ВПР() в случае наличия повторов всегда выбирает только первое значение сверху (см. статью ), то этот подход не годится (названия Фруктов будут выведены неправильно).

Поэтому механизм сортировки придется реализовывать по другому.

ИНДЕКС(Продажи;
ОКРУГЛ(ОСТАТ(НАИБОЛЬШИЙ(
--(СЧЁТЕСЛИ(Продажи;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
СТРОКА()-СТРОКА($E$6));1)*1000;0)
)

Данная формула сортирует столбец Объем продаж (динамический диапазон Продажи ) по убыванию. Пропуски в исходной таблице не допускаются. Количество строк в исходной таблице должно быть меньше 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. В ячейку А15 исходной таблицы введите слово Морковь ;
2. В ячейку В15 введите Объем продаж Моркови = 25;
3. После ввода значений, в столбцах D и Е автоматически будет отображена отсортированная по убыванию таблица;
4. В сортированной таблице новая строка будет отображена предпоследней.

Скорость вычислений формул

На "среднем" по производительности компьютере пересчет пары таких формул массива, расположенных в 100 строках, практически не заметен. Для таблиц с 300 строками время пересчета занимает 2-3 секунды, что вызывает неудобства. Либо необходимо отключить автоматический пересчет листа (Формулы/ Вычисления/ Параметры вычисления ) и периодически нажимать клавишу F9 , либо отказаться от использования формул массива, заменив их столбцами с соответствующими формулами, либо вообще отказаться от динамической сортировки в пользу использования стандартных подходов (см. следующий раздел).

Альтернативные подходы к сортировке таблиц

Отсортируем строки исходной таблицы с помощью стандартного фильтра (выделите заголовки исходной таблицы и нажмите CTRL+SHIFT+L ). В выпадающем списке выберите требуемую сортировку.

Получим идентичный нашему вариант таблицы, но при добавлении в таблицу новых значений придется применять фильтр заново.

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

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

Как и в предыдущей задаче предположим, что в столбце, по которому ведется сортировка имеются повторы (названия Фруктов повторяются).

Для сортировки таблицы придется создать 2 служебных столбца (D и E).

=СЧЁТЕСЛИ($B$7:$B$14;"<"&$B$7:$B$14)+1

Эта формула является аналогом для текстовых значений (позиция значения относительно других значений списка). Текстовому значению, расположенному ниже по алфавиту, соответствует больший "ранг". Например, значению Яблоки соответствует максимальный "ранг" 7 (с учетом повторов).

В столбце E введем обычную формулу:

=СЧЁТЕСЛИ($D$6:D6;D7)+D7

Эта формула учитывает повторы текстовых значений и корректирует "ранг". Теперь разным значениям Яблоки соответствуют разные "ранги" - 7 и 8. Это позволяет вывести список сортированных значений. Для этого используйте формулу (столбец G):

=ИНДЕКС($B$7:$B$14;ПОИСКПОЗ(СТРОКА()-СТРОКА($G$6);$E$7:$E$14;0))

Аналогичная формула выведет соответствующий объем продаж (столбец Н).

Задача 2.1 (Двухуровневая сортировка)

Теперь снова отсортируем исходную таблицу по Объему продаж. Но теперь для повторяющихся значений (в столбце А три значения 74), соответствующие значения выведем в алфавитном порядке.

Для этого воспользуемся результатами Задачи 1.1 и Задачи 2.

Подробности в файле примера на листе Задача2.

Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» или «от Я до А»). Если данные числовые, их можно отсортировать в порядке возрастания или убывания. Если в диапазоне данных есть строка или столбец, в которых содержатся данные типа время или дата, их можно отсортировать в прямом или обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.

Сортировать данные можно по одному условию (например, сортировка списка сотрудников по фамилии) или нескольким (например, сортировка списка сотрудников по занимаемой должности, а внутри каждой должности фамилии отсортировать в алфавитном порядке). Данные можно сортировать по столбцу (или нескольким столбцам) или по строке.

Сортировка по одному критерию

Пошаговые действия:

1. В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
2. На вкладке Данные найти группу команд .

Отметим, что буквы на этой кнопке указывают только на направление сортировки, а вид кнопки остается один и тот же и при текстовых, и при числовых данных.

Существует и другой удобный способ сортировки данных: щелкнув правой кнопкой мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка , а далее - требуемый вариант сортировки.

Пошаговые действия:

1. Выделить одну ячейку из сортируемого массива данных.

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

1. На вкладке Данные найти группу команд и на ней выбрать команду Сортировка .
2. Последовательно задать уровни сортировки (определяемые именем столбца).

Нажимая на стрелку возле трех полей (Столбец , Сортировка , Порядок ) необходимо выбрать:

1. Имя столбца для сортировки.
2. Тип критерия (в зависимости от того, будет ли вестись сортировка по значениям данных в столбце, или по оформлению ячейки, или по значку ячейки).
3. Порядок сортировки (по убыванию или по возрастанию).

Если выбранный для сортировки столбец содержит названия месяцев или дней недели, то в списке поля Порядок можно выбрать опцию Настраиваемый список и в новом окне отметить один из предлагаемых вариантов сортировки.

Сортировка по форматированию

Часто для анализа данных делается заливка ячеек (или шрифта) цветом. С помощью сортировки можно также упорядочивать данные на основе их форматирования.

Пошаговый порядок действий:

1. Щелкнуть по любой ячейки из столбца, по которому будет выполняться сортировка.
2. На вкладке Данные выбрать группу , а затем выбрать команду Сортировка .
3. В поле Столбец укажите столбец по которому будет проводиться сортировка.
4. В поле Сортировка из всплывающего меню выбрать критерий сортировки: цвет ячейки , цвет шрифта или значок ячейки .
5. Поле Порядок содержит два выпадающих списка. В первом нужно выбрать тип критерия, а во втором - размещение ячеек, отсортированных по данному критерию (строку Сверху или Снизу ).
6. При необходимости добавить еще один критерий сортировки, в окне Сортировка нужно выбрать кнопку Добавить уровень .

Можно также воспользоваться командой «Копировать уровень» , заменив в поле «Порядок» прежнее значение на новое.

1. После выбора сортировки нажать кнопку ОК .