Level4 データ分析｜クエスト3
売上明細が増えて、担当者ごとの売上を計算するのが大変です……。
数式を入力せずに、項目ごとの合計をまとめる方法はありませんか？
売上明細から担当者別や商品別の売上を確認するとき、項目ごとに数式を入力して集計するのは大変です。
データの件数が増えると、数式を作るだけでなく、集計する条件を変更するたびに計算し直す必要もあります。
このようなときに役立つのが、ピボットテーブルです。
ピボットテーブルを使うと、数式を入力しなくても、売上などのデータを担当者別・商品別・期間別に集計できます。
集計に使用する項目を［行］［列］［値］［フィルター］へ配置することで、同じ売上明細からさまざまな集計表を作成できます。
この記事では、売上明細からピボットテーブルを作成し、担当者別や商品別に売上を集計する基本操作を解説します。
また、売上金額の合計を求める方法だけでなく、売上データの個数を数える方法や、元データを変更したあとの更新方法も確認します。
売上明細からピボットテーブルを作成し、項目の配置を変更しながら、担当者別や商品別に売上を集計できるようになりましょう。
ピボットテーブルとは
ピボットテーブルとは、表に入力されている大量のデータを、項目ごとに集計できる機能です。
たとえば、次のような売上明細があるとします。
通常の表では、売上が1件ずつ記録されているため、明細を見ただけでは担当者別や商品別の合計をすぐに確認できません。
ピボットテーブルを使うと、同じ売上明細から次のような集計表を簡単に作成できます。
- 担当者別の売上合計
- 商品別の売上合計
- 担当者と商品を組み合わせた売上合計
- 指定した期間の売上合計
- 担当者別や商品別の売上件数
数式を1つずつ入力する必要はありません。
集計に使用する項目を選び、配置する場所を指定することで、合計やデータの個数を求められます。
項目の配置を変えて集計できる
ピボットテーブルでは、同じ売上明細を使いながら、集計する項目を入れ替えられます。
たとえば、担当者ごとの売上を確認したい場合は、担当者名を集計の基準にします。
集計の基準を商品名へ入れ替えると、商品ごとの売上を確認できます。
さらに、担当者と商品を縦方向と横方向に配置すれば、どの担当者が、どの商品を、いくら売り上げたのかを組み合わせて確認できます。
ピボットには「軸を中心に回転する」という意味があります。ピボットテーブルでは項目の配置を入れ替えることで、同じデータをさまざまな切り口から集計できます。
元データを残したまま集計表を作成できる
ピボットテーブルを作成しても、元の売上明細が書き換えられることはありません。
売上明細とは別に集計結果が作成されるため、元データを残したまま、目的に合わせた集計表を確認できます。
また、ピボットテーブル内の項目を入れ替えても、元データの並び順や内容は変わりません。
ピボットテーブルが向いている場面
ピボットテーブルは、次のような場面に向いています。
- 売上明細から担当者別の売上を確認したい
- 商品ごとの売上や販売件数を比較したい
- 月ごとの売上を集計したい
- 集計する項目を切り替えながらデータを確認したい
- データの件数が多く、手作業での集計が難しい
一方で、ピボットテーブルを正しく作成するには、元データが集計しやすい形に整理されている必要があります。
元データには、1行目に見出しを入力し、1行に1件のデータを記録します。表の途中に空白行や空白列を入れず、セル結合も使用しません。
ピボットテーブルを作成する前に元データを整える方法は、次の記事で詳しく解説しています。
Excelでピボットテーブルを作成する基本手順
ここからは、売上明細を使ってピボットテーブルを作成します。
今回は、次の項目が入力された売上明細を使用します。
元データの詳しい内容は、あとで担当者別・商品別・期間別の集計に使用します。
1．集計したいデータ全体を選択する
データ全体を選択しましょう。
表の途中に空白行や空白列がなければ、表全体をドラッグして選択しなくても、Excelが連続したデータ範囲を認識します。
2．［挿入］タブをクリックする
売上明細内のセルを選択したまま、リボンの［挿入］タブをクリックします。
［挿入］タブには、テーブルやグラフなどを作成するためのボタンが用意されています。
3．［ピボットテーブル］をクリックする
［挿入］タブの［ピボットテーブル］をクリックします。
クリックすると、［テーブルまたは範囲からのピボットテーブル］画面が表示されます。
［既存のワークシート］を選択すると、元データと同じシート内にもピボットテーブルを配置できます。今回は元データと集計結果を区別しやすくするため、新しいワークシートへ作成します。
［テーブルまたは範囲］に、売上明細の範囲が指定されていることを確認します。
- 売上明細の見出しが含まれている
- 最後の行まで指定されている
- 必要のない空白部分が含まれていない
4．空のピボットテーブルを確認する
［OK］をクリックすると、新しいワークシートが追加されます。
新しいワークシートには、次の2つが表示されます。
- 空のピボットテーブル
- ［ピボットテーブルのフィールド］作業ウィンドウ
［ピボットテーブルのフィールド］には、元データの1行目に入力した「日付」「担当者」「商品」「売上」が表示されます。
この一覧に表示される項目を、フィールドと呼びます。
フィールド一覧が表示されない場合
ピボットテーブルを作成しても、画面右側に［ピボットテーブルのフィールド］が表示されないことがあります。
その場合は、空のピボットテーブル内をクリックしてください。
それでも表示されない場合は、次の方法で表示できます。
- ピボットテーブル内をクリックする
- ［ピボットテーブル分析］タブをクリックする
- ［フィールド リスト］をクリックする
フィールド一覧では、項目の追加・移動・削除ができます。
空のピボットテーブルを作成しただけでは、まだ集計結果は表示されません。
行・列・値・フィルターの役割
ピボットテーブルでは、元データの見出しをフィールドとして使用します。
今回の売上明細では、次の項目がフィールド一覧に表示されています。
- 日付
- 担当者
- 商品
- 売上
これらのフィールドを、画面右下にある次の4つのエリアへ配置して集計表を作成します。
- フィルター
- 列
- 行
- 値
それぞれの役割を確認しましょう。
［行］は項目を縦方向に並べる
［行］には、集計結果を縦方向に分類したいフィールドを配置します。
たとえば、［担当者］を［行］へ配置すると、担当者名がピボットテーブルの上から下へ並びます。
［担当者］を［行］へ配置すると、ピボットテーブルの左側に担当者名が縦方向に表示されます。
フィールドを配置するには、フィールド名を目的のエリアまでドラッグします。フィールド名をクリックしたまま動かし、目的のエリアでボタンを離してください。
［列］は項目を横方向に並べる
［列］には、集計結果を横方向に分類したいフィールドを配置します。
たとえば、［商品］を［列］へ配置すると、商品名がピボットテーブルの左から右へ並びます。
［担当者］を［行］、［商品］を［列］へ配置すれば、担当者と商品の組み合わせごとに集計結果を確認できます。
- 縦方向：担当者
- 横方向：商品
このように、2つの項目を組み合わせて確認したいときに［行］と［列］を使います。
1つの項目だけで集計する場合は、必ずしも［列］を使用する必要はありません。最初は［行］と［値］だけでも集計表を作成できます。
［値］は合計やデータの個数を求める
［値］には、合計やデータの個数などを求めたいフィールドを配置します。
今回の売上明細では、［売上］を［値］へ配置します。
［担当者］を［行］、［売上］を［値］へ配置すれば、担当者ごとの売上合計を求められます。
［値］では、主に次のような集計ができます。
- 数値を合計する
- データの個数を数える
- 平均を求める
- 最大値や最小値を求める
この記事では、基本となる合計とデータの個数を使用します。
［売上］を［値］へ配置したときに「売上の合計」ではなく「売上のデータの個数」と表示されることがあります。
値フィールドの設定から集計方法を修正しましょう。
［フィルター］は表示するデータを絞り込む
［フィルター］には、条件を指定して集計結果を絞り込みたいフィールドを配置します。
たとえば、［日付］を［フィルター］へ配置すると、確認したい日付を選択できます。
特定の日付を選択すれば、その日付のデータだけを使った集計結果へ切り替えられます。
今回の売上明細では、次のような使い方ができます。
- 表示する日付を選択する
- 特定の担当者だけに絞り込む
- 特定の商品だけに絞り込む
4つの役割を整理する
［行］［列］［値］［フィルター］は、次のように使い分けます。
- 行：項目を縦方向に並べる
- 列：項目を横方向に並べる
- 値：合計やデータの個数を求める
- フィルター：表示するデータを条件で絞り込む
4つのエリアを最初からすべて使用する必要はありません。
まずは［行］に分類する項目、［値］に計算する項目を配置すれば、基本的な集計表を作成できます。
担当者別の売上を集計する
最初に、売上明細から担当者ごとの売上合計を求めます。
今回は、フィールドを次のように配置します。
- ［行］：担当者
- ［値］：売上
［担当者］を縦方向へ並べ、担当者ごとに［売上］の合計を求める集計です。
［担当者］を［行］へ、［売上］を［値］へ配置します。
［値］に「合計 / 売上」と表示されていることを確認しましょう。
「データの個数 / 売上」と表示された場合は、売上金額ではなくデータの件数が集計されています。
ピボットテーブルに、次の内容が表示されていることを確認します。
- 左側に担当者名が並んでいる
- 担当者ごとの売上合計が表示されている
- 最後の行に総計が表示されている
総計には、すべての担当者の売上を合計した金額が表示されます。
担当者別の売上件数を求める
同じフィールド配置でも、［売上］の集計方法を「合計」から「データの個数」へ変更すると、担当者ごとの売上件数を確認できます。
- ［行］：担当者
- ［値］：売上のデータの個数
売上金額の合計ではなく、各担当者に何件の売上データがあるのかが表示されます。
集計する内容を変更するときは、数式を作り直すのではなく、フィールドの配置や集計方法を変更します。
集計方法と表示形式を整える
［売上］を［値］へ配置すると、通常は売上金額の合計が表示されます。
しかし、ピボットテーブルでは合計だけでなく、データの個数・平均・最大値・最小値など、目的に合わせて集計方法を変更できます。
この記事では、実務でよく使用する次の2つを確認します。
- 合計：担当者別や商品別の売上金額を合計する
- データの個数：担当者別や商品別の売上件数を数える
集計方法を変更する
フィールドを次のように配置したピボットテーブルを使用します。
- ［行］：担当者
- ［値］：売上
- ［値］に配置されている［売上］の右側にある［▼］をクリックする
- ［値フィールドの設定］をクリックする
- ［集計方法］から［データの個数］を選択する
- ［OK］をクリックする
［OK］をクリックすると、合計やデータの個数に変更できます。
［データの個数］は、そのフィールドに値が入力されているデータを数えます。そのため、売上欄に空白がある場合は、そのデータは個数に含まれません。
数値フィールドは通常「合計」で集計されますが、Excelが元データを文字列として認識した場合は「データの個数」になることがあります。
集計方法を［合計］へ変更できたとしても、元データの内容が統一されていないと、正しい集計結果にならない可能性があります。
- 数字が文字列として入力されている
- 金額の代わりに文字が入力されている
- 数値と文字列が混在している
売上金額に桁区切りを設定する
売上金額が「120000」のように表示されていると、金額を読み取りにくくなります。
［値フィールドの設定］から表示形式を変更し、「120,000」のように桁区切りを表示しましょう。
- ［値］にある［売上］の［▼］をクリックする
- ［値フィールドの設定］をクリックする
- ［表示形式］をクリックする
- ［数値］を選択する
- ［桁区切り（,）を使用する］にチェックを入れる
- 小数点以下の桁数を「0」にする
- ［OK］をクリックする
- ［値フィールドの設定］画面でも［OK］をクリックする
［OK］をクリックすると、ピボットテーブル内の売上金額に桁区切りが表示されます。
元データを変更したらピボットテーブルを更新する
ピボットテーブルを作成したあとに元データを修正しても、集計結果は自動では切り替わりません。
次のような変更を行った場合は、ピボットテーブルを更新する必要があります。
- 売上金額を修正した
- 担当者名や商品名を修正した
- 新しい売上明細を追加した
- 空白だったセルへデータを入力した
ここでは、元データの売上金額を修正し、ピボットテーブルへ反映してみましょう。
1．元データを修正する
元の売上明細があるワークシートを開き、売上金額を修正します。
今回は、いずれかの売上金額を別の金額へ変更してください。
売上金額を修正しただけでは、ピボットテーブルの集計結果は変わりません。
2．ピボットテーブル内をクリックする
ピボットテーブルがあるワークシートへ戻り、集計表内のセルをクリックします。
ピボットテーブル内を選択すると、リボンに［ピボットテーブル分析］タブが表示されます。
3．［更新］をクリックする
［ピボットテーブル分析］タブを開き、［更新］をクリックします。
更新すると、修正した元データを使って集計結果が再計算されます。
- ピボットテーブル内のセルをクリックする
- ［ピボットテーブル分析］タブをクリックする
- ［更新］をクリックする
ピボットテーブル内を右クリックし、表示されたメニューから［更新］を選択する方法でも更新できます。
同じブックに複数のピボットテーブルがある場合は、［更新］のメニューにある［すべて更新］を使うと、まとめて更新できます。
4．集計結果を確認する
更新後のピボットテーブルを確認し、修正した売上金額が担当者別の合計や総計へ反映されていることを確認しましょう。
更新しても追加したデータが反映されない場合は、次の点を確認してください。
- 追加した行が集計範囲に含まれているか
- 元データと追加した行の間に空白行がないか
- ピボットテーブルのデータソースが正しく設定されているか
まとめ｜ピボットテーブルでデータを項目別に集計しよう
ピボットテーブルを使うと、数式を入力しなくても、売上などの明細データを項目別に集計できます。
担当者別・商品別・期間別など、確認したい内容に合わせてフィールドの配置を変更できる点が、ピボットテーブルの特徴です。
- ピボットテーブルは、明細データを項目別に集計する機能
- ピボットテーブルを作成できる
- 集計方法を変更すると、売上金額の合計や売上件数を確認できる
- 元データを追加・修正したあとは、ピボットテーブルを更新する
分類したい項目は［行］や［列］へ、計算したい項目は［値］へ、表示を絞り込みたい項目は［フィルター］へ配置しましょう。
次のクエストへ進もう
ピボットテーブルを使うと、明細データを目的に合わせて集計できます。
次のクエストでは、ピボットテーブルの集計結果からピボットグラフを作成し、データを視覚的に分析する方法を学びましょう。
Level4の学習順序を確認したい方は、Level4攻略フローをご覧ください。
コメント