【Python×Excel】条件を満たすデータや個数や合計を求める

Contents

顧客ごとの売上件数をカウントする
- ポイント
- 実行結果
顧客マスタにない顧客について集計する
顧客ごとの売上額を集計する
- 結果
- 参考文献

ースポンサーリンクー

顧客ごとの売上件数をカウントする

ポイント

まず、最初に顧客マスタのデータを全てからのリストの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が一致）_１を加えてカウントする
    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に「１」を加えた。売り上げの合計を集計するためには、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列（インデックス＝６）」
      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")