EXCEL 「並び替え」の誤動作について

WinXP/Excel2003 です。

大変お世話になります。
いまは定年退職し依頼されたExcelのアプリケーションなど
趣味で作っています。
今回、思わず躓いたのが「並び替え」です。

次のデータを昇順ソートすると

　　あいざわ　まさき
　　おかだ　りょうた
　　おがた　けいすけ

結果

　　あいざわ　まさき
　　おがた　けいすけ
　　おかだ　りょうた

となってしまいます。
五十音順では明らかに間違っています。
インターネットでいろいろ調べたんですが、
公式文書として以下がありました。

http://office.microsoft.com/ja-jp/word/HA1020319 …

だとしたら、みなさんどうやって回避してるのでしょう？
これだと、全く使えないので、
なにか回避策、あるいは正しい設定方法があるのではないかと
想像してますが、ご存知の方、ご指導願えれば幸いです。

No.6 回答者： fumufumu_2006 回答日時： 2008/03/31 21:50

ANo.4です。

>さてさて、では実際にどんなアルゴリズムにしようか、
>ちょっと悩みますね。

そういう仕事してませんが、私なら・・・
どれでもいいけど、基本的には楽な方。
更にはバグが出にくい方、コードが少ない方。
あとはクライアントが要望した場合、「うーん、結構難しいですねー」と言いながら、それに合わせる。
と言うスタンスが好きです。

この回答へのお礼

コード作成は最終的に個人的な審美眼によるものだと思います。
最低条件として、
バグ、速度、コード量（メモリー）だと思いますが、
最近はメモリーが安いので、バグ、速度のウェイトが高いでしょうね。

たぶん、HEX文字列に変換するワークシート関数（sjis）を作成し、
それをsort対象のワークシートの空き列に定義し、
従来のVBAのコードのsort範囲とsortキーを変更する程度の改修を考えています。一番楽でバグが少ない方法かと思います。
fumufumu_2006 さんのコードもなかなか洗練されていて、
さすがだな・・と思いました。
こんなコードがすらすら書けるようになりたいものです。
大変ありがとうございました。

お礼日時：2008/04/01 12:03

No.5 回答者： zap35 回答日時： 2008/03/31 20:50

＃０３です

＞でも、ちょっと重たいなぁ・・・
そうですね。データが何万件もあるのであればソートを何回も繰り返しますので遅くなるでしょう。

余計とは思いましたが、興味深かったので私もマクロを書いてみました。選択した範囲に対してふりがなでソートを行います。ソートHeaderの有無は自動判定にしていますので、表によってはタイトル行もソートされるかもしれません。

Sub PhoneticSort()
Dim rng As Range, keyC
Dim idxR, idxC, trgC As Long
Const cols As Integer = 10 '挿入する作業列の数（＝フリガナ文字数）
　Application.ScreenUpdating = False
　On Error GoTo end0
　If TypeName(Selection) = "Range" Then
　　Set rng = Selection
　　keyC = Application.InputBox("ソートキーの列名を入力して下さい", Type:=2)
　　If keyC = False Then GoTo end0
　　keyC = StrConv(keyC, vbNarrow)
　　rng.Cells(1, 1).Offset(0, rng.Columns.Count).Resize(1, cols).EntireColumn.Insert
　　For idxR = rng.Row To rng.Row + rng.Rows.Count - 1
　　　For idxC = 0 To cols - 1
　　　　trgC = rng.Column + rng.Columns.Count + idxC
　　　　Cells(idxR, trgC).Value = Mid(Cells(idxR, keyC).Value, idxC + 1, 1)
　　　Next idxC
　　Next idxR
　　trgC = rng.Column + rng.Columns.Count
　　For idxC = trgC + cols - 1 To trgC Step -1
　　　rng.Resize(rng.Rows.Count, rng.Columns.Count + cols).Sort _
　　　　key1:=Cells(rng.Row, idxC), order1:=xlAscending, header:=xlGuess
　　Next idxC
　　rng.Cells(1, 1).Offset(0, rng.Columns.Count).Resize(1, cols).EntireColumn.Delete
　End If
end0:
　Application.ScreenUpdating = True
End Sub

なおテストしたデータは以下です（「データ」→「並び替え」の順番に並んでいます）
Code　　Name　　　　　　　Phonetic
1　　　　座　頭くじら　　　ざ　とうくじら
2　　　　座頭　くじら　　　ざとう　くじら
3　　　　佐堂　賢二　　　　さどう　けんじ
4　　　　佐藤　浩二　　　　さとう　こうじ
5　　　　浜崎　あゆみ　　　はまざき　あゆみ
6　　　　浜崎　歩　　　　　　はまさき　あゆむ

これがマクロ実行後は
Code　　Name　　　　　　　Phonetic
4　　　　佐藤　浩二　　　　さとう　こうじ
3　　　　佐堂　賢二　　　　さどう　けんじ
1　　　　座　頭くじら　　　ざ　とうくじら
2　　　　座頭　くじら　　　ざとう　くじら
6　　　　浜崎　歩　　　　　　はまさき　あゆむ
5　　　　浜崎　あゆみ　　　はまざき　あゆみ
になります。

