• 受付
  • 困ってます

Excelで英数字を昇順で並び替えたい

  • 質問No.9727985
  • 閲覧数103
  • ありがとう数1
  • 回答数9

お礼率 20% (9/45)

Excelで、A列に下記のようなデータが入力されています。

A10B10
A1B1
A1B5
A2B1
A10B1
A2B5
B1A5
A1B13
B1A10
B10A2
B4A3
B1A1 ・・・

これを、下記のように「記号順と記号の後ろの数字を昇順」にして並べ替えたいです。

A1B1
A1B5
A1B13
A2B1
A2B5
A10B1
A10B10
B1A1
B1A5
B1A10
B4A3
B10A2

単に昇順化するとA10B1などが先に来てしまい理想通りになりません。
隣の列にSUBSTITUTE関数などで英字を数字化させたり消して並び替えてもうまくいきません。
アドバイスをいただければと思います。

回答 (全9件)

  • 回答No.9

ベストアンサー率 28% (25/87)

[No.1] です.
条件の後出しは,あなたのデータ処理能力が疑われるだけですよ.
あなたはデータ処理をする立場にあるのですから,そのデータの範囲を知っていることは最低条件です.

> アルファベットはAとB、数字は1桁または2桁という前提です。
これが私があなたの例から読み取った条件です.

あなたの追加条件は,Bに-がある場合もあるということですね.
おそらくAにも-があるのではないかと推測します.
ABだけでCはないのか?
-の後ろは1桁だけなのか?
アルファベットは1文字だけなのか?
-以外の文字はないのか?

条件を提示してください.
  • 回答No.8

ベストアンサー率 47% (49/104)

No.3です
まだ回答が付いていないようなので、試しにStrCmpLogicalWを使ったコードを組んでみました。
基準セルを変更すれば、元データの位置及びソート後のデータを出力する位置を変更できます(現在は同じ場所に上書きするようになっています)。

Option Explicit

Private Declare Function StrCmpLogicalW Lib "SHLWAPI.DLL" ( _
          ByVal lpStr1 As Long, _
          ByVal lpStr2 As Long) As Long

Sub test()
 Dim i As Long, j As Long, cnt As Long, tmp As Variant
 Dim SourceRange As Range
 Dim SortCell As Range
 Dim Ary() As String

 '基準セルを設定
 Set SourceRange = Cells(1, 1)
 Set SortCell = Cells(1, 1)
 
 '元データを取得
 Set SourceRange = Range(SourceRange, SourceRange.End(xlDown))
 cnt = SourceRange.Count
 ReDim Ary(1 To cnt)
 For i = 1 To cnt
  Ary(i) = SourceRange(i)
 Next i

 'バブルソート
 For i = 1 To cnt - 1
  For j = i + 1 To cnt
   If StrCmpLogicalW(StrPtr(Ary(j)), _
        StrPtr(Ary(i))) < 0 Then
    tmp = Ary(i)
    Ary(i) = Ary(j)
    Ary(j) = tmp
   End If
  Next
 Next

 '出力
 SortCell.Resize(cnt, 1).Value = WorksheetFunction.Transpose(Ary)
End Sub
  • 回答No.7

ベストアンサー率 30% (79/256)

A1からデータがあるとして B1に

=CONCAT(IFERROR(TEXT(FILTERXML("<a><b>"&CONCAT(MID(A1,SEQUENCE(LEN(A1)),1)&IF(ISNUMBER(-MID(A1,SEQUENCE(LEN(A1)),1))*ISERR(-MID(A1,SEQUENCE(LEN(A1),,2),1))+ISERR(-MID(A1,SEQUENCE(LEN(A1)),1))*ISNUMBER(-MID(A1,SEQUENCE(LEN(A1),,2),1)),"</b><b>",""))&"</b></a>","//b"),"000"),""))
データ最終行までコピーして B列を基準にソート
  • 回答No.6

ベストアンサー率 20% (272/1322)

B1: =LEFT(A1,1)
C1: =MID(A1,2,5)
D1: =LOOKUP(10^17,LEFT(C1,COLUMN($1:$1))*1)
E1: =TEXT(D1,"00")
F1: =SUBSTITUTE(A1,B1&D1,"")
G1: =LEFT(F1,1)
H1: =SUBSTITUTE(F1,G1,"")*1
I1: =TEXT(H1,"00")
J1: =B1&E1&G1&I1
範囲 B1:J1 を下方にオートフィル⇒範囲 A:J 選択⇒J列をキーにして[昇順]に[並べ替え]
【お断り】過去に登場された回答者が考案された数式を無断借用しています。m(_._)m
  • 回答No.5

ベストアンサー率 27% (4524/16174)

