やりたいこと
- 1行目を見出しとして2～50行目までの表から、F列が"条件"である行を別の場所に抜き出して表示したい。
- VBAは使わず、関数のみで実現する。
関数
=IF(ROW(A1)>COUNTIF($F$2:$F$50,"条件"),"",INDEX(A$1:A$50,SMALL(INDEX(NOT($F$2:$F$50="条件")*1000+ROW($A$2:$A$50),),ROW(A1))))
解説
IF(ROW(A1)>COUNTIF($F$2:$F$50,"条件"),"",
エラー判定部分です。
条件に一致する行数よりも現在抽出しようとしている行数が大きい場合、空白文字を返します。
現在の行数を後述のSMALL関数内部で順位として使用しているため、条件に一致する行数より大きい値を指定すると1000を加算した行が選択されてしまい、#REFエラーになってしまいます。
これを回避するため、現在抽出しようとしている行数が条件に一致する行数よりも大きい場合は空白文字を返すようにしています。
なお、ROW(A1)の部分が現在抽出しようとしている行数を表すため、どんな場合でもA1固定にします。
SMALL(INDEX(NOT($F$35:$F$53="条件")*1000+ROW($A$35:$A$53),),ROW(A1))
INDEX(NOT($F$35:$F$53="条件")*1000+ROW($A$35:$A$53),)は、条件に一致しない行は1000+行番号、一致する列は行番号となる配列を返します。
例) 2,5,6行目が一致する場合→[2,1003,1004,5,6,1007,1008,...]
ここから、SMALL関数で[現在抽出しようとしている行数]番目に小さい値を取得しています。
ここもROW(A1)が現在の抽出行数を表すため、A1固定です。
これにより抽出した行番号を用いて、外側のINDEX関数で対象行を抽出する仕組みです。
こんな時は
抽出結果が連続しない(間に空行がある)場合
1行目に1番目、4行目に2番目...という感じで抽出結果が連続しない場合、ROW(A1)で抽出行数を指定してしまうと1行目は1番目、4行目は4番目...になってしまい、間の結果が抜けてしまいます。
この場合、最初と最後のROW(A1)の部分をCOUNTA関数に置き換えることで解決できます。
例) A101,A104,A105に1,2,3番目の抽出結果を表示する場合
A101=IF(COUNTA($A$100:$A100)>COUNTIF($F$2:$F$50,"条件"),"",INDEX(A$1:A$50,SMALL(INDEX(NOT($F$2:$F$50="条件")*1000+ROW($A$2:$A$50),),COUNTA($A$100:$A100))))
※A100が空白セルの場合はCOUNTA($A$100:$A100)+1とする。
A101をコピーし、A104,A105にペーストします。
検索対象の表がシートの一番上にない場合
検索対象の表が一番上にない場合は、取得した行番号から表開始位置の前行までの行数を引いてやる必要があります。
例)検索対象の表が、34行目を見出しとして35～50行目の場合
=IF(ROW(A1)>COUNTIF($F$35:$F$50,"条件"),"",INDEX(A$34:A$50,SMALL(INDEX(NOT($F$35:$F$50="条件")*1000+ROW($A$35:$A$50),),ROW(A1))-ROW($A$33)))
引いてやる必要があるのは、SMALL関数で行数を取得した結果に対してです。
また、引いてやる数は常に固定なので、セル番地は絶対指定で指定します。
検索条件のバリエーション
検索条件を変更する場合、修正対象は以下の2か所です。
- IF関数条件式のCOUNTIF関数部分
- SMALL関数内のINDEX関数部分
AND/OR条件
COUNTIF関数部分は、Excel2007以降ならAND条件にCOUNTIFS関数、それ以前のバージョンはSUMPRODUCT関数を利用します。OR条件の場合はCOUNTIFの和を利用します。
INDEX関数内で条件指定する方法は、SUMPRODUCT関数の条件指定と同様です。
AND条件
COUNTIFS($F$2:$F$50,"条件1",$G$2:$G$50,"条件2")
SUMPRODUCT(($F$2:$F$50="条件1")*($G$2:$G$50="条件2"))
SMALL(INDEX(NOT(($F$2:$F$50="条件1")*($G$2:$G$50="条件2"))*1000+ROW($A$2:$A$50),),ROW(A1))
OR条件
COUNTIF($F$2:$F$50,"条件1")+COUNTIF($G$2:$G$50,"条件2")
SMALL(INDEX(NOT(($F$2:$F$50="条件1")+($G$2:$G$50="条件2"))*1000+ROW($A$2:$A$50),),ROW(A1))
あいまい検索
COUNTIF関数部分はワイルドカード(*)が利用できるため特に問題はありません。
INDEX関数の条件指定ではワイルドカードが使えないため、あいまい検索を利用したい場合は文字列操作関数を利用する必要があります。
前方一致
COUNTIF($F$2:$F$50,"条件*")
SMALL(INDEX(NOT(LEFT($F$2:$F$50,2)="条件")*1000+ROW($A$2:$A$50),),ROW(A1))
後方一致
COUNTIF($F$2:$F$50,"*条件")
SMALL(INDEX(NOT(RIGHT($F$2:$F$50,2)<>"条件")*1000+ROW($A$2:$A$50),),ROW(A1))
部分一致
COUNTIF($F$2:$F$50,"*条件*")
SMALL(INDEX(NOT(ISNUMBER((FIND("条件",$F$2:$F$50))))*1000+ROW($A$2:$A$50),ROW(A1))