EXCEL－複数列から特定内容の行のみ抽出

EXCEL2010です。

添付画像のように、「好物１～３」に入力されている内容で特定の文字（メロン）を含む行のみを抽出するにはどのようにしたらよいでしょうか。

No.3 ベストアンサー 回答者： bunjii 回答日時： 2014/08/21 20:55

貼付画像はExcel 2013で検証してた結果です。

Excel 2010でも同様な結果を得られると思います。

チョット面倒臭い数式になりますが次のようにされると良いでしょう。
A13=IFERROR(IF(INDEX($A$1:$E$6,SUMPRODUCT(SMALL(($C$2:$E$6="メロン")*ROW(A$2:A$6),COUNTIF($C$2:$E$6,"<>メロン")+ROW(A1))),COLUMN())<>"",INDEX($A$1:$E$6,SUMPRODUCT(SMALL(($C$2:$E$6="メロン")*ROW(A$2:A$6),COUNTIF($C$2:$E$6,"<>メロン")+ROW(A1))),COLUMN()),""),"")
A13の数式を右と下へオートフィルでコピーしました。

上の表のデータが空欄のとき下の表に0を表示しても良ければIF関数を除けますので簡略化できます。
A13=IFERROR(INDEX($A$1:$E$6,SUMPRODUCT(SMALL(($C$2:$E$6="メロン")*ROW(A$2:A$6),COUNTIF($C$2:$E$6,"<>メロン")+ROW(A1))),COLUMN()),"")

No.4 回答者： keithin 回答日時： 2014/08/21 21:08

添付図

Ａ８にめろんを記入
Ａ９に
=INDEX(A:A,SMALL(IF($C$2:$E$6=$A$8,ROW($C$2:$E$6),9999),ROW(A1)))&""
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力
右に下にコピー。





＃参考
Ｆ２：
=IF(COUNTIF(C2:E2,"めろん"),"yes","")
などのように記入して以下コピー
Ｆ列をオートフィルタで絞ってしまった方が遥かに簡単かつ実用的で、応用の広いやり方です。

No.2 回答者： tom04 回答日時： 2014/08/21 20:43

No.1です。

たびたびごめんなさい。

前回のSheet1の作業列の数式が抜けていました。
前回の配置通りとしてSheet1の作業列G2セルの数式は
=IF(COUNTIF(C2:E2,Sheet2!A$1),ROW(),"")
としてフィルハンドルでずぃ～～～！っと下へコピーしておいてください。

どうも失礼しました。m(_ _)m

No.1 回答者： tom04 回答日時： 2014/08/21 20:41

こんばんは！

↓の画像のように別Sheetに表示させるとします。
作業用の列を設ける方法が簡単だと思います。

↓の画像で上側が元データのSheet1、下側がSheet2とします。
Sheet2のA1セルに「特定内容」のデータを入力します。

画像ではSheet2のA4セルに
=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!$G:$G,ROW(A1)))&"","")
という数式を入れ、列・行方向にフィルハンドルでコピー！
これで画像のような感じになります。m(_ _)m