[[20220826153350]]　『文字、数値の混在データのソート』（たまりん）　ページの最後に飛ぶ

『文字、数値の混在データのソート』（たまりん）

いつも大変お世話になっています
今回特殊なソートを行いたいのですがお知恵を拝借できないでしょうか

A列に
AAAA
AAA1
BB1C
AA1CC
BA3A
BBBC
とあったとします
普通に昇順をかけると
AA1CC
AAA1
AAAA
BA3A
BB1C
BBBC
となりますが
AAAA
AAA1
AA1CC
BA3A
BBBC
BB1C
という結果を出したいのです。
どのようにすればよいでしょうか。
間に数値が入る場合、普通に昇順をかけると文字列よりも数字が優先順位が上に来ています。
ここを文字列を上にそのあと数値としたいのです。
いい方法はないでしょうか。

前提条件として
1) 2文字目までは必ずアルファベット
2) 3文字目以降に数字が入ることがある
3) 文字は3文字以上上限なし

今回の例題は1列ですが、実際には複数列あり、
A列をキーにすべての行を連動させて並べ替えを行いたいのです。

宜しくお願いいたします。

＜使用 Excel：Office365、使用 OS：unknown ＞

 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
 A2,"0","ｱ")
   ,"1","ｲ")
   ,"2","ｳ")
   ,"3","ｴ")
   ,"4","ｵ")
   ,"5","ｶ")
   ,"6","ｷ")
   ,"7","ｸ")
   ,"8","ｹ")
   ,"9","ｺ")

 などという作業列を基準にして並べ替える。というのは...  やっぱダメすかね? ^^;

（白茶） 2022/08/26(金) 16:47

白茶さん
コメントありがとうございます
それも考えたのですが
前提条件3) 文字は3文字以上上限なし
があるので厳しいですね、せめて上限設定があればいいのですが

（たまりん） 2022/08/26(金) 16:59

白茶さん
SUBSTITUTEは位置指定しなければどこにある文字もすべて置換するから
いけるのか、、、
もう少し考えてみます
ほかにいいアイデアがあれば教えてください
宜しくお願いいたします
（たまりん） 2022/08/26(金) 17:04

 MicroSoft365なら、これだけでスピル出来そう。

 B1セル =SORTBY(A1:A6,BYROW(A1:A6,LAMBDA(a,TEXTJOIN("",TRUE,LET(c,MID(a,SEQUENCE(1,LEN(a)),1),IF(ISNUMBER(c*1),CHAR(9505+c),c))))))

 行  __A__  __B__
  1  AAAA   AAAA 
  2  AAA1   AAA1 
  3  BB1C   AA1CC
  4  AA1CC  BA3A 
  5  BA3A   BBBC 
  6  BBBC   BB1C

（半平太） 2022/08/26(金) 19:42

半平太さん
コメントありがとうございました
記載通りしてみたところ関数内の「c」の記述部分がすべてエラー「この数式には問題があります」
が出てしまい実行できませんでした。
LAMBDA関数は初めて見ましたので調べたところoffice365からの新しい関数のようですね。
自分で関数を作成できると記載がありました。
ただ、使用可能バージョンが「16.0.14729.20260」となっていました
当方のバージョンは「16.0.13127.21490」となっています。
これが原因でしょうか？
社内PCの為バージョン管理されており、当方では上げることができない状態です。

後正直恥ずかしながら、関数の内容が私には高度すぎて理解ができないです。
もしよろしければ、ご教授いただけないでしょうか。
宜しくお願いいたします。
（たまりん） 2022/08/29(月) 09:15

 >高度すぎて理解ができないです。
 数式の検証というのがあるのでそれを実行してみると分かりますよ。
（?） 2022/08/29(月) 09:26

たまたま、並べ替えが一致したので
投稿させていただきます。
案として、作業列を作る前提ですので、
NGなら飛ばしてください。

 > 1) 2文字目までは必ずアルファベット 
 > 2) 3文字目以降に数字が入ることがある

「2文字目まで」と「3文字目以降」の作業列を
作って並べ替えてみてはどうでしょうか？

並べ替えは、B列を昇順、C列を降順。

   =LEFT(A1,2)  =REPLACE(A1,1,2,"")
          ↓       ↓
A列      B列       C列
AAAA     AA        AA
AAA1     AA        A1
AA1CC    AA        1CC
BA3A     BA        3A
BBBC     BB        BC
BB1C     BB        1C

