前回まではピポットテーブルの基礎知識についてまとめてきました。
ピポットテーブルでは上位○○位や下位○○位のデータを出力したり
値の降順・昇順でまとめれる機能がありましたね。
データが多ければ行数も増えるため、値が小さい項目はまとめたい所です。
しかしピポットテーブルの通常の機能にはその他の項目は存在しません。
それでも関数を合わせることで同じ機能を持たせることができます。
使う関数も簡単なのでぜひ活用してみてください。
それでは見ていきましょう。
■見本のピポットテーブルについて
今回使用する見本のデータとピポットテーブルを見てみましょう。
見本は下図になります。
項目としては「日時」「曜日」「商品」「単価」「個数」「合計金額」になります。
このデータをピポットテーブルに落とし込んだのが右側になります。
では実際にその他を出してみましょう。
■ピポットテーブルでその他を表示する
では実際にその他を表示させてみましょう。
手順は以下の通りです。
①行の統計付きで並び替えたい順に並び替える
②その他以外の項目を表示させる
③その他の項目を作成する
では１つずつ見ていきましょう。
・①行の統計付きで並び替えたい順に並び替える
まず行の統計付きで並び替えたい順に並び替えましょう。
なぜ行の統計付きなのかというと③のその他を計算するときに必要になります。
そして今回は個数の合計の降順で並び替えてみました。
合計の大きい順ですね。
並び替えする方法についてはこちらから☟
【ピポットテーブル④】行・列ラベルの降順・昇順・その他の並び替えオプション・手動の並び替えについて ► 独学エクセル塾 (dokugakuexcel.com)
なので今回は個数が多い上位○○位を表示させて、それ以外をその他で表示します。
では次のステップです。
・②その他以外の項目を表示させる
次にその他以外の項目を表示させたいと思います。
問題はどこに表示させるのかですが、表示させるのはピポットテーブルでなく、通常のセルになります。
では見本を見ていきましょう。
まず項目を表示させます。
参照させるのはピポットテーブルの項目のセル番地を「＝」で参照します。
フィルダウンで全項目参照してください。
今度は上位○○位の表示についてです。
今回は上位5位までは表示させるようにしましょう。
参照先は先ほどと同様にピポットテーブルから参照します。
注意点としてピポットテーブル内のデータを「＝」からクリックで参照しようとすると
下図のような参照式になってしまいます。
これではピポットテーブルの変化に対応できなくなるので「＝AF6」といった
セルの番地で参照するようにしてください。
あとはその他の項目を作成するだけです。
・③その他の項目を作成する
最後にその他を追加してみましょう。
まず手入力で最終行に「その他」を打ち込みます。
こちらは任意なのでどのような文字でも構いません。
では、バナナのその他を求めてみましょう。
入力する関数は下記の通りです。
=MAX(AF:AF)-SUM(AM6:AM10)
では仕組みを紹介します。
まずMAX関数で「MAX(AF:AF)」のように対象の行の最大値を求めます。
MAX関数についてはこちらから☟
【MAX関数】エクセルで範囲を指定して、数値の最大値を求める方法について ► 独学エクセル塾 (dokugakuexcel.com)
ここでの最大値は統計の値になりますので「31」となりますね。
後はこの「31」から「SUM(対象範囲)」でその他以外の合計を引けばその他の項目の完成です。
SUM関数についてはこちらから☟
【SUM関数】エクセルで範囲を選択し、合計（総和）を関数を使用して求める方法 ► 独学エクセル塾 (dokugakuexcel.com)
今回のその他以外の項目は「AM6からAM10」なので「SUM(AM6:AM10)」を引きます。
結果はこちら。
バナナの項目のその他ができました。
後はフィルダウンで全てのその他を表示させてみましょう。
ついでに見栄えを良くしましたがこれでその他の完成です。
■動作確認
この方法は実際にピポットテーブルで表示していないので
スライサーやフィルターの機能を満たしていないように思えますが参照しているのはピポットテーブルのセル番地なのでピポットテーブルに合わせて変化してくれます。
■注意点
・統計の最大値の算出について
この方法では統計値のMAX値を列参照で求めていました。
しかし同列に別のピポットテーブルまたはデータがあった場合、もしそちらの数値が大きい場合は
最大値が変化してしまい、算出結果にずれが生じてしまいます。
なので参照範囲をカスタマイズするか、同列にデータが無いように管理する必要があります。
・フィルターによる項目数の変化について
先ほどの見本では上位5位を表示させ、それ以外をその他としました。
しかし、フィルターで絞り込んだデータが５つ以下の場合、項目がずれてしまいます。
なので項目の数が変化するリスクがある場合は「IF関数」などで「参照データが無ければ空白」にしたり、
条件付き書式でデータが無ければ、塗りつぶしで見えなくするといった工夫が必要になります。
IF関数についてはこちらから☟
【IF関数】エクセルで○○だったら△△を表示する～条件分岐を身に着ける～ ► 独学エクセル塾 (dokugakuexcel.com)
条件付き書式についてはこちらから☟
【条件付き書式】必見！使用方法や設定方法を詳しく説明～ルールの追加・編集・削除・優先順位～ ► 独学エクセル塾 (dokugakuexcel.com)
とはいいつつも、項目が増えることはないのでその他の算出はできます。
■まとめ
いかがだったでしょうか。
現時点でピポットテーブルにその他の機能は存在しませんが
本記事でその他の表示が可能になります。
フィルターの変化にも対応できるので是非参考にしてみてください。
それでは次回の記事でお会いしましょう。
コメント