ハイフンで結ばれた数を並べ替えるには?

Question 15.1 Previous Next
いつもお世話になっております。
質問ですが、文字列(例:1-1~1-10)で並べ替えを行うと、1-1の次に1-10がきてしまいます。
私としては、1-1,1-2~1-10としたいのですが、どうすればよいでしょうか? 何か良い方法をご教授ください。
Answer   Copyright (C) 1999.3.2 永井善王
いろいろな方法が、あると思います。ここでは、ハイフンを取り除いてから並べ替える方法を考えてみましょう。
あなたのシートのレイアウトがわからないので、下図のサンプルを使って説明します。
表1: ソートする前 表2: ソートした後
表1をエクセルで普通に並べ替えすると、表2のようになってしまいます。
人間が行う場合でも、1.01と、1.10なら、誰でも間違えずに並べれますが、1-1と1-10ではどちらが小さいのか、判断に困りますね。
エクセルが判断に困ってこうなる訳ではないですが、その理由は省略します。
あなたが希望する順番にするためには、エクセルが並べ替える直前に、データの桁揃えをしてあげると良いかと思います。

 表3
表3を見ながら、説明を読んでください。

1) B列は - の位置、つまりA列のデータの左から何文字目にハイフンがあるかを表しています。
2) C列は - の前(左)にある数字を取り出してあります。
3) D列は - の後(右)に   〃      〃


・B2セルに入っている式は、
=FIND("-",A2,1) で、その意味は「A2セルに入っている文字列の1文字目(左)から順にハイフンを探して、何文字目で見つかったか」を答えてくれます。
・C2セルに入っている式は、
=LEFT(A2,B2-1) で、その意味は「A2セルに入っている文字列の左から、B2セルに入っている数より1少ない数の文字を取り出して」くれます。
・D2セルに入っている式は、
=RIGHT(A2,LEN(A2)-B2) で、その意味は「A2セルに入っている文字列の右から、A2セルに入っている文字列の文字数からB2セルに入っている数を引いた数の文字を取り出して」くれます。

A2セルの文字がハイフンの前後でC2セルとD2セルに分かれましたね。表3は、わかりやすく説明するために、式を分けて作りました。もしあなたが、エクセルの関数に慣れてなければ、この機会に納得しておいてください。後日きっと役立ちます。
なお、B3セルからD7セルまでは、B2セルからD2セルの式をコピーして貼り付けてあります。

話を進めましょう。実際には、どうすれば良いのでしょうか・・

1. 事前準備 ・・ データの入ったブックとは別に、並べ替え用のブックを作っておきます。

作り方は、表1、表2、表3と、下図の表4と表5、そしてマクロ用のモジュールシートを1つのブックに入れて、好きな名前を付けて保存します。
表4は表3と同じ式を埋め込んだシートで、右図のとおりです。 表4

表5は並べ替え用の作業シートです。マクロで一時的に使うので白紙のままで良いです。
2. マクロを組む ・・ 大きい流れは次のとおりです。
   1) データが入ったブックを開く
   2) データ(A列)を、並べ替え用ブックの表1のA列に写す
   3) 表1のA列のデータの数を調べておく
   4) 表4の式を、並べ替え用ブックの表5に、データの数だけ写す
   5) 表5のデータを並べ替える
   6) 並べ替えが済んだデータ(A列)を、表5から元のブックのA列に値だけ写す

3. マクロが組めたら、並べ替え用のブックを保存します。
  もしもマクロが組めなかったら、下記のマクロをモジュールシートにコピーペーストして、上書き保存します。
'------------------------------------------------------------------------------
Sub ハイフンで結ばれた数を並べ替える()

 本来はここで「元シートから表1へデータを写す」マクロを書く

    Sheets("表5").Select                        '作業用のシートを選択する
        Cells.Clear                             'すべてクリア
    Sheets("表1").Select                        'データの入ったシートを選択する
        下端 = Range("A1").End(xlDown).Row      '下端検出
        Range(Cells(1, 1), Cells(下端, 1)).Copy 'A列のデータをコピーする
    Sheets("表5").Select                       '作業用のシートを選択する
        Range("A1").PasteSpecial Paste:=xlAll   'データをすべて貼り付ける

    Sheets("表4").Select
        Range("B1:D2").Copy                     '列タイトルと式をコピーする
    Sheets("表5").Select
        Range("B1").PasteSpecial Paste:=xlAll   '列タイトルと式を貼り付ける

        Range("B2:D2").Copy                     'コピーする
        Range(Cells(3, 2), Cells(下端, 4)).PasteSpecial Paste:=xlAll '貼り付ける

    Sheets("表5").Select
        Range("A1").Select
        Selection.SortSpecial SortMethod:=xlSyllabary, Key1:=Range("C2"), _
        Order1:=xlAscending, Key2:=Range("D2"), Order2:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation _
         :=xlTopToBottom                    'ソートする(第1キー:C列、第2キー:D列)

    Sheets("表5").Select
        Range(Cells(2, 1), Cells(下端, 1)).Copy 'ソート済みのデータをコピーする
        Application.CutCopyMode = False         'コピーモード解除
        Range("A1").Select                      '左上角セルに位置付け

 本来はここで「表5から元シートへ値だけを写す」マクロを書く

End Sub
'------------------------------------------------------------------------------
4. でき上がったマクロを、試しに実行してみてください。 表5

右図のように、表5ができれば成功です。

上記のマクロは、元シートから表1へデータを写す部分と、できあがった表5から元シートへ値だけを写す部分を、作ってありません。それは、マクロを何度でも試せるようにしておきたいからです。
マクロの動きの確認ができたら、このマクロ集の「コピー」のページを参考にして、自分で作ってみてください。

あなたが Internet Explorer 4.0と、Excel97または95がインストールされたパソコンで、このHPを見ているのでしたら、ここをクリック すればエクセルが起動して、サンプルブックが開きます。数式やセルの書式設定を、すぐに見ることができます。
見終ったら、エクセルシートが映っている画面右上角の × をクリックすれば、この画面へもどれます。

 

Excel VBA Macro