Excelで動的なパレート図を作成する方法
この記事では、動的パレート図を作成する方法を学習します。
ヴィルフレド・パレートにちなんで名付けられたパレート図は、棒と折れ線グラフの両方を含むタイプのグラフであり、個々の値は棒で降順で表され、累積合計は線で表されます。
例を挙げて理解しましょう:
-
学校の苦情データがあり、パレート図を作成する必要があります。
列Cにヘルパー列の累積%が必要です。セルC2の数式は= SUM($ B $ 2:B2)/ SUM($ B $ 2:$ B $ 11)*になり、数式を下にドラッグします
動的なパレートを作成するには;チャートには、いくつかの計算を行う3つのセルが必要です。まず、スクロールバーを作成し、セルB16にリンクします。スクロールバーを右クリックし、[フォーマットコントロール]を選択して、以下のスナップショットに示すように値を入力します
セルB14では、数式は= B16 / 100で計算されます*ターゲット
セルB15内; *累積%
には次の式があります。
* = INDEX($ C $ 2:$ C $ 11、IFERROR(MATCH($ B $ 14、$ C $ 2:$ C $ 11,1)、0)+1)
-
次に、さらに2つのヘルパー列を作成する必要があります。つまり、強調表示された%と残りの%
セルD2では、数式は* = IF($ B $ 15> = C2、B2、NA())
です。
セルE2では、数式は* = IF($ B $ 15 <C2、B2、NA())
です。
-
最後に、すべてがパレート図を作成するために設定されます。範囲A1:A11とC1:E11を選択する必要があります
[デザイン]タブで、[グラフの種類]をクリックします。折れ線グラフの種類を[累積%]に適用し、[2次軸]をクリックします
120%を表示するように累積%を100%にする必要があります。2次軸を右クリックして[軸のフォーマット]を選択します。*最大値を1.2ではなく1として選択します
これで、動的パレート図を表示する準備が整いました。唯一の問題は、スクロールバーをクリックすると、2つのシリーズがあるため、バーが元の位置からシフトすることがわかります。この問題を解決するには、壊れたベンチ(強調表示された%)バーをクリックし、右クリックして[データシリーズのフォーマット]を選択します。シリーズオーバーラップでは、スナップショットの下に100%参照を入力します
-
どのターゲット%が選択されているかを確認するには;セルJ24に、数式を
と入力します。 === * = “ターゲット”&TEXT(B14、 “0%”)
このようにして、動的パレート図を作成できます。スクロールバーの値を増減すると、ターゲットが更新されます。