分析結果を見やすく編集！ピボットテーブルで分析を効率化する方法（表示・書式編）

「Excel業務を何とか効率化したい」そう考えている人に習得をおすすめしたいのが「ピボットテーブル」機能です。ピボットテーブルを使えば、関数を知らなくても大量のデータの集計が可能。さらにピボットテーブルを使えるようになれば、関数を使用するよりも早く、簡単な操作で様々な集計ができるので、データ分析がはかどるメリットもあるのです。作業効率をあげたいなら、ピボットテーブルはぜひ習得したいスキルの一つと言えます。

今回の記事では、ピボットテーブルで集計したデータをより見やすく表示する方法を説明します。見た目がわかりやすくなることで、自分の分析効率を上げるというメリットもあります。それだけでなく、作成したピボットテーブルを他の人に共有するときも説明しやすくなり、相手にも喜ばれます。難しい関数を覚える必要もなく、画面上のクリック・ドラッグの操作を覚えるだけですので、ぜひ本記事を参考にピボットテーブルを見やすく編集してみましょう。

ピボットテーブルの使い方を紹介した過去の記事：
ピボットテーブルで何ができる？表の集計・分析を効率化しよう
集計以外もできる！ピボットテーブルで分析を効率化する方法（平均・比率編）
日付や番号ごとに仕分けよう！ピボットテーブルで分析を効率化する方法（階層構造・グループ化編）
フィルターを活用！ピボットテーブルで分析を効率化する方法（並べ替え・抽出編）

【ピボットテーブルの応用:その7】ピボットテーブルの表示形式を変えて分析結果をわかりやすく変えよう

ピボットテーブルの初期設定では、集計した数値をそのまま表示します。「このままだと数値の桁が数えにくい」「『〜千円』の単位で表示させたい」という場合は、「表示形式」を設定する必要があります。ここでは、「表示形式」の一般的な設定変更を説明します。

集計結果にカンマ（,）を表示しよう

集計結果に3桁ごとにカンマ（,）を表示するには、「値」フィールドのセルを右クリックし、「値フィールドの設定」を選択します。

ウィンドウが開いたら、左下の「表示形式」をクリックします。

左側の枠から「数値」を選択し、「桁区切り（,）を使用する」にチェックを入れ、「OK」をクリックします。元のウィンドウに戻るので、ここでも「OK」をクリックしましょう。

集計された値が3桁ごとにカンマで区切られました。ピボットテーブルで作成した表が小さい場合は、「値」部分全体をドラッグして選択した状態で「ホーム」タブから「桁区切りスタイル」を選択しても集計結果にカンマが表示されます。

下の桁を省略してすっきり表示させよう

高額な商品を取り扱っている会社や、多くの取引先を抱える会社の場合、桁の大きい数値の集計を日常的に行う必要があります。ここでは、千円単位、百万円単位など、下の小さな桁を省略して、すっきりと見やすいピボットテーブルを作成する方法を紹介します。

値フィールドのセルの上で右クリックし、「値フィールドの設定」を選択します。ウィンドウが開いたら「表示形式」を選び、左側の項目の一番下にある「ユーザー定義」を選択します。

「種類」の入力欄に「#,##0,」と半角で入力しましょう。この末尾の0の右にあるカンマが、「数値を千円単位で表示する」という指示になりますので、忘れずに入力してください。

千円未満の単位が省略されました。ちなみに、前の画面で右のカンマをもうひとつ増やして「#,##0,,」と入力すると百万円単位の表示に切り替えることができますが、その場合は十万以下の位はすべて切り捨てられます。（99万であっても「0」と表示されます。）

省略した下3桁をもう一度表示させたいとき：表示形式を解除する

表示形式で省略した桁を再度表示させたい場合は、表示形式の解除を行う必要があります。そのときは値フィールドを右クリックして「値フィールドの設定」から「表示形式」を選び、左側の項目の一番上の「標準」を選択しましょう。

見出しを書き換えよう

下3桁・下6桁の位を省略して表示するなど、意図的に表示方法を変更している場合、そのことがわかるように「単位：千円」などと書き添える必要があります。見出しの名前を変更して任意の名前をつけるには、見出しのセルをクリックして新しい見出しの名称をキーボードで入力します。

