セルの中の数字の並べ替え

質問

こんにちは　いつもお世話になっています
エクセル2003を使っています。
本の索引を作ってる途中です。
A列に文字列、B列にページ数が入力されています。
B列の数字はページ数ごとに小数点で区切ってあります。表示形式は標準です。（そのためでしょうか、例えば下記の北海道でページ数が一つのセルは小数点がつかずにセルの右端に入力されます）
例えば、

A 　　　　　　　　　B
青森 　　　　　　321.24.369.125.45.
岩手 　　　　　　65.54.65.214.114.
北海道 　　　　　　　　32
宮城 　　　　　　45.245.165.
秋田
東京 　　　　　　132.45.87.63.

こんな感じで350行ぐらいあります。項目によってはB列にデータが無いセルもあります。
ここで質問なのですが、
B列の各セルの中で小数点で区切られた数字ごとに左から昇順で並べ替える方法を教えてください。
現在はセルごとにワードに貼り付けて小数点を置換で改行し縦にしてからソートするなどして大変手間がかかります。
よろしくお願いします。

回答No.3

web2525 · ベストアンサー

セル内にドットで区切られた数値データーの並び替え自体を間数式やマクロで行おうとすると結構複雑な処理が必要になると思われます

EXCELの標準機能のみで実現する方法として

1.B列を選択してコピー
2.新たなシートを追加してA列に貼りつけ
3.A列を選択した状態で[データー]⇒[区切り位置]でドット区切りでデーターの分割
4.行単位でソート
※並び替えは行単位で実施する必要があるため、この部分の連続動作のみマクロ化
Sub Macro1()
Rows("1:1").Select
For i = 1 To 350　'データー個数に合わせてループ回数は修正してください
Selection.Sort Key1:=Range("A" & i), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
Selection.Offset(1, 0).Select
Next
End Sub

5.並び替えが終了したシートをCSV形式で保存
6.保存したファイルをメモ帳で開きカンマ[,]をドット[.]に置き変え
7.連続したドット[..]をドット[.]に置き変え
※連続したドットがなくなるまで繰り返してください

8.全体を選択して元データーのB列に貼り付け
※この際に3で実施した区切り位置の設定が残っている場合、ドット区切りでデーターが分割されてしまいますが、テキストウィザードで固定長を選択すればお望みの形式で貼り付けされます

この操作も面倒と感ずるようであれば、初めからデーターの修正がしやすい形式で保存する工夫をする方法を考える方がいいです

お礼

web2525　様　ありがとうございました。お蔭様で解決しました。
丁寧に教えていただき助かりました。
簡単で恐縮ですが、お礼申し上げます。

回答No.7

imogasi

