Zenn
Open13

文化祭会計の効率化

Excel
文化祭
Y - AY - A

内容

  • 会計の仕組み

  • お金配布前

    • 参加する団体へどう説明するのか

  • お金を配布する時

    • お金を配布するときにわたす領収書的なやつ

  • 精算をおこなうとき

    • Excelでどうやってお金を計算するのか
      (各団体に入力してもらうとき)
Y - AY - A

仕組み

私の通う学校では、「生徒会費」として全校生徒から毎年同じ額のお金を集めています。文化祭の予算はこの生徒会費から支出されており，前年度の２～３月あたりに行われる予算決定会議[1]で承認された予算をもとに活動を行います。
文化祭の予算はだいたい150万円ぐらいです。(2023年は、1 588 000円)

前年度の12月ごろ

  • 文化祭の実行委員会が発足する

前年度の1月ごろ

  • 実行委員会の各班で購入したいものをまとめる
    総務班、会計班、教室班、装飾班、広報班、食物模擬店班、体育館後夜祭班
  • 文化祭に参加する団体(各クラスと部活動など)の予算を暫定的に決めておく

5月ごろ

  • 各団体の会計担当者に，会計のシステムを説明する
    冊子を配布

7月ごろ

  • 団体の担当の先生にお金を配布
    団体の種類によって金額が決められている
    会計班の班員それぞれにいくつかの団体を担当してもらう
    班員と団体の担当の先生それぞれに，受領証のようなものに名前を書いてもらい，保存しておく

9月ごろ

  • 文化祭を開催
    食べ物を販売する団体は，当日中にミスがないかを確認する
  • 清算
    各団体の会計担当者に，それぞれの団体で購入したものをExcelに入力してもらう
    団体の使ったお金を，保存しておいたレシートと突き合わせる
脚注

  1. 文化祭，体育祭，合唱祭の各実行委員会会計担当と，生徒会の会計担当が集まって予算について審議する会議。参加者は、生徒会、文化祭幹部、体育祭幹部、合唱祭幹部。 ↩︎

Y - AY - A

明細書に求める機能

  1. 掲載する情報
  • 団体の情報
  • 購入したものの一覧
    • 商品名，単価，購入個数，小計
    • レシート別の購入金額
  1. 以下の機能を持つ
  • 入力・計算・表示(印刷)を分離する
    • 入力する情報
      • 団体名
      • 配布額
      • 入力者の組番氏名
      • 買ったものの一覧(店舗に買いに行ったもの)
      • 買ったものの一覧(通販で購入したもの)
    • 自動で計算する情報
      • 必要事項が入力されているかどうか
      • 購入額の合計(店舗に買いに行ったもの)
      • 購入額の合計(通販で購入したもの)
      • 購入額の合計(すべて)
      • レシート毎の使用額
      • 残額(配布額-購入額の合計)
    • 印刷される情報 (カッコ内は具体例。かっこがあるものは，各団体の担当者に入力してもらう内容を直接参照する。それ以外はExcelの数式で計算する)
      1. 団体の情報の総括
      • 入力者 (5E24 苗字氏名)
      • 配布額 一次予算 (16,000円)
      • 配布額 二次予算 (3,000円)
      • 配布額 合計
      • 使用額 買い出し (8,510円)
      • 使用額 通販(2,980円)
      • 使用額 合計
      • 残金
      1. レシート別購入金額
      • 買い出し
        1. ○○
        2. ○○
        3. ○○
        4. ○○
      • 通販
        1. ○○
        2. ○○
        3. ○○
        4. ○○
      1. 購入したもの一覧
        (省略)
Y - AY - A

Excelにおける工夫

問題点

  • ページのレイアウトの設計が面倒である
  • すぐに数式の参照先がずれる
  • 各団体の担当者に入力してもらうと、数式を壊される
  • いつ印刷したものかを記録する方法がない(印刷直後に日付と時刻を書く暇がない)

改善する方法

入力用のシートと印刷用のシートを分離する

Y - AY - A

入力用シート

見出しのみが入力されたテーブルを入れておく。(テーブルの名前を_入力用テーブルとしておく)

Y - AY - A
  • 購入したいものの調査
商品名 購入方法 単価 購入個数(予定) 小計
ガムテープ 通販 150 3 450
Y - AY - A
  • 購入したいものの調査
レシート番号 商品名 単価 購入個数 小計
1 ガムテープ 150 3 450
1 消費税 45 1 45
Y - AY - A

