顧客ごとの売上件数をカウントする
ポイント
- まず、最初に顧客マスタのデータを全てからのリストの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/01/16 19:33時点 | 楽天市場調べ)
ポチップ
コメント