「Excel VBAの基本はマスターしたけど、グラフやピボットテーブルなど、Excelの多彩な機能を自動化するにはどのようにコードを書けばいいのかな？」
Excelの多彩な機能を自動化したいと考えている人に向けて、実例を使ってコードの書き方を調べる手順を解説します。
Excelのメインとなる機能は表を作ることですが、他にも多彩な機能を持つ万能なアプリです。
グラフ・ピボットテーブル・オートシェイプなど以前からあるものや、テーブル・スパークライン・モダンExcelなど、挙げてみるときりがありません。
また、日頃の業務の効率を上げるためにExcelをマクロで自動化する人も少なくありません。
Excelマクロを学習できる書籍や講座もたくさん出回っていますが、プログラミングの基本とセルや関数の操作について解説したものがほとんどだと言えるでしょう。
本記事では、ピボットテーブルを作成するためのVBAコードを完成させるという目標に向け、実際に手を動かした手順を公開します。
この手順はとても再現性が高く、Excelのほとんどの機能にも応用可能です。
本記事の内容を日々のマクロ作成に活用していただけるとうれしいです。
まずはマクロ記録機能でコードを作る
下の画像はピボットテーブルを作るときに使った売上入力用のテーブルです。
テーブルを原本に、マクロ記録でピボットテーブルを作ります。
行見出しに「品名」、値に「金額」を設定しました。
下の画像はマクロ記録で作成されたコードです。
冗長でムダが多いコードになっていますが、この中からピボットテーブルを自動作成するために必要なキーワードを調べていきます。
マイクロソフト公式リファレンスを使う
コードを上から眺めるとPivotCaches.Createというワードが使えそうなので、さっそくどんな動きなのか検索します。
ヘルプメニューをクリックすると、マイクロソフト公式リファレンスが開きます。
リンク先のマイクロソフト公式リファレンスサイトです。
検索窓にキーワードを入力します。
「すべての～」が表示されたらそのままクリックしましょう。
検索結果が表示されました。検索１位のページを開いてみます。
PivotCaches.Createメソッドの解説ページに飛びました。
ページをスクロールすると、パラメーターについての詳しい解説が載っています。
パラメーターは関数の引数と同じようなもので、構文を使いこなすためにも真っ先に目を通しておきたいところです。
さらに下へスクロールすると、例文も掲載されています。
高レベルのコードが載っていることも多いので、目を通しておくとスキルアップに役立ちます。
いったんパラメーターに戻り、SourceTypeに注目します。
SourceTypeパラメーターではXlPivotTableSourceTypeが設定値として使われています。
解説ページがあるようですので、クリックしてページに飛んでみましょう。
XlPivotTableSourceTypeの解説ページに飛びました。
Excelだけでなく、外部データベースなどからもピボットテーブルを作ることもできるようです。
列挙型の解説ページの「名前」と「値」の使い分けが特に重要です。
「名前」はExcel VBAだけに使えて、「値」はAccessからExcelを操作する時などに使う書き方です。
マクロ記録で自動作成されたコードを確認すると、xlDatabaseが使われています。
xlDatabaseを1に置き換えても正常に作動します。
今度はVersionに注目すると、省略可能なパラメーターとなっているようです。
記録されたコードに書かれているVersionのところを省略しましたが、エラー発生などの異常は見られませんでした。
こんな感じで、不要なところはどんどん削除していきましょう。
試運転後に不要箇所を削除する
マクロ記録されたコードの中で不要だと思われるものをコメント化しました。
ピボットテーブルのマクロ記録の場合では、自動生成されたコードの8割近くが不要だったようです。
コメントを削除すると、かなりスッキリとしたコードになりました。
もちろん、動作確認は忘れずに済ませておきましょう。
従来の手順の場合は、いったんここで完了となります。
おまけ-Chat GPTなどのAIを活用する
ついでに、Chat-GPTにピボットテーブルを作るVBAコードについて聞いてみました。
マクロ記録した操作をできるだけ忠実にAIに伝えていきます。
表のデータは一部のみを貼り付けました。
以下がChatGPTの回答内容です。
コードはそのまま下に貼り付けておきます。
Sub CreatePivotTable()
'ChatGPT作成分
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
Dim pf As PivotField
' ワークシートを取得
Set ws = Worksheets.Add
' ピボットキャッシュを作成
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
' ピボットテーブルを作成
Set pt = pc.CreatePivotTable(TableDestination:=ws.Range("A1"), TableName:="PivotTable1")
' 行見出しに「品名」を追加
Set pf = pt.PivotFields("品名")
pf.Orientation = xlRowField
' 値に「金額」の合計を追加
With pt
With .PivotFields("金額")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "金額の合計"
End With
End With
End Sub
変数や適切なコメントなどが入っていて、とても見やすいコードですよね！
さっそく期待に胸を膨らませプログラムを実行してみましたが、エラーが発生してプログラムが中断してしまいました。
デバックで中断した部分を確認したところ、ピボットテーブルを作成するところが黄色くなっているようです。
結論からすると一行前のSourceDataが原因でした。
セル範囲をテーブル名に修正することで正しくプログラムが動作しました。
今回は正確に伝えきれていない情報があったため、ChatGPTから一発で正解をもらえませんでした。
ChatGPTだけで自動化させるためのコードを完成させることは難易度が高く、マクロ記録とうまく掛け合わせるのが今のところ一番確実な方法のようです。
まとめ
今回は、Excelの様々な機能を自動化する際に、コードの書き方を調べる方法について紹介しました。
本記事ではピボットテーブルを例にあげて実演してみましたが、Excelの他の機能でもほとんど同じ手順で自動化することが可能です。
さらに、AIを活用する方法として、Chat GPTを使うのもとても有効な方法です。
ChatGPTはマクロ記録よりもずっときれいなコードに仕上げてくれますが、100％正しい動作を保証するものではありません。
現時点では、マクロ記録からスリムにしたコードとChatGPTで調べたコードを組み合わせ、自分のベストな回答を導き出すのが一番よい方法だと思います。
今回紹介した方法を使って、必要な機能を自動化するためのコードを学んでいけば、業務効率化につながるスキルを身につけることができるでしょう。