- ベストアンサー
エクセル関数で条件に合った行を抜き出す方法
- エクセルのバージョンは2013です。条件に合った行を抜き出すための関数を探しています。
- A1からC100までのデータを比較し、D1よりタイムオーバーしている行の性別、職業、時間をE1、F1、G1に抜き出したいです。
- 複数行がタイムオーバーしている場合は、E2、F2、G2に順にデータが続きます。すべての行がタイムオーバーしている場合は、E100、F100、G100に同じデータが入ります。
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
タイムオーバーか否かの判定に関して確認したいのですが、例えば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のセル範囲に貼り付けて下さい。 以上です。
その他の回答 (3)
- kagakusuki
- ベストアンサー率51% (2610/5101)
回答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関数を使った方法は、処理が重くなりますので、作業用列を用いる方法を使われる事をおすすめ致します。
お礼
こちらのやり方も試してみたいと思います。 参考になる回答をありがとうございました。
- bunjii
- ベストアンサー率43% (3589/8249)
>色々検索してよく似た内容のやり取りを調べましたが、どうしてもうまくいかず断念。 試した数式を提示して頂ければ何処に誤りがあるか説明できます。 他人が組んだ数式を理解するにはそれなりの組み込み関数の基本的な使い方を修得していないと無理でしょう。 >作業用列を追加するのも可能です。 作業列があると数式が簡単になりますが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行までに模擬データを作成し、動作テストを行ったものです。
お礼
便利な方法を教えていただきありがとうございました。 使用されている関数がまだ知識不足のため理解しきれてませんが、 これから勉強して応用できるようにしたいと思います。
- kagakusuki
- ベストアンサー率51% (2610/5101)
間違えました。 回答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(),""))
お礼
修正内容、反映しました。 ありがとうございました。
お礼
本日、教えていただいた関数を試したところ、 目的通りの結果を出すことが出来ました。 感謝感激です。 しかしながら、中身が完全に理解できません。 少しずつ勉強して自分なりに応用できるようにしたいと思います。 対応頂き、本当にありがとうございました。