【Python】数式をセルに設定する。

pythonを使用してExcelファイルの操作を勉強しています。
本日の気づき(復習)は、セルの数式に関してです。
pythonでExcelを操作するため、openpyxlというパッケージを使用しています。

と、

の、2枚のシートを持つ「合計」と言うブックを

この様な、数式を使ったシートにしたいです。

したいことは以下の3点です。

1. セルF6～F15：前月の販売金額をコピーし貼り付ける。
2. セルG6～G15：相対参照で数式を縦方向にコピーする。
3. セルE3～G3 ：相対参照で数式を横方向にコピーする。

なんだか、だんだんと通常のExcel業務に近づいてきた気がします。

1. 前月の販売金額をコピーし貼り付ける。

f-sringsとVLOOKUP関数

ただ単にコピーして貼り付けたのでは味気ないのでf-sringsと
VLOOKUP関数を使用してみます。

wb = load_workbook('合計.xlsx')

lastmonth = '202101'
month = '202102'

ws_lastmonth = wb[lastmonth]
ws = wb[month]

for row in ws.iter_rows(min_row=6, max_row=ws.max_row):
    row_count = row[0].row
    row[5].value = f'=VLOOKUP(C{row_count},{lastmonth}!$C$6:$E$15,3,FALSE)'

VLOOKUP関数は便利なのですが第4引数の「TRUE」「FALSE」がどうにも微妙な理解で
「TRUE」の近似値検索が数字ならまだ解るのですが文字列となると・・・。
とりあえず私は、「FALSE」しか使ったことがないです。

2. 相対参照で数式を縦方向にコピーする。

Translatorオブジェクト

f-sringsを使って数式を作ろうとも考えたのですが
openpyxlに、既存の数式を相対参照でコピーする機能が用意されていたので
こちらを使ってみます。

# コピー元の数式をセルG6に設定
cell = Translator(コピーする数式,
                  origin=相対参照の元となるセル番地).translate_formula(コピーする方向のセル番地)

なんだか、Excelの数式をドラッグしてコピーしていく方法と似てますね。

# コピー元の数式をセルG6に設定
ws['G6'] = '=E6-F6'

for row_no in range(7, ws.max_row + 1):
    cell_no = f'G{row_no}'
    ws[cell_no] = Translator(ws['G6'].value,origin='G6').translate_formula(cell_no)

今回は縦方向なのでこんな感じでしょうか。

3. 相対参照で数式を横方向にコピーする。

こちらは横方向に相対参照なので

ws['E3'] = '=SUM(E6:E15)'

for col_no in range(6, ws.max_column + 1):
    col_alphabet = ws.cell(row=1, column=col_no).column_letter
    ws[f'{col_alphabet}3'] = Translator(
        ws['E3'].value, origin='E3').translate_formula(f'{col_alphabet}3')

こんな感じでしょうか。

最後に上記の記述を纏めて

from openpyxl import load_workbook
from openpyxl.formula.translate import Translator

wb = load_workbook('合計.xlsx')

lastmonth = '202101'
month = '202102'

ws_lastmonth = wb[lastmonth]
ws = wb[month]

origin_cell_no_col = 'E3'
ws[origin_cell_no_col] = '=SUM(E6:E15)'

origin_cell_no_row = 'G6'
ws[origin_cell_no_row] = '=E6-F6'

# 202101シートのデータを張り付ける
for row in ws.iter_rows(min_row=6, max_row=ws.max_row):
    row_count = row[0].row
    row[5].value = f'=VLOOKUP(C{row_count},{lastmonth}!$C$6:$E$15,3,FALSE)'

# 各数値の合計を表示
for col_no in range(6, ws.max_column + 1):
    col_alphabet = ws.cell(row=1, column=col_no).column_letter
    ws[f'{col_alphabet}3'] = Translator(
        ws[origin_cell_no_col].value, origin=origin_cell_no_col).translate_formula(f'{col_alphabet}3')

# 前月販売分との差額を表示
for row_no in range(7, ws.max_row + 1):
    cell_no = f'G{row_no}'
    ws[cell_no] = Translator(ws[origin_cell_no_row].value,
                             origin=origin_cell_no_row).translate_formula(cell_no)

wb.save('合計_集計後.xlsx')

出来ました！
どうにか再利用性を高めるために、関数にしてみたかったのですが
自分にはまだまだ敷居が高かったようです・・・。特にネーミングセンス。

他にも、f-sringsとTranslatorオブジェクトの使い所の見分け方や

【1】の処理と【2】の処理はどちらも同じなので、一つにまとめることはできないかなと。
2020/02/17追記
自分なりに解消策を考えてみました。

from openpyxl import load_workbook
from openpyxl.formula.translate import Translator

wb = load_workbook('合計.xlsx')

lastmonth = '202101'
month = '202102'

ws_lastmonth = wb[lastmonth]
ws = wb[month]

# 起点となるセルの数式を設定
ws['E3'] = '=SUM(E6:E15)'
ws['F6'] = f'=VLOOKUP(C6,{lastmonth}!$C$6:$E$15,3,FALSE)'
ws['G6'] = '=E6-F6'

# 行で相対参照
for col_no in range(6, ws.max_column + 1):
    col_alphabet = ws.cell(row=1, column=col_no).column_letter
    ws[f'{col_alphabet}3'] = Translator(
        ws['E3'].value, origin='E3').translate_formula(f'{col_alphabet}3')

# 列で相対参照
for col_no in range(6, ws.max_column + 1):
    col_alphabet = ws.cell(row=1, column=col_no).column_letter
    for row_no in range(7, ws.max_row + 1):
        cell_no = f'{col_alphabet}{row_no}'
        ws[cell_no] = Translator(ws[f'{col_alphabet}6'].value,
                                 origin=f'{col_alphabet}6').translate_formula(cell_no)

wb.save('合計_集計後.xlsx')

と、記述をしてみたはいいのですが
まだまだ記述を纏める事が出来そうです。宿題は変わらず山積みです！

勉強を進めていく中で一つずつ解決していきたいですね！