【キノカレッジ|業務効率化クラス|22年10月(1)】Pythonでエクセルの業務を自動化しよう|Python×Excel

課題内容

あなたは、キノコード株式会社の業務効率化担当です。
営業事務の田中さんが、売上データなどをExcelで集計をして、営業部長への報告や営業会議の資料を作成しています。
しかし、このたび田中さんが退職されるので、この仕事を自動化したいという依頼が営業部長からありました。
このExcel業務をPythonにより効率化をしたいと考えています。
この業務が効率化できたのであれば、別の営業チームにも横展開をして、さらなる業務効率化を図りたいとも伝えられています。
具体的には、Excelの項目名を変更したり、集計がしやすいようにデータを整えることで、完全自動化ができるのではないかと考えています。
なお、田中さんはこの業務に毎日1時間とられています。毎月20時間の工数です。
しかし、田中さんと同じような業務をしている営業事務の方は、会社全体でで30人います。したがって、600時間の工数削減です。
人件費で言うと、4-5人分の業務削減インパクトです。
会社からは、時流のDXの案件であり、注目されている仕事です。

データのファイル名とカラム説明

売上データ

カラム名説明
社員ID社員ごとに割り振られている一意の番号
1月売上社員ごとに1月売上
2月売上社員ごとに2月売上
3月売上社員ごとに3月売上
備考欄自由記入欄

社員別売上データ

カラム名説明
社員ID社員ごとに割り振られている一意の番号
氏名
性別
所属支店社員が配属されている支店
1月売上社員ごとに1月売上
2月売上社員ごとに2月売上
3月売上社員ごとに3月売上
備考欄自由記入欄

(1)月間の売上が上位5名の売上合計額を求め、全体の売上額に対する比率を求める
・「売上データ.xlsx」を開く
・1月の売上データのフィルター機能を使って並び替え
・1月の売上上位5人の合計値を算出
・1月の売上金額合計を算出する
・上位5名÷売上金額合計をして割合を算出する
・2月と3月分の同じ作業を繰り返す

(2)社員ごとに前月売上比を算出したい
・「売上データ.xlsx」を開く
・「2月売上÷1月売上」の計算をする
・「3月売上÷2月売上」の計算をする
・「備考欄」の列を削除

(3)月間の売上が5万円以上の男女比を算出する
・「社員売上データ.xlsx」を読み込む
・1月の「売上5万円以上、かつ、男性の社員数」のデータ抽出をする
・2月の「売上5万円以上、かつ、男性の社員数」のデータ抽出をする
・3月の「売上5万円以上、かつ、男性の社員数」のデータ抽出をする
・各データフレームの行数をカウントし、各月の男女比率を計算する

(4)月ごとで、所属支店と性別ごとに売上金額の合計を算出する
・「社員売上データ.xlsx」を開く
・sumifs関数を使って集計をするorピボットテーブルを使って集計をする

(5)月ごとに、売上合計の50%は、トップから何人の営業マンで占めているのかを求める
・「売上データ.xlsx」を開く
・1月の売上データのフィルター機能を使って並び替え
・各月の売上金額合計を算出する
・上から何人分で売上金額の50%になるかを計算する

この課題に使われる関数メソッド

(1)
・Excelファイルの読み込み・・・pandas.read_excelメソッド
・データフレームの先頭から指定行数表示・・・pandas.DataFrame.headメソッド
・データフレームのカラムの要素で並び替え・・・pandas.DataFrame.sort_valuesメソッド
・カラムの抽出
・デーー他フレームの合計を計算・・・pandas.DataFrame.sumメソッド
・小数点を指定した形式で表示・・・formatメソッド

(2)
・データフレームのコピーを作成・・・pandas.DataFrame.copyメソッド
・カラムの抽出
・データフレームのカラムやインデックスを削除・・・pandas.DataFrame.dropメソッド
・四則演算(/)
(3)
・Excelファイルの読み込み・・・pandas.read_excelメソッド
・四則演算
・演算子(==, &, >=, /)
・オブジェクトの要素数を取得・・・len関数
(4)
・データフレームをカラムごとにグルーピング・・・pandas.DataFrame.groupby
・データフレームをピボットテーブル集計・・・pandas.DataFrame.pivot_table
・データフレームのカラムの要素で並び替え・・・pandas.DataFrame.sort_valuesメソッド
・データフレームの累積話を計算・・・pandas.DataFrame.cumsum
・データフレームの最大値を取得・・・pandas.DataFrame.max
・同じ処理を繰り返し実行・・・for文
・データフレームをExcelファイルに書き出し・・・pandas.DataFrame.to_excel
(5)

この課題に対応するキノクエストのレッスン

Pandas講座 LESSON1
データフレーム (DataFrame) とは
・四則演算

Pandas講座 LESSON3
CSV・Excelファイルの読み込み・書き出し、データベースとの接続方法
・Excelファイルの読み込み・・・pandas.read_excelメソッド
・データフレームをExcelファイルに書き出し・・・pandas.DataFrame.to_excel

Pandas講座 LESSON4
データ抽出の方法
・カラムの抽出

Pandas講座 LESSON5
データの並び替えの方法
・データフレームのカラムの要素で並び替え・・・pandas.DataFrame.sort_valuesメソッド

Pandas講座 LESSON6
データ集計(groupby)の方法
・データフレームをカラムごとにグルーピング・・・pandas.DataFrame.groupby

Pandas講座 LESSON7
データ集計(pivot_table)の方法
・データフレームをピボットテーブル集計・・・pandas.DataFrame.pivot_table

Python超入門講座 LESSON5
演算子
・演算子(==, &, >=, /)

Python超入門講座 LESSON7
繰り返し
・同じ処理を繰り返し実行・・・for文