前回までは、ピボットテーブルに書式設定をしたり、ピボットテーブルから、比率の累計を GETPIVOTDATA関数 で取得し、元テーブルに「ランク」を表示させる列を作成したりしました。

今回は、この元テーブルの「条件付き書式」で、「ランク」のABCの値で色分けをしたりして、見やすくしてみたいと思います。

 

 

ここまでで、もととなるテーブル「売上データ」 から、ピボットテーブルを用いてパレート図を作成し、さらに、ピボットテーブルからの情報を、「売上データ」へとフィードバックさせて、各商品にランクを付けました。

ランクに合わせて元データを見やすくする

ここでは、ランクに合わせて、売上データの書式を変更し、見やすくしてみます。

数式を使用して、書式設定する

元データが入っている「売上データ」ワークシートを開いて、条件付き書式を設定したい範囲を選択します。

次に、リボンの「ホーム」タブから「条件付き書式」 → 「ルールの管理」 を開きます。

条件付き書式ルールの管理」が開くので、[新規ルール] をクリック。

新しい書式ルール」が開くので、

  • [数式を使用して、書式設定するセルを決定] を選択。
  • 数式に、[=$H2="A"] を入力。

としてから、[書式] をクリック。

セルの書式設定」ダイアログが開くので、以下のように設定します。

  • 「フォント」タブで、[スタイル] を太字に、[] を赤に設定。
  • 「塗りつぶし」タブで、色を黄色を選んで、[OK] をクリック。

これで、ルールセットが一つ追加されました。

ここで、[OK] ボタンをクリックして摘要させると、次のように表示が変わると思います。

ここでの要点

もし、「ランク」列にのみ、条件付きの書式を設定したいのであれば、条件を判定するセルと、書式を設定するセルが同じなので、さきのピボットテーブルで行ったようなお手軽な条件付きの書式設定を追加するか、あるいは「新しい書式ルール」で、「指定の値を含むセルだけを書式設定」を使って、「セルの値」が、"A"に等しい、として書式を設定する方法もあります。

しかし、今回は、「条件を判定したいセル」と、「書式を設定したいセル」が違うので、[数式を使用して、書式設定するセルを決定] を使用しています。

また、この時、条件を判定する数式に入れるセルのアドレスですが、ここでは$H2となっています。これは、今回洗濯しているセルの範囲が $A$2:$G$27 となっています。このとき、一番上の行は、2 行目となっているので、この2行目のラインから、条件を判定するセルを指定します。

また、H2 なのになんで $H2 なのか、となるかもしれませんが、今回は複数の行、列に渡るセル範囲を選択しているので、H列は絶対参照 として固定し、行は相対参照 とすることで数式が破綻してしまうのを防いでいます。

残りの書式設定

ここまで来たら、あと残りの条件付き書式を設定します。

まず、ランクが B の場合は、

  • フォントスタイルは太字
  • フォントの色は赤
  • フォントの背景色は標準

とします。

次に、ランクが C の場合は、

  • フォントスタイルは標準
  • フォントの色は水色
  • フォントの背景色は標準

とします。

最後に、ランクが何も表示されていない場合(要するに空白)の場合、

  • フォントスタイルは標準
  • フォントの色はグレー
  • フォントの背景色は標準

とします。

なぜ、ランクの値が空白の場合を考慮しているかというと、それは後で分かるようにしています。

そして、書式を適用された結果は、次のとおりになります。

ピボットテーブルの行ラベルにフィルタをかけてみる

ここで、ピボットテーブルのある「パレート分析」ワークシートに戻り、ピボットテーブルの行ラベルの右横にあるボタンをクリックします。

すると下の方に、商品名がツリー表示されており、すべての項目にチェックが入っていると思います。

[すべて選択] のチェックを外し、いったんすべての選択を解除してから、武器にのみチェックを入れてみます。

チェックが終わったら[OK]で摘要させましょう。

すると、以下のようにピボットテーブルと、パレート図が変化すると思います。

そうして、売上データの方のワークシートに戻ってみると、一部のセルが「#REF!」となってしまいます。

これは、 GETPIVOTTABLE 関数が、値の参照ができなくなったために、参照エラーが発生しています。と同時に、ランク列の数式も同じエラーを返しています。

この問題は、「比率の累計」列の数式を次のように変更すると解決できます。

要するに、IFERROR() 関数で GETPIVOTTABLE 関数の結果をチェックするようにします。

IFERROR関数

書式: IFERROR( 値, エラー時の値 )

この関数は、調査する値(数式) にエラーがある場合、エラー時の値 を返します。エラーがない場合は、この 値(数式)

ここで値となるのは、GETPIVOTDATA 関数になります。また、エラー時の値には "" を入れることによって、空白を返すようにしています。

これは、

=IF(ISERROR(GETPIVOTDATA(引数...)),"",GETPIVOTDATA(引数...))

と書いているのと同意になります。

この結果、「売上データ」がどのように変わったかというと、下の図のようにピボットテーブルから情報を取得できない商品については、文字色がグレーになっていることがわかります。

これで、

  • ランクを表示する数式で、なぜ空白を返す必要があるのか。
  • 売上データの条件付き書式で、なぜランクが空白の場合のルールを登録したか。

の理由はおわかりいただけたでしょうか?

まとめ

3回に渡って、Excel で ピボットテーブル を用いてパレート図を作成し、その結果から元の売上データの書式設定をして、ランク分けが見やすくする方法について説明してきました。

ピボットテーブルは、売上順、もしくは比率の多いもの順に並んでいるので、比重の重いものから軽いものへ、順番に見ていくことができます。

また、元となった「売上データ」は、商品区分の順番に並んでいると思うので、商品の検索性が高くなります。例えばこの商品区分では、これらの商品は比重が大きいが、こちらの商品はあまり比重が大きくない、といった分析をするには「売上データ」のような表の方が見やすいとおもいます。

もちろん、「売上データ上」で直接並び替えて、パレート分析をする方法もあります1。そうすると、商品順に並べ替えたり、あるいは売上額順に並日買えてみたりもできるにはできますが、例えば、「この商品群だけ」とか、「この店舗の」、「この担当者の」といったフィルタをかけたときに意味がなくなってしまいます。

ピボットテーブルであれば、フィルタをかけても同じように構成比と、比率の累計を再計算してくれるので、大変ハンドリングしやすくなります。

もしこの記事が参考になれば、幸いです。

関連記事


  1. というか、パレート分析をExcelで、と検索すると多くのサイトはこの方法を紹介していると思います。 

0件のコメント

コメントを残す

メールアドレスが公開されることはありません。