Сортировка в Excel: сортировка данных по тексту, дате, числу или цвету

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

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

Доступ к параметрам сортировки в Excel

Excel предлагает несколько удобных способов доступа к функции сортировки и её настройкам.

1,1 Кнопки сортировки на вкладке Лента

Самый быстрый способ применить сортировку — воспользоваться кнопками на вкладке «Лента».

Щёлкните по любой ячейке в столбце со значениями, которые нужно отсортировать, перейдите на вкладку Данные, в разделе Сортировка и фильтр нажмите кнопку По возрастанию или Сортировка от Я до А, чтобы отсортировать данные в алфавитном порядке по возрастанию или убыванию. См. скриншот:

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

1,2 Диалоговое окно сортировки

В группе Сортировка и фильтр на вкладке Данные расположена ещё одна кнопка Сортировка. См. скриншот:

После нажатия этой кнопки Сортировкапоявится диалоговое окно Сортировка, как показано на скриншоте ниже:

Затем в диалоговом окне Сортировка вы можете выбрать нужные правила сортировки данных.

Совет: вы также можете открыть это диалоговое окно Сортировка с помощью сочетания клавиш. Для этого последовательно нажмите на клавиатуре клавиши Alt + A + S + S.

1,3 Параметры сортировки в меню фильтрации

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

Примечание: Параметры сортировки зависят от типа данных в столбце: если в столбце содержится текст, отображаются команды По возрастанию и Сортировка от Я до А; если в столбце находятся числа — команды Сортировка от наименьшего к наибольшему и Сортировка от наибольшего к наименьшему; если в столбце содержатся даты — команды Сортировка от старых к новым и Сортировка от новых к старым.

1,4 Параметры сортировки через контекстное меню

В Excel можно быстро и удобно сортировать данные с помощью параметров в контекстном меню: просто щёлкните правой кнопкой мыши любую ячейку в столбце со значениями, которые нужно отсортировать, — и вы увидите шесть доступных вариантов сортировки. Пожалуйста, выберите нужный вам (см. снимок экрана):

Основные способы использования сортировки

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

2,1 Сортировка данных по тексту, числам или датам

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

1. Выделите диапазон данных, который нужно отсортировать, затем нажмите Данные > Сортировка. См. снимок экрана:

2. В диалоговом окне Сортировка выполните следующие действия:

• В разделе Столбецвыберите имя столбца, по которому требуется выполнить сортировку;
• В разделе Сортировать по выберите Значения ячеек.
• В разделе Порядокукажите порядок сортировки. (Чтобы отсортировать текстовые строки, выберите)от А до Я или от Я до А; чтобы отсортировать числовой список, выберите От наименьшего к наибольшему или От наибольшего к наименьшему; чтобы отсортировать ячейки с датами, выберите от старых к новым или от новых к старым.)
• Совет: Если ваши данные содержат заголовки, обязательно установите флажок параметра Включить заголовки. Если заголовков нет — снимите этот флажок.

3. Затем нажмите кнопку ОК, и данные в окне «Выбрать» будут немедленно отсортированы по указанному вами столбцу.

2,2 Сортировка данных по цвету ячеек, Цвет шрифта или значку ячейки

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

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

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

2. В диалоговом окне Сортировка укажите следующие параметры:

2,1) Установите первый цвет ячейки вверху следующим образом:

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

2,2) Затем нажмите кнопку Добавить условия, чтобы добавить второй и последующие уровни правил; повторите описанные выше шаги для настройки формата ячеек второго и последующих цветов.

• Совет: Если ваши данные содержат заголовки, убедитесь, что установлен флажок параметра Включить заголовки. Если заголовков нет, снимите этот флажок.

3. После завершения настройки нажмите кнопку ОК, и диапазон данных будет отсортирован по указанному вами цвету ячеек (см. снимок экрана):

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

2,3 Сортировка данных по нескольким столбцам

Если у вас большой набор данных, как на снимке экрана ниже, и вы хотите выполнить многоуровневую сортировку для удобного просмотра — например, сначала по столбцу «Регион», затем по «Области» и, наконец, по «Продажам», — как это сделать в Excel?

Чтобы отсортировать данные по нескольким столбцам, выполните следующие действия:

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

2. В диалоговом окне Сортировка нажмите кнопку Добавить условия дважды, так как для сортировки используются три столбца. После этого в списке появятся два уровня правил Затем по:

Совет: если ваши данные содержат заголовки, обязательно установите флажок Включить заголовки. Если заголовков нет — снимите этот флажок.

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

4. Затем нажмите кнопку ОК, и вы увидите результат сортировки, как показано на снимке экрана ниже:

2,4 Сортировка данных на основе Пользовательские списки

Помимо сортировки данных в алфавитном или числовом порядке, эта функция поддерживает и сортировку по пользовательским спискам. Например, вы можете отсортировать приведённый ниже диапазон данных по статусу: «Не начато», «В процессе», «Завершено» — именно в том порядке, который вы задали. Ниже описано, как выполнить такую сортировку.

1. Выделите Диапазон данных, который необходимо отсортировать, затем нажмите Данные>Сортировка, чтобы открыть диалоговое окно Сортировка.

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

Советы: Если ваши данные содержат заголовки, убедитесь, что установлен флажок Включить заголовкиЕсли заголовков нет, снимите этот флажок.

3. Откроется диалоговое окно Пользовательский список, в котором выполните следующие действия:

• Нажмите кнопку НОВЫЙ СПИСОКв поле списков Пользовательские;
• Введите текстовые строки в том порядке, в котором вы хотите их сортировать, в поле Элементы списка; (при вводе текста нажимайте клавишу)Enter, чтобы разделять элементы.)
• Наконец, нажмите кнопку Добавить, и новый список сразу же появится в поле Пользовательский список.

4. Затем нажмите кнопку ОК, чтобы вернуться в диалоговое окно Сортировка. Новый элемент «Пользовательские списки» теперь отображается в поле Порядок раскрывающегося списка.

