Excelのピボットテーブル Excelショートカットキー Excelメニュー Excel(エクセル)活用術

ピボットテーブルで活用したい5つの機能。グループ化、フィルター他

投稿日:2015年9月30日 更新日:


Excelピボットテーブルとショートカットキーを効率的に学ぶ講座②
これまでの内容
・ピボットテーブルについて知る
・ピボットテーブルを作成する元となるデータのルール
前の記事
Excelピボットテーブルとショートカットキーを効率的に学ぶ講座①

スポンサーリンク

講座①で作成したピボットテーブルを使ってください。
※この記事からスタートの場合、Pivotサンプルデータ02をダウンロ-ドして、シート「Sheet1」から初めてください。

ここでやる内容
・千円単位の表示の設定
・ピボットテーブルのグループ化
・ピボットテーブルのフィールドの展開/折りたたみ
・ピボットテーブルの比率の計算
・ピボットテーブルの条件付き書式
・ウィンドウ枠の固定
・ショートカットキーでの操作

3.ピボットテーブルの色々な機能を使う

①列エリアの「部門名」を「月度」に変更してください。(マウスで操作)

このようにラベルを変更するだけで、簡単に集計軸を変更することが可能です。

②ピボットテーブルの値に「,(カンマ)」を入れる
「,(カンマ)」が入っていない数字は資料として見づらいので、表記を変更しましょう。

「右クリックキー 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,」と入力します。

「-(マイナス)」値が赤字になりました。

⑤行ラベルに上に1階層追加してみる
行ラベルボックスの「小計区分」の上に「部門名」を追加します。(マウスを使ってください)

⑥ピボットテーブルのグループ化で月度情報を年度情報にまとめる
横に長くなってしまっているので、コンパクトにまとめてみましょう。
列ラベルで表示している月度情報は、3年間の4-6月のものです。
年度ごとでまとめてみましょう。

列ラベルの「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)」、桁数を「1」にします。

パーセンテージの小数点桁数が1になりました。

⑨ピボットテーブルのフィルター機能を使って2期比較の資料にする

3期間ではなく2期間比較をしたい場合は、列ラベルにセル選択した状態で「Alt + ↓」でフィルターボックスを表示させ、「2013」のチェックボックスを外します。

年度が2014と2015のみが表示されました。

前期と比較してどれくらい数値が増減しているかわかります。

⑩条件付き書式を使って異常値を目立たせる
※フィルターを選択して、2013にチェックを入れて表示させてください。

パーセンテージで表示されましたが、ぱっと見ただけではチェックすべきものを見つけにくいです。
条件付書式を使って数字が大きいものを目立つようにさせましょう。

列ラベル「合計 / 金額2」の枠部分を選択すると、列「合計 / 金額2」が一括で選択されます。(マウスを使ってください)

「ホーム(Alt + H)」→「条件付書式(L)」→「データバー(D)」

パーセンテージの数値が大きいセルほどデータバーが長く表示されます。

条件付書式には「指定値よりも大きいもの・小さいもの」「カラースケール」などさまざまなものが用意されています。

カラースケールの場合

⑪フィルターを解除して全期間を展開させる
行ラベル「2014」を選択した状態で、「右クリックキー or (Shift + F10)」→「展開/折りたたみ(E)」→「フィールド全体を展開(E)」を選択します。

⑫値の表示方法を横並びから縦並びに変える
横に長いので、表示方法を少し変えてみましょう。

列ラベルボックスにある「∑値」を行ラベルボックスに移動させます。(マウスを使ってください)

列に並列表記されていた値が行ラベルに表記されました。

⑬「展開」機能を使って小計区分をすべて表示させる(全て展開している場合は飛ばしてください)
行ラベルの部門名のどれかを選択した状態で、「右クリックキー or (Shift + F10)」→「展開/折りたたみ(E)」→「フィールド全体の展開(E)」を実行します。

全部門の小計区分が表示されました。
※部門左の「+」が「-」になりました。

⑭列タイトル、行タイトルが画面スクロールした時に消えないようにウィンドウ枠の固定する
データ量が多くなると1画面で収まりきらず、スクロールしていくと行タイトルや列タイトルが見えなくなります。
その場合はラベルを固定しましょう。

行をスクロールしていくと・・・

行タイトルが見えなくなります。

固定方法は3つあります。
A:選択しているセルより「上の行、左の列」を固定する
B:先頭行のみ固定する
C:先頭列のみ固定する

今回利用するのはAの方法です。

固定したい行と列のクロスする右下のセルを選択し、「表示(Alt + W)」→「ウィンドウ枠の固定(F)」→「ウィンドウ枠の固定(F)」を実行します。

下に移動してください。
行タイトルが固定されたままなのがわかります。

横に移動してください。
列ラベルが固定されたままなのがわかります。

次へ→Excelの印刷設定。1ページに収める・タイトルを全ページに表示


フォローしていただけると嬉しいです

follow us in feedly Twitterボタン

-Excelのピボットテーブル, Excelショートカットキー, Excelメニュー, Excel(エクセル)活用術
-, , ,

Copyright© 経理と事務の効率化 , 2017 AllRights Reserved.