query で Pivot したデータの合計値の列を追加

spreadsheet

MMULTIで合計値を計算できるが，色々はまったのでめも。

[月別元データ]シートの内容

=query(
  importrange("スプレッドシートのキー", "マージ!B:S"),
  "Select Col12, sum(Col18), sum(Col17) Where Col7 = '●' Group by Col12 Pivot year(Col1), month(Col1)+1 Label sum(Col18) '元本', sum(Col17) '評価額'"
)
<||

元データを引っ張る。
>||
=offset('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), counta('月別元データ'!B1:1))

転置して，数値化

=TRANSPOSE( arrayformula( offset('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), counta('月別元データ'!B1:1)) * 1))

かけ合わせるベクトルを作る

=ARRAYFORMULA(OFFSET('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), 1)^0)

かけ合わせて，転置。合計値行を作る

=transpose(MMULT( TRANSPOSE( arrayformula( offset('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), counta('月別元データ'!B1:1)) * 1) ), ARRAYFORMULA(OFFSET('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), 1)^0)) )

合計値の列を追加

={offset('月別元データ'!A1, 0, 0, counta('月別元データ'!A:A), counta('月別元データ'!1:1)); 
"合計",transpose(MMULT( TRANSPOSE( arrayformula( offset('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), counta('月別元データ'!B1:1)) * 1) ), ARRAYFORMULA(OFFSET('月別元データ'!A1, 1, 1, counta('月別元データ'!A2:A), 1)^0)) )}