☰

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」を含むデータは「1_」になります (データ内の文字数)

関連情報