Zenn
TRANs - 生徒会情報機構Publicationへの投稿
🧾

文化祭の会計業務を効率化する(Excel)

Y - A
2024/02/23に公開
Excel
Word
高校生
文化祭
神エクセル
tech

執筆開始からもう11か月経ってる

はじめまして，現在高校二年生のYasakoです。都内の中等教育学校に通っています。
2023年の12月まで、記念祭幹部会計班に所属していました。この記事では、今年の記念祭の会計業務のために行った工夫や効率化について書こうと思います。

※マクロなしです。関数で完結します[1]

目次

まず、記念祭の会計について説明します。

0. 会計の仕組みについて

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

前年度の1月ごろ

  • 記念祭の幹部が発足する
  • 幹部の各班で購入したいものをまとめる
  • 記念祭に参加する団体(各クラスと部活動など)の予算を暫定的に決めておく
  • 三幹部合同予算決定会議で、他の行事幹部と生徒会に予算案を提出する

5月ごろ

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

7月ごろ

  • 団体の担当の先生にお金を配布
    団体の種類によって金額が決めておいた

9月ごろ

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

10~11月ごろ

  • ミスの確認
    Excelに入力してもらった精算資料に、ミスがないかチェックする
    先生にミスを指摘してもらう

12月ごろ

  • 三幹部合同決算会議
    他の行事幹部と生徒会の承認を受けるための会議

1. Excelでできる工夫について

昨年度までは、パソコン室に各団体の会計担当を集め、買いたいものの商品名を入力してもらっていました。しかし、以下のような問題を抱えていました。

  • 入力に時間がかかるので、何度も会計担当を集めなければならい
  • 入力ミスが多発する
  • 記入済みのExcelファイルを、パソコン室の提出先フォルダに入れずに帰ってしまう（別の日に、もう一度生徒を呼び出す必要があり、面倒）
  • あらかじめ入力しておいた数式を、生徒が意図せず壊してしまう
  • (そもそも)Excelのブックに不具合がたくさんある
    • （4~5年ほど引き継がれるうちに、少しずつ壊してしまったのだと思う）
  • 表の途中に新しい行を挿入すると、レイアウトが崩れる
    • 新しい行を挿入をする際は、追記する部分より下に入力されているデータすべてを、一行ずつずらさなければならない）
  • レイアウトが汚い
    • ミスがないかを確認する際に不便
  • レイアウトを変更することができない

これらの問題を解決するため、インターネットで工夫の方法を検索したところ、 「入力するシート」と「印刷用のシート」を分ける という内容の記事を見つけました。（今検索しても見つけられませんでした。見つけたら追記します）

この、「入力と印刷（表示）の分離」という考え方により、かなりの労力を減らすことができました。

2. そもそもなぜ問題が発生するのか？

昨年度（2022年）の文化祭で使用したExcelのフォーマットで、上記の問題の背景にあるのは何だろうかと考えました。改善すべき項目として、以下の内容を考えました。

  • 入力してほしいところのみ入力できるようにする

    • プログラミングと同じように考えてみます。Excelのシートをデータベースとして考えるなら、入力されたデータはレコードにあたり、Execlに入力した関数はデータベースを操作するプログラムにあたるのではないでしょうか。
    • 編集されても問題のないシート（行や列の幅の変更も許容する）と、ロックをかけて編集できないようにしたシートを用意すればよい。
    • 印刷する情報をまとめるために必要な計算は、非表示にした計算用シートでやれば良いでしょう。

  • 読んでわかるように関数を使用する

    • Excelでは、プログラミングにおける変数のような「名前」を使うことができます。（日本語もOK）
    • 最近搭載された=slambda関数を使って、関数を定義することもできます。（VBAいらない）

  • わざわざパソコン室に来なくても入力できるようにする

    • SharePoint上にファイルをアップロードし、いつでも各自のパソコン [4]で編集できるようにする。

3. 改善のために使用する関数・機能

A. シートの保護

Excelでは、編集可能なセルと、編集不可能なセルを設定することができます。

B. 「名前」の定義

「名前」という変数を用いることができる。
名前を付けたい範囲を選択し、左上にあるセル番地が表示されているところに、設定したい名前を入力すると設定できる。（一度名前を設定したあと、別の名前を入力した場合、もともとついていた名前は削除されません。）「数式」→「名前の管理」でも設定、編集が可能。

C. テーブル

テーブルを使うと、以下のようなメリットがあります。

  • プログラムにおける配列のような書き方でデータを取得できる
  • セルの範囲ではなく、定義した名前でデータを取得できる
  • 罫線や塗る色を、自動で変えてくれる
  • データの並べ替えが簡単にできる

D. スピル

セルに入力した値や、入力した関数が返す値が配列だった場合、結果が別のセルに「あふれて(=spil)」結果が表示される機能です。

