☰

Как отсортировать числа в виде списка в Excel?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 sorting
Как отсортировать числа в виде списка в Excel?

У меня есть столбец «чисел» в Excel, который я хотел бы отсортировать следующим образом:

1.1
1.2
1.2.1
1.2.6
1.2.9
1.2.10
1.2.11
1.3

Однако независимо от типа ячейки — числовой или текстовый, Excel сортирует их следующим образом:

1.1
1.2
1.2.1
1.2.10
1.2.11
1.2.6
1.2.9
1.3

Это также можно назвать «естественной сортировкой» — в языке программирования, таком как PHP, это достигается с помощьюnatsort функция. Но я не могу найти способ сортировки таким образом в Excel.

Для более подробного пояснения контекста: эти числа представляют разделы и подразделы — не десятичные дроби, а скорее элементы списка:

1. Section
    1. Sub-section
    2. Sub-section
        1.  Item
        6.  Item
        9.  Item
        10. Item
        11. Item
    3. Sub-section

решение1

Вы можете составить таблицу для разделения значений:

Для каждого столбца используйте заголовки (строка 1) и формулы (строка 2+):

A1: text    A2: (your section numbers)
B1: dot1    B2: =FIND(".",A2,1)  
C1: dot2    C2: =IFERROR(FIND(".",A2,B2+1),LEN(A2)+1)  
D1: num1    D2: =VALUE(MID(A2,1,B2-1))  
E1: num2    E2: =IFERROR(VALUE(MID(A2,B2+1,C2-B2-1)),0)  
F1: num3    F2: =IFERROR(VALUE(MID(A2,C2+1,LEN(A2)-C2)),0)

Это выглядит так:

      A      B      C      D      E      F

1   text   dot1   dot2   num1   num2   num3
2   1.1       2      4      1      1      0
3   1.1.3     2      4      1      1      3
4   2.10.7    2      5      2     10      7

Затем вы можете выполнить пользовательскую сортировку по столбцам num1, num2 и num3.

решение2

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

1.2.3вернется 1,002,003, пока

1.51.5.5возвращает 1,051,005.005- и эти числа затем можно использовать для сортировки.

Я считаю, что это менее обременительно, чем использование строковых функций на рабочем листе или сортировка по нескольким столбцам.

UDF выглядит следующим образом:

Function LList(stInVal As String) As Double

Dim iPower          As Integer
Dim vSplit          As Variant
Dim i               As Long

iPower = 6

vSplit = Split(stInVal, ".", -1)

For i = 0 To UBound(vSplit)
    LList = LList + CInt(vSplit(i)) * 10 ^ (iPower - 3 * i)
Next i

End Function

решение3

Дуг подробно объясняет это. Надеюсь, это понятно!

http://youtu.be/74WdfqAB8mY

решение4

Только что столкнулся с той же проблемой...

если вам нужно быстрое решение (и вас не смущает использование «.», то просто найдите все «.» и замените на «_» — это позволит Excel отсортировать данные в иерархическом порядке.

сортировка почти заработает... теперь все, что вам нужно сделать, это добавить завершающее «подчеркивание» к вашим данным... так данные с одним символом «1» станут «1_» (к количеству символов в ваших данных)

Связанный контент