こんにちはこんにちは。ECF Tech担当
Michiharu.Tです。
今回はExcelのピボットテーブルの基本的な使い方についてまとめてみました。ピボットテーブルを使うことで大量データの計算、集計や分析が簡単にできます。本記事では次のような売上一覧表を元データとして、ピボットテーブルを作成してみたいと思います。記事に合わせて実際に試してみたい方は、こちらからExcelファイルのダウンロードができます。
ピボットテーブルの作成
作成するには、メニューから「挿入」→ 「ピボットテーブル」を選択します。
下のようなウィンドウが表示されます。
- ①ピボットテーブルを「新規ワークシート」に作成します。
- ②テーブル/範囲のボックスをクリックすると、ピボットテーブルにする対象データを選択できます。下のように項目名を含めて範囲選択します。
「ピボットテーブルのフィールド」というウィンドウが表示されます。
- ①商品名、売上金額、顧客名のフィールドを選択しています。
- ②自動的にフィルター、行、列、値のいずれかに配置されます。
下のようなピボットテーブルが表示されました。
商品名ごとに売上金額が合計されています。細目は顧客名ごとになっています。
フィルター、行、列、値の各エリアにあるフィールドを操作して、動作を見てみましょう。「ピボットテーブルのフィールド」が表示されていない場合は、ピボットテーブルの部分をクリックします。フィールドをドラッグ＆ドロップすることで、移動できます(下図)。
表の見た目を変更
いくつかのパターンを見てみましょう。各設定値は下記エリアのどこに項目が入っているかを示しています。
商品名を細目とし、顧客名で集計
(設定値)
- 行
- 顧客名
- 商品名
- 値
- 合計/売上金額
※行に複数の項目がある場合、上に設定されている項目が最も大きな集計単位となります。
顧客名を行、商品名を列としたクロス集計
(設定値)
- 行
- 顧客名
- 列
- 商品名
- 値
- 合計/売上金額
商品名を行、顧客名を列としたクロス集計
(設定値)
- 行
- 商品名
- 列
- 顧客名
- 値
- 合計/売上金額
フィルターの設定
次にフィルターを設定してみましょう。「日付」フィールドをクリック(下図①)します。下図のように行エリアにいくつかの項目が追加されます(下図②)。
今回は「月(日付)」のみをフィールドとして残します。「日付」と「日(日付)」は、各項目をクリック後下図のようにして削除します。
月(日付)をフィルターエリアにドラッグしてみましょう。表に下図のようなフィルターが追加されます。
図中①部分をクリックすると、ウィンドウが開きます。今回は「複数のアイテムを選択」(図中②)をクリックして、1月～3月を選んでみたいと思います。
売上の内容が1月～3月の期間のものに変化します。
集計方法の変更
「合計/売上金額」のフィールドをクリックするとメニューが表示されます。「値フィールドの設定」をクリックします(下図)。
下図のウィンドウが開きます。「選択したフィールドのデータ」から「個数」を選んで「OK」をクリックします。
ピボットテーブルの値が変わります。例えば下図の赤囲みの部分は、おかしのたなかがいちごキャンディーを購入した件数が3件あったことを示しています。
他にも平均や最大、最小などの値を使用することができます。
値のメニュー
各値を右クリックすると、様々なメニューが開きます(下図)。いくつかをご紹介します。
※以降の画面は「値のフィールド」を「合計」に戻した状態で行っています。
詳細の表示
「詳細の表示」を選択すると別シートが作成され、下図のような詳細が表示されます。下図は「にじいろスーパー」と「いちごキャンディ」が交差するセルをクリックした際に表示されたものです。３件の売上金額を合計していたことが確認できます。
値の集計方法
「値の集計方法」を選択すると、様々な集計方法を選択できます。ここでは列集計に対する比率を選択してみます(下図)。
ピボットテーブルの表示が切り替わります。例えば下図の赤囲み部分は、あんドーナツの売上の28.57%を、おかしのたなかが占めていることを表しています。
並べ替え
「並べ替え」を使用すると各項目を利用した並べ替えができます。あんドーナツの列を右クリックしてメニューを表示し、並べ替えで降順を選択すると、下の表のようになります。
あんドーナツ列の降順で表全体が並べ替えされています。
ピボットテーブル分析
ピボットテーブル分析のメニューから、分析に役立つ様々な機能が使用できます。
スライサーの挿入
スライサーを使用すると、様々な切り口で表を見ることができます。「ピボットテーブル分析」から「スライサー」を選択します。
今回は「月(日付)」を使用します。
下図①のようなウィンドウが表示されます。「2月」を選ぶと表全体が2月の売上に限定されて表示されるようになります。
ピボットテーブルの使い方を簡単にまとめてみました。まだまだ紹介しきれていない機能がたくさんありますが、徐々に更新しながら増やしていけたらと思っています。ひき続き、Excelの学習にお役立て頂けますと嬉しいです。
