エクセル折れ線グラフにおける平均の上方と下方ハイライト
チャートの線がシリーズの平均より上か下かを視覚的に確認するには、チャートに平均線を挿入するだけです。こんな感じになります。
魅力的ですか？それともこれ？
私はあなたの答えを推測することができます。
では、線が平均より上または下にあるときに領域を強調表示するこのグラフをどのように作成するのでしょうか。ステップバイステップでそれを学びましょう。
線が平均より上と下の場合のハイライト領域学習する最良の方法は、例によって学習することです。それでは、例から始めましょう。
ここに、個々のセールスマンによる総売上高を示すデータがあります。このデータをグラフに視覚化したいと思います。
ステップ1：3つのヘルパー列を追加する
このようなグラフを作成するには、3つのヘルパー列が必要です。最初のヘルパー列には平均データが含まれます。 2番目のヘルパー列には、売上が平均よりも大きいすべてのポイントが含まれます。そして、3番目の列には、売上が平均よりも少ないポイントがあります。
したがって、平均列の式は次のようになります。
|
=AVERAGE($B$2:$B$7) |
この式を平均列の最初のセルに書き込み、下にドラッグします。すべてのセルの値が同じであることを確認してください。数式をコピーしたときに参照が変更されないように、 `link：/ excel-range-name-what-is-an-absolute-reference-in-excel [absolutereferencing]`を使用しました。
次の列では、すべてのUP値が必要です。これは、平均データと売上データの間に正の差があります。つまり、売上高が平均よりも大きい場合にのみ、売上高から平均データを差し引く必要があります。
D2の式は次のようになります：
|
=IF(B2>C2,B2-C2,””) |
下にドラッグします。
次の列では、すべてのDown値が必要です。平均データと売上データの差はマイナスになります。つまり、売上が平均よりも小さい場合にのみ、売上から平均データを差し引く必要があります。
E2の式は次のようになります：
|
=IF(B2<C2,B2-C2,””) |
下にドラッグします。
ステップ2：Sales AdviserとSalesを選択し、折れ線グラフを挿入します。この例では、範囲A1：C7を選択します。 [挿入]→ [グラフ]→ [線と面積]→ [マーカー付きの線]に移動します。このようなチャートがあります。
ステップ3：平均級数をもう一度追加する
グラフを右クリックし、[データの選択]オプションをクリックします。ここで、[凡例とエントリ]セクションの[追加]ボタンをクリックします。系列の値として平均範囲C2：C7を選択します。元の平均線に線を挿入します。書式設定で必要になります。大事です。
ステップ4：データにアップシリーズとダウンシリーズを追加する
平均シリーズを再度追加したので、同様にアップシリーズとダウンシリーズを1つずつシリーズに追加します。最後に、次のようなグラフが作成されます。これは私たちが望んでいるもののようには見えませんよね？待って、それは進化するでしょう。
ステップ5：2番目の平均系列、上および下系列を積み上げ面グラフに変更するグラフ上の任意の系列を右クリックし、[データ系列グラフタイプの変更]オプションをクリックします。これにより、グラフが大幅に変更され、このExcelの折れ線グラフになりたい形になり始めます。
ステップ6：平均面積スタックチャートに塗りつぶしなしを選択：
平均パーツの領域を右クリックし、フォーマットデータ系列をクリックします。塗りつぶしオプションに沿って、塗りつぶしに移動し、塗りつぶしなしオプションを選択します。
これで、グラフは私たちが望んでいたとおりに表示されます。ダッシュボード/プレゼンテーションのテーマに合うように、少しフォーマットを行う必要があります。
ステップ7：テーマに合わせてグラフをフォーマットします。
チャートから不要なものをすべて削除します。平均的な凡例は必要ないため、グラフから削除しました。グラフをきれいに見せたいので、グリッド線を削除しました。上の部分を緑に、下の部分を赤に変更しました。チャートのタイトルを「平均に対する売上高」と名付けました。
最後に、私はこのチャートを持っています。
注：*このチャートは、目標対達成チャートとして使用できます。平均シリーズの値をターゲットとブームに変更するだけで、Excelでクリエイティブなターゲットと達成度のグラフが表示されます。
以下のテンプレートファイルをダウンロードします：
|これは、ダッシュボードのもう1つの重要な機能です。これは、特定の条件で値を合計するのに役立ちます。