5. После этого нажмите кнопку ОК, чтобы закрыть диалоговое окно. В результате диапазон данных будет отсортирован по пользовательскому списку, который вы задали (см. снимок экрана):

2,5 Сортировка данных Слева направо (горизонтальная сортировка)

Обычно данные в таблице сортируются вертикально — сверху вниз, но иногда бывает нужно отсортировать их по значениям в строке (слева направо). Например, в приведённом ниже диапазоне данных я хочу выполнить сортировку по значениям строки «Имя».

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

1. Выделите диапазон данных (без заголовков), который нужно отсортировать, и нажмите Данные > Сортировка, чтобы открыть диалоговое окно сортировки.

2. В открывшемся диалоговом окне Сортировка нажмите кнопку Параметры, затем в диалоговом окне Параметры сортировки выберите опцию Сортировать слева направо, см. снимок экрана:

3. Щелкните OK, чтобы вернуться в диалоговое окно «Сортировка». В разделе Строка укажите номер строки, на основе которой необходимо выполнить сортировку данных, затем выберите Значения ячеек в разделе Сортировать по и, наконец, задайте нужный порядок сортировки в разделе Порядок. См. снимок экрана:

4. Затем нажмите OK, и ваши данные будут отсортированы, как показано на снимке экрана ниже:

2,6 Сортировка данных с учетом регистра

Как правило, сортировка данных в Excel выполняется без учета регистра, как показано на первом снимке экрана ниже. Но что делать, если вам нужно отсортировать данные с учетом регистра — как на втором снимке экрана? Как этого добиться в Excel?

Чтобы выполнить сортировку С учетом регистра в Excel, сделайте следующее:

1. Выделите диапазон данных, который необходимо отсортировать, затем щелкните Данные > Сортировка, чтобы открыть диалоговое окно Сортировка.

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

Совет: Если ваши данные содержат заголовки, обязательно установите флажок Включить заголовки. Если заголовков нет — снимите его.

3. В этом диалоговом окне нажмите кнопку Параметры, а затем в окне Параметры сортировки установите флажок С учетом регистра. См. снимок экрана:

4. Нажмите OK > OK, чтобы закрыть диалоговые окна. Данные будут отсортированы с учётом регистра: сначала строки в нижнем регистре, затем — в верхнем, если текст совпадает. См. снимок экрана:

Расширенные способы сортировки

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

3,1 Сортировка данных по частоте встречаемости

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

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

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

1. Введите приведённую ниже формулу в пустую ячейку рядом с исходными данными (например, в B2), затем перетащите маркер заполнения вниз, чтобы применить её ко всем остальным ячейкам. См. снимок экрана:

=COUNTIF($A$2:$A$16,A2)

Примечание: В приведённой выше формуле A2:A16 — это диапазон данных, которые нужно отсортировать по частоте, а A2 — первое значение в этом списке.

2. Затем выделите ячейки с формулой и щелкните Данные > По возрастанию или Сортировка от Я к А — в зависимости от ваших задач. В появившемся окне Предупреждение о сортировке выберите Расширить выделенный диапазон. См. снимки экрана:

3. Затем нажмите кнопку Сортировка, и исходный столбец будет отсортирован по частоте, как показано на снимках экрана:

Сортировка данных по частоте с помощью удобной функции

Если у вас установлен Kutools для Excel, вы можете быстро и легко отсортировать данные по частоте встречаемости с помощью его функции Расширенная сортировка — без использования вспомогательного столбца.

1. Выделите список данных, который нужно отсортировать, затем нажмите KUTOOLS PLUS > Сортировка > Расширенная сортировка. См. снимок экрана:

2. В диалоговом окне Расширенная сортировка выберите столбец для сортировки в разделе Столбец, затем укажите Частота в разделе Сортировать по в раскрывающемся списке и, наконец, задайте порядок сортировки в разделе Порядок. См. снимок экрана:

Совет: Если ваши данные содержат заголовки, убедитесь, что установлен флажок Включить заголовкиЕсли заголовков нет, снимите этот флажок.

3. Затем нажмите кнопку OK, и список данных будет немедленно отсортирован по количеству вхождений. См. снимок экрана:

3,2 Сортировка данных по длине строки

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

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

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

1. Введите формулу =LEN(A2) в пустую ячейку рядом с исходными данными (например, в B2), а затем перетащите маркер заполнения вниз, чтобы применить её ко всем остальным ячейкам. См. снимок экрана:

2. Затем выделите ячейки с формулой и щелкните Данные > По возрастанию или Сортировка от Я к А — в зависимости от ваших задач. В появившемся окне Предупреждение о сортировке выберите Расширить выделенный диапазон. См. снимок экрана:

3. Затем нажмите кнопку Сортировка, и строки будут отсортированы по длине. При необходимости вы можете удалить вспомогательный столбец B. См. снимок экрана:

Сортировка данных по длине символов с помощью простого параметра

Чтобы быстро и легко отсортировать данные по количеству символов, воспользуйтесь удобным инструментом — Kutools для Excel. Благодаря функции Расширенная сортировка вы легко справитесь с этой задачей.

1. Выделите список данных, который нужно отсортировать, затем нажмите KUTOOLS PLUS > Сортировка > Расширенная сортировка.

2. В диалоговом окне Расширенная сортировка выберите в разделе Столбец столбец, по которому нужно выполнить сортировку, а затем укажите Длина текста в раскрывающемся списке поля Сортировать по. Наконец, задайте нужный порядок сортировки в разделе Порядок. См. снимок экрана:

Советы: Если ваши данные содержат заголовки, обязательно установите флажок Включить заголовки. Если заголовков нет — снимите этот флажок.

3. Затем нажмите кнопку ОК, и текстовые строки в списке отсортируются по длине — как показано на снимке экрана ниже:

3,3 Сортировка Полное имя по Фамилия

