Excelでの作業のほとんどは、その特徴を活かした、「データ管理」にあると言えます。
データ管理には、表を作成して目的の数値を求める「集計」作業や「分析」作業が多く、事務職などにお勤めの場合は、特に重要なスキルとなるものでしょう。

ただし、ここで重要になるのが集計の方法です。

Excelでは様々な集計の方法があり、それこそ様々な方法で「正しい数値」を求めることはできますが、問題は表が大きくなればなるほど、効率よく求めるようとすると難しく見える場合があるという点です。

そのような時に活躍するのが今回の、「ピボットテーブル」です。
この方法知っておくと、 業務の効率やエクセルの分析スキルが著しく向上しますので、ぜひ覚えておきましょう。

 

ピボットテーブルでデータを集計する

下の表は、ある全国展開をしているパソコンショップにおける、4~6月度の売上リストです。
この表から、『各支店ごとで商品別の売上が知りたい』と上司から依頼された場合、一見して集計作業はとても骨が折れるように感じます。

そこで、ピボットテーブル機能を利用して、必要なデータを抽出・集計できるようにしてみましょう。

%e5%85%83%e3%83%87%e3%83%bc%e3%82%bf

 

使い方例1)ピボットテーブルの作成

ピボットテーブルを作成する為には次の(1)~の手順で行います。

(1)表内の任意のセルを選択します。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e4%bd%9c%e6%88%901

 

(2)次の手順でピボットテーブルを開始します。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e4%bd%9c%e6%88%902

①エクセル上部の[挿入]タブをクリックし、リボンの表示を切り替えます。
②左側にある「ピボットテーブル」をクリックします。

 

(3)新しく表示された「ピボットテーブルの作成」ダイアログボックスにて次の設定を行います。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e4%bd%9c%e6%88%903

①「テーブル/範囲」欄にて、元となる表の範囲が自動的に選択されているか確認します。

→※違っていれば、正しい範囲をマウスで選択し直します。

②「新規ワークシート」を選択します。

→※現在のシート内に作成したい場合は、「既存の・・・」を選択してください。

③[OK]ボタンを押して設定を完了します。

 

(4)新しいシートが自動的に作成され、下のようなピボットテーブル用のシートが表示されます。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e4%bd%9c%e6%88%904

sponsored link

 

使い方例2)ピボットテーブルの構成について

ピボットテーブルの画面構成と、使い方について確認しておきましょう。

(1)画面構成
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e6%a7%8b%e6%88%901

①【ピボットテーブル】

右の欄で設定した構成を元に、自動的に集計された表の内容が表示されます。

②【フィールドリストエリア】

元の表で行や列のタイトルとして使用していた、各項目(※「売上日」や「支店」など)が『アイテム』として表示されます。この「アイテム」を下にある各設定エリア、又は、ON/OFFすることで、表を再構成することが出来ます。

③【レポートフィルターエリア】

この欄に配置したアイテムを元に、ピボットテーブル(表)が集計されます。

④【列エリア】

この欄に配置したアイテムの内容が、ピボットテーブルの各列の項目名となります。

⑤【行エリア】

この欄に配置したアイテムの内容が、ピボットテーブルの各行の項目名となります。

⑥【値エリア】

この欄に配置したアイテムの内容が、ピボットテーブルにて実際に集計される数値となります。

 

(2)使い方

フィールドリストエリアから、項目(アイテム)を、下にある各エリアにドラッグして、実際に作りたい表の行や列、集計する値を決定します。

 

以上が、ピボットテーブルの説明となります。

 

使い方例3)ピボットテーブルでの集計

では、実際にピボットテーブル機能を使って、『各支店ごとで商品別の売上』を集計してみましょう。

(1)フィールドリストエリアから、「支店」の項目をマウスで行エリアまでドラッグして配置します。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e9%9b%86%e8%a8%881

 

(2)ピボットテーブルが変化して、各行に「支店」の名称が表示されました。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e9%9b%86%e8%a8%882

 

(3)同じように、次のアイテムもそれぞれドラッグします。

①「商品名」を列エリアにドラッグします。
②「金額」を値エリアにドラッグします。

 

(4)各列には「商品名」の名前、表の内容には「金額」が表示され、自動的に集計されました。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e9%9b%86%e8%a8%884

 

POINT!

あやまって違ったアイテムをドラッグしたりなど間違った場合は、次のような操作で、配置したアイテムをOFF、設定をやり直すことが出来ます。

%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e9%9b%86%e8%a8%88point%e9%a0%85%e7%9b%ae%e5%89%8a%e9%99%a4

 

ピボットテーブルのデータの範囲変更の方法

せっかく出来たピボットテーブルですが、下のようにピボット作成後に元となる表にデータを追加しました。

データの追加
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e7%af%84%e5%9b%b2%e5%a4%89%e6%9b%b41

 

ところが、元の表にデータを追加したにも関わらず、ピボットテーブル自体の数値には反映されていません。

ピボットテーブルの状態

 

こういった場合は、ピボットテーブル自体の設定を変更する必要があります。

 

使い方例)データの範囲変更

元となるデータの範囲を変更し、ピボットテーブルに反映させましょう。

(1)次のように設定の変更を開始します。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e7%af%84%e5%9b%b2%e5%a4%89%e6%9b%b43

①エクセル上部の「ピボットテーブルツール」-[分析]タブをクリックし、リボンの表示を切り替えます。
②中ほどにある、「データソースの変更」ボタン(絵の方)をクリックします。

 

(2)新しく表示された「ピボットテーブルの移動」ダイアログボックスにて、設定を修正します。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e7%af%84%e5%9b%b2%e5%a4%89%e6%9b%b44

