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

このエントリーをはてなブックマークに追加
python
Python
ースポンサーリンクー

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])
    #ここから請求書作成の処理を行う(後半へ続く)

実行結果

f:id:Moriane:20220212140147p:plain

 

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

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

・オレンジの枠の中にアテナや明細を入力する
・数式は指定しておく
※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")

結果

毎月の処理をもっと簡単にする

今回のプログラムを毎月使用する場合は、売上データのブック名や請求日を画面から入力できるようにすると便利になる。
画面で入力した文字をプログラムで受け取るには、input()を利用する。カッコの中に文字列を指定すると、ユーザーに何を入力してもらいたいかメッセージを表示することができる。
f:id:Moriane:20220220154755p:plain

f:id:Moriane:20220220155204p:plain

実行結果

表示されたメッセージに何らかのテキストを入力してEnterキーを押すと、input()は入力されたテキストを文字列として返す。

f:id:Moriane:20220220155253p:plain

参考図書

¥2,398 (2024/02/27 14:55時点 | 楽天市場調べ)
\楽天ポイント4倍セール!/
楽天市場
\ポイント5%還元!/
Yahooショッピング

コメント

タイトルとURLをコピーしました