При сортировке списка Полное имя Excel использует первый символ имени для упорядочивания. Но что делать, если требуется сортировать данные по Фамилия? Ниже приведены несколько приёмов, которые помогут легко отсортировать Полное имя по Фамилия в Excel.

Сортировка Полное имя по Фамилия с помощью вспомогательного столбца

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

1. Введите или скопируйте приведённую ниже формулу в пустую ячейку рядом с исходными данными (например, в ячейку B2), а затем перетащите маркер заполнения вниз, чтобы применить формулу к нужным ячейкам. См. снимок экрана:

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

2. Выделите ячейки с формулой и нажмите Данные > По возрастанию или Сортировка от Я к А, в зависимости от ваших потребностей. В появившемся окне предупреждения Предупреждение о сортировке выберите Расширить выделенный диапазон. См. снимок экрана:

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

Сортировка Полное имя по Фамилия быстрым способом

Если вы не знакомы с формулами и хотите отсортировать «Полное имя» по фамилии без их использования, на помощь придёт функция Расширенная сортировка из Kutools для Excel.

1. Выделите ячейки с полными именами, которые нужно отсортировать, и нажмите KUTOOLS PLUS > Сортировка > Расширенная сортировка.

2. В диалоговом окне Расширенная сортировка выберите в разделе Столбец столбец, по которому нужно выполнить сортировку, а затем укажите Фамилия в раскрывающемся списке поля Сортировать по. Наконец, задайте нужный порядок сортировки в разделе Порядок. См. снимок экрана:

Советы: Если ваши данные содержат заголовки, убедитесь, что установлен флажок Включить заголовкиЕсли заголовков нет, снимите этот флажок.

3. Затем нажмите кнопку ОК, и столбец «Полное имя» будет немедленно отсортирован по фамилии.

3,4 Сортировка Адрес электронной почты по домену

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

Сортировка Адрес электронной почты по домену с помощью вспомогательного столбца

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

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

=RIGHT(B2,LEN(B2)-FIND("@",B2))

2. Оставьте выделенными ячейки с формулой и нажмите Данные > По возрастанию или Сортировка от Я к А, в зависимости от ваших потребностей. В диалоговом окне Предупреждение о сортировке установите флажок Расширить выделенный диапазон. См. снимок экрана:

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

4. После сортировки при необходимости можно удалить вспомогательный столбец.

Сортировка Адрес электронной почты по домену за несколько кликов

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

1. Выделите весь диапазон данных, который нужно отсортировать, и нажмите KUTOOLS PLUS > Сортировка > Расширенная сортировка.

2. В диалоговом окне Расширенная сортировка выберите в разделе Столбец столбец, по которому нужно выполнить сортировку, затем укажите Домен электронной почты в раскрывающемся списке поля Сортировать по. Наконец, задайте нужный порядок сортировки в разделе Порядок. См. снимок экрана:

Советы: Если ваши данные содержат заголовки, убедитесь, что установлен флажок Включить заголовкиЕсли заголовков нет, снимите этот флажок.

3. Нажмите кнопку ОК, и диапазон данных будет отсортирован по домену электронной почты, как показано на снимке экрана ниже:

3,5 Сортировка одного столбца для сопоставления с другим

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

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

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

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

=MATCH(B2,$A$2:$A$10,0)

2. Затем выделите столбец B и новый вспомогательный столбец, после чего нажмите Данные > Сортировка, чтобы открыть диалоговое окно Сортировка. В диалоговом окне Сортировка выполните следующие действия:

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

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

Сортировка двух столбцов с несовпадающими элементами для сопоставления

Иногда элементы в двух столбцах могут не совпадать полностью. Например, вы можете отсортировать данные во втором столбце так, чтобы они точно соответствовали данным в первом — с одинаковыми значениями в одних и тех же строках, как показано на скриншоте ниже.

1. Вставьте новый пустой столбец между двумя существующими столбцами.

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

=IF(ISNA(MATCH(A2,$C$2:$C$8,0)),"",INDEX($C$2:$C$8,MATCH(A2,$C$2:$C$8,0)))

3,6 Сортировка и извлечение уникальных значений из списка данных

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

1. Введите приведённую ниже формулу в пустую ячейку — например, C2, — а затем нажмите клавиши Ctrl + Shift + Enter, чтобы получить первый результат (см. скриншот):

=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($A$2:$A$12,"<"&,$A$2:$A$12)-SUM(COUNTIF($A$2:$A$12,"="&,C$1:C1)),0)),"")

Примечание: В формуле A2:A12 — это диапазон данных, из которого вы хотите извлечь уникальные значения, а C1 — ячейка над той, в которую вы вводите формулу. При необходимости измените эти ссылки.

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

3,7 Одновременная независимая сортировка нескольких строк или столбцов

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

Индивидуальная сортировка нескольких строк

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

Сортировка нескольких строк независимо с помощью формулы

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

2. Затем скопируйте или введите приведённую ниже формулу в пустую ячейку — H2, и нажмите клавиши Ctrl + Shift + Enter одновременно, чтобы получить первый результат (см. скриншот):

=INDEX($B2:$E2, MATCH(COLUMNS($B2:B2), COUNTIF($B2:$E2, "<="&,$B2:$E2), 0))

3. Затем выделите ячейку с формулой H2 и перетащите маркер заполнения вправо, чтобы скопировать формулу в остальные ячейки первой строки. В данном примере перетащите его до ячейки K2 (см. скриншот):

4. Выделите ячейки с формулами в первой строке (H2:K2) и перетащите маркер заполнения вниз, чтобы скопировать формулы в остальные строки. Теперь вы увидите, что значения в каждой строке отсортированы по возрастанию независимо друг от друга.

Сортировка нескольких строк независимо с помощью кода VBA

Следующий код VBA также поможет вам легко отсортировать данные в каждой строке по алфавиту. Выполните следующие шаги:

