Excel。オートフィルターをつかわずに、該当するデータを別のセルに表示したい
＜IF＋ROW関数 IFERROR＋INDEX＋SMALL＋ROW＋COLUMN関数＞
データベースや表から、該当する条件のデータを抽出して、別のセルに表示したい場合、オートフィルターでデータを抽出して、コピーをする。
この作業でもいいのですが、抽出条件が変わるとなると、オートフィルターで抽出してコピーするという作業が面倒になってきます。
そこで、数式だけで、対応することもできます。
A1:C8にデータがあって、G1のクラス名に合致するデータを、F4を起点として抽出表示したいわけです。
G1を「B」にしたら、クラスBのメンバーが抽出され表示するというものです。
最初は準備として、D2に次の数式を設定し、D8までオートフィルで数式をコピーします。
=IF(B2=$G$1,ROW()-1,"")
B列のデータと、G1のデータが合致したら、行番号-1を算出するという数式ですね。
ROW関数は行番号を算出します。
「-1」するのは、あとでINDEX関数をつかうのですが、座標がわかりやすくするために、「1」からの値にしたいからです。
今回は、データの1件目が2行目にありますので、「-1」すれば、「1」から表示することができます。
F4にメインとなる数式を設定します。
設定したら、オートフィルで数式をコピーします。
=IFERROR(INDEX($A$2:$C$8,SMALL($D$2:$D$8,ROW(A1)),COLUMN(A1)),"")
長い数式ですが、これだけで、該当するデータを抽出し表示することができます。
F4:H8まで数式を設定してあります。
それでは、数式を確認していきます。
最初のIFERROR関数ですが、データの件数が少ない場合、「#NUM!」というエラーが表示されてしまうので、その防止のためにIFERROR関数をつかっています。
INDEX関数は、設定した範囲の中から、指定した行番号と列番号のデータを抽出するという関数です。
最初の引数は、「配列」です。
「$A$2:$C$8」と設定します。
オートフィルで数式をコピーしますので、絶対参照を忘れないようにしましょう。
2番目の引数は、「行番号」です。
SMALL($D$2:$D$8,ROW(A1))
先程、IF関数で算出した結果を、SMALL関数を使い、1番小さい値を抽出させます。
1番小さい値、次に2番目の小さい値というようにしたいので、SMALL関数をつかい、さらに、ROW関数でA1を指定します。
オートフィルで数式をコピーすると、A1がA2とかわり、2番目・3番目に小さい値のある場所を見つけてくれるという仕組みです。
最後の引数は、「列番号」です。
COLUMN(A1)
こちらも、先程と同じ仕組みで、オートフィルで数式をコピーするときに、座標がズレるようにしています。
このように、INDEX関数をつかうことで、データを抽出することもできます。