この回答へのお礼

例の方法を見事にコード化しましたね。
しかも短時間で・・・・
まったくレベルが違いますね。
コードの使い方が洗練されてるし、バグ対策も考慮されて・・
いいものを見せていただきました。
ありがとうございました。

お礼日時：2008/04/01 11:43

No.4 回答者： fumufumu_2006 回答日時： 2008/03/31 18:04

ANo.3さんのにダブりますが、漢字コード順がいいなら一時的に作業行に文字列の漢字コード(16進）を作っては？

B列を作業列にして、A列を並び替えます。

Function sjis(k As String) As String
Dim res As String
Dim i As Integer
For i = 1 To Len(k)
res = res & Hex(Asc(Mid(k, i, 1)))
Next
sjis = res
End Function

Sub test1()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = ActiveSheet
Set ws = Sheet1
ws.Range("B1").Formula = "=sjis(A1)"
ws.Range("B1").Copy Destination:=ws.Range("B1:B" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
ws.Cells.Sort Key1:=ws.Range("B1"), Order1:=xlAscending, Header:=xlNo
ws.Columns("B").ClearContents
Application.ScreenUpdating = True
End Sub

ただ、これだと小文字の「っ」とかはどうするのかと言うのがあるので、順番テーブルを用意する方法はどうでしょうか？
「ヴ」とかも処理できるように全角カタカナに変換しました。
テーブルに文字がないと文字が無かったと判断します。
256個以上は無いとして、2文字hexにしました。
個人的にはrange("?")よりcells(?,?)の方が好きなので・・・

Const kana = "　１２３４５６７８９" & _
"ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ" & _
"アァイィウヴゥエェオォ" & _
"カガキギクグケゲコゴ" & _
"サザシジスズセゼソゾ" & _
"タダチヂツヅッテデトド" & _
"ナニヌネノ" & _
"ハバパヒビピフブプヘベペホボポ" & _
"マミムメモ" & _
"ヤャユュヨョ" & _
"ラリルレロ" & _
"ワヲン" & _
"ー（）．・"

Function kanaOrder(ByVal k As String) As String
Dim res As String
Dim i As Integer
Dim p As Integer
k = UCase(StrConv(k, vbKatakana))
For i = 1 To Len(k)
p = InStr(kana, Mid(k, i, 1))
If p > 0 Then
res = res & Right("0" & Hex(p), 2)
End If
Next
kanaOrder = res
End Function

Sub test2()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Cells(1, 2).Formula = "=kanaOrder(A1)"
ws.Cells(1, 2).Copy Destination:=ws.Range(ws.Cells(1, 2), ws.Cells(ws.Cells(ws.Rows.Count, 1).End(xlUp).Row, 2))
ws.Cells.Sort Key1:=ws.Cells(1, 2), Order1:=xlAscending, Header:=xlNo
ws.Columns(2).ClearContents
Application.ScreenUpdating = True
End Sub


p.s.
activesheetを並べ替えてますが、Set ws = sheets("??")とすれば、表示していないシートも並べ替えられます。

この回答へのお礼

真っ向からのアルゴリズムですね。
（案１）
HEX変換した文字列をSORTする
（案２）
コレートシーケンスを自作して、
その位置データに変換しsortする

いずれも鮮やかですね。
ちなみにExcelのコレートシーケンスは
「ァアィイゥウヴェエォオ・・・」
のようですので、それはそれで、
案１で充分かと思いました。

大変勉強になりました。
貴重な時間をいただきありがとうございました。
今後ともよろしくお願いします。

さてさて、では実際にどんなアルゴリズムにしようか、
ちょっと悩みますね。

でも、最初に戻りますが、
清音=濁音=半濁音
とする、基本思想はなぜ必要になったのでしょうね？

しかも、その結果「等価」の場合は、
吃音＜清音＜濁音＜半濁音　とする
こんなに複雑なコレートシーケンスはなぜ必要だったのか。
なにか深いわけがありそうですね。
（つぶやきです^^;）

いずれにしても皆さんありがとうございました。

お礼日時：2008/03/31 20:59

No.3 回答者： zap35 回答日時： 2008/03/31 16:44

Microsoftの仕様には時々使いにくいものもありますね。

Dor_001さんはマクロをお使いになるようですので、マクロを使えばこんな方法もあります（力業になります）

１）並べ替えしたいデータ範囲の右に、一時的にふりがなの最長文字列分の作業列を追加する
２）各列にふりがなを１文字分ずつ表示する
３）挿入した列の内、最後（右端）の列をキーにして並び替えする
４）またその前（左となり）の１列で並び替える（これを挿入した先頭列まで繰り返す）　→　ここまでで期待する並び順になるはず
５）作業列を削除する

画面描画はFalseにしておくとよいでしょう。マクロまでは書きませんが、その気があれば試してみてください。

ところでこの質問をお借りしますが
　http://oshiete1.goo.ne.jp/qa3890433.html
はまだ解決しませんか？
　＞上から順に行くのが現実的かと思いますので、
だとロジックが複雑になると思いますがいかがでしたでしょうか。

この回答へのお礼

zap35さん、いつもお世話になっています。
昔はアセンブラー言語などでバブルソートやらクイックソートなど
よく作りましたが、まさにその乗りですね。
このアルゴリズムでコード化すると正しい結果が出ますね。
でも、ちょっと重たいなぁ・・・
「Justify」の件はやはり、最大領域の下位セル+1を起点として、
End(xlUp)を使わせてもらいました。
ありがとうございました。

お礼日時：2008/03/31 20:11

No.2 回答者： imogasi 回答日時： 2008/03/31 10:37

第１行にタイトル（例氏名）行を入れ（本質ではないが、リストというのが基本なので。

）
中間にスペース（データなし）行を入れず
フリガナを使わない、にチェックをいれ
大文字と小文字を区別するにチェックを入れ
行単位
でやってみてください。
うまくいくはずです
＝＝＝＝
文字コードに関心を持ったり、プログラム経験がない人はそう感じるかも知れませんが、コンピュターのソートは
文字コードで考えて
最左の桁から、ビット比較的に
比較して、勝負か付く（０ビットが勝、１のビットの方が巻け）ところまで比較を続け、１回目の勝負がついたところで決定（打ち切り）です。コンピュタの原初的仕組みです。
（上記は文字列の場合で、数値の場合は人間の大小の考えと、コンピュターの内部ビット表現が一致するよう定義されています。
動かしようがありません。）
ーー
最近（１９８５年以後、パソコンなどから）エクセルなどのソフトなどからコンピュタに入り始めた人は
ソフト側で裏で（便利なように）細工をしているので、ソートといえばそんな便利なものかと思いがちですが、修正ソートキーを裏で作って、それで自分（世間）の思うようにソートするようにしてるはずです。
ーー
結論は、コンピュタの基本原理は動かしようがないので、自分の思うように、逆手にとって、原ソートキーから、修正ソートキーを作らないとなりません。
●レディメードで「フリガナを使う」
●関数で別列に修正ソートキーを作る
●プログラムで修正キーを作る
ほとんど３番目でないと出来ないでしょうが。
--
ただそんなに修正キーを作らなければならないケースは多くない。
前株後株、カ）など入力の多様化で、困ってしまって、必要になる場合が多い。
ーー
スペースの問題
昔の半角かなの濁点・半濁点
の場合の扱いで、普通にソートすると、常識、慣用と異なる結果
ガ出るので、苦労した経験があります。
ーー
質問の点で、テスト的に
=CODE("か")は　9259
=CODE("が")は　9260
になったので、同じ桁位置に２つがあれば、普通のソートでは、「か」が先に来ます。
（ただし昇順指定）
ただ内部文字コードや色々あって各過程で使用文字コードが変化することがあるので、詳しい知識を必要とする場合があると思うが、文字コードの設計者は「か」を先（小さいコードで）に定義するはずです。

この回答への補足

丁寧で、かつご親切な解説をいただきありがとうございます。
ただ、

＞第１行にタイトルを入れ
＞中間にスペース行を入れず
＞フリガナを使わない、にチェックをいれ
＞大文字と小文字を区別するにチェックを入れ
＞行単位

はすでにやってますが、
うまくいきませんでした。
なにか他にチェックしておかないといけない設定とかあるんでしょうか？

内部コードで単純比較してるのだったら、
このような結果は出ずに、
素直に正しい結果が出るのと思うのですが、
ご説明のように、
なにか、小細工してるようですね。
それが何のためなのかよくわからないところです。
そして、それがむしろ足を引っ張っているような気がします。

VBAでの一連の処理の流れの中のSORTなんですが、
処理前に、姓名の「名」の方を削除して、
SORT後、処理がすべて終わった段階で「名」を付加してます。
手作業です。

補足日時：2008/03/31 12:43

No.1 回答者： ganko3 回答日時： 2008/03/31 10:08

テストしてみたら次の順序で並びました。

おか
おが
おかた
おかだ
おがた
おがだ
おかたけ
おかだけ
おがたけ
おがだけ
おかたり
おかだり
おがたり
おがだり

清音、濁音の順序より後ろの字のほうが優先されるようですから、ご希望のように並べるには、姓と名を分けてまず姓で次の優先順位が名で並び変えたらよいと思います。

この回答への補足

回答ありがとうございます。
ただ、このやり方だと、姓・名として２列
必要となります。
本来は１列で管理すべきデータと思いますので、
ソートの前後等で、余計な処理が必要となります。
ganko3さんの方法で逃げるしかないとしたら、
みなさん不便を感じてないのでしょうか？？

Microsoftともあろうものが、どうも納得できないですね。
仕様と言われればそれまでなんですが・・・
直す気はさらさらないのでしょうかね？
その辺の理由なんか知りたいですね。

補足日時：2008/03/31 10:27