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

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

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

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

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

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

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

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

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

さぁ、Excelを起動しよう

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

元データの作成

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

グラフをつくる

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

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

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

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

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

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

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

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

結果、こうなります ↓

これで完成です！

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

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

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

まとめ

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

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

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

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

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

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