ピボットテーブルの項目見出しには「平均／金額」「合計／数量」「行ラベル」「列ラベル」などの名前が自動的に設定されます。そのままでも支障はありませんが、分析結果を社内で共有したり、取引先に提出したりする場合は、この方法でわかりやすい名前に変更しましょう。

【ピボットテーブルの応用:その8】小計・総計を省略してレイアウトをすっきり見せよう

ここでは、表示する必要のない項目の表示設定を変更し、整理して表示する方法をお伝えします。ピボットテーブルの初期設定では、必ず項目ごとの総計や小計の値が表示されるようになっています。「まずは内訳の数値だけに注目して分析したい」「提出する表のフォーマットが決まっていて、小計を表示させたくない」という場合は、これから紹介する方法を使って集計結果を一部省略しましょう。

総計を省略する

ピボットテーブルで集計すると、右端と最下部の行には自動的に「総計」が表示されるようになっています。

この総計は、設定を変更することで行ラベル分（右側）だけ表示させたり、列ラベル分（下側）だけ表示させたりすることができます。もちろん、どちらも不要なのであれば両方とも表示させないようにすることも可能です。表示を変更するには、ピボットテーブル内部のセルをどれかひとつクリックした状態で「デザイン」タブの「集計」をクリックします。

クリックするとプルダウンが表示され、表示形式を選ぶことができます。初期設定では「行と列の集計を行う」となっているので、目的に応じて表示方法を変更しましょう。

「列と行の集計を行わない」の画面は上のようになります。再度表示方法を変更したい場合も同様に、ピボットテーブル内部のセルを選択した状態で「デザイン」タブから「総計」をクリックしましょう。

小計を省略する

階層構造になったピボットテーブルでは、初期設定の状態だと上位フィールドごとの小計が表示されています。

ピボットテーブル内部のセルをどれかひとつクリックした状態で「デザイン」タブの「小計」をクリックすると、初期設定以外の小計の表示位置を選択することができます。

「小計を表示しない」「すべての小計をグループの末尾に表示する」「すべての小計をグループの先頭に表示する」の中から小計の表示方法を選ぶことができます。小計を表示させたくない場合は「小計を表示しない」をクリックしましょう。

小計の表示が省略されました。ちなみに、「すべての小計をグループの末尾に表示する」だとフィールドの末尾に集計行が1行追加されます。

「すべての小計をグループの先頭に表示する」は初期設定と同じです。上位の項目見出しと同じ行に小計が表示され、その下に内訳となる下位のカテゴリーの項目が並びます。

【ピボットテーブルの応用:その9】注目したいデータを強調して、ピボットテーブルをさらに見やすくしよう

ピボットテーブルでも、通常のExcelの表と同様に「条件付き書式」を適用することができます。条件付き書式を設定すると、

• 「〜以上」
• 「〜より小さい」
• 「平均以上」
• 「50以上100以下」

など、一定の条件に該当する集計値のセルの書式を自動的に変更することができます。条件書式を使えば「集計値が指定した金額以上の部分セルを塗りつぶす」「上位3位までのセルに色を付ける」など、ピボットテーブルの見た目をわかりやすく変更できます。

一定の数値以上のセルを強調して分析する

ピボットテーブルで特定のセルを強調するには、「値」フィールドの中をドラッグして選択した状態で「ホーム」タブから「条件付き書式」「セルの強調表示ルール」の順に選択し、適用したい書式を選びます。ここでは「指定の値より大きい」を選択してみましょう。

「新しい書式ルール」というダイアログボックスが開きます。強調ルールを確認し、基準となる値を入力して、「OK」をクリックします。

例えば、「各店で売上が300万円以上だった四半期をマークしたい」というときは、「セルの値」「次の値より多い」「300,000,000」と入力します。基準値は半角数字で入力しましょう。また、売上が下の「書式」を変更すると、強調するセルや文字の色を変更できます。（初期状態では薄い赤色の背景に濃い赤色の文字で表示されるようになっています。

300万より大きい集計値のセルが赤く塗り潰されました。
設定した条件付き書式を一度削除したい場合は、対象となる書式が設定された部分をすべて選択した状態で「ホーム」タブを開き、「条件付き書式」から「ルールのクリア」、「選択したセルからルールをクリア」を順に選択します。

