Excel中級者

[保存版]7分ですべてわかる!Excelピボットテーブルの使い方

しんたろ。
この記事でわかること
  1. ピボットテーブルをどんな時に使用するかわかる
  2. ピボットテーブルの作り方がわかる
  3. ピボットテーブルでできることがわかる
  4. ピボットテーブルのよくある悩み疑問の解決策がわかる
  5. ピポットテーブルでよくあるトラブルと解決方法がわかる
  • 「前任者が大量のデータを残してやめたんだけど、何もまとまってないしデータの扱い方がわからない・・・」
  • 「上司から急にデータ分析を任されて困ってる・・・」
  • 「今週末の会議までに上期の売上実績を分析して、下期の見通しを発表するための資料を作らないといけない・・・」

 こんな経験はありませんか?これ、全部私の体験談です。笑

 Excelの便利な機能に「ピボットテーブル」があります。ピボットテーブルを使用すると、複雑な関数を使用しなくても、マウス操作だけで簡単に大量のデータを集計したり分析してくれます。

 ピボットテーブルについては、内容が深すぎるため掘り下げると本が1冊広辞苑並に書けるのですが、この記事を見るだけでピボットテーブルの使い所や使い方を解説します。

しんたろ。

7分で解説しちゃいます!

ある程度わかるよ!って人は、目次の中から気になる項目をクリックしてくださいね。

この記事の目次
  1. Excelピボットテーブルでできることは?
    結果:大量のデータを扱う時がオススメ
  2. エクセルのピポットテーブルの作り方※動画付き
  3. ピポットテーブル操作時に知っておきたい設定方法
  4. ピポットテーブルのよくある疑問Q&A
  5. ピポットテーブルでよくあるトラブル
  6. まとめ

Excelピボットテーブルでできることは?
結果:大量のデータを扱う時がオススメ

 大量のデータは、さまざまな角度から分析をしなければ正しい情報は得られません。そこで用いられるのが「クロス集計」です。「クロス集計」とは、2つまたは3つのデータのみに焦点を当て、それぞれを縦軸と横軸に割り振って表にする集計方法です。

例えば元データに「顧客ごとの売上金額」「支社ごとの売上金額」などの複数のレベルごとにデータを加工できます。

クロス表1

上の表は、とある企業の商品販売数を年度別で示した表です。2022年度にかけて増加しており、順調に経営をすすめているように感じます。

クロス表2

細かく支店別で見てみると、特にC支店の売上が高いことがわかります。ピポットテーブルを用いると、これらのような注目するポイントの異なるクロス集計表を簡単に作成できます。

何千件・何万件とデータがある際は、ピポットテーブルをぜひ活用しましょう!

ピポットテーブルのメリット

  1. 集計表を簡単に作れる
  2. データの傾向を掴むことができる
  3. 関数を使わないので初心者でも安心
  4. INDEX関数 + MATCH関数をフル活用できる
  5. 「タイトル行を除いて、データの列全体の書式設定をする」ことが簡単になる

ピボットテーブルのデメリット

  1. 機能の存在に気づかない人が多く、そのため使い慣れていない人も多い事実
  2. 列幅や行の高さの調整が難しい
  3. データ行の削除は面倒くさい
  4. 相対参照は苦手
  5. その他、独自の機能制限がある
しんたろ。

メリット・デメリットを理解することで、作業効率が変わります。まずはピポットテーブル作成には欠かせない、テーブル作成について解説します。

テーブルの作成方法

テーブル作成方法1
  1. テーブルを変換したい表の任意のセルにカーソルを置きます。ここでは「A1 No.」にカーソルを合わせています。
  2. リボンの「挿入」タブから「テーブル」を選択。
テーブル作成方法2
  1. 自動で範囲を認識してメッセージが表示されるので「OK」を左クリック。
テーブル作成方法3
しんたろ。

これで完成!テーブル作成時の注意点を紹介します

テーブル作成の注意点

  • セルを結合しない
  • 表の周りは空白にする
  • 半角と全角は、どちらかに揃えておく
  • 列(フィールド)には同じ種類のデータを入力する

