前段:
メーカーの営業をしていると営業活動以外にもエクセルを使った事務処理が多々ある。
本投稿では常日頃エクセルで行っている作業をPythonでの置き換えに挑戦。
内容:
例：顧客より所要情報を下記フォーマットで受領する。
社内見積もりフォーマットでは四半期ベースの為、エクセルにて該当フォーマットを作成。
今回は上記フォーマット作成をPythonで行う
# 四半期版のコラム作成
xx=['1Q','2Q','3Q','4Q']
yy=['2021','2022','2023','2024']
combined = [y+'/'+x for y in yy for x in xx]
['2021/1Q',
'2021/2Q',
'2021/3Q',
'2021/4Q',
'2022/1Q',
'2022/2Q',
'2022/3Q',
'2022/4Q',
'2023/1Q',
'2023/2Q',
'2023/3Q',
'2023/4Q',
'2024/1Q',
'2024/2Q',
'2024/3Q',
'2024/4Q']
# 空のデーターフレーム作成し、コラムに四半期をfor loopで入れ込む
ss=pd.DataFrame(index=['所要'])
for com in combined:
ss[com]='']
2021/1Q 2021/2Q 2021/3Q 2021/4Q 2022/1Q 2022/2Q 2022/3Q 2022/4Q 2023/1Q 2023/2Q 2023/3Q 2023/4Q 2024/1Q 2024/2Q 2024/3Q 2024/4Q
所要
# 四半期に変換させる為所要を単純に割る4。
# 値はエクセルから読み込んでも良いが、今回は4個の為手打ち。
mm=[3000,3245,6554,6666]
uu=map(lambda x: float(x)/4,mm)
uu=list(uu)
uu
[750.0, 811.25, 1638.5, 1666.5]
# 各数字を4x繰り返す。
# フォーマットが4x4になる。
ll=[[u]*4 for u in uu]
ll
[[750.0, 750.0, 750.0, 750.0],
[811.25, 811.25, 811.25, 811.25],
[1638.5, 1638.5, 1638.5, 1638.5],
[1666.5, 1666.5, 1666.5, 1666.5]]
# 4x4を16x1に変換
ll=np.ravel(ll)
ll=list(ll)
ll
[750.0,
750.0,
750.0,
750.0,
811.25,
811.25,
811.25,
811.25,
1638.5,
1638.5,
1638.5,
1638.5,
1666.5,
1666.5,
1666.5,
1666.5]
# 格データーを入れ込み完成
for com,l in zip (combined,ll):
ss[com]=l
ss
2021/1Q 2021/2Q 2021/3Q 2021/4Q 2022/1Q 2022/2Q 2022/3Q 2022/4Q 2023/1Q 2023/2Q 2023/3Q 2023/4Q 2024/1Q 2024/2Q 2024/3Q 2024/4Q
所要 750.0 750.0 750.0 750.0 811.25 811.25 811.25 811.25 1638.5 1638.5 1638.5 1638.5 1666.5 1666.5 1666.5 1666.5
# おまけの逆バージョン
# 今度は四半期ベースから年単位に変換を行う。
# 先ずは列と行の入れ替え
cd=ss.T
cd
所要
2021/1Q 750.00
2021/2Q 750.00
2021/3Q 750.00
2021/4Q 750.00
2022/1Q 811.25
2022/2Q 811.25
2022/3Q 811.25
2022/4Q 811.25
2023/1Q 1638.50
2023/2Q 1638.50
2023/3Q 1638.50
2023/4Q 1638.50
2024/1Q 1666.50
2024/2Q 1666.50
2024/3Q 1666.50
2024/4Q 1666.50
# 今回は[年コラム」でgroup byを使用する。
# その為numpyのselect機能(Excelのif)を使用し、四半期ベースを年単位に変換していく
condition=[cd.index.str.contains('2021'),cd.index.str.contains('2022'),cd.index.str.contains('2023'),cd.index.str.contains('2024')]
result=['2021','2022','2023','2024']
cd['year']=np.select(condition,result)
cd
所要 year
2021/1Q 750.00 2021
2021/2Q 750.00 2021
2021/3Q 750.00 2021
2021/4Q 750.00 2021
2022/1Q 811.25 2022
2022/2Q 811.25 2022
2022/3Q 811.25 2022
2022/4Q 811.25 2022
2023/1Q 1638.50 2023
2023/2Q 1638.50 2023
2023/3Q 1638.50 2023
2023/4Q 1638.50 2023
2024/1Q 1666.50 2024
2024/2Q 1666.50 2024
2024/3Q 1666.50 2024
2024/4Q 1666.50 2024
# group byで集計し、.Tで行列を入れ替え完成
k=cd.groupby(cd['year']).sum()
k=k.T
k
year 2021 2022 2023 2024
所要 3000.0 3245.0 6554.0 6666.0
エクセルでの手順:
*メインはPythonの為、解説は簡略化
(Index,Offset関数を使用する)
Index関数を使用するためのネタ準備
上段の連番は=ROUND(COLUMN(B2)/4,0)で作成
下段の1,2,3,4はエクセルの連続コピー機能で作成
↓はindex関数と↑の連番を使用し、2021/1Qの体裁を作成してく為のネタ
Index関数にて作成していく
=INDEX($I$5:$L$5,0,I2)&"/"&INDEX($I$6:$L$6,0,I3)→Pythonでいうこれcombined = [y+'/'+x for y in yy for x in xx]
=INDEX($C$3:$F$3,0,I2)/4→Pythonでいうこれuu=map(lambda x: float(x)/4,mm)
おまけのエクセル版
offset関数用のネタ準備
SUM(OFFSET())関数で4個の合計を算出
=SUM(OFFSET($I$9,0,I4,1,4))→2021/1Qを開始点(↑の0+1)として4個選択し合計→開始点から5番目(↑の4+1)から4個選択し合計.....
完成
結果:
今回の加工だけでいえばエクセルに軍配が上がりそうだが、個人的にはPythonの方が複雑な処理には向いてそう👏
余談ですが、今後もメーカー営業xPythonというテーマで記事を拡充していこうと思う。