エクセルでFALSEだけ抽出し切れ目なく順に表示

質問

エクセル2010で、ワークシート関数の質問です。
Q3:R19の表があります。
Q列にはTRUEかFALSEが必ず入ります。（空白や文字列は無し）
R列には文字列です。
Q列でFALSEのセルだけ、同じ行のとなりのR列の文字列を順に抜き出し、V9セル以下に切れ目なく表示したいのです。
TRUEを抜き出すのならV9セルに
=IF(ROW(A1)>COUNTIF($Q$3:$Q$29,TRUE),"",INDEX($R:$R,1/LARGE(INDEX(($Q$3:$Q$29)/ROW(A$3:A$29),),ROW(A1))))
と、以前教えてもらった数式を入れて、下へドラッグ・アンド・ドロップすれば添付の図のように抽出できます。でもこの式自体をよく理解できていないので、応用ができません。式の中のTRUEをFALSEに変えただけではだめでした。
どう変えればいいのでしょうか？
作業列を作って、=NOT(Q3) とし、TRUEとFALSEを逆転させ、Q列の代わりに作業列を参照する方法やVBAでなら思いつきますが、それ以外にいい方法はないのでしょうか？

回答No.4

Chiquilin · ベストアンサー

せっかく LARGEで抽出しているのに 何故エラー対策なんて入れる必要が
あるのでしょう。

=INDEX(R:R,1/LARGE(INDEX((1-(Q$3:Q$29))/ROW(R$3:R$29),),ROW(W1)))&""

お礼

Chiquilinさん、すごいです！
IF関数をとっても、＆””で0が表示されるのを防止したのはわかりましたが、なんでこんな短い式で正しく表示されるのか理解できません。
でも希望通りにできました。ありがとうございます。
できれば、数式の意味を解説していただけたら幸いです。

回答No.6

kagakusuki

回答No.1です。

>そのようにやってみましたが何も表示されませんでした。

　当方は、回答No.1の回答を投稿する前に、回答No.1に添付した画像の様に実際に表を作成して動作確認を行っており、その際には添付画像に写っている通りにちゃんと表示される事が確認出来ておりました。
　ですから、本当に「そのようにやって」みたのであれば、何も表示されないという事は考えられませんので、質問者様が「そのようにやって」いない事が原因だと考えられます。

>R2に「R3以下に入力されているどの値とも重複しない値」(例えば「銀行名」等)を項目名として入力して下さい。

という操作をしていなかったという事はないでしょうか?
　或はV8セルに

=$R$2

という関数を入れ忘れてはいないでしょうか?
　また、列や行を間違えてはいないでしょうか?

　これらの点をもう一度御確認下さい。

お礼

kagakusukiさん、ごめんなさい。
R2に「銀行名」といれるべきところ、あやまってQ2にいれていました。
R2に入れなおしたら正しく表示されました。大変申し訳ありませんでした。

回答No.5

Chiquilin

TRUEを除外すればいいんですから もっと単純に

=INDEX($R:$R,SMALL(INDEX(($Q$3:$Q$29)*1000+ROW(R$3:R$29),0),ROW(W1)))&""

これでもいいはずです。

お礼

何度もありがとうございます。
こちらも正しく表示されました。

回答No.3

msMike

V9: =IFERROR(INDEX(R:R,SMALL(IF(Q$3:Q$19=TRUE,ROW(W$3:W$19),""),ROW(A1))),"")
【お断り】上式は必ず配列数式として入力のこと

お礼

ありがとうございます。
配列数式とはますます理解できませんが、Q$3:Q$19=TRUEをQ$3:Q$19=FALSEにしたらうまく表示されました。

回答No.2

bunjii

>Q列でFALSEのセルだけ、同じ行のとなりのR列の文字列を順に抜き出し、V9セル以下に切れ目なく表示したいのです。
>TRUEを抜き出すのならV9セルに
提示の数式はQ3:Q19の論理結果がTRUEに対するものなのでFALSEに対する数式に置き換えるには判定する論理を反転すれば良いことになります。
=IF(ROW(A1)>COUNTIF($Q$3:$Q$29,TRUE),"",INDEX($R:$R,1/LARGE(INDEX(($Q$3:$Q$29)/ROW(A$3:A$29),),ROW(A1))))
　　　　　　↓
=IF(ROW(A1)>COUNTIF($Q$3:$Q$29,FALSE),"",INDEX($R:$R,1/LARGE(INDEX((NOT($Q$3:$Q$29))/ROW(A$3:A$29),),ROW(A1))))

変更した部分（２ヶ所）
COUNTIF($Q$3:$Q$29,TRUE)
　　　　　　↓
COUNTIF($Q$3:$Q$29,FALSE)

INDEX(($Q$3:$Q$29)/ROW(A$3:A$29),)
　　　　　　↓
INDEX((NOT($Q$3:$Q$29))/ROW(A$3:A$29),)

お礼

ありがとうございました。
無事、FALSEだけ表示されました。
助かりました。

回答No.1

kagakusuki

もしR列のデータに重複しているものが全くないという場合には以下の様にされると良いと思います。

　まず、R2に「R3以下に入力されているどの値とも重複しない値」(例えば「銀行名」等)を項目名として入力して下さい。
　次に、V8セルに次の関数を入力して下さい。

=$R$2

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

=IF(ROWS(V$9:V9)>COUNTIF($Q:$Q,FALSE),"",IF(ISERROR(1/(VLOOKUP(FALSE,INDEX($Q:$Q,MATCH(V8,$R:$R,0)+1):INDEX($R:$R,MATCH(TRUE,$Q:$Q)),2,FALSE)<>"")),"",VLOOKUP(FALSE,INDEX($Q:$Q,MATCH(V8,$R:$R,0)+1):INDEX($R:$R,MATCH(TRUE,$Q:$Q)),2,FALSE)))

　次に、V9セルをコピーして、V10以下に貼り付けて下さい。

　以上です。

お礼

ありがとうございます。
そのようにやってみましたが何も表示されませんでした。