（renka） 2022/08/29(月) 10:10

 ＞ただ、使用可能バージョンが「16.0.14729.20260」となっていました
 ＞当方のバージョンは「16.0.13127.21490」となっています

 これは有難い情報です。
 MicroSoft365なのに、使える人と、使えない人がいるので戸惑っている所です。

 新関数をふんだんに使ったので、どれが使えて、どれが使えないか教えていただけませんか？
 　SORTBY、BYROW、LAMBDA、LET、SEQUENCE

  例えば　=s とすればSortbyとかSequenceとか候補が出て来るので、使える関数かどうか判定できます。

 ＞関数の内容
 方針だけですけど。。

 　Midで一文字ずつ分解する
 　それに1を掛ける
 　文字はエラーになるので、そのまま使う
 　エラーにならない数字には9505を加算する。
 　　するとカタカナの文字コードになるので、
 　　それをChar関数を使って、ァ,ア,ィ,イ,ゥ,ウ,ェ,エ,ォに変換する
 　以上が完了したら、Textjoinを使って文字を再度結合する
 　Sortbyを使って、再結合された文字順を基準に元データを並び変える

（半平太） 2022/08/29(月) 10:15

renkaさん
コメントありがとうございました
C列を降順、この方法だと
数字が1～9アルファベットは大文字A～Zまで使用されるので

仮にB,C列が
KA　AA
KA　1A
KA　2A
KA　BB
KA　CA
となった場合不具合が生じます
書き方が半端で申し訳ありませんでした

半平太さん
コメントありがとうございました

関数の使用可否ですが以下の通りでした
SORTBY　OK
BYROW　NO
LAMBDA　NO
LET　NO
SEQUENCE　OK

でした

 ＞関数の内容
解説頂きありがとうございました
当方で使えない関数の為実際の動きを見ての検証ができないので
まだよく理解できていませんが
やろうとしていることは理解できました
ありがとうございました
（たまりん） 2022/08/29(月) 11:38

?さん
コメントありがとうございました
半平太さんへの返信の通り
使えない関数があるので検証できていませんでした
複数の関数を使用していたので
NETで書かれていることだけではよくわからなかったのです
こんがらがってしまって、、、
ありがとうございました
（たまりん） 2022/08/29(月) 11:42

 > C列を降順、この方法だと

では、先頭に１文字追加して、
並べ替えは、B列を昇順、C列を昇順。

 =LEFT(A1,2)  =IF(CODE(MID(A1,3,1))>57,"a","Z")&REPLACE(A1,1,2,"")
         ↓      ↓
A列      B列    C列
KAAA     KA     aAA
KABB     KA     aBB
KACA     KA     aCA
KA1A     KA     Z1A
KA2A     KA     Z2A

ご希望の並びなのかは不明ですが。

（renka） 2022/08/29(月) 12:58

 同じバージョンじゃないのでワークする分かりませんが、新関数を使わないで、
 B列に基準となるデータを出してみますので、それを基準に並べ替えが出来るかトライしてみてください。

 B1セル =TEXTJOIN("",TRUE,IFERROR(CHAR(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)+9505),MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)))
 下にコピー

 その後、B列を基準に並び替え（手動で）

 <並替え後の図>
 行  __A__  ___B___
  1  AAAA   AAAA   
  2  AAA1   AAAア  
  3  AA1CC  AAアCC 
  4  BA3A   BAイA  
  5  BBBC   BBBC   
  6  BB1C   BBアC

（半平太） 2022/08/29(月) 14:57

0→a
1→b
2→c

9→j

 に変換してソートした後

a→0
b→1
c→2

j→9

　に戻すとできます。
（昔同じオーダーを受けたときの臨時策ですが）

（ん） 2022/08/30(火) 17:35

ご回答いただいた皆様
Cの病で寝込んでいました。
長いこと返答できなくて申し訳ありません。

半平太さん
理想の形で処理ができそうです。
この方法を使わせていただきます。
ありがとうございました、感謝いたします。

renkaさん、んさん
コメントありがとうございます。
テキスト内に数字が複数入る場合や、テキスト数が3文字以上5文字6文字と増えていく可能性が今後出てくることから、
半平太さん案を採用させていただきました。
ご助言いただき、大変ありがとうございました。
（たまりん） 2022/09/08(木) 11:38

コメント返信：

[ 一覧(最新更新順) ]

[[20220826153350]] 『文字、数値の混在データのソート』（たまりん） ページの最後に飛ぶ

[[20220826153350]]　『文字、数値の混在データのソート』（たまりん）　ページの最後に飛ぶ