Excel：ピボットテーブルでパレート分析（２）

前回に引き続いて、Excel で作成したパレート分析のピボットテーブルを、更に見栄えを良くしていきます。

 

 

パレート分析では、「構成比の比率の累計」の値によって、A～C までのランク付けをします。

そのランクによって、「条件付き書式」を設定することで、より表を見やすくしようと思います。

 

ピボットテーブルに条件付き書式を設定する

ピボットテーブルの「比率の累計」列の任意の値にカーソルを合わせます。

リボンの「ホーム」タブの「条件付き書式」を選択し、[アイコンセット] → [その他のルール] を選択。

「新しい書式ルール」が開くので、[”比率の累計” 値が表示されているすべてのセル] にチェックを入れ、ルールの内容を下図のようにし、[OK] をクリック。

すると、ピボットテーブルの「比率の累計」列に、色分けされたアイコンが付きました。

ここでは、

• 赤 … Aランク（累計が 80% までの商品）
• 黄 … Bランク（累計が 90% までの商品）
• 緑 … Cランク（累計が 90% より大きい商品）

に分けています。

ランク分けのための比率は、あくまでも例です。必要に応じて変更してください。

ピボットテーブルの結果を元データに反映

パレート分析で作成した表は、ランク分けを見るのは見やすいですが、実務上では実際の並び（例えば商品いちらんであるとか）とは違う上に、レコード数をおおくなって1ページには収まらない場合があります。

例えば、元になるテーブル「商品データ」は、おおよそ商品群ごとに並んでいると思います。ピボットテーブルは、売上の大きい商品順に並んでいるので、商品群はバラバラになります。

この時、ピボットテーブルの内容が、元になったテーブルに反映できると便利だとはおもいませんか？

下準備

まず、ピボットテーブルを作成したシートに「パレート分析」 という名前をつけます。

これは、ワークシートを別のワークシートから参照しやすいように、名前をつけておくほうが何かと都合が良いからです。

次に、元となるテーブル、「売上データ」に切り替えて、下図の例のように、「比率の累計」 列の見出しを入力します。

次にI2 のセルにカーソルを合わせて、次のような数式を入力します。

GETPIVOTDATA関数

ある値を表の中からを検索し、マッチしたレコード（行）の、あるフィールド（列）の値を取得したい関数としてよく使われるのは、「VLOOKUP関数」であるとか、「INDEX関数」だと思います。

ピボットテーブルから値を検索して、必要な値を取得する場合は GETPIVOTDATA関数 を使用します。

文法は、以下の通りです。

GETPIVOTDATA( データフィールド, ピボットテーブル, [フィールド1, アイテム1],[フィールド2, アイテム2], ...)

1. データフィールド

データフィールドは、ピボットテーブルから取得したいフィールド名を指定します。今回は、「比率の累計」 を指定します。

2. ピボットテーブル

ピボットテーブルのデータのある任意のセルを指定します。

大体は、データの入っているセル範囲の、一番左上のセルを指定するのが定番です。今回の例の場合は、パレート分析!$A$4 となります。

これは、後でもわかりますが、フィルタ等を指定することでピボットテーブルのデータ表示セルの範囲は変化するからです。

3. フィールド, アイテム ...

ピボットテーブルから検索するための検索値を指定します。

この例では、ピボットテーブルの「商品名」 フィールドから、元データの商品名が B2 であるレコードを検索します。

数式を入力し終わると、数値が入ると思います。

ピボットでの「比率の累計」は、自動的に書式が「パーセンテージ」で教示されていますが、ここでは、数値で表示されるので、小数点以下の桁の表示を追加しておきます。概ね4桁程度あればいいでしょう。

その次に、カーソルをいま数式を入力したI2 にカーソルを合わせて、フィルハンドルをクリックし、データの末尾までドラッグします。

ランクを数式で解決する

さらにもう一つフィールドを元のテーブルに追加します。ここでは、セル H1 に、「ランク」という見出しをつけています。

さあ、それではこのランク列に、先程GETPIVOTDATA で取得した値から ABC のランクを割り振りましょう。

カーソルを H2 に合わせて、次のような数式を入力します。

IF(I2<0.8,"A", IF(I2<0.9,"B", IF(I2="","","C")))

ここでは、IF文を入れ子にして、

• 比率の累計セルの値が、0.8 より下であれば、A にする。
• 比率の累計セルの値が、0.9 より下であれば、B にする。
• 比率の累計セルの値が、空白（""） であれば、空白を、そうでなければ C にする。

という条件にしています。

入力が終わったら、先程の例と同じように、フィルハンドルをつまんで、ドラッグし、数式をコピーします。

まとめ

ピボットテーブルで集計された各種データの取り出しには、「GETPIVOTDATA関数」 を使用するということがわかりました。

これを使用すると、ピボットテーブルと連動して、別のテーブルの値を変更する、なんていうこともできたりしますので、ぜひ覚えておいて損はないと思います。

また、「ランク」列の数式を設定するときに、「なぜ、比率の累計のセルが空白であることを想定しなければならないのか」については、次回説明しますので、よろしくおねがいします。

関連記事

• Excel：ピボットテーブルでパレート分析（１）
• Excel：ピボットテーブルでパレート分析（３）