|
|
| Excel VBA Macro ・ 値の操作 ・ FAQ |
|
ハイフンで結ばれた数を並べ替えるには？
|
|
|
|
Question 15.1 |
|
 |
 |
|
|
いつもお世話になっております。
質問ですが、文字列（例：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を見ているのでしたら、ここをクリック すればエクセルが起動して、サンプルブックが開きます。数式やセルの書式設定を、すぐに見ることができます。
見終ったら、エクセルシートが映っている画面右上角の × をクリックすれば、この画面へもどれます。
|
|
|
|
|