①「テーブル/範囲」欄に、正しい元データとなる表の範囲を入力、又はドラッグにて選択します。
②[OK]ボタンを押して設定を完了します。

 

(3)ピボットテーブルに追加した範囲のデータも反映されました。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e7%af%84%e5%9b%b2%e5%a4%89%e6%9b%b45

 

POINT!

操作中、もしエクセル上部に[分析]タブなどが表示されない場合は下のように操作してください。

%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e7%af%84%e5%9b%b2%e5%a4%89%e6%9b%b4point%e3%83%aa%e3%83%9c%e3%83%b3%e8%a1%a8%e7%a4%ba

 

ピボットテーブルのグラフデータの並び替え

ピボットテーブル機能には、表を思い通りに作り変えることの出来るほかに、『ピボットグラフ』という、その作成したピボットテーブルに対応したグラフを自動的に表示させる機能もあります。

次に、ピボットグラフの作り方から、そのデータの並べ替え編集までを見てみましょう。

 

使い方例1)ピボットグラフの作成

次の(1)~の手順でピボットテーブルを元にグラフを表示させてみましょう。

(1)ピボットグラフの作成を開始します。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%82%b0%e3%83%a9%e3%83%95%e8%a1%8c%e5%88%97-%e4%bd%9c%e6%88%901

①エクセル上部の「ピボットテーブルツール」-[分析]タブをクリックし、リボンの表示を切り替えます。
②右側にある、「ピボットグラフ」ボタンをクリックします。

 

(2)新しく表示される「グラフの挿入」ダイアログボックスから、今回は集合縦棒を選択し、[OK]ボタンを押してみます。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%82%b0%e3%83%a9%e3%83%95%e8%a1%8c%e5%88%97-%e4%bd%9c%e6%88%902

 

(3)ピボットテーブルに対応した、集合縦棒グラフが自動的に作成されます。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%82%b0%e3%83%a9%e3%83%95%e8%a1%8c%e5%88%97-%e4%bd%9c%e6%88%903

 

使い方例2)データの並べ替え

作成したグラフは、元のピボットテーブルを元に、「沖縄、大阪、東京、名古屋」の順で表示されています。
では、金額の「総計」でグラフを表示したい場合の操作を見てみましょう。

(1)ピボットテーブルの総計の数字を右クリックし、表示されたメニューから「並べ替え」-「降順」の順に選択します。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%82%b0%e3%83%a9%e3%83%95%e4%b8%a6%e3%81%b9%e6%9b%bf%e3%81%881

 

(2)ピボットテーブル、及び、グラフが「総計」の多い順に並べ替えられました。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%82%b0%e3%83%a9%e3%83%95%e4%b8%a6%e3%81%b9%e6%9b%bf%e3%81%882

 

このように、ピボットテーブルを変更すると、グラフ自体の表示も変化しますので、用途に合わせて活用しましょう。

 

ピボットテーブルでデータをグループ化する

下の表は、ピボットテーブルの列を「営業担当」、行を「売上日」として集計した結果です。
このような場合、日付ごとの集計ではなく、月ごとの集計にまとめる機能をピボットテーブルでは「グループ化」と言います。

%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab-%e3%82%b0%e3%83%ab%e3%83%bc%e3%83%97%e5%8c%961

 

使い方例)日付のグループ化

次の手順で月ごとの集計へとグループ化しましょう。

(1)日付の入力されているセルを右クリックして、メニューから「グループ化」を選択します。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab-%e3%82%b0%e3%83%ab%e3%83%bc%e3%83%97%e5%8c%962

 

(2)新しく表示される「グループ化」ダイアログボックスにて、次の設定をします。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab-%e3%82%b0%e3%83%ab%e3%83%bc%e3%83%97%e5%8c%963

①[単位]欄にて、「月」をクリックし選択します。
②[OK]ボタンを押して完了します。

 

(3)行の項目が月ごとにまとめられました。
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab-%e3%82%b0%e3%83%ab%e3%83%bc%e3%83%97%e5%8c%964

 

グループ化できないときってどんなとき?

単位ごとにグループ化できる機能は便利ですが、次のような時は使用出来ないため注意が必要です。

下の表は、先ほどと同じピボットテーブルですが、1点、日付の表示が少し違っています。
この表を月ごとにグループ化してみましょう。

元のピボットテーブル
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab-%e3%82%b0%e3%83%ab%e3%83%bc%e3%83%97%e5%8c%96%e4%b8%8d%e5%8f%af1

 

同じようにグループ化の操作を行おうとしたところ、下図のようなエラーメッセージが表示されました。

グループ化の操作に対するエラー
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab-%e3%82%b0%e3%83%ab%e3%83%bc%e3%83%97%e5%8c%96%e4%b8%8d%e5%8f%af2

 

ピボットテーブルでグループ化出来るのは日付などの数値のみですが、今回の場合の問題を元となるデータで見てみましょう。

ピボットテーブル元の表
%e3%83%94%e3%83%9c%e3%83%83%e3%83%88%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab-%e3%82%b0%e3%83%ab%e3%83%bc%e3%83%97%e5%8c%96%e4%b8%8d%e5%8f%af3

 

上図でわかるように、日付が入力されているセルの表示形式が「標準」、つまり文字列と認識されているのが問題でした。

繰り返しになりますが、グループ化するには表示形式の分類が「日付」でなければいけません。そのため、今回のように日付のような入力であっても、実際にエクセルが「日付」と認識していなければグループ化できないのです。