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

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

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

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

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

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

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

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

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

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

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

После щелчка по этому Сортировать кнопка, а Сортировать появится диалоговое окно, как показано на скриншоте ниже:

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

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

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

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

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

1.4 Параметры сортировки по щелчку правой кнопкой мыши

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

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

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

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

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

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

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

• В Column раздел, выберите имя столбца, по которому вы хотите отсортировать;
• В Сортировать по раздел, выберите Значения ячеек вариант;
• В Оформить заказ раздел укажите порядок сортировки. (Для сортировки текстовых строк выберите А to Z or От Z до A; Чтобы отсортировать список номеров, выберите Самый маленький до самого большого or От наибольшего к наименьшему; Чтобы отсортировать ячейки даты, выберите От старых к новым or Новых к старым.)
• Советы: Если в ваших данных есть заголовки, убедитесь, что У моих данных есть заголовки проверено. Если у ваших данных нет заголовков, снимите этот флажок.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. Продолжайте нажимать на Опции в этом диалоговом окне и в Параметры сортировки окно подсказки, отметьте Деликатный случай вариант, см. снимок экрана:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

После установки Kutools for Excel, пожалуйста, сделайте так:

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы быстро и легко отсортировать данные по количеству символов, я порекомендую удобный инструмент - Kutools for Excel, С его Расширенная сортировка вы легко справитесь с этой задачей.

После установки Kutools for Excel, пожалуйста, сделайте так:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

После установки Kutools for Excel, пожалуйста, сделайте так:

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

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

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

3, Затем нажмите OK кнопка, столбец ФИО был отсортирован сразу по фамилиям.

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

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

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

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

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

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

2. Не снимая выделения с ячеек формулы, нажмите Данные > Сортировка от А до Я or Сортировка Z по A как вам нужно, в Предупреждение о сортировке диалоговое окно, отметьте Расширить выбор, см. снимок экрана:

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

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

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

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

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите весь диапазон данных, который вы хотите отсортировать, а затем щелкните Кутулс Плюс > Сортировать > Расширенная сортировка.

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

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

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

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

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

 Отсортируйте два столбца с одинаковыми элементами, чтобы они соответствовали

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

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

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

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

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

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 и нажмите Shift + Ctrl + 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, а затем нажмите Shift + Ctrl + 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 для приложений окно.

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

4, Затем нажмите F5 ключ для запуска этого кода, данные в каждой строке были немедленно отсортированы в порядке возрастания, см. снимок экрана:

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

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

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

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

2. Затем введите приведенную ниже формулу в пустую ячейку - F3 и нажмите Shift + Ctrl + 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 для приложений окно.

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

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

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

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

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

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

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

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

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

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

После установки Kutools for Excel, пожалуйста, сделайте так:

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

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

• Целые строки: Случайным образом перемешать все строки в выбранном диапазоне.
• Целые столбцы: Случайным образом перемешать все столбцы в выбранном диапазоне.
• Ячейки в каждой строке: Перемешать ячейки в каждой строке отдельно.
• Ячейки в каждом столбце: Перемешать ячейки в каждом столбце по отдельности.
• Все ячейки в диапазоне: Рандомизировать все ячейки в выбранном диапазоне.

3, Затем нажмите Ok кнопка, данные сразу же рандомизируются.

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

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

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

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

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

=MONTH(B2)        (extract month number)
= ДЕНЬ (B2)              (извлеките номер дня)

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

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

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

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

После установки Kutools for Excel, выполните следующие действия:

1. Выберите диапазон данных, который вы хотите отсортировать, а затем щелкните Кутулс Плюс > Сортировать > Расширенная сортировка.

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

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

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

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

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

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

=TEXT(B2,"MMDD")

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

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

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

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

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

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

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

=WEEKDAY(B2)           (Start from Sunday (1) to Saturday(7))
= ДЕНЬ НЕДЕЛИ (B2,2)       (Начало с понедельника (1) по воскресенье (7))

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

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

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

