ExcelでQC（業務改善）！！
どうも、Kaiです。大学院も９週間を終え、１週間の休みを迎えられてホッとしています。今回は教員ハック。教員の仕事に欠かせないExcelに関しての記事です。前回は、記録の電子化をご紹介しました。前回はコチラ
今回も応用編です。
Excelの応用に必要な視点はズバリ教育現場の当たり前をExcelで効率化できないかであり、それが結果、他の先生があまりしていない意外な活用につながるでした。今回のターゲットは定期的に使う、形式と手順の変わらない書類です。
・毎回使う書類（特殊勤務実績や勤務時間の割振申請）
・毎月使う書類（部活動報告書）
・使用頻度の高い書類（物品購入伺や県外出張伺・復命書）
こういった学校から決められている「当たり前」となっている書類たちをカスタマイズしていきましょう。今回は、部活動の物品購入伺を例に応用ワザを書いていきます。まずは完成形であるコチラをご覧ください。
今回はいよいよSheetをまたぎます。伺書の黄色部分を埋めれば即提出可能で、左のシートの上のボタンを押せば出納簿も一瞬で自動で完成するシロモノです。完成形の活用イメージは以下の通りです。
ボタン１つでこの通り！
下の各伺書のデータ(今回は３枚目まで)が一瞬で転記されました！
一方初期化もボタン１つでこの通り！
綺麗さっぱり消えました。
予算がいくら残っているか。
昨年度と同じものを買うときにどこの何を買ったか。
これらは時間をかければ解決するけど地味に面倒な時間です。生徒会の担当の先生に予算がいくら残っているか聞いたり、事務室に保管されている昨年度のファイルを確認したりと工数が増えがち。これらをすべてまとめてしまおうというのが趣旨です。この応用術メリットは以下の３つ。
・使い手に易しい
・出納簿を一瞬で更新
・翌年も使える
そのために活用した機能は以下のとおり。
１. セルのメモ、コメント
２. 条件付き書式 空欄にハイライト
３. 関数・・・SUM、IF
４. マクロ・・・データの集約、初期化
マクロ？！と思った方もいらっしゃるかもしれませんが思っているより簡単なので大丈夫です。
では早速見ていきましょう：）
洗練された定期使用ファイルの作り方
物品購入伺を例に進めていきます。
都道府県や、学校種（小・中・高）でも違うと思いますが、だいたい被りそうな項目を選んで作ってみました。本質は変わりませんのでご自身の使用している形式に当てはめて活用してみてください。
準 備
①まずは、学校から指定されているファイルをコピーして自分用を１つ作りましょう。
②次に、Sheetを増やします。
出納簿は新しく作るので画面下の＋マークを押しましょう。Sheet名を出納簿としておくとわかりやすいです。
伺書は編集を終えてからコピーするのでそのままでOKです。
③出納簿Sheetに次のような表を作りましょう。
・１つも数式や設定を入れていない文字だけの表です。
・セルの幅は特に決まりはないのでそれっぽくて大丈夫です。
・色は項目が見やすいように単純に塗りつぶしてみました。
・複数の行や列の幅を同じだけ変えたいときは、Ctrlを押しながら複数選択できます。
品名は４列、規格は３列、購入先は３列。これは、後に編集する伺書で対応するデータに合わせるためです。下のSheetの列数に対応しています。
品名は４列(C-F)、規格は３列(G-I)、購入先は３列(N-P)。ここが学校によって異なると思いますので、それぞれの伺書に設けられた列数に対応させてください。
①セルのメモ、コメント
指定のセルで右クリックすると、新しいコメントもしくはメモをつけることができます。今回は「予算を入力してください」とメモをつけました。これで編集者が変わっても安心です：）
②条件付き書式 空欄にハイライト
次に、伺書を編集していきましょう。
コチラが完成形です。
記入しなければならない部分に条件付き書式でハイライトします。印刷時に映りにくい黄色の網掛けがオススメです。やり方はコチラ
③関数 SUM
では、完成形の出納簿のSheetの関数をチェックしてみましょう。
数式リボン ― 数式の表示 でしたね。
なんと、SUM関数のみです。笑
学校の元データを使っている伺書のSheetには数式はありません。ということで、出納簿については残高の欄にSUM関数を組み、「= 予算 – 支出の合計(SUM関数)」を入力して関数は終了です。伺書の方は、合計の欄へSUM関数を入れましょう。
表示形式は「通貨」にしておきましょう。
④関数 IF
提出書類の方は、完成形にはこんな感じで数式が入っています（数式を表示しているので横に伸びていますが、伺書です。）これからこれを作っていきましょう。
上４つの項目(会計年度、会計名、科目、部活動)は検索値が別Sheet(今回なら出納簿)の値を引っ張ってきているので注意です。出納簿のそれぞれの項目に対応するように式を作りましょう。IF関数を使うことで値が入っていなければ空欄で返すように設定します。
Sheet名「(1)」の会計年度の欄には=IF(出納簿!E3=””,””,出納簿!3)と入力されています。
これは下のSheet「出納簿」のE3のセル(令和５年と記入されているセル)＝空欄ならば、空欄のまま、空欄でなければE3の値を反映させる、という式です。他のハイライト箇所の式も同じようSheet名出納簿のデータに対応しています。
Sheet名「出納簿」の以下の部分から値を引っ張ってきています。
下の関数は数量欄が空欄なら空欄、そうでなければ数量×単価を反映させるという式です。
ここまでできたら伺書をコピーしましょう。Sheetと書いてある部分で右クリック、コピーにチェックをしてOK。
↓
ちなみにコピーされたSheetの名前は、たとえば「出納簿 (2)」のように Sheet名 (2) と記載されます。こだわりがなければコピーするSheet名は (1) としておくとコピーされたSheetが(2), (3), (4)…と表示されるので編集いらずで楽チン。今回は年間で５回提出することを想定し、５つコピーを作りました。
⑤マクロ 開発タブ
さて、最初の謳い文句を覚えているでしょうか。伺書の黄色部分を埋めれば即提出可能で、左のシートの上のボタンを押せば出納簿も一瞬で自動で完成するシロモノでした。一瞬で処理するために地道な命令を今から作っていきましょう。
このそれぞれの伺書のデータが出納簿の欄へ自動で飛べばOK。出納簿と伺書を見比べてどの項目が、どの項目に対応するのか確認してみましょう。では、いよいよマクロを組んでいきます！マクロは簡単に言えば、「自動処理」になります。それぞれの項目が自動で集約されるとありがたいですよね。それを今から組んでいきます。
まずは、開発タブを設定しましょう。
【Macの場合】
緑のバーの「…」からその他のコマンドを選択。
リボンのメインタブにある開発にチェックする。
これで開発タブがリボンに現れました。
【Windowsの場合】
ホームからオプションを選択。
リボンのユーザー設定から開発にチェック。
これで同じく完了です。
今回はデータを一斉に集約するボタンと
伺書に直接記入したデータをすべて消すボタンを作成します。
手順としては、
①命令を作成
データを一斉に集約する命令 ＝α
データを一斉に消す命令 ＝β
②ボタンを２つ作成
③各ボタンにαβの機能を付与
という流れになります。この処理を覚えれば、γ、Δ・・・と命令を作ってさえおけばボタンを作って、いつでもその処理が可能になります。
ぜひ頑張りましょうね：）
⑥マクロ 集約
くどいようですが最初の謳い文句を覚えているでしょうか。ボタンを押せば出納簿も一瞬で自動で完成するシロモノでしたね。
データを一斉に集約する命令 ＝αとしたときのαの中身は
α＝Sheet(1)の各データをSheet「出納簿」の指定の欄にコピペする、Sheet(2)の各データをSheet「出納簿」の指定の欄にコピペする、・・・Sheet(5)の各データをSheet「出納簿」の指定の欄にコピペする
です。一瞬で自動で処理の正体はこの地道な一連の処理を記録してExcelにやってもらうということです。では記録していきましょう：）
１. マクロの記録
開発タブからマクロの記録を選択してください。
起動したウィンドウのマクロ名の欄に命令名を記入しましょう。データを集約する命令のタイトルとなりますのでわかりやすい名をつけましょう。
このデータの集約が先ほどのαに該当します。OKを押すと、ここから記録終了のボタンを押すまでの動作がすべて記録されます。伺書の必要な値を１つずつ全て出納簿にコピペしていきます。伺書の品名・規格名の欄からコピーしていきましょう。
値貼り付けをしてください。
購入先と支出額の合計も同様にコピペ(値の貼り付け)します。
Sheet (2)へ移動し、同じことを繰り返します。これを(3), (4)…とすべてのSheetで行います。少し大変ですが後でラクできるので頑張りましょう！すべて終わったら記録を終了をクリック。クリックされたところまでが「データの集約」という命令に記録されています。
２. ボタンの作成＆マクロの登録
開発タブの挿入をクリック。
ボタンを選び、作ります。
ボタンに名前をつけましょう。
ボタンで右クリックしてマクロ名「データの集約」を登録します。
完成です！ お疲れ様でした！！
⑦マクロ 初期化
データを一斉に消去する命令を作ります。
データを一斉に消す命令 ＝βとしたときのβの中身は
β＝Sheet「出納簿」に記載されたデータを消去する、Sheet(1)に入力されたデータを消去する、Sheet(2)に入力されたデータを消去する、・・・Sheet(5)に入力されたデータを消去する
です。繰り返しますが一瞬で自動で処理の正体はこの地道な一連の処理を記録してExcelにやってもらうということです。では記録していきましょう：）
１. マクロの記録
開発タブからマクロの記録を選択してください。
起動したウィンドウのマクロ名の欄に記入しましょう。
データを消去する命令のタイトルとなりますのでわかりやすい名をつけましょう。
このデータの集約が先ほどのβに該当します。OKを押すと、ここから記録終了のボタンを押すまでの動作がすべて記録されます。
出納簿の記録を選択して消去。
各Sheetの黄色部分を選択して消去。
これらをすべて消去し最初の白紙状態にして、記録の終了を押します。クリックされたところまでが「データの消去」という命令に記録されています。
２. ボタンの作成＆マクロの登録
先ほどと同じようにボタンを作り、マクロを登録していきます。
ボタンを選び、作ります。
ボタンに名前をつけましょう。
ボタンで右クリックしてマクロ名「データの消去」を登録します。
完成です！ お疲れ様でした！！
これで全行程終了です。５回分の提出書類とボタン１つで対応する出納簿が完成しました。僕はサッカー部で購入機会も多かったので１５回分ほど作成していました。次年度はこれをコピーして初期化ボタンを押せばすぐ使えます。さらに、昨年度のデータをすぐに見直せるので非常に効率的でした。
まとめ
いかがでしたか。
今回は物品購入を例に、書類データのカスタマイズについて書いてきました。今回はマクロも入ってきたので難しかったですね。正直ここまでする教員はなかなかいないと思います。
このファイルはマクロを組まなくても十分役に立ちます。ただ、マクロの記録が上手に使えるようになれば、圧倒的に時短が実現します。例えば特定のSheetへのジャンプや、印刷もボタン１つでできるようになります。
僕もかつて生徒会の会計を担当していたときに全部活の出納簿を汗水流しながら何時間もかけてやっていました。それをマクロに変えたらその業務はボタンを押して終了。処理時間合わせても10秒の業務となりました。あの衝撃は計り知れないものがありました。
「最初はこんなことを覚えるのに多大な労力を費やして大丈夫かよ…」と思っていましたが、Excelの凄さと可能性を確信した瞬間です。ぜひ皆さんにもこの利便性を味わってほしいと思います。
１つお伝えすると、今回のように１つのファイル内でSheetをまたぐなら比較的シンプルですが、複数のファイルをまたぐ場合はVBAといってコードを理解する必要があるので年度更新時などには注意が必要です。どんどん身の回りのものをExcelに任せていきましょう：）
では。
コメント