Fin POP

人生のノウハウや投資、子育ての学習をゲームのようにレベルアップできる情報を配信

家計簿を自分でカスタマイズするために役立つSUMIFの実力


スポンサーリンク

スポンサーリンク

f:id:Barrgan:20180424214428p:plain

過去に無料で使えるWEB家計簿をExcelで配布しています。

www.fin-pop.info

無料で使う場合はGoogleが提供する「スプレットシート」で活用できる家計簿です。
今後自分で家計簿をカスタマイズしたい人向けに私が配布しているWEB家計簿に使われるExcelの数式を紹介します。
初歩的な数式ですので、まだExcelでできる事や分からない人は一度参考にしてみてください。

社会人5年目で覚えた最初の数式

社会人になって5年が経過する頃に売り上げの集計などをする必要があり、Excelにあるデータを集計するのですが、データ量が多く引継ぎに教わったのが並び替え後に一行挿入からのSUMでの集計でした。
更にデータは手入力のため集計する際に同じ名前で全角か半角かやカタカナで記載されているなど名称一致していないものがあり悪戦苦闘。

毎週集計するのに時間が掛かり過ぎるのと入力されたものをわざわざ集計するのは面倒というきっかけからExcelの数式を覚えるようになりました・・・会社の質を疑うレベルですね。
ピボットテーブルという簡単に集計してくれる機能がありますが、報告用の書式に合わせてなおかつ自動で計算してくれるものを作成をきっかけの集計業務を効率化をしていきました。
その流れで自分のための家計簿を簡単に作れるのですぐに作ったのが冒頭の家計簿です。
家計簿を作る際に使った数式は1つだけです。

使う数式はSUMIFのみ

誰もが知っているSUM。更に一歩進んだSUMIF
SUMIFは「範囲内」の条件に合う検索条件と一致する合計範囲を集計します。
=SIMIF(対象となる範囲,検索条件,合計する範囲)
用するに集計表の中に食費や日用品に外食などの支出があります。SUMIFで食費だけの売り上げを調べたりする事ができます。
f:id:Barrgan:20180417230052p:plain

=SUMIF(J6:J61,G6,L6:L61) 食費で入力した際に下に移動する際にオートフィルを使って自動で貼り付けますが、範囲が1個下に移動した分、1個分ずれる時の対策として「$」を使います。
上記の数式を活用すると=SUMIF($J$6:$J$61,G6,$L$6:$L$61)または範囲を固定にする=SUMIF(J:J,G6,L:L)とJの全てとLの全ての範囲に設定する方法があります。
カスタマイズをする際には「なるほど!」と思えるので覚えておいて損はないです。

データ入力規則のリスト機能で効率化

f:id:Barrgan:20180417223417p:plain

家計簿の集計表の項目は必ずリストで選択するようにします。
リストで選択するようにしないと打ち間違えなどで検索条件と一致しないと集計しないので今月の生活費(変動費)の項目をリストで選ぶようにすれば、手入力の手間を省き、しっかり集計されるようになります。
リストの選択を作る方法は、上記のタブのデータ⇒データの入力規則⇒データ入力規則で設定の入力値の種類をリストに選択し、リストとなる範囲を元値の所で設定します。
f:id:Barrgan:20180417223814p:plain

f:id:Barrgan:20180417224204p:plain

Excelを普通に使っている人は当たり前の内容ですが、知らない人にはかなり有効に活用できる数式と機能です。
Excelが苦手な人でもまずこの数式と機能を覚える事で更にステップアップしてExcelを有効活用してみてください。