メインコンテンツへスキップ

03 · 行に対する集計

シナリオ​

データブロックの下に合計フッター行を付けたり、会社全体の合計のようにソース全体を横断する集計値をヘッダーセルに引き込みます。

ブラケット集計 ― データブロックを対象​

{{ SUM([更新金額]) }}
{{ COUNT([更新金額]) }}
{{ AVERAGE([更新金額]) }}
{{ MIN([更新金額]) }}
{{ MAX([更新金額]) }}

データブロック内で使うと、反復されるソース行の上で累積されます。フッター行(データブロックの下の行で、同じデータブロック行にはテンプレートブロックがない場合)で使うと、ちょうど展開されたブロックの結果を指します。

| A1: 取引先     | B1: 更新金額             |
| A2: {{ [取引先] }}| B2: {{ [更新金額] }}     | ← データブロック
| A3: 合計       | B3: {{ SUM([更新金額]) }}| ← フッター

ソース行 3 つで展開すると、3 行目が 5 行目に押し下がり、B5 には 3 つの 更新金額 の合計が入ります。

ソース限定集計 ― ソース全体を対象​

{{ SUM(更新一覧[金額]) }}        # アクティブブロックではなくソース全体
{{ COUNT(顧客[取引先]) }}

SUM(SourceName[Column]) と書くと、xl3 は名前で指定したソース全体を合算します ― フィルタやジョインを経たブロックではありません。ブロックがフィルタされても値が変わってはいけないヘッダーの「全体合計」セルでこの形を使います。

更新一覧 は __sources__ で宣言した名前です。Recipe 07 を参照してください。

フィルタはブロックだけを変え、ソースには触れない​

{{ @filter [地域] = "東京" }}
{{ [取引先] }}    | {{ [更新金額] }}
合計:              | {{ SUM([更新金額]) }}        # 東京の行のみ
Overall:            | {{ SUM(Source[更新金額]) }}  # 全行

SUM([更新金額]) はフィルタ適用後のブロックを反映します。SUM(Source[更新金額]) はフィルタを無視します。

動作しないこと ― 集計内の算術式​

SUM、AVERAGE、MIN、MAX、および 1-arg COUNT の引数は 必ず列参照([Column] または Source[Column])でなければなりません。行単位の算術式、リテラル、関数呼び出しが集計内に入ると、パース時点で xl3/eval/bad-aggregate-arg として 拒否されます(ADR-0059):

{{ SUM([数量] * [単価]) }}     # ✗ 行単位の算術 ― 拒否
{{ SUM(1 + 2) }}                # ✗ リテラル式 ― 拒否
{{ SUM(IF([地域]="東京", [金額], 0)) }}   # ✗ 関数呼び出し ― 拒否
{{ AVERAGE([売上] - [原価]) }}   # ✗ 行単位の引き算 ― 拒否

意図された動作です。Excel の SUMPRODUCT / 配列数式(行単位で計算してから集計)の意味論は XTL 0.x の対象外です ― ADR-0059 § "Why not allow SUM([a] + [b])" を参照。

解決策: ソースにヘルパー列を追加​

「売上 = Σ 数量 × 単価」のような標準パターンは、ソースワークブックに行単位の積を列として事前に作っておき、その列を合計する形で表現します:

# ソースデータに「金額」列を追加:
| 数量 | 単価 | 金額           |
|   3 |   100 |   =B2*C2 (または事前計算済の 300) |
|   2 |   150 |   =B3*C3 (または 300)             |

# テンプレートで:
{{ SUM([金額]) }}             # ✓ ― 事前計算済の列を合算

ソースがプログラムで生成されるなら掛け算結果を列に直接書き込み、手で管理するワークブックなら 金額 列に普通の Excel 数式を置きます。

解決策: 行単位セル + フッター集計​

行単位の積だけを表示すればよく合計は別途必要ない場合は、行単位のテンプレートセルで計算します:

| {{ [数量] }} | {{ [単価] }} | {{ [数量] * [単価] }} |   # ✓ 行単位

{{ [数量] * [単価] }} は反復される各行ごとに評価されるので正常に動作します。ただしこれは SUM([数量] * [単価]) とは異なります ― 行単位の積のフッター合計も合わせて必要なら、上のヘルパー列方式に戻るか、フッターセルにネイティブの Excel SUMPRODUCT 数式を入れます(ADR-0046 により xl3 が保持します)。

メモ​

  • 集計は ADR-0007 に従って空値を飛ばします。
  • COUNT は空でない値だけを数えます。空値を含む全行数が必要なら、絶対に空にならない列に対して COUNT(Source[any-required-col]) を使ってください。
  • 空でない値が 0 個の状態で AVERAGE はエラーではなく空値を返します。
  • 集計引数に合成式(リテラル、算術、関数呼び出し)を入れると ADR-0059 に従って xl3/eval/bad-aggregate-arg が発生します。
  • スペック参照: spec/language.md の「Aggregates」、ソース意味論は ADR-0012、引数形状ルールは ADR-0059。