おぉ、動かせるぞコレ!Excelでつくる損益計算書推移グラフ

損益計算書推移グラフ

スポンサードリンク

Excelで売上高と売上原価のグラフをつくる。次は売上高と人件費のグラフをつくる。次は…

わざわざたくさんのグラフをつくらなくても。一度グラフをつくれば、好きなグラフを見れる方法がありますよ。

損益計算書の推移グラフを例にお話しします。

選べば動くExcelグラフをご覧にいれます

損益計算書の推移グラフ。どの項目をグラフにするか、どの項目の組み合わせでグラフにするかはそのときどきです。

そのたびにいろいろなグラフをつくるというのもメンドーだ。

ということで、「項目を選べば動く」グラフをつくってみることにしましょう。こんなカンジです↓

よろしければダウンロードして動かしてみてください。→ Excelファイルをダウンロード

graph1

 

さぁ、Excelを起動しよう

それではさっそく、つくり方をみていきましょう。まずはExcelを起動します。

ここからは、Excel2016を使用しての説明になります  

元データの作成

「損益計算書の推移グラフ」ということで、元データを準備します。今回使うのはコチラ ↓

ドロップダウンリストを設置する

続いて、ドロップダウンリストを設置していきます。次の手順です。

  1. タイトル行をコピペする
  2. ドロップダウンリストを設置するセルを選択する
  3. 「データの入力規則」をクリックする
graph3

「データの入力規則」ウィンドウが表示されるので、「入力値の種類」から「リスト」を選択します。

graph4

「リスト」の値を設定します。

  1.  「元の値」欄を選択して、入力状態にする
  2.  元データの「売上高」から「営業利益」までを選択する
graph5

「元の値」が選択されたことを確認して、「OK」ボタンをクリックします ↓

graph6

これでC16セルにドロップダウンリストが設定されました。これをC17セルにもコピーします。

  1. C17を選択する
  2. Ctrl+Dでコピーする
graph7

C16セルにドロップダウンリストを設置した手順で、C18セルにもドロップダウンリストを設置します。

ただし「元の値」の選択では、元データの「原価率」から「営業利益率」までを選択します。こうなるはずです ↓

graph8

ドロップダウンリストに合わせて元データを引っ張る

続いて、ドロップダウンリストで選択した項目について、元データの数字を引っ張るように設定します。

  1.  D13からO13に、「2~13」を入力する
  2. D16に算式「=VLOOKUP($C16,$C$4:$O$12,D$13,FALSE)」を入力する
graph9

D16の算式「=VLOOKUP($C16,$C$4:$O$12,D$13,FALSE)」は、VLOOKUPという関数を使っています。少し補足しますと、

VLOOKUPのカッコ書きの中はカンマで4つの項目に区切られています。それぞれの項目には次のような意味があります。

  • ふたつめの項目は「C4からO12」の範囲を見る
  • ひとつめの項目は「C4からO12」の範囲の中から、「C16」の値を探す
  • 三つめの項目は、「C4からO12」の範囲のうち、「C16」の値について、「D13」番目の列の値を探す。「D13=2」なので、「C4からO12」の範囲のうち、「C16」の値について、2列目の数字を探す、ということになります
  • 四つ目の項目は、「C16」と「完全一致(FALSE)」のデータを指示するものです

また、お気づきのとおり、VLOOKUP内のセルについては「$」を用いています。これは絶対参照と言いますが、これを使うことによって、他のセルへの個別の算式入力を省略できます。

絶対参照の詳細説明は省略させていただきますが、絶対参照を使わない場合、このあとコピーするセルへの個別の算式入力が必要になります。

ちょっとウンザリしてしまったかもしれませんが、話を戻しましょう。続いて、D16の算式をコピーします ↓

graph10

これで、C16からC18のドロップダウンリストの選択により、元データが反映されるようになります 。試してみましょう↓

graph11

元データが正しく反映されていることが確認できたら、D16からO18までの書式を整えておきましょう。

D16からO17までは「カンマ」を、D18からO18までは「パーセント」を ↓

graph12

グラフをつくる

では、さいごにグラフをつくっていきます。

  1.  C15からO18までのデータを選択する
  2.  「挿入」タブを選択する
  3.  「折れ線」グラフを選択する
graph14

表示されたグラフの体裁を整えていきます。

まず、グラフの元データの3行目「率」のグラフが見えなくなっているので修正します。

  1. 「人件費率」の折れ線をクリックして選択する
  2. 右クリックでウィンドウメニューを表示する
  3. 「データ系列の書式設定」を選択する
graph15

続いて、「率」のグラフについて「第2軸」を選択します ↓

graph16

これで「率」のグラフも表示できました。あとは、好みで体裁を整えます。わたしの場合には、

  1.  「グラフツール」→「デザイン」→「クイックレイアウト」で、データテーブルなどを表示する
  2. 折れ線の太さを調整する
  3. グラフ内の文字の大きさを調整する

結果、こうなります ↓

graph17

これで完成です!

設置したドロップダウンリスト3つのうち、2つは実数データの項目をグラフに表示できます。

もうひとつのドロップダウンリストで比率データの項目をひとつグラフに表示できます。

これにより、ドロップダウンリストを動かすだけで、いろいろな組み合わせのグラフが瞬時に表示できます。いちいち、グラフをつくらなくてよいというのがメリットです。

 

まとめ

Excelで「損益計算書の推移グラフ」をつくってみました。

ただのグラフではなく、動かせるグラフという特徴のあるグラフです。ちょっとした関数と工夫で、Excelも動きのあるものがつくれるという一例です。

ぜひこれを応用して、ほかにも便利なグラフをつくってみましょう。

 

************
  きょうの執筆後記
************

ブログには書けない・書きにくいことその他。きょうの「執筆後記」は毎日メルマガでお届け中です。

よろしければメルマガ(無料)をご登録ください! → 登録はこちらから

スポンサードリンク



スポンサードリンク

毎週月・木 配信中!
経営・お金のヒントWeekly News

経営の考え方・銀行対応・補助金・減税など役立つ情報を、無料メルマガにて定期的にお届けします。

ABOUTこの記事をかいた人

税理士レス経理エバンジェリスト、フリーランス型税理士。1975年生まれ。 フリーランスの経理・会社の銀行融資支援を得意にする、横浜市の諸留誕税理士事務所・所長。2016年4月、18年間の「勤め人」を脱して独立開業。以来、ブログを毎日更新中!