Excelのピボットテーブルの作り方｜集計・更新・割合の表示まで

ピボットテーブルでできること

毎月の売上の一覧から「担当者ごとの合計」「商品ごとの月別の金額」を出したいとき、SUMIFS などの式を 1 つずつ書くと、集計の切り口を変えるたびに作り直しになります。ピボットテーブルを使うと、項目をドラッグして並べ替えるだけで、同じ一覧からいろいろな集計表を作れます。

Microsoft の公式サポートでも、ワークシートのデータを分析・集計する機能として紹介されています。

元データを整える

ピボットテーブルは元の表の形が崩れていると、うまく集計できません。公式サポートでは、元データについて次の点が挙げられています。

この記事では、次のような売上の一覧を例にします。

元データは、先に Excel の「テーブル」にしておくのがおすすめです。公式サポートによると、テーブルを元にしたピボットテーブルは、テーブルに足した行が更新時に自動で集計の対象に入り、新しく足した列もフィールドの一覧に出てきます。テーブルの作り方は Excelのテーブル機能の使い方 で説明しています。

ピボットテーブルを作る手順

公式サポートの Windows 版の手順です。

1. 表の中のセルを選ぶ

   元データの表の中のどこか 1 つのセルを選びます。

2. [挿入] > [ピボットテーブル] を押す

   リボンの [挿入] タブから [ピボットテーブル] を選びます。

3. 範囲と置き場所を決める

   元データの範囲 (またはテーブル名) が正しいかを確かめ、置き場所に [新規ワークシート] か [既存のワークシート] を選んで [OK] を押します。

4. フィールドをエリアに入れる

   画面の右側に出る [ピボットテーブルのフィールド] で、使いたい項目にチェックを入れるか、下のエリアへドラッグします。

4つのエリアの使い分け

フィールドの一覧の下には、フィルター・列・行・値の 4 つのエリアがあります。公式サポートの説明をまとめると次のとおりです。

チェックを入れるだけの場合、公式サポートのとおり、ふつうは数字以外の項目が「行」に、数字の項目が「値」に入ります。

担当者別・商品別の売上表にする

例の売上データなら、次のように入れると「担当者 × 商品」の金額の表ができます。

• 行: 担当者
• 列: 商品
• 値: 金額

担当者と商品を入れ替えたいときは、行と列のエリアの間でフィールドをドラッグするだけで表が組み替わります。

合計を件数や平均に変える

値のエリアに数字の項目を入れると、既定では合計 (SUM) で集計されます。公式サポートによると、データが文字として扱われていると件数 (COUNT) になります。合計のはずが件数になっているときは、元データの数字が文字として入っていないかを確かめてください。

集計方法を変えるには、Windows では値のエリアにあるフィールド名の右の矢印から [値フィールドの設定] を開き、[集計方法] で合計・個数・平均などを選びます。

総計に対する比率で表示する

金額そのものではなく「全体の何 % か」を見たいときは、同じ [値フィールドの設定] の [値の表示の種類] タブで、総計に対する比率を選びます。担当者ごとの売上の構成比を出したいときに使えます。

データを足したら更新する

元データをテーブルにしていない場合、範囲の外に足した行は更新しても集計に入りません。行が増えていく表は、最初にテーブルにしてからピボットテーブルを作っておくと、更新するだけで済みます。

自分で作るのが難しいときは

ピボットテーブルは、元データが 1 つの表にまとまっていれば強力です。ただ、支店ごとに別々のファイルで売上を管理している、毎月の締めで何枚ものシートを手で貼り合わせている、といった状況では、集計の前のデータ集めに時間がかかります。

複数のファイルからデータを集める部分を VBA で自動にしたり、集計表の更新までボタン 1 つで済む形にしたりできます。どこから手を付けるか迷うときは、シートナビの相談フォームからお気軽にご相談ください。

よくある質問