Excel(エクセル)のピボットテーブルは、膨大なデータを簡単に分析や集計するときに大いに役立つ便利機能です。
簡単に誰でも利用することが出来ますが、使ったことが無い方にとっては、ハードルが高いイメージがあるのではないでしょうか。
この記事ではピボットテーブルを使える条件などの基本的な操作から、具体的な実践での使い方、人と差が付く応用テクニックを紹介します。
また、ピボットテーブルを使った実例として、後半の見出しに「ピボットテーブルを使った家計簿」と「重複データを確認する方法」についてご紹介します。
ピボットテーブルで何が出来る？
それでは、下の売上データに対してピボットテーブルを使うとどんなことが出来るのでしょうか。
ピボットテーブルを使うと、下の図のように月別の地域ごとの売上高を簡単に一瞬で集計することが出来ます。エクセルが苦手な方でも１分程度あれば十分可能な操作です。
ピボットテーブルは通常の表とは異なり、行項目や列項目を自由に変更することが出来る上、集計方法も「合計」だけでなく「データ個数」や「平均」なども簡単に集計することが出来ます。
また、元データの数値の変更にも対応可能で、ピボットテーブルの集計結果を瞬時に更新することが可能です。
例えば、売上明細データに対してピボットテーブルを起動すれば、「月別にいくらの売上があったか」や「どの支店が一番売上が多かったか」「どの品目が一番売れたのか」などが一目で分かるようになります。
ピボットテーブルの使い方(基本編)
ピボットテーブルのイメージは少し出来たかと思いますので、基本的な使い方を順を追って解説します。
ピボットテーブルを使うための元データを準備
ピボットテーブルを使うためには、まずは元データを用意する必要があります。つまり、売上明細データや支払明細データや資産明細データなどの集計や分析したいデータのことです。
ピボットテーブルを正しく使うためには、元データに対して以下の4つの条件を整える必要があります。
【ピボットテーブルの元データの４つ条件】
①列項目に空白セルが無い
➁書式に統一性がある(全角、半角、カタカナなど）
③セルが結合されていない
④表に隣接したセルにデータが入っていない
ピボットテーブルを挿入する
元データを整え終えたら、さっそくピボットデーブルを使ってみましょう。
元データの中であればどのセルでもいいのでカーソルを合わせてから(図①)、「挿入」タブの「ピボットテーブル」をクリック(図➁)します。
そうすると、下のような「ピボットテーブルの作成」画面が表示されますので、元データのデータ範囲(図③)が正しければ、「OK」をクリック(図④)します。この操作でピボットテーブルのための新しいワークシートが自動で作成されます。
ピボットテーブルを挿入
ピボットテーブルの構成を理解しよう(集計前)
表示された集計前のピボットテーブルのワークシートについて説明します。
ワークシートの左側がピボットテーブル(図①)で、画面右端がピボットテーブルの作業フィールド(図➁)です。
作業フィールドには、元データの列項目が自動で登録されていて、この項目をフィールド下に配置されている「フィルター」や「列」や「行」や「値」にドラッグ＆ドロップすることで、自分の好みの集計や分析を行うことが出来ます。
【構成①】ピボットテーブル：自分の好みの集計が表示されるエリアです。
【構成➁】作業フィールド：ピボットテーブルを作成するための作業エリアです。
ピボットテーブルの構成を理解しよう(集計後)
それでは実際に作業フィールドの項目を①～③のようにドラッグ＆ドロップしてみましょう。
- 行項目に設定したい項目を、「行」フィールドにドラッグ＆ドロップ(図①)
- 集計したい値が含まれた項目を「値」フィールドにドラッグ＆ドロップ(図➁)
- 一部のデータで値を表示したい場合は、その項目を「フィルター」フィールドにドラッグ＆ドロップ(図③)
上の図のように地域別の４～６月の売上数量をピボットテーブル上で集計することが出来ました。このように、ピボットテーブルを使うと、ドラッグ＆ドロップのみで簡単に自分の望むデータの集計や分析を行うことが出来ます。
ここで、新たに「行ラベル」「列ラベル」「値エリア」「レポートフィルター」の４つの構成を紹介します。
【構成③】行ラベル：表の縦軸を表します。
【構成④】列ラベル：表の横軸を表します。
【構成⑤】値エリア：数値が表示される部分です。
【構成⑥】レポートフィルター：一部のデータのみを表示させることが出来ます。
ドラッグ＆ドロップをやり直す
ドラッグ＆ドロップした内容をやり直したい場合は、「フィルター」「列」「行」「値」の各フィールドに挿入されている項目をドラッグ＆ドロップで元の位置に戻す(図①)ことで集計の対象から外すことが出来ます。
または、列項目のチェックを外すことでも、集計の対象から外すことが出来ます。
ピボットテーブルの使い方(実践編)
この見出しでは、ピボットテーブル実践編として、さきほどの果物の店舗別売上データを使いながら、具体的な使い方をもう少し掘り下げて紹介します。
売上データを分析
例えば、果物ごとの月別の売り上げを見たいときには、以下のように項目を設定すれば売上を時系列で表示します。
月別＆商品別の売上高を集計
「ドラッグ＆ドロップする項目」
・「行」フィールド⇒「商品名」
・「値」フィールド⇒「販売高４月」「販売高５月」「販売高６月」
このようにピボットテーブルを使って、日別や月別や年度別などの値を集計することが出来ます。
売上高を集計する２つ目の例として、販売店舗別の果物の４月販売高を分析したい時は、以下のように項目を設定すれば、売上高を分析して表示することが出来ます。
月別＆商品別の売上高を集計
「ドラッグ＆ドロップする項目」
・「行」フィールド⇒「商品名」
・「列」フィールド⇒「販売店舗」
・「値」フィールド⇒「販売高４月」
レポートフィルターを使ってデータを絞る
レポートフィルターは、一部のデータのみをピボットテーブルに表示したい時に利用します。
たとえば、下の図のように、地域別の４～６月の果物の売上高を集計するが、「バナナ」のみの売上高を集計したいという場合は、「フィルター」フィールドに「商品名」をドラッグして、プルダウンのメニューから「バナナ」だけを選びます。
「ドラッグ＆ドロップする項目」
・「行」フィールド⇒「地域」
・「値」フィールド⇒「販売高４月」「販売高５月」「販売高６月」
・「フィルター」フィールド⇒「商品名」
このフィルター機能は「バナナ」の１つだけでなく、「バナナ」と「梨」というように複数の条件を設定することも可能です。
行ラベルを複数設定する
行ラベルは１項目だけでなく、複数の項目を設定することが出来ます。
下のように、行ラベルに「地域」だけでなく「商品名」を追加すると、地域別の各果物の販売高も集計することが出来ます。このように、行ラベルに複数の項目を設定すると、より詳細な分析が可能となるので便利です。
また、行ラベルの順番によって表示のされ方が変わります。集計や分析したい内容に合わせて、行ラベルの順番を変更することも大切です。
「行」フィールドに追加されている項目をドラッグ＆ドロップで順番を入れ替えてみましょう。そうすると、下のように果物別の各地域の販売高が集計されました。
見比べると下のようになります。行ラベルの追加の仕方次第で分析できる内容も変わってきます。
ピボットテーブルを昇順/降順や五十音順で並び替える
ピボットテーブルを作成すると、規則性が無い並び順で表示されますので、見栄えをよくするためにデータの並びを昇順や五十音順に並び替えしましょう。
数値の入った列を降順に並び替える方法
下のように、「販売高(円)４月」列の各果物の売上高は「1,757,500」⇒「1,457,500」⇒「291,500」⇒「663,000」と規則性が無く並んでいます。この並び順を昇順や降順で並び替えたい時は、「販売高(円)４月」列にカーソルを合わせた状態(図①)で、右クリックし、右クリックメニューから「並び替え」⇒「降順」を選択(図➁)します。
そうすると、下のように果物の販売高が大きいものから降順で並び替えることが出来ました。
小計の大きいものから降順に並び替える方法
下のように、地域別の売上高の小計は、「6,590,500」⇒「4,697,500」⇒「6,941,500」⇒「4,742,000」と規則性が無く並んでいます。この並び順を昇順や降順で並び替えたい時は、「販売高(円)４月」列の小計が表示されているセルにカーソルを合わせた状態(図①)で、右クリックし、右クリックメニューから「並び替え」⇒「降順」を選択(図➁)します。
そうすると、下のように地域別に果物の販売高が大きいものから降順で並び替えることが出来ました。
地域名を五十音順に並び替える方法
下のように、地域名の並び順は、「東京(と)」⇒「愛知(あ)」⇒「福岡(ふ)」⇒「大阪(お)」と規則性が無く並んでいます。この並び順を五十音順で並び替えたい時は、「地域」が表示されているセルにカーソルを合わせた状態(図①)で、右クリックし、右クリックメニューから「並び替え」⇒「昇順」を選択(図➁)します。
そうすると、下のように地域名を五十音順に並び替えることが出来ました。
ピボットテーブルを更新する方法
ピボットテーブルは、元データの値を変更したり表の間に行や列を挿入しても、簡単にピボットテーブルを更新することが出来ますので、操作方法を紹介します。
元データの東京の「バナナ」の販売数量を300個から8,000個に変更します。
ピボットテーブルのシートに戻り、ピボットテーブル内で右クリック(図①)し、右クリックメニューの中から「更新」をクリック(図➁)します。
そうすると、下のようにピボットテーブルの集計結果が自動で更新されます。
元データの範囲を変更する方法(データソースの変更)
ピボットテーブルの元データに新たな行を最下行を追加したり、新たな列を右端の列に追加することがあると思います。
こんな時、ピボットテーブルに反映させるために、上の見出しで紹介した『更新』ボタンを使っても変更箇所のデータは反映されません。
この場合は、「データソースの変更」を利用する必要があります。それでは更新の手順を紹介します。
まず、元データの最下行に新たなデータを追加します。
次にピボットテーブルのシートに移動し、ピボットテーブル内の任意のセルをクリックします。
すると、「ピボットテーブル分析」タブが表示されるので、「データソースの変更」をクリックします。
「ピボットテーブルのデータソースの変更」画面が表示されるので、元データの範囲を確認し、新たに追加した行が範囲に含まれていないことを確認します。
データ範囲を直接キー入力で変更するか、右横の↑ボタンを使ってドラッグ＆ドロップで新たなデータ範囲を選択してください。変更し終えたら「OK」を押してください。
そうすると、下の図のようにピボットテーブルでの集計結果も自動で更新されます。
集計結果を明細データにアウトプットする方法
ピボットテーブルの集計結果を明細データとしてアウトプットすることが出来ます。
ピボットテーブルの中で、内容を確認したい値をダブルクリックしてください。そうすることで、新しいワークシートが吐き出され、明細データをアウトプットすることが出来ます。
旧ピボットテーブル形式に変更する
『Excel2007』以前のピボットテーブル(以降、旧ピボットテーブル ）は、ピボットテーブル自体に直接項目をドラッグ＆ドロップすることが可能でした。
旧ピボットテーブルの方が視覚的に列レベルや行ラベルや値を設定しやすく、このバージョンの方が使いやすい方もいるため、形式を旧バージョンに変更する方法を紹介します。
まずは、ピボットテーブル内の右クリックメニューから「ピボットテーブル オプション」をクリックします。「ピボットテーブル オプション」画面の「表示」タブを選択し、「従来のピボットテーブルのレイアウトを使用する(グリッド内でのフィールドのドラッグが可能）」にチェックを入れて、「OK」で画面を閉じます。
そうすると、下のように旧ピボットテーブル形式に切り替わります。「作業フィールド」の仕様は今のピボットテーブル形式と同じですが、旧ピボットテーブルは「行ラベル」「列ラベル」「値エリア」「レポートフィルター」がドラッグ＆ドロップ出来るように青線の枠で囲われています。
作業フィールドの項目をピボットテーブルに直接ドラッグ＆ドロップすると、集計や分析を行うことが出来ます。
ピボットテーブルの使い方(応用編)
この見出しでは、覚えておくと得するピボットテーブルの応用テクニックを紹介します。
「小計」行の表示/非表示を切り替える
ピボットテーブルで行ラベルを複数設定すると、下のように「小計」行が自動で生成されます。
しかし、ピボットテーブルの集計結果の利用の仕方次第では、この「小計」行や「小計」行の値が邪魔な場合がありますので、表示しない方法を紹介します。
「小計」の値を非表示にする場合は、ピボットテーブル内のどのセルでもいいのでクリックすると、「デザイン」タブが表示されます。
この「デザイン」タブの「小計」⇒「小計を表示しない」を選択すると「小計」の値を非表示にすることが出来ます。
「小計」行自体を非表示にする場合は、さきほどの「旧ピボットテーブル形式に変更する」の見出しで紹介した旧ピボットテーブルであれば非表示にすることが可能です。
まず、旧ピボットテーブル形式に変更し、先ほど同様、「デザイン」タブの「小計」⇒「小計を表示しない」を選択します。
そうすると、下のように旧ピボットテーブル形式であれば、「小計」行自体を非表示にすることが可能です。
ピボットテーブルを別のワークシートに貼り付けて利用する
ピボットテーブルの集計結果を、別のワークシートに貼り付けて利用する方法を紹介します。
まず、ピボットテーブルの枠外のセルから、ピボットテーブル全体を選択して、「Ctrl」キーを押しながら「C」キーでコピーします(図①)。次に、貼り付け先のシートに移り、右クリックメニューから「形式を選択して貼り付け」⇒「値の貼り付け」をクリックします。
貼り付けたデータの空白セルを埋めたい場合は、下の手順でセルを埋めることが出来ます。
まず、表全体の範囲を選択後、「Ctrl」キーを押しながら「G」キーを押して、「ジャンプ」画面の「セル選択」をクリックします。
「選択オプション」画面が表示されますので、「空白セル」にチェックを入れて、「OK」をクリックします。
空白セルのみ選択された状態となりますので、上のセルを”＝”で参照します。次に、「Ctrl 」 キーを押しながら 「Enter」キーを押して、選択したセル全てに同じ数式を一括で挿入すると、下のように空白セル全体を埋めることが出来ました。
”＝”の数式がたくさんのセルに挿入されたまま表を加工するとミスが発生するかもしれませんので、表全体を値のみで貼り付け直し、罫線やセル色などを調整すれば、ピボットテーブルを利用した表の完成です。
ピボットテーブルで『平均値』を集計する
ピボットテーブルは、『合計』だけでなく『平均値』や『データ個数』を集計することも可能です。
下は、AクラスとBクラスの個人別の１００走の結果を纏めた表です。この表にピボットテーブルを挿入します。
ピボットテーブルを作成すると、下のように『合計』で集計されますが、１００走の結果を合計してもあまり意味はないので、『平均値』で集計する方法を紹介します。
値エリアにカーソルを合わせた状態で右クリック(図①)して、右クリックメニューから「値の集計方法」⇒「平均」を選択します。
そうすると、下のようにピボットテーブルの集計方法が「平均」に変わり、集計結果が更新されました。このように右クリックメニューから集計方法を「平均」や「データ個数」、「最大値」「最小値」に変更することが可能です。
「スライサー」機能を挿入する
ピボットテーブルの『スライサー』機能とは、ピボットテーブルのデータを簡単に一瞬でフィルタリングできる機能です。
この「スライサー」の使い方を紹介します。
まずピボットテーブル内の任意のセルをクリックします。すると、「ピボットテーブル分析」タブが表示されるので、「スライサーの挿入」をクリックします。
「スライサーの挿入」画面が表示され、元データの列項目の一覧が表示されます。
ピボットテーブルは、商品で集計されていますが、今回は「地域」も合わせて確認したいので、「地域」にチェックを入れて「OK」を押します。
そうすると、「地域」のスライサーが表示され、「愛知」「大阪」「東京」「福岡」の各地域のスライサーボタンが表示されます。
このボタンが薄い青色の時は、対象のデータがピボットテーブルに表示されている状態です。
「愛知」の「商品」の売上を確認するために、スライサーの「愛知」をクリックします。
そうすると、下の様に、愛知の商品別売上高を確認することが出来ます。
スライサーで複数の項目を選択する時は、スライサー画面の右上の「複数選択」ボタンを押してから、２つ目以降の項目を選択してください。そうすると、「愛知&東京」の「商品別売上」が確認することが出来ます。
スライサーを解除したい時は、右クリックメニューの「”**”の削除」を押してください。「スライサー」画面を選択してから「Delete」キーでも削除することが可能です。
ピボットテーブルデザインを変更
ピボットテーブルのデザインを、「デザイン」タブ⇒「ピボットテーブルスタイル」から変更することが出来ます。
ピボットテーブルの集計結果のスクリーンショットを取って、そのまま別の資料に貼り付けて使用したい場合は、このメニューから見栄えを整えると時短に繋がります。
ピボットテーブルグラフを挿入する
ピボットテーブルの集計結果をグラフにすることも可能です。
ピボットテーブルにカーソルを合わせると「ピボットテーブルの分析」タブが表示されますので、「ピボットグラフ」をクリックし、好みのグラフを挿入します。
下のように、ピボットグラフが挿入されます。通常のExcelグラフとは異なり、ピボットテーブルを構成している各項目がグラフ上にボタンとして配置されていて、クリックすることで一部のデータのみをグラフに表示することも可能です。
集計方法が決まっていない時は『おすすめピボットテーブル』を使う
ピボットテーブルに慣れるまではどの項目をドラッグ＆ドロップすればいいかイメージが湧きづらいです。
その場合は、「おすすめピボットテーブル」を利用することで、元データの内容をExcelが自動で判断して、おすすめの集計方法を表示してくれます。
ピボットテーブルにカーソルを合わせると「ピボットテーブルの分析」タブが表示されますので、「おすすめピボットテーブル」をクリックし、「おすすめピボットテーブル」画面から使いたい表をクリックします。
ピボットテーブルでデータ個数や重複データを確認する
ピボットテーブルは表として活用する以外にも、データの個数や重複データを確認するのに役立ちます。
例えば、下の地域別の販売高がまとめられた表に、同じ地域(もしくは同じ行)が混ざっていないか確認したい時は、「地域」列のみをデータ範囲として指定してピボットテーブルを挿入します。
「地域」列のみを範囲選択した後に、「挿入」タブの「ピボットテーブル」をクリックします。
「行ラベル」も「値エリア」も「地域」をドラッグ＆ドロップしてピボットテーブルを作成すると下のようになります。
数値の入っていない項目を値エリアに挿入すると、自動でデータの個数が集計されるようになり、下の図のように「大阪」が２行存在していることが分かりました。
ピボットテーブルの家計簿は集計が簡単！
エクセルで家計簿をつけている方は多いと思いますが、ピボットテーブルで家計簿を管理することも可能です。
ピボットテーブルを使った家計簿には以下の５つのメリットがあります。
１．１カ月や１年間の支出や収入の集計が簡単
２．家計簿のサイズがコンパクト
３．出費が無い日の入力枠を作成する必要が無い
４．1月～12月までの家計簿が一つのワークシートで管理出来る
５．集計の仕方を自由に簡単に変更できる
下の表のように、入出金がある度に明細データを入力していきます。
この家計簿の明細データを元データとしてピボットテーブルを使用すると、自分の見たい項目を自由に集計することが出来ます。
このピボットテーブルを使った家計簿の作り方は、下のリンク記事で詳しい作り方を紹介していますので、ぜひ参考にしてみて下さい。
これからはピボットテーブルで時短！
この記事ではピボットテーブルの全般的な使い方を紹介しました。データを分析や集計する時にとても強力な味方になってくれる便利ツールですので、難しそうに感じるかもしれませんが、慣れれば誰でも簡単に利用出来ます。
まずはピボットテーブルを挿入し、自由にドラッグ＆ドロップすることから始めて、実戦に取り入れてみて下さい。