以下のことを反省してください。エクセルで
（１）並べ替えとは「セルの」順序を並びかえるのだ
（２）なにで並べ替えるかというと基本はセルの値（数値か文字コード）で並べ替えるのだ。（ユーザー定義の順の並べ替えやふりがな順で並べ替えられるのは、エクセルが裏で細工してるからである）色の順なども出現するかもしれないがそれも同じで、基本はセルのあたいだ。
ーー
本件は１セルの中の区切り文字で区切った部分を単位にして並べ替えたいということのようだが、そのままでは、エクセルで並べ替えられるはずが無い。
ーー
そこ各セルに、データー区切り位置で分離して並べ替えるべきだ。
プログラムの出来る人は、変数や配列に区切った値を入れて、プログラムでソートするなどして並べ替えられるが、質問者は経験無いでしょう。
ーー
ただし、各セルに分離しても以外に列でソートするのは難しそうだ。
列ソートはエクセルの本筋ではなく、あまり使わなかったが、どうも多数行ではうまくいかない。
其れで各行のソートの繰り返しをVBAでやってみた。
外に良いやり方があるか、既出・今後の回答に良い回答が出ないかを見てください。
ーー
例データ（分離後）　A-F列
A列でソートをとりあえず行う（アイウエオ順やABC順で）そのソート後が下記とする。数字はページ数のつもり
a 212 34 12 15 213
b 12 37 34 67
c 56 76 31
としてみる。こういうデータが凸凹状態（不揃い）の場合なのでVBAでやってみた
ーー
マクロの記録を取って、少々変更し、行ごとの繰り返しをしている。
Sub Macro3()
d = Range("B65536").End(xlUp).Row
'MsgBox d '最終行
For i = 2 To d 'データは第２行目からとする
r = Range("iv" & i).End(xlToLeft).Column '各行の右端の列をとらえる
'MsgBox r
'以下ソート　マクロの記録から少し修正
Range(Cells(i, "B"), Cells(i, r)).Select
Selection.Sort Key1:=Cells(i, "B"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, SortMethod _
:=xlStroke, DataOption1:=xlSortNormal
Next i
End Sub
ーー
結果
a 12 15 34 212 213 213
b 12 34 37 67
c 31 56 76

お礼

imogasi　様　ありがとうございました。
丁寧に教えていただき助かりました。
せっかく教えていただいたのですがこちらの能力不足で使いこなせませんでした。
ページ数を分割後、索引項目をつけたまま実行すると成功する行もあるのですが、索引項目もソートされる行もありました。索引項目を除いて実行するとソートに成功していない行が出ました。
再度試してみます。
簡単で恐縮ですが、お礼申し上げます。

回答No.6

imogasi

並べ替えというのが、（１）セルの単位で（２）セルの値で（表j形式でない、で並べられるという常識をはっきり認識して無いから、こんな質問異なる。
（１）のことからセル単位で（別々のセルに）分けないとエクセルでは並べ替えできない。
また（２）からセルの値は並べ替え目的に応じてここのセルに分離しないとならない。
ーー
プログラムでやるのなら、配列に一時的にわけて、その配列の値をソートするなどのことが出来るか、経験無いだろう。
ーー
一番よいのは、各行の索引語句は左列で別扱いのソートをして、だい２列から右は列ソートすればページ順になる
要らないカンマは都合よく省いてくれる。
ーー
エクセルを使うときは、データが完成後、後々何をしたいか、エクセルではどういう機能を使うか、そのためにはどうしなければ
（入力しておくべきか）よく考えてやら無いといけない。
今頃になって質問するようでは周到さが足りない。
ーー
さてデーター区切り位置で各セルに分離した後
ページ番号で左から右に並べるやり方は
例データ
a 212 34 12 15 212
b 12 37 34 67
c 56 76 31
のようにジャグジー状態のときは
Sub Macro3()
d = Range("B65536").End(xlUp).Row
MsgBox d '最終行
For i = 2 To d '第２行目からとする
r = Range("iv" & i).End(xlToLeft).Column '右端の列をとらえる
'MsgBox r
'以下ソート　マクロの記録から
Range(Cells(i, "B"), Cells(i, r)).Select
Selection.Sort Key1:=Cells(i, "B"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, SortMethod _
:=xlStroke, DataOption1:=xlSortNormal
Next i
End Sub
列ソートでデータ数（列数）が不揃いの場合、操作でやってみたがうまくいかないのでマクロでやった。
もし私が知らないだけなら、後続のよい回答によってください。

回答No.5

KURUMITO

面倒な操作になりますが次のようにしてはどうでしょう。
B列を選択してからエクセル2010でしたら「データ」タブの「区切り位置」を選択します。
「カンマやタブなどの区切り文字・・・」で「次へ」をクリック、「区切り文字」でその他の項目にドット.を入力し「次へ」から「完了」と進みます。例えばB列に数値が最大で8つあるとしたらI列までに数値が並びます。
そこでJ1セルには次の式を入力してQ1セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COLUMN(A1)>COUNT($B1:$I1),"",SMALL($B1:$I1,COLUMN(A1)))

R列には答えを表示させるとしてR1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(J1:Q1)>0,J1&".","")&IF(COUNT(J1:Q1)>1,K1&".","")&IF(COUNT(J1:Q1)>2,L1&".","")&IF(COUNT(J1:Q1)>3,M1&".","")&IF(COUNT(J1:Q1)>4,N1&".","")&IF(COUNT(J1:Q1)>5,O1&".","")&IF(COUNT(J1:Q1)>6,P1&".","")&IF(COUNT(J1:Q1)>7,Q1&".","")

答えをB列に表示したい場合にはR列を選択してから「コピー」し、その後にB1セルを選択して右クリックし、「形式を選択して貼り付け」で「値」にチェックをしてOKします。昇順で並べ替えされたデータが表示されます。その後は作業列として使ったC列からR列までを選択し削除してもよいでしょう。

お礼

KURUMITO　様　ありがとうございました。
丁寧に教えていただき助かりました。
取り急ぎお礼だけで恐縮ですが大切に使わせていただきます。

回答No.4

kagakusuki

以下の様な方法は如何でしょうか。
　今仮に、元データが入力されているシートがSheet1であり、B列の1個のセル内に入力されている、区切られた数字の個数は、例えば10個以内であるものとします。
　まず、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ISNUMBER(VALUE(SUBSTITUTE(Sheet1!$B1,".",))),IF(COLUMNS($A:A)>LEN(Sheet1!$B1)-LEN(SUBSTITUTE(Sheet1!$B1,".",))+ISERROR(FIND(".",Sheet1!$B1)),"",MID(Sheet1!$B1,FIND("゛",SUBSTITUTE("."&Sheet1!$B1,".","゛",COLUMNS($A:A))),FIND("゛",SUBSTITUTE(Sheet1!$B1&".",".","゛",COLUMNS($A:A)))-FIND("゛",SUBSTITUTE("."&Sheet1!$B1,".","゛",COLUMNS($A:A))))*1),"")

