条件に合った行を抜き出す関数を教えて下さい。

質問

エクセルの関数についての質問です。
色々検索してよく似た内容のやり取りを調べましたが、
どうしてもうまくいかず断念。
どうかよろしくお願いします。

A1に性別、A2は男・A3は女...とランダムにA100まで続く。
B1に職業、B2以下ランダムで"会社員"などのワードがB100まで続く。
C1に時間、0:00:12や0:11:50というタイム形式(h:mm:ss)でC100まで続く。

D1に0:05:00とC1と同じタイム形式の入力がある。

で、ここからなんですが、

C1:C100のデータとD1を比較し、
D1よりタイムオーバーしている行のみを、そのまま、
E1(性別)・F1(職業)・G1(時間)へ抜き出したいのです。
複数ある場合は、
E2・F2・G2...と上から順に続く。
極端に言えば、すべてタイムオーバーしていれば、
E100・F100・G100まで同じデータが並ぶことになります。

説明が足らなければ補足しますので、
なんとかよろしくお願いします。
エクセルのバージョンは2013です。
作業用列を追加するのも可能です。

回答No.1

kagakusuki · ベストアンサー

タイムオーバーか否かの判定に関して確認したいのですが、例えばD1セルに0:50:00と入力されていた場合、0:50:00丁度はタイムオーバーとは見做さず、0:50:01以上のものをタイムオーバーと見做すと考えれば宜しいのでしょうか?
　もしそれで宜しければ以下の様にされると良いと思います。

　今仮に、I列を作業列として使用するものとします。
　まず、I2セルに次の関数を入力して下さい。

=IF(OR(COUNT($C2,$D$2)<2,$C2<0,$D$2<0),"",IF($C2>$D$2+0.000001,ROW(),""))

　次に、I2セルをコピーして、I3～I100のセル範囲に貼り付けて下さい。
　次に、E2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT($I:$I),"",IF(INDEX($A:$C,SMALL($I:$I,ROWS($2:2)),COLUMNS($E:E))="","",INDEX($A:$C,SMALL($I:$I,ROWS($2:2)),COLUMNS($E:E))))

　次に、E2セルをコピーして、F2～G2のセル範囲に貼り付けて下さい。
　次に、G2セルの書式設定の表示形式を[時刻]の

13:30:55

に設定して下さい。
　次に、E2～G2のセル範囲をコピーして、E3～G100のセル範囲に貼り付けて下さい。

　以上です。

お礼

本日、教えていただいた関数を試したところ、
目的通りの結果を出すことが出来ました。
感謝感激です。

しかしながら、中身が完全に理解できません。
少しずつ勉強して自分なりに応用できるようにしたいと思います。

対応頂き、本当にありがとうございました。

回答No.4

kagakusuki

回答No.1です。

　もし作業列を使わずに済ませるのであれば、以下の様な方法があります。

　まず、E2セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER($D$2),$D$2>=0),IF(ROWS($2:2)>COUNTIF($C:$C,">"&$D$2+0.000001),"",IF(INDEX($A:$C,SUMPRODUCT(NOT(ISERROR($C$2:$C$100))*(COUNTIF(OFFSET($C$1,1,,ROW($C$2:$C$100)-ROW($C$1)),">"&$D$2+0.000001)<ROWS($2:2)))+ROW($C$1)+1,COLUMNS($E:E))="","",INDEX($A:$C,SUMPRODUCT(NOT(ISERROR($C$2:$C$100))*(COUNTIF(OFFSET($C$1,1,,ROW($C$2:$C$100)-ROW($C$1)),">"&$D$2+0.000001)<ROWS($2:2)))+ROW($C$1)+1,COLUMNS($E:E)))),"")

　次に、E2セルをコピーして、F2～G2のセル範囲に貼り付けて下さい。
　次に、G2セルの書式設定の表示形式を[時刻]の

13:30:55

に設定して下さい。
　次に、E2～G2のセル範囲をコピーして、E3～G100のセル範囲に貼り付けて下さい。

　以上です。 (抽出結果は回答No.1の方法のG列までの部分と同様になりますので、画像は割愛します)

　尚、配列値で抽出する方法やSUMPRODUCT関数を使った方法は、処理が重くなりますので、作業用列を用いる方法を使われる事をおすすめ致します。

お礼

こちらのやり方も試してみたいと思います。
参考になる回答をありがとうございました。

回答No.3

bunjii

>色々検索してよく似た内容のやり取りを調べましたが、どうしてもうまくいかず断念。
試した数式を提示して頂ければ何処に誤りがあるか説明できます。
他人が組んだ数式を理解するにはそれなりの組み込み関数の基本的な使い方を修得していないと無理でしょう。

>作業用列を追加するのも可能です。
作業列があると数式が簡単になりますが1つの数式で対応可能です。
E2=IF(COUNTIF($C$2:$C$20,">"&$D$1)>=ROWS($C$2:C2),INDEX(A:A,SMALL(INDEX(($C$2:$C$20>$D$1)*ROW($C$2:$C$20)+($C$2:$C$20<=$D$1)*ROW($C$21),0),ROW()-1)),"")
基本的にはタイムオーバーになっている行番号を抽出してINDEX関数やOFFSET関数を使って順次目的のセルの値を抽出します。
提示の数式ではINDEX関数を使っています。
行番号の抽出にはC列の時間と$D$1の値を比較し、タイムオーバーとなるセルの行番号を配列値で抽出します。その配列からSMALL関数で小さい順に取り出す仕掛けになっています。
SMALL(INDEX(($C$2:$C$20>$D$1)*ROW($C$2:$C$20)+($C$2:$C$20<=$D$1)*ROW($C$21),0),ROW()-1)
該当する行番号の数をカウントし、それ以上を抽出しないためにIF関数で条件分岐しています。そのカウント方法はCOUNTIF関数を使っています。
COUNTIF($C$2:$C$20,">"&$D$1)
IF関数での分岐には現在の処理数が何番目であるかをROWS関数で比較し、抽出の続行または中断の判定を行っています。
添付画像は20行までに模擬データを作成し、動作テストを行ったものです。

お礼

便利な方法を教えていただきありがとうございました。
使用されている関数がまだ知識不足のため理解しきれてませんが、
これから勉強して応用できるようにしたいと思います。

回答No.2

kagakusuki

間違えました。
　回答No.1の関数では、タイムオーバーか否かの基準となる時間を入力するセルを誤ってD1セルではなくD2セルにしてしまっておりました。
　ですから、I2セルに入力する関数を以下の様に訂正致します。

×
=IF(OR(COUNT($C2,$D$2)<2,$C2<0,$D$2<0),"",IF($C2>$D$2+0.000001,ROW(),""))

　　↓
○
=IF(OR(COUNT($C2,$D$1)<2,$C2<0,$D$1<0),"",IF($C2>$D$1+0.000001,ROW(),""))

お礼

修正内容、反映しました。
ありがとうございました。