ナンバーズ３の当選予想番号を、過去の当選番号からエクセル(LibreOffice)を用いて選定しています。PYthonでエクセル関数式を相対参照でコピーする方法を探していましたが、なかなか良い方法が見つからず、検索しながら多数のコードを参考に試行錯誤を繰り返していました。
何度も試しているうちに偶然にコードが完成しましたので、サンプルコードを以下に紹介します。
# エクセル関数式を相対参照でコピーするオリジナル関数
# ライブラリのインポート
import openpyxl
from openpyxl.formula.translate import Translator
# sample.xlsx ファイルを開く
wb = openpyxl.load_workbook('sample.xlsx')
ws = wb.active
# 関数式をfor文で横に連続コピーする関数を定義する
def fx_copy_col():
for col in range(col_start, col_end + 1):
col_alphabet = ws.cell(1, col).column_letter
ws[f'{col_alphabet}{row}'] = Translator(origin_fx, origin=origin_cell).translate_formula(f'{col_alphabet}{row}')
# 関数式をfor文で縦に連続コピーする関数を定義する
def fx_copy_row():
for row_no in range(row_start, row_end + 1):
cell_no = f'{col}{row_no}'
ws[cell_no] = Translator(origin_fx, origin=origin_cell).translate_formula(cell_no)
# 上記関数の引数を設定して関数を呼び出す
# 横コピーfor文の range() 関数の引数
col_start = 1 # A列から
col_end = 4 # D列までfor文で繰り返す
# 横コピーTranslator オブジェクトの引数
origin_fx = '=A6' # 設定する数式
origin_cell = 'A2' # セルA2を指定
row = 2 # 2行目を指定
fx_copy_col() # 横コピー関数を呼び出す
# 横コピーTranslator オブジェクトの引数
origin_fx = '=A15' # 設定する数式
origin_cell = 'A4' # セルA4を指定
row = 4 # 4行目を指定
fx_copy_col() # 横コピー関数を呼び出す
# 縦コピーfor文の range() 関数の引数
row_start = 6 # 6行目から
row_end = 15 # 15行目までfor文で繰り返す
# 縦コピーTranslator オブジェクトの引数
# 設定する関数
origin_fx = '=SMALL(B$6:B$15,A6)'
origin_cell = 'C6' # セルC6を指定
col = 'C' # C列を指定
fx_copy_row() # 縦コピー関数を呼び出す
# 縦コピーTranslator オブジェクトの引数
# 設定する関数
origin_fx = '=LARGE(B$6:B$15,A6)'
origin_cell = 'D6' # セルD6を指定
col = 'D' # D列を指定
fx_copy_row() # 縦コピー関数を呼び出す
# ファイルを保存する
wb.save('sample.xlsx')
実行結果
一番苦労したのは Translatorオブジェクトのコードで、横コピーでは
ws[f'{col_alphabet}{row}'] = Translator(origin_fx, origin=origin_cell).translate_formula(f'{col_alphabet}{row}')
上記コードの中に2か所ありますが、
f'{col_alphabet}{row}' この部分で、
縦コピーでは、
cell_no = f'{col}{row_no}' の部分です。
もともと、このコードの意味がよく分からないので、どうやってもエラーになり、諦めかけていたところたまたま上手くいって完成しました。
後に調べて、これは f-storing という機能とのことですが、
そういう訳でこのコードについては、今でもよく分からないので説明は省略します。
ナンバーズのような数字をエクセルで扱う場合、やることは90%以上が昇順に並べ替え等の関数式の縦の連続コピーです。そのため、その都度 Pythonでfor文のコードを書くより直接エクセルで手動コピーした方がずっと楽でした。ところが、このコードが偶然にも完成できたので、これで約６５００回分のデータを十二分に分析して更に当選確率が上がることが期待できます。
