Excelのマクロ(VBA)を使った自動化で顧客管理を効率化

マクロとは？VBAとは？

「マクロ」とは、ソフトウェアを自動で操作する機能のことです。自動化を実行する命令文自体を「マクロ」と呼ぶこともあります。「VBA」とは、自動化を実行するプログラミング言語「 Visual Basic for Application」を指します。マクロ、VBAという言葉は似た文脈で出てくるため混同することも多いですが、マクロは自動化の機能や命令文のことで、VBAはマクロに使うプログラミング言語だと覚えておきましょう。

マクロでできること

Excelのマクロでできることは、データの入力や転記、操作の自動化をはじめ、マイクロソフト社が提供するメールツール、Outlookとの連携など、多くの操作を実行できます。

Office スクリプトとの違い

プログラミング言語を使ったExcelの自動化については、「Office スクリプト」も存在します。「Office スクリプト」とは、Web版のExcelなどのために作られた自動化の命令文です。言語はVBAでなく、JavaScriptのスーパーセット（上位互換）である TypeScriptを使用しているのが特徴です。このOffice スクリプトは、Excelの[自動化]タブから設定します。本レッスンは、古いバージョンのExcelでも使えるVBAを使ったマクロについて解説するため詳細は触れませんが、オンラインでExcelを使うことが主流になるにつれてOfficeスクリプトもさらに使われるようになるでしょう。

Power Queryとの違い

Power Query は、Excel2016から登場した比較的新しい機能です。複数のExcelファイルや、CSVなどとのデータの接続、統合などを行うことができます。一度設定すれば、データ元が変更された場合にも更新する機能がある「自動化」の文脈で語られることがあり、マクロで実行していた操作もPower Queryで簡単に実装できる場合があります。Power Queryの詳細は、次のレッスンで解説します。

重複チェックのマクロを作ってみよう

これまでのレッスンで使ってきた「顧客管理・商談管理テンプレート」を使って、「見込み客・取引先マスタ」シートに重複がないかチェックするマクロをつくってみましょう。「見込み客・取引先マスタ」は顧客情報の基となるデータベースですので、重複が無いように登録していく必要がありますが、営業担当者全員が登録のたびに重複チェック作業をするの一定の時間がかかりますし、作業を忘れる可能性があります。そのため、重複チェックを自動化するマクロを用意して、営業担当者はボタンを押すだけでチェックが終わるようにするのが狙いです。

「顧客管理・商談管理テンプレート.xlsx」をダウンロード

下記が完成後のExcelです。

まず、「重複チェック」というボタンを押すと、会社名、窓口、メール、電話番号の列に同じ値が入っていたらセルが赤くなります。次に「重複チェック解除」というボタンを押すと、赤くなっていたセルが元に戻ります。2つのボタンそれぞれにマクロが割り当てられています。

重複チェックのVBAを登録しよう

マクロの設定は[開発]タブで行います。初期設定では[開発]タブは表示されていませんので、[ファイル]> [オプション]>[リボンのユーザー設定]で、[リボンのユーザー設定] および [メイン タブ] の下の [開発] チェック ボックスをオンにして表示させます。

[Visual Basic]をクリックすると、下記のようなウインドウが立ち上がります。このウインドウがVBAを書き込む場所です。左上のプロジェクトウインドウで右クリックし、[挿入]>[標準モジュール]を押下します。これは、今開いていたExcelについて、[モジュール]というVBAの記録場所を作る作業です。

右に表示された白紙のウインドウに下記の2つのVBAを連続して貼り付けてください。これは、[Module1]というVBAの記録場所に「重複チェック」と「重複チェック解除」という2つの命令文を登録する作業です。

Sub 重複ﾁｪｯｸ()
'
' 重複チェック Macro
'
'
Range("E:E,F:F,I:I,J:J").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

Sub 重複ﾁｪｯｸ解除()
' 重複チェック解除 Macro
'
'
Range("E:E,F:F,I:I,J:J").Select
Selection.FormatConditions.Delete
End Sub

VBAを2つ記録すると、End Subの次の行に、プログラムの記述が終わったことを示す区切り線がで自動で入ります。これで自動化のマクロを2つ作ることができました。

