バーコード管理.xlsmを「作る」には？～②「全体の構図」編～
＜この記事は、Twitterで配布している「バーコードで提出物管理」の解説記事です〉
小学校用のものは「教室に映し出して、提出状況がリアルタイムで分かるようにする」のが目標でした。なので、
① バーコードで読み取った数字列をガンガンためてくゾーン
② ①を参照し、データ解析するゾーン
③ ②を参照し、リアルタイムで提出状況がわかるゾーン
④ ③を参照し、全体の傾向等を解析し、③に還元するゾーン
以上の4つのゾーンに分け、１つのシートにまとめています。
厳密に言えば、②や④のゾーンはほかのシートでも構いません。しかし、①と③を同じシートにすることで、「出力を見ながら入力をする」というようにしています。
では書いていきますが、まず最初にやるのはセルやゾーンのレイアウトを決めること。特に③のゾーンは見た目が大切なので、最初に作ります。最終的なゴールを意識してから取り組まないと、やり直しがものすごーーく大変になってつらいです（と言っても、作りながらガンガン修正していくわけですが…）。
では、実際につくった順に。
③ ②のゾーンを参照し、リアルタイムで提出状況がわかるゾーン のレイアウト
まずはここから。例示のシートだと、A2からU16までです。
最初にも書いたとおり、最終的なゴールイメージをもつことが大切。今回のヤツは、
「なんか表になってて、横に名前、縦に提出物。とりあえず5種類くらいでいいかな。提出したらチェックマークが入って……出した人は名前が消えたら、誰が出してないかすぐ分かるな。…あ！あと何人なのかもカウントしとくと分かりやすい！」
てな事を考えて作ったのが、冒頭にもあった図です。
2行は、タイトル行。「あれ？1行目は？」と思う方は、行を再表示してみましょう。日付とかが出てきました。これは、データを他のシートに転記するときのアンカーとして入れたもの。簡単に言うなら、この日付を目印にしてどこの列に入れるかを判断させているのです。ほかにも、クラスの人数だったり、それぞれの提出物の提出合計だったりをカウントしています。
A2の部分は、「どの項目を表示するか」選べるようにしてあります。ここをプルダウンで選べば、全体が切り替わる！というイメージです。（やり方は、「データ入力の規則」で調べてみましょう！）
あとは、「名前とチェックマークがあると分かりやすいかなー」というイメージで、場所づくりだけしておく。これで、とりあえず見た目は完成です。
① バーコードで読み取った数字列をガンガンためてくゾーン
次に作ったのが、バーコードを入れていくゾーンです。例示のシートだと、W列とX列。
③でU列まで作ってしまったので、とりあえず1列あけてW列にしました。1行目は空白にしておき、2行目にタイトルの「提出」を入力しておきます。…が、後でマクロを組むときに「列の1番上から検索して、空白の行を見つけてそこを選択する」というプログラムを作りました。なので、W1にも「提出」と入れてマスを埋めています。
そして、隣のX列には「欠席」した人の番号を貯めます。ここに1つでもコードを入れると、そこから出席番号を読み取り、③の欄を「欠」で埋めるというようにしよう、と。上の2行に「欠席」と入れたのは、W列と同じ理由です。
ここは、上から順に貯めていくだけなのでこんな感じ。プログラムの根幹は、②と③です。
② ①を参照し、データ解析するゾーン
いよいよ本丸。
ここは、貯めたバーコードからデータを抜き出し、③にデータを送るという作業をしています。シートでいうと、y列とz列です。
「データを抜き出し、データを送る」なんて言うと物々しいですが、仕組みはシンプル。mid関数を使って「出席番号と提出物番号」を抜き取っているだけです。
W列からZ列までの文字を、白から黒に変えてみました。たとえばz列には、こんな式が入っています。
=mid(w○,6,3）
これは、「W○セルの左から６文字目を先頭に、３文字分抜き出せ」という関数です。今回のバーコードでは、＊を先頭に年度・年組・出席番号・提出番号・アスタリスクという文字列を入力しています。その中から「出席番号」と「提出物番号」を抜き取るため、＊を含めた６文字目からの３文字分を参照しているというわけです。
これが抜き取れれば、「出席番号２５番の人が、項目１を提出した」という情報が分かるわけです。
同じ仕組みが、Z列にもあります。ここはx列から、出席番号だけを抜き出すmid関数です。あえてここには書かないので、どんな関数にすればよいのか考え、実際に見て答え合わせをしてみてください。
③ ②のゾーンを参照し、リアルタイムで提出状況がわかるゾーン の中身
さて、ここまで準備ができたら③のゾーンに戻ります。最初にやったのはただのレイアウトだけでした。なので、C3：H16、J3：N16、Q3：U16に
「Y列やZ列を参照して、Y列に入力されれば『✔』を、Z列に入力されれば『欠席』をそれぞれ出力する関数」
が入れば、OKなわけです。具体的には、こんな関数。
=IF(COUNTIF($Y:$Y,$▲▲*10+■)>0,"✔",IF(COUNTIF($Z:$Z,$▲▲)>0,"欠席",""))
▲▲は、その子の出席番号が入ったセルの番地。■は、提出物番号が入ります。たとえばD8であれば、「出席番号６番」の「提出物番号２」なので、
=IF(COUNTIF($Y:$Y,$A10*10+2)>0,"✔",IF(COUNTIF($Z:$Z,$A10)>0,"欠席",""))
関数の内容を少し説明。見ただけで「あーなるほど」とわかる人は、次にとんでOKです。
=IF(COUNTIF($Y:$Y,$A10*10+2)>0,"✔",IF(COUNTIF($Z:$Z,$A10)>0,"欠席",""))
これは、２つのIFを組み合わせて３つのパターンを出力しています。
①の中身は、次のようなイメージ。
Y列には、バーコードから抜き出した３桁の数が入っています。最初の２桁が出席番号で、最後の１桁が提出物番号。なので、出席番号であるA10を10倍して提出物番号を足せば、バーコードから抜き出した３桁の数と一致するわけです。「COUNTIFって？」「$とか*とか、何？？」という方は、関数で調べてみましょう。
②の中身は、次のようなイメージ。
Z列の方は、出席番号しか抽出していません。なので、出席番号がそのまま入っているA10と照らし合わせればOK。
こんな関数が、C3：H16、J3：N16、Q3：U16に入っています。
④ ③を参照し、全体の傾向等を解析し、③に還元するゾーン
あとは、残りの作業です。
まずは、C列からV列までの20行目から33行目。ここは、③のゾーンの対応しているセルが空白なら0、それ以外（✔か欠席）なら1を入れるという作業をしています。
ここのゾーンの機能は２つ。１つは、ある提出物の提出済み合計数をカウントすること。もう１つは、一人ひとりがいくつ提出物を出しているかカウントすること。この「全部提出した人がわかるようにする」という機能をつけたかったので、わざわざ作業スペースとして作りました。デフォルトでは、文字色が白なのでわかりづらいですが…。
文字色を黒にすると、こんな感じです。H列やO列、V列には、それぞれの行の合計数が計算されています。
それらを参考にして、17行目でそれぞれの列での提出合計数を出します。また、さらにその合計数をQからUの１行目で計算し、S15の「残り人数」計算に使っています。S15はOFFSET関数を使っているのですが……解説はまた今度。とりあえず、関数を使った作業はほぼ完成です。
その他
その他に使っているのが、
・条件付き書式
・入力規則
・VBAマクロ
などなど。この辺はまた、個別で解説しなければいけない程度にはボリューミーなので…後日改めてにしようと思います。
終わりに
いかがだったでしょうか？バーコードから抽出し、それを参照するところに焦点を当てながら、全体の構成を解説してみました。
ここまで書けば、「じゃあ、こういうのはどうだろうか？」と改良してくれる人が増えるのではないか。あるいは、「ここはこうするとどうでしょうか？」という前向きな意見などももらえるのではないかと思ってみたり。あるいは、実際に使ってみた感想などいただけるととても嬉しいです。
読んでいただき、ありがとうございました！😀
この記事が気に入ったらサポートをしてみませんか？