E. よく使う関数一覧

  • =SEQUENCE(行,[列],[開始],[目盛り])
    数字のリストを出力する。
  • =XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
    データを検索するのに便利
    複数の条件で検索することも可能（論理式を使う）
  • =FILTER(array,include,[if_empty])
    配列を操作する関数。新しい。
  • =MAX(数値 1, [数値 2], ...)
    入力されたデータのなかで、一番大きい値を返却する。
    使い方としては、『「テーブルに入力したデータの番号」の最大値を取得する』事で、データの件数を取得する
  • =SUMIFS(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)
    条件を満たす場所にあるデータの合計を取得
    例えば、団体別使用金額のデータがあったとき、特定の条件を満たす団体の使用額合計を計算するとき
  • =IF(論理式, [値が真の場合], [値が偽の場合])
    みんな知ってるIF関数。論理式では、配列を扱うことができる。
  • =COUNTIFS (条件範囲 1, 検索条件 1, [条件範囲 2, 検索条件 2],…)
    SUMIFS と似ている。条件を満たすデータの件数を取得。
    入力にミスがないかを確認する部分で使った。（詳しい説明はあとで）
  • =OFFSET(reference, rows, cols, [height], [width])
    特定の範囲をそのまま書き写すようなときに便利
  • =VSTACK(array, [array], ...)
    配列を縦につなげる。意外と使う。=HSTACKは横につなげる。

F. スピル範囲演算子

印刷専用のシートを作る際、印刷範囲が自動で変わるのが望ましいです。
OFFSET 関数を使って印刷範囲を動的に変更する方法もあるのですが、ヘッダーやフッターを編集する度に関数を入力しなおす必要があり、とても面倒です。そこで、スピル範囲演算子「#」を利用し、印刷範囲を動的に変更します。

G. 条件付き書式

これを使いこなせれば、スピルと組み合わせることで、データの件数の変化に合わせて罫線を変更することができます。
私の学校では、会計資料を紙とPDFとExcelの三種類で提出する必要があったため、印刷した際の読みやすさも重視する必要がありました。

H. カメラ機能（リンクされた画像の貼り付け）

これを使えば、美しい配置が簡単に実現できます。 極端な 方眼エクセルが必要ありません。
例えば、同じページに二枚の表を配置することを考えます。このとき、横のマスの個数が異なるけれど、表の両端をそろえたいとします。


こんな感じの表を作りたいとする

方眼エクセルを使って作ろうとすると、

こんな風にする必要があります。これは、まだ楽なほうです。
縦と横にいくつも表が並び、それぞれの升目の大きさも違うとなると、恐ろしく面倒な調整が必要になります。

改善案として、「リンクされた画像の貼り付け」があります。


これくらいの表なら普通に作れるだろう

印刷したいフォーマットをつくります。（上で使用したサンプルでは、別の入力専用のシートを参照し、自動で数値が計算されています。）
この範囲を選択し、「コピー」します。
何もない部分に張り付けた後、右下にあるボタンから、「リンクされた図」を選択します。

すると、

このような画像を作ることができます。元のセルに記入されている値を変更すると、この画像の表示も変更されます。
このようにして出力した画像を組み合わせることで、下のようなレイアウトを簡単に作ることができます。


「PDF/XPSの作成」から出力したPDFには、テキストが埋め込まれます

I. 外部ファイルの参照

Excelの数式では、他のファイルに入っているデータを読み込むことができます。(ただし、読み込まれるファイルを全て開いた状態にしておく必要があります。)

4. 実際にやった効率化

以前軽くまとめたスクラップです。
https://zenn.dev/yasako/scraps/259ee2846b0beb

A. Excelファイルの配布

Teams上で配信しました。

あらかじめ各団体の会計担当が所属するTemasのグループを作っておき、作成した入力用のExcelファイルをアップロードしておきました。

B. 入力

Excelのテーブル機能を使用しました。実際にお店に行って購入したものと、通販で購入したものを別々の表にまとめる必要があったため、テーブルも二つ用意しました。

C. PDF出力

「PDF/XPSの作成」の機能を使い、Excelのファイルが存在するパスにPDFを出力してもらいました。

D. 印刷

以下の流れで行いました・

  • パソコン室でSharePointを開いておく
  • 各自のパソコンでPDFを出力してもらう[5]
  • パソコン室のプリンターで印刷し、各団体の会計担当に渡す
  • 各団体の担当の先生のチェックを受ける

E. 決算会資料の制作

決算会のために作る資料のうち、もっとも負担が大きいのは、記念祭幹部が購入した物品すべてについて、予算との変更点をまとめることです。作業を簡単にするため、予算のリストと決算のリストにあるすべての商品に一意の番号をつけました。予算と決算で対応する商品番号を入力し、XLOOKUP関数でほかの値を取得することで、作業を効率化しました。

5. その他、コツ

A. 連番の振り方について

商品につける番号に、以下のような意味を持たせました

