Как сортировать смешанные числа и текст, многоуровневые числа в Excel
В учебнике объясняются распространенные проблемы с упорядочением буквенно-цифровых строк в Excel и предлагаются рабочие решения для сортировки чисел как текста и текста как чисел.
Сортировка текста и чисел отдельно в Excel так же проста, как ABC или 123 🙂 Но изменение идентификаторов продуктов, артикулов и других значений, которые содержат как буквы, так и цифры, может вызвать проблемы. Алфавитная сортировка Excel не может различать числовую часть строки, чтобы обрабатывать ее отдельно. Если вы хотите лучше контролировать сортировку буквенно-цифровых строк, просмотрите приведенные ниже примеры.
Сортировка чисел как текста в Excel
При сортировке столбца чисел от наименьшего к наибольшему в Excel вместо этого:
1, 2, 3, 11, 12, 13, 110, 120, 130
иногда вы можете получить что-то вроде этого:
1, 11, 110, 12, 120, 13, 130, 2, 3
Это происходит, когда числа на самом деле являются текстом, и они сортируются как текст — подобно словам, которые расположены на основе их букв, «текстовые числа» сортируются на основе их цифр, а не их значений.
Если целевой столбец содержит числа, отформатированные как текст, все, что требуется для нормальной сортировки чисел, — это преобразовать текст в число. Результат находится в столбце C на скриншоте ниже.
Напротив, если вы хотите отсортировать числа как текст, сначала преобразуйте числа в текст, а затем щелкните вкладку «Главная» > группа «Редактирование» > «Сортировка и фильтр» > «Сортировка от А до Я».
Результат будет таким же, как в столбце A на предыдущем снимке экрана.
Как сортировать смешанные числа и текст в Excel
Благодаря встроенной функции сортировки Excel буквенно-цифровые строки, содержащие как текст, так и числа, всегда сортируются как текст, т. е. по буквам, по цифрам. Чтобы отсортировать столбец чисел, содержащих буквенный префикс или суффикс, как числа, выполните следующие действия. И вот результат, которого мы пытаемся достичь.
Чтобы Excel не обрабатывал числа в буквенно-цифровых строках как текст, мы собираемся извлечь эти числа во вспомогательный столбец и отсортировать по этому столбцу. Это позволит вам сохранить исходные данные без изменений, но изменить их так, как вам нравится.
- В столбце рядом с исходными значениями введите формулу, которая извлекает число из строки.
В этом примере мы можем просто извлечь все символы после дефиса (“-“). В Excel 365 это можно сделать с помощью функции ТЕКСТПОСЛЕ. Как и в случае с любой другой функцией Text, ее вывод всегда является текстом, независимо от того, извлекаете ли вы буквы или цифры, поэтому мы умножаем результат на 1, чтобы преобразовать текстовую строку в число.
=ТЕКСТПОСЛЕ(A2;”-“)*1
В старых версиях Excel того же результата можно добиться, используя комбинацию трех разных функций, извлекающих текст после определенного символа. Кроме того, выполняется операция *1 для преобразования вывода текста в число:
=ВПРАВО(A2, ДЛСТР(A2) – ПОИСК(“-“, A2)) *1
Кончик. Чтобы быстро извлечь числа из любой позиции в ячейке, вы можете использовать инструмент «Извлечение», включенный в наш Ultimate Suite для Excel.
- Отсортируйте исходные данные по извлеченным числам. Выбрав числа, перейдите на вкладку «Главная» > «Сортировка и фильтр» > «Сортировать от меньшего к большему». В появившемся диалоговом окне выберите параметр «Расширить выбор» и нажмите «Сортировать». Дополнительные сведения см. в разделе Как сортировать и хранить строки вместе.
Теперь строки отсортированы как числа, и при необходимости вы можете удалить или скрыть вспомогательный столбец.
Сортировка строк по тексту и числам одновременно
Если ваши исходные строки содержат несколько разных элементов, вы можете извлечь каждый элемент в отдельный столбец, а затем отсортировать по нескольким столбцам. В результате строки будут расположены, как в столбце D на скриншоте ниже:
Поскольку все строки в нашем наборе данных имеют одинаковый шаблон, самый простой способ разделить текст и числа на разные столбцы — использовать регулярные выражения. Пользовательская функция с именем RegExpExtract или Ablebits Regex Tools будет работать хорошо.
Чтобы использовать пользовательскую функцию, вам сначала нужно добавить ее код в свой Excel, как описано в этом руководстве. После этого воспользуйтесь следующими формулами.
Чтобы извлечь первое вхождение текста из ячейки A2, используйте формулу в ячейке B2:
=RegExpExtract(A2, “[^\d]+”, 1)
Чтобы извлечь первое число, формула в C2:
=RegExpExtract(A2, “\d+”, 1)*1
Чтобы извлечь второе число, формула в D2:
=RegExpExtract(A2, “\d+”, 2)*1
Во 2-й и 3-й формулах мы умножаем вывод RegExpExtract на 1, чтобы превратить извлеченную подстроку в число.
После разделения текста и чисел отсортируйте набор данных по нескольким столбцам:
- Выберите все столбцы (в нашем случае A2:D16).
- На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка.
- Добавьте столько уровней сортировки, сколько вам нужно, и выберите нужный порядок сортировки.
- Когда закончите, нажмите OK.
В нашем случае выбранный диапазон сначала сортируется по тексту от А до Я, затем по номеру 1, а затем по номеру 2 от меньшего к большему:
Результат именно то, что мы искали:
Кончик. Чтобы переупорядочить строки по-другому, измените порядок уровней в диалоговом окне «Сортировка».
Как сортировать многоуровневые/иерархические числа в Excel
В Microsoft Excel многоуровневые числа, такие как 1.1, 1.1.1, 1.1.2, являются строками и сортируются как текст, а не числа:
Чтобы отсортировать многоуровневые строки как числа, вы можете использовать пользовательскую функцию со следующим синтаксисом:
HierarchyNumber(диапазон, del, max_level)
Где:
- Диапазон — это диапазон для сортировки.
- Del — это разделитель, используемый для разделения уровней, обычно это точка (.).
- Max_level — это максимальное количество уровней в числах иерархии.
А вот и код функции:
Пользовательская функция для сортировки номеров иерархии
Функция HierarchyNumber(диапазон Как диапазон, del как строка, max_level как целое число) как Double Dim часть как Variant часть = Split(range.Value, del) For Index = 0 To UBound(part) HierarchyNumber = HierarchyNumber + part(Index) * ( 10 ^ ((max_level – Index) * 2)) Функция окончания следующего индекса
Что делает функция, так это генерирует числа, которые позволяют сортировать многоуровневые числовые строки. Точнее, он разбивает строку иерархии на отдельные числа (части), обрабатывает каждую часть на основе приведенного ниже алгоритма и суммирует результаты:
часть (индекс) * (10 ^ ((уровень – индекс) * 2))
Например, для 1.1 функция выдает такой результат:
1*(10^((2-0)*2)) + 1*(10^((2-1)*2)) = 10100
Для 1.2 это:
1*(10^((2-0)*2)) + 2*(10^((2-1)*2)) = 10200
Как сортировать номера иерархии с помощью пользовательской функции:
- Вставьте код функции HierarchyNumber в стандартный модуль кода вашей книги и сохраните его как книгу с поддержкой макросов (.xlsm). Подробная инструкция здесь.
- В пустой ячейке рядом с первой строкой иерархии введите формулу HierarchyNumber и нажмите клавишу Enter.
- Перетащите формулу вниз на столько строк, сколько необходимо.
- Отсортируйте свой набор данных на основе столбца формул.
Предположим, у вас есть многоуровневые числовые строки с максимум 3 уровнями в столбце A. Формула для B2:
=ИерархическийЧисло(A2, “.”, 3)
После копирования формулы вы получите такой результат:
После этого вы сортируете весь набор данных (A2:B19) по столбцу формулы от меньшего к большему:
И получить многоуровневые числовые строки, отсортированные как числа:
Сортировка многоуровневых чисел с префиксом текста
В этом примере обсуждается немного другой случай, когда многоуровневые номера имеют префикс с некоторым текстом, скажем, «Уровень 1.1.1». На первый взгляд задача кажется сложнее, но на самом деле решение проще 🙂
Во-первых, вы извлекаете многоуровневые числовые подстроки в отдельный столбец. Для этого вы можете использовать инструмент «Извлечение», включенный в наш Ultimate Suite, или формулу, которая извлекает все символы после пробела:
=ВПРАВО(A2, ДЛИН(A2) – ПОИСК(” “, A2))
В диалоговом окне «Сортировка Excel» добавьте два уровня — сначала по извлеченному номеру иерархии, а затем по исходной строке — и нажмите «ОК»:
Появится следующее предупреждение, где вы выбираете «Сортировать числа и числа, сохраненные как текст отдельно».
В результате строки, содержащие многоуровневые числа, сортируются по числам:
Вот как сортировать числовые строки и многоуровневые числа в Excel. Спасибо за внимание и до встречи в нашем блоге на следующей неделе!
Доступные загрузки
Сортировка смешанных чисел и текста в Excel — примеры (файл .xlsm)
Полнофункциональная пробная версия Ultimate Suite (файл .exe)