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

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

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

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

В Excel существует несколько способов доступа к функции сортировки и её параметрам.

1.1 Кнопки сортировки на ленте

Самый быстрый способ воспользоваться функцией сортировки — использовать кнопки сортировки на ленте.

Щёлкните любую ячейку в столбце с данными, которые нужно отсортировать, затем перейдите на вкладку Data, в разделе Sort & Filter нажмите кнопку Sort A to Z или Sort Z to A, чтобы отсортировать данные в алфавитном порядке по возрастанию или убыванию. См. скриншот:

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

1.2 Диалоговое окно сортировки

В группе Sort & Filter на вкладке Data есть ещё одна кнопка Sort, см. скриншот:

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

Далее в диалоговом окне Sort вы можете выбрать соответствующие правила для сортировки данных по вашему усмотрению.

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

1.3 Параметры сортировки в меню фильтра

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

Примечание: Эти параметры сортировки будут меняться в зависимости от данных в столбце: если в столбце текст, появятся Sort A to Z, Sort Z to A; если числа — Sort Smallest to Largest, Sort Largest to Smallest; если даты — Sort Oldest to Newest, Sort Newest to Oldest.

1.4 Параметры сортировки по правому клику

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

Основные способы сортировки

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

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

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

1. Выделите диапазон данных, который хотите отсортировать, затем нажмите Data > Sort, см. скриншот:

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

• В разделе Column выберите имя столбца, по которому хотите сортировать;
• В разделе Sort On выберите опцию Cell Values;
• В разделе Order укажите порядок сортировки. (Для сортировки текста выберите A to Z или Z to A; для чисел — Smallest to Largest или Largest to Smallest; для дат — Oldest to Newest или Newest to Oldest.)
• Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

3. Затем нажмите кнопку OK — выбранные данные будут отсортированы по указанному столбцу сразу.

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

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

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

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

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

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

• В разделе Column выберите Name или другой столбец, где есть цветные ячейки. В этом примере цветные ячейки есть во всех столбцах, вы можете выбрать любой столбец;
• В разделе Sort On выберите опцию Cell Color;
• В разделе Order выберите цвет ячейки, который хотите разместить сверху или снизу;

2.2) Затем нажмите кнопку Add Level, чтобы добавить второе и последующие условия, повторите вышеуказанные шаги для каждого цвета.

• Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

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

Совет: По аналогии с вышеописанными шагами вы также можете отсортировать данные по цвету шрифта или значку ячейки, выбрав Font Color или Conditional Formatting Icon в диалоговом окне Sort.

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

Если у вас большой набор данных, как показано на скриншоте ниже, и вы хотите выполнить многоуровневую сортировку для более удобного чтения, например, сначала по столбцу Region, затем по State, и в конце по Sales, выполните следующие действия:

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

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

2. В диалоговом окне Sort нажмите кнопку Add Level дважды, если вы хотите использовать три столбца для сортировки. После этого вы увидите два уровня Then by в списке:

Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

3. В выпадающих списках Sort by и Then by выберите имена столбцов, по которым хотите сортировать, затем для каждого столбца выберите Cell Values в разделе Sort On, и в конце укажите нужный порядок сортировки.

4. Затем нажмите OK, и вы получите результат сортировки, как показано на скриншоте ниже:

2.4 Сортировка данных по пользовательскому списку

Вместо сортировки данных по алфавиту или числам функция Sort также позволяет сортировать по пользовательскому списку. Например, вы хотите отсортировать данные по статусу — Not Started, In Progress, Completed, как вы определили. Вот как это сделать.

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

2. В диалоговом окне Sort выберите столбец для сортировки в разделе Column, затем выберите Cell Values в разделе Sort On, в разделе Order нажмите Custom List, см. скриншот:

Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

3. Откроется окно Custom Lists, выполните следующие действия:

• Нажмите NEW LIST в окне Custom lists;
• Введите текстовые значения в том порядке, в котором хотите сортировать, в поле List entries; (При вводе значений нажимайте Enter для разделения записей.)
• В конце нажмите кнопку Add — новый список сразу добавится в окно Custom lists.

4. Затем нажмите OK, чтобы вернуться в диалоговое окно Sort. Новый пользовательский список теперь отображается в выпадающем списке Order.

