Googleスプレッドシートの .xlsx は Excel で静かに壊れる — 203セルを動的配列なしで書き直す

Googleスプレッドシートで作ったものを .xlsx で書き出して配ると、受け取った側の Excel では別のものが開く。しかも壊れ方が静かなので、開いた相手も、渡したこちらも気づけない。

パスワード管理シートを Booth に出したあと、商品名に「Excel / Googleスプレッドシートで使う」と書いている以上は Excel で実際に動くのか確かめようと思って、書き出した .xlsx を openpyxl で開いた。ダッシュボードの数式が全部 __xludf.DUMMYFUNCTION という見慣れない関数になっていた。数えたら203セル。そして203セル全部が IFERROR に包まれていた。この記事では、その203セルをどう見つけて、動的配列に頼らない形にどう書き直したかを書きます。

エラーが出ないから、壊れていることに気づけない

Googleスプレッドシート専用の関数は、.xlsx に書き出しても消えない。Excel が解釈できない関数として __xludf.DUMMYFUNCTION("元の数式") の形で保存され、元の数式は引数の文字列として中に残る。Excel はこれを未知の関数として扱うので、本来なら #NAME? が出る。

出なかった。元の数式が IFERROR で包まれていたからだ。

=IFERROR(__xludf.DUMMYFUNCTION("IFERROR(SORTN(FILTER({'パスワード一覧'!A2:A80, …

Googleスプレッドシート側では「該当が無い月は — を出す」ための保険として付けた IFERROR が、Excel では「そんな関数は無い」という例外まで一緒に飲み込む。返るのは空文字かフォールバック文字列で、画面上は「— 該当なし —」と出る。データが0件のときの正常な表示と、関数が動いていないときの表示が、見分けられない。

配布していたファイルで壊れていたのは次の5箇所だった。

flowchart TD
  A["Sheets の数式<br/>ARRAYFORMULA / FILTER / SORTN / QUERY"] --> B["Excel で開く"]
  B --> C["その関数が存在しない"]
  C --> D["外側の IFERROR が例外ごと捕まえる"]
  D --> E["空文字が返る"]
  E --> F["画面には「— 該当なし —」"]

重複パスワードの警告が「該当なし」と出ているのは、重複が無いからではなく、判定そのものが走っていないから。安全に関わる表示でこれが起きていた。

壊れたセルは目で探さず、数式セルだけを機械で数える

__xludf.DUMMYFUNCTION は文字列として残るので、openpyxl で全シートを舐めれば全数が出る。目視でダッシュボードを眺めるより確実だし、修正後に0件になったことも同じコードで確かめられる。

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 "")
                # 数式セルだけを見る。FAQ 本文に関数名が出てくるのは誤検知なので除外する
                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("=") の1行が要る。このファイルには「使い方ガイド」シートがあり、FAQ の回答文に ARRAYFORMULA という語が普通に出てくる。数式セルに絞らないと、直しようのない文章まで検出結果に混ざる。

実測は203セル。内訳はパスワード一覧199・ダッシュボード3・サブスク管理1で、パスワード一覧の199はほぼ G列だった。ARRAYFORMULA は G2 の1セルだけが本体で、G3 以降は =IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),"") という値のプレースホルダに置き換わっている。Sheets が配列で埋めていた範囲が、書き出しの時点で198個の抜け殻になっていた。

動的配列に逃げない — FILTER / SORT / UNIQUE は Excel 2016 に無い

Excel 365 には FILTER / SORT / UNIQUE / TAKE がある。Google 側の書き方にいちばん近いので置き換え先としては自然だが、これらは Excel 2019 と 2016 には無い。買った人がどのバージョンを使っているかは分からないし、Booth の商品ページに「365 が必要」と後から但し書きを足すのは、売り方として筋が通らない。

代わりに、動的配列を必要としない形に落とした。

考え方は単純で、「並べ替えて上位N件を取る」を2段に割る。まず各行に順位や連番をヘルパー列として持たせ、表示側は「順位が n の行を引く」だけにする。並べ替えという操作そのものを消すと、動的配列が要らなくなる。

書き直しは openpyxl から行単位で流し込んだ。200行×4列のヘルパー式を手で貼るのは現実的ではないし、あとで範囲を変えたくなったときにスクリプト側の定数を1つ直せば済む。

順位は SUMPRODUCT でヘルパー列に書く

「最終更新から90日を超えたものを、経過日数の降順に並べる」をヘルパー列で書くと次のようになる。RANK を使わないのは、対象を絞った上での順位が要るからだ。

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,"")'
    )

生成される式はこうなる。

=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,"")

条件を掛け算で積むのが SUMPRODUCT の使い方で、「自分より大きい件数 + 1」が順位になる。ここで2つ効いている。

ISNUMBER の項は外せない。L列（経過日数）は該当が無い行で "" を返すが、Excel の比較では文字列が数値より大きいと評価されるので、$L$2:$L$200>90 だけだと空行が全部条件を満たしてしまう。数値行に限定する掛け算を挟んで落とす。

同値の扱いも要る。経過日数が同じ行が2つあると順位が重複し、表示側の MATCH はどちらか片方しか引かないので、もう一方が表から消える。(値が同じ) × (行番号が小さい) の項を足して、同値なら上の行を先に置く。

REGEXMATCH の代わりは、配列定数と SUBSTITUTE の差分

