エクセルのデータやCSVファイルを読み込んでクロス集計をする場合、VBAとPython(Pandas)を組み合わせると便利です。
以下でVBAとPythonをどのように組み合わせて処理を行うか説明します。
目次
CSVファイルを読み込んでクロス集計する
今回、扱うCSVファイルは以下です。
A列: entrydate
B列: userid
C列: name
D列: seibetsu
E列: age
F列: totalmoney
G列: birthday
上記のデータを以下のようにクロス集計(ピボットテーブル集計)していきます。
行：年月(entrydateの年月を取得)
列：seibetsu
値：totalmoneyの件数(セル数)、累計(セル内の数値の合計値)
上記の計算を行います。
このプログラムをVBA単独でやろうとすると、VBAプログラムが長くなるだけではなく、高度なVBA知識も必要になります。
そこでこのページではVBAとPythonを組み合わせて、合計で20行程度のプログラムで上記の処理を実行することを目指します。
VBAからPythonを使ってPandasでクロス集計させる
今回の方針としては、VBAからPythonを使ってPandasでクロス集計させます。
Pandasについては、以下のページで紹介しています。
VBAからPythonを呼び出す
残念ながら、VBAの初期設定のままではPythonを呼び出すことはできません。
実はVBAからPythonを呼び出すときには、xlwingsというツールを使う必要があります。
ツールのインストールから設定方法まで以下のページで詳しく紹介しています。
詳しくは以下をご覧ください。
VBAプログラムの解説
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
'プログラム0｜変数設定の指定 Option Explicit 'プログラムA-1｜Mainプログラム開始 Sub Main() 'プログラムA-2｜シート設定 Dim ws1 As Worksheet Set ws1 = Worksheets("Sheet1") 'プログラムA-3｜CSVファイルをダイアログで開く Dim filepath As Variant filepath = Application.GetOpenFilename("CSV,*.csv") 'プログラムA-4｜CSVファイルのパスをB1に出力 ws1.Range("B1").Value = filepath 'プログラムA-5｜集計後のエクセルファイルのパスをB2に出力 ws1.Range("B2").Value = ThisWorkbook.Path & "\" & "pivottable.xlsx" 'プログラムA-6｜プログラムBを呼び出す Call CallPython 'プログラムA-7｜プログラム終了 End Sub 'プログラムB｜Pythonファイルを呼び出す Sub CallPython() RunPython ("import crosstab_csv;crosstab_csv.main()") End Sub |
以下で詳しく説明します。
プログラム0｜変数宣言の指定
|
1 |
Option Explicit |
「Option Explicit」とは、変数宣言を強制するためのものです。
予期しないエラーを防止できるため「Option Explicit」を入力することを習慣化することを推奨しています。
詳しい説明は以下のページで紹介しています。
プログラムA-1｜Mainプログラム開始
|
1 |
Sub Main() |
VBAではプロシージャという単位でプログラムを呼び出します。
プロシージャの構文は下記となっています。
|
1 2 3 |
Sub プロシージャ名() '実行プログラム End Sub |
「Sub」で始まり「End Sub」で終わります。
プロシージャに関連するmoduleの話については以下で説明しています。
プログラムA-2｜シート設定
|
1 2 |
Dim ws1 As Worksheet Set ws1 = Worksheets("Sheet1") |
Sheet1をws1として扱います。
プログラムA-3｜CSVファイルをダイアログで開く
|
1 2 |
Dim filepath As Variant filepath = Application.GetOpenFilename("CSV,*.csv") |
ダイアログを開いて、対象となるCSVファイルを選択します。
プログラムA-4｜CSVファイルのパスをB1に出力
|
1 |
ws1.Range("B1").Value = filepath |
プログラムA-3で指定したCSVファイルのパスをセルB1に出力します。
ここでB1に出力する理由は、プログラムBで実行するPythonプログラムで対象とするCSVファイルのパスを取得するためです。
VBAとPythonのプログラム間での受け渡しができないので、一度エクセルに出力してパス情報を受け渡そうというわけです。
プログラムA-5｜集計後のエクセルファイルのパスをB2に出力
|
1 |
ws1.Range("B2").Value = ThisWorkbook.Path & "\" & "pivottable.xlsx" |
セルB2にクロス集計を実施したエクセルファイルのパスを出力します。
Pythonプログラム内で、セルB2の値を取得して、クロス集計したエクセルファイルの保存先のパスとして活用します。
プログラムA-6｜プログラムBを呼び出す
|
1 |
Call CallPython |
プログラムBの「CallPython」を実行します。
プログラムA-7｜プログラム終了
|
1 |
End Sub |
プログラム1と対になるプログラムで、プログラム終了させるための記述です。
「End Sub」を読み込むと、プログラムが終了します。
プログラムB｜Pythonファイルを呼び出す
|
1 2 3 |
Sub CallPython() RunPython ("import crosstab_csv;crosstab_csv.main()") End Sub |
VBA実行中のエクセルファイルと同じフォルダに保存してあるPythonファイル「crosstab_csv」の「main()」を呼び出します。
RunPythonメソッドは上記の引数を取ります。
なおRunPythonは、Pythonとxlwingsをインストールしていないと使えません。
詳しくは以下をご覧ください。
VBAからPythonを実行する方法について詳しく説明しています。
Pythonプログラム
呼び出すPythonプログラムは以下です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
# プログラム1｜ライブラリの設定 import xlwings as xw import pandas as pd # プログラム2｜mainプログラム def main(): # プログラム3｜Pythonとエクセルを接続 wb = xw.Book.caller() # プログラム4｜CSVファイルを読み込む filepath = wb.sheets('Sheet1').range('B1').value df = pd.read_csv(filepath, encoding='Shift-jis') # プログラム5｜年月で集計するための列を追加 df['年月'] = pd.to_datetime(df['entrydate']).dt.strftime("%Y-%m") print(df['年月']) # プログラム6｜ピボットテーブルとしてデータを出力 pivotdata = pd.pivot_table(df,index=['年月'],columns='seibetsu', values='totalmoney',fill_value=0, aggfunc=['count','sum']) # プログラム7｜ファイル名を取得 filename = wb.sheets('Sheet1').range('B2').value # プログラム8｜ピボットテーブルの結果をエクセルとして出力 pivotdata.to_excel(filename, sheet_name='Sheet1') |
以下で詳しく説明します。
プログラム1｜ライブラリの設定
|
1 2 |
import xlwings as xw import pandas as pd |
このPythonプログラムで使うライブラリを設定します。
xlwingsはVBAを実行しているエクセルファイルを取得するのに使用します。
pandasはクロス集計を行うときに使用します。
プログラム2｜mainプログラム
|
1 |
def main(): |
関数名をmainとして扱います。
プログラム3｜Pythonとエクセルを接続
|
1 |
wb = xw.Book.caller() |
VBAから呼び出したエクセルファイルをwbとして扱います。
プログラム4｜CSVファイルを読み込む
|
1 2 |
filepath = wb.sheets('Sheet1').range('B1').value df = pd.read_csv(filepath, encoding='Shift-jis') |
Sheet1のセルB1の値を取得してfilepathとします。
セルB1には、プログラムA-4でCSVファイルのパスを出力しているので、CSVファイルのパスを取得できます。
pandasを使って、CSVファイルを読み込みます。
対象とするCSVによって「encoding=’Shift-jis’」がないと、文字化けが発生する場合があります。
プログラム5｜年月で集計するための列を追加
|
1 |
df['年月'] = pd.to_datetime(df['entrydate']).dt.strftime("%Y-%m") |
対象のCSVファイルの[entrydate]の日付から「yyyy-mm」として年月を取得します。
なお、エクセルではなくPython上での計算なので、以下のプログラムで値をチェックします。
printで検証
|
1 2 |
df['年月'] = pd.to_datetime(df['entrydate']).dt.strftime("%Y-%m") print(df['年月'].head()) |
上記はPycharmのprint結果を表示しています。
「print(df[‘年月’].head())」で先頭の5つのデータのみ出力しています。
「yyyy-mm」でデータを取得できていることが分かります。
プログラム6｜ピボットテーブルとしてデータを出力
|
1 |
pivotdata = pd.pivot_table(df,index='年月',columns='seibetsu', values='totalmoney',fill_value=0, aggfunc=['count','sum']) |
「pd.pivot_table()」でCSVファイルのデータをクロス集計します。
df → CSVファイルのデータ
index=’年月’ → 年月の列をインデックス(ピボットテーブルの行)
columns=’seibetsu’ → seibetsuの列をカラム(ピボットテーブルの列)
values=’totalmoney’ → totalmoneyの列を値を算出(ピボットテーブルの中身)
fill_value=0 → ピボットテーブルの中身に対象となる値がないとき、0で埋める
aggfunc=[‘count’,’sum’] → ピボットテーブルの中身は件数と累計値を算出
上記の形でピボットテーブルを出力します。
実際のエクセルは以下です。
プログラム7｜ファイル名を取得
|
1 |
filename = wb.sheets('Sheet1').range('B2').value |
プログラムA-5で出力したエクセルファイルのパスを取得します。
プログラム8｜ピボットテーブルの結果をエクセルとして出力
|
1 |
pivotdata.to_excel(filename, sheet_name='Sheet1') |
プログラム6のピボットテーブルの結果を、プログラム7のfilenameで指定したエクセルファイル名(パス)で保存します。
「sheet_name=’Sheet1’」で出力するエクセルファイルの名前をSheet1とします。
VBAやPythonについて詳しく理解したいなら
VBAを活用すると、仕事を効率化できる幅を広げることができます。
たとえば私が実際にVBAを活用して効率化してきた作業は以下の記事で紹介しています。
興味がある人は以下の記事もご覧ください。
動画でも解説しています。
エクセルマクロVBAで出来ることを15の事例で紹介｜日常業務をラクにするヒントを見つけよう！
(音声が小さいので、ボリュームを上げてご覧いただければと思います)
またPythonについては以下で紹介しています。
VBAの勉強方法
私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。
少しずつレベルアップしながら、難しい内容に挑戦していくと効率的に学ぶことができます。
上記のリンクでは、VBA勉強に役立つ内容を紹介しています。
興味がある人はご覧ください。