エクセル関数の検索・抽出・置換えについて質問です。

質問

以前 質問して、
素晴らしいマクロを教えて頂きました。

仕事が早くでき、楽しくなってきたところ、
なんと会社で不具合があり、マクロ禁止になってしまいました・・・ 　Σ（￣□￣)

改めて質問します！

２列の『旧 語録』の文に、
５列の『単語表・旧』が含まれていれば、

その単語だけが、
５列の右隣の、６列の『単語表・改正後』に変わり、

３列の『改正後の語録』のような文になるようにしたいのです。

５＆６列の『単語表』の配列は変えずに、
関数で　どうにかなるでしょうか？

回答を、心から　お待ちしております。

回答No.2

kagakusuki · ベストアンサー

もし、『旧 語録』の1つのセル内に、『単語表・旧』の文字列が複数含まれている場合には、その全てを関数を使って置換する事は困難です。
　そして、もし、『旧 語録』の1つのセル内には、『単語表・旧』の文字列が必ず1つ以下（1個か0個）しか含まれていないのでしたら、R3C3セルに次の数式を入力してから、R3C3セルをコピーして、R3C4以下に貼り付けると良いと思います。

=IF(SUMPRODUCT(ISNUMBER(FIND(R4C5:R11C5,RC2))*1)=1,SUBSTITUTE(RC2,INDEX(C5,SUMPRODUCT(ISNUMBER(FIND(R4C5:R11C5,RC2))*ROW(R4C5:R11C5))),INDEX(C6,SUMPRODUCT(ISNUMBER(FIND(R4C5:R11C5,RC2))*ROW(R4C5:R11C5)))),IF(SUMPRODUCT(ISNUMBER(FIND(R4C5:R11C5,RC2))*1)=0,RC2&"","置換不能"))

　尚、上記の数式では、『旧 語録』の1つのセル内に、『単語表・旧』の文字列が複数含まれている行があった場合には、「置換不能」と表示されます。

お礼

kagakusukiさん、
ご回答、ありがとうございます☆

早速　貼り付けて復習してみましたところ、できました！　感激です。
（『旧 語録』のなかには、『単語表・旧』が1つ以下しか含まれていません。）

睡眠時間と手間を裂いて 教えて頂いた関数を
これから勉強し、
大切に使わせて頂きます（＾ー＾）人

回答No.4

tom04

No.1です！
たびたびお邪魔します。

No.2様のお礼欄に
＞『旧 語録』のなかには、『単語表・旧』が1つ以下しか含まれていません。

とありますので一安心しました。
実は以前のVBAのコードも前回の数式での方法も同一セルに検索対象が複数ある場合は上手く動作しないはずでしたので・・・

それからついでと言っては失礼ですが、この際ですので
前回の数式はSUMPRODUCT関数を使っていて、PCにとってはかなりの負担になってしまいます。
せっかく作業用の列を使っていますので、少しでも軽く動く数式にしてみました。

前回の画像の配置だとして、Sheet1の作業列の数式はそのまま使っていただいて
Sheet1のC3セルの数式を

=IF(COUNT(D3:K3),SUBSTITUTE(B3,INDEX(Sheet2!$A$3:$A$10,MAX(D3:K3)),INDEX(Sheet2!$B$3:$B$10,MAX(D3:K3))),"")

としてオートフィルで下へコピーしてみてください。
少しはスムーズに動くと思います。
何度も失礼しました。m(__)m

お礼

tom04さん、他にも考えて下さって、ありがとうございます（ＵＵ）″

おっしゃる通りです！

実際は、『旧・改正後 単語』が千個以上あり、
『語録』は、少ない時もありますが、何万件もある事が・・・（・＿・）

それだけで容量が大きくなり、オートフィルターの操作だけで時間がかかります。

教えて頂いたマクロで、何日にも及ぶ作業が
一瞬で出来、みんなで喜んでおりましたが、思いがけないトラブル。

本当に、本当に助かります。
今からまた、復習してきます！　（＾＾）

回答No.3

kagakusuki

回答番号ANo.2です。
　もし、ANo.1様の御回答の様に、旧語録の数だけ作業列の列数を増やしても良い場合には、以下の様な方法を使用すれば、『旧 語録』の1つのセル内に、『単語表・旧』の文字列が複数含まれている行がある場合にも、正常に置換する事が出来ます。

　今仮に、旧と改正後の語録や、単語表が存在しているシートがSheet1であり、Sheet2の1～9列を作業列として使用するものとします。
　まず、Sheet2のR3C1セルに次の数式を入力して下さい。

=Sheet1!RC2&""

　次に、Sheet2のR3C2セルに次の数式を入力して下さい。

=SUBSTITUTE(RC[-1],INDEX(Sheet1!R4C5:R11C5,COLUMNS(C2:C)),INDEX(Sheet1!R4C6:R11C6,COLUMNS(C2:C)))

　次に、Sheet2のR3C2セルをコピーして、R3C3～R3C9の範囲に貼り付けて下さい。
　次に、Sheet2のR3C2～R3C9の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　次に、Sheet1のR3C3セルに次の数式を入力して下さい。

=Sheet2!RC[6]&""

　次に、Sheet1のR3C3セルをコピーして、Sheet1のR3C4以下に貼り付けて下さい。

　以上です。

お礼

お～っ！
kagakusukiさん、ふたつも回答してくださって
ありがとうございます（＾▽＾）

復習しましたところ、これも又、すぐに出来ました！
ご回答に　感謝です。

教えて！ｇｏｏでの、最初の質問の時も
助けていただきました。（締め切るのを忘れてた・・・）

マクロが使えない会社での作業、
教えて頂いた数々の関数が、非常に心強い 頼みの綱です。

心から、感謝しております。

回答No.1

tom04

こんばんは！
前回回答した者です。

VBAの方が簡単なのですが、どうしても関数での方法がご希望のようなので・・・
一例です。
↓の画像（小さくて見づらいかもしれません）のようにSheet2に表を作成しておき、それを利用します。
どうしても作業用の列が必要になってしまいます。
↓の画像の配置での数式になります。

Sheet1の作業列D3セルに
=IF(COUNTIF($B3,"*"&INDEX(Sheet2!$A$3:$A$10,COLUMN(A1))&"*"),COLUMN(A1),"")
という数式を入れ、列方向にSheet2の旧語録の数だけ（8列）右へオートフィルでコピーし、
そのまま下へオートフィルでコピーします。

そして、Sheet1のC3セルに
=IF(COUNT(D3:K3),SUBSTITUTE(B3,INDEX(Sheet2!$A$3:$A$10,SUMPRODUCT((D3:K3<>"")*COLUMN($A$1:$H$1))),INDEX(Sheet2!$B$3:$B$10,SUMPRODUCT((D3:K3<>"")*COLUMN($A$1:$H$1)))))
という数式を入れオートフィルで下へコピーすると画像のような感じになります。

尚、作業列が目障りであれば非表示にするか、離れたセルに作業用の列を設けた方が良いかもしれません。

この程度しか思いつきませんが、他に良い方法があればごめんなさいね。m(__)m

お礼

tom04さん（＾▽＾）

毎回、貴重なお時間をさいて頂き、ありがとうございます。

マクロ残念で仕方ありません（u_u)q

今回も、わかりやすく画像を付けてくださり感謝です。
今から、即効で復習させていただきます☆