・セルを結合しない

 データを活用する際は、基本的に結合は厳禁です。うまくデータの集計ができなくなります。結合はデータを扱わず、見せるためだけの時に使用しましょう。

テーブル作成時の注意1

 1行目のセルB:セルCを結合している状態でテーブル作成をすると、うまくできません。

テーブル作成時の注意2

 セル「A1」にカーソルを合わせている状態で、テーブルを作成しようとすると「先頭行をテーブル見出しとして使用する」にチェックが付きません。

この状態で「OK」を押すと見出しがデータとしてカウントされてしまいます。

テーブル作成時の注意3

 もし「先頭行を見出しとして使用する」にチェックをつけて変換すると、結合が解除され勝手に見出し名が付きます。

テーブル作成時の注意4
しんたろ。

必ず見出しは列ごとに1つずつ必要ってことを覚えておきましょう。

ただし「見出し」だけではなくて、表の中にある結合もNGです。

テーブル作成時の注意5

クラスが同じという理由で、結合によるデータを作ったままテーブル変換をしてしまうと・・・?

テーブル作成時の注意6

「№5」の玉ねぎさんのクラスが勝手に空白になり、正しく集計することができなくなります。

しんたろ。

見た目のために「セルの結合」をしたくなるけど、テーブルを使用するさいは「セルの結合」は厳禁です!

・表の周りは空白にする

 テーブルを作成する時に、表だけではなくて日付や表題の場合もありますよね。

テーブル作成時の注意7
テーブル作成時の注意8
しんたろ。

表の適当な場所で「テーブルの作成」をしたら、今回のように自動で認識し変換してくれます。範囲指定すれば、今回の事象は回避できますよ。

・半角と全角は、どちらかに揃えておく

 半角と全角は別のデータとして扱います。あらかじめ揃えておく必要があります。

テーブル作成時の注意9

・列(フィールド)には同じ種類のデータを入力する

 データ分析を行う際は、必ず列ごとに種類で分けてデータ管理を行うようにしましょう。Excelは何でも入力できてしまうので、万が一データのズレがあった際はデータの量が膨大になるほど修正が大変です。

 氏名なら氏名、クラスならクラスを入力すると、正しくデータの集計やグラフ作成ができます。

テーブル作成時の注意10
しんたろ。

注意点はこれくらいです。ピポットテーブルの注意点も同じですので、ピポットテーブル作成の前に一度見直してみてくださいね。

エクセルのピポットテーブルの作り方※動画付き

ピポットテーブルの作成方法1
  1. 対象の表上にカーソルを合わせた状態にする
  2. 「挿入」タブをクリック
  3. 「ピポットテーブル」をクリック
ピポットテーブルの作成方法2
  1. [新規ワークシート]か[既存のワークシート]のどちらかを選択。新規ワークシートを選択すると、新しいシートに作成されます。既存のワークシートを選択すると「テーブル/範囲:」に任意のセルを設定することができます。
  2. 「OK」をクリック
  3. -COMPLETE-
ピポットテーブルの作成方法3

「フィールド名」にあるデータを各エリアにドラッグすると、簡単に表が作成できます。

ピポットテーブルの作成方法4

ピポットテーブルの作成方法・編集の仕方

  • データソースの変更はどこからするか
  • レポートはどこからするか
  • オプションはどこからするか

 ここでは、ピポットテーブルを作成してからの各設定方法を順に解説していきます。

データソースの変更はどこからする?

 「ピポットテーブルの範囲指定を間違えてしまった」と言う方に、簡単に再設定できる方法を解説します。[データソースの変更]から調整ができます。

ピポットテーブルの作成方法。編集方法1
  1. ピポットテーブルにカーソルを合わせる
  2. [ピポットテーブル分析]タブをクリック
  3. [データソースの変更]をクリック
ピポットテーブルの作成方法。編集方法2
  1. 「このブックのテーブルまたは範囲を使用」にある「場所:」をクリック
  2. 変更したい範囲のセルを選択する
  3. 「OK」をクリック -COMPLETE-

