Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2016)
部分一致でマッチした文字を抽出する方法
投稿日時: 18/09/27 22:50:00
投稿者: gori0615

部分一致でマッチした文字を抽出する方法を知りたいです。
LOOKUP関数とFIND関数を組み合わせて近いことはできたのですが、
(B列に入力する関数=LOOKUP(0,0/FIND($C$2:$C$5,A2),$C$2:$C$5)とします。)
 
1 A B C
2 AAA C列とマッチした値 AAA
3 AAA_BB C列とマッチした値 AAA_BB
4 AAA_CC C列とマッチした値 AAA_CC
5 BBB C列とマッチした値 AAA_DD
6 BBB_DD C列とマッチした値 BBB
:
 
C列で指定した値がA列に含まれていた場合、C列に指定した値をB列に入力したいのですが、
上記だと、例えばB3の期待値としては、AAA_BBが入力されて欲しいのですが、上から検索して
最初にマッチするAAAが入力されてしまいます。
A列から部分一致で検索し、C列と完全一致でB列に書き込む方法を教えて頂けないでしょうか。
ちなみにA列は少し端折ってますが、文章的なもので、そこにC列の文字列が含まれていると
考えて下さい。
 
わかり難い文章で恐縮です。
何卒宜しくお願いいたします。

回答
投稿日時: 18/09/28 18:22:45
投稿者: WinArrow
投稿者のウェブサイトに移動

説明がよくわかりませんが、
 
B列セルに
=VLOOKUP(C1,A:A,1,FALSE)
でよいのかな?
 
A列に存在しないと#N/A!エラーになるけど・・・・

回答
投稿日時: 18/09/28 21:15:14
投稿者: んなっと

なるべく長い文字数の検索語がヒットするようにしたいのですね。
 
D2
=LEN(C2)
下方向・↓
として、C:D列をD列をキーとして昇順に並べ替えてください。
 
      A   B    C    D
1 元の文字列 一致 検索語 文字数
2    AA_BB  BB   AA    2
3   AAAABB AAAA   AB    2
4    ABABB ABAB   BB    2
5   ABAAAA AAAA   AAA    3
6   ABABBB ABAB  ABAB    4
7   BC_BBB  BB  AAAA    4
 
それから
B2
=LOOKUP(1,0/FIND($C$2:$C$7,A2),$C$2:$C$7)
下方向・↓
とすればうまくいくと思います。
 
 
※注意してほしいのは、=LOOKUP(1,0/[条件]  )の形です。
=LOOKUP(0,0/[条件]  ) だと、以下のように期待したものと異なる結果を返すことがあります。
 
B2
=LOOKUP(0,0/FIND($C$2:$C$7,A2),$C$2:$C$7)
下方向・↓
 
      A   B    C    D          
1 元の文字列 一致 検索語 文字数           
2    AA_BB  BB   AA    2           
3   AAAABB  AAA   AB    2 ←AAAAになってほしい
4    ABABB  BB   BB    2 ←ABABになってほしい
5   ABAAAA  AAA   AAA    3 ←AAAAになってほしい
6   ABABBB  BB  ABAB    4 ←ABABになってほしい
7   BC_BBB  BB  AAAA    4           
 
 
 
 
●C列を並べ替えたくないときは...
 
      A   B    C
1 元の文字列 一致 検索語
2    AA_BB  BB   AA
3   AAAABB AAAA   AAA
4    ABABB ABAB  AAAA
5   ABAAAA AAAA   AB
6   ABABBB ABAB  ABAB
7   BC_BBB  BB   BB
 
B2
=INDEX(C:C,MOD(AGGREGATE(14,6,LEN(C$2:C$20)*1000+ROW($2:$20)/(FIND(C$2:C$20,A2)>0),1),1000))
下方向・↓
 
これなら検索語の範囲を厳密にC$2:C$7とする必要はなく、C$2:C$20のように少し余裕を持たせることもできます。

回答
投稿日時: 19/01/18 23:26:16
投稿者: masExcel

かなり難解な質問です。
(いろんな意味でw)
質問している意味が正しければ、下の計算式で対応できるかと・・・
内容的に、配列を使用しないと解決でないないようです。そうすると、数式は一気に難解になります。
てって、通常なら、vbaで対応した方が、スマートです。
 
       A B C E
1 AABAC B B
2 EEABBC EA EA
3 AAEACCF AAA A
4 FFBCA BCA BCA
5 BFBDEA FB FB
6
 
 
セル B1 の計算式
 
{=TEXTJOIN("",TRUE,IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),LEN(C1))=C1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),LEN(C1)),""))}
 

回答
投稿日時: 19/01/19 09:44:01
投稿者: んなっと

引用:
C列で指定した値がA列に含まれていた場合、C列に指定した値をB列に入力したいのですが、
上記だと、例えばB3の期待値としては、AAA_BBが入力されて欲しいのですが、上から検索して
最初にマッチするAAAが入力されてしまいます。

つまりこういうこと。
              A     B     C
1         元の文字列  最長一致   検索語
2   モモ&天Youtube犬猫動画    モモ     猫
3 モモ&黒猫天のほっこり動画   黒猫天     天
4   柴犬モモ&黒猫天の動画  柴犬モモ   黒猫天
5     柴犬&黒猫天の動画 柴犬&黒猫 柴犬&黒猫
6                        犬
7                       モモ
8                     柴犬モモ
 

回答
投稿日時: 19/01/19 09:54:54
投稿者: んなっと

ワイルドカードの可能性も考えると...
 
              A     B     C
1         元の文字列  最長一致   検索語
2   モモ&天Youtube犬猫動画    モモ     猫
3 モモ&黒猫天のほっこり動画   黒猫天     天
4   柴犬モモと黒猫天の動画 柴犬*黒猫   黒猫天
5   天が柴犬モモの下敷きに  柴犬モモ 柴犬*黒猫
6                    黒猫*柴犬
7                        犬
8                       モモ
9                     柴犬モモ
 
B2
=INDEX(C:C,MOD(AGGREGATE(14,6,LEN(C$2:C$20)*1000+ROW($2:$20)/(SEARCH(C$2:C$20,A2)>0),1),1000))
下方向・↓

トピックに返信