今回は、自動的に更新される動的ピボットテーブルの作成方法を解説します。そもそもピボットテーブルがわからない方は以下の記事を参照ください。
動的ピボットテーブルとは何か
そもそもここで言う動的ピボットテーブルとは何でしょうか？通常のピボットテーブルの場合、元データが更新された場合、それをクロス集計に反映させるには”更新”ボタンを都度押す必要があります。自動的に元データの更新内容を反映することはできません。
それに対して、今回作成する動的ピボットテーブルの場合は、元データが更新された場合にその内容をクロス集計にもリアルタイムに反映することができます。
詳細は後述しますが、これはOfficeの新機能であるスピル機能と、ワークシート関数であるUNIQUE関数及びTRANSPOSE関数などを用いて実現することができます。
そもそもスピルとは何かがわからないと後半の説明が理解できないと思いますので、ご存じない方は以下の記事を参照ください。
動的ピボットテーブルの作成方法
では具体的に作成方法を紹介します。今回使用する元データは以下のテーブルです。
なぜテーブルでないといけないかはUNIQUE関数の記事で説明していますのでご参照ください。
では、別シートでクロス集計表を作成します。まずはUNIQUE関数を使って行ラベルを作成します。
次に、UNIQUE関数とTRANSPOSE関数を組み合わせて列ラベルを作成します。
TRANSPOSE関数については以下を参照ください。
最後に、集計します。集計方法は様々ありますが、例えば以下では販売価格を合計するためにSUMIFS関数を設定しています。これで、[商品名]x[担当者]ごとに販売価格を集計することができています。
なおSUMIFS関数については以下を参照ください。
これで作成完了です。
自動更新の確認
では作成したクロス集計表が自動更新されるかを確認します。元データのテーブルに、以下のように4行追加します。
すると、何も操作していないのにクロス集計表も更新されているのがわかります。
比較用に、更新前のクロス集計表は以下です。
SUMIFS以外の集計方法
上ではSUMIFS関数を使って合計数を集計しましたが、他にもデータ数を数えたり、平均を計算することもできます。例えばCOUNTIFSを使うと、以下のようにデータ数を数えることができます。
COUNTIFS関数については以下から参照ください。
またAVERAGEIFS関数を使うと以下のように平均を算出することができます。
コメント