8

В Excel можно сортировать буквенно-цифровые символы из a-1, a-2, a-3 ... a-123 вместо a-1, a-10, a-100, a-11?

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

Я застрял.

2 ответа2

2

Результат

Пользователь @ fixer1234 прав, вы, вероятно, хотите использовать строковые функции. Вот один из способов сделать это.

Шаг 1

[Обновлено]

В столбце "Числа" выделите диапазон, а затем разделите текст на дефис: do ...

Data > Text to Columns > с Delimited > Next > Other: - > Finish

Обратите внимание, что в текстовом поле Other: вам нужен дефис (-). И убедитесь, что соседний столбец (справа) пуст, прежде чем сделать это, чтобы не перезаписывать важные данные.

Вы также можете использовать эту функцию для извлечения числа из столбца A:

=RIGHT(A2,LEN(A2)-SEARCH("-",A2))

Шаг 2

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

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

[ОБНОВЛЕНИЕ] Вы можете использовать следующую функцию (хотя пока мы не совсем знаем, почему), чтобы определить самое длинное число:

=MAX(INDEX(LEN(C2:C14),,1))

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

=MAX(LEN(C2:C14))

(Убедитесь, что диапазон точный для конкретной таблицы, которую вы используете.)

После нажатия CTRL-SHIFT-ENTER содержимое ячейки изменится на это, но ввод этого вручную ничего не изменит:

{=MAX(LEN(C2:C14))}

Однако вы хотите сделать это хорошо. Просто определите, сколько цифр составляют наибольшее число в вашем списке: "1", конечно, имеет 1 цифру, "10" имеет 2 цифры, "100" имеет 3 цифры и так далее.

Шаг 3

Наконец, в столбце "Развернуть" эта функция преобразует числа из столбца "Числа" в текст с числом предшествующих нулей, которое вы определили, которое следует использовать на шаге 2.

=TEXT(C2,"000")

Убедитесь, что вы поставили кавычки вокруг нулей.

Если наибольшее число имеет 8 цифр, то ваша функция будет выглядеть так:

=TEXT(C2,"00000000")
1

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

Предположим, префикс «a-» никогда не меняется, записи в столбце A начинаются со строки 2, а столбец B доступен. В B2 вы должны поместить что-то вроде: = value(mid(a2,3, len(a2) -2)) и затем скопировать формулу по мере необходимости. Для сортировки выделите оба столбца и выполните сортировку по B.

Если могут быть другие префиксы, скажем, b-, c- и т.д., Аналогичным образом разделите текстовую часть на другой столбец. Затем отсортируйте текстовый столбец в качестве первого столбца сортировки и числовой столбец в качестве второго.

Все, что вы выделите, будет отсортировано по столбцам сортировки.

Чтобы объяснить строковые функции, начните с функции mid, которая извлекает строку символов из строки символов. mid(a2,3, len(a2) -2) просматривает текст в a2, начинается с третьего символа (первая цифра, предполагая, что все префиксы «a-»), а затем принимает количество символов, которое на два меньше, чем число в исходном тексте (длина исходного текста минус символы "a-"). Затем функция значения превращает этот результат в число, а не в строку числовых символов.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .