エクセル 特定の文字を含むセルとその上のセルを抜き出したい

教えて下さい！

例えば下記のような表があった場合


　　　　　A列　　　　　　　　　　B列
1行　赤　　　　　　　　　　　赤
2行　りんご　青森　　　　　　りんご　青森　　
3行　青　　　　　　　　　　　黄
4行　いちご　千葉　　　　　　りんご　宮城
5行　黄　　　　　　　　→
6行　りんご　宮城　
7行　白
8行　いちご　茨城　


というようにA列に「りんご」という文字を含むセルがあったら、B列に「りんご」を含むセルそのものと、「りんご」を含むセルのすぐ上のセルを抜き出したいのです。

ご教授の程宜しくお願いいたします。

No.1 ベストアンサー 回答者： tom04 回答日時： 2020/06/14 19:21

こんばんは！

一例です。
↓の画像のようにD1セルに「検索文字」を入力すると表示できるようにしてみました。
一気にやっています。

画像ではB1セルに
=IFERROR(INDEX(A$1:A$1000,SMALL(IF(ISNUMBER(FIND(D$1,A$1:A$1000)),ROW(A$1:A$1000)-MOD(ROW(A1),2)),INT(ROW(A2)/2))),"")

配列数式なのでCtrl＋Shift＋Enterで確定！（←必須★）し
フィルハンドルで下へコピーしています。

※　作業用の列を設けて、段階を踏めばもっと簡単な数式でできると思います。m(_ _)m

この回答へのお礼

ありがとうございます！一発でできました！m(__)m

お礼日時：2020/06/15 17:48

No.4 回答者： d-q-t-p 回答日時： 2020/06/15 15:34

何の質問なのか書いてない上に バージョンも不明ですが

最新の Excelだという前提で回答します。

=FILTER(A1:A7,ISNUMBER(FIND("りんご",A1:A7))+ISNUMBER(FIND("りんご",A2:A8)))

この回答へのお礼

ご回答ありがとうございます。情報が不足しており大変し訳ございませんでした。当方のExcelが2013年版の為か折角ご指導いただいたのに「その関数は正しくありません」と表示されできませんでした。今後は質問内容とバージョンを必ず記載したいと思います。誠に申し訳ございませんでした。

お礼日時：2020/06/15 17:46

No.3 回答者： fujillin 回答日時： 2020/06/15 09:36

こんにちは

ご提示の例の場合で、B1セルに

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$999)/(FIND("りんご",$A$2:$A$999)>0),INT(ROW(A2)/2))-MOD(ROW(A1),2)),"")

という式を入力し、下方にフィルコピーでいかがでしょうか？

※ 検索値が「りんご」とは限らずいろいろと変えたいような場合は、どこかのセルに検索値を入力することにして（例えばC1セルとします）、式中の"りんご"の部分を$C$1に入れ替えればそのようにすることが可能です。

この回答へのお礼

ありがとうございます！一発でできました！同じ結果を求めるのに色々なアプローチがあるのですね(*^^*)

お礼日時：2020/06/15 17:48

No.2 回答者： mike_g 回答日時： 2020/06/14 20:15

添附圖參照

D1: =SMALL(IF(LEN(A$1:A$100)-LEN(SUBSTITUTE(A$1:A$100,C$1,""))>0,ROW(A$1:A$100)),ROW(A1))
【御斷はり】上式は必ず配列數式として入力の事
E1: =OFFSET(D$1,(ROW(A1)-1)/2,)-MOD(ROW(A1),2)
B1: =IFERROR(INDEX(A$1:A$100,E1),"")

この回答へのお礼

ありがとうございました！複数の列に分ければそれぞれがより簡単な数式ですみますね(*^^*)

お礼日時：2020/06/15 17:48