1. Выделите данные, которые нужно отсортировать в каждой строке.

2. Удерживая нажатыми клавиши ALT + F11, откройте окно Microsoft Visual Basic for Applications.

3. Затем нажмите Вставка > Модуль и вставьте следующий код в окно Модуля.

Код VBA: Сортировка нескольких строк независимо

Sub SortIndividualR()
'Updateby Extendoffice
    Dim xRg As Range, yRg As Range
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set xRg = Selection
    If xRg.Count = 1 Then
        MsgBox "Select multiple cells!", vbExclamation, "Kutools for Excel"
        Exit Sub
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    Application.ScreenUpdating = False
    For Each yRg In xRg.Rows
        yRg.Sort Key1:=yRg.Cells(1, 1), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        Orientation:=xlSortRows
    Next yRg
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    Application.ScreenUpdating = True
End Sub

Затем нажмите клавишу F5 , чтобы запустить этот код. Данные в каждой строке будут немедленно отсортированы по возрастанию (см. скриншот):

Индивидуальная сортировка нескольких столбцов

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

Сортировка нескольких столбцов независимо с помощью формулы

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

2. Введите приведённую ниже формулу в пустую ячейку — F3, а затем одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы получить первый результат. После этого перетащите маркер заполнения вниз, чтобы скопировать формулу в другие строки (см. скриншот):

=INDEX(A$3:A$6,MATCH(ROWS(A$3:A3),COUNTIF(A$3:A$6,"<="&,A$3:A$6),0))

3. Выделите ячейки с формулами в первой строке (F3:F6) и перетащите маркер заполнения вправо, чтобы скопировать формулу в другие столбцы. Теперь значения в каждом столбце будут отсортированы по возрастанию независимо друг от друга, как показано на скриншоте ниже:

Сортировка нескольких столбцов независимо с помощью кода VBA

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

1. Удерживая нажатыми клавиши ALT + F11, откройте окно Microsoft Visual Basic for Applications.

2. Затем нажмите Вставка > Модуль и вставьте следующий код в окно Модуля.

Код VBA: Сортировка нескольких столбцов независимо

Sub SortIndividualJR()
'Updateby Extendoffice
    Dim xRg As Range
    Dim yRg As Range
    Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error Resume Next
    Set xRg = Application.InputBox(Prompt:="Range Selection:", _
                                    Title:="Kutools for excel", Type:=8)
    Application.ScreenUpdating = False
    For Each yRg In xRg
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=yRg, Order:=xlAscending
            .SetRange ws.Range(yRg, yRg.End(xlDown))
            .Header = xlNo
            .MatchCase = False
            .Apply
        End With
    Next yRg
    Application.ScreenUpdating = True
End Sub

3. Затем нажмите клавишу F5, чтобы запустить этот код. Появится диалоговое окно: «Пожалуйста, выберите диапазон данных, которые необходимо отсортировать» (см. скриншот):

4. Затем нажмите кнопку ОК, и каждый столбец будет быстро отсортирован независимо.

3,8 Случайная сортировка данных в Excel

Обычно мы сортируем данные по алфавиту в порядке возрастания или убывания, но пробовали ли вы когда-нибудь сортировать данные Случайно выбрать? В этом разделе я расскажу, как перемешать диапазон ячеек в случайном порядке.

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

Как правило, для генерации случайных чисел можно использовать функцию СЛЧИС (RAND), а затем отсортировать данные по этому случайному списку. Выполните следующие действия:

1. Введите формулу =RAND() в пустую ячейку рядом с вашими данными и перетащите маркер заполнения вниз, чтобы создать список случайных чисел, как показано на скриншоте ниже:

2. Оставив выделенными ячейки с формулами, нажмите Данные > По возрастанию или Сортировка от Я к А, в зависимости от ваших предпочтений. В появившемся диалоговом окне Предупреждение о сортировке выберите опцию Расширить выделенный диапазон (см. скриншот):

3. Затем нажмите кнопку Сортировать, и список данных будет немедленно перемешан (см. скриншот):

Случайная сортировка ячеек, строк или столбцов с помощью удивительной функции

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

1. Выделите диапазон данных, который нужно отсортировать случайным образом, затем нажмите KUTOOLS PLUS > Сортировка > Случайно переставить (см. скриншот):

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

• Вся строка: Перемешайте «Всю строку» в разделе «Случайно выбрать».
• Сортировать все столбцы: Перемешайте все столбцы, чтобы выбрать случайный порядок.
• Сортировать в каждой строке: Перемешать и отсортировать каждую строку по отдельности.
• Сортировать в каждом столбце: Перемешать — сортировка выполняется в каждом столбце по отдельности.
• Сортировать все ячейки: Случайным образом перемешайте все ячейки в выбранном диапазоне.

3. Затем нажмите ОК, и данные немедленно перемешаются.

3,9 Сортировка списка дат по месяцу, дню или по месяцу и дню

При сортировке дат в Excel функция «Сортировка» по умолчанию упорядочивает список по году, месяцу и дню. Однако иногда возникает необходимость отсортировать даты только по месяцу, только по дню или по комбинации месяца и дня, игнорируя год. Как этого добиться в Excel?

Сортировка списка дат по месяцу или дню с использованием вспомогательного столбца

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

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

=MONTH(B2)        (extract month number)
=DAY(B2)(извлечение номера дня)

2. После получения номеров месяцев или дней выделите ячейки с формулами и нажмите Данные > По возрастанию или Сортировка от Я до А — в зависимости от ваших задач. В появившемся окне Предупреждение о сортировке выберите Расширить выделенный диапазон (см. снимок экрана):

3. Затем нажмите Сортировка, и даты будут отсортированы по месяцам, игнорируя годы и дни (см. снимок экрана):

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

Если у вас установлен Kutools для Excel, с помощью его функции Расширенная сортировка вы можете отсортировать список дат по месяцу или дню всего за несколько щелчков.