パスワード強度の判定は REGEXMATCH(E2, "[A-Z]") で文字種を見ていた。Excel に正規表現は無い。文字種ごとの出現数を数える方向に変える。

def char_class_count(cell, chars):
    """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] の判定をそのまま移せる。記号は「全長 − 大文字 − 小文字 − 数字」で出す。

最初は MID(E2, ROW(INDIRECT("1:"&LEN(E2))), 1) で1文字ずつ配列に展開して FIND で照合する書き方にした。検証にかけたら全件が「弱」に倒れた。INDIRECT の配列展開は環境によって挙動が変わるうえ、IFERROR で包まれた側に落ちるので、やはり静かに全部「弱」になる。INDIRECT を使わない書き方に変えて、この症状は消えた。

表示側は INDEX/MATCH で引くだけにする

ヘルパー列に順位が入っていれば、ダッシュボード側は「順位 n の行から A列を取る」だけで済む。

def lookup(rank_col, value_col, first_row, row):
    n = row - first_row + 1
    return (
        f'=IFERROR(INDEX({SHEET}!{value_col}$2:{value_col}${LAST_ROW},'
        f'MATCH({n},{SHEET}!${rank_col}$2:${rank_col}${LAST_ROW},0)),"")'
    )

=IFERROR(INDEX('パスワード一覧'!A$2:A$200,
         MATCH(1,'パスワード一覧'!$R$2:$R$200,0)),"")

順位は ROWS(B$10:B10) のような相対参照でも書けるが、行ごとに Python 側で式を生成しているので、リテラルの整数を埋めるほうが単純で、関数サポートの差も踏まない。IFERROR はここでも使うものの、今度は「順位 3 の行が存在しない＝該当が3件未満」という本来の意味しか持たない。

沈黙するのは数式だけではない

体験版に入れるサンプルデータを openpyxl で書き込んだとき、同じ形の失敗をもう一度やった。日付を "2019-04-02" という文字列で入れたら、経過日数・要更新件数・更新推奨 TOP5 の3つが同時に空になった。

L列は INT(TODAY()-K2) で経過日数を出している。K2 が文字列だと減算が成立せず、IFERROR が受けて空文字を返す。ヘルパー列の ISNUMBER はそれを正しく除外し、MATCH は引くべき行を見つけられず、ダッシュボードは「該当なし」を出す。どこにもエラーは出ない。

ws.cell(row=r, column=10).value = datetime.date.fromisoformat(reg)  # str ではなく date
ws.cell(row=r, column=10).number_format = "yyyy-mm-dd"

IFERROR が便利なのは、想定した失敗だけを受けているときだけ。想定していない失敗まで同じ見た目に畳んでしまうと、配布物が動いていないことを誰も知らないまま残る。

まとめ

Googleスプレッドシートで作って .xlsx で配るなら、書き出したファイルを一度は機械で開いたほうがいい。__xludf.DUMMYFUNCTION を数えるだけなら20行で済むし、修正後に0件へ戻ったことも同じコードで確認できる。

書き直しの方針としては、Excel 365 の動的配列に寄せるより、ヘルパー列と INDEX/MATCH まで落としたほうが配布物には向いていました。式は長くなるが、Excel 2016 と Googleスプレッドシートで同じ数字が出る。作る側が Python で生成する前提なら、式の長さは払うべきコストのうちに入らない。

同じ「壊れているなら気づける形にしておく」という発想は、Zod スキーマで運用ルール違反をビルドで落とす話にも書いた。直したファイルはパスワード管理シートに反映してある。

よくある質問

Googleスプレッドシートを .xlsx で書き出すと何が起きる？

Google 専用関数がそのまま残ります。ARRAYFORMULA / FILTER / SORTN / QUERY / UNIQUE / REGEXMATCH は Excel に存在しないので、書き出したファイルでは openpyxl から __xludf.DUMMYFUNCTION という関数として見えます。中身は元の数式が文字列で保存されているだけで、Excel 側では計算されません。

なぜエラーではなく「該当なし」と表示される？

元の数式が IFERROR で包まれているからです。Googleスプレッドシート側では「該当が無いときに — を出す」ための保険として付けた IFERROR が、Excel では「関数が存在しない」という例外まで一緒に捕まえます。結果として #NAME? ではなく空文字やフォールバック文字列が返り、画面上は正常に見えます。配布ファイルでは203セル全てがこの形でした。

Excel 365 の FILTER / SORT / UNIQUE で置き換えればいい？

買い手の Excel のバージョンを仮定できるなら、それでかまいません。動的配列は Excel 2019 / 2016 には無いので、環境を選ばずに配るなら使えない。各行に順位・連番・フラグを出すヘルパー列を作り、表示側は INDEX/MATCH で引く形にすると、Excel 2016 と Googleスプレッドシートの両方で同じ結果になります。

REGEXMATCH が無い環境で文字種をどう数える？

配列定数と SUBSTITUTE の差分で数えます。SUBSTITUTE($E2,{"A","B",…},"") のように配列定数を渡すと各文字を消した文字列が返るので、LEN の差を SUMPRODUCT で合計すれば出現数になる。SUBSTITUTE は大文字と小文字を区別するので、大文字だけを数える用途にもそのまま使えます。MID と INDIRECT で1文字ずつ展開する書き方は環境差が出やすく、実際に検証で全件が「弱」に倒れました。