5. Затем нажмите OK, чтобы закрыть диалоговое окно — диапазон данных будет отсортирован по вашему пользовательскому списку, см. скриншот:

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

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

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

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

2. В появившемся диалоговом окне Sort нажмите кнопку Options, затем в диалоговом окне Sort Options выберите Sort left to right, см. скриншот:

3. Нажмите OK, чтобы вернуться в окно Sort, теперь в разделе Row укажите номер строки, по которой хотите сортировать, затем выберите Cell Values в разделе Sort On, и укажите порядок сортировки в разделе Order, см. скриншот:

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

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

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

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

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

2. В диалоговом окне Sort в разделе Column укажите имя столбца, по которому хотите сортировать, затем выберите Cell Values в разделе Sort On, и укажите нужный порядок сортировки в разделе Order, см. скриншот:

Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

3. Далее нажмите кнопку Options в этом диалоговом окне, и в появившемся окне Sort Options отметьте Case sensitive, см. скриншот:

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

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

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

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

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

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

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

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

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

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

2. Затем, выделив ячейки с формулой, нажмите Data > Sort A to Z или Sort Z to A, как нужно. В появившемся окне Sort Warning выберите Expand the selection, см. скриншоты:

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

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

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

1. Выделите список данных, который хотите отсортировать, затем нажмите Kutools Plus > Sort > Advanced Sort, см. скриншот:

2. В диалоговом окне Advanced Sort выберите столбец для сортировки в разделе Column, затем выберите Frequency в выпадающем списке Sort On, и укажите порядок сортировки в разделе Order, см. скриншот:

Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

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

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

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

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

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

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

2. Затем, выделив ячейки с формулой, нажмите Data > Sort A to Z или Sort Z to A, как нужно. В появившемся окне Sort Warning выберите Expand the selection, см. скриншот:

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

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

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

1. Выделите список данных, который хотите отсортировать, затем нажмите Kutools Plus > Sort > Advanced Sort.

2. В диалоговом окне Advanced Sort выберите столбец для сортировки в разделе Column, затем выберите Text length в выпадающем списке Sort On, и укажите нужный порядок сортировки в разделе Order. См. скриншот:

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

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

3.3 Сортировка полных имен по фамилии

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

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

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

1. Введите или скопируйте формулу ниже в пустую ячейку рядом с исходными данными, например, в B2, и протяните маркер заполнения вниз для применения формулы, см. скриншот:

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

2. Затем, выделив ячейки с формулой, нажмите Data > Sort A to Z или Sort Z to A, как нужно. В появившемся окне Sort Warning выберите Expand the selection, см. скриншот:

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

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

Если вы не знакомы с формулами, для сортировки полных имен по фамилии без формул воспользуйтесь функцией Advanced Sort в Kutools для Excel.

1. Выделите ячейки с полными именами, которые хотите отсортировать, затем нажмите Kutools Plus > Sort > Advanced Sort.

2. В диалоговом окне Advanced Sort выберите столбец для сортировки в разделе Column, затем выберите Last name в выпадающем списке Sort On, и укажите нужный порядок сортировки в разделе Order. См. скриншот:

Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

3. Затем нажмите OK — столбец с полными именами будет отсортирован по фамилиям сразу.

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

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

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

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

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

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

2. Затем, выделив ячейки с формулой, нажмите Data > Sort A to Z или Sort Z to A, как нужно. В диалоговом окне Sort Warning выберите Expand the selection, см. скриншот:

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

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

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

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

1. Выделите весь диапазон данных, который хотите отсортировать, затем нажмите Kutools Plus > Sort > Advanced Sort.

2. В диалоговом окне Advanced Sort выберите столбец для сортировки в разделе Column, затем выберите Mail domain в выпадающем списке Sort On, и укажите нужный порядок сортировки в разделе Order. См. скриншот:

Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

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

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

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

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

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

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

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

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

• Выберите вспомогательный столбец, по которому хотите сортировать данные, в разделе Column;
• Затем выберите Cell Values в разделе Sort On;
• В конце выберите Smallest to Largest в разделе Order.

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

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

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

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. Затем нажмите Insert > Module и вставьте следующий код в окно Module.

Код 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

4. Затем нажмите 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. Затем нажмите Insert > Module и вставьте следующий код в окно Module.

Код 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. Затем нажмите OK — каждый столбец будет отсортирован по отдельности быстро.

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

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

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

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

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

