ピボットテーブルとショートカットキーを効率的に学ぶ講座の②です。
ピボットテーブルで活用したい機能について紹介していきましょう。
よかったらフォローしてください。→ハヤトのTwitter
1.ピボットテーブルとショートカットキーを効率的に学ぶ講座
2.ピボットテーブルで活用したい５つの機能。グループ化、フィルター他
3.Excelの印刷設定。１ページに収める・タイトルを全ページに表示
4.フィルターや置換機能でピボットテーブルのデータを修正
5.ピボットテーブルの集計や表示を変更して損益計算書をカスタマイズする
6.Vlookup関数を使ってデータを追加してピボットテーブルをカスタマイズする
これまでの内容
・ピボットテーブルについて知る
・ピボットテーブルを作成する元となるデータのルール
前の記事
→Excelピボットテーブルとショートカットキーを効率的に学ぶ講座①
講座①で作成したピボットテーブルを使ってください。
※この記事からスタートの場合、Pivotサンプルデータ02をダウンロ－ドして、シート「Sheet1」から初めてください。
目次
ピボットテーブルで活用したい５つの機能。グループ化、フィルター他
ここでやる内容
・千円単位の表示の設定
・ピボットテーブルのグループ化
・ピボットテーブルのフィールドの展開/折りたたみ
・ピボットテーブルの比率の計算
・ピボットテーブルの条件付き書式
・ウィンドウ枠の固定
・ショートカットキーでの操作
集計する列情報を変更する
列エリアの「部門名」を「月度」に変更してください。（マウスで操作）
このようにラベルを変更するだけで、簡単に集計軸を変更することが可能です。
数値を見やすくする
ピボットテーブルの値に「,(カンマ)」を入れます。
「,(カンマ)」が入っていない数字は資料として見づらいので、表記を変更しましょう。
「右クリックキー or (Shift + F10)」→「値フィールドの設定（N)」を選択します。
※値が表示されているどれかのフィールドを選択している必要があります。
「値フィールドの設定」ダイアログボックスが開きます。
左下の「表示形式（Alt + N)」を選択し、「セルの書式設定」のダイアログボックスを開きます。
「分類（Alt + C）」でリストボックスをアクティブにし、「↓キー」で数値を選択し、「桁区切り（,)を使用する（Alt + U)」にチェックを入れます。
「カンマ(,)」が入りました。
表示単位を変更する
表示形式の「ユーザー定義」を使って数値を「千円」単位表示にする
「分類（Alt + C）」でリストボックスをアクティブにし、「↓キー」で「ユーザー定義」→「種類（Alt + T）」をアクティブにし、「#,##0,」と入力します。
千円単位での表記に変わりました。
数値の見せ方を変える
表示形式の「ユーザー定義」を使って数値を「千円」・「－（マイナス）」値を赤字にする
「右クリックキー or Shift + F10」→「値フィールドの設定（N)」→「表示形式（Alt + N)」→「分類（Alt + C）」でリストボックスをアクティブにし、「↓キー」でユーザー定義を選択し、「種類(T)」に「#,##0,;[赤]#,##0,」と入力します。
「－（マイナス）」値が赤字になりました。
詳細分析のため階層を追加する
行ラベルに上に１階層追加してみる
行ラベルボックスの「小計区分」の上に「部門名」を追加します。（マウスを使ってください）
グループ化を行う
グループ化機能を使うことで元データにない情報を追加することができます。
ここでは月度情報を年度情報にまとめてみます。
横に長くなってしまっているので、コンパクトにまとめてみましょう。
列ラベルで表示している月度情報は、３年間の４－６月のものです。
年度ごとでまとめてみましょう。
列ラベルの「2013/04」を選択した状態で、「Shift」を押しながら「→」で「2013/06」まで選択し、「右クリックキー or (Shift + F10)」→「グループ化（G）」を実行します。
同じように
「2014/04」「2014/05」「2014/06」
「2015/04」「2015/05」「2015/06」
それぞれグループ化します。
グループ化した名称を変更する。
「右クリックキー or (Shift + F10)」→「値フィールドの設定（N)」を実行します。
※グループ1,2,3のラベルのどれかを選択している必要があります。
名前の指定内の「月度2」が反転して選択されていますので、「年度」に変更します。
 |
