Practical Excel委員会・部活動名簿の作成 2009.02.06
ここでは関数を応用し「委員会・部活動名簿作成ファイル」を作っていきます。
「委員会・部活動名簿」作成ファイルは、先に紹介している「座席表」と同じく、名列データと検索関数の組み合わせで作成できます。しかし、学年・クラスのデータが追加される分「座席表」より少し複雑になります。多少複雑ではありますが、順を追って行けばその仕組みを理解出来ると思います。
ここでの作成過程は、仕組みを知る事に重点を置くため、最もシンプルな形にしてあります。
作成する「委員会・部活動名簿」のサンプルファイルはダウンロード出来ます。
※直ぐに使用できる「名簿作成支援ソフト」もありますので、よければどうぞ。
書籍紹介本を執筆しました
好評販売中！
「教師のExcel ～校務（個人業務＋チーム業務）カイゼンのためのデジタルリテラシー～」
基本の学びなおしから校務をサポートするシステム作りまで（技術評論社）
教員みんなができる「チーム業務」のポイントを丁寧にわかりやすく解説しています。また「個人業務」を更に効率化するための学び直しと応用テクニックを解説しています。
この書籍の活用してもらうことで、先生方が児童・生徒に向き合う時間を、更に確保できることを願っています！
■ 詳細（技術評論社） ■ 内容紹介：note ■ 購入：amazon
委員会・部活動名簿の設計
まずは、どのような「委員会・部活動名簿作成ファイル」にするかを考えます。
では、ここで作る「委員会・部活動名簿作成ファイル」を簡単に説明をします。
設計概要
・全校生徒の人数は毎年３００人未満なので、名列欄は３００人分作成
・委員会・部活動の人数は最大でも４０人なので、検索結果表示欄は４０人分作成
・学年・クラス・番号、それぞれの欄に値を入力すると、隣に名前とふりがなが表示
使用シート
「名列」シート・・・学年・クラス・出席番号・氏名・ふりがなの入力用シート。３００人分の欄作成。
「名簿作成」シート・・・学年・クラス・名前を入力することで、名簿を作成・印刷するシート。
データの誤入力を防ぐため「名列」シートと「名簿作成」シートとは別に作成することにします。
名列データ欄の作成
まず「名列」シートを作成し、そこに「名列データ」欄を作っていきます。
名列データ欄に必ず必要なモノは、検索するための一意のデータ（場合によっては昇順）と氏名です。それ以外にも必要に応じて「氏名」欄の隣に欄を付け加えていきます。今回は「ふりがな」を追加します。
「座席表」では、出席番号が一意でしたが、今回は学年・クラスが加わるので、出席番号では対応できません。どうしたら一意のデータになるでしょうか？
「学籍番号を併せて入力する」等の方法が考えられますが、最も簡単な（だと思われる）方法は、「学年・クラス・出席番号」を繋げて一意のデータを作成する事です。
「通番」・・・人数把握のため
「結合欄」・・・学年・クラス・出席番号を繋げた一意のデータ欄。CONCATENATE関数で結合
「学年」・・・学年データ欄
「クラス」・・・クラスデータ欄
「番号」・・・出席番号データ欄
「氏名」・・・氏名データ欄
「ふりがな」・・・ふりがな欄。必須項目ではありません
セル範囲に名前を定義
参照するセル範囲が固定されている場合は、セル範囲に名前を定義すると便利です。
名前を定義するメリットは、「データの扱いが容易になる」「数式が見やすくなる」「他シートのセル範囲でも名前で指定できる」といった事が挙げられます。
さて、名前を定義する方法は２つあります。１つは「セル・セル範囲を選択後、数式バー左の名前ボックスに任意の名前を入力」、もう一つは「数式」タブ→「名前の管理」の「新規作成」で行います。
今回は、「名列」シートの３つのセル範囲にそれぞれ名前を定義しました。
・通し番号のセル範囲[A2:A301]：「Num_Data」
・結合欄のセル範囲[B2:B301]：「Per_Data」
・結合欄を含むデータ欄のセル範囲[B2:G301]：「Data_Area」
「名簿作成」シートの枠作成
次に「名簿作成」シートの枠を作成していきます。
枠はデータ入力用の「学年」「クラス」「番号」枠と表示用の「氏名」「ふりがな」枠の５つです。そして「学年・クラス・番号」の結合欄を作成します。
セルから文字がはみ出るのを防ぐため、セルの書式は「縮小して全体を表示する※」に設定しておきます。
※セル範囲を「右クリック」→「セルの書式設定」→「配置」→「縮小して全体を表示する」にチェック
数式の挿入
次に数式を挿入していきます。
数式の処理は、データが入力されたら、「データを結合」→「結合データを基に検索・表示」の２ステップです。
では最初に、データを結合するために、結合欄に数式を挿入します。
データの結合
データを結合しますが検索時のエラーを防ぐため、この段階で「学年・クラス・番号が入力されているか？」「結合されたデータが存在するか？」の確認し、２つの条件を満たした場合にのみ結合データを表示するようにします。
１．データの結合
結合欄セル[A2]にセル[B2][C2][D2]（学年・クラス・名前）の結合データを表示する。
=B2&C2&D2 ※CONCATENATE関数でも可能
このままでは「存在しないデータ」を入力した場合、検索時にエラーになります。それを防ぐために
MATCH関数でデータの有無を確認します。
２．データの有無確認
結合したデータ(B2&C2&D2)が[Per_Data]に存在するかをMATCH関数で調べる。
=MATCH(B2&C2&D2,Per_Data,0)
データが存在しない場合、MACTHの結果はエラーになります。
３．エラー処理
IF関数とIS関数を用い、MATCH関数の戻り値で条件分岐。
データがなければ（MATCHの結果がERROR値なら）空白、あればセル[B2][C2][D2]を結合し表示。
=IF(ISERROR(MATCH(B2&C2&D2,Per_Data,0)),"",B2&C2&D2))
これでデータの誤入力によるエラーを回避します。
結合データを基に検索・表示
「データの結合」が正常に行われた場合、その「行」に氏名とふりがな（セル[E2][F2]）を表示します。ここでは検索にVLOOKUP関数を使用します。
１．検索・表示
VLOOKUP関数の検査値にはセル[A2]の結合データを指定し、セル範囲「Data_Area」の先頭列を
検索し「Data_Area」５列目（氏名列）※の同じ行の値を返す。※ふりがなは６列目
=VLOOKUP(A2,Data_Area,5,0)
２．エラー処理
「１．」の数式だけだと結合データ（セル[A2]）が空白の際エラーになるので、IF関数でエラー回避。
=IF(A2="","",VLOOKUP(A2,Data_Area,5,0))
蛇足
作業セル（セル[A2]）を使用せず氏名欄（セル[E2]）のみに数式を挿入すると下図になります。見やすさや扱いやすさを考えて使い分けましょう。
入力規則
入力するデータ（学年・クラス・番号）は決まった値なので、セルに入力規則を設定します。
入力規則による入力値制限
入力値を制限するセルを選択し、「データ」→「入力規則」をクリックします。
すると、「データの入力規則」ダイアログボックスが表示されます。
その中の「設定」タブを選択し、「入力値の種類」に「リスト※」を指定します。
※リストを指定し「ドロップダウンリストから選択する」のチェックボックスをONにすると、ドロップダウンリストから値を選択する形になります。
「元の値」の箇所にリストに載せる値を指定します（学年のセル範囲では「1,2,3」、クラスのセル範囲では「A,B,C,D,E」、番号のセル範囲では「=Num_Data」）。
※下図は「座席表」での入力規則設定例です。
シートの保護
あと気をつけなければならないのが、データや数式の誤消去です。何かの弾みでの誤消去もエラーに繋がります。その防止には「シートの保護」機能が有効です。
「シートの保護」は文字通りシートを保護しますが、保護状態でも許可したセルのみ値の変更可に設定出来ます。
シート保護時でも値の変更を可能にするセル（ここでは番号欄）を選択し、 「右クリック」→「セルの書式設定」→「保護」→「ロック」のチェックボックスをOFFにします。
その後、「ツール」→「保護」→「シートの保護」→（保護にパスワードをかけるなら「シートの保護を解除するためのパスワード」を入力）→「OK」
※下図は「座席表」でのシート保護設定例です。
INDEX関数での作成
ここまではVLOOKUP関数で作成してきましたが、INDEX関数とMATCH関数を利用したケースを紹介します。
INDEX関数もVLOOKUP関数と同様にセル範囲「Data_Area」を使用します。尚、MATCH関数で該当する行の位置を返しています。
サンプルファイル
これまで説明したもののファイルです。先にも記述しました通り「仕組みの理解」に重点を置いているため実用的な作りにはなっていませんので、自分なりに改変して下さい。尚、ファイルによる問題が生じないよう気をつけておりますが、万が一、本ファイルの使用によって損害等が生じたとしても、責任は取りませんので、悪しからずご了承下さい。
サンプルファイル「委員会・部活動名簿作成」 ダウンロード