VBAは、1行目がプロシージャ名、いわゆるマクロ名（例: Sub 重複ﾁｪｯｸ解除()）、最終行がマクロの終了（End Sub）と決まっています。その間の黒文字がそれぞれ処理を示すコード、という構成です。ちなみに行の先頭に「'」のある行はメモで、コードではありませんが自身でわかりやすくするために記入します。

今回のマクロに使われているコードの説明を下記に記しますが、厳密に理解する、というよりも、マクロはExcelの操作の流れを分解して、それぞれコードで表現する必要がある、という構造を理解しましょう。構造を理解して、自動化したい具体的な操作をVBAでどう書くか調べることができる、というイメージをつかみましょう。

マクロ	説明
重複チェック
	Range("E:E,F:F,I:I,J:J").Select: E列、F列、I列、およびJ列を選択します。これは重複をチェックする範囲を指定しています。 Selection.FormatConditions.AddUniqueValues: 選択範囲に一意の値を強調表示する条件付き書式を追加します。つまり、同じ値が複数回現れる場合、そのセルを強調表示します。 Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority: 追加した条件付き書式を優先条件として設定します。これにより、複数の条件がある場合でも最初に評価される条件となります。 Selection.FormatConditions(1).DupeUnique = xlDuplicate: 一意でない（重複した）値がある場合に、指定された書式を適用します。xlDuplicateはExcelの定数で、重複した値に対する条件を指定します。 With Selection.FormatConditions(1).Font から End With: 重複が見つかった場合のフォントの書式を設定します。 - Color = -16383844: フォントの色を指定。 - TintAndShade = 0: 色調の濃さを0に設定。 With Selection.FormatConditions(1).Interior から End With: 重複が見つかった場合のセルの背景色の書式を設定します。 - PatternColorIndex = xlAutomatic: 背景色のパターンの色を自動に設定。 - Color = 13551615: 背景色を指定。 - TintAndShade = 0: 色調の濃さを0に設定。 Selection.FormatConditions(1).StopIfTrue = False: 条件が満たされても次の条件を評価するように設定します。 このマクロは、範囲内で列E、F、I、Jにおける値の重複を検出し、それを特定の書式で強調表示します。
重複チェック解除
	Range("E:E,F:F,I:I,J:J").Select: E列、F列、I列、およびJ列を選択します。これは重複チェックを解除する範囲を指定しています。 Selection.FormatConditions.Delete: 選択範囲に設定されている条件書式（ここでは重複チェック）を削除します。 このマクロは、指定された範囲で設定されている条件書式（重複チェック）を解除します。

マクロを実行するボタンを設置しよう

マクロを登録したら、それを実行するボタンをExcelのシート常に設置しましょう。[開発]タブのボタンを押下し、シートの任意の場所に「重複チェック」「重複チェック解除」というボタンをつくります。

設置したボタンを右クリックして[マクロの登録]ウインドウを立ち上げ、「重複チェック」のボタンには「重複チェック」のマクロを選択して[OK]を押します。「重複チェック解除」のボタンには「重複チェック解除」のマクロを選択して[OK]を押します。これで作成した2つのマクロがボタンと関連付けられ、それぞれのボタンを押すたびにそれぞれのマクロが実行されるようになりました。

「マクロの記録」を使ってみよう

Excelは、上記のようにVBAをゼロから書くこともできる一方で、自分の操作を自動的にVBAに書き出してくれる「マクロの記録」という機能があります。例えば、列の重複チェックのような決まりきった操作の場合（指定する列や変更する色が毎回完全に同じ、など）は、VBAにしたい操作をこの「マクロの記録」で記録し、実装する、という方法を取ることができます。

マクロの記録」を使う場合の注意点は、全てが記録される、という点です。例えば、自動化したい操作を始める前にH1セルを選択していた場合、H1セルを選択する、という操作もVBAに記録されます。不要な操作でコードを長くしてしまう原因になるので注意しましょう。

マクロ・VBAの注意点

Excelの自動化に活躍するマクロですが、VBAというプログラミング言語を誰もが理解し、修正できるわけではありません。現実には「情報システム部のXXさんがExcelが得意」というような状況ではないでしょうか？こうなると、共有のExcelファイルを加工してマクロが動かなったときに、特定の人に頼るしか無い状況に陥ってしまいます。

VBAにしても、Officeスクリプトにしても、共有のExcelファイルで導入する際は、職場の複数人が対応できる状況が望ましいでしょう。