エクセルで時間をかけずに予算計算書を作るコツを教えます

こんにちは、「今より楽に仕事をしよう」デスクワークラボの吉井良平です。

 

エクセルで予算を作っている方は、結構いらっしゃいます。大きな会社以外では、ほとんどの方がエクセルを使って予算を作られているのではないでしょうか。

エクセルで作られた予算書を見ると、転記に相当時間がかかっているなぁ、と感じることがよくあります。

また、予算というものは修正や追加がつきものなので、修正や追加がしやすいやり方で作っておくと、後で楽になります。

ちょっとした工夫をするだけで予算書を作成する時間がだいぶ削減されますので、これから紹介するやり方を参考にしてみて下さいね。

一般的な予算書類の問題点

 

下記の画像はある団体の予算書ですが、このような形式で予算書を作成されていることが多いです。

問題ないとは思いつつ、一部消してます

これを例に、一般的な予算書の問題点を考えてみましょう。

 

金額が連動していない

明細の欄に細かい内訳が書いてありますが、この方法だと別途計算してから、合計金額を入力しなければいけません。

計算機を使って計算されている場合が多いと思いますが、計算するのにも結構時間がかかります。

追加や修正が発生した場合は、明細の中身を変更して、合計金額を変更して、という手間が発生します。

 

ミスが発生しやすい

明細と合計金額が連動していないため、「明細の欄には書いてあるけど、合計金額には反映されていない」というミスが発生しやすくなっています。

ミスを防ぐためには、かなり注意をしながら書類を作成する必要があるので、書類作りがストレスになります。

予算の審議をする会議で、ミスに誰も気づかない場合もあります。または、金額が合っているかの確認のため、誰かが計算機で確認しながら会議をしている、というのもよくある話です。

 

予算というのは次年度の計画を決めていく上で、とても重要です。予算=計画と言っても良いですよね。

書類を作ったり、計算をするのに時間をかけるのではなく、予算の中身を作ることに集中できるように、エクセルのテクニックを知っておきましょう。

 

時間をかけず、ミスも少なくしながら予算書を作る方法

 

最終的には会社の書式に合わせないといけないかもしれませんが、最初から書式に合わせて作ろうとすると、転記が多くて時間がかかり、ミスも多くなります。

転記をせずに、追加・修正がしやすい方法で予算書を作りましょう。

また、この方式の方が予実管理もしやすいのです。

 

データベース形式で作る

「データベース形式」というと少し難しく感じるかもしれませんが、「縦方向に」「スキマなく」データが並んでいるものをデータベースといいます。

「縦方向にスキマなく」に関しては、「顧客管理データベース作成の記事」をご覧ください。

何も考えずに、縦方向にどんどん予算の明細を追加していけば良いだけです。

予算書の作り方

どしどし入力していきます

「スキマなく」ということなので、費目欄もすべて埋める必要があります。

が、明細以外の部分は後でまとめてコピペして埋めれば大丈夫です。

金額欄は、数量x単価の計算式を入れておいて大丈夫です。後で人数や単価が変更になった場合、いちいち計算しなおさなくてもよくなります。

 

並べ替えをする

エクセルでは「同じ名前が並んでいる部分の集計」ということができるので、集計される基準に従って並べ替えを行います。

費目の並び順が決まっている場合はちょっと細工をしないといけませんが、今のところはあまり気にせず、とりあえずあいうえお順で並べておきます。(後で、費目の並び順ごとに並べ替えをする方法を紹介します)

集計したい部分で並べ替えをしておきます

並べ替えの方法が分からない方は、無料オンライン講座「データの並べ替え」をご覧ください。

 

集計をする

並べ替えをしたら、同じ名前の部分を集計します。

集計は、「データ」タブの右側の方に「小計」というボタンがあるので、そこを押すと集計する場所を選ぶことができます。

 

エクセルの小計機能

明細で集計した状態

会議資料の場合は、これをこのまま印刷して提出すると全く漏れがなく、見る方も項目ごとに追えるようになります。

合計だけを出したい場合は、左側の「-」ボタンを押すと、同じ名前のグループを畳むことができます。細かな明細の紹介が必要ない場合は、明細部分を折りたたんでおくと、資料枚数の節約になります。

すべて折りたたんだ状態

なかなか言葉では分かりにくいかもしれませんので、動画にもしてみました。

これで簡単に予算計算書ができあがりました!

作るのが楽なだけではなく、見る側にとっても「明細が分かりやすい」「計算間違いが無い」ということから、安心して内容の審議ができます。

 

会社の規定のフォーマットに合わせる場合のテクニック

 

先ほど出来上がった予算計算書ですが、どうしても会社の規定のフォーマットに入れないといけない場合もあります。

そんな時に使えるテクニックを紹介します。

 

費目を規定の順番に合わせる

先ほどは適当に「あいうえお順」で費目を並べ替えしましたが、費目の表示に順番があることも多いです。(むしろその方が普通ですが)

重要な費目ごとに並んでいた方が予算の審議もはかどりますので、並べ替えが必要な場合は対応しないといけません。

 

費目を順番ごとに並べ替える場合は、別のシートに費目の並び順表を作っておきます。

名称と並び順の表です

先ほどの予算表に、順番を表示する列を一つ作って、VLOOKUP関数で並び順を引っ張ってきます。

VLOOKUP関数の使い方は、「考え方が分かれば難しくない!VLOOKUP関数の使い方」をご覧ください。

あとは、引っ張ってきた順番で並べ替えをすれば大丈夫です。

VLOOKUP関数で、順番を引っ張ってくる

 

明細をテキストにする

かかる費用を「○○代:@10 x 3」とかのテキスト(文字)にしないといけない場合もあります。

これははっきり言って面倒ですが、会議の書式に合わせないといけない場合は仕方がありません。

そんな時は、先ほど作った予算書の明細部分を、CONCATENATE関数という関数でつないで、文字列にしてしまいます。

上記の例で説明すると、F4のセル、@(単価記号)、1000円単位の単価、x(かけ算の記号)、G4のセルをつないで、一つの文字列にしています。

ちょっと面倒なような気もしますが、一回計算式を作っておけば後はコピペでできるので、手入力よりは絶対に早いですし、ミスもありません。

計算式の結果を、「値のみコピー」でコピペして書式に貼り付けていけば、だいぶ予算資料の作成が早くなります。

 

予算を月別に集計する

予算を月別に集計する必要がある場合は、先ほどの小計機能を使っても良いですが、ピボットテーブルを使うと良いでしょう。

サクッと月別に集計ができます。

 

予算の書式によっては、SUMIF関数を使った方が良い場合もあると思います。

必要に応じて、いろんな機能を使っていきましょう!

 

まとめ

 

予算を作るのは、実行計画を予算に反映させて、予算の結果をまた実行計画に反映させて・・・の計画の策定が目的のため、予算の計算自体に時間がかかっていては、その後の実行計画に反映させる時間が無くなり、ただ作っただけに終わってしまいます。

エクセルには、単純な計算といった事務作業に役立つ機能がたくさんありますので、それらの機能を使って、より早く、より正確な予算を作っていきましょうね。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

ABOUTこの記事をかいた人

昭和48年広島県生まれ。現在も広島に住んでいます。 趣味は音楽鑑賞(邦楽、洋楽問わず、クラシック、童謡も)、マンガです。エクセルを効率的に使う方法を紹介して、あなたのお役に立ちたいと思っています。