2022.09.18 2023.03.12

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

しんたろ。

この記事でわかること

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

「前任者が大量のデータを残してやめたんだけど、何もまとまってないしデータの扱い方がわからない・・・」
「上司から急にデータ分析を任されて困ってる・・・」
「今週末の会議までに上期の売上実績を分析して、下期の見通しを発表するための資料を作らないといけない・・・」

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

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

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

この記事の目次

LINEでExcelを気軽に学べる
Excelピボットテーブルでできることは?
結果：大量のデータを扱う時がオススメ
エクセルのピポットテーブルの作り方※動画付き
- ピポットテーブルの作成方法・編集の仕方
  - データソースの変更はどこからする？
  - レポートのレイアウト変更はどこからするか
- ピポットテーブル作成時の注意点
ピポットテーブル操作時に知っておきたい設定方法
ピポットテーブルのよくある疑問Q＆A
- ピポットテーブルは何列 and 何行まで可能か
  限界値を解説
- ピポットテーブルの百万円単位は？
ピポットテーブルでよくあるトラブル
- フィールドに何も表示されない場合
  - ・原因１.ピボットテーブルにカーソルを合わせていない
  - ・原因2.作業ウィンドウを閉じてしまった
- 参照設定方法
  - ・Excel2016以前
  - ・Microsoft365
まとめ

期間限定でChatGPT✖️Googleスプレットシートのコンテンツ配布中！

LINEでExcelを気軽に学べる

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

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

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

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

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

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

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

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

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

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

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

テーブルの作成方法

テーブルを選択

テーブルを変換したい表の任意のセルにカーソルを置きます。ここでは「A1 No.」にカーソルを合わせています。
リボンの「挿入」タブから「テーブル」を選択。

テーブル範囲指定

自動で範囲を認識してメッセージが表示されるので「OK」を左クリック。

テーブル完成

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

テーブル作成の注意点

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

・セルを結合しない

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

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

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

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

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

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

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

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

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

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

・表の周りは空白にする

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

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

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

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

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

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

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

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

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

ピボットテーブル設定方法

対象の表上にカーソルを合わせた状態にする
「挿入」タブをクリック
「ピポットテーブル」をクリック

ピボットテーブル範囲指定

［新規ワークシート］か［既存のワークシート］のどちらかを選択。新規ワークシートを選択すると、新しいシートに作成されます。既存のワークシートを選択すると「テーブル/範囲:」に任意のセルを設定することができます。
「OK」をクリック
-COMPLETE-

ピボットテーブル完成

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

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

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

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

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

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

ピポットテーブルにカーソルを合わせる
［ピポットテーブル分析］タブをクリック
［データソースの変更］をクリック

「このブックのテーブルまたは範囲を使用」にある「場所：」をクリック
変更したい範囲のセルを選択する
「OK」をクリック　-COMPLETE-

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

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

・［表形式］で表示するためには

［デザイン］タブを選択
［レポートのレイアウト］をクリック

一覧から［表形式で表示］をクリックします

表形式でピポットテーブルが表示されました

･オプションはどこから設定するか

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

［ピポットテーブル分析］タブを選択
［オプション］をクリック　-COMPLETE-

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

フィールド名は必須
結合セルは禁止
空白行を作らない
クラス名や生年月日等、決まった項目ごとにデータを揃える
※下の表は、データを揃える際に便利な関数一覧になります。

関数名	使用例	説明
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関数を使用します。また、集計をおこなう関数は変更できます。必要に応じて、ユーザ設定の計算を作成ることもできます。

個数や合計の変更方法

［ピポットテーブル分析］タブを選択
［フィールドの設定］をクリック

［集計の方法］タブを選択
［集計の方法フィールド］ボックスで’使用する関数を選ぶ
［OK］をクリック　-COMPLETE-

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

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

引用元：Microsoftサポート

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

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

・スライサーを使用してピポットテーブルのデータをフィルターで処理する

ピポットテーブル内の任意の場所をクリックして、リボンのピポットテーブルタブ［ピポットテーブルの分析］と［デザイン］を表示
［ピポットテーブル分析］、［スライサーの挿入］の順にクリック
［OK］をクリック

［スライサー挿入］ダイヤログログボックスで、スライサーを作成するフィールドのボックスにチェック。
［OK］をクリック

各スライサーで、ピポットテーブルに表示するアイテムをクリック
-COMPLETE-

・データを手動でフィルター処理する

ピポットテーブルの［行ラベル］または［列ラベル］の矢印▼をクリック
行ラベルまたは列ラベルの一覧が表示されるので、一番上の［すべて選択］ボックスをオフにし、ピポットテーブルを表示するアイテムのボックスにチェック
フィルター矢印のアイコンが変わり、フィルターが適用されたことを示す。

・レポートフィルターを使用して、アイテムをフィルター処理する

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

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

レポートフィルターを追加する

ピポットテーブルの任意の場所をクリック
［ピポットテーブルのフィールドリスト］で領域内のフィールドをクリックし、［レポートフィルターに移動］を選択

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

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

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

行または列のレポートフィルターを表示する

ピポットテーブル、またはピボットグラフの関連するピボットテーブルをクリク
ピボットテーブルの任意の場所を右クリックし、［ピポットテーブルのオプション］をクリック
［レイアウト］タブで以下のオプションを指定

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

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

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

レポートフィルターでアイテムを選ぶ

ピポットテーブルで、レポートフィルターの横のドロップダウン矢印をクリック
レポートに表示するアイテムの横にあるチェックボックスをオン。すべてのアイテムを選ぶには、［すべての選択］の横にあるチェックボックスをクリック

別のワークシートにレポートフィルターページを表示

1つ以上のレポートフィルターがあるピポットテーブルの任意の場所をクリック
リボンの［ピポットテーブルの分析］→［オプション］→［レポートフィルターページの表示］の順にクリック
［レポートフィルターページの表示］ダイアログボックスでレポートフィルターフィールドを選ぶ、［OK］をクリック

・上位または下位10アイテムを表示する

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

ピポットテーブルの［行ラベル］または［列ラベル］の横にある矢印▼をクリック
選択されているアイテムを右クリックし、［フィルター］をクリックしてから、［トップテン］または［下位10項目］をクリック
最初のボッk数に数値を入力
2番目のボックスで、抽出に使うオプションを選択。以下のオプションで使用可能

アイテム数で抽出するには［項目］を選択
パーセンテージで抽出するには［パーセンテージ］を選択
合計で抽出するには［合計］を選択

・選択フィルターで選択したアイテムのみを表示するor非表示にする

ピポットテーブルで、選択フィルターで表示する１つ以上のアイテムをフィールドから選択
選択されているアイテムを右クリックし、［フィルター］をクリック
次んのいずれかの操作を実行

選択されているアイテムを表示するには、［選択されたアイテムのみ］をクリック
選択されているアイテムを非表示するには、［選択した項目を表示しない］をクリック

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

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

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

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

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