エクセルの文字列並べ替えについて

質問

エクセルの文字列並べ替えについて
エクセル2000です。
半角文字列の数字とアルファベットを通常のエクセルの機能で昇順で並べ替えた場合
012
013
111
1AA
1B1
211
222
22C
23A
2AB
2BC
のように並ぶと思います。（ここでの数字は数値でなく、すべて文字列です）
数字→アルファベットの順番のようです。

これを「アルファベット」→「数字」の順番で並べ替える方法はないでしょうか？
以下のような順にです。
ユーザー定義のリストに基づいた並べ替えがあることは存じていますが、3桁だけでも英数混在だと36×36×36=46,656個のリストを登録しなければならないことになり、現実的と思えません。
目的は、ホストコンピュータから出力される紙のデータがなぜかこのような順で出てきて、それにエクセルで作成したデータをあわせる必要が出てきたためです。
012
013
1AA
1B1
111
2AB
2BC
211
22C
222
23A
エクセルの一般機能でもVBAでもかまいません。ご教示いただけると幸いです。

回答No.6

end-u · ベストアンサー

>セルの書式で文字列として入力されていても数字だけのセルにはフリガナがつかない...

そんな事はないです。
数字文字列だったらフリガナはつきます。
数値にはつきません。

「数値が入力されているセルの書式を後で文字列に設定しても
　既入力の数値が文字列に変わるわけではない」

Sub ステップ実行()
　　Sheets.Add
　　Range("A1:A2").Value = "1"
　　Range("B1:B2").Formula = "=type(A1)"
　　Range("A1:A2").NumberFormat = "@"
　　Range("A2").Value = "1"
　　Range("A1").Phonetic.Text = "A1"
　　Range("A2").Phonetic.Text = "A2"
　　Range("A1:A2").Phonetics.Visible = True
End Sub

>となって、１が０より上になってしまいます。
そうではなくて、本来
011
012
013
1AA
1B1
111
2AB
2BC
211
22C
222
23A
この並びにならなくてはいけないのに数字のみのセルが上に来ている、
という点に着目しないといけないでしょう。
そこで疑うべきは
『（ここでの数字は数値でなく、すべて文字列です）』
という前提条件なのです。
：
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
　　.NumberFormat = "@" '※
　　For Each r In .Cells
　　　　tmp = r.Value
　　　　x = Len(tmp)
　　　　ret = Space(x * 2)
　　　　For i = 1 To x
　　　　　　s = Mid$(tmp, i, 1)
　　　　　　If IsNumeric(s) Then
　　　　　　　　s = CStr(CLng(s) + 90)
　　　　　　Else
　　　　　　　　s = CStr(Asc(s) - 1)
　　　　　　End If
　　　　　　Mid(ret, i * 2 - 1, 2) = s
　　　　Next
　　　　r.Value = tmp '※
　　　　r.Phonetic.Text = ret
　　　　'Debug.Print tmp, r.Phonetic.Text
　　Next
　　.Sort Key1:=.Item(1), Order1:=xlAscending, _
　　　　　Header:=xlNo, OrderCustom:=1, _
　　　　　MatchCase:=False, Orientation:=xlTopToBottom, _
　　　　　SortMethod:=xlPinYin
　　'.Phonetics.Visible = True
End With
：
最初から対策してれば良かったのでしょうけど
私もまだまだ未熟ものですから...orz

お礼

end-uさま、完璧なコードのご回答を感謝いたします。
これでばっちりです。
ただ、あらたに書き加えられた以下の２行についての疑問です。
.NumberFormat = "@" '※
これは、もともとセル書式を文字列にしているので無くても正しく動きました。
ところが、
r.Value = tmp '※
これは、なぜ一旦temに代入したr.Value を再度セルにもどしているのでしょうか？
これがないとセル書式が文字列で、なかに数字だけのデータはワークシート関数でみてもフリガナがつきませんでしたので絶対必要な対策でしょうが、理由がわかりません。
よろしければご教示いただけませんでしょうか？
ありがとうございました。

回答No.7

end-u

ん？
>「数値が入力されているセルの書式を後で文字列に設定しても
>　既入力の数値が文字列に変わるわけではない」
なので
「セルの書式を文字列に設定した後に既入力の数値を文字列として入力し直してる」わけです。
Sub ステップ実行() が、その事象の確認用テストコード。

お礼

Sub ステップ実行() がそういう意味だったのですか・・・。
理解が悪くてお手数をおかけいたしました。
よくわかりました。
ありがとうございます。
これからもよろしくご指導ください。
(o。_。)oペコッ.

