顧客ごとの売上件数をカウントする
ポイント
- まず、最初に顧客マスタのデータを全てからのリストのcustomer_listに取り込んでおく
- customer_listの顧客を1件ずつループさせて、その顧客IDと一致したら、売上データが計上された場合だけ「顧客ID、顧客名称、売上件数(count_sales)」をリストにしてresult_listに追加する
- 全ての顧客について集計したら、売上データのブックに集計結果用のシートを追加して、そこにresult_listの中身を書き込む
- 最後にそのブックを別名保存
import openpyxl
#顧客マスタのブック、シート
import openpyxl
#顧客マスタのブック、シート
wb_master = openpyxl.load_workbook("顧客マスタ.xlsx")
ws_master = wb_master["Sheet1"]
#売上データのブック、シート
wb_data = openpyxl.load_workbook("売上データ_202007.xlsx")
ws_data = wb_data["Sheet1"]
#顧客マスタの全データリスト
customer_list = []
for row in ws_master.iter_rows(min_row=2):
if row[0].value is None:
break
value_list = []
for c in row:
value_list.append(c.value)
customer_list.append(value_list)
#集計結果を入れるリスト
result_list = []
#顧客ごとに処理
for customer in customer_list:
customer_id = customer[0]
customer_name = customer[1]
#売上件数のアカウント
count_sales = 0
#売上データの検索
for row in ws_data.iter_rows(min_row=4):
#検索条件(売上データの顧客IDが一致)_1を加えてカウントする
if row[1].value == customer_id:
count_sales = count_sales + 1
if count_sales > 0:
#顧客ID、顧客名称、売上件数の3項目を追加
result_list.append([customer_id, customer_name, count_sales])
#集計結果用シートを追加
ws_new = wb_data.create_sheet(title="顧客別売上件数")
#ヘッダー書き込み
ws_new.append(["顧客ID", "顧客名称", "売上件数"])
#集計結果書き込み
for result in result_list:
ws_new.append(result)
#別ブックで保存
wb_data.save("売上データ_202007_顧客別売上件数.xlsx")
実行結果
顧客マスタにない顧客について集計する
- 顧客マスタにない顧客については集計されない。そこで次のコードを追加しておけば、顧客マスタにない顧客の見落としを防げる
- 最終行のwb_data.save(“売上データ202007顧客別売上件数.xlsx”)の前に追記する
・・・
id_list = []
#集計結果書き込み
for result in result_list:
ws_new.append(result)
#集計した顧客IDのリストを作成
id_list.append(result[0])
#集計されていない顧客を検索
for row in ws_data.iter_rows(min_row=4):
#検索条件(集計した顧客IDのリストに含まれない)
if row[1].value not in id_list:
#集計されていない顧客IDと顧客名称を表示
print(row[1].value, row[2].value)
#別ブックで保存
wb_data.save("売上データ_202007_顧客別売上件数.xlsx")
顧客ごとの売上額を集計する
- このプログラムは、上記の「売上件数をカウントする場合」とほとんど同じ
- 売上件数のカウントでは、顧客IDが一致するデータがあるたびに、count_salesに「1」を加えた。売り上げの合計を集計するためには、sum_salesに「売上額」を足していく
- 売り上げデータの「計」のセルには数式が入力されているので、値を取得できるように、ブックの読み込み時に「data_only=True」を指定するのを忘れないようにすること!
import openpyxl
#顧客マスタのブック、シート
wb_master = openpyxl.load_workbook("顧客マスタ.xlsx")
ws_master = wb_master["Sheet1"]
#売上データのブック、シート。data_only=Trueで数式の「計」セルから値を取得できるようにする
wb_data = openpyxl.load_workbook("売上データ_202007.xlsx", data_only=True)
ws_data = wb_data["Sheet1"]
#顧客マスタの全データリスト
customer_list = []
for row in ws_master.iter_rows(min_row=2):
if row[0].value is None:
break
value_list = []
for c in row:
value_list.append(c.value)
customer_list.append(value_list)
#集計結果を入れるリスト
result_list = []
#顧客ごとに処理
for customer in customer_list:
customer_id = customer[0]
customer_name = customer[1]
#売り上げの集計
sum_sales = 0
#売上データの検索
for row in ws_data.iter_rows(min_row=4):
#検索条件(売上データの顧客IDが一致)
if row[1].value == customer_id:
#売上の額は「G列(インデックス=6)」
sum_sales = sum_sales + row[6].value
if sum_sales > 0:
#顧客ID、顧客名称、売上合計の3項目を追加
result_list.append([customer_id, customer_name, sum_sales])
#集計結果用シートを追加
ws_new = wb_data.create_sheet(title="顧客別売上")
#ヘッダー書き込み
ws_new.append(["顧客ID","顧客名称","売上計"])
#集計結果書き込み
for result in result_list:
ws_new.append(result)
#別ブックで保存
wb_data.save("売上データ_202007_顧客別売上合計.xlsx")
結果
参考文献
¥2,398 (2025/05/31 17:48時点 | 楽天市場調べ)

コメント