2. Затем, выделив ячейки с формулой, нажмите Data > Sort A to Z или Sort Z to A, как нужно. В появившемся окне Sort Warning выберите Expand the selection, см. скриншот:

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

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

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

1. Выделите диапазон данных, который хотите отсортировать случайным образом, затем нажмите Kutools Plus > Sort > Sort Range Randomly. См. скриншот:

2. В диалоговом окне Sort/Select Range Randomly на вкладке Sort выберите нужный вариант:

• Entire rows: перемешать все строки в выбранном диапазоне случайным образом.
• Entire columns: перемешать все столбцы в выбранном диапазоне случайным образом.
• Cells in each row: перемешать ячейки в каждой строке по отдельности.
• Cells in each column: перемешать ячейки в каждом столбце по отдельности.
• All cells in the range: перемешать все ячейки в выбранном диапазоне.

3. Затем нажмите Ok — данные будут перемешаны сразу.

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

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

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

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

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

=MONTH(B2) (извлечь номер месяца)
=DAY(B2) (извлечь номер дня)

2. После получения номеров месяцев или дней выделите ячейки с формулой, затем нажмите Data > Sort A to Z или Sort Z to A, как нужно. В появившемся окне Sort Warning выберите Expand the selection, см. скриншот:

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

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

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

1. Выделите диапазон данных, который хотите отсортировать, затем нажмите Kutools Plus > Sort > Advanced Sort.

2. В диалоговом окне Advanced Sort выберите столбец для сортировки в разделе Column, затем выберите Month или Day в выпадающем списке Sort On, и укажите нужный порядок сортировки в разделе Order.

Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

3. Затем нажмите OK — данные будут отсортированы по месяцу или дню, как вы указали, игнорируя год, см. скриншот:

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

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

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

=TEXT(B2,"MMDD")

2. Затем выделите ячейки с формулой и нажмите Data > Sort A to Z или Sort Z to A, как нужно. В появившемся окне Sort Warning выберите Expand the selection, см. скриншот:

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

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

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

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

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

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

=WEEKDAY(B2)(начиная с воскресенья (1) до субботы (7))
=WEEKDAY(B2,2)(начиная с понедельника (1) до воскресенья (7))

2. Затем выделите ячейки с формулой и нажмите Data > Sort A to Z или Sort Z to A, как нужно. В появившемся окне Sort Warning выберите Expand the selection, см. скриншот:

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

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

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

1. Выделите диапазон данных для сортировки, затем нажмите Kutools Plus > Sort >Advanced Sort.

2. В диалоговом окне Advanced Sort выберите столбец для сортировки в разделе Column, затем выберите Day of the week в выпадающем списке Sort On, и укажите нужный порядок сортировки в разделе Order.

Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

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

3.11 Сортировка списка дат по кварталу

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

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

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

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

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

2. Затем выделите ячейки с формулой и нажмите Data > Sort A to Z или Sort Z to A, как нужно. В появившемся окне Sort Warning выберите Expand the selection, см. скриншот:

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

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

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

1. Выделите диапазон данных, который хотите отсортировать, затем нажмите Kutools Plus > Sort > Advanced Sort.

2. В диалоговом окне Advanced Sort выберите столбец для сортировки в разделе Column, затем выберите Quarter в выпадающем списке Sort On, и укажите нужный порядок сортировки в разделе Order.

Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

3. В конце нажмите OK — выбранный диапазон будет отсортирован по кварталу, как нужно.

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

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

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

2. В диалоговом окне Sort в разделе Column выберите столбец с названиями месяцев, в разделе Sort on выберите Cell Values, в разделе Order выберите Custom List, см. скриншот:

Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

3. Затем в появившемся окне Custom List выберите полные названия месяцев (January, February, March, …) или сокращённые (Jan, Feb, Mar…), в зависимости от того, как месяцы указаны на листе, см. скриншот:

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

Совет: Для сортировки по дням недели выберите полные названия (Sunday, Monday, Tuesday, …) или сокращённые (Sun, Mon, Tue…) в окне Custom Lists.

3.13 Сортировка данных по четным и нечетным числам

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

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

Для сортировки списка чисел по четности создайте формулу для определения четных и нечетных чисел, затем примените функцию Sort.

