Excelは表計算ソフトなので、仕分けして集計するソフトだと言えます。数々の集計機能はありますが、中でもピボットテーブル機能は、最もExcelらしい、Excelの中でも最強の機能だと言えます。
販売システムからダウンロードしたCSVファイルのような元になる販売一覧データから、一瞬にして、商品ごとの集計表を作り上げることができます。
ピボットテーブルは高度な機能と言われますが、実は操作もシンプルで、初心者から使える機能だと思っています。
今回は、初心者向けにピボットテーブルをシンプルに簡単に使う考え方について紹介していきます。
仕分けと集計
左側の色がついているテーブルの販売データから、右の販売方法ごとの数量の合計を求めるにはどうしたらいいでしょうか。
「店頭」の合計金額なら、「店頭」のデータを見て、同じ行の数量を抜き出して、抜き出したものを合算します。
それを「ネット」と「通販」についても実施して完成させます。
この種類ごとに分けることを「仕分け」、合計することを「集計」と呼びます。
この仕分けと集計をするのがExcelを使う本当の目的です。
「集計」の方法は合計だけではありません。場合によっては、平均や最大値、最小値、ばらつきなどを計算することもあります。よくあるのはデータ個数を調べるということもあります。このデータでデータ個数を調べれば、販売回数を見ることができます。
この処理を人間がするとしたら、かなり大変です。できるだけ労力をかけないために、販売方法でテーブルを並べ替えて、1つの販売方法を一か所に集めて合計するという方法であればできそうですが、それが何項目もあると、それなりに時間がかかってしまいます。
そこで、仕分けと集計をする機能である「ピボットテーブル」が必要になります。ピボットテーブルでは、ほんの3秒で集計表を作り上げることができます。
集計表とは
集計表は「仕分け」して「集計」しています。それぞれのセル範囲があります。
この集計表では、A3からA7のセル範囲が仕分け項目、B3からB7のセル範囲が集計値です。
ピボットテーブルで作成する集計表には、大きく分けて3種類あります。
以下の集計表は、「販売方法」ごとの数量の合計の表です。これが「仕分けの方法が1つだけの集計表」です。
次の集計表は、縦方向に「販売方法」が仕分けされています。さらに横方向にも「焙煎方法」でも仕分けされています。
ネット販売の内訳は5行目を見ればわかりますね。
この表は、「仕分け方法が複数項目の集計表」です。
A4からA8のセル範囲とB4からF4が仕分け項目、B5からF8のセル範囲が集計値です。
複数項目なので、次のように販売した「年」を追加したような集計表もあります。
販売方法の中で年ごとに分かれていますね。
Excelは、縦と横の表なので、2項目以上は見づらくなってきます。また、集計表も細かく、大きくなっていくので、集計している意味がなくなってきます。
よって、見ることができる限界は3項目までで、それ以上の集計表は、僕はあまり作成しません。
最後の集計表の形は次のような形です。一見、複数の仕分け項目があるように見えますが、実は仕分けしているのは販売方法のみです。
そのかわり、集計しているのが数量データの個数と数量データの合計値です。つまりこれは「集計が複数ある表」です。
集計が複数ある表で、仕分け項目も複数ある集計表だと次のようになります。
縦方向に販売方法ごとに仕分け、横方向に年ごとに仕分けし、数量のデータ個数、数量の合計を集計しました。
やはり、仕分けも複数、集計も複数になると、だんだん見づらくなり、一目でデータ全体を把握できなくなります。
ピボットテーブルの作成
ピボットテーブルの作成は簡単です。できれば元の表はテーブルにしておくとよいのですが、テーブルにしていないセル範囲でも作成できます。データの1行目には必ず項目名を同じ項目名がないようにして入力しておいてください。
集計する項目に関しては空白セルがないことが望ましいです。仕分けする項目には空白はあってもよいです。
その一覧表のどこかをクリックします。
ピボットテーブルはセルの範囲に新規に入れるものなので、「挿入」タブです。
ピボットテーブルは挿入タブのテーブルグループの「ピボットテーブル」です。ピボットテーブルの絵のボタンをクリックしましょう。「おすすめピボットテーブル」でもいいのですが、思い通りのピボットテーブルが作れない場合があります。
次のようなダイアログボックスが表示されます。元データの確認と、ピボットテーブルの作成場所を指定できます。ピボットテーブルは大きな範囲で作業したほうが楽なので、僕は新しいワークシートに作成します。よほどの理由がない限り、ここはそのままOKボタンをクリックしています。
次のように新しいシートの左にある「ピボットテーブル〇」と書いてある大きな範囲がピボットテーブルの本体です。ここに集計表が出来上がっていきます。
右側には「ピボットテーブルのフィールド」の作業ウィンドウが表示されます。ここを操作してピボットテーブルを設定していきます。ピボットテーブルのフィールドの作業ウィンドウはピボットテーブルを選択している間しか表示しません。もし消えたらピボットテーブルをクリックしましょう。
ピボットテーブルは、それぞれの項目を「配置」することで設定できます。
ピボットテーブルのフィールドの作業ウィンドウに注目すると、項目名が上にあります。その下には4つのボックスがありそれぞれ「フィルター」「列」「行」「値」となっています。この「値」を集計するもので、他の3つは「仕分け」をするものです。それぞれのボックスに上から項目名をドラッグすることで配置できます。
特にどの項目から配置するかという順番は決まっていませんが、おすすめは、まず、集計する値を配置するとわかりやすいです。
上の数量を下のボックスの中の「値」のエリアまでドラッグします。
それだけで数量の総合計が求まります。
今度は、販売方法を「行」にドラッグします。
総合計が、行方向に販売方法で分割されて、それぞれの販売方法ごとに仕分けされた集計表が出来上がります。ここまでほんの一瞬です。
さらに焙煎方法を「列」にドラッグします。
それぞれの販売方法が、列方向に焙煎方法でも分割されて、それぞれの販売方法ごと、それぞれの焙煎方法ごとの集計表が出来上がります。
ピボットテーブルの変更
作成したピボットテーブルは、変更ができます。
列にある焙煎方法を行の販売方法の下に配置します。
販売方法の中で、焙煎方法ごとに集計しています。
その項目での仕分けや集計をやめたいときは、ボックスに配置している項目を、セルの上までドラッグすることで、その項目の仕分けや集計をやめることができます。
販売方法をセル上にドラッグします。
焙煎方法だけの集計表になりました。
複数の項目で集計したい場合は、今ある値のボックスに配置した項目に追加で、値のボックスに集計したい項目を追加で配置します。すでに値のボックスにある項目と同じものを追加することができます。
再度、数量を値に配置します。
数量の合計の集計が追加されました。
これでは意味はないのですが、合計とデータ個数を一度に見たいときは、この状態にしてから、集計の方法を変更します。
集計の方法の変更は、その集計値のいずれかのセルを右クリックし「値の集計方法」の中から集計方法を選択できます。今回は「データの個数」にします。
販売回数と販売数量合計を並べた表を作成できました。
また、右クリックして「計算の種類」を選択することで、値ではなく、比率を表示することもできます。
「列集計に対する比率」にすると、その列の合計値を100％とした比率の表になります。
このように計算方法を変更するといった設定の変更は右クリックでできます。
また、ピボットテーブルを選択していると、リボンに「ピボットテーブル分析」と「デザイン」タブが表示されます。この2つを使ってピボットテーブルを詳細に設定することができます。
ピボットテーブル分析タブでは、ピボットテーブルの詳細の設定、デザインタブではピボットテーブルの表示方法についての設定ができます。
つまり、ピボットテーブルで、追加で何かを操作したいときや操作方法に迷ったときは、右クリックしてみるか、リボンにあるピボットテーブル用のタブをクリックしてみるというのが攻略法です。
ピボットテーブルの活用方法
ピボットテーブルの結果は、以下の数式バーを見るとわかるように計算式ではなく、実際の値として求められています。
そのまま計算式としてどこかにコピーするといった使い方は、できません。
また、ピボットテーブルの値を参照した数式を作成しようとすると、その参照がGETPIVOTDATAという聞きなれない関数になり、使いにくくなります。
ピボットテーブルのデータを元にしたグラフを作成しようとすると、グラフがピボットグラフという種類のグラフになります。
このようにピボットテーブルをそのまま使おうとすると、さまざまな点で使いにくいです。
そこで、ピボットテーブルを別の範囲に貼り付けて使うとよいでしょう。
ピボットテーブル全体をコピーすると、ピボットテーブルとしてコピーされてしまうので、ピボットテーブルの一部をコピーします。
ピボットテーブルの総計を含めずに選択して、コピーすれば、ピボットテーブルではなく、普通のセルとして貼り付けることができます。
ピボットテーブルに必要な前準備
仕入個数ではなく、仕入金額で集計するにはどうしたらいいでしょう。
今回、元の表には、数量しかなく、金額のデータはありませんでした。そのため、金額の集計をすることはできませんでした。
ここで、次のようなそれぞれの商品の単価が書いてある一覧表があれば、それぞれの販売の数量を掛け合わせることで1行ごとに金額を計算できます。
この処理をピボットテーブルで行うには、特別な機能を使わなければなりません。
ピボットテーブルの前に、VLOOKUP関数という2つの表を連結させる関数を使って、販売一覧表に単価を連結し、1行ごとの金額を求めることが先決です。
そのデータを使ってピボットテーブルを作成するのが最も簡単で早い方法です。
なお、ピボットテーブルで商品ごとの個数の合計の集計表を作成後に、それぞれの商品コードごとに単価をかけ合わせる方法でもシンプルです。ただこの方法は商品ごとの集計をしたときに有効で、焙煎方法ごとや販売方法ごとの集計では、あらかじめ元の表に集計用のデータを計算しておく必要があります。
ピボットテーブルの欠点
便利なピボットテーブルなのですが、唯一、欠点があります。しかも仕事の流れを考えると大きな欠点になりえるものです。
普通、Excelでは計算の元になったデータを書き換えれば、計算値が自動的に変わります。
しかし、ピボットテーブルでは、元データを書き換えても、自動的には計算値が変更にならず、更新する作業が必要になります。
データタブの中のすべての更新をすれば済む話なのです。
しかし、今、更新が完了した表が表示されているのか、それとも元データを変えたまま更新はされていない状態なのか、一目見ただけではわかりません。
つまり、更新し忘れが起きてしまう可能性があります。
ピボットテーブルを使う上で、このことは必ず念頭に置かなければいけません。
また、ピボットテーブルは、データの増加にも弱いです。
元にデータに1件足しても、その範囲を元データの範囲には含みません。含めるためにはピボットテーブルを選択し、リボンに表示されるピボットテーブル分析の中のデータソースの変更で、元データ範囲を再設定する必要があります。
この設定も忘れていると、たとえ更新したとしても、あとから追加されたデータは無視され続けるので、非常に危険です。この追加データを自動で含めないことに関しては、元データをテーブルにしてからピボットテーブルを作成すれば問題なくなります。これがピボットテーブルの元データをテーブルにする理由です。
ピボットテーブルに向いている仕事
仕事の流れとして、ピボットテーブルには更新をし忘れる可能性があるということが言えます。
その欠点から考えて、更新しないデータ分析の仕方であれば、安全だと考えられます。
毎日、お客様との取引データが販売一覧表に追加され、その結果を月次報告書に定期的にまとめる自動化もピボットテーブルでは可能です。しかし、データが追加される谷に変更ボタンをクリックしなければなりません。そのような定期的に集計する仕事は、ピボットテーブルは苦手です。
データが追加されたり、入れ替えたりしただけで集計のレポートをするようなシートは、ピボットテーブルよりもSUMIF関数などの集計する関数で計算式を作成しておいて、元データが変わったら自動で再計算される機能で作った方が安全です。
そのような定期的な仕事ではなく、分析が1回だけ、しかも元データは変更がないという分析ならどうでしょう。
例えば、販売方法別に集計しても大きな差や特徴はなかったけど、焙煎方法で仕分けしてみたら特徴があった、ということを発見することもExcelでの仕事です。
このように分類する項目を変更して、特徴を発見していくことはピボットテーブルの得意なところです。
元データはもう先月の売り上げデータを使い、間違いのないようにした時点のデータを操作して現状のなかで面白い仕分け方法を見つける、その傾向を掴んでこれからの販売をどうしていくか考える題材を作る、ということにピボットテーブルは向いています。
このようなどの組み合わせに特徴があるのかはピボットテーブルを作ってみないとわかりません。
このような分析をするときは、次々と分析方法を変えることができるピボットテーブルが有効なのです。
まとめ
ピボットテーブルを初心者向けにもわかりやすく説明しました。ピボットテーブルはExcelがExcelらしい一番の機能なのです。
ピボットテーブルは一般的に初心者向けではないと言われていますが、実際にはその使い勝手の良さから、初心者でも簡単に集計表を作ることができると言えます。
特に元のデータ表が1つの場合で、データを連結する必要がない場合には、関数や複雑な計算式の必要がなく集計表を、項目をドラッグしていくだけで作ることができるので、これほど簡単なことはありません。
複数の表をまとめる場合は、VLOOKUP関数の知識が必要になるでしょう。初心者にとって、それだけが難しいところと言えるでしょう。しかし、実際にVLOOKUP関数もポイントを押さえて役割をきちんと把握すれば、初心者でも扱えるものだと思います。
また、ピボットテーブルは自動で更新されないので、更新作業をし忘れるかもしれないということは、実際にはとても危険なことだと思います。このことから定期的に作成する、月次報告書で使うということはできるだけ避けていきたいです。そういったものは集計関数を組み合わせて作成します。
ピボットテーブルでは、データの分析方法がわからないときや、どの傾向かわからないときに、とにかく数多くのパターンを分析して特徴を見つける必要があって、そのような時に、何度も項目を入れ替えられるメリットで効率的な分析をすることができるのです。
