2016/11/30 公開

【今さら聞けない】 ピボットテーブルの使い方・ミスと解決方法

Pocket

これまでのExcel活用術では、様々な関数をもとに「集計」を行ってきました。元々このExcelは集計やデータ分析に特化しているソフトですが、そのなかでも「ピボットデーブル」は、手間がかからず、瞬時に様々な集計ができる機能です。

今回はExcelを使ううえでマスターしておけば困らないピボットテーブルについて、今さら聞けない基礎知識から実践までを解説します!

1.ピボットテーブルの基礎知識
2.ピボットテーブルの使い方・実践
3..ピボットテーブルで起こりうる失敗と解決方法

1.ピボットテーブルの基礎知識

まずはピボットテーブルの基礎知識について、改めて確認しておきましょう。このピボットテーブルとは、リストとしてまとめられたデータを様々な視点から統計をとったり、分析する際に使う機能です。グラフとして視覚化することもできる便利な機能ながら、「難しそう……」なんてイメージから一般的に使っていない、知らないという方もいるといわれています。

今回は、社内満足度調査を集計した以下のデータをもとにしていきます。

pivot1

このデータからピボットテーブルを作成すると、性別や年代別、項目ごとに合計数を出すことができるのです。対象がどれだけ膨大なデータであったとしても、欲しい情報だけを選んで抽出することが可能になります。

pivot2

条件に合ったデータを選び、抽出・合計したい時、SUMIF関数を使えばいいのではないかと考える方もいるのではないでしょうか。しかし、SUMIF関数は対象となる表をその度に作り直す必要があるほか、不便な点があります。データが増えた時のこと、自分以外にもデータを扱う担当者がいることを考えると、操作が簡単でスムーズなピボットテーブルのほうが適しているといえます。

2. ピボットテーブルの使い方・実践

ではいよいよ、ピボットテーブルの作成に移りましょう。対象となるデータを範囲指定し、〔挿入〕タブから「ピボットテーブル」を選びます。

pivot3

すると、分析するデータとピボットテーブルの作成先を選択する画面になります。ピボットテーブルのために分析する範囲が合っているかの確認とともに、既存のワークシートに作成する場合は作成したい部分のセルをクリックしましょう。

pivot4

以上の操作により、ピボットテーブルの枠が作成された状態になります。右上に表示された「ピボットテーブルビルダー」に項目をドラッグ・アンド・ドロップしていけば、集計表が完成します。

ピボットテーブルは値の集計方法を変更することも簡単にできます。各フィールドの見出しをダブルクリックすると開く「ピボットテーブルフィールド」の「集計の方法」から、「平均」を選ぶだけ。同時にフィールド名も変更できるのです。

pivot5-e1472713392127

集計の方法は平均以外にも、最大値や最小値や積と様々です。表をもとに関数で対応しようとすると、合計はSUMIF、平均はAVERAGEIF関数と、関数をその度に使い分けなければいけません。先述した通り、SUMIF関数では不便である可能性が発生します。

併せて、セルの表示形式の変更についても平均で集計した際は覚えておきたいポイントです。

平均で集計をすると、どうしても小数点以下に数字が並んでしまうことでしょう。これを見やすくするためには、範囲を指定した後にショートカットキーの「Ctrl+1」を使い、表示された「セルの書式設定」の「表示形式」・「分類」から「数値」を選びます。

pivot6

中央部の「小数点以下の桁数」の桁数を0から1に変更することで、すっきりと見やすい表示にできました。

3. ピボットテーブルで起こりうる失敗と解決方法

便利なピボットテーブルですが、何かしらの不備があると作成できません。

pivot8

せっかくピボットテーブルを作ろうとしたのに、警告が出てしまった……そんな時に考えられる原因は、見出しの項目の空白です。入力の際に項目名を消してしまっていたほか、セル結合により空白が生じていたのかもしれません。

そしてピボットテーブルに修正箇所があると気づいた時、直接ピボットテーブルで編集をしようとすると、以下のエラーメッセージが出てしまいます。

pivot9

実はピボットテーブルそのものに直接データを入力することはできないのです。修正したい時は、必ず元データのほうを修正する、と覚えておきましょう。

またここで覚えておかなければいけないのは、元データを修正した後には〔ピボットテーブル分析〕のタブから「更新」を押すことです。更新を選択して初めて、ピボットテーブルに修正が反映されます。

それでも修正が反映されない……といった場合は、元データの最終行、列に追加したデータであることが考えられます。追加分のデータは最初に作ったピボットテーブルの範囲から外れてしまうので、反映されないのです。

この問題は、元データをテーブルに変換しておくことで防ぐことができます。〔挿入〕タブの「表」を選び、範囲を確認してOKを押しましょう。元データがテーブルに変換され、データを追加しても「更新」ボタンを押せばピボットテーブルに反映されるようになります。

pivot10-e1472713473286

ピボットテーブルで、業務の効率化を目指す。

膨大なデータを取り扱う、蓄積していったデータをもとに様々な方法で分析する際、関数を使うことが難しい場合もあります。そんな時に役立つ機能、ピボットテーブルを使用すれば業務の効率化につながることでしょう。

■Excel活用術バックナンバー
Vlookup関数とエラー「#N/A」の回避方法を伝授!人材管理に役立つExcel Vlookup術[vol.1]
MATCH関数とVLOOKUP関数の合わせ技!人材管理に役立つEXCEL VLOOKUP術[vol.2]
INDEX関数とVLOOKUP関数を組み合わせて昇給額を算出!EXCEL VLOOKUP術[Vol.3]
VLOOKUP関数で同一の検索値が複数ある場合のデータ抽出術!EXCEL VLOOKUP術[Vol.4]前編
重複する検索値はCOUNTIF関数で個別化しよう!EXCEL VLOOKUP術[Vol.4]後編
VLOOKUP、COUNTIF、MATCH関数を使って勤務管理を効率化!EXCEL VLOOKUP術[Vol.5]
【TODAY・DATEDIF・CONCATENATE】エクセル関数を使った社員名簿・社員台帳の作り方
【REPT関数、MOD関数、ROW関数】エクセル関数を使った見やすい表の作り方
【エクセルでグラフ作成】会社の人事データを整理するためのグラフの作り方

マネたまご マネたまをフォローすれば最新記事をお届けします!
運営会社 | Copyright © kaonavi, inc. All Rights Reserved.