Как сортировать строки по прописным и фильтровать строки в нижнем регистре в Excel?
При работе со списками в Excel, содержащими смесь текста в верхнем регистре, с заглавной буквы и в нижнем регистре, может возникнуть необходимость упорядочить данные именно по регистру — например, разместить сначала все записи, написанные прописными буквами, затем — с заглавной буквы и в конце — строчные (или в обратном порядке). Хотя стандартные инструменты сортировки Excel не поддерживают сортировку с учётом регистра напрямую, существуют эффективные обходные методы, позволяющие достичь нужного результата. Такая сортировка особенно полезна при обработке данных, соответствующих определённым соглашениям об именовании, кодам или идентификаторам, где регистр символов может указывать на разные категории или уровни. Ниже приведён пример такого набора данных:
Содержание
- Сортировка строк по прописным Фильтр по строкам в нижнем регистре с помощью вспомогательного столбца
- Макрос VBA для сортировки с учётом регистра без вспомогательного столбца
Сортировка строк по прописным Фильтр по строкам в нижнем регистре с помощью вспомогательного столбца
Хотя Excel изначально не поддерживает сортировку с учётом регистра, вы можете использовать формулу во вспомогательном столбце для определения регистра каждого значения, а затем отсортировать всю таблицу на основе этой информации. Этот метод особенно удобен, если вы предпочитаете работать с формулами и встроенными инструментами Excel, и его легко адаптировать даже под более сложные текстовые структуры.
1. Введите следующую формулу в пустую ячейку рядом с вашими данными — если ваши данные начинаются с ячейки A2, введите эту формулу в ячейку B2:
=IF(EXACT(A2,UPPER(A2)),"Upper Case",IF(EXACT(A2,LOWER(A2)),"Lower Case",IF(EXACT(A2,PROPER(A2)),"Proper Case","Other")))Эта формула анализирует значение в ячейке A2 и классифицирует его как «Все в верхнем регистре», «Фильтр по строкам в нижнем регистре», «Первая буква в верхнем регистре» или «Другое», если оно не соответствует ни одному из первых трёх типов. При необходимости более сложной классификации формулу можно адаптировать — её структуру вы найдёте на скриншоте ниже:
2. Используйте маркер заполнения, чтобы скопировать эту формулу вниз по столбцу для всех строк, которые нужно классифицировать. Результаты отображают регистр текста для каждого элемента вашего списка. Убедитесь, что при копировании ссылки указывают на правильные ячейки:
3. После добавления классификации по регистру в столбец B выделите весь диапазон данных, включая вспомогательный столбец (например, A1:B11). Нажмите Данные > Сортировка, затем в диалоговом окне сортировки:
- Choose the helper column (“Case” or B) в разделе Столбец
- Выберите Значенияв разделе Сортировать по
- Выберите нужный порядок сортировки («от А до Я» или «от Я до А») в зависимости от того, хотите ли вы сначала Фильтр по строкам в нижнем регистре или сначала Все в верхнем регистре
Примечание: Если в вашей таблице есть заголовки, установите флажок Включить заголовки, чтобы обеспечить корректную сортировку.
4. Нажмите кнопку ОК в диалоговом окне, чтобы выполнить сортировку. Теперь ваш список будет сгруппирован по регистру текста: при порядке сортировки «от А до Я» сначала отобразятся строки в нижнем регистре, затем — с первой буквой в верхнем регистре и, наконец, полностью заглавные («Все в верхнем регистре»). Чтобы разместить полностью заглавные строки первыми, измените порядок сортировки на обратный. Результат сортировки продемонстрирован ниже:
Советы и рекомендации:
1. После сортировки данных вы можете удалить вспомогательный столбец, чтобы сохранить лист в чистоте.
2. Чтобы изменить порядок сортировки (например, чтобы прописные буквы шли первыми), в диалоговом окне сортировки выберите «от Я до А» вместо «от А до Я».
3. При работе с длинными списками внимательно проверяйте ссылки в формуле при перетаскивании, чтобы избежать ошибок.
4. Имейте в виду: метод вспомогательного столбца не сортирует элементы внутри каждой группы по алфавиту, если вы не добавите второй уровень сортировки в диалоговом окне.
Макрос VBA для сортировки с учётом регистра без вспомогательного столбца
Если вам требуется настоящая сортировка с учётом регистра — а не просто группировка по категориям — и вы хотите обойтись без вспомогательных столбцов, встроенная сортировка Excel окажется недостаточной: она не различает регистр текста. В таких случаях на помощь приходит макрос VBA, который обеспечивает прямую сортировку данных с истинным учётом регистра. Это особенно ценно при работе с большими наборами данных, автоматизированными процессами или регулярными задачами: макрос сортирует мгновенно и может использоваться многократно.
Этот метод идеально подходит, когда вам нужен более тонкий контроль над логикой сортировки или требуется автоматизировать процесс для нескольких листов или диапазонов.
Меры предосторожности: Макросы перезапишут текущий порядок сортировки выделенного диапазона. Всегда создавайте резервную копию данных перед запуском новых кодов VBA и убедитесь, что макросы включены в вашем экземпляре Excel.
Выполните следующие действия:
1. Перейдите в раздел Инструменты разработчика > Visual Basic. В окне Visual Basic for Applications нажмите Вставка > Модуль. Вставьте следующий код в новый модуль:
Sub CaseSensitiveSort()
Dim WorkRng As Range
Dim ws As Worksheet
Dim col As Integer
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select Range to Sort:", xTitleId, WorkRng.Address, Type:=8)
col = Application.InputBox("Enter the Column Number to Sort By (e.g.,1 for first column):", xTitleId, 1, Type:=1)
Set ws = WorkRng.Worksheet
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=WorkRng.Columns(col), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange WorkRng
.Header = xlYes
.MatchCase = True
.Apply
End With
End SubЭтот макрос позволяет выбрать диапазон для сортировки, указать номер столбца, по которому нужно выполнить сортировку, и применяет сортировку с учётом регистра. Аргумент .MatchCase = True гарантирует, что прописные, строчные буквы и слова с первой заглавной буквой будут сортироваться отдельно.
2. Чтобы запустить макрос, вернитесь в основное окно Excel и нажмите клавишу F5 или кнопку «Выполнить». Вам предложат выбрать диапазон и указать номер столбца для сортировки. После подтверждения макрос выполнит сортировку с учётом регистра.
Устранение неполадок и советы:
– Если возникают ошибки, дважды проверьте выделенный диапазон и указанный номер столбца.
– Неверный номер столбца может привести к неожиданным результатам сортировки.
– Макрос работает только на активном листе; чтобы отсортировать данные на нескольких листах, повторите процедуру для каждого из них.
Это решение обеспечивает более надёжную сортировку в сложных или автоматизированных задачах, где важен учёт регистра, и идеально подходит опытным пользователям или тем, кто стремится избежать ручных операций с вспомогательными столбцами.
См. также:
Как выполнить сортировку по расширению файла в Excel?
Как отсортировать список в столбце по длине символов в Excel?
Как отсортировать данные по наиболее часто встречающемуся значению в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек