セルに入力した文字列を一覧表の中から抽出したいと考えております。

B2セルに入力した任意の文字列を一覧表の中から抜き出して別の場所に抽出したいのですが、以下の式で”総務部”を「B2」で指定し検索範囲を一覧表の全体に指定することは可能でしょうか？
「B2」セルには検索の都度入力する文字列を変更したいのですが、それも可能でしょうか？
ご教示のほどよろしくお願いいたします。

=IFERROR(INDEX($A$3:$G$8,MATCH(
LARGE(($F$3:$F$8="総務部")*1/ROW($A$3:$A$8),ROWS($A$15:$A15)),
1/ROW($A$3:$A$8),0),COLUMNS($A$14:A$14)),"")

文章力が無いので意味が通じる文になっていないかもしれませんが…。
よろしくお願いいたします。

質問者からの補足コメント

• 皆様、本当にご親切にありがとうございます。
  
  検索する値は文字ですが、セルの中にはその他の文字も一緒に入っています。
  例えば建物名の列のデータには
  「××メゾン」
  「メゾン○○」
  「アリスト△△」
  「ハイツ××」
  「○○レジデンス」
  「△△ハイツ」
  等と入力されています。
  その文字列の中から、例えば「メゾン」だったり「ハイツ」が入っているセルを探して、そのセルを含む行全体を抽出表示させたいのです。
  「メゾン」が入っているセルが複数有ったら、複数行全部を抽出表示させたいのです。
  データはI列まで入力されています。
  
  よろしくお願いいたしますｍ(__)ｍ

  
    補足日時：2020/04/15 17:18

No.6 回答者： nouble1 回答日時： 2020/04/14 13:54

お礼　拝見しましたよ。

〉構文の意味も分からず…

そんな事　申してませんよね？

定数配列しか　受け入れない、
引数に、

配列数式からの　返値を、
渡すには、

其の戻り値が　定数配列とは、
違う旨に　みなされる故、
其のままでは　駄目で、

ので、
一定の　施策が、
求められる。


此の、

施策履行さえ　守れば、
但の　エンターだけで、
式を　確定しても、
機能する。


そう、

伝えているだけでは？


あのですね、

〉LARGE(($F$3:$F$8="総務部")*1/ROW($A$3:$A$8),ROWS($A$15:$A15))

此の　式中の、
($F$3:$F$8="総務部")*1/ROW($A$3:$A$8)
て、
配列数式ですよね、

しかし、
LARGE構文の　第一引数も、
MATCH構文の　第二引数も、
本来は　定数配列で、

配列数式の　戻り値を、
受け付けないのですよ。


ですので、

其の為に　其のキャップを、
埋める　意味で、

コントロール+シフト±エンター
此による　確定か、
不可欠になり、

連れて、
単なる　クリックだけで、
簡単に　壊れ得るような、
低信頼性の　式を、
残さずに　得ないのです。


然しながら、

配列数式の　範囲、
先式では、
($F$3:$F$8="総務部")*1/ROW($A$3:$A$8)
此を、
INDEX(($F$3:$F$8="総務部")*1/ROW($A$3:$A$8),,)
と　して、
LARGE(INDEX(($F$3:$F$8="総務部")*1/ROW($A$3:$A$8),,),ROWS($A$15:$A15))
と　すれば、

内側に　エラーが、
なく、
最終的な　戻り値が、
一つならば、

但の　エンターでも、
エラーには　ならないのですよ。


ご質問は、

其の　主旨として、
「エラーに　させない、」
「機能させる、」
では？


所で、

変な　式ですね、
少し　判ってなさそう、

LARGE(($F$3:$F$8="総務部")*1/ROW($A$3:$A$8),ROWS($A$15:$A15))
中の、

