VBA 単語群の置き換え。エクセルブック名置き換え.xlsm シート名作業台列 S列置き換

Question

VBA 単語群の置き換え。

エクセルブック名　置き換え.xlsm
シート名　　　　　作業台
列　　　　　　　　S列
置き換え前文章　　セルS4からS20000

3行目は、
列A から列ZAまで、見出しがあり、それぞれ項目A、項目B、右方向へ項目ZAまで表記されています。

文章の例　セルS4
今日はAAAとBBBにCCC時にDDD公園で会う予定。

S5を含めて下方に自由な文章が格納されています。疑問文や肯定文や否定文や会話の記録です。数字やアルファベットも混在しまた全角や半角の数字も混在しています。

これらがワークシート名　作業台　に　格納されています。

さて、このマクロ有効ブック名　置き換え.xlsmには、
もうひとつのワークシート名　
単語リストがあり、

1行目が項目の見出しとして使われ
セルA1が　置換前単語
セルB1が　置換後単語

セルA2 　AAA
セルA3　BBB
セルA4　CCC
セルA5　DDD
実はこの下の方向に単語は全部で3000種類くらいあります

単語は、人名や駅名や都道府県別の自治体名や郵便番号など、グループがあります。

セルB2　EEEE
セルB3　FFFF
セルB4　GGGG
セルB5　HHHH

このVBAのコードを知りたいです。
Loopを、単語の種類別にするのでしょうか。
電話番号や金額についての文章を全角で入力している部分もあります。

Qchan1962 · Accepted Answer

こんにちは
バグの可能性をつぶす事が必須と考えますが、Replaceを使用するとしたら
キーワード先頭に文字を追加する手法はうまく行かないかと
消したくないキーワードの文字間に入れなくては一意の単語にならないかと
たとえそうしても1文字書き換えの場合はダメですけれど、、

処理速度に関しては検証しないと何とも言えないのですがデモデータ作るのがネックで（実際と違うデータでは意味がないかも）面倒ですね
検証が必要な理由は色々と手法が考えられる為です。

現在どの様なコードなのか分からないのですが、
多くのキーワードで置き換えるならメソッドより関数の方が判り易いかもですが、速度はおそいですかね。

色々想像、アドバイスを練っても仕方ないので（自分だったらをベースに）
書くと下記のような感じ（すでにされているかもです）

データ加工ベースで処理速度はある程度しか考慮していません

Dim rpData As Variant, TrgData As Variant
    Dim Ary()
    Dim j As Long, i As Long, n As Long
    TrgData = Range("S4", Cells(Rows.Count, "S").End(xlUp))
    rpData = Range("A3", Cells(Rows.Count, "B").End(xlUp))
    For j = 1 To UBound(TrgData)
        ReDim Preserve Ary(n)
        Ary(n) = TrgData(j, 1)
        For i = 1 To UBound(rpData, 1)
            Ary(n) = Replace(Ary(n), rpData(i, 1), rpData(i, 2))
        Next
        n = n + 1
    Next
    Range("E4").Resize(UBound(Ary, 1) + 1, 1) = Application.Transpose(Ary)

表示されているシートが対象
暫定でE4セル以下に出力

未検証なのでコンパイルして確認と出力セルの変更が必要です。
試す場合は、A列B列S列を新規シートに書き出し試してください
スクリーンの更新停止など定番コードは割愛
出力対処範囲は事前にクリアーした方がわずかに早いかな？

Qchan1962 · Answer

＃７です
皆さんのアドバイスに同意なので書くことはないのですが
自身が挙げたコードについて検証してみたので追記します

元配列を作成している部分は、アクティブシートとなっていますが
別シート（該当シート）オブジェクトを指定する事が出来ます

データ内容によりますが
Ary(n) = Replace(Ary(n), rpData(i, 1), rpData(i, 2))前に
If InStr(TrgData(j, 1), rpData(i, 1)) > 0 Then　で分岐すれば
全体の処理速度を上げる事が出来るかもしれません。

この部分だけ
        For i = 1 To UBound(rpData, 1)
            If InStr(TrgData(j, 1), rpData(i, 1)) > 0 Then
                Ary(n) = Replace(Ary(n), rpData(i, 1), rpData(i, 2))
            End If
        Next

デモデータを作成して実行した結果20秒～1分程度でした
（データによるので参考にならないかも）
Replaceメソッドの方はこの場合、処理も遅くレンジレベルで書き換えが行われるので割愛

重大なバグが高い確率で予想されるロジックは＃８様ご指摘のように
検証する必要があると思います。

一度きりデータ加工に使うのであればよいけれど、、
プログラムで文字列を扱う場合は作る段階で工夫しないと難しいですね。
（よく氏名入力でボックスを変えるとかスペースを入れるとか、、あれです）

fujillin · Answer

No6です。

＞質問の骨子はループをつかって、
＞同じエクセルマクロブック内の単語リストシートを参照して
＞右の文章の単語を入れ替えていくという部分です
「遅い」と言っているので、現状ですでにできているのではないのでしょうか？
No5にも書きましたように、基本的には2重ループ（ループの中にループがある構成）になると思われます。
（No7様の例にもあるような、2重ループです）

しかし、それ以前に、No2様が示しておられる問題点の解決をどうするのかについてのロジックをきちんとしておかないとうまくいかないでしょう。
＞◆マークを７つ、当該単語の先頭に付ける処理をほどこし
（↑）これでうまくいくとは思えませんけれど・・・？