→ |
 |
ラベルに自動的に付された「グループ1～3」の名称を変更します。
「グループ1」→「2013」
「グループ2」→「2014」
「グループ3」→「2015」
比較のための設定をする
ピボットテーブルの「計算の種類」を使って年度数値の比較をしてみる
この状態でも数字の増減はわかりますが、売上が増減によるものなのか、費用が増減なのかわかりずらいです。
そこで比率を使ってみましょう。
※「A1工事部門」の「＋」をクリックして小計区分を表示させてください。（マウスを使ってください）
「金額」を値ボックスに追加します。
値を選択した状態で「右クリックキー or (Shift + F10)」→「計算の種類（A）」→「行集計に対する比率（R)」を選択します。
合計金額におけるそれぞれの数値で割った％が表示されます。
総額に対するその年度の金額の割合が算出されます。
A1工事部門の「01売上」2013年度の23,637を総額62,089で割ると38.07%と算出されます。
小数点桁数を変更する
小数点2桁を小数点1桁の表示に変更しましょう。
小数点1桁表示にしたい場合は、「右クリックキー or (Shift + F10)」→「表示設定(Alt + N)」→「分類(Alt + C)」で「↓キー」で「パーセンテージ」→「小数点以下の桁数（Alt + D)」、桁数を「１」にします。
パーセンテージの小数点桁数が１になりました。
フィルター機能をする
ピボットテーブルのフィルター機能を使って２期比較の資料にする
３期間ではなく２期間比較をしたい場合は、列ラベルにセル選択した状態で「Alt + ↓」でフィルターボックスを表示させ、「2013」のチェックボックスを外します。
年度が2014と2015のみが表示されました。
前期と比較してどれくらい数値が増減しているかわかります。
条件付き書式を使って異常値を目立たせる
フィルターを選択して、2013にチェックを入れて表示させてください。
パーセンテージで表示されましたが、ぱっと見ただけではチェックすべきものを見つけにくいです。
条件付書式を使って数字が大きいものを目立つようにさせましょう。
列ラベル「合計 / 金額２」の枠部分を選択すると、列「合計 / 金額２」が一括で選択されます。（マウスを使ってください）
「ホーム(Alt + H)」→「条件付書式(L)」→「データバー(D)」
パーセンテージの数値が大きいセルほどデータバーが長く表示されます。
条件付書式には「指定値よりも大きいもの・小さいもの」「カラースケール」などさまざまなものが用意されています。
カラースケールの場合
フィルターを解除して全期間を展開させる
行ラベル「2014」を選択した状態で、「右クリックキー or (Shift + F10)」→「展開/折りたたみ（E)」→「フィールド全体を展開（E)」を選択します。
値の表示方法を変える
集計表が横に長くなってしまったので、表示方法を少し変えてみましょう。
列ラベルボックスにある「∑値」を行ラベルボックスに移動させます。（マウスを使ってください）
列に並列表記されていた値が行ラベルに表記されました。
「展開」機能で表示範囲を広げる
「展開」機能を使って小計区分をすべて表示させてみましょう。
（全て展開している場合は飛ばしてください）
行ラベルの部門名のどれかを選択した状態で、「右クリックキー or (Shift + F10)」→「展開/折りたたみ(E)」→「フィールド全体の展開(E)」を実行します。
全部門の小計区分が表示されました。
※部門左の「＋」が「－」になりました。
ウィンドウ枠を固定する
列タイトル、行タイトルが画面スクロールした時に消えないようにウィンドウ枠の固定する
データ量が多くなると１画面で収まりきらず、スクロールしていくと行タイトルや列タイトルが見えなくなります。
その場合はラベルを固定しましょう。
行をスクロールしていくと･･･
行タイトルが見えなくなります。
固定方法は３つあります。
Ａ：選択しているセルより「上の行、左の列」を固定する
Ｂ：先頭行のみ固定する
Ｃ：先頭列のみ固定する
今回利用するのはＡの方法です。
固定したい行と列のクロスする右下のセルを選択し、「表示(Alt + W)」→「ウィンドウ枠の固定(F)」→「ウィンドウ枠の固定(F)」を実行します。
下に移動してください。
行タイトルが固定されたままなのがわかります。
横に移動してください。
列ラベルが固定されたままなのがわかります。
