Deprecated : The each() function is deprecated. This message will be suppressed on further calls in /home/zhenxiangba/zhenxiangba.com/public_html/phproxy-improved-master/index.php on line 456
文字列の中の数字で並べ替えたい?:Excel関数の技
Home »
エクセル関数の技 »
文字列の中の数字で並べ替えたい?
当サイトの掲示板にて質問されたものです。
質問内容:下図のように、A列の値をB列へ並べ替えたい。
並べ替えの規則は?
上記の並べ替えはまず左の2桁の数字の昇順、次いで右側の数字の昇順で並べ替えられているようです。
下図のように、左の数字を数値にしたものをB列へ、右の数字を数値にしたものをC列に取り出して、「並べ替え」をすることで期待するように並べ替えることができまます。
並べ替えのキーが2つあるとちょっと面倒ですので、2組の数字を1つの数値へ変換します
幸いなことに、この例では左の文字列は LEFT(A1,2) 、右の数字は RIGHT(A1,3) で取り出すことができます。
これを一つの数値にするために =LEFT(A1,2)*1000+RIGHT(A1,3) とします。
これは、右の数字が999まで入る可能性があるので、左の数値を1000倍してから加算しています。
=(LEFT(A1,2)&RIGHT(A1,3))*1 とすることもできます。
作成した数値の順番を求めます。
順番は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)) としました。
作業列を使いたくない・・・といったケースでは?
このような↓数式を考えました。
=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 とした部分を元に戻すと、最初に書いた数式になります。
スポンサードリンク
よねさんのWordとExcelの小部屋 »
エクセル関数の技 »
文字列の中の数字で並べ替えたい?
PageViewCounter
Since2006/2/27