例えば、事前に置換前の語群を解析して、文字数の少ないものから順に処理してゆくとか。（それで意図通りなのかは不明ですけれど…）。
ヒットした場合には、直接置換せずにユニークな処理番号に一時的に置換しておくなど。
基本が文章処理なので、文章中に存在しないはずのコードを利用して（例：Char(27)とか）idコードを挟んでおくなどが考えられるかと。
語句検索を終了してから、改めて、idコードを置換後の語句群に戻すなど。
それでも、通常の数字を使うと、idコードを誤ってヒットさせない工夫も必要になりますけれど・・・
いっそのこと、0～9に対応させて、JISコードの未使用部分を使うとか、2進法のidにしておいて文字コードを2種類ですませてしまうとか…

・・・などを考慮すると、
＞質問の骨子はループをつかって、～～
ご質問内容の検索・置換をする部分よりも、ロジックの作成とそのための前・後処理等の方がコードの大半を占めることになるのではないかと想像しますけれど・・・？

fujillin · Answer

No5です。

文章を読んで意味がわからない場合は、無視してください。

＞現場は処理に10分ぐらいかかっています。
現状、どのように処理しているのか不明ですけれど、もしも行っていなければ、計算の最初に以下の処理を追加してみてください。
若干の速度向上が見込めます。
（既に行っている場合は、必要はありません）

Application.ScreenUpdating = False
　Application.Calculation = xlCalculationManual

※ 計算終了時に、元に戻すのをお忘れなきように。

https://docs.microsoft.com/ja-jp/office/vba/api/excel.application.screenupdating
https://docs.microsoft.com/ja-jp/office/vba/api/excel.application.calculation

fujillin · Answer

こんにちは

どのようなロジックで処理しているのか不明ですが、部分検索の場合、No2様がご指摘の「包含関係」にある語をどうしているのかが気になります。
さらに、処理の順序によっては「置換後」の単語が、再度、別の検索語にヒットしてしまう可能性もありそうです。

＞コードの中にループが3種類ある場合を考えると
上記のような問題点を無視して単純に考えれば、ループは2種類（単語群、文章群）の2重ループになるのではないでしょうか？（順番に処理してゆくたぐいのループとは思えません。）

＞処理スピードが遅くて困っています。
画面のリフレッシュやシート内の計算を停止しておくことで若干速くなります。
また、シートへの読み書きの回数が多いと遅くなります。
ループが約3000×20000回あるので、ループ内で1回の読み、書きをするだけで1.2億回のアクセスになります。（回数は、処理方法により大幅に増減しますが…）

メモリが許すのであれば、まとめてデータを読み込み、メモリ上で全部の処理を行ってしまうようにすれば、速度の向上を期待できると思います。
「文章群の読み書き」、「単語群の読み込み」の合計4回のアクセスで済ませることが可能になりますので。
（1回で一つの群へのアクセスが終わるものとしています。分散していたりするとそうはなりませんが）
あるいは、折衷案的に、文章群、単語群の一方をメモリに常駐させる形にしておくようにしたり、文章群は行1000単位でまとめて読み書きするようにしておくなどの工夫をすることで、それなりの速度向上を期待できるのではないかと推測します。
（現状の処理方法が既にそのようになっていれば、変わりませんけれど）

とは言え、最初に挙げた問題点に対する解決がなされていないと、気づかないところで期待とは異なる処理がなされていることになりそうな懸念を持ちます。

高木さんWIN · Answer

都道府県・区市町村については事前に特殊処理が可能として、その後の単語はその中に影響しないよう回避できるのでしょうか？
と言うのが気になる点です。

都道府県名・区市町村名・人名・施設名・・・・のどれにマッチングしたのかをどのようにして判断するのかでしょうかね。
どれかへのマッチングなら正規表現でも可能かと思えますが、どれにマッチングしたかは攻略できたとしても、そのマッチングが求めていたものなのかどうかデータ内容によって変わってくるかもですし。
それでも厳しいかもですね。

形態素解析ツールを用いる方法とかでの業務委託が宜しいかもと。
でもグレードの高い回答者様なら解決に導いてくださるかもしれませんね。

mygoonickname · Answer

No.1の者です。

コードをアップできると、もっと良いアドバイスがあるかと思います。
（見られて困る様な部分は、書き換えたりして。）

量が多い様でしたら、配列処理とかにした方が良いかも知れませんね。

高木さんWIN · Answer

あいまい検索を用いたとした時、他と該当してしまう可能性があるのなら結構厳しいのでは？

例えば人名：
前：山田
後：小山田

にしたいと思っても、住所で『山田町』とあったら『小山田町』となるのではないのかな？
なので『形態素解析（かな？）』を用いてその文章にどんな品詞の語句があるか分割し、その分割した語句を置換した後に連結させる必要がありそうですけど？
数字なんかは200→300が1200→1300なんてのもありそうですね。
文章ではなく語句のみなら完全一致で回避できるかもですが。

あ”、お礼や補足（個人向け）は不要です。
このＩＤはめったに使いませんので、１～２ヶ月放置されてる場合もあります。
本来は別ＩＤですので。
他の方の参考に載せるって事なら補足欄はご自由に。

mygoonickname · Answer

おはようございます。

参考になりそうなリンクを貼っておきます。
先ずは、下記を参考に組まれてみては？と思います。

リストで複数語を連続置換
https://kokodane.com/2013_macro_36.htm

VBA 単語群の置き換え。 エクセルブック名 置き換え.xlsm シート名 作業台 列 S列 置き換

こんにちは

＃７です

No6です。

No5です。

こんにちは

都道府県・区市町村については事前に特殊処理が可能として、その後の単語はその中に影響しないよう回避できるのでしょうか？

No.1の者です。

あいまい検索を用いたとした時、他と該当してしまう可能性があるのなら結構厳しいのでは？

おはようございます。

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

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

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

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

VBA 単語群の置き換え。エクセルブック名置き換え.xlsm シート名作業台列 S列置き換