2012
11
12

特定の文字(複数可)を検索して、転記や書式変更するExcelVBA

ちょっと前にGoogleAnalyticsの検索結果を、複数指定した単語を全部抜き出して分析するExcelVBAを作ったんですが、そこで書いた検索(もどき)のコードを解説してみました。


概要

121112-1

選択していない「data」という名前のシートに、文字列がA1から下にずらっと並んでいるとして、その中から指定した複数キーワードにひっかかるものだけをアクティブシート(「data」シートとは別)のA1から下へ転記していくというものです。冒頭で書いた分析は、これをベースにつくっています。

コード

ちょっと長いですが、こんな感じで。コピペすればそのまま動くと思います。

初心者の方はこちらを参考にどうぞ!

解説

キーワードを分割して配列へ

まず一番最初の4行目でInputBoxへキーワードをユーザーに入力してもらいます。そのキーワードをとりあえずstrという変数にまるごと格納。

15~26行の中で、strをスペース毎に分割して、配列へひとつずつ入れていきます。まずは16行目で、str内にスペースが含まれているか判別します。

ここでキーワードがひとつだった場合、又は全て分割されて、最後のひとつだった場合の処理をしています。キーワードを格納してDoを抜けます。

次に19行目、スペースが含まれていた場合です。ここで使っているのが、

です。strの左からn番目の文字をひとつずつ、スペースかどうか判別しています。スペースでなければnを+1して繰り返し。スペースが見つかったら左側をキーワードとして格納、右側をstrに上書き、という作業をしています。

121112-2

イメージとしては、こんな感じ。これを繰り返すと最終的にスペースがなくなるので、先程の16行目からIf内に入っていってDoを抜けるという寸法です!

転記前の文字列と照らし合わせる

28~53行で、「data」というシートの文字列と、先ほど分割したキーワードをひとつずつ照合させて、ひとつでも含まれているものがあれば転記するということをしています。ここはそんなに難しくはないですね。

いかがでしたでしょうか。7,8,35,36行目で半角・小文字に変換してから照合することでそのあたりの検索バラつきには対応しております。スペースにも半角・全角あるので、ここは必須かも。結構ニッチな内容な気もしますが、どなたかのご参考にでもなれれば幸いですー。

追記

コメント欄にてリクエストを頂いたので、作ってみました。

行全てを転記する

こちらは、ほぼ上記のコードでいけるので、変更点のみ。46行目と47行目をこちらへ差し替えます。

こちらは値のみを貼り付けするので、文字色など書式もコピーしてきたい場合はPaste:=xlValuesを削除してください。

検索ヒットしたテキストだけを強調

こちらは転記ではなく、アクティブシート内のキーワードを太字・赤にします。

セル内のキーワードのみ、太字や赤字にする方法はこちらを参考にさせていただきました

今回の肝は、「半角・全角や、小文字・大文字なども検出する」ということを前提に書いてきました。そのため、前述の転記するコードではキーワードと比較対象の両方を半角・小文字へ変換してから比較するという方法をとっています。これが、転記のときは単なるコピペだったので問題なかったのですが、セル内の特定文字を太字などにしたい場合、

という書き方になるようでして、対象文字列を半角へ変換したときに一部カタカナで文字数が変わってしまい、不具合が起こりました。

例えば、「グラス」は3文字ですが、「グラス」にすると4文字として処理されてしまうのです。このズレによって正しい位置が特定できなくなってしまいます。

そのため、今回のコードでは、キーワードを配列化するときに半角と全角で文字数が違うものは両パターン格納しておき(16~18, 23~26行)、比較対象の文字列は小文字変換のみにして半角化は行わない(50行)、という方法をとることにしました。

と、いう感じでいかがでしょうか。これはわたしも仕事で使えそうです!teruさんありがとうございましたー!

  • このエントリーをはてなブックマークに追加
  • follow us in feedly 618
  • RSSを登録

公開日:2012/11/12
更新日:2013/12/03


2件のコメント

  • teru
    2013年12月3日 10:08 AM

    Google検索から来ました。日付が確認できないので、古い記事かもしれませんね。
    マクロの解説ありがとうございます。
    ここでは、スペース区切りテキストを別シートに転記していますが、転記するときにセルではなくヒットした行全体を転記するとか、または、検索ヒットしたテキストだけを指定した強調表示にできると、大量の自然文章が格納されたExcelシート(学術情報や特許情報など1行1データ形式、列は項目ごとのデータです。)を精査するときに大変助けになりますので、もしお暇がありましたら、ぜひ作ってみてください。

    • *you
      2013年12月3日 8:07 PM

      teruさん、リクエストありがとうございます。
      すすすみません日付はタイトルの左側に画像で出てるやつなんです、わかりにくくてすみません…!
      わたしとしても興味のある案件だったので、早速作ってみました。記事に追記したのでご参照ください。

コメントを残す




*印は必須項目です。コメントは承認制ですので、反映までしばらくお待ち下さい。(稀にですがスパムの誤判定にて届かないこともあるようですので、必要な際はお問い合わせからお願い致します。)


back to top