Как отсортировать список чисел, разделённых запятыми, в Excel?
Сортировка списка значений по возрастанию или убыванию — одна из самых распространённых задач в Excel. Однако всё усложняется, когда числа находятся в одной ячейке и разделены запятыми, как на скриншоте ниже. Многие пользователи не знают, как отсортировать такие числа внутри отдельной ячейки, ведь стандартные инструменты сортировки Excel работают со строками или столбцами, а не с содержимым конкретной ячейки. В этой статье мы рассмотрим несколько практичных решений, которые позволят быстро и эффективно сортировать числа, разделённые запятыми, прямо внутри ячеек — экономя ваше время и минимизируя ручной труд при работе в Excel.
Сортировка значений, разделённых запятыми, с помощью функции «Текст по столбцам»
Сортировка значений, разделённых запятыми, с помощью Kutools для Excel 
Сортировка значений, разделённых запятыми, с помощью кода VBA
Сортировка значений, разделённых запятыми, с помощью функции «Текст по столбцам»
Чтобы отсортировать числа, разделённые запятыми в одной ячейке, сначала нужно временно разбить их на отдельные ячейки, отсортировать и (при необходимости) снова объединить. С помощью функции Excel «Текст по столбцам» вы быстро разделите данные, ограниченные запятыми, по отдельным столбцам — а затем сможете транспонировать, отсортировать и дополнительно обработать их. Этот подход отлично подходит, если вы готовы к небольшим преобразованиям ячеек и не против выполнить несколько простых ручных действий.
Область применения: этот метод лучше всего подходит пользователям, работающим с небольшими и средними списками и не выполняющим эту операцию часто, так как он требует нескольких ручных шагов и может быть неудобен для динамических или регулярно обновляемых данных.
Несколько важных моментов: убедитесь, что справа от выбранного столбца достаточно пустых ячеек, чтобы избежать перезаписи существующих данных при использовании функции «Текст по столбцам». Проверьте, есть ли пробелы после запятых в ваших данных — они могут повлиять на результат разбиения и потребовать последующего удаления. Обратное объединение отсортированных значений в одну ячейку в рамках этого метода не выполняется автоматически и потребует дополнительных действий.
1. Выделите ячейки, которые нужно разделить по запятым, и нажмите Данные > Текст по столбцам. В шаге 1 мастера преобразования текста в столбцы выберите параметр с разделителями, чтобы указать, что текст будет разделяться по запятым. См. скриншот:
2. Нажмите Далее, чтобы перейти к шагу 2 мастера. Установите флажок напротив разделителя Запятая, затем снова нажмите Далее, чтобы перейти к шагу 3 мастера. Здесь укажите ячейку, в которую следует вывести разделённые значения, убедившись, что в области размещения списка достаточно свободного места справа. См. скриншот:
3. Нажмите Готово, и значения разделятся по соседним столбцам. Проверьте, правильно ли разделились числа; если нет — убедитесь, что в исходных данных отсутствуют пробелы в начале или конце. См. скриншот:
4. Чтобы подготовиться к сортировке, выделите все ячейки с разделёнными данными, нажмите Ctrl + C, чтобы скопировать, затем щёлкните правой кнопкой мыши по пустой области и выберите Вставить специально > Транспонировать. В результате числа будут расположены вертикально в одном столбце. См. скриншот:
После транспонирования ваши числа должны выглядеть следующим образом:
5. Теперь выделите столбец с транспонированными значениями. Перейдите на вкладку Данные и нажмите нужный вариант сортировки в группе Сортировка и фильтр (по возрастанию или убыванию). Если появится диалоговое окно Предупреждение о сортировке, выберите Продолжить с текущим выделением и подтвердите, нажав ОК.
Выбранный столбец теперь отсортирован в указанном порядке. Чтобы обработать несколько ячеек, повторите эти действия для каждой из них.
6. Повторите описанный выше процесс для каждого набора транспонированных значений. Учтите, что этот метод выполняется вручную и идеально подходит для коротких списков.
Совет: после сортировки, если вы хотите снова объединить числа в одну ячейку через запятую, воспользуйтесь функцией TEXTJOIN (в Excel 2016 или новее) или объедините элементы вручную с помощью оператора СЦЕПИТЬ. Обратите внимание на типичные ошибки, такие как пропущенные значения или неправильное размещение разделителей.
Сортировка значений, разделённых запятыми, с помощью Kutools для Excel
При работе с большими наборами данных или при регулярном выполнении подобных задач инструменты, такие как Kutools для Excel, предлагают быстрый и удобный способ разделения и сортировки значений, разделённых запятыми, прямо в ячейках. С помощью утилиты Разделить ячейки вы значительно сократите объём ручной работы. Этот подход идеально подходит пользователям, стремящимся оптимизировать рабочий процесс и избавиться от рутинных операций.
После установки Kutools для Excel выполните следующие шаги:
1. Выделите диапазон, содержащий значения, разделённые запятыми, которые нужно обработать. Нажмите Kutools > Объединить и разделить > Разделить ячейки. В диалоговом окне Разделить ячейки установите флажок напротив пункта Разделить на строки в разделе Тип и выберите пункт Другой в разделе Разделить по. Обязательно укажите запятую (),) в качестве разделителя. См. скриншот:
2. Нажмите ОК. Появится диалоговое окно с запросом выбора ячейки для размещения разделённых значений. Укажите ячейку назначения, под которой достаточно пустых строк, и подтвердите нажатием ОК. Ваши числа, разделённые запятыми, теперь будут разбиты по отдельным строкам. См. скриншот:
3. Выделите один из разделённых столбцов или строк, которые нужно отсортировать, затем перейдите в меню Данные > Сортировка от наименьшего к наибольшему или Сортировка от наибольшего к наименьшему. В диалоговом окне Предупреждение о сортировке снова выберите Продолжить с текущим выделением и нажмите ОК. См. скриншот:
4. При необходимости повторите шаг 3, чтобы отсортировать дополнительные столбцы. После сортировки вы сможете дополнительно обработать или объединить данные по мере необходимости с помощью таких формульных инструментов, как TEXTJOIN.
Совет: этот метод особенно полезен для повторяющихся задач — Kutools автоматизирует множество ручных этапов разделения. Если вы работаете с длинными списками или вам нужно обрабатывать сразу несколько ячеек, это решение существенно повысит вашу эффективность.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Демонстрация: сортировка значений, разделённых запятыми, с помощью Kutools для Excel
Сортировка значений, разделённых запятыми, с помощью кода VBA
Тем, кто стремится полностью автоматизировать процесс независимо от версии Excel, подойдёт простой макрос VBA для разделения, сортировки и повторного объединения чисел, разделённых запятыми, в любой ячейке. Это решение идеально для пользователей, регулярно работающих с повторяющимися задачами или большими объёмами данных, где ручные методы становятся неэффективными.
Преимущества: VBA позволяет эффективно обрабатывать большие объёмы данных, выполнять операции сразу с множеством ячеек и при необходимости легко повторять их. Однако для запуска макросов необходимо включить поддержку макросов в книге и обладать базовыми навыками работы в редакторе VBA. В многопользовательской или высокоавтоматизированной среде этот подход может оказаться наиболее масштабируемым.
Примечание: Всегда сохраняйте свою работу перед запуском новых макросов и, по возможности, тестируйте макрос на копии данных — это поможет избежать случайной потери информации.
1. Нажмите Инструменты разработчика > Visual Basic, чтобы открыть редактор Microsoft Visual Basic для приложений. В редакторе выберите Вставка > Модуль и вставьте следующий код в модуль:
Sub SortCommaSeparatedNumbers()
Dim rng As Range
Dim cell As Range
Dim arr As Variant
Dim sortedArr As Variant
Dim temp As String
Dim i As Long, j As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select range to sort comma-separated numbers", xTitleId, rng.Address, Type:=8)
Application.ScreenUpdating = False
For Each cell In rng
If Not IsEmpty(cell.Value) Then
arr = Split(cell.Value, ",")
' Bubble Sort
For i = LBound(arr) To UBound(arr) - 1
For j = i + 1 To UBound(arr)
If Val(arr(i)) > Val(arr(j)) Then
temp = arr(i)
arr(i) = arr(j)
arr(j) = temp
End If
Next j
Next i
cell.Value = Join(arr, ",")
End If
Next cell
Application.ScreenUpdating = True
MsgBox "Sorting done!", vbInformation, xTitleId
End Sub2. Закройте редактор VBA, вернитесь в Excel и нажмите Alt + F8, выберите SortCommaSeparatedNumbers, затем нажмите Выполнить. Появится диалоговое окно с запросом на выбор диапазона ячеек для обработки. После того как вы выберете диапазон и подтвердите его, скрипт обработает каждую ячейку, автоматически отсортирует содержащиеся в ней числа и объединит их обратно через запятые.
Устранение неполадок и советы: Этот макрос сортирует значения как числа — даже если после запятых стоят пробелы. Если ячейки содержат нечисловые элементы, макрос всё равно выполнит их сортировку, но результат может оказаться неожиданным. Чтобы сортировать по убыванию, замените оператор сравнения в коде с If Val(arr(i)) > Val(arr(j)) Then на If Val(arr(i)) < Val(arr(j)) Then. Если ваш список содержит текст или смешанные данные, рекомендуем добавить проверку типов. Обязательно создавайте резервную копию данных перед выполнением пакетных операций.
В заключение, сортировать числа, разделённые запятыми внутри ячеек, в Excel можно несколькими способами — каждый из них оптимален для разных сценариев и версий программы. Функция «Текст по столбцам» или надстройка Kutools отлично подходят для ручной обработки и решений с использованием готовых инструментов, тогда как VBA обеспечивает эффективную автоматизацию, особенно при работе с часто обновляемыми или объёмными наборами данных. Выбирайте наиболее подходящий метод, ориентируясь на специфику задачи, объём данных и версию 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек