この記事では、動的パレート図を作成する方法を学習します。

ヴィルフレド・パレートにちなんで名付けられたパレート図は、棒と折れ線グラフの両方を含むタイプのグラフであり、個々の値は棒で降順で表され、累積合計は線で表されます。

例を挙げて理解しましょう:

  • 学校の苦情データがあり、パレート図を作成する必要があります。

img1

列Cにヘルパー列の累積%が必要です。セルC2の数式は= SUM($ B $ 2:B2)/ SUM($ B $ 2:$ B $ 11)*になり、数式を下にドラッグします

img2

動的なパレートを作成するには;チャートには、いくつかの計算を行う3つのセルが必要です。まず、スクロールバーを作成し、セルB16にリンクします。スクロールバーを右クリックし、[フォーマットコントロール]を選択して、以下のスナップショットに示すように値を入力します

img3

セルB14では、数式は= B16 / 100で計算されます*ターゲット

セルB15内; *累積%

には次の式があります。

* = INDEX($ C $ 2:$ C $ 11、IFERROR(MATCH($ B $ 14、$ C $ 2:$ C $ 11,1)、0)+1)

img4

  • 次に、さらに2つのヘルパー列を作成する必要があります。つまり、強調表示された%と残りの%

セルD2では、数式は* = IF($ B $ 15> = C2、B2、NA())

です。

img5

セルE2では、数式は* = IF($ B $ 15 <C2、B2、NA())

です。

img6

  • 最後に、すべてがパレート図を作成するために設定されます。範囲A1:A11とC1:E11を選択する必要があります

img7

[デザイン]タブで、[グラフの種類]をクリックします。折れ線グラフの種類を[累積%]に適用し、[2次軸]をクリックします

img8

img9

120%を表示するように累積%を100%にする必要があります。2次軸を右クリックして[軸のフォーマット]を選択します。*最大値を1.2ではなく1として選択します

img10

これで、動的パレート図を表示する準備が整いました。唯一の問題は、スクロールバーをクリックすると、2つのシリーズがあるため、バーが元の位置からシフトすることがわかります。この問題を解決するには、壊れたベンチ(強調表示された%)バーをクリックし、右クリックして[データシリーズのフォーマット]を選択します。シリーズオーバーラップでは、スナップショットの下に100%参照を入力します

img11

  • どのターゲット%が選択されているかを確認するには;セルJ24に、数式を

と入力します。 === * = “ターゲット”&TEXT(B14、 “0%”)

img12

このようにして、動的パレート図を作成できます。スクロールバーの値を増減すると、ターゲットが更新されます。