AIで作る価格表Excel、数式の結果が0のまま渡る状態を止める
XlsxWriterのようなライブラリで作ったExcelファイルでは、数式の計算済み値が0のまま保存されることがあります。単価25円と数量100を入れた価格表でも、計算をしない環境では合計が0円に見える場合があります。数式が壊れたとは限りません。0円に見える原因はほかにもあり、ここで扱うのはライブラリで作ったファイルの話に限ります。
今回は、AIで作成を補助した価格表をデジタル商品として配る場面を想定した検証記録です。実在の商品や販売額は使いません。A1*B1の結果が2500になる小さなExcelを2通り作り、ファイルの中に残る値を検査します。
0が見えるのは、開く前の値を読んでいるからです
XlsxWriterの公式文書では、ライブラリ自身は数式を計算せず、デフォルトでは数式結果として0を保存すると説明されています。Excelなどがファイルを開くと再計算する印も付けます。
通常のExcelで開く購入者の場合は、再計算後に正しい値が見えるはずです。でも、計算をしないビューア、PDF変換、モバイルアプリでは0だけを表示することがあります。販売ページのプレビューで0円に見えれば、正しい数式を入れていても説明責任は残ります。
私が確認したいのは、数式そのものではなく、受け取る人が最初に見る値です。計算できる環境だけを前提にすると、配布前の確認が抜けます。
2つの検証ファイルを作ります
次のスクリプトは、単価25、数量100、合計A1*B1の最小ファイルを作ります。price-table-cache-zero.xlsxに保存する計算済み値は0、price-table-cache-ok.xlsxは2500です。
この小さなXLSXでは、XlsxWriterの挙動を再現します。商品ファイルや購入者の情報は使いません。
検証用XLSXを作るコード
# make_samples.py
from pathlib import Path
from zipfile import ZIP_DEFLATED, ZipFile
ROOT = Path(__file__).parent
PARTS = {
'[Content_Types].xml': '''<?xml version="1.0" encoding="UTF-8"?><Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/></Types>''',
'_rels/.rels': '''<?xml version="1.0" encoding="UTF-8"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>''',
'xl/workbook.xml': '''<?xml version="1.0" encoding="UTF-8"?><workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="価格表" sheetId="1" r:id="rId1"/></sheets></workbook>''',
'xl/_rels/workbook.xml.rels': '''<?xml version="1.0" encoding="UTF-8"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/></Relationships>''',
}
def write(name, cached):
sheet = f'''<?xml version="1.0" encoding="UTF-8"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData><row r="1"><c r="A1"><v>25</v></c><c r="B1"><v>100</v></c><c r="C1"><f>A1*B1</f><v>{cached}</v></c></row></sheetData></worksheet>'''
with ZipFile(ROOT / name, 'w', ZIP_DEFLATED) as z:
for path, body in PARTS.items():
z.writestr(path, body)
z.writestr('xl/worksheets/sheet1.xml', sheet)
write('price-table-cache-zero.xlsx', '0')
write('price-table-cache-ok.xlsx', '2500')数式と計算済み値を別々に読みます
次の検査は、XLSXをZIPとして開き、C1の数式と保存値を読みます。今回の検査では、数式がA1*B1なら2500以外を止めます。汎用的な数式の計算器ではありません。価格表のように、期待値を固定できる箇所だけを確認するためのものです。
# check_cached_formula.py
from pathlib import Path
from xml.etree import ElementTree as ET
from zipfile import ZipFile
import sys
NS = {'x': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
for path in map(Path, sys.argv[1:]):
with ZipFile(path) as z:
root = ET.fromstring(z.read('xl/worksheets/sheet1.xml'))
cell = root.find(".//x:c[@r='C1']", NS)
formula = cell.findtext('x:f', namespaces=NS)
cached = cell.findtext('x:v', namespaces=NS)
expected = 25 * 100
if formula == 'A1*B1' and cached == str(expected):
print(f'OK file={path.name} formula={formula} cached={cached}')
else:
print(f'HOLD file={path.name} formula={formula} cached={cached} expected={expected}')一時ディレクトリへ2つのスクリプトを置き、検証ファイルを作ってから検査しました。
work="$TMPDIR/xlsx-formula-cache-sample"
mkdir -p "$work"
cd "$work"
python3 make_samples.py
python3 check_cached_formula.py price-table-cache-zero.xlsx price-table-cache-ok.xlsx実行結果です。
HOLD file=price-table-cache-zero.xlsx formula=A1*B1 cached=0 expected=2500
OK file=price-table-cache-ok.xlsx formula=A1*B1 cached=2500数式はどちらも同じです。それでも、最初のファイルは保存値が0なので止まりました。2つ目は2500を保存しているため通っています。
検査で決められることと、決められないこと
この検査で分かるのは、決めたセルに期待した数式と保存値が入っていることだけです。消費税、割引、端数処理、参照先のシートまでは自動で正しいと判断しません。
その範囲を広げたくなったら、先に商品で約束する入力例を決めます。たとえば単価25、数量100なら合計2500と明記できるなら、同じ形で検査できます。期待値が決められないセルを、曖昧なまま通過させないことが大切です。
AIに価格表の初稿を作らせると、式や見た目を早く用意できます。しかし、生成したファイルをそのまま販売物にしないほうが安全です。購入者がExcelを開く前に見る環境を1つ想定し、保存値を確認する段階を足します。
次の出荷では、少ない入力で試します
今回の入力は、25と100の1組だけです。実際のテンプレートでは、0、1、小数、値引き後など、購入者が最初に使いそうな数組を決めます。各組で期待する合計を先に書き、保存値と比べます。
ここで通しても、販売の許可にはなりません。利用規約、説明文、閲覧環境での見え方も別に読みます。それでも、数式が見えているだけで安心して0のまま配る失敗は、出荷前に止められます。
出典
