表のデータを別のブックに転記する
やること
・既存のExcel表に、顧客マスタから検索したデータを転記
⇨今回は「売上データ202007.xlsx」の右端の「H列」に、顧客の「当該営業担当」を顧客マスタから転記
(ポイント)
・「顧客マスタ.xlsx」と「売上データ202007.xlsx」の2つのブックをload_workbook()で読み込んでおく
・「顧客マスタ.xlsx」は最初に全データを空のリストのcustomer_listに取り込んでおく。売上データは1行ずつ読み込みながら「B列」の顧客IDと一致する顧客データをcustomer_listから検索する
⇨該当する顧客データが見つかったら、顧客マスタの6列目にある「当社営業担当」(customer[5])を「H列」のセルにかきこむ
・売上データの全行について処理を完了したら、最後に別名でブックを保存
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) #データ行番号 row_num = 3 #列名追加 ws_data["H" + str(row_num)].value = "当社営業担当" #売上データを1行ずつ処理 for row in ws_data.iter_rows(min_row=4): row_num = row_num + 1 #顧客ID:売上データの『B列』(インデックス=1) customer_id = row[1].value #顧客の検索 for customer in customer_list: #検索条件(顧客IDの一致) if customer[0] == customer_id: #当社営業担当を「H列」に追加 ws_data["H" + str(row_num)].value = customer[5] break #別名で保存 wb_data.save("売上データ_202007_営業担当あり.xlsx")
売上データを顧客ごとのシートに転記する
・今回は顧客ごとのシートに振り分けて転記する
・シートは、売上データのブックにプログラムで追加し、サイトにそのブックを別名で保存
(ポイント)
・顧客マスタの全データをからリストのcustomer_listに読み込んでおく
・customer_listの顧客を1件ずつループし、その顧客IDと一致するデータを売上データから検索し、data_listに追加していく
⇨「同じ子役の売上データ」がdata_listに格納される
・該当する顧客の売り上げデータが1件でも存在したら、その顧客名称でシートを新規に追加して、data_listの中身をそのシートに書き込む。この処理を全ての顧客分繰り返すと完了
・「売上日」のセルは、日付で表示するために、number_formatに年月日の書式を代入しておく。また、「計」のセルには数式が入力されているため、そのまま書き写すと元のセルの位置を参照して、誤った計算をしてしまうため、売上データのブックを読み込むときに「data_ouly=True」を指定し、値だけを書き写すようにする
import openpyxl #顧客マスタのブック、シート import openpyxl #顧客マスタのブック、シート 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"] #顧客マスタの全データリスト 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: #顧客名称をシート名にしてシート追加 ws_new = wb_data.create_sheet(title=customer_name) #ヘッダー書き込み ws_new.append(["売上日","顧客ID","顧客名称","商品名","単価","数量","計"]) #データ書き込み row_num = 2 for data in data_list: ws_new.append(data) ws_new.cell(row_num,1).number_format = "yyyy/m/d" row_num = row_num + 1 #別ブックで保存 wb_data.save("売上データ_202007_顧客別シート.xlsx")