千の位 百の位 下二桁
班の番号 店舗での購入か、通信販売か 1からはじまる連番

こうすることで、後で商品が増えたとしても、商品に振る番号で困ることはありません。

B. 進捗管理について

複数人で業務をするときも、一人で何かをするときも、何日までに何をどれだけ終わらせるかの計画を立てておくことが大切です。たいていの場合、計画通りにはいきません。しかし、とりあえず計画を立ててみることで、見落としていた重要な点に気づくことが多いのも事実です。計画通りにいかないことが分かったタイミングでその都度計画を立て直すことが大切です。

C. 他人との情報共有について

チームで活動する際は、他人とのコミュニケーションが重要です。
「自分は相手に情報を伝えたつもりだったが、実際にはうまく伝わっていなかった」という失敗を防ぐためには、

  • 伝える側
    • 簡潔に短くまとめて伝える
    • 結論から伝える
    • メッセージとして残し、後から確認できるようにする [6]
    • 伝えた内容を、相手にまとめて言ってもらう
  • 聞く側
    • 聞いた内容を自分の言葉でまとめなおし、相手に伝えて誤解がないかを確認してもらう
    • (会話の途中、または直後に)その場でメモを取る
    • 取ったメモを相手に見せ、間違いがないかを確認する
    • メモの内容を相手に送っておく
      などの対策があります。

D. データに問題がないかなどをチェックする際は、人間が確認するものとパソコンが確認するものに分離する

計算ミスの有無は、数式が間違ってさえいなければパソコンに全て任せるべきです。逆に、規約上購入できないものを間違って購入していないかを確認するのは、人間でないとできません。パソコンができることは任せてしまいましょう。

6. ネ申Excelについて考えたこと

いわゆる方眼エクセルやネ申Excelなどが、ネット上で議論の的となっています。人によって極端に意見が分かれる理由として、「人によって言葉の定義が異なる」ことがあるようです。 [7]

私が考えた内容は以下の通りです。

A. Excelを使用する目的をはっきりさせる

Excelを使用する目的は、いくつかに分類できるでしょう。

  • データの集計・分析
  • データの収集(入力したファイルを回収する)
  • 印刷用資料の作成

どの目的で使うかによって、適切な使い方は異なります。印刷のみを目的としている資料(会議の議事録など？) [8]の場合、セル結合や方眼エクセルは、そこまで大きな問題にはならないでしょう。
逆に、外部に公開する用の統計データは、Excelの適切な使い方をするべきでしょう。

B. Excelの使い方を勉強する

数値データ入力されるべき箇所に、単意味含めたテキストデータが入力されていることがあるようです。また、表が1ページに収まらないから、途中でシートの一番上に戻ってデータが入力されていることもあるようです。それぞれ、表示形式や印刷範囲の設定方法などを知っていれば、またExcelの使用目的をはっきり認識できていれば、活用しにくいデータを公開することもなかったでしょう。

おわりに

読んでいただきありがとうございます。この記事で、少しでも誰かの役に立てたなら幸いです。

あくまでExcelはツールの一つであり、Excelを使用することが目的になってはならないと思います。

初めての記事なので、わかりにくいところがあるかと思います。アドバイス等をいただけるとありがたいです。

編集履歴

  • 2024/03/03
      1. Dの追加
脚注

  1. ぼくがVBAが嫌いだからです。また、VBAで書いたコードを、次の学年の生徒に引き継ぐことが難しいからです ↩︎

  2. 記念祭，体育祭，合唱祭の各幹部会計担当と，生徒会の会計担当が集まって予算について審議する会議 ↩︎

  3. 食品購入に充てる予算(70~80万円)は別です。合計して230万円ほどになります ↩︎

  4. 僕の学校では、生徒全員がSurfaceを持っているか借りています ↩︎

  5. パソコン室には、Excel2019がインストールされており、スピルやFILTER関数などの重要な機能が使えません ↩︎

  6. 私の学校ではTeamsが使用できます。先生に頼んで、会計班のメンバーと担当の先生が入ったグループを作っていただき、決定した情報をそこに流すようにしていました。(先生に直接会いに行くことは稀でした) ↩︎

  7. こちらの記事より https://www.publickey1.jp/blog/17/excelexcel_excelexcelpr.html ↩︎

  8. 議事録であれば、Markdownなどのテキストデータの方が良いと思います。しかし、新たなツールを導入すること」への反発や負担が大きすぎる場合は、Excelを本来の目的とは異なるツールで使っていたとしても、しょうがないと思います ↩︎

TRANs - 生徒会情報機構
TRANs - 生徒会情報機構Publication

TRANs（生徒会情報機構）は、デジタル化に取り組む生徒会間の情報共有・知見交流・技術共有・システム統合に向けて設立された機関です。地域間の情報格差の是正や技術的問題の早期解決なども目標として掲げています。 現在、Discordにおいて40名以上とともに活動しています！

Discussion