エクセルで置換シートを用いた変換について教えてください。

Question

エクセルで日本語で書かれているsheet1をsheet2にある英語変換表を用いて英語に変換したいのですが、以下のマクロで実行すると、
例えば「建物」は「Building」になるのですが「建物附属設備」は「Interior Work」に変換されるべきところ「Building付属設備」となってしまいます。
他にも置換リストに同様の参照事項があると、同様に「日本語+英語のミックス」になるのですが
これはどのように改善すればよろしいでしょうか？
マクロは全くの素人で、ネットでこのようなことを書いてくださっている記述をコピペしただけなので、ド初心者にどうぞよろしくご指導ください。m(__)m



Sub 置換() 'この行から
Dim i As Long, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
For i = 2 To wS2.Cells(Rows.Count, "A").End(xlUp).Row
wS1.Cells.Replace what:=wS2.Cells(i, "A"), replacement:=wS2.Cells(i, "B"), lookat:=xlWhole
Next i
End Sub 'この行まで

tom04 · Accepted Answer

No.2・4です。

ん～～～
何も変化なしですかぁ～～？
こちらではちゃんと置換できたのでそのままコードを記載したのですが・・・

一つ気になるのは、Zongaiさまの補足欄に
＞A1に　建物　B1にBuilding
＞A2に建物付属設備　B2にInteriorWork

とありますね。
お示しのコードはSheet2の2行目以降のループになっているので
当然1行目は項目行で置換データは2行目以降にあるものだとして投稿しました。

もし1行目から置換データがあるのであれば
前回のコードの変更が必要です。

Sub 置換2()
　Dim i As Long, lastRow As Long
　Dim wS1 As Worksheet, wS2 As Worksheet
　　Set wS1 = Worksheets("Sheet1")
　　Set wS2 = Worksheets("Sheet2")
　　　lastRow = wS2.Cells(Rows.Count, "A").End(xlUp).Row
　　　wS2.Range("A:A").Insert
　　　　Range(wS2.Cells(1, "A"), wS2.Cells(lastRow, "A")).Formula = "=LEN(B1)"
　　　　wS2.Range("A1").CurrentRegion.Sort key1:=wS2.Range("A1"), order1:=xlDescending, Header:=xlNo
　　　　wS2.Range("A:A").Delete
　　　For i = 1 To wS2.Cells(Rows.Count, "A").End(xlUp).Row
　　　　wS1.Cells.Replace what:=wS2.Cells(i, "A"), replacement:=wS2.Cells(i, "B"), lookat:=xlPart
　　　Next i
 End Sub

こんな感じになると思います。

※　変更箇所はご自身で確認してみてください。m(_ _)m

zongai · Answer

ちょっと補足漏れがありましたので訂正。

------------

書かれているものについて、

> wS1.Cells.Replace what:=wS2.Cells(i, "A"), replacement:=wS2.Cells(i, "B"), lookat:=xlWhole

最後の「lookat:=xlWhole」はセルの完全一致で置換するので、
<<Sheet1>>に「建物」は「建物附属設備」という言葉【セルに単独で入っている】のであれば、現状で望んだ通りの結果になるはずです。
<<Sheet1>>に「建物附属設備とは建物の～」と書かれているセルでは全く置換が行われません。

------------

<<Sheet1>>　が抜けてました。

zongai · Answer

Sheet1　と　Sheet2　がごちゃごちゃに解釈されてしまってるようなので、

強調して書いてみました。

なお、完全一致で変換するなら「Building付属設備」のような変換はありえません。
これは部分一致です。
このへんの言葉と動作の認識もおかしな感じになっちゃってますね。

----------------------------------

書かれてるコードですが、

<<Sheet1>>の全セルに対して、
<<Sheet2>>の２行目の言葉から順に置換を行っていく、

というもので間違いないですよね？

------------

書かれているものについて、