レポートのレイアウト変更はどこからするか

 ピボットテーブルレポートのレイアウトは、コンパクト形式でアイテムのラベルを繰り返さないように設定されています。

・[表形式]で表示するためには
ピポットテーブルの作成方法。編集方法3
  1. [デザイン]タブを選択
  2. [レポートのレイアウト]をクリック
ピポットテーブルの作成方法。編集方法4
  1. 一覧から[表形式で表示]をクリックします
ピポットテーブルの作成方法。編集方法5
  1. 表形式でピポットテーブルが表示されました
・オプションはどこから設定するか

 ピポットテーブルのオプション設定は、「表示形式」「レイアウト」「データ」「代替えテキスト」の4つの内容を細かく設定することができます。

ピポットテーブルの作成方法。編集方法6
  1. [ピポットテーブル分析]タブを選択
  2. [オプション]をクリック -COMPLETE-
ピポットテーブルの作成方法。編集方法7
[表示形式]
ピポットテーブルの作成方法。編集方法8
[レイアウト]
ピポットテーブルの作成方法。編集方法9
[データ]
ピポットテーブルの作成方法。編集方法10
[代替テキスト]

ピポットテーブル作成時の注意点

  • フィールド名は必須
  • 結合セルは禁止
  • 空白行を作らない
  • クラス名や生年月日等、決まった項目ごとにデータを揃える
    ※下の表は、データを揃える際に便利な関数一覧になります。
関数名使用例説明
ASC=ASC(A1)全角の英数字・カタカナ・記号を半角にする
JIS=JIS(A1)半角の英数字・カタカナ・記号を全角にする
UPPER=UPPER(A1)アルファベットの小文字を大文字にする
LOWER=LOWER(A1)アルファベットの大文字を小文字にする
SUBSTITUTE=SUBSUTITUTE(A1,”B”,”C”)「B」を「C」に置換する
TIRM=TRIM(A1)不要なスペースを削除する

ピポットテーブル操作時に知っておきたい設定方法

ピポットテーブルのキーボード操作
-ショートカットキーの紹介-

 「ピポットテーブルの作成」を開くには、[Alt]→[N]→[V]→[T]キーを順に押すと、ダイヤルログボックスが表示されます。

ピポットテーブルショートカットキー

テーブルのキーボード操作
-ショートカットキーの紹介-

 「テーブルの作成」を開くには、[Ctlr]→[T]キーを順に押すと、テーブルが適用されます。

テーブルショートカットキー

フィールド設定方法

 値の領域データは、ピポットグラフレポートの基になるソースデータ(表示されていない値)を次の方法で集計します。数値はSUM関数を使用し、テキスト値はCOUNT関数を使用します。また、集計をおこなう関数は変更できます。必要に応じて、ユーザ設定の計算を作成ることもできます。

個数や合計の変更方法

フィールド設定方法1
  1. [ピポットテーブル分析]タブを選択
  2. [フィールドの設定]をクリック
フィールド設定方法2
  1. [集計の方法]タブを選択
  2. [集計の方法フィールド]ボックスで’使用する関数を選ぶ
  3. [OK]をクリック -COMPLETE-
フィールド設定方法3

使用できる集計関数はこちら

関数集計方法
SUM値の合計。数値規定の関数。
COUNT値の個数。COUNT関数は、COUNTAワークシート関数と
同じように動作します。
COUNTは、数値以外の値に
対する規定の関数
AVERAGE数値の平均値
MAX最大値
MIN最小値
PRODACT数値の積
COUNT
NUMBERS
数値である値の個数。
COUNT NUMBERS関数は、COUNTワークシート関数と
と同じように動作します。
STDEV母集団の推定標準偏差
(母集団のサブセットを標本とする)
STDEVP母集団の標準偏差
(集計されるデータ全体が母集団のもの)
VER母集団の推定分散
(母集団のサブセットを標本とする)
VARP母集団の差異
(集計されるデータ全体が母集団のもの)
重複しない
値の数
一意の値数。この集計関数は、
Excelのデータモデルを使用する場合のみ機能します。

引用元:Microsoftサポート

ピポットテーブルの行とフィルター表示

 ピポットテーブルの細かい設定を覚えて更に作業効率化できるにしましょう。

・スライサーを使用してピポットテーブルのデータをフィルターで処理する
  1. ピポットテーブル内の任意の場所をクリックして、リボンのピポットテーブルタブ[ピポットテーブルの分析]と[デザイン]を表示
  2. [ピポットテーブル分析]、[スライサーの挿入]の順にクリック
  3. [OK]をクリック
  1. [スライサー挿入]ダイヤログログボックスで、スライサーを作成するフィールドのボックスにチェック。
  2. [OK]をクリック
  1. 各スライサーで、ピポットテーブルに表示するアイテムをクリック
    -COMPLETE-
・データを手動でフィルター処理する
  1. ピポットテーブルの[行ラベル]または[列ラベル]の矢印▼をクリック
  2. 行ラベルまたは列ラベルの一覧が表示されるので、一番上の[すべて選択]ボックスをオフにし、ピポットテーブルを表示するアイテムのボックスにチェック
  3. フィルター矢印のアイコンが変わり、フィルターが適用されたことを示す。
・レポートフィルターを使用して、アイテムをフィルター処理する

 レポートフィルターを使用すると、ピポットテーブル内の様々な値のセットを素早く表示することができます。

フィルターで選択したアイテムがピボットテーブルに表示され、選択されていないアイテムは非表示になります。 別のワークシートにフィルターページを表示する場合に、そのオプションを指定できます。

レポートフィルターを追加する
  1. ピポットテーブルの任意の場所をクリック
  2. [ピポットテーブルのフィールドリスト]で領域内のフィールドをクリックし、[レポートフィルターに移動]を選択

 複数のレポートフィルターを作成するには、この手順を繰り返します。レポートフィルターはピポットテーブルの上に表示されるので、簡単にアクセスが可能です。

 フィールドの順書を変更するには、[フィルター]領域のフィールドを目的の一にドラッグするか、フィールドをダブルクリックして[上へ移動]または[下へ移動]を選択。

 これに応じて、レポートフィルターの順はピポットテーブルに反映される。

行または列のレポートフィルターを表示する
  1. ピポットテーブル、またはピボットグラフの関連するピボットテーブルをクリク
  2. ピボットテーブルの任意の場所を右クリックし、[ピポットテーブルのオプション]をクリック
  3. [レイアウト]タブで以下のオプションを指定

a.[レポートフィルター]領域の[フィールドの配置]リストボックスで、次のいずれか手順を選択します。

  • 行のレポートフィルターを上から下に表示するためには、[上から下]を選択
  • 列のレポートフィルターを左から右に表示するには、[左から右]を選択

b.[列ごとのフィールド数]ボックスで、別の列または行を開始する前のフィールド数を入力または選択

レポートフィルターでアイテムを選ぶ
  1. ピポットテーブルで、レポートフィルターの横のドロップダウン矢印をクリック
  2. レポートに表示するアイテムの横にあるチェックボックスをオン。すべてのアイテムを選ぶには、[すべての選択]の横にあるチェックボックスをクリック
別のワークシートにレポートフィルターページを表示
  1. 1つ以上のレポートフィルターがあるピポットテーブルの任意の場所をクリック
  2. リボンの[ピポットテーブルの分析]→[オプション]→[レポートフィルターページの表示]の順にクリック
  3. [レポートフィルターページの表示]ダイアログボックスでレポートフィルターフィールドを選ぶ、[OK]をクリック
・上位または下位10アイテムを表示する

 他にも、上位または下位10件の値や特定の条件を満たす日付を表示するフィルターを適用できます。

  1. ピポットテーブルの[行ラベル]または[列ラベル]の横にある矢印▼をクリック
  2. 選択されているアイテムを右クリックし、[フィルター]をクリックしてから、[トップテン]または[下位10項目]をクリック
  3. 最初のボッk数に数値を入力
  4. 2番目のボックスで、抽出に使うオプションを選択。以下のオプションで使用可能
  • アイテム数で抽出するには[項目]を選択
  • パーセンテージで抽出するには[パーセンテージ]を選択
  • 合計で抽出するには[合計]を選択
・選択フィルターで選択したアイテムのみを表示するor非表示にする
  1. ピポットテーブルで、選択フィルターで表示する1つ以上のアイテムをフィールドから選択
  2. 選択されているアイテムを右クリックし、[フィルター]をクリック
  3. 次んのいずれかの操作を実行
  • 選択されているアイテムを表示するには、[選択されたアイテムのみ]をクリック
  • 選択されているアイテムを非表示するには、[選択した項目を表示しない]をクリック
しんたろ。

フィルターを解除すると、非表示のアイテムが再表示されます。同じフィールドの別のアイテムを右クリックし、[フィルター]をクリックしたのち、[フィルターのクリア]をクリックします。

・フィルターオプションのオンorオフを切り替える

 1つのフィールドに複数のフィルターを適用する場合、またはピポットテーブルにフィルターボタンを表示しないようにする場合は、次の方法でオプションまたは、その他のフィルターオプションのオンとオフを切り替えることができます。

  1. ピポットテーブル内の任意の場所をクリックし、リボンの[ピポットテーブル]タブを表示
  2. [ピポットテーブル分析]タブで[オプション]をクリック

a.[ピポットテーブルオプション]ダイアログボックスの[レイアウト]タブをクリック

b.[レイアウト]領域で、必要に応じて[1つのフィールドに複数のフィルターを使用可能にする]ボックスをオンまたはオフ

c.[表示]タブをクリックし、[フィールドのキャプションとフィルター]チェックボックスをオンにまたはオフにし、フィールドキャプションとフィルタードロップダウンを表示または非表示にする

ピポットテーブルのよくある疑問Q&A

ピポットテーブルは何列 and 何行まで可能か
限界値を解説

機能最大数
1つのシートのピポットテーブルレポート数使用可能メモリに依存
フィールドあたりの重複しないアイテム1,048,576
1つのピポットテーブルレポートの
行フィールドまたは列フィールドの数
使用可能メモリに依存
1つのピポットテーブルレポートに
作成できるレポートフィルター
256(ただし使用可能メモリに依存)
1つのピポットテーブルレポートの
作成できる値フィールド
256
1つのピポットテーブルレポートの
集計アイテムの数式の数
使用可能メモリに依存
1つのピポットグラフレポートに
作成できるレポートフィルター
256(ただし使用可能メモリに依存)
1つのピポットグラフレポートに
作成できる値フィールド
256
ピポットグラフレポートに
集計アイテムの数式
使用可能メモリに依存
ピポットテーブルアイテムの
MDXの名前の長さ
32,767
関連するピボットテーブルの
文字列の長さ
32,767
フィルターのドロップダウンの
一覧に表示されるアイテム
10,000

引用元:Microsoftサポート

ピポットテーブルの百万円単位は?

 表形式の設定を変更する方法は、[フィールドの設定]から設定可能です。

ピポットテーブルよくあるQ&A1
  1. 任意のピポットテーブルにカーソルを合わせる
  2. [ピポットテーブルの分析]タブを選択
  3. [フィールドの設定]タブをクリック
  4. [表示形式]をクリック
ピポットテーブルよくあるQ&A2
  1. [ユーザー定義]をクリック
  2. [種類:]にて「#、##」と入力。上の図は「万」も追加で入力しています。
  3. [OK]を入力
ピポットテーブルよくあるQ&A3

-COMPLETE-

ピポットテーブルでよくあるトラブル

 ここでは、ピポットを作成する際によくあるトラブル4つを解説します。

フィールドに何も表示されない場合

 「ピボットテーブルを作成したり参照したりしているとき、画面の右のほうに表示されるはずの[ピボットテーブルのフィールド]が表示されない。」

そんな時に今回は2つの原因と、どうしたら表示されるか解説します。

・原因1.ピボットテーブルにカーソルを合わせていない

ピポットテーブルよくあるトラブル1

対処法:ピポットテーブル内の任意のセルにカーソルを合わせる

・原因2.作業ウィンドウを閉じてしまった

 ピポットの作業ウィンドウ画面を[閉じる☒]ボタンで消した場合です。

ピポットテーブルよくあるトラブル2

対処法1:ピポットテーブル内で右クリックして[フィールドリストを表示する]をクリック

ピポットテーブルよくあるトラブル3

対処法2:[ピポットテーブル分析]→[フィールドリスト]をクリック

ピポットテーブルよくあるトラブル4

参照設定方法

参照設定GETPIVOTDATA関数のイメージ

 デフォルトでは、数式でピポットテーブルのいずれかのセルを参照しようとすると、「GETPIVOTDATA関数」が自動的に挿入される設定です。

しかし、相対参照にして1つの数式をコピペで使いまわしたいのに「GETPIVOTDATA関数」だと絶対参照扱いになり、コピペが非常にしにくいです。

>>相対参照・絶対参照の詳しい解説はこちら

GETPIVOTDATA関数が自動で挿入されない設定方法をExcel2016以前とMicrosoft365の2つの方法を解説します。

・Excel2016以前

参照設定1
  • リボンの[ファイル]タブをクリック
参照設定2
  • [その他]→[オプション]をクリック
参照設定3
  • [数式]→[ピポットテーブル参照にGetPivotData関数を使用する]のチェックをOFF
  • [OK]をクリック -COMPLETE-

・Microsoft365

参照設定4
  • ピポットテーブル内にカーソルを合わせる
  • [ピポットテーブル分析]タブを選択
  • [オプション]のVを選択
参照設定5

オプション全体をクリックではなく、[V]をクリック!

参照設定6
  • [GetPivotDataの生成]をクリック。-COMPLETE-

まとめ

この記事でわかること
  1. ピボットテーブルをどんな時に使用するかわかる
  2. ピボットテーブルの作り方がわかる
  3. ピボットテーブルでできることがわかる
  4. ピボットテーブルのよくある悩み疑問の解決策がわかる
  5. ピポットテーブルでよくあるトラブルと解決方法がわかる

 ピポットテーブルは複雑な関数を使わずに作ることができるExcel最強の機能です。ぜひ使いこなしてみてください。

 

全会社員を残業地獄から救いたい
本ブログの有効活用方法
1
Concept
初心者に分かりやすい解説

しんたろ。ブログでは「少しでも残業を減らしたい」「時間効率を上げて新しいことにチャレンジしたい」「仕事で評価を上げて年収を上げたい」そんなあなたに向けて私が学んできたノウハウを最短ルートで教えるExcelブログとなっています。

覚えておくべき関数や操作方法を徹底解説!

2
Concept
Excelを最短で
習得する方法を解説

「独学で習得できる気がしない」そんな方に効率よく習得できる記事を書きました。

本や動画を紹介していますが、やはり一番効率の良い習得方法は、講師にわからないことを質問できる『オンラインスクール』です。

3
Concept
Excelが大活躍!
会社員でも不就労所得を作れる。

「会社の収入だけでは不安」そう思い出会った副業がブログでした。書いた記事たちが、あなたが寝ている間もずっと働いてくれます。『不就労』の完成です。

記事を書く際の様々な分析にExcelがとても役立ちます。私は2023年末までにブログで脱サラする予定です!

あなたもブログで脱サラしましょう。


ABOUT ME
しんたろ。
しんたろ。
脱サラ目指す会社員
デスクワークに追われ、仕事が極端に遅かった私がExcelを学んだら会社から評価され残業も大幅削減を実現!早期FIREするための副業もしています。 あなたの人生も好転させたい!ぜひ本ブログをご活用ください。
記事URLをコピーしました