($F$3:$F$8="総務部")*1/ROW(…
の、
*1は　無意味では？


後、

何故　浮動小数値を、
多用していますか？

圧倒的に　負荷量が、
違いますので、
処理速度が　飛躍的に、
落ち込み、
何千倍も　速度が、
劣ります。


又、

2進10進変換にも、
10進2進変換にも、
不確定要素が　多く、

とても　信頼性を、
欠く　式と、
なってませんか？


何だか、

とても　婉曲的な
式記載で、

まるで　誰かの、
著作権主張を　迂回するために、

仕方なく、
こう　書いたような、

素直さのない、
違和感　極まりなさを、
感じますよ？


抑も、

総務部が　F列内に、
含まれる　行を、
抽出したいなら、

=IFERROR(1/SMALL(INDEX(($F$3:$F$8=$B$2)/ROW($A$3:$A$8),,),row(A1)),"")
かも　知れないし、

何番目の　行に、
あるか　だけなら、

MATCH構文なんか、
使うだけ　無駄で、

=SMALL(INDEX(($F$3:$F$8=$B$2)*10^15+($F$3:$F$8=$B$2)*ROW($A$3:$A$8),,),ROW(A1))

B2を　含む、
行抽出なら、
未確認で　申し訳ないが、

=IFERROR(OFFSET($A$2, SMALL(INDEX(($F$3:$F$8=$B$2)*10^15+($F$3:$F$8=$B$2)*ROW($A$3:$A$8),,),ROW(A1)),COLUMN(A1)-1,1,1),"")
此だけで　済みますよ？


まぁ、

確かに　私の、
著作権内　ですがね。


兎に角、

行洗い出し抽出なんかに、
浮動小数部を　多用しない事
MATCH構文なんか　使わない事、

お勧めして　おきます。

No.5 回答者： mike_g 回答日時： 2020/04/14 11:44

参考になるかだうか・・・(Excel 2019)

先ず下準備から
範囲 A2:E11 選択⇒Alt+AC⇒“上端列”のみにチェック残し⇒Enter

愈々(?)、ホンバンです。
次式を入力したセル G2 を右方にズズーッとオートフィル
=INDEX(INDIRECT(G1),MATCH($B1,INDEX($A2:$E11,0,MAX(IF($A2:$E11=$B1,COLUMN($A2:$E11)))),0)-1)
【お断り】上式は必ず配列数式として入力のこと

【蛇足】
セル G4 に次式を入力しておけば、上記の式は =INDEX(INDIRECT(G1),$G4) で済む！
==MATCH(B1,INDEX(A2:E11,0,MAX(IF(A2:E11=B1,COLUMN(A2:E11)))),0)-1
【お断り】上の長い式は必ず配列数式として入力のこと

この回答へのお礼

ご回答ありがとうございます。
当方Excel初心者で、先ず下準備の時点「範囲 A2:E11 選択⇒Alt+AC⇒“上端列”のみにチェック残し⇒Enter」
から不明です。
配列数式についてはShiftキー+Ctrlキー+Enterキーを同時に押す事でよろしいですよね？
ホンバン以降は理解出来るのですが。。。
よろしくお願いいたします。

お礼日時：2020/04/15 12:09

No.4 回答者： nouble1 回答日時： 2020/04/14 10:21

少なくとも、

SMALL構文、LARGE構文、
等内での、

定数配列を　引数に、
求める　箇所に、

配列数式よりの　値を、
渡す場合は、

INDEX構文で　括る、
必要が　あります。


此は、

簡単な　非公式技術なのですが、

然しながら、
此の話を　提示出来る、
方は、
とても　少ないようですね。


所で、

身勝手で　済みませんが、
INDEX構文での　索引を、
持ちいている場合は、

私は　サポートしない事と、
しています。

この回答へのお礼

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

当方は、構文の意味も分からずご質問している状態でしょうか。。
日本人ですが日本語が下手でお手数をお掛けしすみませんでした。

お礼日時：2020/04/14 10:41

No.3 回答者： mike_g 回答日時： 2020/04/14 10:15

呈示された「以下の式」は無視してコメントします。

》 B2セルに入力した…ことは可能でしょうか？
其の事だけを讀んだ限りでは可能だと思ひます。

囘答者が容易な方法を提案するには、行列番号入りの「一覧表」の必要最小限の行列から成る（10行5列の）サンプル表と「別の場所に抽出した」サンプル表が必要です。
出來るだけ「簡単な」表をお願いしたのは、貴方自身が囘答を廣大な實用表に應用すれば済む事だからです。

「一覧表」と其処からデータを抜き出す「別の場所」は、同一シートですか？別シートですか？
別シートの場合、”総務部”を指定するセル B2 はドッチ側にありますか？

この回答へのお礼

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

一覧表の中からA列～I列までに入力した内容（その都度B列の入力内容だったりE列の入力内容だったりします）を検索し、該当したセルを含む行全てをsheet2に抽出したいと考えております。
全部で500行程のデータです。

文章が下手で申し訳ありません。
よろしくお願いいたします。

お礼日時：2020/04/14 10:37

No.2 回答者： fujillin 回答日時： 2020/04/14 10:07

こんにちは

＞任意の文字列を一覧表の中から抜き出して別の場所に抽出したいのですが
別にわざわざ抽出する必要もなく、「任意の文字列」を直接書き出すのではダメなのでしょうか？
それとも、「一覧表の中に指定文字列が存在すればそれを書き出し、一覧表に存在しない場合は空白にする」ような照合チェックを行いたいということでしょうか？
意味を成すには、後者くらいしか思いつかないので、それを前提にしてみると・・

結局、
　「一覧表に指定文字列が存在するかどうか」
をチェックできれば良いことになります。
一致検索で良いとするなら、MATCH関数等で検索し、見つかれば行番号が、見つからなければエラーが返されるのを利用すれば宜しいかと。

かなりの仮想が入ってしまっていますが、こんな感じでしょうか？
　=IF(ISERROR(MATCH($B$2,一覧表,0)),"",$B$2)
※ 「一覧表」は一覧表の範囲、または名前の定義で設定しておきます。

この回答へのお礼

早速のご返答ありがとうございます。
日本語が下手で（日本人ですが）すみませんです。

お礼日時：2020/04/14 10:36

No.1 回答者： iothes00 回答日時： 2020/04/14 10:04

B2と$F$3:$F$8のチェックでその行の列を必要分出す関数です

=INDIRECT("G"&SMALL(IF($B$2=$F$3:$F$8,1,10000)*ROW($F$3:$F$8),ROW()-2))

この場合はG列を表示します
最後のROW()-2は順位なので、現在位置からマイナスして先頭が１になるようにします。

ctrl+enterで
｛=INDIRECT("G"&SMALL(IF($B$2=$F$3:$F$8,1,10000)*ROW($F$3:$F$8),ROW()-2))｝
になります

下にコピーすれば対象ものを複数取得できます。

この回答へのお礼

早速のご回答ありがとうございます。
この後でご教示の通り入力してみます。

一覧表の中からA列～I列までに入力した内容（その都度B列の入力内容だったりE列の入力内容だったりします）を検索し、該当したセルを含む行全てをsheet2に抽出したいと考えております。
全部で500行程のデータです。

文章が下手で申し訳ありません。
よろしくお願いいたします。

お礼日時：2020/04/14 10:34