Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
文字列の置換について
投稿日時: 17/12/20 15:46:09
投稿者: どすこい

昨日の問合せの続きです。
 
下記のように1つのエクセルブックの中に2つのシートがあます。
Sheet1とSheet2に入る文字列はいつも変動します。
その前提でSheet1の2行目に関数を入れたいです。
その関数は、Sheet2のA1〜A列最終行の値が、Sheet1の1行目に含まれている(完全一致するケースは
あまりないので、部分一致)場合、Sheet2の値と同じ行のC列の値をSheet1の2行目に代入したいです。
 
この関数を入れて実現できた!と思ったのですがよく見ると、Sheet1の1行目とSheet2のA列の文字が「完全一致」しないと2行目の値が出力されません。
=INDEX(Sheet2!$C:$C,MATCH(0,INDEX(0/FIND(Sheet2!$A1:$A100,C1),),0))&""
 
たとえばSheet1の1行目には「A1」と書かれている場合もあれば「あああ/A1」や「A1.あああ」のような書き方の場合もあるので、完全一致ではなく部分一致でもヒットするようにしたいです。
もし解決方法があればおしえてください。
 
  
 //現状
<Sheet1>
     A列     B列    C列       D列
1行目  ああA1    空白  えええA2s1  えA2s2ええ
2行目
3行目
4行目
・ 

  
 <Sheet2>
     A列     B列    C列      
1行目  A1    空白    正解●
 2行目 A2s1 正解×
 3行目 A2s2 正解▲
 4行目 A3 正解2
・ 

//理想
<Sheet1>
     A列     B列    C列       D列
1行目  A1      空白   えええA2s1      えA2s2ええ
2行目   正解●         正解×      正解▲  
3行目
4行目
・ 

 

回答
投稿日時: 17/12/20 16:16:08
投稿者: WinArrow
投稿者のウェブサイトに移動

>=INDEX(Sheet2!$C:$C,MATCH(0,INDEX(0/FIND(Sheet2!$A1:$A100,C1),),0))&""
 
この数式でできると思いますが・・・
セルA2には
=INDEX(Sheet2!$C:$C,MATCH(0,INDEX(0/FIND(Sheet2!$A1:$A100,A1),),0))&""
セルC2には
=INDEX(Sheet2!$C:$C,MATCH(0,INDEX(0/FIND(Sheet2!$A1:$A100,C1),),0))&""
セルD2には
=INDEX(Sheet2!$C:$C,MATCH(0,INDEX(0/FIND(Sheet2!$A1:$A100,D1),),0))&""
 

投稿日時: 17/12/20 16:42:01
投稿者: どすこい

WinArrow 様
 
ご丁寧な回答ありがとうございます。
実はいただいた関数通りに設定しておりました。
しかし、この関数では、1行目の文字列前後の値によって、2行目がでない場合があります。
例えば、下記の場合セルC1の「A2s12」だと2行目が表示されましたが、
A2、D2、E2は空白のままでした。1行目の各セルの文字列の左右の文字や数字によるのだとおもうのですが
A2、D2、E2にも文字が表示されるようにする回避策はありますでしょうか。
 
   A列    B列   C列
1行目 aA1     えええ    A2s12    ...A2s2      A3iu
2行目             正解▲     

回答
投稿日時: 17/12/20 18:03:52
投稿者: んなっと

Sheet2の「A1」のうしろに半角スペースや改行しないスペースなどの余計な文字がついて
「A1_」のようになっている可能性もあります。
どこかからデータを取り入れ(貼り付け)たときについたゴミですね。
その場合は、削除して「A1」と入力しなおせばうまくいくかもしれません。
 
 
念のため新規ブックで試しましょうか。
 
・新規ブックでSheet2のA1に「A1」、C1に「正解●」と入力
 
  A B    C
1 A1   正解●
 
・次にSheet1のA1に「ああA1/うう」と入力して
A2
=INDEX(Sheet2!$C:$C,MATCH(0,INDEX(0/FIND(Sheet2!$A1:$A100,A1),),0))&""
としてみてください。
 
       A
1 ああA1/うう
2    正解●
 
「部分一致」で正しい結果を返すと思います。

投稿日時: 17/12/20 18:28:13
投稿者: どすこい

んなっと様
 
ありがとうございます!
Sheet2のA列をきれいにしたら、表示できました!!!
質問ばかりで申し訳ないのですが、無事に表示された今、
C列のように「A10」「A111」のような表記がある場合「A1」と同じ値が2行目に出力されてしまいます。
こちらは、回避策はあるのでしょうか。。。
 
A列      B列    C列
ああA1/うう    A2/うう    ああA10/うう
1         2          1
 
教えていただけたら大変助かります。

投稿日時: 17/12/20 18:37:37
投稿者: どすこい

また補足なのですが、sheeet1の1行目には各セルに
「A21n1」「A21n2」「A21n3」・・・・「A21n20」のようにデータが存在する場合もあります。
現状ですと、「A21n20」は「A21n2」と同じ値が、3行目に落ちてしまいます。。。。
 

回答
投稿日時: 17/12/20 19:14:13
投稿者: んなっと

《方法1》
 
Sheet2で
 
D1
=LEN(A1)
下方向・↓
としてD列全体→A列全体の順に選択 降順に並べ替え
 
    A B    C D
1 A2s11   正解○ 5
2  A2s1   正解× 4
3  2s12   正解▲ 4
4  A10   正解2 3
5   A1   正解● 2
 
これで上にあるものが優先され、
Sheet1の式は変えなくても一応最長一致になります。
 
 
しかし頻繁にデータ変更があって並べ替えが面倒なときは...
 
《方法2》Sheet2はそのままで
Sheet1
 
     A    B     C     D
1 あA10/う いA1い えA2s12え えA2s1え
2   正解2 正解●   正解▲  正解×
           ↑C2は正解×もあてはまるが、下にあるものが優先される
A2
=INDEX(Sheet2!$C:$C,MOD(1000*AGGREGATE(14,6,LEN(Sheet2!$A1:$A100)/(FIND(Sheet2!$A1:$A100,A1)>0)+ROW($1:$100)/1000,1),1000))&""
右方向・→
 
あまり確認していませんので、勘違いしているかもしれません。

投稿日時: 17/12/20 19:20:36
投稿者: どすこい

んなっと様
 
ありがとうございます!!!!!
<方法1>でできました!!!!!
半日モヤモヤ悩んでいたのですごくうれしいです。
大変勉強になりました。
どうもありがとうございました。