テーブルでは、末尾に集計行を設けられますが、途中に小計行を設ける機能はありません。これは、「データの集合」というテーブル本来の用途を考えれば無理からぬところです。
その本来の用途に目をつぶって、テーブル内に小計行を設ける方法を紹介します。
小計行付きテーブルの作り方
(1) 小計グループの列を設ける
明細行／小計行の区別とグループ分けを判別するための[Grp]列を設けます。この例では、グループは a, b, c …の1字で判別します。小計行は、その1字に「小計」を追記する形式で判別します。
グループを分けない場合は、すべて空欄のままでOKです。
(2) 金額欄に数式を入力する
ここの数式が今回の主要ポイントです。
テーブルの利点を活かすため、明細行と小計行とで別の数式を使い分けたりせずに共通の数式を使います。このため、数式内にIFS関数による条件分岐を設けます。
=IFS(LEN([@Grp])>1,SUMPRODUCT(N([Grp]=LEFT([@Grp])),[単価],[数量]),ISNUMBER([@数量]),[@単価]*[@数量],TRUE,””)
少し複雑な数式になるので、構造を整理しておきます。@の有無に注意してください。[@列名]は列全体、[列名]は各行の列(セル)を示しています。
=IFS( ① LEN([@Grp])>1, SUMPRODUCT(N([Grp]=LEFT([@Grp])),[単価],[数量]), ② ISNUMBER([@数量]), [@単価]*[@数量], ③ TRUE, "")① : 小計行
[小計G]の文字数で小計行を判別します。N([小計G]=LEFT([@小計G]))で、対象明細行を条件判定してSUMPRODUCTで集計しています。例えば、「A小計」なら「A」の明細行が対象になります。
② : 明細行
[数量]が数値かどうかで明細行を判別しています。
③ : その他
小計行でも明細行でもない空行もありうるため、""として#N/Aエラー表示を避けています。
(3) 集計行に数式を入力する
=SUMPRODUCT([単価], [数量])
デフォルトの「SUBTOTAL(109, [金額])」では小計行の金額も含まれてしまうため、SUMPRODUCTを使って単価と数量の値のある各行から算出しています。
(4) 条件付き書式を設定する
小計行の色やフォントを調整して明細行と区別しやすくします。金額列と同様に[小計G]の文字数で小計行を判別します。
テーブルを使えば各行の数式や範囲の維持が半自動化されていることから、万事解決というわけではないものの比較的安定的な運用ができるのです。