エクセルのピボットテーブルの基本の集計方法を覚えよう
初心者エクセルのピボットテーブルの集計は、「合計」以外に「個数」などに変更することってできるんですか？
Dr.オフィス初期設定の状態は、「合計金額」が表示されるようになっているんだけど、「個数」や「平均値」などに変更することもできるよ！
ピボットテーブルの「集計」に関する操作は、ピボットテーブルの基本的な操作です。
「合計」以外の値の集計方法を覚えておくと、データの使い道をさらに広げられるようになります。
エクセルのピボットテーブルでマスターしておきたい集計に関する機能
- 「合計」以外の数値に変更する
- オリジナルの計算式を追加して集計する方法
- 数値をグループ化して見やすくする手順
- 集計行の表示・非表示
ピボットテーブルの「集計」に関する設定の変更方法を覚えると、活用範囲を広げることができるので参考にしてみてください。
エクセルのピボットテーブルの作り方と基本操作をくわしく解説した関連記事がありますので、こちらもぜひご覧ください。
＼ Officeドクター読者限定・無料Q&A ／
この記事の内容でわからないことや、今すぐ解決したいOfficeのお悩みはありませんか？
Officeドクターの中の人が、公式LINEで直接ご質問にお答えします！
下のボタンをタップして、表示された入力欄からそのまま送信してくださいね。
目次
エクセルのピボットテーブルの集計は「合計」が基本になっている
エクセルでピボットテーブルを作成すると、初期設定では挿入されている数字の「合計」が集計されるようになっています。
例えば、「値」に「金額」のフィールドを挿入すると、「金額の合計」が集計されるということです。
「金額」のフィールドを「値」のボックスに挿入すると、「金額」から「合計/金額」という文字に変わります。
これは、「金額」の数字が「合計」として集計されていることを表しています。
初心者ピボットテーブルの集計って「合計」するときにしか使えないんですか？
Dr.オフィス設定を変更すれば、「最大値や最小値」、「個数」や「平均」などに変更ができるよ。
集計の値を「合計」以外に変更する方法は、次の項目をご覧ください。
集計の値を「個数」・「平均」などに変更する方法
集計の値は、「合計」以外に「個数」、「平均」、「最大」、「最小」など11種類の計算方法に変更することが可能です。
計算方式の設定は、上記の「フィールドの設定」を使います。
今回は、「個数」と「平均」に変更する方法を紹介していますが、他の計算方法も基本の操作はすべて同じです。
個数に変更する
ピボットテーブルの値を「金額」から「個数」に変更してみましょう。
STEP
ピボットテーブル内の金額が入力されている任意のセルを選択
ピボットテーブル内の金額が入力されている任意のセルをクリックします。
STEP
「フィールドの設定」を選択
- ピボットテーブルのエリア内のセルをクリックする
- 「ピボットテーブル分析」タブをクリック
- 「フィールドの設定」を選択
STEP
「個数」を選択
- 「選択したフィールドのデータ」の枠にある「個数」をクリックする
- 「OK」を選択
STEP
フィールドの値が「個数」に変更された
「札幌」のデータを例にみると、「赤枠」が「それぞれの商品の販売個数」、「青枠」が「個数の合計」です。
各支店の商品の販売個数の合計を確認したいときは、フィールドの値を「合計」から「個数」に変更してみてください。
STEP
「ピボットテーブルのフィールド」の値の表記が変わる
「合計」から「個数」に変更するとピボットテーブルのフィールドの「値」に挿入されているフィールド名が「合計/金額」から「個数/金額」に変わります。
平均に変更する手順
平均に変更する場合も、「個数」を変更したときの基本的な操作は同じです。
STEP
金額が入力されている任意のセルをクリック
金額が入力されている任意のセルをクリックします。
個数になっていた場合は、個数の値が入力されているセルをクリックしてください。
STEP
「フィールドの設定」を選択
- 「ピボットテーブル分析」タブをクリック
- 「フィールドの設定」を選択
STEP
計算の種類を「平均」に変更
- 「選択したフィールドのデータ」から「平均」を選択
- 「OK」を押す
STEP
値が「平均」に変更された
総計が、各支店の商品売上の個数に対する平均金額に変更されました。
値フィールドの名前を変更する方法
ピボットテーブルに表示されている値フィールドの名前は変更することが可能です。
ピボットテーブル内に表示されている赤枠の「値フィールド」の名前は、変更することができます。
STEP
変更したい文字が入力されているセルを選択
変更したい値フィールドが入力されているセルをクリックします。
STEP
「アクティブなフィールド」の下にある枠内の文字を変更
- 「ピボットテーブル分析」タブをクリック
- 「アクティブなフィールド」の文字の下にある枠内の文字を削除して、変更したい文字を入力
- Enterで確定
STEP
変更した文字がピボットテーブルに反映された
変更した文字をピボットテーブルに反映することができました。
既存のフィールド値を使ってオリジナルの計算フィールドを設定して集計する
「既存のフィールド値を使って、オリジナルの計算フィールドを設定して集計する」と言われても、ちょっとわかりにくいですよね。
実際のデータ例を使って、どういうことなのか確認してみましょう。
赤枠の「合計/売上」の金額は、元のテーブルから表示したものです。
青枠の「合計/手数料込み金額」は、赤枠の「合計/売上」の金額に10%分の手数料を上乗せした金額になるようにオリジナルの計算フィールドを作成して、表示しています。
上記のように、既存のフィールド値を基準にしてオリジナルの計算式を作り、集計することができます。
「集計フィールド」を使ってオリジナルの計算を追加する
「集計フィールド」を使って、オリジナルの計算を追加してみましょう。
STEP
「フィールド/アイテム」を選択
- 「ピボットテーブル分析」タブをクリック
- 「フィールド/アイテムセット」のアイコンを押す
- 「集計フィールド」を選択
STEP
「集計フィールドの挿入」に新しい計算方法を入力
- 「名前」の枠の中に「手数料込み金額」と入力
- 「数式」の枠の中に「=売上*1.1」と入力
- 「追加」を押す
- 「OK」をクリック
Dr.オフィス計算式に使用する既存フィールド名は、ピボットテーブルで使用しているフィールド名と同じ名称を使用してね。
違う名称で入力してしまうと、エラーになってしまうから注意しよう！
STEP
作成した計算フィールドが追加された
ピボットテーブルのデータ範囲のテーブルにはデータは追加されていませんが、ピボットテーブルと「ピボットテーブルのフィールド」には、作成した計算フィールドが自動的に追加されます。
追加したオリジナルの計算を削除する
追加したオリジナルの計算フィールドを削除したいときは、以下の手順で操作を行います。
STEP
「ピボットテーブル分析」タブにある「集計フィールド」を選択
- ピボットテーブル内を選択したあとに「ピボットテーブル分析」タブをクリック
- 「フィールド/アイテムセット」のアイコンを押す
- 「集計フィールド」を選択
STEP
作成したフィールド名を選択して削除する
- 「名前」の枠にある下向き矢印をクリック
- 表示された名前から「手数料込み金額」の文字の上をクリックする
- 「削除」を押す
- 「OK」をクリックする
STEP
追加した計算フィールドが削除された
ピボットテーブルと「ピボットテーブルのフィールド」から追加した計算フィールドを削除することができました。
エクセルのピボットテーブルで項目別に集計する手順
エクセルのピボットテーブルで項目別に集計したい場合は、集計したいフィールドを「行」または「列」に挿入します。
例えば、上記のように「分類」と「商品名」を表示して、「分類」で集計をしたい場合は、「行」のボックスに「分類」と「商品名」、「値」のボックスに「金額」のフィールドを挿入します。
ピボットテーブルの作成方法、フィールドの追加の仕方がわからない場合は、こちらの記事で詳しく解説していますので、こちらをご覧ください。
集計したいフィールドを「行」または「列」に挿入
上記のように、「分類」と「商品名」が左側、「金額」が右側に来るように集計したい場合は、「行」のボックスに「分類」と「商品名」、「値」のボックスに「金額」のフィールドを挿入します。
「分類」と「商品名」が上段、「金額」が下段になるように集計したい場合は、「列」のボックスに「分類」と「商品名」、「値」のボックスに「金額」を挿入してください。
月別に集計
月までの売上額が支店別に入力されているデータを使って、月別に集計する手順を解説します。
例えば、左のように支店別に各月の売上額が入力されているデータを使って、「縦方向に支店名」、「横方向に月」を表示して集計したい場合は、以下のようにフィールドを挿入してください。
行：支店
月：列
値：売上
各フィールドのボックスにフィールドを挿入すると上記のような状態になります。
今度は、「行」と「列」のフィールドを入れ替えてみましょう。
「行」と「列」のフィールドを入れ替えると、縦方向に「月」、横方向に「支店名」を表示して集計することができます。
列：支店
行：月
値：売上フィールドを挿入する位置を変えると、ピボットテーブルの形状が変わるので、用途に合わせて作成してみてください。
担当者別に集計
担当者別に集計したい場合は、担当者名が入力されているデータを使用して、「行」または「列」に担当者名のフィールドを挿入します。
左側のテーブルのように、担当者が入力されているデータを使って、ピボットテーブルの「行」または「列」に「担当者のフィールド」を挿入すると、上記のように「担当者別」に集計することができます。
縦方向に「月」、横方向に「担当者」が表示されるように集計してみましょう。
この画面のように集計したい場合は、以下のようにフィールドを挿入してください。
列：担当者
行：日付 「月(日付)」と「日(日付)」は、自動で生成されます。
値：金額 （担当者/金額と表示されます）今度は、横方向に「担当者」、縦方向に「商品分類」を表示して集計してみましょう。
このような形で集計したい場合は、以下のようにフィールドを挿入してください。
列：担当者
行：商品分類
値：金額実際のデータで操作する場合は、「列」または「行」のいずれかの見出しに「担当者」、と集計したいフィールド（例題の「月」または「商品分類」に該当する部分）を挿入しましょう。
「行」・「列」にフィールドを挿入してクロス集計を行う手順
クロス集計を行いたい場合は、「行」と「列」の両方に集計に使用したいフィールドを挿入します。
先ほどのデータのように、「列方向」と「行方向」の両方にフィールドを挿入すると、クロス集計を行うことができます。
しかし、上記のように、「列」または「行」のいずれかのボックスが空白になっていると、クロス集計ができないので、注意してください。
グループ化してエクセルのピボットテーブルを見やすくしてみよう！
日付が日にち単位まで入力されているデータを使うと、日付をグループ化することができます。
例えば、日にちまで入力されているデータをグループ化すると、「同じ月」ごとに1つにまとめられるので、上記のように1か月分のデータとして集計できるようになります。
日付をグループ化する
日付まで入力されているデータを月ごとにグループ化する手順と、月と日付を同じ列で表示させる方法を解説していきます。
ピボットテーブルの参照元は、上記のテーブルを使用しています。
担当者別に日付を月ごとにグループ化する
日付まで入力されているデータを月ごとに表示されるように、ピボットテーブルをグループ化してみましょう。
STEP
それぞれのボックスにフィールドを挿入する
担当者ごとに月別にグループ化したい場合は、上記のようにフィールドを挿入します。
列：担当者
行：日付（「月(日付)」と「日(日付)」は、自動で挿入されます）
値：金額（担当者/金額）という表示に変わります。STEP
日付を展開したいときは「＋」をクリックする
日付を展開したい場合は、「+」をクリックすると表示が「－」に変わり、上記のように1月分のデータがすべて表示されます。
初心者ピボットテーブルの左上にある「担当者/金額」という文字を削除することってできないんですか？
Dr.オフィスピボットテーブルに影響するセルのデータは削除ができないんだ。
「担当者/金額」という文字は削除することはできませんが、文字を背景と同じ色に変えると、見えなくすることは可能です。
- 見えなくしたい文字が入力されているセルをクリック
- 「ホーム」タブをクリックする
- フォントの色の下向き矢印をクリック
- 背景色と同じ色を選択
「担当者/金額」の文字が見えなくなり、ピボットテーブルの見出しが見やすくなりました。
ピボットテーブルの仕様上、表の中にある特定の文字を削除することはできませんが、どうしても見えなくしたい場合は、「フォントの色を背景色と同じにする」という方法を試してみてください。
「+」ボタンを表示させずに月ごとにデータをまとめて集計する
「+」ボタンを表示させずに、月ごとにデータをまとめて集計することも可能です。
「日(日付)」と「日付」をピボットテーブルのフィールドの枠外へドラッグしてください。
「+」のボタンがなくなり、すっきりした表になりました。
グループ化を解除する方法
グループ化した日付は、ピボットテーブルを削除せずに解除することができます。
- グループ化されているデータが入力されているセルを選択
- 「ピボットテーブル分析」タブをクリック
- 「グループ解除」を押す
グループ化が解除されてすべてのデータが表示されます。
グループ化できないときは入力されているデータの内容を確認しよう
ピボットテーブルがグループ化できない場合は、フィールドに挿入した項目のデータ内容を確認してみましょう。
ピボットテーブルのグループ化ができないときは、以下のような原因が考えられます。
- 日付が文字列になっている
- ピボットテーブルのデータが古い（元データが変更されているのに、ピボットテーブルが更新されていない）
例えば、日付の前にアポストロフィ('）が入力されていると、日付ではなく、文字列として判断されます。
日付がグループ化できない場合は、日付以外の文字が入力されていないか、確認してみましょう。
また、ピボットテーブルは表からも作成は可能ですが、データを追加した場合は、「データソースの変更」を行う必要があります。
「データソースの変更」から参照している表の範囲を変更していなかった場合は、ピボットテーブルに反映されません。
データの一部分がグループ化されない場合は、「データソースの変更」から表の範囲を修正してください。
Dr.オフィスピボットテーブルを作成するときは、参照元の表をテーブルに変換しておこう！
表をテーブルに変換しておくと、データを追加するとデータ範囲が自動的に拡張されます。
- テーブルにデータを追加
- 「ピボットテーブル分析」のタブをクリック
- 「更新」を押す
テーブルに変換したデータからピボットテーブルを作成した場合は、「更新」をクリックするとピボットテーブルにデータが追加されて、すべてのデータをグループ化することができます。
空白セルの文字を表示しない方法とグループごとに空白行を挿入する手順
元のテーブルに空白行があった場合、そのテーブルを元にピボットテーブルを作成すると「(空白行)」という文字が表示されてしまいます。
「(空白行)」と表示されてしまった場合は、元のテーブルに空白行が挿入されていないか、確認してください。
空白を除いて集計したい場合は、元のデータの空白行をなくしてからピボットテーブルを作成する
ピボットテーブルに「(空白行)」と表示されないようにしたい場合は、テーブルに挿入されている不要な空白行を削除しておきましょう。
テーブルの空白行を削除すると、ピボットテーブルに「(空白行)」を表示されなくなります。
グループごとに空白行を挿入して見やすくする
各グループごとに空白行を挿入して、ピボットテーブルを見やすくすることもできます。
上記のように、各グループの下に空白行を追加する手順を解説していきます。
STEP
ピボットテーブル内の任意のセルを選択
ピボットテーブル内にある任意のセルをクリックしてください。
STEP
「各アイテムの後ろに空行を入れる」を選択
- 「デザイン」タブをクリック
- 「空白行」のアイコンを押す
- 「各アイテムの後ろに空行を入れる」をクリックする
STEP
空白行が挿入された
各グループの下に空白行を挿入することができました。
集計の表示・非表示を切り替える方法
ピボットテーブルに表示されている「集計」の表示は、非表示に切り替えることができます。
必要に応じて、表示・非表示を切り替えてみてください。
小計を表示しない
小計の表示を非表示にする手順を解説します。
STEP
「デザイン」タブから「小計を表示しない」を選択
- ピボットテーブル内の任意のセルをクリックする
- 「デザイン」タブをクリック
- 「小計」のアイコンを押す
- 「小計を表示しない」を選択
STEP
「小計」が非表示になった
「小計」が非表示になり、「総計」のみが表示されるようになりました。
小計が表示される位置を指定する
「小計」を表示する位置は、数値の上部または下部を選択できます。
- ピボットテーブルの範囲内のセルを選択
- 「デザイン」タブをクリック
- 「小計」のアイコンを押す
- 「すべての小計をグループの末尾に表示する」または「すべての小計をグループの先頭に表示する」のいずれかを選択
「末尾」を選択すると、上記の画像のように小計が各グループの末尾に表示され、「先頭」を選択すると各グループの先頭部分に小計が表示されます。
総計を表示しない
「総計」も基本の操作は「小計」のときとほとんど同じです。
「総計」を非表示にする場合も、「デザイン」タブから操作を行います。
STEP
「行と列の集計を行わない」を選択
- ピボットテーブルの範囲内にあるセルをクリックする
- 「デザイン」タブをクリック
- 「総計」のアイコンを押す
- 「行と列の集計を行わない」を選択
STEP
総計の表示がなくなった
総計の表示がなくなり、小計だけが表示されている状態になりました。
総計の表示方法を変更する
「総計」の表示方法は、以下の3つに切り替えることができます。
「行と列の集計を行う」
「行と列の集計を行う」を選択すると、行方向、列方向の両方に総計が表示されます。
「行のみ集計を行う」
「行のみ集計を行う」を選択すると、表の右側に「総計」が表示されます。
「列のみ集計を行う」
「列のみ集計を行う」を選択すると、表の下に「総計」が表示されます。
データの集計の目的に合わせて、いずれかの表示方法を選択してみてください。
エクセルのピボットテーブルの集計に関するQ＆A
思い通りの集計をするために基本操作をマスターしよう！
ピボットテーブルを使って集計を行う場合、「行」のボックスに挿入するのか、「列」のボックスに挿入するのかによって、同じフィールドを使っていても表の形状が変わるので、用途に合わせて変更してみてください。
また、複数箇所を同時に変更すると、どの部分が変わったのかがわかりにくくなってしまうため、ボックスに挿入するフィールドの種類や、デザインなどを変更したい場合は、1箇所ずつ変更することをおすすめします。
最後に、ピボットテーブルを使って思い通りの集計をするために、覚えておきたい機能をおさらいしておきましょう。
おさらい
- 「合計」以外の数値に変更する方法
- オリジナルの計算式を追加して集計する手順
- 数値をグループ化して見やすくする方法
- 集計行の表示・非表示の切り替え方
ピボットテーブルは、ちょっとした操作で見た目が変わってしまうため、「何をどうしたいか」を理解した上で操作するのがポイントです。
今回紹介した機能の使い方を参考にして、ピボットテーブルで思い通りの集計を行ってみてください。
エクセルのピボットテーブルは、他にも関連記事があります。
ピボットテーブルの基本操作をわかりやすく解説していますので、こちらの記事もあわせてご覧ください。