こんにちは、福田です。
仕事とはまったく関係ないのですが、小学校のPTA役員になって、会計の方からExcelでの管理についてご質問等々いただいたので、いくつかシートの見直しをしてみました。
その時に、「こんな関数があったのか！」とびっくりしたもの含めて、多用した関数ベスト３をお届けします
最後に、汎用的に使えそうな部分だけ切り出してまとめてみましたので、よかったらダウンロードして使ってください(^^)/
第3位 TEXTで和暦表示
PTAの資料って、元号表記が多いですよね。
私はぜんぜん使ったことなかったのですが、Excelって西暦を和暦に変換してくれるんですね！便利！！
期間表示のところなどで使いました。
TEXT(A1,"[$-ja-JP-x-gannen]ggge年m月d日;@") のような感じで利用できました。とってもいいですね！この数式の [$-ja-JP-x-gannen] この部分は、元年が表示されるようです！
本当は「R6」表記もあったらなーと思いましたが、「令6」が gge で表現できましたので、まーいっかなと思っております。
第2位 EOMONTHで月末を計算
EOMONTH(A1,0) で、A1セルに指定した日付の月末日を取得できます。A1=2024-12-01の場合は、2024-12-31が取得できます。 EOMONTH(A1,1) だと1カ月後の月末 2025-01-31 が取得できます。
これで、計算期間を算出したりできました。例えば1学期分の計算だとしたら、4/1～8/31までとかですね。
今回作ったExcelシートでは、開始日を入力すれば、１学期末、２学期末、年度末は自動で計算するようにしました。ただし、想定開始日は4/1なので、それ以外が開始日の場合は別途修正が必要ですm(__)m
逆に EOMONTH(A1,-1) とすれば、1カ月前の月末が取得できて、前年度分の日付を取得したいときに役立ちました。
第1位 SUMIFSで期間内の対象データの合計を取得
PTA会計データの場合、分類と期間と両方を見ないといけない場面があって、SUMIFだけでは対応しきれませんでした。そのため、SUMIFSで一気に条件設定ができて、非常にきれいな数式にすることができました！
SUMIFS($E$4:$E$50,$I$4:$I$50,$A7,$C$4:$C$50,">="&$H$6,$C$4:$C$50,"<="&$H$7) のように記載することで、各項目の期間分の集計ができました。下の図では、出納の中の「繰越金」を、4/1~8/31までに入金された分だけ合計して出力しています。
参考資料 今回作ったExcelブック
といっても、やっぱり現物を見ないとわからないよね！と思う方もいらっしゃると思いましたので、添付します！
いろいろ書きたいことがあるのですが、いくつか注意点を記載してみました。
- みんなが利用するExcelシートに関数を埋め込んだ時に、私は入力してほしくないセルはロックする派です。関数を修正する場合には、シート名を右クリックして、「シートの保護を解除」してから編集してください。シート保護はパスワード無しです。
- 1ページ目の年度名と開始日を入力すると、それ以外のシートの日付が連動します。
- 予算案に前年度の決算、今年度の予算を入力してください。今年度の予算のところの数字が、「1学期末会計報告」「2学期末会計報告」「年度末会計報告」に連動されます。
- このExcelファイルは、年度の初めに「はじめに」「予算案」を入力後は、「出納」シートに入力していけば、最後まで資料が勝手に作成されることを目的としています。備考欄は引き継がれませんので、備考は毎回入力をお願いします。
- 予算案～年度末会計報告まで、項目はすべて上から順番にまったく同じであることが前提です。また、予算案の項目を、「出納」シートの科目で選択できるようにしています。こうすることで、科目に変なものを入力されて集計できないことを減らそうとしています。
- しかし、リストになっていても上書きされてしまう可能性もあるので、そこは仕方ないと割り切っています
- 科目のデータの入力規則は、収入の部と支出の部で項目一覧がわかれていて、データの入力規則で設定がめんどうでできないと感じたので、「予算案」の
収入の部～予備費までを選択して入力規則にしています。科目が項目名とそろっていれば問題無いです。
- 「予算案」「年度末会計報告」の左上の＜４号議案＞みたいなのは、総会資料で使うとき用のものです。適宜書き換えたり削除したりお願いします。
活用してくださったら嬉しいです！(^^)/
