1. Home » 
2. エクセル関数の技 » 
3. 文字列の中の数字で並べ替えたい？

文字列の中の数字で並べ替えたい？:Excel関数の技

並べ替えの例題　　topへ

• 当サイトの掲示板にて質問されたものです。
• 質問内容：下図のように、A列の値をB列へ並べ替えたい。
  
• 並べ替えの規則は?
  • 上記の並べ替えはまず左の2桁の数字の昇順、次いで右側の数字の昇順で並べ替えられているようです。
    下図のように、左の数字を数値にしたものをB列へ、右の数字を数値にしたものをC列に取り出して、「並べ替え」をすることで期待するように並べ替えることができまます。
    

作業列を利用する　　topへ

• 並べ替えのキーが2つあるとちょっと面倒ですので、2組の数字を1つの数値へ変換します
  1. 幸いなことに、この例では左の文字列は　LEFT(A1,2)　、右の数字は　RIGHT(A1,3)　で取り出すことができます。
     これを一つの数値にするために　=LEFT(A1,2)*1000+RIGHT(A1,3)　とします。
     これは、右の数字が999まで入る可能性があるので、左の数値を1000倍してから加算しています。
     • =(LEFT(A1,2)&RIGHT(A1,3))*1　とすることもできます。
     
  2. 作成した数値の順番を求めます。
     順番はRANK関数で昇順とします。　=RANK(D1,$D$1:$D$5,1)　とします。
     
• 順番（E列）の1から順番にA列の値を取り出します。
  • INDEX関数とMATCH関数の完全一致検索で値を取り出します。
    =INDEX($A$1:$A$5,MATCH(ROW(A1),$E$1:$E$5,0))　としました。
    

作業列を使わない　　topへ

• 作業列を使いたくない・・・といったケースでは？
  • このような↓数式を考えました。
    =INDEX($A$1:$A$5,MATCH(SMALL(INDEX(LEFT($A$1:$A$5,2)*1000+RIGHT($A$1:$A$5,3),0),ROW(A1)),INDEX(LEFT($A$1:$A$5,2)*1000+RIGHT($A$1:$A$5,3),0),0))
  • まず取り出したい範囲のセルを　INDEX関数に入れます。
    =INDEX($A$1:$A$5,・・・・
    ここからキーとなるものの一番小さい値から順番に取り出すことにしました。
  • キー(Key)は　LEFT($A$1:$A$5,2)*1000+RIGHT($A$1:$A$5,3)　としました。(上記の作業列D列の値と同じです)
    これを　INDEX（Key,0）　という配列に入れます。
    このKeyの一番小さい値は　SMALL(INDEX(Key,0),ROW(A1))　となります。
  • これが、INDEX（Key,0） の何番目にあるのか? というのをMatch関数で調べます。
    MATCH(SMALL(INDEX(Key,0),ROW(A1)),INDEX(Key,0),0)
  • これと基のINDEX部分を合わせると
    =INDEX($A$1:$A$5,MATCH(SMALL(INDEX(Key,0),ROW(A1)),INDEX(Key,0),0))
    となります。
  • 説明上　Key　とした部分を元に戻すと、最初に書いた数式になります。
    

スポンサードリンク

　PageViewCounter
　
　Since2006/2/27