ピボットテーブル、おすすめピボットテーブルの使い方[Excelの基本操作(上級)]
事前準備
図のようにエクセルに値を入力してください。
※E4、E5の左上の緑のマークはエクセルの「おせっかい」機能ですのでエラーではありません。
データを入力するのが大変な方は下記からダウンロードできます。
excel_basicoperation_al_2.xlsx 11.1 KB (11,402 バイト)
操作対象
リボンの「挿入」タブの緑線で囲まれた部分を使用します。
ピボットテーブルの作成
B2からG15のセルを範囲で選択します。
次に「ピボットテーブル」アイコンをクリックします。すると「ピボットテーブルの作成」ウインドウが表示されますので図のように入力してください。
OKボタンを押すと新規シート(Sheet2)が作成されてシート内は図のように表示されます。
次に右側にある「ピボットテーブルのフィールド」の「役職」「社員氏名」「標準報酬」のチェックボックスをオンにします。※必ず「役職」「社員氏名」「標準報酬」の順番でチェックして下さい。
自動的にピボットテーブルが図のようになります。
これでピボットテーブルが作成できました。次は操作方法となります。
メモ
ピボットテーブルツールの分析/デザインの操作
ピボットテーブルの任意のセルを選択するとリボンの一番右にピボットテーブルツールが表示されます。
[分析]
番号を振りましたので順に説明していきます。
※ドリルダウン/ドリルアップは次回の記事で説明します。
[デザイン]
デザインの操作は初級、中級から上級へ進んで来た方にとっては難しくありませんので、適宜、触ってくださいね。
1.ピボットテーブル
「ピボットテーブル名」は名前を変更できます。
「オプション」をクリックすると「ピボットテーブル オプション」ダイアログが表示されてピボットテーブルの設定をする事が可能です。※初めての方や不慣れの方はこの設定は一番後回しで先にピボットテーブルの操作に慣れましょう。
「レポートフィルターページの表示」は記事の最下部で解説します。
「GetPivotDataの生成」に「レ」がある場合には、数式を作成する際にピボットテーブルを選択すると関数のGETPIVOTDATA()が自動生成されます。
A2のセルを選択して「=」を入力します。次にB4を選択すると自動的にGETPIVOTDATAが生成されます。
2.アクティブなフィールド
選択したアクティブな「フィールドの名前」を変更します。
3.フィールドの設定
任意のセルを選択して「フィールドの設定」をします。
4.フィールドの展開/フィールドの折りたたみ
フィールドを「展開・折りたたみ」ます。
5.グループの選択/グループの解除
A7、A8を選択して「グループの選択」をクリックします。するとグループが作成されます。
次にA8を選択して右クリックを押します。
「社員氏名2」の小計をクリックします。
グループ1の小計(420,000)が表示されました。グループはこのように使います。
グループの確認が終わったら、A8を選択して「グループの解除」を押してグループを解除します。
6.スライサー
スライサーをクリックします。「スライサーの挿入」ウインドウが表示されますので「役職」をオンにしてください。
OKボタンを押すとスライサーが表示されますので「一般」をクリックします。
ピボットテーブルのデータが「一般」に絞り込まれました。
スライサーの設定を解除するには右上にあるアイコンをクリックします。また、スライサーを削除するにはスライサーの枠を選択して「Deleteキー」を押します。
7.タイムラインの挿入
タイムラインをクリックします。「タイムラインの挿入」ウインドウが表示されますので「入社年月日」をオンにしてください。
OKボタンを押すとタイムラインが表示されますので「2015年4月」をクリックします。
ピボットテーブルのデータが「入社年月日が2015年4月」に絞り込まれました。
タイムラインの設定を解除するには右上にあるアイコンをクリックします。また、タイムラインを削除するにはタイムラインの枠を選択して「Deleteキー」を押します。
8.フィルターの接続
「フィルターの接続」をクリックすると先程作成したスライサーとタイムラインが表示されます。
※この時点でスライサーとタイムラインを削除してもOKです。
9.更新(更新/すべて更新)
Sheet1の「サブリーダー」を「主任」に変更します。次にSheet2でピボットテーブルを選択して「すべてを更新」をクリックします。
このようにピボットテーブルの元のデータを変更した場合はピボットテーブル側で「更新」または「すべてを更新」します。
10.データソースの変更
ピボットテーブルの元データを変更します。
11.アクション(クリア/選択/ピボットテーブルの移動)
ピボットテーブルをクリア、選択、移動する事ができます。※誤ってクリアした場合は、右側にある「ピボットテーブルのフィールド」から「役職、社員氏名、標準報酬」の順番でオンにします。
12.フィールド/アイテム/セット
ここでは緑枠で囲まれた部分を解説します。
※セットに関しては「ピボットテーブルで複数のテーブルを使用する」でご紹介します。
[集計フィールド]
「集計フィールド」をクリックすると「集計フィールドの挿入」ダイアログが表示されますので図のように入力してください。
OKボタンを押すと「合計/給与」というフィールドが追加されました。
[集計アイテム]
A5を選択して「集計アイテム」をクリックすると「社員氏名への集計アイテムの挿入」ダイアログが表示されますので図のように入力してください。
OKボタンを押すと「予備費」というアイテムが追加されました。
[解決の順序]
「解決の順序」をクリックすると「集計アイテムの解決の順序」ダイアログが表示されますので「予備費」を選択して削除を押します。
[数式の一覧表]
新しいシートが作成されて「集計フィールド、集計アイテム」の数式などが表示されます。
13.ピボットグラフ
「ピボットグラフ」アイコンをクリックすると「グラフの挿入」ダイアログが表示されます。
「縦棒」を選択してOKボタンを押します。すると縦棒のグラフが表示されます。グラフの移動、サイズ変更、削除などは以前に解説したオブジェクトと同様の操作となります。
ピボットグラフが通常のグラフと異なるのはフィルターが操作できる点です。※グラフの左下にある役職や社員氏名をフィルターで操作できます。
14.おすすめピボットテーブル
ピボットテーブルの形式を変更する事が出来ます。
15.表示(フィールドリスト/±ボタン/フィールドの見出し)
フィールドリスト(右側にある「ピボットテーブルのフィールド」)、±ボタン、フィールドの見出しの表示設定です。
ピボットテーブルのフィールド/値フィールドを右クリック
A4のセルを右クリック(フィールド)
図のようなポップアップメニューが表示されます。
「移動」はフィールドの上下左右の順序を移動する事が出来ます。その他の項目は前述した通りです。
B4のセルを右クリック(値フィールド)
図のようなポップアップメニューが表示されます。
「値の集計方法」「計算の種類」は「値フィールドの設定」で同様の操作が可能です。
「詳細の表示」をクリックすると新しいシートに詳細情報が表示されます。作成されたシートは削除しても構いません。
フィールドリストの操作
フィールドリストとは右側にある「ピボットテーブルのフィールド」の事です。番号を振りましたので順番に解説していきます。
※「その他のテーブル...」はクリックすると「データモデルを使用しないと複数のテーブルは使用できません。」というエラーメッセージが表示されるだけです。関連記事：「ピボットテーブルで複数のテーブルを使用する」
事前準備
ピボットテーブルを図のように初期値に戻してください。
1.フィールドの上で右クリック
「役職」の上で右クリックをして「列レベルに追加」を押します。
するとピボットテーブルが図のように変化します。
このように「行ラベルに追加/列ラベルに追加/値に追加」はピボットテーブルのデータ形式を変更する事が出来ます。
[レポートフィルターに追加]
今度は「社員氏名」の上で右クリックをして「レポートフィルターに追加」を押します。すると役職毎に標準報酬が合計されました。
「分析」タブの一番左にある[ピボットテーブル][オプション][レポートフィルターページの表示]をクリックすると社員全員の名前でシートが作成されます。※社員氏名のフィルターが「すべて」になっていますので絞り込む事も可能です。また、作成されたシートは削除しても構いません。
2.ボックス間でフィールドをドラッグ
ピボットテーブルを図のように初期値に戻してください。
図のように「行」にある「役職」を列にドラッグします。ドラッグは役職の上でマウスの左クリックを押したまま、マウスを「列」へ移動してマウスを離します。
するとピボットテーブルが図のように変化します。
ボックス間でフィールドをドラッグするとピボットテーブルのデータ形式が変化しますので適宜、試してくださいね。
おすすめピボットテーブルの作成
「おすすめピボットテーブル」アイコンをクリックすると自動でピボットテーブルを生成してくれます。
メモ
これでピボットテーブルの基本操作(98%)は完了となります。この記事で説明がなかった箇所は「ピボットテーブルのドリルダウン、ドリルアップ」と「ピボットテーブルで複数のテーブルを使用する」で解説します。
エクセル講座のクイックリンク
関連記事