印刷用シート

使用する関数(など)

  • テーブルの参照
    =_入力用テーブル[商品名]とか、=_入力用テーブル[単価]のような形で取得できる。(戻り値は配列)
  • =SEQUENCE()
    数列を出力するのに便利。
    入力用シートに入っているデータを印刷シートに転記する際、番号を付けたいとする。この時、『「テーブルを参照して返却された配列」の個数』を代入することで、1から件数分の数列を簡単に入力できる。 スピル により、複数のセルに自動で値が入る。オートフィルとか=ROW()とかを使わなくて良い
Y - AY - A

運用の流れ(予定)

  • 9/10 文化祭当日
    この日は食物模擬団体の精算がメイン

  • 9/13 文化祭片づけ日
    この日に、各団体に「明細書」を入力してもらう。

Y - AY - A

一枚目のシート


実際に使用したシートの1枚目

  • 灰色のセルのみ選択・編集が可能。
  • 団体の種類と明細書の種類は、リスト以外の値を入力できないようにしている。
  • 一次予算は、団体の種類ごとに決められた値を配布した。「団体の種類」の値に応じて自動で変わる。
  • 値が入っているセルには、すべて名前がついている。
    セル番地ではなく、直観的に理解しやすい日本語で関数をかける。
    セル範囲の名前の先頭に、半角のアンダーバーをつけることで、関数入力中に補完機能が働く。
    (日本語入力だと、文字を確定した後初めて補完ができる。これだといちいち日本語を入力する必要があり不便)


名前の定義における工夫

  • また、必要事項を入力したあと、詳しい入力方法を説明

    手順の説明
Y - AY - A

二・三枚目のシート

中身がほぼ同じ[1]なのでまとめて説明。


二枚目のシート

  • シートのロックは一切かけていない
  • 数値の入るセルは、入力時のIMEを無効にしたうえで、数値(整数)以外が入力されるとエラーとなるようにした。(どちらも、[データ]>[データツール]>[データの入力規則]からせっていが可能)


「無効」にすると半角/全角ボタンを押しても半角のまま入力される

  • テーブルとして設定しているため、関数の中で簡単にデータを扱うことができる。

    テーブル名を設定
  • 「小計」の欄に入っている数式は、=[@[単価(¥)]]*[@個数] である。(「@」は、「同じ行の」という意味らしい)
脚注

  1. 違いは「実際に店舗で購入したもの」と「通販で購入したもの」と、記入する内容を分けているだけ ↩︎

Y - AY - A

四枚目のシート


単純な入力ミスを可能な限り排除

例えば、単純な入力ミスとして以下のようなものが考えられる。

  • レシートから商品を転記する際に、
    • 転記漏れした
    • 同じ商品を二回転記した
    • 価格の末尾の0の個数を間違えた
    • レシート番号[1] を入力し忘れた

このような単純なミスを防ぐため、「二通りの方法で金額を入力してもらい、両者が一致するかを確認する」という方法をとった。

  1. 商品すべての価格をひとつづつ入力
    「二・三枚目のレシート」の項で説明した通り
    四枚目のシートの、「レシート番号」の一つ下のマス(B22)には、 =SEQUENCE(MAX(_table入力用_買い出し[レシート番号])) という数式が入っており、1からレシート番号の最大値までの値を スピルを使用して入力している。
    「OK/NG」の列の一つ右には、レシート番号1番と同じ行ののセルに、 =SUMIFS(_table入力用_買い出し[小計],_table入力用_買い出し[レシート番号],B22#) が入っている。
    つまり、各レシートで購入した品目の合計金額が入力されている(文字の色が白なので見えていないだけ)
  2. レシートに書かれている合計金額を手で入力してもらう(灰色のセル　条件付き書式を使用して色をつけている。ロックの解除を条件付き書式で設定する方法がおそらくないので、その列以外のみロックするようにしている)

上記1と2の値が等しいかどうかをを確認することで、入力ミスを防ぐ


レシート別金額を自動計算している

脚注

  1. 精算を効率化するため、各団体のレシート全てに番号をつけてもらっています。Excelで =SUMIFS 関数を使ってレシート一枚当たりの合計金額を計算するため、すべての商品にレシート番号を入力してもらう必要があります。「二・三枚目のシート」の一番左の行のことです。詳しくは、六枚目のレシートを参照 ↩︎