最大1万円超分の電子書籍プレゼント♪

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を、単語の種類別にするのでしょうか。
電話番号や金額についての文章を全角で入力している部分もあります。

質問者からの補足コメント

  • 都道府県自治体名など住所の置き換えに関しては、特殊修理できるので、大丈夫な状態です。
    都道府県の数が限られる一方、市区町村名は数万くらいありますが、一定のルールがあるので。
    上手く説明できなくて申し訳ないです。
    現状持っている VBA は 手でマクロを動かしたときの操作を繰り返しているだけで VBA のコードの中に
    単語を格納しています。
    処理スピードが遅くて困っています。

    コードの中にループが3種類ある場合を考えると、ループ1を
    対象のセル2万行まで処理してからループににうつるのでしょうか?

      補足日時:2021/09/24 08:54
gooドクター

A 回答 (9件)

こんにちは


バグの可能性をつぶす事が必須と考えますが、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列を新規シートに書き出し試してください
スクリーンの更新停止など定番コードは割愛
出力対処範囲は事前にクリアーした方がわずかに早いかな?
    • good
    • 0
この回答へのお礼

誠にありがとうございます

お礼日時:2021/09/24 12:05

#7です


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

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

データ内容によりますが
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メソッドの方はこの場合、処理も遅くレンジレベルで書き換えが行われるので割愛

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

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

丁寧にありがとうございます。

お礼日時:2021/09/24 14:13

No6です。



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

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

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

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

文字の置き換えはある意味言語処理にかかる面があり当然ながら例外的なものの処理を集めた部分があるでしょう
人間の脳も論理的な処理と、大量の例外処理をしているのかもしれません。
現場は VBA に関する初歩的な技術が無く複数の単語をマクロ的に操作した結果の、コードを
数千種類長文にしたものです。

例外処理はこの現場の長文によって行うと思いますが

単語リストを参照して文字を置き換えていくという部分を学びたいという趣旨でした

他の皆さんも随分時間を使っていただいたようです

本当にありがとうございます

お礼日時:2021/09/24 13:30

No5です。



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

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

 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual

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

https://docs.microsoft.com/ja-jp/office/vba/api/ …
https://docs.microsoft.com/ja-jp/office/vba/api/ …
    • good
    • 0
この回答へのお礼

ありがとうございます

質問内容の脇役的な部分ですが参考にいたします。

質問の骨子はループをつかって、
同じエクセルマクロブック内の単語リストシートを参照して右の文章の単語を入れ替えていくという部分です

お礼日時:2021/09/24 11:47

こんにちは



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

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

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

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


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

初級レベル未満の状態で質問していることに限界を感じながらお尋ねしている状況です。
単語リストを参照しながらたくさんの任意の文章に含まれるものを置き換えるループを知りたいです。

置き換えてはならないものは現場はコードの中に単語を書き込んでおり、◆マークを7つ、当該単語の先頭に付ける処理をほどこし、自動で置き換えられないようにして、コードの最後に◆マークを一斉に除去する方法です。

現場は処理に10分ぐらいかかっています。

お礼日時:2021/09/24 10:46

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


と言うのが気になる点です。

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

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

ありがとうございます。
ここでは一旦単語リストを参照して置き換えていくループのマクロを知りたいという趣旨に読んで頂ければと思います。

お礼日時:2021/09/24 10:39

No.1の者です。



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

量が多い様でしたら、配列処理とかにした方が良いかも知れませんね。
    • good
    • 0
この回答へのお礼

そうですよね。
申し訳ないですが補足欄へ追記しましたように
VBA の技術がなく 単語を連続して置き換えるマクロをコードとしてコピー貼り付けしています。
当然ループはなく何千種類の単語をコードの中に埋め込んだひどい状態です。
やはり VBA で単語リストを読み込みに入って置き換えが最後まで終わったら次の単語リストを処理するというループが良いかと思います
もう恥ずかしくてコードの貼り付けはできません

お礼日時:2021/09/24 09:58

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



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

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

あ”、お礼や補足(個人向け)は不要です。
このIDはめったに使いませんので、1~2ヶ月放置されてる場合もあります。
本来は別IDですので。
他の方の参考に載せるって事なら補足欄はご自由に。
    • good
    • 2

おはようございます。



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

リストで複数語を連続置換
https://kokodane.com/2013_macro_36.htm
    • good
    • 1
この回答へのお礼

ありがとうございます

お礼日時:2021/09/24 08:40

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

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