出席者・欠席者・出席率の計算表を、Excelのボタンと関数を使って作る
今日は、エクセルでちょっとした便利機能を実現してみましたので、復習がてら紹介しようと思います。
会社で毎月、メンバーが固定された定例の会議の報告書を作っているのですが、その回の出席者・欠席者と出席率を毎回手動入力していました。出席率は、開催基準を満たしていることの確認のため、欠席者名の表示は報告書回覧のためです。
んで、手動入力の常で、ミスが起きるんですよね。欠席者名を一人漏らしてしまったり、欠席者数と出席率が矛盾してたり、総人数を勘違いしてたり(笑)
この辺を自動化して、マウスポチポチだけでできるようにならんかなーと、ずっと思ってはいたんですが、出席者名と欠席者名の欄をそれぞれ人数分用意して、出席／欠席を選んだ方に表示させるだけなら簡単なんですよね。
こんなイメージ↓
でもこうじゃないんですよ。
歯抜けはカッコ悪いんですよ。
歯抜けじゃなくて、手で入力する場合みたいに詰めて表示したいんですよ。
んで、通勤電車内でいろいろ調べながら頑張って作ってみたのがこちらです。↓
実際に動作を確認してみたい方は、こちらからダウンロードして動かしてみてください（変なウイルスとかは仕込んでないのでご安心ください。マクロ無効ブックです）。
ブログのテーマにＶＢＡ練習を掲げておきながら関数とオプションボタンの機能の記事を書く。
ブログあるあるの一つ「テーマぶれ」ですね…あるよね？(笑)
動作解説
いちおー、どういう動作になっているか解説します。
ボタンの選択肢とセルを連動
「出欠」列のオプションボタンの選択は
隣の列のセルに連動しています。
出席フラグ・欠席フラグ
出席フラグ列と欠席フラグ列には、こんな式が入っています。
「出席フラグ」列の式は
条件分岐① Ａ列に文字が無ければブランク
Ａ列に文字があれば条件分岐②へ
条件分岐② Ｃ列が2であればブランク
Ｃ列が1であれば、「Ｄ列の２行から現在行までの最大数字」＋１
という内容です。
Ｄ２だけ絶対参照になっているのは、オートフィルで一気に式を入力できるようにするためです。
欠席フラグ列の式は、出席フラグ列の式を条件分岐②の１と２を入れ替えているだけです。
ここで、「ＡもＣも絶対参照にしとけば、出席フラグ列と欠席フラグ列いっぺんにオートフィルできるじゃん」と思われた方、素晴らしいですね。その通りです(笑)
なんせ考えながら作ってたもんで…
出席者・欠席者列
出席者列、及び欠席者列の式は以下の通りです。
ちと式が長いので、分解しましょう。
条件分岐
まずＩＦ式の条件分岐ですが、これは名前を表示するかしないかを判断するものです。
ＲＯＷ関数で取得した行番号の数字と、フラグ欄の最大数とを比較し、行番号の方が大きくなっちゃったら名前を表示しない、という内容です。
ＲＯＷ関数は行番号の数字を取ってくるだけに使ってるので、ここのＡはＢでもＸでもＡＵでもＶＷでもなんでもいいです。要するに、出席者の欄の何番目の行に当たるのかを判断してるだけ。
写真では、出席フラグ欄の最大数は４なので、４番目の行まで名前を入れて、それ以降はブランクになっています。
MATCH関数について
で、次。左から順に行くとすればINDEX関数の説明になるのですが、ここはあえてMATCH関数の説明をします。
こちらでもROW関数が出てきていますが、ここでは「出席者欄の現在セルの行番号が、出席フラグ欄の何番目のセルに入っているか」を判断しています。
例えば出席者セルの１行目（セルＪ３）については、出席フラグ欄の１の数字が何行目にあるかを探し、Ｄ４にあるのを発見し、２という数字（Ｄ３：Ｄ１３の範囲内の２番目の行なので）を取ってきます。
INDEX関数について
INDEX関数については、「指定範囲内で行番号、列番号で指定されたデータを取ってくる」という動作をします。
今回の場合、指定範囲はＡ３：Ａ１３。行番号はMATCH関数で見つけた数字。１列しかないので列番号指定は省略しています。
出席者の一人目の欄は、MATCH関数で見つけた数字が”２”なので、指定範囲の２番目の名前「ダンジョー」を取ってきています。
出席者数、欠席者数、出席率について
出席者数、欠席者数は、フラグ範囲内の最大数を取ってきているだけです。
出席率は、メンバー表の名前の数をCOUNTA関数で数えて、割り算しているだけですね。表示形式をパーセンテージに変更すればＯＫです。
こーゆーのをほしいと思った時に素早く作れると、僕ももう少し使える人材になれるんですけどねー…。今回は、下調べに２日。作業時間は通勤時間丸々（１時間くらい）かけてしまいました。
ちなみに、今回オプションボタンを使用してるんですが、これは入力の手間は１クリックで済むんですが、欄を増やしたい場合に面倒です。自動でコピーされないので、欄を増やすたびにオプションボタン２つと枠をコピーして、連動するセルの設定もやり直す必要があります。
というわけで、出席／欠席の選択をプルダウンリストに変更した物も作ってみました。
これだと、入力の手間は１クリックから３クリックに増えてしまうんですが、メンバーが増えた時の行の追加が凄く楽です。途中に行を挿入して、他の要素はコピペするだけでいいので。
どちらを選ぶかは、使う人と作る人、どちらの時間を大事にするかによりますね。
僕はプルダウンにしちゃおうかな…
編集後記
ここしばらく、タイマムシンさん呪術廻戦のアニメを見ておりました。
その主題歌の「廻廻奇譚」がカッコいい歌なので、今度機会があったら歌ってやろうと思って歌詞を見ながら聞いてみたのですが、「五条は大抵」だと思ってた部分が「五常を解いて」だったことが判明して、ちょっと自分の耳の性能に疑問が出てきました。
そうだよね、なんぼ主題歌とは言っても、そこまでキャラ推しの歌作らないよね…(笑)
聞きたい方はこちらからどうぞ。
それではまた。
タイマムシンのオススメ本はこちら
