エクセル 関数 指定の値のある行の一部を参照

添付画像の様に
下記表Ａのステータスが「保」となっている行の
管理番号と備考欄のみを抽出し別シートに上から順に表にしたいと思っています。

フィルターで絞ってマニュアルで作成するのでなく、
関数を入れておいてステータス「保」が出たら自動で出てくるようにしたいのですが
可能でしょうか？

ちなみに管理番号と備考は隣り合っておらず間に違う項目が左右にあります。

関数詳しい方ご教授ください。
よろしくお願いいたします<m(__)m>

No.4 ベストアンサー 回答者： tom04 回答日時： 2017/01/24 08:42

No.１です。

＞ステータスがJ10から始まっていると・・・

当然使っている数式のそれぞれの範囲が変わってきます。
お示しの数式をそのまま使うとします。
Sheet2の配置は前回の画像通りだとすると、A2セルに
=IFERROR(INDEX(aaa!$A$1:$W$1000,SMALL(IF(aaa!$J$1:$J$1000="保",ROW($A$1:$A$1000)),ROW(A1)),MATCH(A$1,aaa!$10:$10,0)),"")

前回同様配列数式ですので、Ctrl+Shift＋Enterで確定し
列・行方向にコピーしてみてください。

※　数式の説明をすると長くなってしまいますので、今回は割愛しますが、
お示しの数式内にある
＞ROW(A5)
がSMALL関数の「順位」になります。
配列数式のJ列が「保」となっている行が「TRUE」となり、その行番号の小さい順に表示させなければならないので
最初に数式を入れるセルには
必ず「1番目」すなわち ROW(A1)　とします。（A列でなくてもB列でもZ列でも構いません）

※　ステータスがJ10から始まっていると範囲がわかっているのであればINDEX関数の範囲をA列から指定せずにJ列から指定すると
=IFERROR(INDEX(aaa!$J$10:$W$1000,SMALL(IF(aaa!$J$10:$J$1000="保",ROW($A$10:$A$1000)-9),ROW(A1)),MATCH(A$1,aaa!$J$10:$W$10,0)),"")
という配列数式でも大丈夫だと思います。

後者の数式は逆に判りにくいでしょうかね？m(_ _)m

この回答へのお礼

出来ました。
助かりました。ありがとうございました。

お礼日時：2017/02/05 23:24

No.3 回答者： yuji3690 回答日時： 2017/01/23 21:59

エクセルの式は自分が理解できる方法でないと修正や改良ができないので、他の例も書いてみますね。

自分が一番分かり易いと思った方法で試してみてください。

配置が分からないので、ステータスをZ、管理番号をAZ、備考をBZ、結果の管理番号をCZ、結果の備考をDZとして、表Aにはそれぞれ4行目～100行目にデータがあるとします。

CZ4=SUMPRODUCT((Z$4:Z$100="保")*1,(COUNTIF(INDIRECT("Z$4:Z"&ROW(Z$4:Z$100)),"保")=ROW()-ROW(CZ$3))*1,AZ$4:AZ$100)
DZ4=IF(CZ4>0,INDIRECT("BZ"&MATCH(CZ4,AZ:AZ,0)),"")

これらを必要なだけコピーするのでどうでしょう？
CZ4の説明としては、Z4~100が保と入力されている行の中で、Z4から数えた保の数が「結果を表示するセルがCZ3を0として何番目か」と一致する行、に対応するAZ4~100のセルのデータを取得します。

もう少し細かく説明すると、
Z4~100が保かどうかをTRUEかFORSEで判定し、*1によって1か0かという判定に変更しています。
質問者の例で言うと、(0,0,0,1,0,0,1,0)と判定されています。
そして、CZ4であれば、それが保の1番目かどうかを同様に判定しています。
例で言うと(0,0,0,1,0,0,0,0)です。CZ5であれば2番目なので(0,0,0,0,0,0,1,0)となっています。
3つ目は少し違って、そのままAZ4~100が入っています。
例で言うと(123,456,789,741,852,963,159,753)です。
そしてこれらの対応する数字を掛け合わせます。
CZ4で例を示すと(0*0*123,0*0*456,0*0*789,1*1*741,0*0*852,0*0*963,1*0*159,0*0*753)となっているので、(0,0,0,741,0,0,0,0)となります。
これが最終的にごうけいされるので、741が表示されます。

該当数以上(例の場合だと3つ以上)の行に式を入れていると、該当しないセルが0と表示されます。
これを表示したくなければ、=IF(式=0,"",式)とすると0ではなく空白になります。
式の部分は上で書いたCZ4=の後の式を丸々うつしてください。

次にDZ4の説明ですが、
CZ4に数字がある場合、MATCHによってそれと一致するBZ列のセルを探して、該当するセルの行番号を取得します。
そしてINDIRECTによって、BZと行番号をくっつけてBZ○というセルのデータを取得します。

この回答へのお礼

ありがとうございました。

お礼日時：2017/02/05 23:25

No.2 回答者： tatsu99 回答日時： 2017/01/23 08:50

表Aの具体的な位置を提示してください。

ステータス、管理番号、備考、それらの各列の情報
データの開始行（3行と思われる）

検索結果の具体的な位置を提示してください。
管理番号、備考の列
データの開始行（3行と思われる）

上記が提示されれば、具体的な関数の式が提示できます。
提示されないと、この関数を使えというような考え方のみしか回答できません。

No.1 回答者： tom04 回答日時： 2017/01/23 08:36

こんにちは！

＞管理番号と備考は隣り合っておらず間に違う項目が左右にあります。
具体的な配置が判らないのでやり方だけ・・・
↓の画像のように元データはSheet1にあり、Sheet2に表示するとします。

Sheet2の1行目項目名はSheet1と同じ項目名にしておいてください。
A2セルに
=IFERROR(INDEX(Sheet1!$A$1:$E$1000,SMALL(IF(Sheet1!$A$1:$A$1000="保",ROW($A$1:$A$1000)),ROW(A1)),MATCH(A$1,Sheet1!$1:$1,0)),"")

配列数式ですので、Ctrl＋Shift+Enterで確定！
A2セルのフィルハンドルで列・行方向にコピーすると
画像のような感じになります。m(_ _)m

この回答へのお礼

回答ありがとうございます。

やってみたんですがうまくいかないので教えてください(+_+)
表のあるシート名がaaa
ステータスがJ10から始まっていると
下記になりますでしょうか。

=IFERROR(INDEX(aaa!$A$1:$W$1000,SMALL(IF(aaa!$J$10:$J$1000="保",ROW($A$1:$A$1000)),ROW(A5)),MATCH(A$1,aaa!$10:$10,0)),"")

お礼日時：2017/01/23 23:27