ピボットテーブルの使い方 Excel(エクセル)の集計、値の並び替え、集計方法、集計方法の変更、フィルタ機能、ピボットグラフの作り方など
Excel(エクセル)には様々な集計機能がありますが、その中でも最も強力なピボットテーブル機能を、操作方法を交えてわかりやすく紹介します。
ピボットテーブルを使うメリットは何といっても集計の速さです。
件数の多いデータに適しております。
普通にExcelで表を作って集計のための関数や計算式を入力したりフィルタ機能のオン、オフを個別に行わないといけないという工程を大幅にショートカットできるという点が、ピボットテーブルを使うメリットといえます。
ピボットテーブルは、一見難しそうに感じるかもしれませんが、実はとても簡単です。
ピボットテーブルの使い方 Excel(エクセル)値の集計、並び替え、集計方法
まずは下記のような表を作成してみましょう。このデータは、２行目から販売日、商品、部署、金額の売上を記録した一覧表です。一覧表の1行目にそれぞれの項目名が記録されています。
全部で１年間分の797件の売上データです。
ピボットテーブルを使う時は、①表データのどこかをクリックして、②［挿入］タブの中のピボットテーブルをクリックします。
↓下記のようなダイアログボックスが表示されます。
シートが追加され、↓の画面に切り替わります。
シートの右側に［ピボットテーブルのフィールドリスト］の作業ウィンドウが表示されます。
この作業ウィンドウを使ってピボットテーブルを設定していきます。
上には、項目名の一覧が表示されています。
下には、フィルター、列、行、値の欄があります。
上の項目名を、下の4つのエリアにドラッグして配置することで集計方法を設定します。
今回は、売上金額を集計しますので、［金額］項目が値の集計対象になります。
［ピボットテーブルのフィールド］作業ウィンドウの中の項目名［金額］をドラッグして、下の［値］欄にドラッグします。
すると全ての合計金額がまず表示されます。
今はすべての金額の集計しか求まっていませんが、商品ごとや、日付ごとにも集計できます。
商品ごとに縦（行方向）に集計したいときは、［ピボットテーブルのフィールド］作業ウィンドウの中の項目名［商品］をドラッグして、下の［行］欄にドラッグします。
↓商品名ごとの金額の集計が追加されました。
さらに部署ごとに売り上げを集計したい場合は、部署の項目を列ラベルへドラッグします。
下記のようなテーブル表が出来上がります。
これで【どの商品がどの店舗でどれくらい売れたのか】と【各店舗ごとの売り上げ】、【商品ごとの総売り上げ】を集計することができました。
商品ごとに行に、さらに部署ごとに列にまとまっている一覧を表示できました。
例えば、部署ごとに商品を並べてみたいという時は、［行］または［列］に両方の項目を入れると良いでしょう。
［列］欄にある［部署］を［行］欄の［商品］の下にドラッグします。
下記のような表に出来上がります。
商品ごとにどの店舗で売れたのかがわかる表が出来上がります。
このように行や列に複数の項目を配置することもできます。
↑の図のように列ラベルに【商品】行ラベルに【販売日】をドラッグすれば日付けごとに商品の売り上げを集計することもできます。
この様に、任意の項目を【値】【列ラベル】【行ラベル】の欄にドラッグするという操作だけで大量のデータを集計できることがピボットテーブルの基本的な使い方であり機能です。
大量の一覧データを見ただけでは、物事の本質が見えてこないのです。
そこで、例えば月ごとにデータを集計することで、毎月のデータの傾向を掴むことができます。
さらに商品ごと、販売部署ごとに集計すれば、また、合計値ではなく平均値、最大値ではどうか、様々な集計が一瞬でできます。
これだけのスピードで分析ができるので、気づけないことに気付けるチャンスが増えます。
単なる集計をするだけでも優秀ですが、簡単に作り替えられるのもピボットテーブルの大きなメリットなのです。
このような縦横集計のことをクロス集計と呼びます。
ピボットテーブルの機能には他にもフィルタ機能や平均値を求めたりする機能もありますし、ピボットグラフという機能も付随しています。
奥が深い機能ですが、一度使ってみて、あるいは使い方を理解しておいて損は無い機能です。
集計方法の変更
求められる値は合計値だけではありません。
基本は合計値に設定してありますが、合計値が算出されている項目をドラッグして数値の上で右クリックをしてみると
【値の集計方法】から平均値や最大値が求めることが出来るようになっています。平均値を選択すればこの表の場合は日ごとに何がどれくらい売れたかの合計から日ごとに平均いくら売れているのかがわかる表にあっという間に早変わりします。
右クリックして集計方法を合計値から平均値に切り替えて選択するだけで表の計算方法がすぐ変わるというメリットは表が扱うデータが大量であればあるほどその便利さ、ありがたみがわかる仕組みです。
集計方法の変更はセルごとに個別にも変えられますし、複数でも可能です。
フィルタ機能の活用
次は、またまた便利な【フィルタ機能】について解説していきます。
【列ラベル】あるいは【行ラベル】の横の下向き矢印をクリックします。
今回は列ラベルでいきます。
すると↓のようにダイアログボックスが表示されます。
サーバー、タブレット等の商品名の左にチェックボックスがあります。
チェックボックスをクリックしてレ点を付けたり外したりすることでその項目を表から除外したり、あるいは挿入しなおしたりできます。
すると、
合計値を求めたいけど、サーバーの売り上げは除外して計算したい、等という時にこのフィルタ機能が役にたちます。
また、レポートフィールドを使うことでもフィルタ機能と同じ働きを得ることができます。
すると、
画面左上に部署の項目が現れ左端にプルダウンの矢印がありますのでクリック。
ダイアログボックスが表示されます。
ここでは丸の内店を選択します。
丸の内店だけの売上表になります。
このようにピボットテーブルを使えば大量のデータを扱っていても求めたい計算、求めたい数値が一括で簡単な操作だけですぐに得ることが出来ます。
ピボットグラフ
グラフも簡単に挿入することができます。
ダイアログボックスが表示されます。
任意のグラフを選んでOK押下↓
表と連動したグラフが完成。
列と行の要素をドラッグで入れ替えたり、フィルタ機能で要素を外したりするだけでグラフの表示もすぐに切り替わるという手軽さがあります。
ピボットテーブルは仕組みと操作のコツさえつかめば、作業効率や作業時間が大幅に節約できる便利な機能です。
ぜひご活用ください。