1. Выделите диапазон данных, который нужно отсортировать, и нажмите KUTOOLS PLUS > Сортировка > Расширенная сортировка.

2. В диалоговом окне Расширенная сортировка в разделе Столбец выберите столбец для сортировки и укажите Месяц или День в поле Сортировать по из раскрывающегося списка. Наконец, задайте нужный порядок сортировки в разделе Порядок.

Совет: если ваши данные содержат заголовки, убедитесь, что установлен флажок Включить заголовки. Если заголовков нет, снимите этот флажок.

3. Затем нажмите ОК, и данные будут отсортированы по месяцу или дню, как вы указали, игнорируя год (см. снимок экрана):

Сортировка списка дат по месяцу и дню с использованием вспомогательного столбца

Теперь, если вам нужно отсортировать список дат только по месяцу и дню, игнорируя год, функция ТЕКСТ преобразует дату в текстовую строку в нужном формате — после этого вы сможете отсортировать данные так, как вам удобно.

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

=TEXT(B2,"MMDD")

2. Выделите ячейки с формулами и выберите Данные > По возрастанию или Сортировка от Я до А — в зависимости от ваших задач. В появившемся окне Предупреждение о сортировке выберите Расширить выделенный диапазон (см. снимок экрана):

3. Затем нажмите Сортировка, и теперь ваши данные отсортированы только по месяцу и дню.

3,10 Сортировка списка дат по Статистика по дням недели

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

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

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

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

=WEEKDAY(B2)           (Start from Sunday (1) to Saturday(7))
=WEEKDAY(B2,2)(от понедельника (1) до воскресенья (7))

2. Выделите ячейки с формулами и нажмите Данные>По возрастаниюили Сортировка от Я до Ав зависимости от ваших потребностей. В появившемся окне Предупреждение о сортировкевыберите Расширить выделенный диапазон(см. снимок экрана):

3. Затем нажмите Сортировка, чтобы получить нужный результат сортировки (см. снимок экрана):

Сортировка списка дат по Статистика по дням недели с помощью простого параметра

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

1. Выделите диапазон данных, который нужно отсортировать, и нажмите KUTOOLS PLUS > Сортировка > Расширенная сортировка.

2. В диалоговом окне Расширенная сортировка в разделе Столбец выберите столбец для сортировки и укажите Неделя в поле Сортировать по из раскрывающегося списка. Наконец, задайте нужный порядок сортировки в разделе Порядок.

Совет: если ваши данные содержат заголовки, убедитесь, что установлен флажок Включить заголовки. Если заголовков нет, снимите этот флажок.

3. Затем нажмите ОК, и данные будут отсортированы по «Статистике по дням недели» именно так, как вам нужно (см. снимок экрана):

3,11 Сортировка списка дат по кварталу

В этом разделе описано два способа сортировки списка дат по кварталам без учёта года.

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

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

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

=ROUNDUP(MONTH(B2)/3,0)

2. Продолжайте выделять ячейки с формулами, а затем нажмите Данные > По возрастанию или Сортировка от Я к А — в зависимости от того, что вам нужно. В появившемся окне Предупреждение о сортировке выберите Расширить выделенный диапазон, см. скриншот:

3. Затем нажмите кнопку Сортировка, и диапазон данных будет отсортирован по кварталам, как показано на скриншоте ниже:

Сортировка списка дат по кварталу с помощью удобной функции

Если у вас установлен Kutools для Excel, функция Расширенная сортировка поможет вам выполнить эту задачу всего за несколько кликов.

1. Выделите диапазон данных, который необходимо отсортировать, затем нажмите KUTOOLS PLUS > Сортировка > Расширенная сортировка.

2. В диалоговом окне Расширенная сортировка выберите столбец для сортировки в разделе Столбец и укажите Квартал в поле Сортировать по из раскрывающегося списка. Наконец, задайте нужный порядок сортировки в разделе Порядок.

Совет: если ваши данные содержат заголовки, убедитесь, что установлен флажок Включить заголовкиЕсли заголовков нет, снимите этот флажок.

3. Наконец, нажмите кнопку ОК, и данные из диапазона «Выберите диапазон» будут отсортированы по кварталам именно так, как вам нужно.

3,12 Сортировка данных по названиям месяцев или дней недели

Если у вас есть список названий месяцев в текстовом виде, при обычной сортировке они будут упорядочены по алфавиту, а не по календарной последовательности — от января до декабря. Чтобы расположить их именно в хронологическом порядке, используйте пользовательскую сортировку в функции «Сортировка».

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

2. В диалоговом окне Сортировка в разделе Столбец выберите столбец с названиями месяцев, в разделе Сортировать по укажите Значения ячеек, а в разделе Порядок — Пользовательские списки. См. скриншот:

Совет: если ваши данные содержат заголовки, убедитесь, что установлен флажок Включить заголовки. Если заголовков нет, снимите этот флажок.

3. В появившемся диалоговом окне Пользовательские списки выберите полные названия месяцев (январь, февраль, март и т. д.) или сокращённые (янв., февр., март и т. д.) — в зависимости от того, как они указаны на вашем листе (см. скриншот):

4. Затем нажмите кнопку ОК > ОК, чтобы закрыть диалоговые окна. Теперь ваши данные отсортированы по месяцам в хронологическом порядке, как показано на скриншоте ниже:

Совет: чтобы отсортировать по дням недели, в диалоговом окне Пользовательский список выберите полные названия (воскресенье, понедельник, вторник и т. д.) или сокращённые (вс, пн, вт и т. д.) — в зависимости от ваших потребностей.

3,13 Сортировка данных по чётным или нечётным числам

В Excel легко сортировать числа по возрастанию или убыванию, но пробовали ли вы когда-нибудь отсортировать список так, чтобы сначала шли чётные числа, а затем нечётные (или наоборот)? В этом разделе я покажу несколько способов решения такой задачи.

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

