Googleスプレッドシートで作った表を .xlsx で書き出して配ると、受け取った側の Excel では別のものが開きます。ARRAYFORMULA や QUERY は Excel に無いので当然といえば当然なのですが、やっかいなのはエラーが出ないことです。
配布していたファイルを openpyxl で開いたら、Google 専用関数が203セル残っていました。そして203セル全部が IFERROR に包まれていて、Excel では #NAME? ではなく「該当なし」と表示されていました。検出のしかたと、動的配列に頼らない書き直し方をまとめます。
なぜエラーにならないのか
Google 専用関数は書き出しても消えず、__xludf.DUMMYFUNCTION("元の数式") という形で残ります。元の数式は引数の文字列として保持されるだけで、Excel 側では計算されません。
=IFERROR(__xludf.DUMMYFUNCTION("IFERROR(SORTN(FILTER({'パスワード一覧'!A2:A80, …
Googleスプレッドシート側で「該当が無いときに — を出す」ために付けた IFERROR が、Excel では「そんな関数は無い」という例外まで一緒に飲み込みます。返るのは空文字かフォールバック文字列なので、画面上は「— 該当なし —」と正常に見えます。
データが0件のときの表示と、関数が動いていないときの表示が同じになります。目視では見分けられません。
検出は openpyxl で全シート舐めるだけ
__xludf.DUMMYFUNCTION は文字列として残るので、機械で数えられます。修正後に0件になったことも同じコードで確認できます。
import openpyxl
KEYS = ("DUMMYFUNCTION", "REGEXMATCH", "SORTN",
"QUERY(", "ARRAYFORMULA", "UNIQUE(", "FILTER(")
def check_no_google_funcs(path):
wb = openpyxl.load_workbook(path)
bad = []
for sh in wb.worksheets:
for row in sh.iter_rows():
for c in row:
v = c.value
txt = getattr(v, "text", None) or (v if isinstance(v, str) else "")
# 数式セルだけを見る（説明文に関数名が出てくると誤検知するため）
if not txt.startswith("="):
continue
if any(k in txt for k in KEYS):
bad.append(f"{sh.title}!{c.coordinate}")
return bad
txt.startswith("=") は入れておいたほうがいいです。使い方ガイドのような文章シートがあると、FAQ の回答文に ARRAYFORMULA という語が普通に出てきて、直しようのないものまで検出結果に混ざります。
置き換え先に Excel 365 の動的配列を選ばない
FILTER / SORT / UNIQUE は Google 側の書き方に近いので置き換え先として自然ですが、Excel 2019 と 2016 には存在しません。配布物だと相手のバージョンを仮定できないので、今回は使いませんでした。
| 方式 | Sheets | Excel 365 | Excel 2016 |
|---|---|---|---|
ARRAYFORMULA / QUERY / SORTN
|
動く | 動かない | 動かない |
FILTER / SORT / UNIQUE（動的配列） |
動く | 動く | 動かない |
各行のヘルパー列 ＋ INDEX/MATCH
|
動く | 動く | 動く |
方針は「並べ替えて上位N件を取る」を2段に割ることです。各行に順位をヘルパー列として持たせ、表示側は「順位が n の行を引く」だけにします。並べ替えという操作そのものを消せば、動的配列が要らなくなります。
順位は SUMPRODUCT でヘルパー列に書く
「経過日数（L列）が90を超える行だけを、経過日数の降順に並べる」をヘルパー列で書くとこうなります。対象を絞った上での順位なので RANK は使えません。
=IF(AND($A2<>"",ISNUMBER($L2),$L2>90),
SUMPRODUCT(($A$2:$A$200<>"")*ISNUMBER($L$2:$L$200)*($L$2:$L$200>90)
*(($L$2:$L$200>$L2)+(($L$2:$L$200=$L2)*(ROW($L$2:$L$200)<ROW($L2)))))+1,"")
条件を掛け算で積んで、「自分より大きい件数 + 1」を順位にします。200行×複数列を手で貼るのは現実的ではないので、Python 側で行ごとに生成しました。
def rank_renew_formula(row):
L, A = f"$L{row}", f"$A{row}"
la, lb = f"$L$2:$L${LAST_ROW}", f"$A$2:$A${LAST_ROW}"
rows = f"ROW($L$2:$L${LAST_ROW})"
return (
f'=IF(AND({A}<>"",ISNUMBER({L}),{L}>90),'
f'SUMPRODUCT(({lb}<>"")*ISNUMBER({la})*({la}>90)*'
f'(({la}>{L})+(({la}={L})*({rows}<ROW({L})))))+1,"")'
)
ハマりどころ1: ISNUMBER を省くと空行が全部入ってきます。 L列は該当が無い行で "" を返しますが、Excel の比較では文字列が数値より大きいと評価されるため、$L$2:$L$200>90 だけだと空行が条件を満たしてしまいます。数値行に限定する掛け算が要ります。
REGEXMATCH の代わりは配列定数 + SUBSTITUTE
REGEXMATCH(E2, "[A-Z]") のような文字種判定も Excel には移せません。正規表現の代わりに、文字種ごとの出現数を数えます。
def char_class_count(cell, chars):
arr = "{" + ",".join(f'"{c}"' for c in chars) + "}"
return f'SUMPRODUCT(LEN({cell})-LEN(SUBSTITUTE({cell},{arr},"")))'
SUBSTITUTE に配列定数 {"A","B",…,"Z"} を渡すと、それぞれの文字を消した文字列が26個返ります。元の長さとの差を SUMPRODUCT で合計すれば大文字の総数です。SUBSTITUTE は大文字小文字を区別するので、[A-Z] と [a-z] をそのまま分けられます。記号は「全長 − 大文字 − 小文字 − 数字」で出せます。
ハマりどころ2: INDIRECT で1文字ずつ展開する書き方は避けたほうがいいです。 最初は MID(E2, ROW(INDIRECT("1:"&LEN(E2))), 1) で配列に展開して FIND で照合していましたが、検証すると全件が「弱」に倒れました。INDIRECT の配列展開は環境によって挙動が変わるうえ、失敗しても IFERROR の側に落ちるので、やはり静かに全部おかしくなります。
表示側は INDEX/MATCH で引くだけ
ヘルパー列に順位が入っていれば、ダッシュボード側は1行で済みます。
=IFERROR(INDEX('パスワード一覧'!A$2:A$200,
MATCH(1,'パスワード一覧'!$R$2:$R$200,0)),"")
MATCH の第1引数を 1, 2, 3 … と変えれば1位から順に並びます。IFERROR はここでも使いますが、今度は「順位3の行が無い＝該当が3件未満」という本来の意味しか持ちません。
Googleスプレッドシートで作って .xlsx で配るなら、書き出したファイルを一度は機械で開いてみることをおすすめします。20行のスクリプトで済みますし、直したあとの確認にもそのまま使えます。
同値で順位が重複したときの潰し方、日付を文字列で入れて3つの指標が同時に沈黙した話、5箇所の内訳は Aulvem 本家にまとめました → Aulvem｜Googleスプレッドシートの .xlsx は Excel で静かに壊れる。直したファイルはパスワード管理シートに反映しています。