Pythonを使ったエクセルでの加工処理(Aidemy用)



#1.はじめに & 動作環境
バックオフィスにおいて日常的に発生する業務にエクセルの加工作業がある。
これらの多くの作業は同じ作業の繰り返しであることが多いため、これらをできる限り自動化することで業務の効率化と生産性の向上を目指す。

動作環境
    OS : Windows10
    Python : 3.8.3
    jupyter notebook 6.0.1


#2.エクセル関数の洗い出しと絞り込み
日常的に使用するエクセル関数の洗い出しを行う。
同時に、繰り返し行う定型的な作業に使用する関数の絞り込みを行う。

・シェアドライブに保管しているエクセルを開く
・行列の削除
・別名でブック保存
・不要なデータの除外
・複数シートの統合
・merge
・ローカルドライブに保管しているエクセルを開く




#3.工程図 for Excel2 OpenOrders
![スクリーンショット 2021-09-06 6.39.16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1811258/fb55f172-303c-bc48-5942-0aa84ce8c18f.png)


#4.コードの処理の手順概要
1.基幹システムからエクセルに受注データや在庫の情報をダウンロード
2.Pythonを使用してシェアドライブに保存してあるエクセルのデータをpandasに読み込む

![image002.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1811258/ebd19d4c-39c3-a917-a618-ad1ce1f0a36a.jpeg)

3.本作業には不要なおよそ200列を削除
空いた空列に項目名を追加（管理表納入予定日、日付修正、出荷処理）
日付で並び替え（Sort)

4.カスタマーマスターを読み込む

![image006.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1811258/13609a62-d473-dd45-fa07-6a2ac2b62912.jpeg)

5.pandasに読み込んだ受注データとカスタマーマスターを結合し、顧客コードではわかりずらかった顧客名と顧客ごとに違う出荷処理項目を追記して他のメンバーにもすぐにわかるようにする。



6.チェック項目でフィルタリング
7.ローカルドライブに保存。日々MTGでチームと共有し、出荷進捗状況の確認や業務指示に使用する。





```python
import datetime
 
t1 = time.time() # 処理前の時刻
now = datetime.datetime.now()

 
#1.データの読み込み
df_OOLINE = pd.read_excel('C:/BacklogReport_vr3.xlsm', sheet_name='OOLINE ( OIS100)',  index_col=0)
df_MGHEAD = pd.read_excel('C:/BacklogReport_vr3.xlsm', sheet_name='MGHEAD',  index_col=1)
 
#>>>>>>>>>>>>>>>>>>>OOLINEの加工
# 列 "A", "B" の 2 列を抽出
df_OOLINE2 = df_OOLINE.loc[:,['OBORNO','OBCUNO']]
 
# 重複を削除
df_OOLINE2.duplicated(subset='OBORNO').sum()
df_OOLINE2.drop_duplicates(subset='OBORNO', keep='first', inplace=True)
 
df_OOLINE2.to_excel('C:/OpenOrders_OIS100_2.xlsx', sheet_name='test')
 
#>>>>>>>>>>>>>>>>>>>MGHEADの加工
#列の削除　カラムをリストで渡しまとめて削除
columns = ['1','2','3','4','6','8','10',,,,,,,'215']#ブログ公開用にマスキング
df_MGHEAD2 = df_MGHEAD.drop(columns, axis=1)
 
#列の追加
df_MGHEAD2["管理表納入予定日"] = ""
df_MGHEAD2["日付修正"] = ""
df_MGHEAD2["出荷処理"] = ""
 
df_MGHEAD2.to_excel('C:/OpenOrders_MGHEAD.xlsx', sheet_name='test')
 
#>>>>>>>>>>>>>>>>>>>
# merge1 MGHEADとOIS100のマージ

#データ読込　#後に省略検討
df1 = pd.read_excel('C:/02.Backlog Review/OpenOrders_MGHEAD.xlsx', sheet_name='test',  index_col=None)
df2 = pd.read_excel('C:/02.Backlog Review/OpenOrders_OIS100_2.xlsx', sheet_name='test',  index_col=None)
 
#データ型の確認&変更
df2["OBORNO"] = df2["OBORNO"].astype(str)
df3 = pd.merge(df_MGHEAD2, df_OOLINE2, left_on= "MGRORN", right_on="OBORNO", how="outer")
 
#print(df3.head())
 
df3.to_excel('C:/OpenOrders_merge1.xlsx', sheet_name='test')
 
#>>>>>>>>>>>>>>>>>>>
# merge2
 
#データ読込
df_custCode = pd.read_excel('C:/master_CustCode.xlsx', sheet_name='Sheet1',  index_col=0)

#print(df1.head())
#print(df_custCode.head())
 
#データ型の確認
#print(df1.dtypes) #OAPYNO=object
#print(df2.dtypes)#Customer code=object
#df3 = pd.merge(df1, df2, left_on= "OAPYNO", right_on="Customer code")
 
df_merge2 = pd.merge(df3, df_custCode, on="OBCUNO", how="outer")
 
#sort
df_merge2 = df_merge2.sort_values(by="MGTRDT", ascending = True)
 
#filtering
df_merge2 = df_merge2.loc[df_merge2["MGFACI"] == "EJ1"]
df_merge2 = df_merge2.loc[df_merge2["MGTRSL"] != 99]
df_merge2 = df_merge2.loc[df_merge2["MGTRSL"] != 44]
 
#print(df3.head())
 
df_merge2.to_excel('C:/OpenOrders_merge2_' + now.strftime('%Y%m%d') + '.xlsx', sheet_name='test')

#>>>>>>>>>>>>>>>>>>>
t2 = time.time()# 処理後の時刻
elapsed_time = t2-t1
print(f"経過時間：{elapsed_time}") # 経過時間を表示
```


#5.完成 for Excel2 OpenOrders
![2-01.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1811258/3622de38-1d33-7d55-d268-3348044b5a65.jpeg)



#6.作業時間
Python使用前：今まで完全手作業で毎日8分〜10分ほどかかっていた
8min./day = 32hrs/yr.
↓
Python使用後：Pythonでの処理時間123秒 + 手作業での後処理3分 = 計5分/日
5min./day:20hrs/yr.(Python処理時間を除いた手作業時間は12hrs/yr.)
40%の削減効果。実際に手を動かすのは3分だけなので体感的には1/2以下。


#7.終わりに
また毎日同じ作業をしなくてはならないため、手作業で行うのは苦痛だったがこのように少しでも自動化できる平行して別作業を行うこともできるため効率化にも繋がった。
今回作成したコードをそのまま実務に適用している。
今まで全て手作業で行っていたた。目視で必要な列を抽出かつ200列を削除し、顧客名をVlookupで引っ張ってくる工程に毎日30分程度かかっていた。

Pythonを使用することで20分程度に短縮することができ、加えてPython自動処理中に他の作業を行える


実際の現場では、納期管理表は複数人が共有で更新しているため、項目の追加などフォーマットが随時変わっていくことが考えられる。
そのため後で見直ししたときにすぐに内容がわかり、なおかつ修正しやすいようにできる限りシンプルなコードを心がけていきたい。

今後も学習努力を続け、より効果的な書き方や使い方を身につけたいと考えている。

なお、使用した資料は実際に業務で使用しているものをダミーデータに置き換えている。また、ダミーに置き換える時間を短くするためにブログ画像に表示しているデータ量は極端に省略してある。