Как сортировать смешанные числа и текст, многоуровневые числа в 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 на скриншоте ниже.
Сортировка чисел в Excel.

Напротив, если вы хотите отсортировать числа как текст, сначала преобразуйте числа в текст, а затем щелкните вкладку «Главная» > группа «Редактирование» > «Сортировка и фильтр» > «Сортировка от А до Я».
Сортировка чисел как текста в Excel

Результат будет таким же, как в столбце A на предыдущем снимке экрана.

Как сортировать смешанные числа и текст в Excel

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

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

  1. В столбце рядом с исходными значениями введите формулу, которая извлекает число из строки.

    В этом примере мы можем просто извлечь все символы после дефиса (“-“). В Excel 365 это можно сделать с помощью функции ТЕКСТПОСЛЕ. Как и в случае с любой другой функцией Text, ее вывод всегда является текстом, независимо от того, извлекаете ли вы буквы или цифры, поэтому мы умножаем результат на 1, чтобы преобразовать текстовую строку в число.

    =ТЕКСТПОСЛЕ(A2;”-“)*1

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

    =ВПРАВО(A2, ДЛСТР(A2) – ПОИСК(“-“, A2)) *1
    Извлечь число из буквенно-цифровой строки.

    Кончик. Чтобы быстро извлечь числа из любой позиции в ячейке, вы можете использовать инструмент «Извлечение», включенный в наш Ultimate Suite для Excel.

  2. Отсортируйте исходные данные по извлеченным числам. Выбрав числа, перейдите на вкладку «Главная» > «Сортировка и фильтр» > «Сортировать от меньшего к большему». В появившемся диалоговом окне выберите параметр «Расширить выбор» и нажмите «Сортировать». Дополнительные сведения см. в разделе Как сортировать и хранить строки вместе.
    Сортировать строки по номерам.

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

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

Если ваши исходные строки содержат несколько разных элементов, вы можете извлечь каждый элемент в отдельный столбец, а затем отсортировать по нескольким столбцам. В результате строки будут расположены, как в столбце 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, чтобы превратить извлеченную подстроку в число.
Извлекайте числа и текст в разные столбцы.

После разделения текста и чисел отсортируйте набор данных по нескольким столбцам:

  1. Выберите все столбцы (в нашем случае A2:D16).
  2. На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка.
  3. Добавьте столько уровней сортировки, сколько вам нужно, и выберите нужный порядок сортировки.
  4. Когда закончите, нажмите OK.

В нашем случае выбранный диапазон сначала сортируется по тексту от А до Я, затем по номеру 1, а затем по номеру 2 от меньшего к большему:
Сортировка смешанного текста и чисел в Excel.

Результат именно то, что мы искали:
Буквенно-цифровые строки сортируются по тексту и цифрам.

Кончик. Чтобы переупорядочить строки по-другому, измените порядок уровней в диалоговом окне «Сортировка».

Как сортировать многоуровневые/иерархические числа в Excel

В Microsoft Excel многоуровневые числа, такие как 1.1, 1.1.1, 1.1.2, являются строками и сортируются как текст, а не числа:
Сортировка чисел иерархии в Excel

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

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

Как сортировать номера иерархии с помощью пользовательской функции:

  1. Вставьте код функции HierarchyNumber в стандартный модуль кода вашей книги и сохраните его как книгу с поддержкой макросов (.xlsm). Подробная инструкция здесь.
  2. В пустой ячейке рядом с первой строкой иерархии введите формулу HierarchyNumber и нажмите клавишу Enter.
  3. Перетащите формулу вниз на столько строк, сколько необходимо.
  4. Отсортируйте свой набор данных на основе столбца формул.

Предположим, у вас есть многоуровневые числовые строки с максимум 3 уровнями в столбце A. Формула для B2:

=ИерархическийЧисло(A2, “.”, 3)

После копирования формулы вы получите такой результат:
Пользовательская функция для сортировки многоуровневых чисел

После этого вы сортируете весь набор данных (A2:B19) по столбцу формулы от меньшего к большему:
Отсортируйте многоуровневые строки по столбцу формулы.

И получить многоуровневые числовые строки, отсортированные как числа:
Многоуровневые числовые строки сортируются как числа.

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

В этом примере обсуждается немного другой случай, когда многоуровневые номера имеют префикс с некоторым текстом, скажем, «Уровень 1.1.1». На первый взгляд задача кажется сложнее, но на самом деле решение проще 🙂

Во-первых, вы извлекаете многоуровневые числовые подстроки в отдельный столбец. Для этого вы можете использовать инструмент «Извлечение», включенный в наш Ultimate Suite, или формулу, которая извлекает все символы после пробела:

=ВПРАВО(A2, ДЛИН(A2) – ПОИСК(” “, A2))
Извлечь многоуровневые числовые подстроки в отдельный столбец.

В диалоговом окне «Сортировка Excel» добавьте два уровня — сначала по извлеченному номеру иерархии, а затем по исходной строке — и нажмите «ОК»:
Отсортируйте набор данных по извлеченному номеру иерархии и исходной строке.

Появится следующее предупреждение, где вы выбираете «Сортировать числа и числа, сохраненные как текст отдельно».
Сортировка чисел и чисел, хранящихся в виде текста, отдельно.

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

Вот как сортировать числовые строки и многоуровневые числа в Excel. Спасибо за внимание и до встречи в нашем блоге на следующей неделе!

Доступные загрузки

Сортировка смешанных чисел и текста в Excel — примеры (файл .xlsm)
Полнофункциональная пробная версия Ultimate Suite (файл .exe)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *