Python＋xlwingsでExcel VBAの適正在庫シミュレーションを書き換えてみた
前回の記事Python初学者がUdemyのたった１つの講座を受けただけでできるようになったことで、たった一つのこの動画講座を視聴しただけでExcel VBAで作った適正在庫シミュレーションソフトをPythonに書き換えられるようになったとお伝えしました。
独学で身につけるPython〜基礎編〜【業務効率化・自動化で残業を無くそう！】
Pythonの入門に最適なコースです。初心者にとって挫折の原因となる過剰な知識を除外した必要十分な基礎レクチャーに加えて、実務での活用を見据えた実践レクチャーも収録。業務を効率化したいならVBAやRPAより断然Pythonがオススメです！
「素人がそんな簡単にPythonをマスターできるのか！？」
と疑う人も多いでしょう。
そこで今回はその証拠をお見せします。
Excel VBAでやっていること
本サイトではいろいろなタイプの適正在庫シミュレーションソフトを公開しています。
この中で一番簡単なのはVBAを使わずにExcelだけで行うもので、こちらになります。
これは単一SKUについての出荷実績データから適正在庫のシミュレーションを行うもので、複数SKUについて一度にシミュレーションすることはできません。
それを可能にしたものがこちらになります。
ここでは複数SKUについてそれぞれの出荷実績データを
(“Sales data”シート)
のように”Sales data”シートにまとめておき、このデータを１列ずつ”Calculation”シートの出荷の列にコピペします。
(“Calculation”シート)
すると、”Calculation”シートで在庫量の推移を計算してくれるので、その値を”Result”シートに値だけコピペします。
これをすべてのSKU について繰り返すことで、下図のようにSKUごとの在庫推移をシミュレーションすることができます。
(“Result”シート)
この中で、シート間のコピペ作業をExcel VBAが担っているわけです。
UdemyのPython講座で学んだこと
ここでUdemyのPython講座で学んだことをおさらいしておきます。
内容盛り沢山の講座ですが、その中にPythonによるExcel作業の自動化があります。
自動化といっても、ファイルもしくはシートをまたがってデータをコピペする簡単な作業の自動化です。
これは正にここでドンピシャでやりたいことです。
openpyxlを使って実装してみる
この講座ではPythonでExcelを操作するために、openpyxlというライブラリーを使っています。
そこで、このopenpyxlをインポートしてPythonでコードを書いてみると次のようになります。
３枚のシート“Sales data”、”Calculation”、”Result”が入った’Auto inventory simulation by Python ver.1.xlsx’というExcelファイルを読み込んで計算した後、その結果を’test.xlsx’という別のファイル名で保存しています。
wb1 = px.load_workbook('Auto inventory simulation by Python ver.1.xlsx')
ws1 = wb1['Sales data']
ws2 = wb1['Calculation']
ws3 = wb1['Result']
for k in range(10):
for i in range(2,122):
ws2.cell(row = 4+i, column = 2).value = ws1.cell(row = i, column = k+2).value
for j in range(51,126):
ws3.cell(row = j-49, column = k+2).value = ws2.cell(row = j, column = 4).value
wb1.save(filename = 'test.xlsx')
ところが出来上がった’test.xlsx’の”Result”シートには、次のように０の羅列が書き込まれてしまいました。
なぜこのようなことになってしまったのでしょうか？
試しに２月１８日の値を見てみましょう。
このように０という値ではなく、式が入っていることがわかります。
これに対応する”Calculation”シートのコピー元も見てみましょう。
同じ式が入っていますね。
つまり、”Result”シートには「値だけ貼り付け」したいところ、計算式が貼り付けられてしまっているためにデタラメな値になってしまっているのです。
そこでopenpyxlにも値だけ貼り付けするような機能があるのかをネットで検索してみました。
すると、ワークブック（ファイル）を読み込む時に、data_only=Trueを引数として追加すればよいことがわかりました。
つまりコードの冒頭箇所である
wb1 = px.load_workbook('Auto inventory simulation by Python ver.1.xlsx')を
wb1 = px.load_workbook('Auto inventory simulation by Python ver.1.xlsx', data_only=True)に書き換えるだけです。
ところが再びシミュレーションしてみると、次のような結果が”Result”シートに書き込まれました。
なんと今度はどのSKUも同じ在庫推移になってしまいました。
これはどう考えてもおかしな結果です。
これは動画の中でも触れられていましたが、openpyxlではセルに書き込まれている文字列を、それが値だろうが式だろうが区別なく入力しますが、その時点では入力されたままです。
そして入力された文字列がExcelによって解釈される（計算される）のは、
wb1.save(filename = 'test.xlsx’)
によってExcelファイルがセーブされる時です。
例えば、openpyxlによって１０が入力されているセルに”=1+1”が入力されたとすると、その時点ではそのセルの値は１０のままですが、ファイルがセーブされて初めて２に変わります。
これはExcelファイルを読み込む時にdata_only=Trueを指定しても、しなくても同じです。
そのため、
“Result”シートに書き込まれた参照式はすべてのSKUにつき同じ
⇒
ファイルをセーブするとすべてのSKUについて同じ結果になる（参照先は最後のSKUの値が反映される）
のです。
xlwingsを使って実装してみる
このようにopenpyxlを適正在庫シミュレーションの用途に使うのは難しそうです。
そこでPythonでExcelを操作するライブラリーが他にないかネットで調べてみました。
するとxlwingsというライブラリーもあり、これだと参照式を常に引き継ぎながら計算できることがわかりました。
コードの書き方（関数の文法）はopenpyxlとは少し異なりますが、ネットにいくらでも記事が載っているので真似しながらコーディングできます。
次のようにコーディングしました。
import xlwings as xw
wb1 = xw.Book('Auto inventory simulation by Python ver.1.xlsx')
ws1 = wb1.sheets['Sales data']
ws2 = wb1.sheets['Calculation']
ws3 = wb1.sheets['Result']
for k in range(10):
for i in range(2,122):
ws2.range((4+i, 2)).value = ws1.range((i, 2+k)).value
for j in range(51,126):
ws3.range((j-49, 2+k)).value = ws2.range((j, 4)).value
wb1.save('Output.xlsx')
wb1.close()出来上がったファイルOutput.xlsxを見ると、下図のように尤もらしい在庫推移が”Result”シートに書き込まれました。
openpyxlにはExcelの罫線やフォントなどの書式を引き継げるというメリットはありますが、今回のように同じ参照式から何度も値だけをコピペするような用途には向いていないことがわかりました。
値だけ貼り付けたい場合にはワークブックを読み込む時にdata_only=Trueを引数として指定すれば、そのワークブックをセーブする時にコピー先の値を確定できますが、コピー元の参照式もその時点の値で確定されてしまうため、再び元データを変えて計算することができません。
つまり、同じ参照式を何度も値だけコピペすることができません。
もしうまいやり方をご存知の方がいれば教えて下さい。
これに対してxlwingsは操作対象のExcelのファイルを開いた状態で入力するため、人がExcelに手入力するように操作することができます。
勿論、元の参照式はそのままで、コピー先に値だけ貼り付けすることも可能です。
デメリットは計算スピードが遅いことです。
Excelファイルを開いたまま関数計算をするので、Excelの処理スピードの制約を受けます。
これは、すべてPythonでプログラミングすることで解決できます。
まとめ
このようにExcel VBAで書かれた適正在庫シミュレーションソフトをPython化するという目的は果たせました。
管理人はUdemyのこの講座を視聴するまではPythonのプログラミング経験ゼロでしたが、ここまでやることができました。
講座で扱っていないxlwingsのライブラリーを使ったため、厳密にはこの講座の内容だけでできたわけではありませんが、xlwingsの使い方はネットでググればいくらでも見つけることができます。
Pythonにはこの他にも多くのライブラリーがあり、関数の文法はそれぞれ異なりますが、そんな文法は覚える必要がないことは、この講座の安井氏も繰り返し言っていました。
そんなものはググればいくらでもヒットするので、その都度ググって真似すればいいだけです。
これも沢山の学習者がいる人気言語Pythonならではのメリットだと思いました。
独学で身につけるPython〜基礎編〜【業務効率化・自動化で残業を無くそう！】
Pythonの入門に最適なコースです。初心者にとって挫折の原因となる過剰な知識を除外した必要十分な基礎レクチャーに加えて、実務での活用を見据えた実践レクチャーも収録。業務を効率化したいならVBAやRPAより断然Pythonがオススメです！