openpyxlのExcelが#NAME?になる原因｜_xlfnで直る – ゼロから始めるClaude Code副業ブログ

Pythonで作った表を開いたら、数式を入れたセルが全部 #NAME? になっとりました。書いたのは XLOOKUP。1行も計算されてへん。

先に結論だけ書いときます。原因はプログラム側の書き方でした。openpyxl で新しめの関数を書く時は、関数名の頭に _xlfn. を付けなあかん。それだけです。

ただ、そこに辿り着くまでに一回まちがった方向へ走ってます。その回り道のほうが役に立つと思うので、順番に書きます。

Pythonは関係なく、Excelで XLOOKUP を打ったら使えんかった、という人は原因が別です。先にXLOOKUPが使えない時の節へどうぞ。

数式のセルだけが、きれいに全部エラー

作っとったのは、商品コードから単価を引っぱってくる表です。Pythonの openpyxl というライブラリで、セルに数式を書き込む形にしました。

ws["D2"] = "=XLOOKUP(A2, Master!A:A, Master!C:C)"

ファイルは普通にできます。エラーも出ません。開いて初めて分かる。

#NAME? は、その名前の関数を知らん、という意味のエラーです。数式を書いたセルだけが、上から下まできれいに全部これになっとりました。

最初に疑ったのは、ソフトのほうやった

XLOOKUP は比較的あたらしい関数です。なので最初に思ったのは、開いとるソフトが対応してへんのやろ、でした。私の環境にはExcelが入ってなくて、LibreOffice で開いとったので、なおさらそう思い込みました。

で、この筋で調べにいこうとした手前で止まりました。同じファイルの TEXTJOIN まで #NAME? になっとったからです。

TEXTJOIN はだいぶ前からある関数です。新しいやつだけが落ちとるなら対応の問題やけど、古いのまで巻き添えになっとるなら話は別のところにある。あのままソフトの対応表を読みにいっとったら、半日は帰ってこれてへんかったと思います。

犯人は、ファイルの中での本名がちがうこと

XLOOKUP関数に_xlfnを付けると#NAME?エラーが解消される比較例 — _xlfnを付けるだけでエラーが直る

xlsx ファイルは、実は中身がZIPです。拡張子を zip に変えて解凍すると、設定やシートの中身が書かれたテキストがぞろぞろ出てきます。数式もその中に、ただの文字列として保存されとります。

そして Excel 2019 のころから後に追加された関数は、ファイルの中では別の名前で書かれとる。XLOOKUP は _xlfn.XLOOKUP という名前です。画面に XLOOKUP と表示されるのは、開いたソフトが接頭辞を外して見せとるだけ。

Excel本体で数式を打つと、この変換は自動でやってくれます。けど openpyxl は、渡した文字列をそのまま書き込むだけ。変換は一切しません。だから素で XLOOKUP と書くと、ファイルの中には存在せん名前の関数が入ることになる。

直し方はこれだけです。

ws["D2"] = "=_xlfn.XLOOKUP(A2, Master!A:A, Master!C:C)"

開き直したら、全行ちゃんと値が入っとりました。

どれに付けて、どれには要らんのか

_xlfn接頭辞が必要なXLOOKUPやLETなどの関数と、そのまま使えるSUMIFSなどの関数を色分けした一覧表 — 接頭辞が要る関数と要らん関数の早見表

全部の関数に付けるわけやないです。私が実際に手元で試して確かめた範囲だと、こうなりました。

接頭辞が要るもの

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.xlsx

csv に落ちるのは、数式やなく計算した結果の値です。つまり出てきたファイルを #NAME? や #REF! で検索して、1件も引っかからんかったら数式は全部生きとる、と機械的に言えます。

実際にこれで、1,098行の商品マスターが全行ちゃんと解決しとることを確かめました。Excelを持ってなくても、渡す前に自分で検品できる。

XLOOKUPが使えない時（Excel 2019以前）

ここまでは「プログラムで書いたら #NAME?」の話でした。検索でここに来た人の中には、Excelに XLOOKUP を打ったらそもそも使えん、という人もおると思います。

その場合は、たぶんExcelの版です。Microsoft のXLOOKUP 関数のページに「Excel 2016 および Excel 2019 では使用できません」とはっきり書いてある。使えるのは Microsoft 365 や Excel 2021・2024 のような新しい版です。職場のPCが2019のまま、というのはわりとある話やと思う。

版を上げられん時は、昔からある関数で同じことができます。この記事の表（商品コードから単価を引く）なら、どっちの書き方でもいける。

=INDEX(Master!C:C, MATCH(A2, Master!A:A, 0))
=VLOOKUP(A2, Master!A:C, 3, FALSE)

XLOOKUP に近いのは INDEX と MATCH の組み合わせのほうです。探す列が左端やなくても使える。VLOOKUP は、探す列が範囲の一番左にある時だけ。どっちも最後の 0 と FALSE が「ぴったり一致で探す」の指定で、これを抜くと近い値を勝手に拾ってきます。

どっちも古い関数なので、openpyxl で書く時も _xlfn. は要りません。3行だけの小さい表で確かめたら、_xlfn. 付きの XLOOKUP と同じ値が出て、見つからん時はどれも #N/A でした。ただ開いたのは LibreOffice で、Excel 2019 の実物では試してません。

おわりに

#NAME? が全セルに出た時、まず疑うべきは開いとるソフトやなく、書き込んだ側の関数名です。_xlfn. を付ける。それで直ります。

ただ個人的にでかかったのは、そこやなくて古い関数まで一緒に落ちとるのが変やと思えたことのほうでした。エラーは全部同じ顔で出てくるんで、どれが不自然かに気づけんと、いくらでも遠回りできてまう。

集計まわりを自動化する話やと、業務改善Webアプリのサンプル1本目を1日で公開した話も書いてます。あっちは表そのものを卒業する側の話です。

Excelの中で自動化するか、Excelの外に出すかで迷っとるなら、Excelの業務自動化はVBAかWebアプリか｜選んだ理由に私がWebアプリを選んだ時の考えを書いてます。