プロが教える店舗&オフィスのセキュリティ対策術

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列まで入力されています。

    よろしくお願いいたしますm(__)m

    「セルに入力した文字列を一覧表の中から抽出」の補足画像1
      補足日時:2020/04/15 17:18

A 回答 (16件中1~10件)

[No.15お礼]へのコメント、


私が作成したワークシートでは、
セル I1 に式を入力後 Ctrl+Shift+Enter の操作を忘れると、当該セルも3行目の全セルもエラー #N/A 表示になります。
    • good
    • 0

[No.11お礼]へのコメント、


》 ギブアップしそうです。。。
「初心者」には難しいので、さうなさる事を御奨めします。
しかし、眞劍に勉強する御積りなら、今後は囘答通りに實行した上でコメントして下さい。
例へば、…
》 セルI1に「#N/A」・セルA3には「#REF!」と表示されて
セルI1のエラーは、囘答[No.9]のステップ2に明記した「上式は必ず配列數式として入力の事」を貴方が眞面目に實行しなかつたから、と推察します。
其処がエラー#N/Aになると、範囲 A3:I3 の全セルもエラー#N/Aになる筈ですが、貴方は『セルA3には「#REF!」と表示』と仰つただけ!他のセルに就いては言及無し!

ギブアップするのも理解出來ます。サヨナラ。
    • good
    • 0
この回答へのお礼

ご指摘、誠にありがとうございます。
配列数式になるよう「Shift 」「Ctrl「」「 Enter」キーを同時に押下しました。
また、表現が不足しており申し訳ございませんでしたが、「セルA3:I13」も全て「#REF!」表示でした。

教えて頂けているように実行しているつもりですが結果が相違しており、何が違うのかと考えた時に、セルの中には検索指定した文字列以外の文字も入っており、そこがきちんと質問出来ていないのかと考えた次第です。

皆様の貴重なお時間を割いて頂きまして、ありがとうございましたm(__)m

お礼日時:2020/04/16 15:46

No13です



図がある方が正解に近いのかなと想像して、気を取り直して、補足の内容でテスト。


添付図は、B15セルに検索値を入れる想定。
A16セルに
=IFERROR(INDEX(A$1:A$10,AGGREGATE(15,6,ROW($D$2:$D$10)*(FIND($B$15,$D$2:$D$10)>0),ROW(A1))),"")
の関数式を入れ、右方、下方にフィルコピーしてあります。

16行目以降に、検索値「メゾン」を含む行が抽出されるというものです。
「セルに入力した文字列を一覧表の中から抽出」の回答画像14
    • good
    • 0

No12です。



No12へのお礼の内容と追加の補足の内容とが一致していないように思えます。
後からいろいろ条件が変わるのは、一からやり直しになるので、正直に言って、考えるのが面倒になりますね。

補足の方を例にするならば、普通にD列で検索して抽出すれば良いので、過去にも何度か質問のあった内容と同じになります。(一般的な抽出処理の関数式)
ただ、大抵の場合は「一致」が抽出条件になっているものが多いと思いますので、条件の部分を「含まれる」に変えてあげる必要があるかも知れません。
    • good
    • 0

No2です



未だになさりたいことがわからないけれど、mike_g様の回答が近い方向に向かっているのか(それすらまったく不明ですが)、仮にそうだと仮定すると・・・

『表全体から指定した検索値に合致するセルを探し、そのセルのある行(全体)の値を抜き出す』
ってなことを行えればいいのかと、勝手に解釈してみました。

添付図では、仮に、$A$2:$E$11の範囲を対象とする表とし、B16に検索したい値を入力するものとしています。
17行目に、指定した値を含む行を抽出するというものです。

添付図では、A17セルに
=INDEX($A$2:$E$11,AGGREGATE(15,6,ROW($A$2:$E$11)/($A$2:$E$11=$B$16),1)-1,COLUMN())
の関数式を入れて、右方向にフィルコピーしてあります。

計算としては、指定された「d5-4」が含まれているセルを表全体から検索して、見つかったD6セルを含んでいる、6行目全体を17行目に抽出しています。

※ 質問内容をほとんど把握できていないので、お遊び的に作成してみました。
  違っていたらスルー願います。


>文章力が無いので意味が通じる文になっていないかもしれませんが…
>当方Excel初心者で、… …から不明です。
他の方もおっしゃっていますが、わからないことを自慢していても何も始まりません。
適切な回答を望むのなら、なさりたいことを回答者に(正確に)伝える努力をする必要があります。
(文章が巧みである必要はまったくありません。正しく伝えさえすれば良いのです)
でないと、いつまでたってもお望みの回答は得られないと思いますよ。
「やりたいこと」を伝えるだけなので、初心者とかは関係ないはずです。
「セルに入力した文字列を一覧表の中から抽出」の回答画像12
    • good
    • 0
