.NET/DioDocs/Excel

ワークシートのデータをグループ化して集計する

by MESCIUS-dev

ワークシートに記載されている売上データから、「担当者」や「商品」別に集計したい、そのようなExcelの集計作業において従来はSUMIFS関数を使っていたケースが多いのではないでしょうか。

例えば以下のようなデータに対して「担当者」ごとにグループ化して集計したい場合、

サンプルデータ

以下のように担当者の人数分だけそれぞれ式を用意する必要があります。

  • 佐藤：=SUMIFS(Sales[売上金額], Sales[担当者], “佐藤”)
  • 鈴木：=SUMIFS(Sales[売上金額], Sales[担当者], “鈴木”)
  • 田中：=SUMIFS(Sales[売上金額], Sales[担当者], “田中”)
SUMIFS関数

さらに、「担当者」と「商品」ごとにグループ化して集計する場合はより複雑です。以下のように担当者と商品の組み合わせの数だけそれぞれ式を用意する必要があります。

  • 佐藤×A商品：=SUMIFS(Sales[売上金額], Sales[担当者], “佐藤”, Sales[商品], “A商品”)
  • 佐藤×B商品：=SUMIFS(Sales[売上金額], Sales[担当者], “佐藤”, Sales[商品], “B商品”)
  • 佐藤×C商品：=SUMIFS(Sales[売上金額], Sales[担当者], “佐藤”, Sales[商品], “C商品”)
  • 鈴木×A商品：=SUMIFS(Sales[売上金額], Sales[担当者], “鈴木”, Sales[商品], “A商品”)
  • 鈴木×B商品：=SUMIFS(Sales[売上金額], Sales[担当者], “鈴木”, Sales[商品], “B商品”)
  • 鈴木×C商品：=SUMIFS(Sales[売上金額], Sales[担当者], “鈴木”, Sales[商品], “C商品”)
  • 田中×A商品：=SUMIFS(Sales[売上金額], Sales[担当者], “田中”, Sales[商品], “A商品”)
  • 田中×B商品：=SUMIFS(Sales[売上金額], Sales[担当者], “田中”, Sales[商品], “B商品”)
  • 田中×C商品：=SUMIFS(Sales[売上金額], Sales[担当者], “田中”, Sales[商品], “C商品”)
SUMIFS関数

このようにデータが増えて式に追加する組み合わせ条件が増えていくにつれて、数式のメンテナンス作業が膨大な量になりがちですが、Excelではこのようなデータ集計の煩雑さを軽減してくれる関数として「GROUPBY関数」と「PIVOTBY関数」が2024年10月に追加されています。

GROUPBY関数とPIVOTBY関数

データに対して「担当者」ごとにグループ化して集計したい場合、GROUPBY関数を使用すると以下のようにデータを集計するための式が1つだけで完了します。式を記載したセルを起点に自動で集計結果の表が作成されます。

GROUPBY関数

さらに、「担当者」と「商品」ごとにグループ化して集計する場合は、PIVOTBY関数を使用すると以下のようにデータを集計するための式が1つだけで完了します。GROUPBY関数と同じようにPIVOTBY関数でも式を記載したセルを起点に自動で集計結果の表が作成されます。

PIVOTBY関数

DioDocs for ExcelでもGROUPBY関数とPIVOTBY関数をサポート

「DioDocs for Excel（ディオドック）」では最新バージョンの「V8J SP2」において、このGROUPBY関数とPIVOTBY関数に対応しています。

DioDocs for Excelでは、Formula2プロパティを使用して動的配列数式（ワークシートのセル範囲に複数の結果を配列として返す数式）を設定することができます。こちらを使用してGROUPBY関数とPIVOTBY関数を実装します。Excelと同じようにテーブルの構造化参照を使用することができるので、関数の引数には「Sales[担当者]」や「Sales[商品]」のようにテーブル名と列名を設定することが可能です。

using GrapeCity.Documents.Excel;

var workbook = new Workbook();
workbook.Open("sample-data.xlsx");
var worksheet = workbook.ActiveSheet;

worksheet.Range["F2"].Value = "GROUPBY";
worksheet.Range["F3"].Formula2 = "=GROUPBY(Sales[担当者], Sales[売上金額], SUM, 0, 0, -2)";

worksheet.Range["F7"].Value = "PIVOTBY";
worksheet.Range["F8"].Formula2 = "=PIVOTBY(Sales[担当者], Sales[商品], Sales[売上金額], SUM, 0, 0, , 0)";

workbook.Save("result.xlsx");

こちらのコードを実行すると以下のようにワークシートでGROUPBY関数とPIVOTBY関数の実行結果が表示されます。

DioDocs for ExcelでもGROUPBY関数とPIVOTBY関数をサポート

今回紹介した機能については以下のデモで動作を確認いただけます。

DioDocsの最新バージョン「V8J SP2」の新機能については、以下のページで紹介していますのでこちらも是非ご覧ください。

さいごに

弊社Webサイトでは、製品の機能を気軽に試せるデモアプリケーションやトライアル版も公開していますので、こちらもご確認いただければと思います。

また、ご導入前の製品に関するご相談やご導入後の各種サービスに関するご質問など、お気軽にお問合せください。

＼  この記事をシェアする  ／