　次に、Sheet2のA1セルをコピーして、Sheet2のB1～J1の範囲に貼り付けて下さい。
　次に、Sheet2のA1～J1の範囲をコピーして、同じ行の2行目以下に貼り付けて下さい。

　次に、Sheet3のA1セルに次の数式を入力して下さい。

=IF(ISNUMBER(VALUE(SUBSTITUTE(Sheet35!$B1,".",))),MIN(Sheet2!1:1)&IF(COUNT(Sheet2!1:1)>1,".",""),Sheet35!$B1&"")

　次に、Sheet3のB1セルに次の数式を入力して下さい。

=IF(COLUMNS($A:B)>COUNT(Sheet2!1:1),"",SMALL(Sheet2!1:1,COLUMNS($A:B))&IF(COUNT(Sheet2!1:1)>COLUMNS($A:B),".",""))

　次に、Sheet3のB1セルをコピーして、Sheet3のC1～J1の範囲に貼り付けて下さい。
　次に、Sheet3のA1～J1の範囲をコピーして、同じ行の2行目以下に貼り付けて下さい。

　次に、Sheet4のA1セルに次の数式を入力して下さい。

=Sheet1!$A1&""

　次に、Sheet4のB1セルに次の数式を入力して下さい。

=Sheet3!$A1&Sheet3!!$B1&Sheet3!!$C1&Sheet3!!$D1&Sheet3!!$E1&Sheet3!!$F1&Sheet3!!$G1&Sheet3!$H1&Sheet3!$I1&Sheet3!$J1

　次に、Sheet4のA1～B1の範囲をコピーして、同じ行の2行目以下に貼り付けて下さい。

　これで、Sheet4に、

A列　　　　　　　　　B列
青森 24.45.125.321.369
岩手 54.65.65.114.214
北海道 32
宮城 45.165.245
秋田
東京 45.63.87.132

という具合に表示されます。

お礼

kagakusuki　様　ありがとうございました。
丁寧に教えていただき助かりました。
取り急ぎお礼だけで恐縮ですが大切に使わせていただきます。

回答No.2

keithin

沢山の文字を結合したり入れ替える，あんまり合理的な関数はありません。

仮に最大５つの数字として，Ｄ１からＨ１までに
321
24
369
125
45
と順不同で（途中に空白セルを交えても構いません）記入しておき，Ｂ１には
=IF(COUNT(D1:H1),SUBSTITUTE(TRIM(CONCATENATE(TEXT(SMALL(D1:H1+0,1),"# "),TEXT(SMALL(D1:H1+0,2),"# "),TEXT(SMALL(D1:H1+0,3),"# "),TEXT(SMALL(D1:H1+0,4),"# "),TEXT(SMALL(D1:H1+0,5),"# ")))," ",".")&".","")
と記入して，コントロールキーとシフトキーを押しながらEnterで入力しておきます。

もっと沢山の数字が並ぶときは，パターンに倣って式を延長して下さい。

＃あるいはエクセル的には，
目次の元データとして暫定的に
青森　321
岩手　 65
青森　 24
宮城　 45
岩手　 54
東京　132
東京　 45
：　　：

といったシングルなデータ並びを先に作成しておき，データが出そろった後に並べ替えと簡単な計算で目的の文字列を整形した方が，数式としては遙かに簡単にできます。

お礼

keithin　様　ありがとうございました。
丁寧に教えていただき助かりました。
取り急ぎお礼だけで恐縮ですが大切に使わせていただきます。

回答No.1

Cupper-2

小数点が2つ以上あると 「文字列」 として扱われます。
小数点が１つまたは1つもない状態は 「数値」 として扱われます。

「データ」の「区切り位置」機能で、セルを分解してはいかがでしょう。
その上でセルを並べ替えるなら楽にできますよ。
必要ならその分解して並べ替えた結果を結合しても良いでしょう。

日本国内で使うことを考えれば、区切り文字はピリオド（．）ではなく
カンマ（，）あるいはセミコロン（；）を使った方が良いでしょうね。

※ 小数点位置や桁区切り文字を 1,000.001 のように使うのは世界的に見て少数派。
　　多くは 1.000,001 のように逆に使います。

お礼

Cupper-2　様　ありがとうございました。
丁寧に教えていただき助かりました。
大変勉強になりました。
簡単で恐縮ですが、お礼申し上げます。