いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Gスプレッドシート)関数を使って小計行を追加する

 Googleスプレッドシートで、関数により表データに小計行を挿入する方法についてです。

  • REDUCE/LAMBDA関数等を使って、データに小計行を加えた表を作成することができます。

手順

 対象となるデータはB3:C11の範囲です。B列には品目が、C列には個数が入力されています。
 これに対しE3セルに式を入力し、品目ごとの小計行を追加した行を取得しています。

 E3セル

=QUERY(
REDUCE({"",""},UNIQUE(B3:B11),
LAMBDA(a,b,{a;FILTER(B3:C11,B3:B11=b);b&" 計",SUMIF(B3:B11,b,C3:C11)})
),
"OFFSET 1",0)

 まずREDUCE/LAMBDA関数のはたらきですが、「{"",""}」つまり1行2列の空文字列をa、そして「UNIQUE(B3:B11)」つまり品目名から重複を取り除いた配列をbと定義しています。
 そして、FILTER関数により元のデータからbの各要素に対応した行(最初は「トマト」に対応した3つの行)を取り出し、さらにそれに対応する見出し(最初は「トマト 計」)と小計からなる1つの行を生成し、それらをaに追加していきます。
 REDUCE関数の第1引数は何か指定する必要がある(「無」にはできない)ため「{"",""}」としていますが、最終的にこの行を取り除く必要があり、QUERY関数によりこれを行っています。具体的にはQUERY関数の第3引数を0とし(見出しの行数を0とすることで先頭の「{"",""}」をデータとみなす)、第2引数を1とする(データから先頭1行を捨てる)ことで実現しています。
 「{"",""}」を「B2:C2」などに代えて見出しを付けてしまえばQUERY関数は省略できますし、例としてはそちらの方がスマートだったかもしれません。


 なお、元のデータがソートされていない場合でも、グループ化され小計行が追加されます。
 ただし品目は初出順に並びますし、個数についても同様です。


 次の画像では、1つ上の例と同じ(ソートされていない)データを用いつつ、品目の並びが最初の例と同じになるように指定しています。ただし同一品目内でのデータの並び方については1つ上の例と同じです。

 G3セル

=QUERY(
REDUCE({"",""},E3:E5,
LAMBDA(a,b,{a;FILTER(B3:C11,B3:B11=b);b&" 計",SUMIF(B3:B11,b,C3:C11)})
),
"OFFSET 1",0)

 E3:E5セルにて並び順を定め、これをREDUCE関数の第2引数として参照しています。もちろん即値で「{"トマト";"りんご";"かぼちゃ"}」などと指定しても同じです。


 さまざまな拡張を考慮すると検討すべき点は多いですが、さしあたりシンプルなケースを紹介しました。