GoogleスプレッドシートのピボットテーブルをGASで自動集計

GoogleスプレッドシートのピボットテーブルをGASで自動集計プログラミング
スポンサーリンク

投資成果の確認のため評価損益の記録は、Googleアカウントがあれば無料で使用できるGoogleスプレッドシートをおすすめします

記録したデータはGoogleスプレッドシートのピボットテーブルを使うと見やすく集計できます。

ピボットテーブルをGoogle Apps Script(GAS)で自動化できるとさらに利便ですよね。

というこで、今回はGASでピボットテーブルを操作するコードをご紹介します

評価損益のデータ記録の自動化はこちらをご覧ください。

スポンサーリンク

GoogleスプレッドシートのピボットテーブルをGASで自動集計

ピボットテーブルの構成要素は次の5つがあります。

  • 集計元のデータ範囲とピボットテーブルの表示場所
  • 行の項目
  • 列の項目
  • 集計する値
  • フィルタ(抽出条件)

これらを指定することで、集計は自由自在です。

GASでの指定方法を説明していきます。

ピボットテーブルをGASで操作するコード

スプレッドシート名「pivot」の「シート1」に以下のデータがあり、「シート2」にピボットテーブルで集計するコードを紹介します。

スプレッドシート名:pivot
シート名:シート1

ABCDE
1日付銘柄取得価格評価額評価損益
21/1海外株式10,00012,0002,000
31/1国内株式9,00010,0001,000
42/1海外株式11,00012,0001,000
52/1国内株式8,00010,0002,000
63/1海外株式7,00010,0003,000
73/1国内株式11,00012,0001,000

集計後のピボットテーブル

海外株式国内株式
日付取得価格評価額評価損益取得価格評価額評価損益
3/17,00010,0003,00011,00012,0001,000
2/111,00012,0001,0008,00010,0002,000
1/110,00012,0002,0009,00010,0001,000

ピボットテーブルで集計するコードです。

//-----------------------------------------------------------------------
//集計元のデータとピボットテーブルの表示場所を設定
//-----------------------------------------------------------------------
//ピボットテーブルを表示するシートを指定
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var PivotSheet = spreadsheet.getSheetByName('シート2');

//集計もとデータの範囲を指定
var sourceData = PivotSheet.getRange('シート1!A1:E7');

//ピボットテーブルの作成
var pivotTable = PivotSheet.getRange('A1').createPivotTable(sourceData);

//-----------------------------------------------------------------------
//行の項目を設定
//-----------------------------------------------------------------------
//集計もとデータの1から数えて何列目かを数値で指定
var pivotGroup = pivotTable.addRowGroup(1);//日付
pivotGroup.showTotals(false).sortDescending();//合計なし、降順

//-----------------------------------------------------------------------
//列の項目を設定
//-----------------------------------------------------------------------
//集計もとデータの1から数えて何列目かを数値で指定
pivotGroup = pivotTable.addColumnGroup(2);//銘柄
pivotGroup.showTotals(false).sortAscending();//合計なし、降順

//-----------------------------------------------------------------------
//集計する値の項目を設定
//-----------------------------------------------------------------------
//集計もとデータの1から数えて何列目かを数値で指定
//PivotTableSummarizeFunctionの後に集計したいものを指定
//SUMは合計、AVERAGEは平均、MAXは最大など
var pivotValue = pivotTable.addPivotValue(3, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('取得価格');
var pivotValue = pivotTable.addPivotValue(4, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('評価額');
var pivotValue = pivotTable.addPivotValue(5, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('評価損益');

詳しく説明していきます。

集計元のデータとピボットテーブルの表示場所を設定

//ピボットテーブルを表示するシートを指定
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var PivotSheet = spreadsheet.getSheetByName('シート2');

//集計元のデータの範囲を指定
var sourceData = PivotSheet.getRange('シート1!A1:E7');

//ピボットテーブルの作成
var pivotTable = PivotSheet.getRange('A1').createPivotTable(sourceData);

コード中のコメントの通りです。

ピボットテーブルを表示するシートを指定して、集計元のデータ範囲を指定しピボットテーブルを作成します。

行の項目を設定

//-----------------------------------------------------------------------
//行の項目を設定
//-----------------------------------------------------------------------
//集計元データの1から数えて何列目かを数値で指定
var pivotGroup = pivotTable.addRowGroup(1);
pivotGroup.showTotals(false).sortDescending();//合計なし、降順

//複数指定する場合は同じ指定を繰り返す
pivotGroup = pivotTable.addRowGroup(2);//1から数えて何列目かを指定
pivotGroup.showTotals(true).sortAscending();//合計あり、昇順

行の項目の設定は、addRowGroup(数値)で行います。

引数の数値は集計元のデータを左から数えた列数を指定します。

今回は日付を行の項目にしていしているので引数は「1」です。

項目は総計の表示・並び替えを指定できます。

showTotals(false)で総計の非表示、trueで表示。

sortAscending()は昇順、sortDescending()が降順です。

同じ指定を繰り返すと、列の項目を追加できます。

列の項目を設定

//-----------------------------------------------------------------------
//列の項目を設定
//-----------------------------------------------------------------------
//集計もとデータの1から数えて何列目かを数値で指定
pivotGroup = pivotTable.addColumnGroup(2);
pivotGroup.showTotals(true).sortAscending();//合計なし、降順

列の項目の設定も「行の設定」とほぼ同じ。

列の項目の設定は、addColumnGroup(数値)で行います。

引数の数値は集計元のデータを左から数えた列数を指定するのは「行の項目」と同じ。

項目の総計の表示・並び替えの指定も同じです。

値の項目を設定

//-----------------------------------------------------------------------
//集計する値の項目を設定
//-----------------------------------------------------------------------
//集計元データの1から数えて何列目かを数値で指定
//PivotTableSummarizeFunctionの後に集計したいものを指定
//SUMは合計、AVERAGEは平均、MAXは最大など
var pivotValue = pivotTable.addPivotValue(3, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('取得価格');
pivotValue = pivotTable.addPivotValue(4, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('評価額');
pivotValue = pivotTable.addPivotValue(5, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue.setDisplayName('評価損益');

値の設定は次の通り。

addPivotValue(数値, SpreadsheetApp.PivotTableSummarizeFunction.集計内容)

数値は行・列と同じで集計元データの列数。

集計内容は合計は「SUM」平均は「AVERAGE」最大値は「MAX」など

表示名称を変更するにはsetDisplayName(表示名)。

今回は値を3つ指定しているので同じ設定を繰り返します。

フィルタ(抽出条件)の設定

//setVisibleValuesでフィルタで抽出したい文字を指定
//addFilterは集計元データの1から数えて何列目かを数値で指定
var criteria = SpreadsheetApp.newFilterCriteria()
criteria = SpreadsheetApp.newFilterCriteria().setVisibleValues(['抽出する項目']).build();
pivotTable.addFilter(3, criteria);

今回の例では設定していませんがフィルタの設定もご紹介します。

setVisibleValues(['抽出する項目'])でフィルタで抽出する項目を指定できます。

addFilter(数値, criteria)で集計元データの列数を数値で指定。

最後に

ピボットテーブルをGASで操作するコードを紹介しました。

このコードを応用するとさまざまな集計が可能になります。

Googleスプレッドシートでの運用成果の記録・集計の手助けになれば嬉しいです。

GASで運用成果の記録は、こちらをご覧ください。

 

よりGASを学ぶならテックアカデミーがおすすめです。

まずは無料体験から。

プログラミング
スポンサーリンク
\ Follow Me /
Yoshiの資産運用記

コメント

タイトルとURLをコピーしました