Чтобы отсортировать список чисел по чётности/нечётности, сначала создайте формулу для определения чётных и нечётных чисел, а затем примените функцию сортировки.

1. Рядом со списком чисел введите формулу =ISODD(A2) в пустую ячейку, а затем протяните её на остальные ячейки. Теперь вы увидите значения ИСТИНА и ЛОЖЬ: ИСТИНА означает нечётные числа, а ЛОЖЬ — чётные.

2. Продолжайте выделять ячейки с формулой, затем нажмите Данные > По возрастанию или Сортировка от Я к А — в зависимости от того, что вам нужно. В появившемся окне Предупреждение о сортировке выберите Расширить выделенный диапазон, см. скриншот:

3. Затем нажмите кнопку Сортировка, и все чётные числа будут сгруппированы вместе, за которыми последуют нечётные (или наоборот). См. скриншот:

Сортировка данных по нечётным или чётным числам с помощью полезной функции

С помощью функции Kutools для Excel — Расширенная сортировка — вы также можете быстро отсортировать числа от нечётных к чётным или наоборот.

1. Выделите диапазон данных, который необходимо отсортировать, и нажмите KUTOOLS PLUS>Сортировка>Расширенная сортировка.

2. В диалоговом окне Расширенная сортировка в разделе Столбец выберите нужный столбец и укажите Четные и нечетные числа в поле Сортировать по. Затем задайте порядок сортировки ()от А к Я — сортировка от чётных к нечётным числам, а от Я к А — от нечётных к чётным). См. скриншот:

Совет: если ваши данные содержат заголовки, убедитесь, что установлен флажок Включить заголовки. Если заголовков нет, снимите этот флажок.

3. Затем нажмите кнопку ОК, и вы увидите следующие результаты:

3,14 Сортировка данных по Абсолютное значение

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

Сортировка данных по Абсолютное значение с помощью вспомогательного столбца

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

1. В соседнюю пустую ячейку (например, B2) введите формулу =ABS(A2), а затем протяните маркер заполнения, чтобы скопировать формулу в остальные ячейки. Теперь все значения отобразятся как абсолютные:

2. Оставьте выделенными ячейки с формулами и нажмите Данные > По возрастанию или Сортировка от Я к А, как требуется. В появившемся окне предупреждения Предупреждение о сортировке выберите Расширить выделенный диапазон, см. скриншот:

3. Затем нажмите кнопку Сортировка, и все числа будут отсортированы по абсолютному значению, как показано на скриншоте ниже:

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

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

1. Выделите диапазон данных, который нужно отсортировать, и нажмите KUTOOLS PLUS > Сортировка > Расширенная сортировка.

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

Совет: Если ваши данные содержат заголовки, обязательно установите флажок «Включить заголовки». Если заголовков нет — снимите его.

3. Затем нажмите кнопку ОК, и все числа будут отсортированы по значениям — от наименьшего к наибольшему или от наибольшего к наименьшему, игнорируя знак «минус».

Автоматическая сортировка данных при вводе или изменении

Функция сортировки в Excel не является динамической: после каждого изменения или добавления новых данных потребуется повторно выполнять сортировку. В этом разделе рассматривается способ автоматической сортировки данных каждый раз при добавлении нового значения в ваш Диапазон данных.

4,1 Автоматическая сортировка числовых значений в столбце с помощью формул

Чтобы автоматически отсортировать список чисел по возрастанию или убыванию, можно использовать формулы на основе функций НАИБОЛЬШИЙ, НАИМЕНЬШИЙ и СТРОКА.

1. Введите или скопируйте приведённую ниже формулу в пустую ячейку рядом с вашим диапазоном «Исходные данные», затем перетащите маркер заполнения вниз до последней ячейки, к которой нужно применить эту формулу. Числа автоматически упорядочатся от наименьшего к наибольшему — см. скриншот:

=IFERROR(SMALL($A$2:$A$100,ROWS(B$2:B2)),"")

Примечание: В этой формуле A2:A100 — это список чисел, который требуется автоматически сортировать с учётом дополнительных строк и пустых ячеек для новых записей; B2 — ячейка, в которую вводится формула.

2. Теперь при изменении значений в «Исходных данных» или добавлении новых данных отсортированный список будет автоматически обновляться, как показано на демонстрации ниже:

4,2 Автоматическая сортировка текстовых значений в столбце с помощью формул

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

1. Введите или скопируйте приведённую ниже формулу в пустую ячейку рядом со столбцом данных, затем одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы получить первый результат. После этого выделите ячейку с формулой и перетащите маркер заполнения вниз до нужных ячеек (см. скриншот):

=IFERROR(INDEX($A$2:$A$100,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$100,">="&,$A$2:$A$100),0)),"")

2. Теперь при вводе новых значений или изменении исходных данных в столбце A текстовые строки в столбце B будут автоматически сортироваться по возрастанию — см. демонстрацию ниже:

4,3 Автоматическая сортировка смешанных числовых и текстовых значений в столбце с помощью кода VBA

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

1. Щёлкните правой кнопкой мыши вкладку листа, на котором нужно автоматически сортировать данные, и выберите в контекстном меню пункт Просмотреть код. В открывшемся окне Microsoft Visual Basic for Applications скопируйте и вставьте следующий код в пустое окно Модуль, см. скриншот:

Код VBA: автоматическая сортировка при вводе или изменении данных по возрастанию:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Примечание: В приведённом выше коде введённые данные автоматически сортируются в столбце A. A1 — это заголовок, а A2 — первая ячейка списка данных.

2. Сохраните и закройте окно с кодом. Теперь при вводе новых данных или изменении исходных данных в столбце A они будут автоматически сортироваться по возрастанию. См. демонстрацию ниже:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Другие случаи сортировки

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

5,1 Сортировка данных внутри ячейки