順位ごとに色をつけて分析する

条件付き書式の「上位／下位ルール」を利用すれば、集計値の中のトップまたはワーストを一目で読み取ることができます。例えば、「総計の値の中で大きい順に上位5位まで強調したい」という場合を見てみましょう。

書式を適用したい範囲をドラッグして選択し、「ホーム」タブの「条件付き書式」をクリック。「上位／下位ルール」から「上位10項目」を選びます。

次に、強調表示したいセルの数を半角入力します。今回は上位5位まで強調したいので、「5」と入力しましょう。ちなみに、ここで「パーセント」にチェックを入れると上位5%までの表示になります。

セルの値が大きい順に5項目が強調されました。逆に数値の小さい順に並べたときの順位で強調ルールを指定したいときは、同様の手順で「下位10項目」を選択しましょう。

平均より上／下の値を強調して分析する

金額や数量の集計値を比較して、平均値との関係をすぐ分かるようにするときも、「上位／下位ルール」が便利です。「平均より上」または「平均より下」を選択すると、金額や数量の平均値よりも上か、または下かで自動的に色をつけることができます。売上に貢献している店舗や客層を分析するのに便利です。また、別に平均値を集計する手間を省けるメリットもあります。


今回は「売上が平均を下回る支店」を強調してみましょう。書式を適用したい範囲をドラッグして選択し、「ホーム」タブの「条件付き書式」をクリックします。「上位／下位ルール」から「平均より下」を選び、ダイアログボックスの内容を確認して「OK」をクリックしましょう。

平均より下の数値データが強調されました。

数値の大きさを棒グラフで表示する

条件付き書式の「データバー」を使えば、グラフ形式で視覚的に数値の大小をわかりやすく表示できます。セルの背景が数値の大きさによって塗りつぶされるような見た目になるので、省スペース、かつわかりやすく表示することができます。

データバーを利用するには、まず比較したいデータの全体をドラッグして選択します。そのままの状態で「ホーム」タブの「条件付き書式」から「データバー」を選択し、棒グラフの表示形式を選びます。

データバーを使用するときの注意点

データバーで表示される棒グラフの長さは、比較するセル全体の数値の大小で決まります。例えば、上のピボットテーブルで「前橋支店」の売上集計だけを選択した状態では、グラフは下のようになります。

今度は、「全ての店舗の、全ての期間」を選択した状態でデータバーを表示してみましょう。

「前橋支店」のデータバーの長さが変わりました。先ほどの棒グラフからは「前橋支店では2018年の第3四半期と2019年の第1四半期の売上が低い」ということが読み取れますが、この棒グラフで見ると「前橋支店の売上は他の店舗に比べるとかなり低い」と分かります。

さらに、次は小計・総計を表示・選択した状態でデータバーを表示させた場合を見てみましょう。各店舗の四半期ごとの売上状況はほとんど同じに見えるようになってしまい、数値の大小がわかりにくくなってしまっています。

データバーは簡単に設定できるだけでなく、視覚的に数値の大小を判断する上でも効果的です。しかし、比較する範囲を取り違えると逆効果になってしまうこともあります。上の例で言えば、各店の売上と総計を同列に比べてしまったことで、せっかくのデータバー表示がわかりにくくなってしまいました。データバー表示を使用するときは、比較対象となるデータの関係に注意し、同列で比較できるものに限定して比較分析しましょう。

まとめ

今回は、「表示形式の変更」や「条件付き書式」の機能を使って、ピボットテーブルで集計した結果を見やすくする方法を説明しました。どちらもExcelで使える基本的な機能であり、実際の集計結果にはさほど影響しないため見落とされがちです。しかし、見た目を整えることで、詳細に分析するべきデータを素早く発見できるようになります。また、データは理論を裏付ける証拠として人に見せ、納得してもらって初めて真価を発揮するものです。もしあなたが作成したピボットテーブルを誰かに見せる機会があるときは、分析結果を正しく伝えるためにも、「わかりやすい表になっているか」「自分が伝えたい分析結果を一目で分かってもらえるか」といった点にもこだわってみてください。

こちらもオススメ