予算・実績の数字をExcelのVLOOKUP関数で連動

  • URLをコピーしました!

予算実績表を作るときに、毎月数字を入力していてはいくら時間があっても足りません。
こういうときには関数が便利です。

スクリーンショット 2013 07 04 6 38 54

毎月の仕事に時間をかけない

毎月、こういった予算実績表を作ってらっしゃる方は多いと思います。
事例では、担当者別に予算と実績を比較していますが、経理や税理士事務所なら損益計算書で比較することも多いです。
スクリーンショット 2013 07 04 6 27 40

この表を毎月作るのに時間をかけていては、いくら時間があってもたりませんし、本当に重要なことができません。

「プリントアウトして、その表を見ながら予算や実績を入力する」
というのは絶対にやってはいけないことです。

Excelにある数字、もっといえばPC内にある数字は、そのまま活用しましょう。
ミスも激減しますし、効率よく表を作ることができるので、その後の意思決定やプラスアルファの資料作成に時間を使えるようになります。

 

 

数字を連動させる

事例では、シート「予算」からシート「予算実績」へ数字を連動させています。
スクリーンショット 2013 07 04 6 38 54

「6月」を「7月」に変更すれば、7月の数字が連動されるので、一瞬にして表が完成します。

 

 

関数の活用

具体的には次のような数式を入れます。
スクリーンショット 2013 07 04 6 55 12

小嶋さんの6月の予算を連動させるには、次のような手順で考えます。

・まずシート「予算」で「小嶋」を探す。→2行目
・次に6月を探す。→4列目→「4」と指定。
これで、2行目4列目の41,000を連動するわけです。

次の山田さんも同じようにVLOOKUP関数で探せます。

ただし、7月になったときには、5列目となるので、「5」と指定し直さなければいけません。
手で入力するよりは楽ですが、毎月変えるのもめんどくさいです。
そこで、Excelに場所を探してもらいます。
次のように入れれば、セルB1に入れた月を自動で探してくれます。
MATCH関数を使えば、こういうことができるのです。
スクリーンショット 2013 07 04 6 45 39

 

 

もう1つの考え方は、INDEX関数を使う方法です。

単純にいれると、こうなります。
スクリーンショット 2013 07 04 6 53 11

シート「予算」のA1からM9の範囲のうち、2行目4列目の数字を連動させるのです。
スクリーンショット 2013 07 04 7 00 19

ただし、これだと人ごとに、月ごとに数式を変えなければいけません。
そこで、MATCH関数で、人も月も探してもらうようにします。

これで、毎月、セルB1を変えるだけで表が完成するのです。
(実績にも同様の関数を入れます)
スクリーンショット 2013 07 04 7 03 26

Excelは規則的なこと、繰り返すことを得意とします。
ただし、資料を作った後の判断はできません。
規則的なこと・繰り返すことはExcelにまかせて、Excelができないことだけを人間がやるべきです。
サンプルファイルをおいておきますので、参考にしてください。

一度しくみをつくれば一生やらなくてすみます(^_^)

 

http://db.tt/mg7kAEVb





【編集後記】
昨日、スタバにいたら、Surfaceを使っている人をはじめてみかけました。
当ブログでも紹介しているSurface、なかなか気に入っているのですが使っている人を見かけません。
昨日は、しかも2人。2人とも女性で、1人はSurfaceRTにタッチカバー、もう1人はSurfaceProに水色のタッチカバーでした。

  • URLをコピーしました!