С помощью Kutools for ExcelАвтора Расширенная сортировка вы можете решить эту проблему как можно быстрее без какой-либо вспомогательной формулы.

После установки Kutools for Excel, пожалуйста, сделайте так:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

После установки Kutools for Excel, пожалуйста, сделайте так:

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

2. В Расширенная сортировка диалоговом окне щелкните столбец, который вы хотите отсортировать по Column раздел, а затем выберите Нечетное и четное число из Сортировать по раздел, затем укажите порядок сортировки (А to Z сортировать от четных до нечетных чисел, и От Z до A отсортировать от нечетных к четным числам), см. снимок экрана:

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

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

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

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

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

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

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

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

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

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

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

После установки Kutools for Excel, выполните следующие действия:

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

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

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

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

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

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

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

Чтобы автоматически отсортировать список чисел в порядке возрастания или убывания, вы можете использовать формулы, основанные на функциях LERGE, SMALL и ROW.

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

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

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

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

4.2 Автосортировка текстовых значений в столбце с формулами

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

1. Введите или скопируйте приведенную ниже формулу в пустую ячейку рядом со столбцом данных и нажмите Shift + Ctrl + 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 для приложений окна, скопируйте и вставьте следующий код в пустое Модули окно, см. снимок экрана:

Код 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»; Или для сортировки нескольких слов, разделенных запятой в ячейке, например, для сортировки «слово, перспективы, превосходство, доступ» как «доступ, превосходство, перспективы, слово». В этом разделе будет рассказано о том, как решить этот тип сортировки в Excel.

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

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

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

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 для приложений окно.

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; ","; ИСТИНА) в пустую ячейку, в которую нужно вернуть результат, а затем перетащите дескриптор заполнения вниз, чтобы скопировать формулу в другие ячейки, все текстовые строки в ячейках будут отсортированы в алфавитном порядке, см. снимок экрана:

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

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 для приложений окно.

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, Затем нажмите 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 for Excel, С его Отразить вертикальный диапазон и Отразить горизонтальный диапазон функции, вы можете изменить диапазон ячеек по вертикали и горизонтали одним щелчком мыши.

После установки Kutools for Excel, пожалуйста, сделайте так:

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

1. Выберите диапазон данных, который вы хотите изменить, а затем нажмите Кутулс > Диапазон > Отразить вертикальный диапазон > Все / Только перевернуть значения, см. снимок экрана:

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

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

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

1. Выберите диапазон данных и нажмите Кутулс > Диапазон > Отразить горизонтальный диапазон > Все / Только переворачивать значения, см. снимок экрана:

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

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 for Excel предоставляет мощную функцию - Сортировать листыс помощью этой функции вы можете сортировать листы в алфавитном или буквенно-цифровом порядке, сортировать листы по цвету вкладок или менять местами вкладки листов по мере необходимости.

После установки Kutools for Excel, выполните следующие действия:

1. Откройте книгу, в которой нужно отсортировать вкладки листов, и щелкните Кутулс Плюс > Рабочий лист > Сортировать листы, см. снимок экрана:

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

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

 Сортировка вкладок рабочего листа на основе цвета вкладки с кодом 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 for ExcelАвтора Сортировать листы вы также можете легко и быстро сортировать вкладки рабочего листа по цвету.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Нажмите Kutools Дополнительная > Рабочий лист > Сортировать листы пойти Сортировать листы диалоговое окно в Сортировать листы диалоговое окно, нажмите Цвет Сортировка на правой панели, см. снимок экрана:

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

---

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

• Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
• Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение... Предотвращение дублирования ячеек; Сравнить диапазоны...
• Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
• Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
• Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
• Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
• Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
• Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
• Группировка сводной таблицы по номер недели, день недели и другое ... Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя...

Больше информации... Полнофункциональная 30-дневная бесплатная пробная версия ... Покупка ... 

---

Вкладка Office - предоставляет интерфейс с вкладками в Office и значительно упрощает вашу работу

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

Больше информации... Полнофункциональная 30-дневная бесплатная пробная версия ... Покупка ...