前回までは、ピボットテーブルに書式設定をしたり、ピボットテーブルから、比率の累計を 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。そうすると、商品順に並べ替えたり、あるいは売上額順に並日買えてみたりもできるにはできますが、例えば、「この商品群だけ」とか、「この店舗の」、「この担当者の」といったフィルタをかけたときに意味がなくなってしまいます。
ピボットテーブルであれば、フィルタをかけても同じように構成比と、比率の累計を再計算してくれるので、大変ハンドリングしやすくなります。
もしこの記事が参考になれば、幸いです。
関連記事
- というか、パレート分析をExcelで、と検索すると多くのサイトはこの方法を紹介していると思います。 ↩
0件のコメント