Python+xlwingsでExcel VBAの適正在庫シミュレーションを書き換えてみた

Photo by Artturi Jalli on Unsplash

前回の記事Python初学者がUdemyのたった1つの講座を受けただけでできるようになったことで、たった一つのこの動画講座を視聴しただけでExcel VBAで作った適正在庫シミュレーションソフトをPythonに書き換えられるようになったとお伝えしました。

独学で身につけるPython〜基礎編〜【業務効率化・自動化で残業を無くそう!】

Pythonの入門に最適なコースです。初心者にとって挫折の原因となる過剰な知識を除外した必要十分な基礎レクチャーに加えて、実務での活用を見据えた実践レクチャーも収録。業務を効率化したいならVBAやRPAより断然Pythonがオススメです!

 

「素人がそんな簡単にPythonをマスターできるのか!?」

と疑う人も多いでしょう。

そこで今回はその証拠をお見せします。

 

【沸騰アジア!】

親日国カンボジアであなたの会社の商品を販売してみませんか?

食品/飲料ヘルスケアコスメ商流と物流を管理人がサポートします。

Naga World in Phnom Penh (2022)

Excel VBAでやっていること

本サイトではいろいろなタイプの適正在庫シミュレーションソフトを公開しています。

【著作権放棄!】適正在庫シミュレーションソフトをすべて公開します。

 

この中で一番簡単なのはVBAを使わずにExcelだけで行うもので、こちらになります。

【無料サンプル付き】適正在庫シミュレーションをエクセルで作る方法

 

これは単一SKUについての出荷実績データから適正在庫のシミュレーションを行うもので、複数SKUについて一度にシミュレーションすることはできません。

それを可能にしたものがこちらになります。

【VBAサンプル付き】適正在庫シミュレーションをエクセルマクロで自動化(前編)

 

ここでは複数SKUについてそれぞれの出荷実績データを

(“Sales data”シート)

 

のように”Sales data”シートにまとめておき、このデータを1列ずつ”Calculation”シートの出荷の列にコピペします。

(“Calculation”シート)

 

すると、”Calculation”シートで在庫量の推移を計算してくれるので、その値を”Result”シートに値だけコピペします。

これをすべてのSKU について繰り返すことで、下図のようにSKUごとの在庫推移をシミュレーションすることができます。

(“Result”シート)

 

この中で、シート間のコピペ作業をExcel VBAが担っているわけです。

 

UdemyのPython講座で学んだこと

ここでUdemyのPython講座で学んだことをおさらいしておきます。

内容盛り沢山の講座ですが、その中にPythonによるExcel作業の自動化があります。

自動化といっても、ファイルもしくはシートをまたがってデータをコピペする簡単な作業の自動化です。

これは正にここでドンピシャでやりたいことです。

 

openpyxlを使って実装してみる

この講座ではPythonでExcelを操作するために、openpyxlというライブラリーを使っています。

そこで、このopenpyxlをインポートしてPythonでコードを書いてみると次のようになります。

3枚のシート“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”シートには、次のように0の羅列が書き込まれてしまいました。

 

なぜこのようなことになってしまったのでしょうか?

試しに2月18日の値を見てみましょう。

 

このように0という値ではなく、式が入っていることがわかります。

これに対応する”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によって10が入力されているセルに”=1+1”が入力されたとすると、その時点ではそのセルの値は10のままですが、ファイルがセーブされて初めて2に変わります。

これは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の処理スピードの制約を受けます。

しかしExcel VBAもその点では同じなので、Excel VBAをより汎用的なPythonに書き換えるという目的には合致するのかなと思いました。

 

まとめ

このようにExcel VBAで書かれた適正在庫シミュレーションソフトをPython化するという目的は果たせました。

管理人はUdemyのこの講座を視聴するまではPythonのプログラミング経験ゼロでしたが、ここまでやることができました。

講座で扱っていないxlwingsのライブラリーを使ったため、厳密にはこの講座の内容だけでできたわけではありませんが、xlwingsの使い方はネットでググればいくらでも見つけることができます。

Pythonにはこの他にも多くのライブラリーがあり、関数の文法はそれぞれ異なりますが、そんな文法は覚える必要がないことは、この講座の安井氏も繰り返し言っていました。

そんなものはググればいくらでもヒットするので、その都度ググって真似すればいいだけです。

これも沢山の学習者がいる人気言語Pythonならではのメリットだと思いました。

独学で身につけるPython〜基礎編〜【業務効率化・自動化で残業を無くそう!】

Pythonの入門に最適なコースです。初心者にとって挫折の原因となる過剰な知識を除外した必要十分な基礎レクチャーに加えて、実務での活用を見据えた実践レクチャーも収録。業務を効率化したいならVBAやRPAより断然Pythonがオススメです!