この回答へのお礼

ありがとうございます。
ご指摘真摯に受け止めております。
仰ってる通りの事をしたいのですが、検索範囲内に検索値が複数有っても大丈夫でしょうか?
Sheet1に入力されているデータから、Sheet2に行の抽出をしたいと考えております。
よろしくお願いいたしますm(__)m

お礼日時:2020/04/15 16:21

[No.5お礼]へのコメント、


》 当方Excel初心者で、…
》 …から不明です。
「文章が下手」とか「初心者」とか何度も吹聴しないで。其れ位は讀めば解るので、寧ろ厭味です。
そんな事より、質問する際は御使ひの Excel のバージョン(に依り操作/説明が異なるので)を明記され度し。

私の Excel 2019 の操作だけど、「Alt+AC」は「Alt+MC」の間違ひでした、御免なさい。御指摘方々訊いて呉れて有難うございました。

[数式](forMula)⇒[定義された名前 <選択範囲から作成>](Create from selection) の所謂キーボード・ショートカットです。

ワークシートを開いた状態で、Altキーを約1秒以上押し續けると、メニュー上のコマンド名の傍らに白抜き文字が乗っかった黒豆腐が表示されるので、其の文字や數字を見乍ら續けて MC と打てば宜しいのです。

説明文を短くする目的で、私はショートカットを多用してゐます。
    • good
    • 0
この回答へのお礼

ご親切にありがとうございます。
使っているのはExcel 2019です。

仰せの通り実行しましたが、
セルI1に「#N/A」・セルA3には「#REF!」と表示されてしまいます。

ギブアップしそうです。。。

お礼日時:2020/04/15 15:24

[No.9]用の添附圖ですm(_._)m

「セルに入力した文字列を一覧表の中から抽出」の回答画像10
    • good
    • 0

[No.3お礼]へのコメント、



添附圖參照
例に依つて、下準備から、
Sheet1 の範囲 A1:I5011 を選擇⇒[名前ボックス]内に表示されている A1 を文字列 dbase に上書きして Enterキーをパシーツ⇒同範囲が選擇状態の儘で、Alt+MC⇒“上端行”のみにチエツク入れ⇒[OK]

愈々ホンバンです。Sheet2 に於いて、
1.セル B1 に「指定文字列」総務部を入力
2.セル I1 に次式を入力
 ̄ ̄=MATCH(B1,INDEX(dbase,0,MAX(IF(dbase=B1,COLUMN(dbase)))),0)-1
 ̄ ̄【御斷り】上式は必ず配列數式として入力の事
3.式 =INDEX(INDIRECT(A2),$I1) を入力したセル A3 を右方にズズーツと
 ̄ ̄オートフイル
    • good
    • 0

失礼、



誤記を 認めました、
お詫びの上、
訂正させて ください。


現行、

=IFERROR(SMALL(INDEX(($F$3:$F$8=$B$2)/ROW($A$3:$A$8),,),row(A1)+SUMPRODUCT((($F$3:$F$8<>$B$2)+0)),"")


改訂後、

=IFERROR(
  SMALL(
    INDEX(
      ($F$3:$F$8=$B$2)
      *ROW($A$3:$A$8)
      ,
      ,
    ),
    ROW(A1)
    +SUMPRODUCT(
      ($F$3:$F$8<>$B$2)+0
    )
  ),
"")

済みません お許しを。
    • good
    • 0
この回答へのお礼

うーん・・・

本当に色々とありがとうございます。
どんどんレベルアップしている気がします。
理解できない私めの能力に問題が。。。
引き続き努力あるのみですね。

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

何度も 済みません、


追記、

=IFERROR(SMALL(INDEX(($F$3:$F$8=$B$2)/ROW($A$3:$A$8),,),row(A1)+SUMPRODUCT((($F$3:$F$8<>$B$2)+0)),"")

此は 確か、
私が 著作を、
主張し得ない、

過去 日経21サイトに、
載っていた、

古来より 引き継がれ続ける、
式ですが。
(違ったかな?
此も 私が、
作った 式だったか… )


此の様に、

邪魔な 不適合値個数を、
先立って 調べ、
其の 個数分を、
データ中から 読み飛ばしても、

浮動小数部を 多用するよりは、
遙かに 速く、
リソースを 喰わず、

優位性が 顕著ですよ。
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!