回答No.5

end-u

作業列使わないVBA案だと、[ふりがな]機能を使った簡易版で
Sub try()
　　Dim r　 As Range
　　Dim tmp As String
　　Dim ret As String
　　Dim s　 As String
　　Dim x　 As Long
　　Dim i　 As Long

　　With Range("A1", Cells(Rows.Count, 1).End(xlUp))
　　　　For Each r In .Cells
　　　　　　tmp = r.Value
　　　　　　x = Len(tmp)
　　　　　　ret = Space(x * 2)
　　　　　　For i = 1 To x
　　　　　　　　s = Mid$(tmp, i, 1)
　　　　　　　　If IsNumeric(s) Then
　　　　　　　　　　s = CStr(CLng(s) + 90)
　　　　　　　　Else
　　　　　　　　　　s = CStr(Asc(s) - 1)
　　　　　　　　End If
　　　　　　　　Mid(ret, i * 2 - 1, 2) = s
　　　　　　Next
　　　　　　r.Phonetic.Text = ret
　　　　Next
　　　　.Sort Key1:=.Item(1), Order1:=xlAscending, _
　　　　　　　Header:=xlNo, OrderCustom:=1, _
　　　　　　　MatchCase:=False, Orientation:=xlTopToBottom, _
　　　　　　　SortMethod:=xlPinYin
　　End With
End Sub
こんな感じ。
考え方は関数編とそんなに変わりません。
IsNumericで分岐させて、シンプルに処理できそうですね。
[ふりがな]を設定するのは一回だけです。
あとは一般機能の[並べ替え]で「ふりがなを使う」と良いです。

お礼

VBAまでありがとうございます。
まだ解読できていませんが、試すと
111
211
222
011
012
013
1AA
1B1
2AB
2BC
22C
23A
となって、１が０より上になってしまいます。

補足

セルの書式で文字列として入力されていても数字だけのセルにはフリガナがつかないのが原因でしょうか？
ためしに111の頭にシングルクォーテーションをつけてやってみたら期待通りになりましたがオリジナルのデータにはシングルクォーテーションが無いんです。

回答No.4

MackyNo1

補助列で並べ替えるなら以下のような数式でできると思います（3桁の場合）。

=SUMPRODUCT((CODE(MID(A1,{1,2,3},1))-ISERROR(MID(A1,{1,2,3},1)*1)*58)*({1000,10,1}))

お礼

ありがとうございます。
でもなぜこれでできるのか解読できずにいます・・・。

回答No.3

KURUMITO

Ａ1セルから下方にお示しのデータがあるとしてＢ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",CODE(MID(A1,1,1))+IF(AND(CODE(MID(A1,2,1))>=65,CODE(MID(A1,2,1))<=90),CODE(MID(A1,2,1))*0.1,CODE(MID(A1,2,1)))/1000+IF(AND(CODE(MID(A1,3,1))>=65,CODE(MID(A1,3,1))<=90),CODE(MID(A1,3,1))*0.1,CODE(MID(A1,3,1)))/100000)

Ｂ列を重点にして昇順で並べ替えをします。

お礼

ありがとうございました。
勉強になります。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

小文字もあるのかなあ、優先順位2通り
B2セル =SUM((CODE(MID(A2,{1,2,3},1))+(MID(A2,{1,2,3},1)<="9")*200)*10^{6,3,0})
C2セル =SUM((CODE(UPPER(MID(A2,{1,2,3},1)))+(MID(A2,{1,2,3},1)<="9")*100)*10^{4,2,0})

考え方は＃1さんと同様

お礼

勉強になります。
ありがとうございました。

回答No.1

end-u

３桁限定だと仮定していいですか？
とりあえず一般機能のタタキ台として関数案。
A列に入力してあるとして作業列に
　=(MOD(CODE(MID(A1,1,1)),60)*10000+MOD(CODE(MID(A1,2,1)),60)*100+MOD(CODE(MID(A1,3,1)),60))*1
これをキーにして並べ替えます。
１文字ずつ分割してCODE化し優先順位を弄って桁数合わせてくっつける、
みたいな式です。

#タタキ台なんで叩かれても文句言いません :D

お礼

end-uさま、すばらしい！
CODEを使うことはわたしも考えたのですが、数字の方がアルファベットより小さかったので諦めていました。
それがMODで解決できるとは・・・・・。
たしかに0～9、A～Zしかないのですからこれで大丈夫ですね。
ありがとうございました。