В этом разделе описано, как сортировать текстовую строку внутри одной ячейки по алфавиту — например, преобразовать «HDAW» в «ADHW» — или упорядочивать несколько слов, разделённых запятыми, как в примере: «word, outlook, excel, access» → «access, excel, outlook, word».

Сортировка строковых значений внутри ячейки в алфавитном порядке

Чтобы отсортировать строковое значение внутри ячейки по алфавиту, создайте пользовательскую функцию, выполнив следующие действия:

1Удерживая клавиши нажатыми ALT + F11, откройте окно Microsoft Visual Basic for Applications.

2. Нажмите Вставка > Модуль и вставьте следующий код в окно Модуль.

Код VBA: сортировка текстового значения внутри ячейки

Function SortCellContents(xRange As Range)
'Updateby Extendoffice
Dim xArr
Dim xF1, xF2 As Integer
Dim xStrValue As String
Dim xStrT As String
If xRange.Count <> 1 Then
    Exit Function
End If
xStrValue = xRange.Value
ReDim xArr(1 To Len(xStrValue))
For xF1 = 1 To UBound(xArr)
    xArr(xF1) = Mid(xStrValue, xF1, 1)
Next
For xF1 = 1 To UBound(xArr)
    For xF2 = xF1 To UBound(xArr)
        If Asc(xArr(xF2)) < Asc(xArr(xF1)) Then
            xStrT = xArr(xF2)
            xArr(xF2) = xArr(xF1)
            xArr(xF1) = xStrT
        End If
    Next xF2
Next xF1
SortCellContents = Join(xArr, "")
End Function

3. Сохраните и закройте этот код, затем вернитесь на лист. Введите формулу =SortCellContents(A2) в пустую ячейку, чтобы получить результат, и перетащите маркер заполнения вниз, чтобы скопировать формулу в другие ячейки. Все текстовые значения в ячейках будут отсортированы в алфавитном порядке (см. скриншот):

Сортировка текстовых строк, разделённых разделителем, внутри ячейки в алфавитном порядке

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

1Удерживая клавиши нажатыми,ALT + F11, откройте окно Microsoft Visual Basic for Applications.

2. Нажмите Вставить > Модуль и вставьте следующий код в окно Модуль.

Код VBA: сортировка текстовых строк, разделённых запятой, внутри ячейки

Function SortCellWithSeparator(CellAddress As Range, DelimiterChar As String, IncludeSpaces As Boolean) As String
'Updateby Extendoffice
Dim xRg As Range
Dim xString As String
Dim xF1, xF2 As Integer
Dim xSArr
Dim xStrT As String
Dim xStrValue As String
Set xRg = CellAddress
xStrValue = WorksheetFunction.Substitute(xRg.Value, " ", "")
xSArr = Split(xStrValue, DelimiterChar)
    For xF1 = 0 To UBound(xSArr)
        For xF2 = xF1 + 1 To UBound(xSArr)
                If xSArr(xF2) < xSArr(xF1) Then
                    xStrT = xSArr(xF2)
                    xSArr(xF2) = xSArr(xF1)
                    xSArr(xF1) = xStrT
                End If
        Next xF2
    Next xF1
xStrValue = ""
For xF1 = 0 To UBound(xSArr)
    xStrValue = xStrValue & xSArr(xF1) & DelimiterChar
Next xF1
SortCellWithSeparator = xStrValue
SortCellWithSeparator = Left(SortCellWithSeparator, Len(SortCellWithSeparator) - 1)
If IncludeSpaces = True Then SortCellWithSeparator = WorksheetFunction.Substitute(SortCellWithSeparator, ",", ", ")
End Function

3. Затем сохраните и закройте этот код, вернитесь на лист и введите формулу =SortCellWithSeparator(A2,",",TRUE) в пустую ячейку для вывода результата. После этого перетащите маркер заполнения вниз, чтобы скопировать формулу в другие ячейки. Все текстовые строки в ячейках будут отсортированы в алфавитном порядке. См. скриншот:

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

5,2 Обратный порядок / переворот данных в столбце или строке

Иногда бывает необходимо перевернуть порядок данных — сверху вниз в вертикальном диапазоне или слева направо в горизонтальном. В этом разделе описаны три способа выполнить такую задачу в Excel.

Обратная сортировка (переворот) порядка данных в столбце или строке с помощью формул

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

Переворот порядка данных в столбце

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

=OFFSET($A$10,-(ROW(A1)-1),0)

Примечание: в приведённой выше формуле A1 — первая ячейка, а A10 — последняя ячейка в столбце.

Переворот порядка данных в строке

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

=OFFSET($A$1,,COUNTA(1:1)-COLUMN(A1),)

Примечание: в приведённой выше формуле A1 — первая ячейка в строке, а 1:1 — номер строки, в которой находятся ваши данные. Если данные находятся в строке 10, измените значение на 10:10.

Затем перетащите маркер заполнения вправо по ячейкам, пока не извлечёте все значения — и ваши данные будут перевёрнуты горизонтально. См. снимок экрана:

Обратная сортировка (переворот) порядка данных в нескольких столбцах или строках с помощью кода VBA

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

Переворот порядка Данные в выделенном диапазоне ячеек по вертикали

1. Сначала создайте резервную копию исходных данных, затем нажмите и удерживайте клавиши ALT + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. Нажмите Вставка > Модуль и вставьте следующий код в окно Модуля.

Код VBA: переворот диапазона ячеек в обратном порядке по вертикали

Sub Flipvertically()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For j = 1 To UBound(Arr, 2)
    k = UBound(Arr, 1)
    For i = 1 To UBound(Arr, 1) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(k, j)
        Arr(k, j) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

3. Затем нажмите клавишу F5, чтобы запустить этот код. Появится диалоговое окно с запросом на выбор диапазона данных, который вы хотите перевернуть по вертикали. См. снимок экрана:

4. Затем нажмите кнопку ОК, и диапазон данных будет перевёрнут по вертикали, как показано на снимках экрана:

Переворот порядка Данные в выделенном диапазоне ячеек по горизонтали

Чтобы перевернуть Диапазон данных в горизонтальном порядке, примените приведённый ниже код VBA:

Код VBA: переворот диапазона ячеек в обратном порядке по вертикали

Sub Fliphorizontally()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For i = 1 To UBound(Arr, 1)
    k = UBound(Arr, 2)
    For j = 1 To UBound(Arr, 2) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(i, k)
        Arr(i, k) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

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

Обратная сортировка (переворот) порядка данных в столбцах или строках всего за один щелчок

Если у вас установлен Kutools для Excel, с его функциями Повернуть диапазон по вертикали и Перевернуть диапазон горизонтально вы сможете перевернуть диапазон ячеек по вертикали и горизонтали всего одним щелчком мыши.

Переворот порядка Данные в выделенном диапазоне ячеек по вертикали

1. Выделите диапазон данных, который вы хотите перевернуть, и нажмите Kutools > Диапазон > Повернуть диапазон по вертикали > Все / Повернуть только содержимое. См. снимок экрана:

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

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

Переворот порядка Данные в выделенном диапазоне ячеек по горизонтали

1. Выделите диапазон данных и нажмите Kutools > Диапазон > Перевернуть диапазон горизонтально > Все / Повернуть только содержимое. См. снимок экрана:

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

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

5,3 Сортировка вкладок листов в Excel по алфавиту или цвету вкладки

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

Сортировка вкладок листов в алфавитном порядке с помощью кода VBA

Следующий код VBA поможет отсортировать Вкладки листов в алфавитном порядке по возрастанию или убыванию. Выполните следующие действия:

1. Удерживая клавиши ALT + F11, вы откроете окно Microsoft Visual Basic для приложений.

2. Нажмите Вставка > Модуль, а затем вставьте следующий макрос в окно Модуля.

VBA: сортировка всех листов в алфавитном порядке

Sub SortWorkBook()
'Updateby Extendoffice
Dim xResult As VbMsgBoxResult
xTitleId = "KutoolsforExcel"
xResult = MsgBox("Click Yes to sort sheets in ascending order;" & Chr(10) & "Click No will sort in descending order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)
For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
        If xResult = vbYes Then
            If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
            ElseIf xResult = vbNo Then
                If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                    Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        End If
    Next
Next
End Sub

3. Затем нажмите клавишу F5, чтобы запустить этот макрос. В появившемся диалоговом окне нажмите Да — и все листы будут отсортированы в алфавитном порядке по возрастанию; нажмите Нет — и все листы будут отсортированы в алфавитном порядке по убыванию, как вам требуется.

Сортировка вкладок листов в алфавитном / буквенно-цифровом порядке с помощью мощной функции

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

1. Откройте книгу, в которой нужно отсортировать вкладки листов, и нажмите KUTOOLS PLUS > Лист > Сортировать все листы. См. снимок экрана:

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

3. Затем нажмите кнопку Ок, и все листы будут отсортированы в соответствии с выбранным вами типом сортировки. См. снимки экрана:

Сортировка вкладок листов по цвету вкладки с помощью кода VBA

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

1. Удерживая ALT + F11клавиши, откройте Microsoft Visual Basic для приложенийокно.

2. Нажмите Вставить > Модуль, а затем вставьте следующий макрос в окно Модуль.

VBA: сортировка всех листов по цвету вкладок

Sub SortWorkBookByColor()
'Updateby20140624
Dim xArray1() As Long
Dim xArray2() As String
Dim n As Integer
Application.ScreenUpdating = False
If Val(Application.Version) >= 10 Then
    For i = 1 To Application.ActiveWorkbook.Worksheets.Count
        If Application.ActiveWorkbook.Worksheets(i).Visible = -1 Then
            n = n + 1
            ReDim Preserve xArray1(1 To n)
            ReDim Preserve xArray2(1 To n)
            xArray1(n) = Application.ActiveWorkbook.Worksheets(i).Tab.Color
            xArray2(n) = Application.ActiveWorkbook.Worksheets(i).Name
        End If
    Next
    For i = 1 To n
        For j = i To n
            If xArray1(j) < xArray1(i) Then
                temp = xArray2(i)
                xArray2(i) = xArray2(j)
                xArray2(j) = temp
                temp = xArray1(i)
                xArray1(i) = xArray1(j)
                xArray1(j) = temp
            End If
        Next
    Next
    For i = n To 1 Step -1
        Application.ActiveWorkbook.Worksheets(CStr(xArray2(i))).Move after:=Application.ActiveWorkbook.Worksheets(Application.ActiveWorkbook.Worksheets.Count)
    Next
End If
Application.ScreenUpdating = True
End Sub

3. Затем нажмите клавишу F5, чтобы выполнить этот код. Листы с одинаковым цветом вкладок автоматически сгруппируются вместе. См. снимки экрана:

Сортировка вкладок листов по цвету вкладки с помощью удивительной функции

С помощью функции Kutools для Excel «Сортировать все листы» вы также можете быстро и легко сортировать вкладки листов по цвету.

1. Нажмите Kutools Plus > Worksheet > Сортировать все листы, чтобы открыть диалоговое окно Сортировать все листы. В диалоговом окне Сортировать все листы нажмите кнопку Сортировка по цвету на правой панели (см. снимок экрана):

2. Затем нажмите кнопку Ok, и листы с одинаковым цветом вкладок автоматически сгруппируются вместе, как показано на снимках экрана ниже:

Лучшие инструменты повышения продуктивности в Office

🤖	KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
	Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
	Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
	Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
	Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
	Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
	Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое

Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

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

Подробнее о Kutools для Excel…Бесплатная загрузка…

---

Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу

• Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
• Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Подробнее о Office Tab…Бесплатная загрузка…

---

Все надстройки Kutools — один установщик

Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.

• Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
• Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
• Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
• 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
• Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек

Подробнее о Kutools for Office…Бесплатная загрузка…