あなたはExcelの「ピボットテーブル」を使えていますか？
「Excelにピボットテーブルは便利だとは聞くけれど、どう使ったらいいか分からない」
「Excelの関数も十分に使えていないのに、ピボットテーブルなんて使いこなせない」
とあきらめていませんか？
ピボットテーブルは、難しい関数が不要で、マウスの操作だけで簡単にデータを分析ができる便利なツールです。
もくじ
この記事を書いているぼくは、以前もピボットテーブルの存在は聞いていましたが、なかなかうまく使いこなすことができませんでした。
しかし、約15年前にMOS Excelの学習を通じて、ようやくピボットテーブルを使えるようになりました。
また、ピボットテーブルの習得がきっかけで、Excelスキルだけでなく、Accessなどデータベースの習得にも非常に役立ったと実感しています。
つまり、ピボットテーブルを使いこなせることが他のITスキルを伸ばす助けにもなる、とも言えるでしょう。
本記事では、ピボットテーブルの未経験者にもイメージできるよう、メリットや使い方、初心者が陥りがちな問題についてシンプルにまとめました。
この機会にピボットテーブルに挑戦してみましょう！
ピボットテーブルをマスターすべき３つの理由
ピボットテーブルが初心者でも使える理由として、以下の３つを挙げました。
- マウス操作だけで集計作業が完結
- データ量が増えても手順は同じ
- 正しいデータの配置方法を学べる
では、順番に説明します。
マウス操作だけで集計作業が完結
ピボットテーブルは、関数やプログラミングを必要としない、マウスだけで簡単にデータを集計できるすばらしいツールです。
例えば、従来の手順であれば1時間ほどかかけていた営業データの集計も、ピボットテーブルを活用すれば1分以内で済ませることができます。
突然上司から集計表を求められたり、異なる視点でのデータ分析を要求されたりしても、ピボットテーブルを使いこなせれば慌てる必要はありません。
データ量が増えても手順は同じ
データ集計に関数や数式を使った場合、式を新しいセルに付け足すなどの調整が必要になることも多いですよね。
一方で、元データの量が増えても操作手順は同じ、というのがピボットテーブルの大きなメリットです。
数式とピボットテーブルを使った集計を比べると、データ量が増えるにつれ、集計作業のスピードはピボットテーブルの方が優れています。
正しいデータの配置方法を学べる
Excelを効率よく操作するのに、表の作り方(構成)はとても重要です。
構成がよくないと、複雑な関数や手作業でむりやり帳尻を合わせた表が仕上がってしまいます。
結果、データ件数や表の項目が増えるたび、余計な修正に時間を費やすことになるのです。
一方、正しくデータが整列された表であれば、ピボットテーブルで素早く集計できます。
万が一項目が増えた場合も、シンプルな修正で済むことがほとんどです。
まとめると、ピボットテーブルを使い続けることで理想のデータ配置をマスターできる、とも言えます。
かんたん！ピボットテーブルを作るまでの３ステップ
ピボットテーブルを作るまでの3ステップは以下のとおりです。
- 元となる表を作成する
- ピボットテーブルを挿入する
- 項目をマウスでドラッグする
では、順番に説明していきます。
元となる表を作成する
ピボットテーブルを作るためには、整列された表が必要となります。
ピボットテーブルに向かない表の典型例は、横見出しに月名が並んだような集計表です。
日頃いろんなところでよく見かける、下のような表ですね。
このような表は、一般に「横持ちの表」と呼ばれます。
見た目は整列されていますが、ピボットテーブルには不向きな表です。
いわゆる、集計に向いた「縦持ちの表」を意識してつくりましょう。
「縦持ちの表」については、当ブログ内の別記事で詳しく解説しているので興味のある方はぜひご覧ください。
ピボットテーブルを挿入する
元になる表が仕上がったら、元データの表を選択しましょう。
集計する表の範囲内であれば、どのセルでもOK。
挿入タブのピボットテーブルをクリック。
ダイアログが表示されたら、そのままOKをクリック。
新しいシートにピボットテーブルが作られました。
画面の右側にフィールドリストが表示されています。
参考までに、こちらに動画付きツイートを掲載しておきます。
項目をマウスでドラッグする
ピボットテーブルは挿入しただけでは使うことができません。
ボックスを操作することで集計を行うことができます。
上半分のフィールドセクションには表の項目を表示、下半分の領域セクションにはフィルタ・行・列・値の4つが表示されています。
設置手順は、項目を下の領域セクションにドラッグするだけ。
最初はどのフィールドをドラッグすべきか迷うこともあると思いますが、まずは自分の直感のままに、いろいろな組み合わせを試すことが、ピボットテーブルを習得するための近道です。
ここで「変な操作をしてしまったらデータが壊れてしまうのでは？」と考えていませんか？
ピボットテーブルは元データを壊さないので、失敗を恐れる必要はありません。
品名を行、店舗を列、合計金額を値にドラッグし、店舗と品名ごとの売上集計表が作ったのが下の画像です。
シンプルなクロス集計表だけでなく、複数の見出しを設定できるのもピボットテーブルのメリットです。
商品と店舗の2項目を行見出しに設定したのが下の画像です。
使わなくなった項目は、フィールドリストの外にドラッグするだけで削除されます。
もちろん、削除済みの項目をもう一度復活させることもできます。
このように何度もやり直しが出来ますので、いろいろ試して頂きたいです。
下のポスト内動画を再生すると、ピボットテーブルを作る動きが確認できます。ピボットテーブルの操作が分からない人、触るのが怖い人は、一度再生してみることをおすすめします。
これだけは覚えておきたい！ピボットテーブルでよく使う４つのテクニック
ピボットテーブルでよく使うテクニックは以下の4つです。
- 集計方法を変更する
- 時間をグループ化でまとめる
- アウトラインを使った行の折りたたみや展開
- データを追加した後に更新する
フィールドリストをセットするだけでも基本的な集計は可能です。
ピボットテーブルの機能を十分に活用するためには、カスタマイズの手順は知っておきたいところです。
集計方法を変更する
ピボットテーブルは、元データに合わせ、自動で合計や個数を集計してくれますが、もちろん一発で希望の集計方法にならないこともあります。
そんな時のために、集計方法を変える方法を知っておかないといけません。
マウス操作だけで完結するシンプルな手順で難しくないので、手を動かしながら覚えていきましょう。
修正したい箇所を右クリックし、集計方法を選択します。
ここで注意しないといけないのは、行見出しや列見出しではなく値を選択するということです。
複数の集計方法が表示されるので、希望の方法を選びましょう。
合計や個数だけでなく、平均値や最大値も選択肢に含まれます。
合計から個数に変更してみました。
時間をグループ化でまとめる
「グループ化」を使うと、日付を月や年単位にまとめることができます。
実際に、日付フィールドを月単位でまとめてみましょう。
日付データを右クリックし、「グループ化」を選びます。
すると、グループ化のダイアログが表示されます。
表示したい単位を選んでOKをクリックしましょう。
月単位ごとにまとまり、ピボットテーブルがスッキリしました。
アウトラインを使った行の折りたたみや展開
ピボットテーブルで2段階の集計表を作った場合は、行の折りたたみや展開ができます。
「ー」をクリックすると、行が折りたたまれます。
店舗毎の集計のみが表示されてピボットテーブルがスッキリしました。
「＋」をクリックすれば展開できます。
データを追加した後に更新する
「更新」をクリックすることで、追記や削除したデータを反映することができます。
元の表をテーブル設定しておけば、範囲修正などのメンテナンスを行わなくて済みます。
初心者が陥りがちなピボットテーブルの問題と解消方法
とても便利なピボットテーブルですが、操作に慣れていない初心者にとって使いづらく感じることもあるかもしれません。
代表的なのは以下の３つです。
- 値の見出し名が長くなる
- 書式設定が更新で解除される
- 関数の使い方が分からない
いずれの問題も、適切な手順を知っていれば解決できます。
値の見出し名が長くなる
ピボットテーブルでは、集計値の見出し名が「合計/○○」や「データの個数/○○」などと長くなり、見た目もよくありません。
見出し名を短くしたい人も多いでしょう？
実は、見出し名をかんたんに修正できる方法があります。
値フィールドを右クリックし、「値フィールドの設定」をクリック。
「名前の指定」の修正で、見出し名を変えることができます。
書式設定が更新で解除される
ピボットテーブル上の数字は、3桁カンマ区切りや小数点桁数の指定を行っても、更新を行うと設定前の初期設定に戻ってしまいます。
しかし、書式も更新後に戻さない方法もあります。
「ピボットテーブルオプション」の下にある「自動調整」と「セル書式の保持」を適切に設定しましょう。
関数の使い方が分からない
ピボットデーブル内で数式を設定する方法が分からず、範囲外のセルに直接式を入れたりしていませんか？
項目を増やして更新させると、作った式が消えてしまうこともあります。
また、書式もくずれてしまいます。
値フィールドの「計算の種類」を活用すれば、簡単な計算を行うことができます。
さらに、思い通りの数式をピボットテーブル内で作りたい場合は、メジャーを活用するのがいいでしょう。
当ブログ内でも、メジャーの使い方について触れている記事もありますので、興味ある方はごらんください。
まとめ
このようにピボットテーブルは、迅速で手軽に集計を行うことが出来ます。ピボットテーブルに難しいイメージを持つ必要はありません。
まずは、ピボットテーブルを挿入してマウスで項目を移動させてみましょう。
実際に操作を行えば、とても手軽な機能であることを理解して頂けると思います。
更に、ピボットテーブルを習得することで、ExcelやAccessなどのスキル向上にもつながりますので使わない手はありません。
是非、ピボットテーブルを使えるようになりましょう。
また、当ブログでは、日常業務で役立つExcelの使い方が別記事でも紹介しています。
これらの便利なテクニックについても当ブログでもご紹介しているので、トップページからチェックしてみましょう。