VBAを使って実務を効率化したいけど、複雑なことがやりにくい。
そういった場合にPythonを使うと処理が楽になることがあります。
だけどPythonだとExcelでの管理が出来ない…。こう思っている人いないですか？
実はPythonはExcelを操作することが出来ます。
それを使うと、日常で使うExcelの管理が、かなり楽になります。
さらに今回はVBAを使ってPythonを起動して、そのPythonでExcelを操作するという形で、実践でかなり使えるプログラムを紹介していきます。
そのために覚えて欲しい内容は3つで、
- VBAでシートの初期化
- VBAからPythonを起動
- PythonからExcelを操作
こちらをぜひ覚えていってくださいね。
VBAでExcelのシート初期化
最初は簡単にVBAでシートの初期化について解説していきます。
今回Excelのファイルとしては
- Excelファイルの名前：python_ope.xlsm
- シートの名前：収益管理
コードとしては、こういった感じです。
Sub シートの初期化()
Sheets("収益管理").Range("B2", "C300").ClearContents
End Sub
これはB2からC300のセルの値を消すという処理になります。
もしシート全体の値を削除したい場合には、こういった書き方も出来ます。
Sub シートの初期化()
Sheets("収益管理").Cells.ClearContents
End Sub
sheetsの後のかっこの中を自分の作ったシート名に変更してください。
VBAからPythonを実行する
そうしたら次はVBAからシェル（コマンドプロンプト）を起動してPythonを起動するVBAになります。
コードとしては、こんな感じです。
Sub RunPython()
Dim cmd As String
cmd = "python test.py"
Shell cmd, vbNormalFocus
End Sub
これはVBAから同一階層にある、test.pyというpythonファイルを起動するVBAになります。
もし実行出来ない場合には、pythonファイルをフルパスで指定すると実行できると思います。
その場合には、以下のような感じになります。
Sub RunPython()
Dim cmd As String
Dim pythonPath As String
pythonPath = "実行するPythonファイルのフルパス"
cmd = "python " & pythonPath
Shell cmd, vbNormalFocus
End Sub
pythonPathという変数に入れるpythonファイルのパスを自分のものに変更してください。
ちなみにこの実行する時に、Excelを読みこませる場合には
保存されたExcelファイルこちらが読みこまれます。
なので編集して保存をしてから実行しないと意図しない結果になることがあるので注意が必要です。
というので、そういった事故を防ぐために、
Sub RunPython()
Dim cmd As String
Dim pythonPath As String
pythonPath = "実行するPythonファイルのフルパス"
' 今のExcelファイルを上書き保存
ActiveWorkbook.Save
cmd = "python " & pythonPath
Shell cmd, vbNormalFocus
End Sub
ActiveWorkbook.Saveという一文を入れてあげると、保存忘れがなくなります。
VBAの最終コード
Sub RunPython()
Dim cmd As String
Dim pythonPath As String
pythonPath = "Pythonのフルパス"
Sheets("収益管理").Range("B2", "C300").ClearContents
ActiveWorkbook.Save
cmd = "python " & pythonPath
Shell cmd, vbNormalFocus
End Sub
PythonからExcelを操作
ここまででVBAからPythonを起動することが出来るようになったので、ここからはPythonでExcelを操作する方法の解説になります。
そのために
- openpyxl
- xlwings
この2つを自分のPython環境にインストールする必要があります。
インストールはpipで出来るので、
pip install openpyxl
pip install xlwingsこの2つを順番にやって、インストールをしてください。
Excelの読み込み
インストールが出来たら、まずはExcelを読みこんでいきます。
この時に使うのがopenpyxlになります。
コードとしては、
import openpyxl
# 読みこめない場合には、Excelのフルパスにする
excel_file_name = "python_ope.xlsm"
wb = openpyxl.load_workbook(excel_file_name)
ws = wb['収益管理']
cell_a2 = ws['A2'].value
print(cell_a2)
これでpython_ope.xlsmの収益管理シート内のA2のセルに入っている値を取得できます。
Excelへ書き込み
次はExcelへ書き込みです。
書き込みをする場合には、xlwingsを使います。
今回は例として、
- A2のセルの値を取得
- 10%の手数料を計算
- A2の値から手数料を引いて利益計算
- 手数料をB2に入力
- 利益をC2に入力
こういったコードになります。
import openpyxl
import xlwings as xw
excel_file_name = "python_ope.xlsm"
wb = openpyxl.load_workbook(excel_file_name)
ws = wb['収益管理']
cell_a2 = ws['A2'].value
commission = cell_a2 * 0.1
profit = cell_a2 - commission
xw.Range('B2').value = commission
xw.Range('C2').value = profit
こちらで読み込みと書き込みが出来るようになったわけですが、このままでは指定したセルの操作しか出来ないです。
なのでfor文を使って全てのデータを処理したいと思います。
for文で連続処理
今回はA列の2行目から21行目に売上金額が入っていて、B列に手数料、C列に利益を計算して入力するというものです。
コードは以下になります。
import openpyxl
import xlwings as xw
excel_file_name = "python_ope.xlsm"
wb = openpyxl.load_workbook(excel_file_name)
ws = wb['収益管理']
row_num = 2
for row in range(2, 21):
sale_price = ws['A' + str(row)].value
commission = sale_price * 0.1
profit = sale_price - commission
xw.Range('B' + str(row_num)).value = commission
xw.Range('C' + str(row_num)).value = profit
row_num += 1
この方法だと20行までと決まっていればいいのですが、行が増えた場合に毎回range(2, ●●)の部分を変更する必要があります。
そのためイテレーターというものを使用する方法が便利です。
import openpyxl
import xlwings as xw
excel_file_name = "python_ope.xlsm"
wb = openpyxl.load_workbook(excel_file_name)
ws = wb['収益管理']
row_num = 2
for row in ws.iter_rows(min_row=2):
sale_price = row[0].value
commission = sale_price * 0.1
profit = sale_price - commission
xw.Range('B' + str(row_num)).value = commission
xw.Range('C' + str(row_num)).value = profit
row_num += 1
こういった感じですね。
先ほどはfor文でrowに数字を順番に入れていって処理していましたが、iter_rows()を使用することで、行全体をrowに入れることが出来ます。
そのためrow[0]でA列に入っている値を取得できるようになります。
ちなみにmin_row=2は2行目から読みこむということです。
基本的に1行目はヘッダーになると思うので、基本はmin_row=2になるかと思います。
というので、これでPythonプログラムを使ってExcelを操作できるのですが、正直ちょっと速度が遅いです。
実践で使えるPythonコード
ここから、このPythonコードを高速化していこうと思います。
今回速度が遅い一番の原因としては、、
【Excelに値を入力】この部分になります。
そのため、入力回数を出来る限り減らすことで処理が高速になります。
実際にコードで書いてみると、こんな感じです。
import openpyxl
import xlwings as xw
excel_file_name = "python_ope.xlsm"
wb = openpyxl.load_workbook(excel_file_name)
ws = wb['収益管理']
input_data = []
for row in ws.iter_rows(min_row=2):
sale_price = row[0].value
commission = sale_price * 0.1
profit = sale_price - commission
input_data.append([commission, profit])
xw.Range('B2').value = input_data
これは一度Pythonのリスト（配列）に値を入れて処理をして、その配列をExcelに1度だけ入力しています。
ちなみにこの配列処理はVBAでも、かなり使う高速化のテクニックの1つです。
もしVBAを使っている人は、以下の記事で解説しているので見て下さいね。
ということで今回はVBAでPythonを実行。
さらにPythonでExcelを操作する方法について解説をしてきました。
Excelで何か管理をしている人は覚えるとめちゃくちゃ便利なので、ぜひ活用してくださいね。