#4です。
参考に読んでください。
VBAで、私製関数を作ってみました。
標準モジュールというところへ、下記をコピペ。
Function keymk(x As Range)
s = ""
n = 0
fstchr = "y"
For i = 1 To Len(x)
y = Mid(x, i, 1)
If Asc(y) >= 48 And Asc(y) <= 57 Then
'数字文字
n = n * 10 + Val(y)
Else
'文字列
If fstchr = "y" Then GoTo P1
s = s & Format(n, "00")
fstchr = "y"
n = 0
P1:

fstchr = "n"
s = s & y
End If
Next i
s = s & Format(n, "00")
keymk = s
End Function
====
シートのA列に下記データを入れる。
B2に、式を =keymk(A2) と入れて、下方向に式を複写。
結果は下記。
A1:A14のセル範囲に

A列   B列
原データ変換後
A1B1A01B01
A1B5A01B05
A1B13A01B13
A2B1A02B01
A2B5A02B05
A10B1A10B01
A10B10A10B10
B1A1B01A01
B1A5B01A05
B1A10B01A10
B4A3B04A03
B10A2B10A02
C12A3C12A03
色々な場合でエラーが出ないかテスト必要。
(注)数字部分は2桁以下に限る。
数字は半角に限る。
もっと簡単なVBAコードにならないかと思いつつ、力不測で・・。関数の難しい式の組み合わせと変わらない?
  • 回答No.4

ベストアンサー率 27% (4524/16174)

・全体的には文字列のデータです。>英数字を昇順、というのは本件に関しては内容を表してません。
・文字の何番目かの1文字を左番目から比較していくのが、コンユーターの大小比較の根底にあります。
・だから数字の部分は、現れる最大桁数を考慮して、修正ソートキーを作らなければならない。エクセルでは別列に作らざるを得ない。
(ただし、アルファベット(本件ではAやB)などの出現位置は、各データで一定で、連なりの文字数の長さも一定でないとこういう議論はできません。)
・修正ソートキーを、1列に作る方法と、本件では4列を使う方法があります。
・1列に修正ソートキーを作るなら、(最大2桁しか数字が表れないとして)、1桁数字は、01のように変換しなければなりません。
ーー
・文字列の数字部分と文字部分の分離は、結構、関数で出すのは、技巧的な方法しかなく、初心者は何日考えても思いつかないものです。
「数字と文字を分ける エクセル」でGoogle照会せよ。
・だからVBAででもやって、関数化し、まる写ししなければしようがないだろう。
・入力の段階から、1は01と入力するよう、設計して、入力者に徹底すべきなんだ。データベース設計の、コード設計などは似たことを考えるもの。
とりあえず、考え方のみ。
  • 回答No.3

ベストアンサー率 47% (49/104)

VBAとAPIが使用できるなら、エクスプローラー順に並べ替えるという方法もあります。
参考URL:https://blog.goo.ne.jp/end-u/e/cdd7ec993d294636981b90cd407a2f97

ファイル名に上記の記号を入れて試してみましたが、エクスプローラではご希望通りの順番に並んでいました。
  • 回答No.2

ベストアンサー率 43% (3491/8058)

>隣の列にSUBSTITUTE関数などで英字を数字化させたり消して並び替えてもうまくいきません。
英字より数字の桁合わせをする方が良いでしょう。
先頭の英字に続く数字が1桁のとき英字の次に"0"を付加して2桁数字に合わせ、中間の英字の右側も同様に2桁に合わせてすべての文字列を6桁に合わせる必要があります。
中間の英字の位置をC列へ抽出すると並べ替え用のデータをB列へ設定し易くなります。
C1=MIN(INDEX(FIND({"A";"B"},A1&"AB",2),0))
B1=LEFT(A1,1)&TEXT(MID(A1,2,C1-2),"00")&MID(A1,C1,1)&TEXT(MID(A1,C1+1,2),"00")
C1とB1を下へ必要数コピーしてBを基準に並べ替えをしてください。
  • 回答No.1

ベストアンサー率 28% (25/87)

アルファベットはAとB、数字は1桁または2桁という前提です。
以下の数式を各セルに入力
入力した数式を下までコピー
F列とG列で昇順に並べる

セルB1 =FIND("A",A1)
セルC1 =FIND("B",A1)
セルD1 =IF(B1<C1,LEFT(A1,C1-1),LEFT(A1,B1-1))
セルE1 =IF(B1<C1,MID(A1,C1,3),MID(A1,B1,3))
セルF1 =LEFT(D1,1) & RIGHT("0" & MID(D1,2,2),2)
セルG1 =LEFT(E1,1) & RIGHT("0" & MID(E1,2,2),2)
補足コメント
oldold_d

お礼率 20% (9/45)

かなり理想的な動きをしてくれました。ご回答ありがとうございます。
数字が2桁のデータでは問題なく機能しました。
ただ、こちらの想定外だったこととして、ハイフン交じりの3桁・4桁のデータが存在したため、対応に難儀しています。
例:A2B5-2、A10B10-1等
投稿日時:2020/03/30 15:15
関連するQ&A

その他の関連するQ&Aをキーワードで探す

ピックアップ

ページ先頭へ