Excelで予算と実際の差異のグラフを作成する

Excelでは、予算と実際の差異のグラフを使用して、XNUMXセットの系列データを比較し、XNUMXつのデータ系列の差異または差異を表示します。差が負の値の場合、XNUMXつの色付きのバーまたは列が表示され、正の値の場合、別の色付きのバーまたは列が下のスクリーンショットに示すように表示されます。この記事では、Excelワークブックで比較分散チャートとミニインセル比較分散チャートを作成する方法について説明します。

Excelで予算と実際の差異チャートを作成する
セルにミニ予算と実際の差異チャートを作成する
驚くべき機能を備えた予算と実際の差異チャートを作成する
予算と実際の差異チャートのサンプルファイルをダウンロードする
ビデオ：Excelで予算と実際の差異のグラフを作成する

Excelで予算と実際の差異チャートを作成する

予算と実際の差異のグラフを作成するには、次の手順を実行してください。

1。次の数式をセルD2に入力して、予算と実際の値の差を計算し、塗りつぶしハンドルを必要なセルまでドラッグします。スクリーンショットを参照してください。

=C2-B2

2。次に、列Aと列Dのデータを選択し、[ インセット > 列グラフまたは棒グラフを挿入 > クラスター化された列、スクリーンショットを参照してください：

3。縦棒グラフを挿入すると、グラフのタイトル、縦軸、グリッド線を削除するなど、必要に応じてグラフの不要な要素を削除できます。次のスクリーンショットのような結果が得られます。

4。次に、横軸を右クリックして、 フォーマット軸 コンテキストメニューのオプション。スクリーンショットを参照してください。

5。オープンで フォーマット軸 ペイン、下 軸オプション タブをクリックします。 ラベル このセクションを展開するには、を選択しますローから ラベルの位置 ドロップダウンリストを表示すると、横軸がグラフの下部に配置されます。スクリーンショットを参照してください。

6。次に、いずれかのバーを右クリックして、 データ系列のフォーマット オプション、スクリーンショットを参照してください：

7。オープンで データ系列のフォーマット ペイン、下 塗りつぶしと線 タブ、から 埋める セクションでは、次の操作を実行してください。

次の項目を選択します。： 塗りつぶし オプション;
チェック 負の場合は反転 チェックボックス;
正と負の値には、 Color 落ちる。

8。バーの色を設定したら、データラベルを追加する必要があります。クリックしてグラフ内のバーを選択し、をクリックしてください。 チャート要素 アイコンをクリックしてから確認してください データラベル から チャート要素 リストボックス、スクリーンショットを参照：

9。柱のバーが非常に狭いため美しく見えません。この場合、必要に応じてバーの幅を調整できます。いずれかのバーを右クリックして、を選択してください。 データ系列のフォーマット、で データ系列のフォーマット ペイン、下 シリーズオプション タブ、変更 ギャップ幅 あなたの必要に応じて、この場合、私はそれをに変更します 80%、スクリーンショットを参照してください：

ヒント:

グラフを作成した後、異なる色でデータラベルを表示したい場合があります。たとえば、正のデータラベルは緑色で、負のデータラベルはオレンジ色で表示します。次のようにしてください。

元のデータテーブルから[分散]列のデータを選択し、を押します。 Ctrl + 1 を開く セルの書式設定 ダイアログボックス、 セルの書式設定 ダイアログボックスで、をクリックします。 カスタム から カテゴリー リストボックスに入力し、以下のコードを種類テキストボックス：

[Color10]General;[Color53] -General

次に、をクリックします。 OK ボタンをクリックすると、必要に応じて結果が得られます。スクリーンショットを参照してください。

注意：他の色が必要な場合は、次のカラーインデックス表をご覧ください。

セルにミニ予算と実際の差異チャートを作成する

セルにミニ予算と実際の差異のグラフを挿入する場合は、次の手順を実行するとよいでしょう。

=C2-B2

2。次に、分散の負の値を表示し、次の数式をセルE2に適用してから、数式を必要な他のセルにコピーしてください。スクリーンショットを参照してください。

=IF(D2<0,D2,"")

3。分散の正の値を表示します。セルF2に次の数式を入力してから、必要な他のセルに数式をコピーしてください。スクリーンショットを参照してください。

=IF(D2>0,D2,"")

4。次に、チャートバーとして使用される特定の記号を挿入する必要があります。クリックしてください インセット > シンボル、そして シンボル ダイアログボックスで ブロック要素 からほぼすべてのフォントで見つけることができます サブセット ドロップダウンリストをクリックして、 フルブロック 記号をクリックし、 インセット シンボルを挿入するには、スクリーンショットを参照してください。

5。特殊記号を挿入した後、以下の数式をセルG2に適用し、この数式を他のセルにドラッグすると、以下の結果が得られます。

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))

注意：上記の式では、 E2 セルに負の分散値が含まれている場合、 A16 セルには、挿入した特定の記号と番号が含まれています 100 は、分散値に基づいてシンボルの数を計算するために使用される変数データです。独自に変更できます。

6。以下の数式をセルH2に適用し、この数式を他のセルにドラッグすると、次の結果が得られます。

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)

注意：上記の式では、 F2 セルに正の分散値が含まれている場合、 A16 セルには、挿入した特定の記号と番号が含まれています 100 は、分散値に基づいてシンボルの数を計算するために使用される変数データです。独自に変更できます。

7。次に、列Gのセルを選択し、[ ホーム > 右揃え データをセルに正しく配置するには、スクリーンショットを参照してください。

8。最後に、必要に応じてXNUMXつの列のフォントの色を別々にフォーマットすると、次のスクリーンショットのような結果が得られます。

驚くべき機能を備えた予算と実際の差異チャートを作成する

Kutools for Excel Excelにはない数十の特別な種類のグラフを提供します。 箇条書き, 目標と実際のチャート, 差分矢印チャート 等々。その便利なツールで- 差異比較チャート、ワークシートで予算と実際の差異のグラフを作成したり、セル内でミニ予算と実際の差異のグラフをすばやく簡単に作成したりできます。 クリックしてKutoolsfor Excelを無料トライアルでダウンロードしてください！