はじめに
前回はPOIを利用したExcelでの帳票作りをテーマに「納品書」と「月間売上一覧」を作成しました。今回は応用編として、前回作成した「月間売上一覧」に、ピボットテーブルとピボットグラフを追加します。
対象読者
- JavaでExcelファイルを操作したい方
ピボットテーブルの活用
ピボットテーブルの追加はテンプレートとして利用した入力ファイル「inputSalesList.xlsx」に「ピボットテーブル」専用のシートを追加し、ピボットテーブルを設定しておくだけでできます。
ピボットテーブルを活用すればクロス集計が可能になり、例えば、売上データを地域別、期間別、商品別、顧客別、販売担当者別などに集計し、売上傾向の分析することができます。また、ピボットテーブルは、フィルタリングや並べ替えなどの機能も提供しています。
例として「担当者別日別売上金額」を作成してみます。ピボットテーブルを挿入したい箇所のセルを選択後、メニューバーの「挿入」から「ピボットテーブル」を選択します。次に下記ダイアログが表示されますので、分析するデータの参照範囲を指定するため、「テーブル / 範囲」を設定します。
参照範囲として「売上データ」を指定しました。「売上データ」はExcelの「名前の定義」を利用して次のように定義しています。
データの参照範囲には「=OFFSET(月間売上一覧!$A$8,0,0,COUNTA(月間売上一覧!$A$8:$A$1048576),7)」と設定しました。これは「月間売上一覧」シートのセル「A8」を左上の位置として指定し、参照する行の範囲は「COUNTA(月間売上一覧!$A$8:$A$1048576)」と設定することで、A列の8行目から最下行の1048576行目までで、値が空白ではない行としています。そして、列の参照範囲はA列を開始列として7列分（G列まで）を指定しています。このように設定することで、POIを利用して「月間売上一覧」に動的に作成される売上明細一覧のすべてをピボットテーブルの参照範囲として含めることが可能になります。
OKボタンをクリックするとピボットテーブルを操作するための領域が表示されますので、領域内をクリックします。クリックすると、右側に「ピボットテーブルのフィールドリスト」が表示されます。
「担当者別日別売上金額」では「売上日」を行ラベルに、「担当者NO」を列ラベルに、売上金額を「Σ 値」の位置に配置します。デフォルトでは「Σ 値」の集計方法が「データの個数」に設定されているので、ピボットテーブル領域内で右クリック、「データの集計方法」から「合計」を選択します。
行ラベルと列ラベルには、それぞれ「月間売上一覧」シートで値の置換用に設定した変数名「$SALES_DATE[]」と「$EMPLOYEE_CODE[]」が表示されます。行ラベルと列ラベルのプルダウンメニューでそれぞれ昇順に並び替えられるように選択します。集計値には売上金額を表示するので、ピボットテーブル領域内で右クリック、「値フィールドの設定」、「表示形式」から「通貨」を選択します。
次にピボットグラフから参照する時のためにピボットテーブルに名前を付けておきます。ピボットテーブル領域内で右クリック、「ピボットテーブルオプション」、「名前」の箇所に「担当者別日別売上金額」と設定します。また、ファイルを開く時にデータが更新されるように「データ」タブを選択し、「ファイルを開く時にデータを更新する」にチェックを入れておきます。
サンプルデータを利用して月間売上一覧を作成した時の月間売上一覧とピボットテーブルの結果は次のようになります。
なお、ピボットグラフのシート追加に対応するため、前回紹介した「SimpleReportCreator.java」の一部に修正を加えています。試される場合は修正版をダウンロードしてください。
結果を確認すると行ラベルには売上のあった日付が追加されています。一方、列ラベルには、売上の担当者Noが追加されています。列レベルでフィルタを設定してみます。列ラベルのプルダウンメニューから、値フィルタを選択し売上金額の合計が2百万円以上の担当者だけを表示するように設定します。
