[[20180611121236]] 『文字列の一致を抽出』(ゾーマ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『文字列の一致を抽出』(ゾーマ)

一致する部分のみ抽出する関数が作りたかったのですが、頭が固くて思いつきませんでした。
やりたいこととしては

ファイルシートA列に
【リンゴ】.xls
【バナナ】.xls
とファイル名が並んでおり、

リストとして別のタブ(ここではsheet1としておきます。)
に抽出したい文字(バナナ、リンゴ、イチゴ)などがA列に並んでいます

ファイルシートB列に関数を入れ込むと
リンゴ
バナナ
とリストのなかから一致している部分だけ抽出して表示させたいです。

すべて同じ【●●●】.xlsならRIGHTやMIDなどでやろうかと思ったのですが、
コピー【●●●】.xls(頭にコピーがついている)や リンゴ.xls(←括弧がない) などで不規則です。

たぶん*とVLOOKUPでなんとかできそうなのですが苦戦しているためご協力よろしくお願いいたします。
伝わっていないことはご質問いただければ追記いたします。

< 使用 Excel:Excel2013、使用 OS:Windows10 >


ちょっとめんどくさいですが、余計な文字をいったん置換してしまって、比較するのはどうでしょうか?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"コピー",""),"【",""),"】","")
(Null) 2018/06/11(月) 12:33


Null 様
ありがとうございます。
不要な文字が分かっていれば空白置換できるのですが、
コピー(リンゴ)(2).xlsなどのトンでもファイル名もあって
関数がとてつもなくなってしまうので断念しました。

リストから抽出方式で頑張りたいです。
(ゾーマ) 2018/06/11(月) 12:38


 ファイルシートのB1セルに
 =IFERROR(INDEX(Sheet1!A:A,SMALL(IF(IFERROR(FIND(Sheet1!A$1:A$100,A1),)*(Sheet1!A$1:A$100<>""),ROW($1:$100),""),1)),"")
 と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定(確定後、式が{}で囲まれればOK)その後、
 下へフィルコピーではどうか。
(ねむねむ) 2018/06/11(月) 13:18

 なおSheet1のリストは最大100行目までとしている。
 もっとある場合は式中の$100をすべて同じ値で大きくしてくれ。
(ねむねむ) 2018/06/11(月) 13:20

ねむねむ 様

配列数式ですね。
とりあえずためしたところできました!
意味は後でじっくり分解して考えます。

ありがとうございました。
(ゾーマ) 2018/06/11(月) 13:46


 以下の式でも出来そうですね。

 =IFERROR(INDEX(Sheet1!A:A,MATCH(1,INDEX(COUNTIF(A1,"*"&Sheet1!A$1:A$100&"*"),0),0)),"")
 下にフィルコピー

(sy) 2018/06/11(月) 16:07


 あっ!
 &"" を忘れてました。

 =IFERROR(INDEX(Sheet1!A:A,MATCH(1,INDEX(COUNTIF(A1,"*"&Sheet1!A$1:A$100&"*"),0),0))&"","")

(sy) 2018/06/11(月) 16:11


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.