> wS1.Cells.Replace what:=wS2.Cells(i, "A"), replacement:=wS2.Cells(i, "B"), lookat:=xlWhole

最後の「lookat:=xlWhole」はセルの完全一致で置換するので、
「建物」は「建物附属設備」という言葉【セルに単独で入っている】のであれば、現状で望んだ通りの結果になるはずです。
「建物附属設備とは建物の～」と書かれているセルでは全く置換が行われません。

------------

「lookat:=xlPart」にすると、セルの部分一致で置換します。

<<Sheet1>>
「建物」
「建物附属設備」
「建物附属設備とは建物の～」

と書かれているセルがあるとしたら、
まず、<<Sheet２>>で、より上にある「建物」で変換が行われ

<<Sheet1>>
「Building」
「Building附属設備」
「Building附属設備とはBuildingの～」

となり、<<Sheet２>>で「建物」より下の行にある「建物附属設備」を変換しようにも既にその言葉は存在しない状態になってしまいます。

失敗ですね。

------------

で・・・・
<<Sheet２>>の順番を文字数が長い順番に並び替えると・・・
まず、<<Sheet２>>で順位が上にある「建物附属設備」で変換が行われ

<<Sheet1>>
「建物」
「Interior Work」
「Interior Workとは建物の～」

となり、<<Sheet２>>で「建物附属設備」より下の行にある「建物」が後から変換されるので

<<Sheet1>>
「Building」
「Interior Work」
「Interior WorkとはBuildingの～」

という変換結果になります。

たぶん、コレが望む結果ですよね。

------------

勘違いされておられるようですが、

文字数で判断していません！

文字数は変換の優先度を決めるための順番に並べ替えるためにつかっているだけ。
変換には関わってきません。

「建物」「建物附属設備」「建物附属用品」の言葉があった場合、
文字数が多い順から並べると
<<Sheet２>>
「建物附属設備」
「建物附属用品」
「建物」
の順番になるでしょ？（1番目と2番めは逆になるかもしれないけど、影響なし）

この順番で<<Sheet1>>を変換していくことになります。

------------

こんな説明でどうでしょう。

やることは・・・

①「lookat:=xlPart」にするのと、
②単語データを文字数が多い順に並べ替え。

変換する場合、この並べ替えをしておくのは割とよく使うテクニックです。

tom04 · Answer

No.2です。

今後Sheet2のデータが増えても対応する方法にしてみました。
Sheet2のA列の文字数の降順に並び替えをするコードを加えています。

↓のコードではどうでしょうか？

Sub 置換()
　Dim i As Long, lastRow As Long  '//←変数の追加★//
　Dim wS1 As Worksheet, wS2 As Worksheet
　　Set wS1 = Worksheets("Sheet1")
　　Set wS2 = Worksheets("Sheet2")
　　　'//▼ここから追加//
　　　lastRow = wS2.Cells(Rows.Count, "A").End(xlUp).Row
　　　　wS2.Range("A:A").Insert
　　　　wS2.Range("A1") = "ダミー"
　　　　Range(wS2.Cells(2, "A"), wS2.Cells(lastRow, "A")).Formula = "=LEN(B2)"
　　　　wS2.Range("A1").CurrentRegion.Sort key1:=wS2.Range("A1"), order1:=xlDescending, Header:=xlYes
　　　　wS2.Range("A:A").Delete
　　　'//▲ここまで
　　For i = 2 To wS2.Cells(Rows.Count, "A").End(xlUp).Row
　　　wS1.Cells.Replace what:=wS2.Cells(i, "A"), replacement:=wS2.Cells(i, "B"), lookat:=xlPart   '//★//
　Next i
 End Sub

※　マクロを実行するたびに、Sheet2の並び替えを行っていますので、
文字列の長さは気にしなくてよいと思います。m(_ _)m

zongai · Answer

書かれてるコードですが、

Sheet1の全セルに対して、
Sheet２の２行目の言葉から順に置換を行っていく、

