顧客ごとの売上データの絞り込み

Contents

3つのExcelブックを読み込んで絞り込む
- 実行結果
顧客ごとの請求書を作成する
請求書のシートを作成する
- 結果
毎月の処理をもっと簡単にする
- 実行結果
- 参考図書

ースポンサーリンクー

3つのExcelブックを読み込んで絞り込む

・最初に顧客マスタの全データを空のリストのcustomer_listに読み込んでおく
・for文でcustomer_listから顧客データを1件ずつ取り出して、繰り返し処理を行う
・繰り返し処理（for文の中）では、取り出した顧客データと売上データの顧客IDを比較し、一致するデータだけをdata_listに追加する
⇨格納したデータを請求書に書き込む
・プログラムの最後では、data_listの先頭[0]と末尾[-1]をprint()で表示して、期待通り売上データが顧客ごとに絞り込まれているかを確認する

import openpyxl
import datetime

#顧客マスタのブック
wb_master = openpyxl.load_workbook("顧客マスタ.xlsx")
ws_master = wb_master["Sheet1"]

#売上データのブック、シート
wb_data = openpyxl.load_workbook("売上データ_202007.xlsx", data_only=True)
ws_data = wb_data["Sheet1"]

#請求書雛形のブック、シート
wb_inv = openpyxl.load_workbook("請求書ひな形.xlsx")
ws_inv = wb_inv["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)

#顧客ごとに処理
for customer in customer_list:
  customer_id = customer[0]
  customer_name = customer[1]
  #売上データの検索
  data_list = []
  for row in ws_data.iter_rows(min_row=4):
    value_list = []
    for c in row:
      value_list.append(c.value)
    #検索条件（売上データの顧客IDが一致したデータだけ追加）
    if value_list[1] == customer_id:
      data_list.append(value_list)

  if len(data_list) > 0:
    #確認（売上データの最初と最後を表示）
    print(data_list[0])
    print(data_list[-1])
    #ここから請求書作成の処理を行う（後半へ続く）

実行結果

顧客ごとの請求書を作成する

・ひな型を準備しておく（体裁を整えておく）
・日付や金額のセルには適切な書式を設定しておく

・オレンジの枠の中にアテナや明細を入力する
・数式は指定しておく

※E列の「区分」の欄に「※」が入力されている場合は税率8％対象、空欄の場合は税率10％対象になるようにSUMIFSで設定
★H29セルの数式（税率8％対象の小計を求める）
⇨=SUMIFS(H12:H28, E12:E28, "※")
★H30セルの数式（税率10％対象の小計を求める）
⇨=SUMIFS(H12:H28, E12:E28, ””)
★H31セルの数式（税率8％対象の小計を求める）
⇨=ROUNDDOWN(H29*8%, 0)
★H23セルの数式（税率10％対象の小計を求める）
⇨=ROUNDDOWN(H30*10%, 0)
★H33セルの数式（合計セル）
⇨=SUM(H29:H32)
★C9セルの数式（合計セル）
⇨=H33

請求書のシートを作成する

・シートのtitleに、顧客IDを収めたcustomer_idを代入して、シート名を「顧客ID」に変更する
・各セルへは、ws_new.cell(行番号、列番号).valueに値を代入して入力
※ここで、「請求日」のセルには、datetime型で日付を入力する
・税率区分は、8％ハウりい上げデータのシートでは「*」でマークされているので、id not Noneで空欄でない場合に「※」を請求書に入力する
・最後に、コピーに使用したひな形のシートを削除して、「請求書_202007.xlsx」という名前でブックを別名保存

import openpyxl
import datetime

#顧客マスタのブック
wb_master = openpyxl.load_workbook("顧客マスタ.xlsx")
ws_master = wb_master["Sheet1"]

#売上データのブック、シート
wb_data = openpyxl.load_workbook("売上データ_202007.xlsx", data_only=True)
ws_data = wb_data["Sheet1"]

#請求書雛形のブック、シート
wb_inv = openpyxl.load_workbook("請求書ひな形.xlsx")
ws_inv = wb_inv["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)

#顧客ごとに処理
for customer in customer_list:
  customer_id = customer[0]
  customer_name = customer[1]
  #売上データの検索
  data_list = []
  for row in ws_data.iter_rows(min_row=4):
    value_list = []
    for c in row:
      value_list.append(c.value)
    #検索条件（売上データの顧客IDが一致したデータだけ追加）
    if value_list[1] == customer_id:
      data_list.append(value_list)

  if len(data_list) > 0:
    #確認（売上データの最初と最後を表示）
    # print(data_list[0])
    # print(data_list[-1])
    # 請求書のひな型のシートをコピー
    ws_new = wb_inv.copy_worksheet(ws_inv)
    #顧客IDをシート名にする
    ws_new.title = customer_id
    #宛名書込み
    ws_new.cell(3,1).value = customer_name
    #請求日書込み
    ws_new.cell(2,8).value = datetime.datetime(2020,8,10)
    #表部分へのデータ書込み（A,B ,E ,F ,G ,H列に書き込む）
    row_num = 12
    for data in data_list:
      ws_new.cell(row_num,1).value = data[0]
      ws_new.cell(row_num,2).value = data[3]
      #税率区分
      if data[7] is not None:
        ws_new.cell(row_num,5).value = "※"
      ws_new.cell(row_num,6).value = data[4]
      ws_new.cell(row_num,7).value = data[5]
      ws_new.cell(row_num,8).value = data[6]
      row_num = row_num + 1

#請求書ひな型のシートは削除
wb_inv.remove(ws_inv)
#別ブックで保存
wb_inv.save("請求書_202007.xlsx")