現在私が、自社内でテストランしている給与計算を紹介します。
私はキャリアコンサルタントとして勤務している会社員ですが、派遣の管理も担当しています。派遣先によって給与の締めがバラバラだったり、タイムカードの書式が違うので担当する社員は混乱がちです。私もそうでした。少しでも入力作業を単純化しながら、一発で給与計算をさせるためにプログラミング言語、パイソンの活用をはじめています。
また当社ではプログラミング教室も運営しているのですが、小中学生に教えているパイソンを実用的に落とし込みたいという狙いもあります。このテストランで問題なければ、現在使用している外部のクラウドを使わなくて済むので経費削減にもなります。
パイソンは機械学習や深層学習のような場面で威力を発揮すると思うのですが、ここでは日々の仕事をちょっとでもラクにするためのツールとして紹介します。
正直、プログラミング言語のパイソン【Python】に関しては初心者ですが、大まかな命令の仕組みとエクセルとの接続方法がわかれば充分使えるレベルになります。エクセルデータを呼び出して、自分がやりたいように処理させるだけです。試行錯誤しながら組み上げたプログラムを紹介します。おそらく本職の方が見たら「え？」というレベルのシンプルなプログラムだと思いますが、以下の通りの内容です。
実際のプログラムでは当然個人名が入っていますが、この記事ではアルファベットで表しています。
下の図はタイムカードの入力画面（簡略化）です。
エクセルは【ヨシダ】という名前です。入力画面は【データ】というシート名にしています。シート名はそれほど重要ではないのですが、集計自体はパイソンが行うので、入力しやすいことを優先してレイアウトすることが大切です。
一番左端の列が【氏名】になっており、その隣が【日付】です。
ひとつ飛んで、【出勤】、【有休】、【始業】、【終業】、【休憩】です。これらの項目や並びは実態に合わせてアレンジしていいでしょう。入力はさすがに手を動かすので、早く誰がやっても打ちやすいことが優先です。月初から月末までのデータがここにずーっと並びます。
ここでは【基本時間】の集計に絞って紹介します。
このままでは（私のレベルでは）集計できないので、15列目では【終業−始業−休憩】の計算をして小数表示にします。これが【基本時間】になります。さらに15列目の値を22列目に【値貼り付け】をします。小数にしてから集計させる方法が、仕組みとしては（自分的には）わかりやすいかなと思ってこのような流れにしています。
セルに計算式が入っていると集計ができないので、純粋に数字だけがセルに入っているようにするために【値貼り付け】をしています。
準備としては【集計】シートも作ります。
下の図のようにスタッフごとに足し算して一覧表になることがゴールです。
上から２行目、３行目の【日付】と一番左端の列の【氏名】は、自分で設定するので入力します。このプログラムによって、設定した【日付】の範囲で【氏名】ごとに【基本時間】をすべて足し算してくれます。
設定する【日付】と【氏名】は必要に応じて変えればいいのですが、それ以外のセルには一切計算式が入っていないことが最大のポイントでしょう。
実際のプログラム全体は以下の通りです。
STEP6〜9は同じことを繰り返しているので、STEP8【基本時間】の集計を中心に解説します。
import openpyxl
#STEP1
from openpyxl import load_workbook
import datetime
#STEP2
filepath=r'C:Usersyoshi_000Documentsヨシダ.xlsm'
wb=load_workbook(filename=filepath)
ws1=wb['データ']
ws2=wb['集計']
#STEP3
startdate=datetime.datetime(int(ws2['B2'].value),int(ws2['C2'].value),int(ws2['D2'].value))
enddate=datetime.datetime(int(ws2['B3'].value),int(ws2['C3'].value),int(ws2['D3'].value))
#STEP4
lastrow1=ws1.max_row
lastrow2=ws2.max_row
lastcol2=ws2.max_column
#STEP5
values1=[[cell.value for cell in row1]for row1 in ws1]
#STEP6…1 【日数】の集計
for i in range(7,lastrow2+1):
for j in range(2,lastcol2+1):
counter6=0
for k in range(2,lastrow1):
if values1[k][0]==ws2.cell(row=i,column=1).value:
junedate=values1[k][1]
if startdate<=junedate<=enddate:
workingtime=values1[k][19]
counter6=counter6+float(workingtime)
#STEP6…2
ws2.cell(row=i,column=2).value=counter6
#STEP6…3
if counter6 is None:
counter6=0
#STEP7…1 【有休】の集計
for i in range(7,lastrow2+1):
for j in range(2,lastcol2+1):
counter7=0
for k in range(2,lastrow1):
if values1[k][0]==ws2.cell(row=i,column=1).value:
junedate=values1[k][1]
if startdate<=junedate<=enddate:
workingtime=values1[k][20]
counter7=counter7+float(workingtime)
#STEP7…2
ws2.cell(row=i,column=4).value=counter7
#STEP7…3
if counter7 is None:
counter7=0
#STEP8…1 【基本時間】の集計
for i in range(7,lastrow2+1):
for j in range(2,lastcol2+1):
counter8=0
for k in range(2,lastrow1):
if values1[k][0]==ws2.cell(row=i,column=1).value:
junedate=values1[k][1]
if startdate<=junedate<=enddate:
workingtime=values1[k][21]
counter8=counter8+float(workingtime)
#STEP8…2
ws2.cell(row=i,column=6).value=counter8
#STEP8…3
if counter8 is None:
counter8=0
#STEP9…1 【深夜時間】の集計
for i in range(7,lastrow2+1):
for j in range(2,lastcol2+1):
counter9=0
for k in range(2,lastrow1):
if values1[k][0]==ws2.cell(row=i,column=1).value:
junedate=values1[k][1]
if startdate<=junedate<=enddate:
workingtime=values1[k][22]
counter9=counter9+float(workingtime)
#STEP9…2
ws2.cell(row=i,column=8).value=counter9
#STEP9…3
if counter9 is None:
counter9=0
#STEP10 ヨシダ集計作成
newfilepath=r'C:Usersyoshi_000Documentsヨシダ集計.xlsx'
wb.save(newfilepath)
・・・・・・・・・・・・・・・・・・・・・・・・・これで以上です。
集計が一瞬で終わり、新しくヨシダ集計というエクセルが完成します。
ヨシダ集計の【集計】シートには、スタッフごとに【基本時間】の合計が入っています。ここでは扱いませんが、この【基本時間】に24を掛け算して、さらに時給を掛け算すれば給与となります。
それではひとつずつ分解して解説します。
★まずはパイソンでエクセルを呼び出すために「Openpyxl」というライブラリを使います。
すでにパイソンがインストールされているのであれば
「スタートメニュー」→「Windowsシステムツール」→「コマンドプロンプト」に「pip install openpyxl」を入力します。Enterを押せば使えるようになります。
★「#STEP〜」の行は、自分がわかるように区切っている印なので無視してOKです。
import openpyxl
#STEP1
from openpyxl import load_workbook
import datetime
★ここまでは何も考えなくてもいいくらいの定番です。エクセルを呼び出して使えるようにして、さらに日付を扱う関数を呼び出しています。datetimeはパイソンをインストールしていれば標準装備されています。
#STEP2
filepath=r'C:Usersyoshi_000Documentsヨシダ.xlsm'
wb=load_workbook(filename=filepath)
ws1=wb['データ']
ws2=wb['集計']
★開きたいエクセルをfilepathとします。イコールの次が「r」としてファイル名が続きます。ファイル名は、開きたいエクセルを右クリックして「プロパティ」→「セキュリティ」→「オブジェクト名」で確認できます。
変数「wb」をfilepathとします。ワークブックの略です。
変数「ws1」をfilepathの中の【データ】シートとします。ワークシートの略です。
変数「ws2」をfilepathの中の【集計】シートとします。
つまり「ws1」の数字を足し算して「ws2」に集計結果が反映されることになります。
#STEP3
startdate=datetime.datetime(int(ws2['B2'].value),int(ws2['C2'].value),int(ws2['D2'].value))
enddate=datetime.datetime(int(ws2['B3'].value),int(ws2['C3'].value),int(ws2['D3'].value))
★集計したい期間を変数startdate、変数enddateとします。期間は【集計】シートで設定しているので「ws2」の値を反映させます。値は整数として代入しているので関数intを使います。
#STEP4
lastrow1=ws1.max_row
lastrow2=ws2.max_row
lastcol2=ws2.max_column
★この後必要になるので、最終行と最終列について変数を使って表します。rowは行のことなのでmax_rowは最終行（つまり一番下の行）を表します。columnは列のことなので、max_columnは最終列（つまり一番右の列）を表します。
変数lastrow1を「ws1」【データ】の最終行（一番下）とします。
変数lastrow2を「ws2」【集計】の最終行（一番下）とします。
変数lastcol2を「ws2」【集計】の最終列（一番右）とします。
#STEP5
values1=[[cell.value for cell in row1]for row1 in ws1]
★変数values1を「ws1」全体をヨコタテの一覧表として取得します。STEP8まで飛びます。
#STEP6…1 【日数】の集計
for i in range(7,lastrow2+1):
for j in range(2,lastcol2+1):
counter6=0
for k in range(2,lastrow1):
if values1[k][0]==ws2.cell(row=i,column=1).value:
junedate=values1[k][1]
if startdate<=junedate<=enddate:
workingtime=values1[k][19]
counter6=counter6+float(workingtime)
#STEP6…2
ws2.cell(row=i,column=2).value=counter6
#STEP6…3
if counter6 is None:
counter6=0
#STEP7…1 【有休】の集計
for i in range(7,lastrow2+1):
for j in range(2,lastcol2+1):
counter7=0
for k in range(2,lastrow1):
if values1[k][0]==ws2.cell(row=i,column=1).value:
junedate=values1[k][1]
if startdate<=junedate<=enddate:
workingtime=values1[k][20]
counter7=counter7+float(workingtime)
#STEP7…2
ws2.cell(row=i,column=4).value=counter7
#STEP7…3
if counter7 is None:
counter7=0
#STEP8…1 【基本時間】の集計
for i in range(7,lastrow2+1):
★「ws2」について、7行目から最終行まで「i」の個数だけ繰り返します。
for j in range(2,lastcol2+1):
★「ws2」について、2列目から最終列まで「j」の個数だけ繰り返します。+1とするのは関数rangeの指定する範囲は（先頭,最後尾のひとつ手前まで）という仕組みになっているためです。
counter8=0
★変数counter8をゼロとします。
for k in range(2,lastrow1):
★「ws1」について、2行目から最終行まで「k」の個数だけ繰り返します。
if values1[k][0]==ws2.cell(row=i,column=1).value:
★変数values1「ws1」【データ】のk行・1列（つまりスタッフの氏名）と「ws2」のi行・1列（こちらも氏名）が一致しているときを選びます。［ ］内のカウントはゼロからはじまるので注意しましょう。1列目の数字を表したいなら［0］となります。
junedate=values1[k][1]
if startdate<=junedate<=enddate:
workingtime=values1[k][21]
counter8=counter8+float(workingtime)
★変数junedateを「ws1」【データ】のk行・2列（つまり日付）とします。
junedateがstartdateとenddateの間（つまり集計期間）に入っているときを選びます。
変数workingtimeを「ws1」【データ】のk行・22列（つまり基本時間）とします。
変数counter8は、counter8にさらにworkingtimeを足し算したものを代入します。
#STEP8…2
ws2.cell(row=i,column=6).value=counter8
★「ws2」【集計】のi行・6列（つまり基本時間の集計結果）に変数counter8を代入します。
#STEP8…3
if counter8 is None:
counter8=0
★変数counter8がゼロのままだったら、ゼロを代入します。
ここまで処理したらSTEP8…1に戻って繰り返しを行います。
#STEP9…1 【深夜時間】の集計
for i in range(7,lastrow2+1):
for j in range(2,lastcol2+1):
counter9=0
for k in range(2,lastrow1):
if values1[k][0]==ws2.cell(row=i,column=1).value:
junedate=values1[k][1]
if startdate<=junedate<=enddate:
workingtime=values1[k][22]
counter9=counter9+float(workingtime)
#STEP9…2
ws2.cell(row=i,column=8).value=counter9
#STEP9…3
if counter9 is None:
counter9=0
#STEP10 ヨシダ集計作成
newfilepath=r'C:Usersyoshi_000Documentsヨシダ集計.xlsx'
wb.save(newfilepath)
★STEP6〜9を行って最後にヨシダ集計というエクセルを新たに作り保存します。
冒頭でも書きましたが、本職のプログラマーは全然違うプログラムになると思います。今回の記事で私が伝えたいのは、私のような素人でも作れる、そして各状況に応じて臨機応変にプログラミングを活用することの提案です。
私も実践してみて、プログラミングができることの可能性を実感することができました。プログラミングを極めようということはありませんが、社内の事務処理のほとんどを自動化できるのではないかと本気で思うようになりました。
参考にしていただけると幸いです。
