Pythonで作った表を開いたら、数式を入れたセルが全部 #NAME? になっとりました。書いたのは XLOOKUP。1行も計算されてへん。
先に結論だけ書いときます。原因はプログラム側の書き方でした。openpyxl で新しめの関数を書く時は、関数名の頭に _xlfn. を付けなあかん。それだけです。
ただ、そこに辿り着くまでに一回まちがった方向へ走ってます。その回り道のほうが役に立つと思うので、順番に書きます。
数式のセルだけが、きれいに全部エラー
作っとったのは、商品コードから単価を引っぱってくる表です。Pythonの openpyxl というライブラリで、セルに数式を書き込む形にしました。
ws["D2"] = "=XLOOKUP(A2, Master!A:A, Master!C:C)"ファイルは普通にできます。エラーも出ません。開いて初めて分かる。
#NAME? は、その名前の関数を知らん、という意味のエラーです。数式を書いたセルだけが、上から下まできれいに全部これになっとりました。
最初に疑ったのは、ソフトのほうやった
XLOOKUP は比較的あたらしい関数です。なので最初に思ったのは、開いとるソフトが対応してへんのやろ、でした。私の環境にはExcelが入ってなくて、LibreOffice で開いとったので、なおさらそう思い込みました。
で、この筋で調べにいこうとした手前で止まりました。同じファイルの TEXTJOIN まで #NAME? になっとったからです。
TEXTJOIN はだいぶ前からある関数です。新しいやつだけが落ちとるなら対応の問題やけど、古いのまで巻き添えになっとるなら話は別のところにある。あのままソフトの対応表を読みにいっとったら、半日は帰ってこれてへんかったと思います。
犯人は、ファイルの中での本名がちがうこと
xlsx ファイルは、実は中身がZIPです。拡張子を zip に変えて解凍すると、設定やシートの中身が書かれたテキストがぞろぞろ出てきます。数式もその中に、ただの文字列として保存されとります。
そして Excel 2019 以降で追加された関数は、ファイルの中では別の名前で書かれとる。XLOOKUP は _xlfn.XLOOKUP という名前です。画面に XLOOKUP と表示されるのは、開いたソフトが接頭辞を外して見せとるだけ。
Excel本体で数式を打つと、この変換は自動でやってくれます。けど openpyxl は、渡した文字列をそのまま書き込むだけ。変換は一切しません。だから素で XLOOKUP と書くと、ファイルの中には存在せん名前の関数が入ることになる。
直し方はこれだけです。
ws["D2"] = "=_xlfn.XLOOKUP(A2, Master!A:A, Master!C:C)"開き直したら、全行ちゃんと値が入っとりました。
どれに付けて、どれには要らんのか
全部の関数に付けるわけやないです。私が実際に手元で試して確かめた範囲だと、こうなりました。
接頭辞が要るもの
- XLOOKUP …
=_xlfn.XLOOKUP(...) - LET / IFS / TEXTJOIN …
=_xlfn.LET(...)の形 - FILTER や SORT などの動的配列 …
=_xlfn._xlws.FILTER(...)と二段になる
素のままでええもの
- SUMIFS / IFERROR / ROUND / SUBSTITUTE / SUMPRODUCT
- テーブルの構造化参照（Meisai[数量] みたいなやつ）
ざっくり言うと、昔からある関数はそのままで、新しめのやつだけ接頭辞が要る。境目はだいたい Excel 2019 のあたりです。
ややこしいのが FILTER や SORT みたいな動的配列の関数で、こっちは _xlfn._xlws. と二段になります。ここは覚えるより、迷ったら実際に書いて開いてみるほうが早いです。
Excelが無くても、数式が生きとるかは機械で確かめられる
今回いちばん怖かったのは、作った本人が気づけん形の壊れ方やったことです。ファイル生成は成功する。プログラムから読み戻しても、数式の文字列はちゃんと入っとる。開いた人の画面でだけ全部エラーになる。
なので、目視やなく機械で確かめる手を1本用意しました。LibreOffice をコマンドから呼んで、xlsx を丸ごと csv に変換します。
soffice --headless --calc --convert-to csv --outdir out book.xlsxcsv に落ちるのは、数式やなく計算した結果の値です。つまり出てきたファイルを #NAME? や #REF! で検索して、1件も引っかからんかったら数式は全部生きとる、と機械的に言えます。
実際にこれで、1,098行の商品マスターが全行ちゃんと解決しとることを確かめました。Excelを持ってなくても、渡す前に自分で検品できる。
おわりに
#NAME? が全セルに出た時、まず疑うべきは開いとるソフトやなく、書き込んだ側の関数名です。_xlfn. を付ける。それで直ります。
ただ個人的にでかかったのは、そこやなくて古い関数まで一緒に落ちとるのが変やと思えたことのほうでした。エラーは全部同じ顔で出てくるんで、どれが不自然かに気づけんと、いくらでも遠回りできてまう。
集計まわりを自動化する話やと、業務改善Webアプリのサンプル1本目を1日で公開した話も書いてます。あっちは表そのものを卒業する側の話です。