1. Рядом со списком чисел введите формулу =ISODD(A2) в пустую ячейку и протяните её вниз — появятся значения TRUE (нечетные) и FALSE (четные).

2. Затем выделите ячейки с формулой и нажмите Data > Sort A to Z или Sort Z to A, как нужно. В появившемся окне Sort Warning выберите Expand the selection, см. скриншот:

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

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

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

1. Выделите диапазон данных, который хотите отсортировать, затем нажмите Kutools Plus > Sort > Advanced Sort.

2. В диалоговом окне Advanced Sort выберите столбец для сортировки в разделе Column, затем выберите Odd and even number в разделе Sort On, затем укажите порядок сортировки (A to Z — от четных к нечетным, Z to A — от нечетных к четным), см. скриншот:

Совет: Если ваши данные содержат заголовки, убедитесь, что опция My data has headers отмечена. Если заголовков нет, снимите этот флажок.

3. Затем нажмите OK — вы получите следующий результат:

3.14 Сортировка данных по абсолютным значениям

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

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

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

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

2. Затем выделите ячейки с формулой и нажмите Data > Sort A to Z или Sort Z to A, как нужно. В появившемся окне Sort Warning выберите Expand the selection, см. скриншот:

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

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

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

1. Выделите диапазон данных, который хотите отсортировать, затем нажмите Kutools Plus > Sort > Advanced Sort.

2. В диалоговом окне Advanced Sort выберите столбец для сортировки в разделе Column, затем выберите Absolute value в разделе Sort On, затем укажите порядок сортировки, см. скриншот:

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

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

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

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

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

Чтобы автоматически сортировать список чисел по возрастанию или убыванию, используйте формулы на основе функций LARGE, SMALL и ROW.

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. Кликните правой кнопкой мыши по вкладке листа, где хотите автоматически сортировать данные, затем выберите View Code в контекстном меню. В появившемся окне Microsoft Visual Basic for Applications скопируйте и вставьте следующий код в пустое окно Module, см. скриншот:

Код 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. Нажмите Insert > Module и вставьте следующий код в окно Module.

Код 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. Нажмите Insert > Module и вставьте следующий код в окно Module.

Код 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. Нажмите Insert > Module и вставьте следующий код в окно Module.

Код 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. Затем нажмите OK — диапазон данных будет перевёрнут по вертикали, как показано на скриншотах ниже:

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

Для переворота диапазона данных по горизонтали используйте следующий код 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, с помощью функций Flip Vertical Range и Flip Horizontal Range вы можете перевернуть диапазон ячеек по вертикали или горизонтали одним кликом.

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

1. Выделите диапазон данных, который хотите перевернуть, затем нажмите Kutools > Range > Flip Vertical Range > All / Only flip values, см. скриншот:

Совет: Если выбрать All, будет перевёрнуто всё форматирование ячеек; если Only flip values — только значения ячеек.

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

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

1. Выделите диапазон данных, затем нажмите Kutools > Range > Flip Horizontal Range > All / Only flip values, см. скриншот:

Совет: Если выбрать All, будет перевёрнуто всё форматирование ячеек; если Only flip values — только значения ячеек.

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

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

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

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

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

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

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

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 для запуска макроса — в появившемся окне нажмите Yes, чтобы отсортировать все листы по алфавиту по возрастанию; нажмите No — по убыванию, как нужно.

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

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

1. Откройте книгу, в которой хотите отсортировать вкладки листов, затем нажмите Kutools Plus > Worksheet > Sort Sheets, см. скриншот:

2. В диалоговом окне Sort Sheets выберите нужный тип сортировки справа, например, Alpha Sort или Alpha Numeric Sort. См. скриншот:

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

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

Обычно мы сортируем вкладки листов по алфавиту, но пробовали ли вы сортировать их по цвету? Следующий код VBA поможет решить эту задачу.

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

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

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 для выполнения кода — листы с одинаковым цветом вкладки будут сгруппированы вместе. См. скриншоты:

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

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

1. Нажмите Kutools Plus > Worksheet > Sort Sheets, чтобы открыть диалоговое окно Sort Sheets, затем нажмите кнопку Color Sort справа, см. скриншот:

2. Затем нажмите Ok — листы с одинаковым цветом вкладки будут сгруппированы вместе, как показано на скриншотах ниже: