CoreGPT
Microsoft 365読了時間 21分

Excelのピボットテーブルの作り方【初心者向け・手順と例つき】

Excelで初めてのピボットテーブルを作る方法を解説します。データの準備、ピボットテーブルの挿入、行・列・値・フィルターへのフィールド配置、合計と個数の切り替え、日付のグループ化、更新、よくあるエラーの対処までまとめました。

Excelのピボットテーブルの作り方【初心者向け・手順と例つき】

Excelでピボットテーブルを作るには、データ内の任意のセルをクリックして 挿入 → ピボットテーブル → テーブルまたは範囲から を選びます(バージョンによっては 挿入 → ピボットテーブル をクリックするだけです)。範囲を確認し、新規ワークシート を選んで OK をクリックします。続いて ピボットテーブルのフィールド ウィンドウで、「地域」のようなテキストのフィールドをオンにして 行 に、「売上」のような数値のフィールドをオンにして 値 に配置します。数式を使わなくても、地域ごとの売上合計が表示されます。

Excelのピボットテーブルとは? リストをグループ化して集計してくれる機能です。どの列でグループ分けするか(たとえば地域)と、どの列を合計・個数・平均するか(たとえば売上)を選ぶだけ。数秒で並べ替えたり切り口を変えたりでき、元のデータは変更されません。

ピボットテーブルのトラブルの多くは、挿入ボタンを押す前の段階で起きます。見出しが空白、空白行がある、数値が文字列になっている、といったケースです。最初の2分で準備をしておけば、ほとんど防げます。

やりたいこと操作する場所
ピボットテーブルを作る挿入 → ピボットテーブル → テーブルまたは範囲から
Excelに候補を提案してもらう挿入 → おすすめピボットテーブル
フィールドごとにグループ化する行(または 列)にドラッグ
フィールドを合計・カウント・平均する値 にドラッグし、値フィールドの設定 で変更
レポート全体を絞り込むフィルター にフィールドをドラッグ、または ピボットテーブル分析 → スライサーの挿入
日付を月や年でまとめる日付を右クリック → グループ化
新しいデータを反映するピボットテーブル分析 → 更新(Alt+F5)
参照するデータ範囲を変えるピボットテーブル分析 → データソースの変更

ステップ1: データを準備する

このガイドでは、日付・地域・商品・売上 の列を持つ売上リスト(1行1注文)を例にします。始める前に、次の点を確認してください。

  • 見出し行が1行だけある。 すべての列に、短くて重複しない見出しが付いている。空白の見出し、結合セル、2段目の見出し行は避けます。
  • データの途中に空白行・空白列がない。 Excelが範囲を自動で選ぶとき、完全に空の行や列があるとそこで止まってしまいます。
  • 各列のデータの種類が揃っている。 売上の列に「n/a」のような文字や、文字列として保存された数値があると、合計ではなく個数が集計されます。
  • 小計や合計の行がない。 集計はピボットテーブルが自動で行います。

そのうえで、リストをテーブルに変換しましょう。リスト内のセルをクリックし、Ctrl+T(または 挿入 → テーブル)を押して、先頭行をテーブルの見出しとして使用する にチェックを入れたまま OK をクリックします。必須ではありませんが、おすすめです。テーブルに追加した行は次回の更新で自動的にピボットテーブルに含まれますが、通常のセル範囲では含まれません。

ステップ2: ピボットテーブルを挿入する(Windows版Excel)

この手順は、Microsoft 365版Excel、Excel 2024・2021・2019・2016で使えます。以下のメニュー名はMicrosoft 365のものです。古いバージョンでも、ピボットテーブル をクリックすると同じダイアログが開きます。

  1. テーブルまたはデータ範囲内の任意のセルをクリックします。Excelがリスト全体を自動で選択します。
  2. 挿入 → ピボットテーブル → テーブルまたは範囲から を選びます。
  3. テーブルまたは範囲からのピボットテーブル ダイアログ(古いバージョンでは ピボットテーブルの作成)で、テーブル/範囲 ボックスを確認します。テーブル名(例: Table1)や Sheet1!$A$1:$D$121 のような範囲が表示されます。範囲が違う場合は、ダイアログを開いたままシート上で正しいセルを選び直します。
  4. ピボットテーブルを置く場所を選びます。
    • 新規ワークシート は、新しいシートに作成します。最も安全な選択です。
    • 既存のワークシート は、場所 ボックスで指定した位置に作成します。フィールドを追加するとピボットテーブルが右や下に広がるので、十分な空きスペースを残してください。
  5. このデータをデータ モデルに追加する は、今はオフのままにします(複数のテーブルを組み合わせるときに使います)。
  6. OK をクリックします。

Excelの「テーブルまたは範囲からのピボットテーブル」ダイアログ。「テーブルまたは範囲を選択」の下のテーブル/範囲ボックスにTable1と表示され、既存のワークシートではなく新規ワークシートが選択され、場所ボックスは空、データ モデルへの追加はオフで、OKが既定のボタンになっている

新しいシートが開き、左側に空のピボットテーブル、右側に ピボットテーブルのフィールド ウィンドウが表示されます。ウィンドウが消えてしまった場合は、ピボットテーブル内をクリックするか、ピボットテーブル分析 → フィールド リスト を選んでください。

ステップ3: フィールドウィンドウでレポートを作る

ウィンドウの上部には、元データの列見出しがフィールドとして並びます。下部には4つのエリアがあります。

  • 行: 左側に縦に並べたい項目(地域、商品、月ごとに1行)。
  • 列: 上部に横に並べたい項目。
  • 値: 計算したい数値(合計、個数、平均)。
  • フィルター: レポート全体を絞り込みたいフィールド。ピボットテーブルの上に表示されます。

地域ごとの売上を合計するには、次のようにします。

  1. 地域 をオンにします。テキストのフィールドは自動的に 行 に入ります。
  2. 売上 をオンにします。数値のフィールドは自動的に 値 に入り、合計 / 売上 になります。

ExcelのピボットテーブルのフィールドウィンドウにフィールドとしてDate、Region、Product、Salesが並び、地域と売上がオンになっている。下の4つのエリアのうち、フィルターと列は空で、行に地域、値に合計 / 売上が入っている

これで地域ごとに1行ずつ表示され、一番下に 総計 が出ます。この例では、東 1,265、北 1,545、南 1,890、西 1,440 で、総計は 6,140 です。合計値には最初は元データの通貨書式が引き継がれません。追加する方法はステップ4で説明します。

チェックを入れるより、ドラッグのほうが細かく制御できます。商品 を 列 にドラッグすると商品ごとの列が横に並び、フィルター にドラッグするとレポートの上のドロップダウンで商品を1つずつ切り替えられます。地域と商品の両方を 行 に入れれば、階層構造のレポートになります。フィールドを外すには、チェックを外すか、エリアの外へドラッグします。

ステップ4: 合計を個数・平均などに切り替える

Excelは数値フィールドを合計し、テキストフィールドを個数で集計します。これを変更するには、次のようにします。

  1. 値 エリアの 合計 / 売上 をクリックして 値フィールドの設定 を選びます(ピボットテーブル内の数値を右クリックして 値フィールドの設定 を選んでもかまいません)。
  2. 集計方法 タブで、個数、平均、最大、最小 などを選びます。
  3. 必要に応じて、名前 ボックスにわかりやすい名称(例: 「注文件数」)を入力します。
  4. 表示形式 をクリックして、通貨や桁区切りを設定します。
  5. OK をクリックします。

同じダイアログの 計算の種類 タブを使うと、合計を 総計に対する比率 などの割合に変えられます。同じフィールドを 値 に2回ドラッグして、合計と個数を並べて表示することもできます。

ステップ5: ピボットテーブルを並べ替え・絞り込みする

  • 合計値で並べ替える: 合計 / 売上 列の数値を右クリックして 並べ替え → 降順 を選びます。この例では、南が一番上に来ます。
  • ラベルで並べ替える: 行ラベル の横の矢印をクリックして、昇順または降順を選びます。
  • 行を絞り込む: 行ラベル の横の矢印をクリックして不要な項目のチェックを外すか、値フィルター → トップテン で上位のみを残します。
  • スライサー: ピボットテーブル内をクリックし、ピボットテーブル分析 → スライサーの挿入 で「商品」などのフィールドをオンにして OK をクリックします。ボタンをクリックするだけでレポートを絞り込めます。日付の場合は タイムラインの挿入 で、日付のスライダーを追加できます。

ステップ6: 日付を月・四半期・年でグループ化する

行 に日付フィールドを追加すると、最近のWindows版Excelでは年・四半期・月に自動でグループ化されることがよくあります。グループ化を自分で指定するには、次のようにします。

  1. ピボットテーブル内の任意の日付を右クリックして グループ化 を選びます。
  2. グループ化 ダイアログの 単位 で、月 や 年 などを1つ以上選びます(データが複数年にわたる場合は両方を選びます。そうしないと、毎年の1月が1行にまとめられてしまいます)。
  3. OK をクリックします。

元に戻すには、日付を右クリックして グループ解除 を選びます。自動グループ化をやめたい場合は、ファイル → オプション → データ で、ピボットテーブルの日付/時刻列の自動グループ化を無効にする項目(英語版では Disable automatic grouping of Date/Time columns in PivotTables)をオンにします。「選択対象をグループ化できません」と表示される場合は、日付列に空白セルや、日付に見えるだけの文字列が含まれています。元データを修正してから更新してください。

ステップ7: ピボットテーブルを更新し、データソースを変更する

ピボットテーブルは、元データが変わっても自動では更新されません。

  • ピボットテーブル内をクリックして ピボットテーブル分析 → 更新 を選ぶか、右クリックして 更新 を選びます(Windowsでは Alt+F5)。
  • ブック内のすべてのピボットテーブルを更新するには、ピボットテーブル分析 → 更新 → すべて更新(Ctrl+Alt+F5)を選びます。

通常のセル範囲の下に行を追加した場合は、更新だけでは反映されません。ピボットテーブル分析 → データソースの変更 → データソースの変更 を選び、新しい範囲を選択するか、テーブル名(例: Table1)を入力して OK をクリックします。一度テーブルを参照先にしておけば、以降はこの作業が不要になります。

おすすめピボットテーブル: Excelに作ってもらう

何をどこに置けばよいかわからないときは、データ内をクリックして 挿入 → おすすめピボットテーブル を選びます。「地域別の売上の合計」のような既成の集計がプレビュー表示されます(バージョンによってダイアログまたはサイドウィンドウ)。1つ選んだら、あとは通常のピボットテーブルと同じように ピボットテーブルのフィールド ウィンドウで調整できます。

Mac版・Web版Excelでピボットテーブルを作る

Mac版Excel: データ内のセルをクリックして 挿入 → ピボットテーブル を選びます。ピボットテーブルの作成 ダイアログで範囲を確認し、新規ワークシート または 既存のワークシート を選んで OK をクリックします。ピボットテーブルのフィールド ウィンドウには同じ4つのエリアがあります。合計を個数に変えるには、値 のフィールドを開いて フィールドの設定 を選びます。更新は ピボットテーブル分析 タブ、またはピボットテーブルを右クリックして行います。

Web版Excel: データを選択して 挿入 → ピボットテーブル を選びます。ピボットテーブルの挿入 ウィンドウで 新しいシート または 既存のシート を選んで自分で作成するか、候補から選びます(おすすめの表示にはMicrosoft 365のサブスクリプションが必要です)。あとは ピボットテーブルのフィールド ウィンドウでフィールドをオンにします。計算方法を変えるには、値を右クリックして 集計方法(英語版では Summarize Values By)を選びます。更新するには、ピボットテーブルを右クリックして 更新 を選びます。

ピボットテーブルがうまく動かないとき

「ピボットテーブルのフィールド名が正しくありません。」 元の範囲に見出しのない列があります。範囲の端にある空の列や、結合された見出しセルが原因であることが多いです。すべての列に見出しを付け、見出しセルの結合を解除し、範囲に空白列が含まれていないか確認してください。

新しいデータが反映されない。 まず更新してください。それでも行が表示されない場合は、元の範囲の外にあります。$A$1:$D$121 のような固定範囲では122行目は対象外です。データソースの変更 で修正します。できればExcelのテーブルを参照させましょう。

数値が合計ではなく個数で集計される。 値の列に空白セルやテキストが含まれています。多くは文字列として保存された数値です(左揃えで、緑の三角が表示されることがあります)。数値に変換し(セルを選択して警告アイコンをクリックし、数値に変換 を選択)、空白を埋めるか削除してから更新し、値フィールドの設定 で 合計 に戻してください。

「データ ソースの参照が正しくありません。」 元の範囲やテーブルが削除または名前変更された、またはブックのファイル名に角かっこが含まれています。データソースの変更 で範囲を修正するか、[ ] を含まない名前で保存し直してください。

「ピボットテーブル レポートを別のピボットテーブル レポートに重ねることはできません。」 同じシート上の2つのピボットテーブルが広がって重なっています。片方を別のシートに移動する(ピボットテーブル分析 → ピボットテーブルの移動)か、間隔を広くあけてください。

よくある質問

ピボットテーブルは何に使うのですか? 長いリストをすばやく集計するための機能です。地域別の売上、月別の注文数、カテゴリ別の平均コストなどを求められます。SUMIFS や COUNTIFS の数式を書く代わりに、フィールドをドラッグして配置するだけで済みます。

複数のシートからピボットテーブルを作るには? シートの列構成が同じなら、まず1つのテーブルにまとめるのが最も簡単です。そうでない場合は、各リストをテーブルにして、このデータをデータ モデルに追加する をオンにしてピボットテーブルを挿入し、ピボットテーブルのフィールド ウィンドウで すべて を選んで全テーブルを表示します。Excelが尋ねたら、テーブル間のリレーションシップを作成します(商品IDなど、共通の列が必要です)。

ピボットテーブルは自動で更新されますか? いいえ。ピボットテーブル分析 → 更新 または Alt+F5 で更新するか、ピボットテーブル オプション の ファイルを開くときにデータを更新する をオンにします。

ピボットテーブルを削除するには? ピボットテーブル内をクリックし、ピボットテーブル分析 → 選択 → ピボットテーブル全体 を選んで Delete を押します。専用のシートにある場合は、シートごと削除してもかまいません。

ピボットテーブルとExcelのテーブルの違いは? Excelのテーブル(Ctrl+T)はデータそのもので、フィルターや自動拡張の機能があります。ピボットテーブルはそこから作る読み取り専用の集計です。数値を変えたいときは、元データを編集して更新します。2つを組み合わせると最も効果的です。

データからすばやく答えを得る

ピボットテーブルは合計を教えてくれますが、その意味を読み解くのは自分です。CoreGPT for Excelなら、シートを読み取って代わりに分析します。「第3四半期の売上で目立つ点は?」と尋ねれば、好調な地域や不自然な落ち込み、傾向をブックのすぐ横で教えてくれます。無料で始められ、Windows、Mac、Web版のExcelで使えます。

試してみる: CoreGPT for Excel

関連記事:

無料AIツール

関連記事