というもので間違いないですよね？

------------

書かれているものについて、

> wS1.Cells.Replace what:=wS2.Cells(i, "A"), replacement:=wS2.Cells(i, "B"), lookat:=xlWhole

最後の「lookat:=xlWhole」はセルの完全一致で置換するので、
「建物」は「建物附属設備」という言葉【セルに単独で入っている】のであれば、現状で望んだ通りの結果になるはずです。
「建物附属設備とは建物の～」と書かれているセルでは全く置換が行われません。

------------

「lookat:=xlPart」にすると、セルの部分一致で置換します。

「建物」
「建物附属設備」
「建物附属設備とは建物の～」

と書かれているセルがあるとしたら、
まず、Sheet２で、より上にあり「建物」で変換が行われ

「Building」
「Building附属設備」
「Building附属設備とはBuildingの～」

となり、それより下の行にある「建物附属設備」を変換しようにも素手にその言葉は存在しない状態になってしまいます。

失敗ですね。

------------

で・・・・
Sheet２の順番を文字数が長い順番に並び替えると・・・
まず、Sheet２で順位が上にある「建物附属設備」で変換が行われ

「建物」
「Interior Work」
「Interior Workとは建物の～」

となり、それより下の行にある「建物」が後から変換されるので

「Building」
「Interior Work」
「Interior WorkとはBuildingの～」

という変換結果になります。

たぶん、コレが望む結果ですよね。

------------

勘違いされておられるようですが、

文字数で判断していません！

文字数は変換の優先度を決めるための順番に並べ替えるためにつかっているだけ。
変換には関わってきません。

「建物」「建物附属設備」「建物附属用品」の言葉があった場合、
文字数が多い順から並べると
「建物附属設備」
「建物附属用品」
「建物」
でしすよね？
この順番で変換していくだけです。

------------

こんな説明でどうでしょう。

やることは・・・

①「lookat:=xlPart」にするのと、
②単語データを文字数が多い順に並べ替え。

変換する場合、この並べ替えをしておくのは割とよく使うテクニックです。

tom04 · Answer

こんにちは！

横からお邪魔します。

コードを拝見すると
＞・・・lookat:=xlWhole
となっていますね。
すなわちセル内完全一致の場合のみそのセル全体が置換されます。

というコトは質問文にあるような結果になることはないと思うのですが・・・

もしかしてSheet1のセル内に長文が入っていて、部分一致で置換したいのでしょうかね？

その場合は
＞lookat:=xlWhole
を
＞lookat:=xlPart
として、
No１さんが仰っているように文字列の長い順に置換すれば問題ないと思います。

※　同じ文字長であればどちらが先でも構いません。m(_ _)m

zongai · Answer

Sheet2の順番が、

建物　｜　Building
建物附属設備　｜　Interior Work

の順になっているから、
最初にヒットする「建物　｜　Building」の変換をしちゃうわけです。

なので、手っ取り早く解決するのは、文字数の多い順番に並べ替えること。

建物附属設備　｜　Interior Work
建物　｜　Building

これなら、先に「建物附属設備　｜　Interior Work」とが「建物〇〇　｜　△△△」を変換し、
どれにも絡まなかった「建物」が「Building」に変換されます。

Sheet2のC列に
[C1]=LEN(A1)
の式を入れて日本語の文字数を取得、全行コピー。
「データ」タブ→並べ替え→C列大きい順

で並べ替えれば望む結果になると思う。

エクセルで置換シートを用いた変換について教えてください。

No.2・4です。

ちょっと補足漏れがありましたので訂正。

Sheet1 と Sheet2 がごちゃごちゃに解釈されてしまってるようなので、

No.2です。

書かれてるコードですが、

こんにちは！

Sheet2の順番が、

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

Sheet1　と　Sheet2　がごちゃごちゃに解釈されてしまってるようなので、