今回の記事ではピポットテーブルの値フィールドの設定についてもまとめています。
値フィールドの設定については集計方法と計算の種類を設定できます。
一例で説明すると集計方法については、データの個数をカウントしたいのか、合計を集計したいのか
最小値・最大値を集計することができます。計算の種類に関しては集計した結果の比率や
累計など算出結果を表示することができます。
ぜひ前回の記事と合わせて参考にしてみてください。
前回の記事はこちらから☟
【ピポットテーブル②】活用方法とフィルター・列・行・値の設定・項目の追加方法 ► 独学エクセル塾 (dokugakuexcel.com)
それでは見ていきましょう。
■値フィールドの設定方法について
まずは値フィールドの設定方法について紹介します。
見本データとして過去の記事から使用している果物屋の売り上げのデータを元に説明します。
データと見本のピポットテーブルは下図になります。
今回はこちらのデータとピポットテーブルを元に見ていきましょう。
ピポットテーブルのフィールドは下記の図の通りです。
列はバナナやみかんなどの商品名、行は購入された日時で設定しています。
そして値は購入された合計に設定しています。
では値のフィールドの「合計/個数」の右の「▼」をクリックします。
すると下記のようなメニューが出てきました。
メニューの一番下に「値フィールドの設定」という項目がありますね。
では、こちらを押してみてください。すると下記のようなメニューを表示することができます。
今回はこちらで値フィールドの設定を行っていきます。
それでは設定方法を見ていきましょう。
■集計方法の設定について
ピポットテーブルの値の集計方法は合計やデータの個数、最大値・最小値など
様々な集計方法があります。
それでは1つずつ見ていきましょう。
・合計
こちらは存在するデータの合計を集計してくれます。
下図の見本をご覧ください。
1/18にりんごが２回に分けて売れています。
そして集計方法を売れた個数の「合計」に設定しました。
それではピポットテーブルを見てみましょう。
1/18の結果は「2個と6個」の合計である「8」が出力されています。
・データの個数
こちらは存在するデータの個数を集計してくれます。
下図の見本をご覧ください。
1/18にりんごが２回に分けて売れています。
そして集計方法を売れた個数の「データの個数」に設定しました。
それではピポットテーブルを見てみましょう。
1/18の結果は「りんごのデータが２つあるので」データの個数である「2」が出力されています。
購入した人数もこちらで集計できますね。
・平均
こちらは存在するデータの平均を集計してくれます。
下図の見本をご覧ください。
1/18にりんごが２回に分けて売れています。
そして集計方法を売れた個数の「平均」に設定しました。
それではピポットテーブルを見てみましょう。
1/18の結果は「2個と6個」の平均である「4」が出力されています。
・最大値・最小値
こちらは存在するデータの最大値・最小値を集計してくれます。
下図の見本をご覧ください。
1/18にりんごが２回に分けて売れています。
そして集計方法を売れた個数の「最大値」に設定しました。
それではピポットテーブルを見てみましょう。
1/18の結果は「2個と6個」の最大値である「6」が出力されています。
ちなみに最小値を設定すると、「2個と6個」の最小値である「2」が出力されています。
・積
こちらは存在するデータの積を集計してくれます。
下図の見本をご覧ください。
1/18にりんごが２回に分けて売れています。
そして集計方法を売れた個数の「積」に設定しました。
それではピポットテーブルを見てみましょう。
1/18の結果は「2個と6個」の積である「2*6の12」が出力されています。
・数値の個数
こちらは存在するデータの数値の個数を集計してくれます。
データの個数と似ていますが、データの個数は文字・数値すべての個数で今回の数値の個数は数値のみの個数を集計してくれます。
下図の見本をご覧ください。
1/18にりんごが２回に分けて売れています。
そして集計方法を売れた個数の「数値の個数」に設定しました。
それではピポットテーブルを見てみましょう。
1/18の結果は「2個と6個」の数値の個数の「2」が出力されています。
・標本標準偏差・標準偏差標準偏差・標本分散・分散
こちらは少しコアな集計方法ですが
よく使用する方にとってはありがたいのではないのでしょうか。
標準偏差については下記の記事にまとめてますので是非参考にしてみてください。
【STDEV.P・STDEV.S関数】2つの違いとは？母集団・標本のばらつきを求める標準偏差を求める ► 独学エクセル塾 (dokugakuexcel.com)
■計算の種類の設定について
ピポットテーブルの値の計算の種類は比率・累計・順位など
様々な計算の種類があります。
今回は代表的に使いそうなものを紹介したいと思います。
・総計に対する比率
こちらはデータの総計を分母とした比率を計算してくれます。
下図の見本をご覧ください。
データの総計は右下の「23」になっています。
右側のピポットテーブルは左のピポットテーブルを総計に対する比率で計算した内容になっています。
たとえば1/18のみかんは「4個」で総計が「23」なので、値には「4÷23」の結果である「17.39％」が計算された値で出力される仕組みです。
・列集計に対する比率・行集計に対する比率
こちらは分母が「列の総計・行の総計」の比率で計算する内容になっています。
まずは「列集計に対する比率」を見てみましょう。
下図の見本をご覧ください。
りんごの列の集計結果を見てみましょう。
1/18に「8個」、1/20に「5個」の合計「13個」が列の集計結果となります。
そして右側のピポットテーブルは左のピポットテーブルを列集計に対する比率で計算した内容になっています。
1/18のりんごは「8個」で列集計が「13」なので値には「8÷13」の結果である「61.54％」が計算された値で出力される仕組みです。
次に「行集計に対する比率」も見てみましょう。
1/18の行の集計結果を見てみましょう。
みかんを「4個」、りんごを「8個」の合計「12個」が行の集計結果となります。
そして右側のピポットテーブルは左のピポットテーブルを行集計に対する比率で計算した内容になっています。
1/18のみかんは「4個」で列集計が「12」なので値には「4/12」の結果である「33.33％」が計算された値で出力される仕組みです。
・基準に対する比率
こちらは選択した基準に対する比率を計算する内容になっています。
計算の種類を「基準値に対する比率」で選択すると「基準フィールド」と「基準アイテム」が選択できるようになります。
下図の見本をご覧ください。
フィールドには「日時」や「曜日」などの基準フィールド（項目）が選べるようになり
基準アイテムには「前の値・後の値」のほかに実際にあるデータのアイテムが選択できるようになってますね。
今回は基準フィールドを「日時」に選択して、基準アイテムを「前の値」で選んでみました。
要約すると前日の値と比較した比率を算出するといった計算になります。
では結果のピポットテーブルを見ていきましょう。
1/18は前日がないので100％になってますね。
1/19のみかんは前日の「4個」に対し、「1個」なので「1÷4」の「25％」が出力されています。
基準は選択する項目によって変えれるので目的に合わせて変更してください。
・基準値との差分・基準値との差分の比率
先ほどは基準値に対する比率を説明しましたが。
差を計算して出力することも可能です。
先ほどと同様に基準フィールドを「日時」に選択して、基準アイテムを「前の値」で
「基準値との差分」を選択してみました。
結果はこのようになります。
例で1/19のみかんを見てみましょう。前日の1/18は「4個」売れてますが1/19は「1個」しか売れてません。前日と比較して「-3個」なので「-3」が出力されてます。
この流れで「基準値との差分の比率」での結果も見てみましょう。
1/19のみかんは前日の1/18はと比較すると「75％減少している」ので
「-75％」が計算結果として表れています。
変化量に注目して確認したい場合にはとても便利な機能です。
・累計・比率の累計
先ほどは差を説明しましたが、今度は累計になっています。
累計は基準フィールドを選択して、そのフィールドに対しての累計の処理となります。
今回も日時を選択して計算してみましょう。
結果はこのようになります。
メロンを例に見ていきましょう。
1/18では「2個」、1/19でも「2個」です。累計なので前日分の「2個」をプラスして「4」が計算されます。
さらに1/20は「4個」で前日の累計分の「4」がプラスされ「8」が出力されていますね。
では「比率の累計」での結果も見てみましょう。
総計を分母とした累計の比率が表示されています。
傾向で確認したい場合にはとても便利ですね。
・昇順での順位・降順での順位
こちらは文字通り順位を計算結果として出力される機能です。
順位は基準フィールドを選択して、そのフィールドに対しての累計の処理となります。
今回も「日時」を基準フィールドとして計算していきます。
では結果のピポットテーブルを見ていきましょう。
今回は「昇順での順位」で表示しています。
昇順なので「小さいものが順位が上」になりますね。
ではりんごを例に見ていきましょう。
1/18～1/20のりんごの数を比較すると、1番小さいのは1個の「1/19」、2番目は8個の「1/18」。
3番目は10個の「1/20」になってます。
なので順位は「1/19」が「1」、「1/18」が「2」、「1/20」が「3」となります。
今回は昇順でしたが降順で計算すると逆になりますね。
■まとめ
いかがだったでしょうか。
値フィールドの設定には集計方法と計算の種類が設定できますが
各項目内でも様々な設定ができるので
複雑なピポットテーブルの算出が可能となります。
今回は値フィールドの設定でしたが次回は列・行ラベルの並び替えについて説明していきます。
列・行ラベルの並び替えについてはこちらから☟
それでは次回の記事でお会いしましょう。