ピボットテーブル
データを一瞬にして集計してくれるピボットテーブル２
以前、ピボットテーブルの作り方について記事にしました。
今回は、そのピボットテーブルについてもう少し深堀りしてた機能や、元データの管理方法について書きたいと思います！よく使うものなのでマスターすると何かと便利だと思います！
自作のフィールドを作る
ピボットテーブルで集計をしていると、元データにはない集計項目が欲しくなることがあります。そんなときのためにピボットテーブルには、自作の計算フィールドを作る機能が用意されています。早速みていきましょう。
サンプルデータはこちらです。※日毎の売上のデータとします。
担当ごとに売上データを集計するピボットテーブルを作成します。
こんな感じですね。（行に担当を入れて、値に１日目～５日目をいれます）
普通は、合計が欲しくなりますよね？でも元データには合計の列はありません。こんな時に自作のフィールドが作れると便利です。
やってみましょう。
「ピボットテーブル分析」→「フィールド/アイテム/セット」→「集計フィールド」の順に進みます。
こんなのが出てきます。
「名前」に「売上合計」と入れます。※新しく集計する項目名になります。何でも良いですが、分かりやすい名前をつけましょう。
「数式」には、集計したい計算式を入れます。今回は、１日目から５日目までを足した合計が欲しいので、一個ずつ足すことにします。下のフィールドのボックスの項目をダブルクリックすると、数式のところにその項目が入ります。プラスなどの記号は自分で入力しましょう。こんな感じです。
「追加」→「OK」と進みます。
こんな感じで出ましたね。
このように、今あるフィールドを使って新しいフィールドを作成することが出来ます。便利な機能なのでぜひ使って下さい！ちなみに、作ったフィールを使って新しいフィールドを作ることも可能です。元データを増やすことなく集計フィールを作成出来るので、元データのデータ量も抑えることが出来ます！
日付をグループ化する
次に、グループ化について説明します。日付が入っているデータを集計するのは、基本的には月間や年間ですよね。元データに日付が入っていれば簡単にまとめることが出来ます。では見ていきましょう。
こんなデータを用意しました。2020年1月から3月までの日別売上データです。
これを、ピボットテーブルで月ごとのデータにまとめてみましょう。
日付を列に入れて、売上を値に入れて、担当を行に入れる。
？？？こっからグループ化の説明しようと思ってたんですが、最近のExcelはこれだけで月間の集計が行われるみたいですね。。。笑
せっかくなんで、古いExcelお使いの方のために、グループ化処理の説明です。こんな風になってる方もいるはずです。
列ラベルに日付が死ぬほど並びます。こいつをまとめます。
まずは、列ラベルの日付のところで右クリックして出てくるメニューの「グループ化」を選択します。※出来ない！って怒られる場合は、元データの日付欄の形式が日付になってない可能性があります。
すると、こんなんが出てくるのでまとめたい単位を選択して「OK」します。
すると、このように列ラベルに月の表示が追加され、ピボットテーブルのフィールドにも月のフィールドが追加されます。
月の横にある「-」ボタンを押すことによって、月ごとの合計数字が表示されます。
他にも、「四半期」や「年」、「秒」「分」「時」などの単位でグループ化することが可能です。
カッコいい見た目にしたり、見やすくしたり変更する。
ピボットテーブルの見た目を変更する方法です。細かく自分で指定も出来ますが、大体のものはボタン一つで変更するデザインの機能で十分だと思います。見た目をちょっと変えてみて、いつもの味気ないデータを少しスタイリッシュに変更することが可能です。また、小計や総計の表示、レイアウトの変更などもすることができます。
変更は、デザインタブで行います。
まずは、レイアウト・ピボットテーブルスタイル・ピボットテーブルスタイルのオプションと大きく3つの機能があります。ここでは、よく使うレイアウトとピボットテーブルスタイルについて見ていきたいと思います。
■まずは、デザインを変える「ピボットテーブル スタイル」です。
ピボットテーブルスタイルの右下の「▼」を押すと、規定で用意されているデザインが沢山出てきます。
試しに、一つ選んでみましょう。
こんな感じでデザインが変わります。他にも色々あるのでお好みのデザインを探して下さい。
次に、ピボットテーブルの体裁を変える「レイアウト」です。レイアウトの中には「小計」「総計」「レポートのレイアウト」「空白行」の項目があります。一つずつ見ていきましょう。
＊小計
「小計」のメニューを表示して、試しに「全ての小計をグループの末尾に表示する」を押してみて下さい。
月々にまとめている状態では、変化はないと思いますが、1月の横にある「+」ボタンを押すと次のような形で、1月の集計が表示されているのが分かります。これが小計です。
＊総計
次は「総計」のメニューです。「総計」は、ピボットテーブルを作成した時にデフォルトで出ています。
ですのでメニューを開いて、「列と行の集計は行わない」を選択し、総計を消して見ましょう。こちらです。
■次はレポートのレイアウトを見ていきましょう。
レイアウトの変更の機能です。分かりやすいように、行のボックスに担当名に加えて、会社名を入れます。図の様に担当名と会社名ごとの売上数字が集計されます。コレを使って見ていきましょう。
それでは、「レポートのレイアウト」のメニューを見てみましょう。「コンパクト形式で表示」「アウトライン形式で表示」「表形式で表示」の3つの表示方法と、「アイテムのラベルを全て繰り返す」「アイテムのラベルを全て繰り返さない」のラベルの表示方法が2つあります。まずは表示形式として、「表形式で表示」を選択して下さい。※デフォルトは「コンパクト形式で表示する」になっています。
図のように、担当と会社名の列が別々になりました。これが表形式です。
このように、ピボットテーブルの見え方を変更することが出来ます。このまま次に「アイテムラベルを全て繰り返す」を押してみます。
図のように、省略されていたアイテムラベルが表示されます。
このように、必要に応じてラベルを表示したりしなかったり出来ます。
まとめ
このように、用途に合わせて簡単に形を変えることが出来るのが、ピボットテーブルの魅力です。元のデータをどんどん追加するだけで最新の集計に反映される。使えるようになると、今までいちいち集計していたデータ整理の作業が一瞬にして終わります。
オススメの本
もっと勉強したい方！ピボットテーブルだけの本があります！
コメント