配列数式って知ってます?

先週エクセルについて質問された。それは、関数などを使ってデータを詰めるというものなのですが、スキッとした解答が思いつかない。半日ほど悩んで、解答例を渡した。かなり感謝されたが、個人的にはどうも・・・。

さて、質問された内容ですが、下表のA1:A10ように時々入っているデータを、B1:B10ように上から詰めて自動的に表示する仕組みが欲しいのです。 

これが問題

私が、半日かけて考えたものは、列左にvlookup関数で検索値にする数字を付ける。この数字はA列にデータのある場合だけ、上から1、2、3と順番に付けます。これに使った関数は、ifとmaxです。後は、vlookupをFALSEで引くだけです。解るかな?でもこれだとvlookup用の表を何処かに用意しなければなりません。この一度置いておいてが、個人的にはどうも・・・。

色々と調べてもらった結果「配列数式」を使えば出来ることが分かりました。このような式をネットで見つけました。この式をB1に入れて、下にコピーすればOKです。

=IF(SUM(($A$1:$A$10<>"")*1)<ROW(A1),"",INDEX($A$1:$A$10,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)),ROW(A1))))

確かに希望どおりの結果が出ます。私は、全てを理解するのに、実験をしながらなので丸1日も掛りました。(($A$1:$A$10<>"")*1)は、FALSEを加算に使える数値にするため、1を乗じたものです。理解できます?それと、配列数式を使っているので、式の確定はenter ではなく、ctrl+shift+enterで確定させます。上手くいくと式の前後に"{"と"}"が付きます。

「配列数式」初めて知りました。それにしても、世の中には頭の良い人が居るものです!! 

(投稿者:92)


カテゴリー: つぶやき パーマリンク

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です