患者中心の保健医療を支える
処方情報分析のリーディングカンパニー

 03-3239-6840

ピボットテーブル、いまさら聞けない活用術・トラブル解決

time 2017/07/03

※解説記事内の画像はExcel 2010のものです。

いろいろな切り口で自由に集計できるピボットテーブルですが、もちろん皆さんは使っていますよね。
今回はちょっと便利な機能やよくあるトラブルの解消法をご紹介します。

ピボットテーブルの作り方(いまさらですが)

釈迦に説法ではありますが、話の流れ上簡単に説明します。
(1) 使いたいデータを用意し、シートに貼り付けます。
01_データ
(2) [挿入]-[ピボットテーブル]をクリックし、貼り付けたデータを【テーブル/範囲】に指定します。
※今回はピボットを新しいシートに作成しますので、【新規ワークシート】を選択しています
02_ダイアログ
(3) できあがり♪あとは表示したい項目や集計値の項目をドラッグすればOK!
03_ピボット作成直後

(1)ではシートに貼らずに外部データ(例えばcsvファイルやAccessデータ)を読み込んだり、(2)では同じシートにピボットテーブルを作ったりできますが、今回は省略します。

ここで重要なことは、『必ず表タイトルをつけること』です。
面倒臭がってタイトルをつけないと
04_ピボット作成ミス
のようになり、何で集計するか全くわからなくなるうえ、先頭の1行分は集計されません。

さあ、みんなでピボットをつかいましょう!
・・・・で終わってしまうと編集長に怒られてしまいますので、ちょっとした便利な活用法を次の章でご紹介します。

ちょっと便利なピボットの使い方

値フィルター

集計する値に対してフィルターをかけられるのはピボットに限らず普通のフィルターでもできますが、意外に便利なのはTop(またはBottom)○○位を表示。
人数でソートかけてしまえば・・・と思うかもしれませんが、複数の切り口を出しているときは、どの項目でフィルターをかけるかで、見え方がちょっと違います。

前章で作成したピボットを施設と年月日を行ラベルに設定し、合計金額を集計値として設定します。
05_ピボットフィルタ前

さて、ここから集計値のTop3フィルターをかけたいと思います。
フィルターの機能名としては[トップテン]とありますが、何位かは自分で設定できます。
(行ラベルの上で右クリック→[フィルター]→[トップテン])

例えば施設でTop3フィルターをかけると・・
06_ピボットフィルタ後-1
と、Top3の施設が表示されますが、年月日でTop3フィルタをかけると
07_ピボットフィルタ後-2
と、こちらは施設毎の年月日Top3を表示させられます。

グループ化

自分でコツコツ、ドラッグ→右クリック→グループ化→ドラッグ→右クリック→グループ化→・・・・ とグループ化することもできますが、値と日付に関しては、一気にグループ化も可能です。
(行ラベルの上で右クリック→[グループ化])

(ア) 値の範囲でグループ化
08_ピボットフィルタ数値

(イ) 日付の期間でグループ化
09_ピボットフィルタ年月日

(ア)はある程度自分でグループ化する値の単位など設定できます。とはいえ、規則的なグルーピングにならざるを得ません。
(イ)は決まった単位でしか設定できません。
ですが、ひとつずつグループ化していくより格段に効率的になるはずです。

多彩な集計/計算方法

ピボット上でシェアが見ることができます。これは大部分の人がご存知でしょう。
もう一歩進んで、各区分でのシェアもこの機能で見ることができます。
今回は施設ごとの薬剤シェアを出してみました。
(値の上で右クリック→[計算の種類]→[親行集計に対する比率])
10_ピボット集計

データが無くても項目を出す

通常ピボットテーブルでは、データの無い項目は表示されません。ですが、今から紹介する方法で、全データの中で1つでもデータがある場合、値が空で表示することができます。

例えば、ある施設でのみ取り扱っている製品がある場合、通常は対象の施設・製品のみピボットには表示されます。
すべての施設で該当の製品を表示したい、という場合は、製品名の上で右クリック→[フィールドの設定]→[レイアウトと印刷]で【データの無いアイテムを表示する】にチェックを入れると表示されます。

データの有無で毎回表示行が変わるとその後の作業が煩雑になるので、私はよく使います。
11_ピボットデータ無いアイテム

関数でピボットの値を取り出すことができる

ピボットで出した値を計算しようとデータを取り出しても、データの順番が変わって意図しない位置のデータを取り出してミスをしてしまう事があると思います。

そんな時、この関数を使えば位置を変えても正しい値が出せます。
12_ピボットデータ関数

関数の使い方は、
GETPIVOTDATA( ①, ②, ③, ④ [, ・・・・以降④, ⑤ の繰り返し] )
①:取り出したい値の項目名
②:ピボットテーブルのセル位置(ピボットのどこかを指定していればOK)
③:条件としたい項目名
④:③の条件で検索したい値

となっています。

ピボットテーブルトラブルあるある

最後に、よくあるピボットのトラブルを取説風にご紹介いたします。

データを増やしたのに、ピボットの値が変わらない。

(原因)
元データを修正しただけではありませんか?
(対応)
元データを修正しただけではピボットテーブルに修正が反映されません。必ずデータを更新してください。(メニューバー[データ]→[すべて更新]→[更新])

データを更新したら、急に項目や値が無くなった

(原因)
データの項目を増やした場合に起きたと思います。この時項目を途中に入れたりして、今まで使っていた項目がデータの範囲外に追いやられていませんか?
(対応)
データの範囲を再度設定してください。

データを更新したら、エラー「そのピボットテーブルのフィールド名は正しくありません。(~以下略)」が出た

(原因)
データの項目を増やした/減らした場合に起きたと思います。この時タイトルなしのデータを使ったり、消してしまったりしてませんか?
(対応)
データの範囲内でフィールド名が無い箇所にタイトルを記載し、再度更新してください。

最後の2つは、もっとよい対応方法があります。
元データをテーブル化することです。
以前テーブルについて紹介しましたが、タイトルは必ず付くし項目追加やデータ行追加しても、自動で範囲が変わってくれますので、改めてテーブル形式の利用をお勧めします!

ピボットの再認識を!

ピボットは一時のデータ集計・分析だけではなく、データ更新などにおいても、継続して誤りなく使えるデータ分析の強い味方です。
改めてピボットを惚れ直してもらえたらうれしいです。

タグ

このブログについて

このブログ(JMIRI Office部)は、製薬企業のマーケティング部門の方やアナリティクス/レポーティングを担当している方に向け、Excel / PowerPointなどのOfficeソフトの使い方、分析の考え方、グラフの使い方、美しい資料の作り方、効率的な作業の進め方など、仕事に役立つ様々な情報を少しゆるめに発信していきます。

Author:医療情報総合研究所(JMIRI)について

医療情報総合研究所 JMIRIは、まだビックデータという言葉が無いころに日本で初めて処方情報データベースの運用・分析を開始した、処方情報分析のリーディングカンパニー。これまでに累計数十万ページ規模の分析レポート/グラフを製薬企業に向けて作成/提供してきました。

[詳細]


お問い合わせ

arrowup

mail