16 · XTL 関数 vs Excel 数式
よくぶつかる落とし穴 ― こ こから始める
このページを開いた理由は、何かが思った通りに動かなかったからである可能性が 高いです。最もよくあるケース:
「セルに ¥1,234,567 を表示したいのに TEXT([金額], "¥#,##0") がうまくいかない」
XTL の TEXT() がサポートするフォーマットトークンはわざと小さく取られていて、通貨トークンは含まれません。正解はセルの数値表示形式(numFmt):
| ステップ | 場所 |
|---|---|
1. テンプレートセルのセル書式を "¥"#,##0 に設定 | Excel のセルの書式設定 → ユーザー定義 |
2. 同じセルに {{ [金額] }}(純粋な数値のみ)を入力 | XTL 置換 |
レンダリングされたセルには数値が入っており、Excel がこれを ¥1,234,567 として表示します。値が依然として数値なので、並べ替え、フィルタリング、他の数式参照がすべて正常に動作します。
同じパターンが (1,234) 形式の負数会計表記(#,##0;(#,##0))、パーセント(0.00%)、日付(yyyy-mm-dd)などにもそのまま適用されます。
「=B2*2 で行ごとに計算したいのに、すべての行が同じ結果を見せる」
xl3 は @repeat で行を展開するとき数式テキストを文字通り保存します ―
B2 を B3、B4 に自動的に書き換えることはありません(契約は ADR-0046 参照)。
代わりに XTL 式を使ってください:
{{ [金額] * 2 }}
これはレンダー時点で各行ごとに評価され、計算された数値がセルに入ります。同じ結果ですが行参照の混乱がありません。
「下部に合計を置きたいのに =SUM(B2:B5) が行が増えても範囲が広がらない」
原因は同じです ― xl3 は範囲参照も書き換えません。2 つの選択肢:
- 列全体参照をフッターに置く:
=SUM(B:B)(または@filterで上の方でデータ行だけ残す)。 - XTL 集計関数: フッターセルに
{{ SUM([金額]) }}を置く。レンダー時点で計算されて数値として入ります。
「行ごとにクリックできるリンクを入れたい」
XTL の HYPERLINK() 関数を使ってください(URL/label の両方を列参照可能):
{{ HYPERLINK([URL], [表示名]) }}
静的 URL ならセルに =HYPERLINK("https://...", "label") 数式を直接入れても 良いです(xl3 が保存します)。
「IF(...) で 5 つの分岐を作ったらネストが深すぎて読めない」
IFS(c1, v1, c2, v2, ...) が XTL の多分岐条件関数です。最後は
TRUE, default で閉じてください:
{{ IFS([R] > 10000, "VIP", [R] > 1000, "標準", TRUE, "ライト") }}
「SUMPRODUCT のように SUM(数量 * 単価) を使いたい」
XTL 集計は引数内に行単位の算術式を受け付けません。{{ SUM([数量] * [単価]) }}、{{ SUM([A] + [B]) }}、{{ AVERAGE([売上] - [原価]) }} のような形はパース時点で xl3/eval/bad-aggregate-arg として拒否されます(ADR-0059)。引数は列参照 1 つでなければなりません: [Column] または Source[Column]。
3 つの解決策(好ましい順):
- ソースにヘルパー列を追加 ― ソースに
金額列(計算式または事前に掛けた値)を作り{{ SUM([金額]) }}を使用。「A × B の合計」標準 XTL パターン。 - フッターセルにネイティブ Excel
SUMPRODUCT― xl3 はセル数式をそのまま保存します(ADR-0046)。フッターセルに=SUMPRODUCT(E2:E10000, F2:F10000)を直接書きます。レンダー時点の実際の行数が分からないのでE2:E10000のようなオーバーシュート範囲を使います。2 つのフッター落とし穴(自列参照、行オーバーシュートの二重カウント)を避けないといけません ― LLM 作成ガイド § Footer pitfalls を参照。 - 行単位 XTL セル + レンダー出力のヘルパー列 ―
{{ [数量] * [単価] }}を行単位セルに置きます(集計ではないので正常動作)。その値までフッターで合計したいなら結局 1 番か 2 番に戻ります。
なぜこの制約があるのか: XTL 0.x は関数表面を小さく予測可能に保ちます。行単位計算後の集計(Excel 配列数式動作)は意図的に deferred ― ADR-0059 § "Why not allow SUM([a] + [b])"。
「SUMIF / COUNTIF / AVERAGEIF を探している」
関数を探さずデータブロックパターンを使ってください。「ステータスが VIP の行の amount 合計」:
{{ @filter [ステータス] = "VIP" }}
{{ @repeat down }}
... データ行テンプレート ...
{{ SUM([金額]) }}
フィルタされた合計とフィルタされていない全行を一緒に表示する必要があるなら、セルに =SUMIF(B:B, "VIP", C:C) を直接入れてください ― xl3 が数式を保存し、Excel が開くときに計算します。
「ISBLANK(x) を使いたい」
0.5.x からサポートしています(ADR-0047)。ADR-0007 基準で空のとき true を返します ― 空白だけの文字列 も空値として見ます。
{{ IF(ISBLANK([メモ]), "(なし)", [メモ]) }}
同じ結果を fallback 形式でも使えます: IFEMPTY([メモ], "(なし)")。
同じ述語を検査します。
一般ルール
ワークブックが作成される前に値が決まらなければならない場合にだけ XTL
{{ ... }}を 使ってください。それ以外は数式をセルに直接入れ、Excel が開くときに計算するようにしてください。
境界はレンダー時点:
- レンダー前 ― XTL のみ可能:
@filter、@sort、@top、@group、@subtotal、ソースデータ集計(SUM、COUNT、…)、クロスソースXLOOKUP、output_file_pattern、__sheet_name_pattern__、__inputs__デフォルト値。Excel は この領域に手をつけられません ― 計算するセルそのものが存在しません。 - レンダー後 ― Excel で十分: セル表示書式、レンダーされた値に対するセル単位算術、 出力値の文字列変換、型検査、出力セルか らの日付コンポーネント抽出。
この原則は normative ― ADR-0043 ― であり、XTL 関数面積を小さく維持する核心 装置です。XTL 表にないすべての Excel 関数は意図的に Excel 数式経路に 落ちるよう設計されています。
対照表
| 目的 | XTL 方式 | Excel 数式方式 | 選択 |
|---|---|---|---|
数値を 1,234,567.00 で表示 | {{ TEXT([A], "#,##0.00") }}(文字列) | セル numFmt = "#,##0.00"、値 {{ [A] }}(数値) | 視覚用は Excel 数式、文字列が必要なら XTL |
¥1,234,567 表示 | (XTL 未対応) | セル numFmt = "¥"#,##0 | Excel 数式 |
| 負数を括弧で表示 | (未対応) | セル numFmt = #,##0;(#,##0) | Excel 数式 |
行単位掛け算(*2) | {{ [A] * 2 }} | =B2*2 ❌ 行ごとに書き換わらない | XTL |
| 行展開上の合計フッター | {{ SUM([A]) }} | =SUM(B:B) 列全体可 | どちらも可 |
| A × B の合計(SUMPRODUCT) | ソースにヘルパー列 + {{ SUM([金額]) }} | フッターセルに =SUMPRODUCT(E2:E10000, F2:F10000) | Excel 数式またはヘルパー列 ― SUM([A]*[B]) は xl3/eval/bad-aggregate-arg 発生 |
| 静的ハイパーリンク | (不要) | =HYPERLINK("...", "label") | Excel 数式 |
| 行単位動的ハイパーリンク | {{ HYPERLINK([URL], [表示名]) }} | quoting 地獄で非現実的 | XTL |
| 「今月」の行だけフィルタ | {{ @filter MONTH([日付]) = MONTH(TODAY()) }} | (Excel はレンダー前フィルタ不可) | XTL 専用 |
| ファイル名「先月」 | {{ TEXT(EDATE(TODAY(), -1), "YYYY-MM") }}.xlsx | (ファイル名に数式経路なし) | XTL 専用 |
| 多分岐ランクラベル | {{ IFS([R]>10000, "VIP", [R]>1000, "Std", TRUE, "ライト") }} | =IFS(B2>10000, "VIP", ...) | どちらも、filter/group がこの値に依存するなら XTL |
| 条件付き集計 | @filter + SUM ブロック | =SUMIF(B:B, "VIP", C:C) | ブロック合計は XTL、横断型は Excel 数式 |
MOD / INT / SQRT / POWER | (XTL 未対応) | セル数式 | Excel 数式 |
| 空値チェック | ISBLANK([X]) または IFEMPTY([X], "fallback") | =ISBLANK(B2) | どちらも、ISBLANK が Excel idiom と一致 |
その他 IS* 型検査 | (未対応) | =ISNUMBER(B2) など | Excel 数式 |
早見決定木
値が次に影響するか:
• どの行をレンダーするか? → @filter / @sort (XTL)
• どうグループ化するか? → @group / @subtotal (XTL)
• 出力ファイル名? → {{ ... }} (XTL)
• シート名? → {{ ... }} (XTL)
• __inputs__ デフォルト値? → {{ ... }} (XTL)
• 行単位の計算済み表示値? → {{ ... }} (XTL)
• セルが*どう見えるか*? → セル numFmt (Excel 側)
• 行単位の数式? → {{ ... }} 式 (XTL)
• 列全体 / 静的計算? → =FORMULA をセルに直接 (Excel 側)
なぜこのルールが存在するのか
XTL 関数面積は意図的に小さく維持されます(ADR-0043)。ポーターが実装するカタログが 明確になるためです。セル出力専用に関数を追加するのは Excel がすでにやっていることを 重複させスペックを膨らませます。
トレードオフ: xl3 出力ワークブックは作成者がセル数式を使うと完全な self-contained ではなくなります ― 開くときに Excel の再計算に依存します。ほとんどの運用レポートではこれが むしろ自然なワークフローです。
XTL にない関数が必要だと感じたとき:
- その値が directive(
@filter、@sort、@top、@group、@subtotal) 内で使われるか、output_file_pattern/__sheet_name_pattern__で使われるか? → XTL である必要があります。XTL が必要なものを提供していなければ「Function re-proposal」 テンプレートでイシューを登録してください(GitHub issues)。 - それ以外 → Excel 数式をセルに直接入れてください。xl3 が保存し、Excel が開くときに 計算します。
参照
- ADR-0043 — Excel-native preference principle
- ADR-0044 — Function batch accepted
- ADR-0045 — Function batch rejected
- ADR-0046 — Cell formula preservation contract
- ADR-0047 — ISBLANK as IFEMPTY alias
- Cookbook 10 — スタイルとブランディング ―
numFmtが正解のとき - Cookbook 11 — TEXT() 書式設定 ―
TEXT()が正解のとき - Cookbook 12 — 空値を扱う ― IFEMPTY / ISBLANK の相棒