【Excelで年収を円単位で把握】エクセルに給与明細を打ち込んで収入を正確に管理してみる

f:id:virtualbox:20160131180208p:plain

 

年収を100万円単位で語る機会は多いが、自分の年収を正確に把握しているという人は珍しいのではないだろうか。自営業者やSOHOなどで自分帳簿をつけている人は馴染みがあるかもしれないが、大抵は収入についてはざっくりとしてしか把握していないもの。

 

昨今ではふるさと納税など、自分の年収を正確に把握していることで得をするケースがあり、毎月給与明細をとりあえず眺めるだけにしていた人は、今回紹介する方法を一度やってみるのはどうだろうか。

なお、対象としているのは僕と同じようなサラリーマンであり、毎月の給与明細なるものが存在している人のケースで紹介していく。

 

◆スポンサーリンク 

 

 

1.年収を正確に把握する目的

この項だけでも語ると長くなるため、別エントリーで改めて紹介するが、要するに得をするためだ。

いちサラリーマンの収入といっても、年収500万のサラリーマンであれば年間で支払う税額はゆうに100万を超えており、各種控除を利用することで数十万クラスの減額が可能だったりする(必ずしも全員に効果があるわけではないが)。

同程度の収入をもつSOHOや自営業者は皆帳簿を書き、賢く節税を日々検討してるのに、サラリーマンは源泉徴収であるため関心を持てる機会がどうしても少ない。

 

最近は「4〜6月の給料によって健康保険料が決まる〜」や、「ふるさと納税は所得の○○%で〜」などの節税の話題が多いが、自身の年収を正確に把握できているかいないかで理解に大きく差が出るだろう。対策を練る前に自己分析をしっかりやるのは基本として言うまでも無い。

 

2.Excelを使って自分の給料を項目ごとに集計

今回やることは単純で、毎月の給料をエクセルに打ち込み、それを合計するだけだ。

それなら芸が無いので、多少今後の分析のために項目ごとの合計を出したり、月ごとの推移がわかるようなものにした。

 

ちなみに、エクセルを使ったことが無い、という人は少数派だろうが、最近はmac派が急増したりなど、自宅PCにエクセルが入っていない、ということも珍しくないだろう。

今回は、macにインストールされたExcel2011と、少数派かもしれない環境で紹介していくが、windowsもmacもどちらにも対応しているgoogleのスプレッドシートなんかでも対応しているちょっとした関数だけを使用する。

 

使う関数は以下のものだけだ

  • INDIRECT
  • VLOOKUP 

給与明細をインポート

集計する基礎データが無いと話にならないが、給与明細なんて基本は紙であることが多いだろうから、ここだけは地道に打ち込んでもらう必要がある。

もちろん、エクセルやcsv形式で給与明細を会社から入手出来る人はインポートしてもらったほうが100倍楽ではあるものの、そういった企業は少数派だろう。

 

手打ちする時の注意事項がある。

  • 項目名の右隣のセルに金額を入れる
  • シートは月※ごとに分ける

※給与支給の周期であれば四半期でもなんでもよい

 

箱のイメージはこんなところだ

f:id:virtualbox:20160131184818p:plain

シートで月別に分け、同じ項目名で金額を変えて記載している。

f:id:virtualbox:20160131184918p:plain

 

AーB列を収入、CーD列を保険料などの差し引かれるものとしており、EーF列は無くても問題ないが、月ごとのて入力時の答え合わせとしてSUM関数でB、D列を合計するようにしている。

 

集計表を作成

月ごとのシート入力が終わったら集計用のシートを用意する。

縦に項目が並び、横に1月〜12月までを並ぶ簡単な表を作る。

f:id:virtualbox:20160131185900p:plain

 

check point

  • 黄色:シート名と一致させる
  • 緑色:各シートの項目名と一致させる

 

上の集計シート上の、B3:M9とB12:M18の範囲のセルにVLOOKUPとINDIRECT関数を使って、各シートの項目の金額を返していく。

 

f:id:virtualbox:20160131220813p:plain

手順①

セルB2 =SUM(B3:B9)

セルB3 =VLOOKUP($A3,INDIRECT(B$1&"!A:B"),2,0)

手順②

セルB2をコピー → C2:M2をドラッグして貼り付け Ctrl + VでOK

セルB3をコピー → B3:M9をドラッグして貼り付け Ctrl + VでOK

 

これで収入側の集計表が出来た。

恐らく以下のように出来ているはず。

 

f:id:virtualbox:20160131221511p:plain

 

あれれ、と。9行目がエラーとなっているけど、これは各シートにA9セル「特別手当」を読みに行ったのに無かったので迷子になっている状態。

ボーナス支給月のみ使うために用意してみたが、今回は不要なので9行目を削除するか、VLOOKUP関数の頭にIFERROR関数を入れることでエラーを回避できる。

 

f:id:virtualbox:20160131223230p:plain

セルB9 =IFERROR(VLOOKUP($A9,INDIRECT(B$1&"!A:B"),2,0),0)

上記の手順②同様セルM9までコピーしてみると、以下のようにエラーが消えて0の値が入り、2行目のSUM関数もエラーが解消される。

 

f:id:virtualbox:20160131223422p:plain

 

"差し引かれるもの"側も同様の手順で以下の関数を入れていく。

手順①

セルB11 =SUM(B12:B18)

セルB12 =VLOOKUP($A3,INDIRECT(B$1&"!C:D"),2,0)

※VLOOKUPの参照範囲のみA:B→C:Dに変える必要があり

手順②

セルB11をコピー → C11:M11をドラッグして貼り付け Ctrl + VでOK

セルB12をコピー → B12:M18をドラッグして貼り付け Ctrl + VでOK

 

f:id:virtualbox:20160131224240p:plain

 

最後に、N列に左側の12ヶ月をSUM関数で合計し、O列にN3(額面総額)に占める割合が出るようにしておく。ついでに桁区切り(3桁ごとにカンマ,を打つ)もやっておくと視覚的にもわかりやすい。

 

途中、色々と端折ったが完成版がこちら

f:id:virtualbox:20160131224442p:plain

 

今回はサンプルデータなので毎月ほぼ一定額になっているが、きちんと各月のシートを入力して用意していけば、N3セルを見ることで額面の年収を確認することが出来る。

また、このサンプルの場合、N20に額面総額-控除総額(差し引かれるもの)を入れて、いわゆる差引支給総額(手取り)を記載した。

 

漠然と年収300万、500万というイメージから、月単位で横に並べると具体的に自分にどれくらいのお金が流れてきているのかが感覚として掴めるようになる。

最近はマネーフォワードのような家計簿アプリも進化しているので、比較的シンプルな収入側のほうもこのように見える化しておくのはいかがだろうか。