openpyxlのExcelが#NAME?になる原因｜_xlfnで直る

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

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

ただ、「新しめ」の範囲は思ったより広いし、LET と FILTER には別の落とし穴もあります。下の表は、2026年10月に本物の Excel（Microsoft 365）で20通りの書き方を開いて確かめ直した結果です（LibreOffice 26.2 でも、そのうち18通りを開きました）。

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

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

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

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

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

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

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

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

この筋で調べにいく手前で止まったのは、同じファイルの TEXTJOIN まで #NAME? になっとったからです。当時の私は「TEXTJOIN は前からある関数やのに落ちとる。ほな対応の問題やないな」と考えました。

正直に書いとくと、この読みは半分まちがってました。あとで確かめたら、TEXTJOIN も Excel 2019 と Microsoft 365 で足された新しめの関数で、XLOOKUP とまったく同じ理由で落ちとった。結論にはたどり着けたけど、理由はたまたまです。

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

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

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

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

直し方はこれだけです。

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

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

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

前に書いた時より範囲を広げて、本物の Excel で確かめ直しました。「接頭辞なし」と「こう書く」の列は実際に開いた結果、「足された頃」の列は Microsoft の説明や私の知っとる範囲です。

前は「境目はだいたい Excel 2019」と書いてましたが、まちがいでした。試した範囲では、2010年の STDEV.S や2013年の IFNA にも要って、2007年の IFERROR や SUMIFS には要らんかった。Excel 2007 より後に足された関数は対象と思っといた方が安全です。

LET は変数の名前にも印が要る

LET は関数名の _xlfn. だけでは足りません。中で使う変数の名前にも _xlpm. を付けなあかん。

ws["D2"] = "=_xlfn.LET(_xlpm.x, Master!C2, _xlpm.x*2)"

これを忘れて _xlfn.LET(x, …) と書いた版は、#NAME? どころか、Excel に開かせた段階で失敗しました。同じ表から LET のその1行だけを抜いた版は、ちゃんと開けとります。試したのはプログラムから自動で開かせる形だけで、人が手で開いた時にどんな画面が出るかは確かめてへんです。

FILTER は、結果が広がらん

FILTER は _xlfn._xlws.FILTER と二段で書くのが Excel の保存する形やと知られとります（ここは私の確かめやなく知識です。_xlfn.FILTER だけで書いた版も開けて、同じ結果になりました）。ファイルは開けて値も出ます。ただ、出るのは先頭の1件だけでした。

試したのは「単価が100円より上の商品」を抜く式で、本当は「りんご」と「ぶどう」の2件が出るはずやった。実際に出たのは「りんご」だけ。Excel の数式バーを見ると、頭に @ が付いとります。下のセルへ結果が広がる書き方（スピル）にならず、1つのセルに1個だけ返す昔の扱いになっとる。

試したのは、セルに数式を文字列で入れるふつうの書き方だけです。この書き方では越えられんかった。FILTER で一覧を出したい時は、Python 側で絞り込んでから値として書き込むほうが確実です。SORT など同じ仲間の関数も同じ扱いのはずですが、そっちは試してません。

渡す前に、自分で確かめる手は2本いる

今回いちばん怖かったのは、作った本人が気づけん形の壊れ方やったことです。ファイル生成は成功する。プログラムから読み戻しても、数式の文字列はちゃんと入っとる。開いた人の画面でだけ全部エラーになる。

1本目：LibreOffice で計算させて、エラーを探す

LibreOffice をコマンドから呼んで、xlsx を csv に変換します。最後の -1 は、全部のシートを1枚ずつ書き出す指定です（新しめの LibreOffice で使える指定で、私は 26.2 で確かめました）。

soffice --headless --convert-to "csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,false,false,false,-1" --outdir out book.xlsx

csv に落ちるのは、数式やなく計算した結果の値です。出てきたファイルを #NAME? や #REF! で検索すれば、計算できんかったセルが見つかります。

ただ、これだけやと見逃す物がありました。

• IFNA：接頭辞なしで書いても、LibreOffice は計算してしまう。Excel で開くと #NAME? になるのに、csv にはエラーが出ません。
• FILTER の1件しか出ん問題：そもそもエラーにならんので、csv では引っかかりません。

2本目：数式の字を読んで、頭が付いとるか見る

なので、計算の結果やなく、数式の字そのものを見る手を足しました。openpyxl で読み戻して、頭が要る関数に _xlfn. が付いてへん所と、LET の変数に _xlpm. が付いてへん所を探すだけです。

import re
import sys
from openpyxl import load_workbook

# 頭（_xlfn.）が要る関数。上の表で確かめた物だけ入れとる。使う関数がほかにあれば足す
NEEDS = {"XLOOKUP", "TEXTJOIN", "CONCAT", "IFS", "IFNA", "STDEV.S", "LET", "FILTER"}

wb = load_workbook(sys.argv[1])
for ws in wb.worksheets:
    for row in ws.iter_rows():
        for c in row:
            f = c.value
            if not (isinstance(f, str) and f.startswith("=")):
                continue
            for name in re.findall(r"(?<![\w.])([A-Z][A-Z0-9.]*)\(", f):
                if name in NEEDS:
                    print(f"{ws.title}!{c.coordinate}  {name} に _xlfn. が無い  {f}")
            if "LET(" in f and "_xlpm." not in f:
                print(f"{ws.title}!{c.coordinate}  LET の変数に _xlpm. が無い  {f}")

LET の行は、最初に書いた版には無かったものです。最初の版を試しの表に当てたら、頭の無い6つ（LibreOffice が見逃した IFNA も含めて）は全部拾えたのに、_xlpm. の抜けた LET は「頭が付いとる」と見て素通りしました。一番怖い壊れ方が、2本のどっちにも引っかからん形やった。LET の行を足した今の版は、20通りの表に当てて、頭の無い6つと _xlpm. の抜けた LET の1つを拾い、正しく書いた物は1つも拾いませんでした。この2本を渡す前に回しとけば、Excel を持ってなくても、だいぶ手前で気づけます。表の中身が正しいか（件数や合計）は、また別に突き合わせるしかないです。

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

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

その場合は、たぶんExcelの版です。Microsoft の公式の「XLOOKUP 関数」のページに「XLOOKUP は Excel 2016 および Excel 2019 では使用できません」とはっきり書いてある。使えるのは Microsoft 365 や Excel 2021 以降の版です。職場の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 が「ぴったり一致で探す」の指定で、これを抜くと近い値を勝手に拾ってきます。見つからん時は、どれも #N/A になります。

どっちも古い関数なので、openpyxl で書く時も _xlfn. は要りません。Microsoft 365 の Excel でも、XLOOKUP と同じ値が出ました。ただ、Excel 2019 の実物では試してません。

おわりに

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

今回いちばん反省したのは、最初の記事で試した範囲が狭かったことです。XLOOKUP が直ったところで満足して、境目を「だいたい2019」とぼかしてた。20通り並べて本物の Excel で開き直したら、2010年の関数まで対象やったし、LET はファイルごと開かんかった。自分で書いた確かめ方にも、IFNA を見逃す穴がありました。

集計まわりを自動化する話やと、CSVの売上集計を1日で作った記録も書いてます。あっちは表そのものを卒業する側の話です。Excelの中で自動化するか、外に出すかで迷っとるなら、Excelの業務自動化はVBAかWebアプリかに私が選んだ時の考えを書いてます。

Excel の自動化で困っとったら

Python で作った表が開けん、数式が壊れる。そういう困りごとは、今の表を見せてもらえたら、直し方と自動化の形をこちらで書き起こします。

相談する（メッセージのみ）