Practical Excel
応用:委員会・部活動名簿の作成

委員会・部活動名簿

 ここでは関数を応用し「委員会・部活動名簿作成ファイル」を作っていきます。

 「委員会・部活動名簿」作成ファイルは、先に紹介している「座席表」と同じく、名列データと検索関数の組み合わせで作成できます。しかし、学年・クラスのデータが追加される分「座席表」より少し複雑になります。多少複雑ではありますが、順を追って行けばその仕組みを理解出来ると思います。

 ここでの作成過程は、仕組みを知る事に重点を置くため、最もシンプルな形にしてあります。
 作成する「委員会・部活動名簿」のサンプルファイルはダウンロード出来ます。

 ※直ぐに使用できる、名簿作成支援ソフトもありますので、よければどうぞ。


委員会・部活動名簿の設計

 まずは、どのような「委員会・部活動名簿作成ファイル」にするかを考えます。
 では、ここで作る「委員会・部活動名簿作成ファイル」を簡単に説明をします。

◆設計概要
 ・全校生徒の人数は毎年300人未満なので、名列欄は300人分作成
 ・委員会・部活動の人数は最大でも40人なので、検索結果表示欄は40人分作成
 ・学年・クラス・番号、それぞれの欄に値を入力すると、隣に名前とふりがなが表示

◆使用シート
 「名列」シート・・・学年・クラス・出席番号・氏名・ふりがなの入力用シート。300人分の欄作成。
 「名簿作成」シート・・・学年・クラス・名前を入力することで、名簿を作成・印刷するシート。

 データの誤入力を防ぐため「名列」シートと「名簿作成」シートとは別に作成することにします。


名列データ欄の作成

 まず「名列」シートを作成し、そこに「名列データ」欄を作っていきます。
 名列データ欄に必ず必要なモノは、検索するための一意のデータ(場合によっては昇順)と氏名です。それ以外にも必要に応じて「氏名」欄の隣に欄を付け加えていきます。今回は「ふりがな」を追加します。

 「座席表」では、出席番号が一意でしたが、今回は学年・クラスが加わるので、出席番号では対応できません。どうしたら一意のデータになるでしょうか?
 「学籍番号を併せて入力する」等の方法が考えられますが、最も簡単な(だと思われる)方法は、「学年・クラス・出席番号」を繋げて一意のデータを作成する事です。

「通番」・・・人数把握のため
「結合欄」・・・学年・クラス・出席番号を繋げた一意のデータ欄。CONCATENATE関数で結合
「学年」・・・学年データ欄
「クラス」・・・クラスデータ欄
「番号」・・・出席番号データ欄
「氏名」・・・氏名データ欄
「ふりがな」・・・ふりがな欄。必須項目ではありません



セル範囲に名前を定義

 参照するセル範囲が固定されている場合は、セル範囲に名前を定義すると便利です。
 名前を定義するメリットは、「データの扱いが容易になる」「数式が見やすくなる」「他シートのセル範囲でも名前で指定できる」といった事が挙げられます。

  さて、名前を定義する方法は2つあります。1つは「セル・セル範囲を選択後、数式バー左の名前ボックスに任意の名前を入力」、もう一つは「挿入」→「名前」→「定義」で「名前と参照範囲を指定する」です。

 今回は、「名列」シートの3つのセル範囲にそれぞれ名前を定義しました。
 ・通し番号のセル範囲[A2:A301]:「Num_Data」
 ・結合欄のセル範囲[B2:B301]:「Per_Data」
 ・結合欄を含むデータ欄のセル範囲[B2:G301]:「Data_Area」



「名簿作成」シートの枠作成

 次に「名簿作成」シートの枠を作成していきます。
 枠はデータ入力用の「学年」「クラス」「番号」枠と表示用の「氏名」「ふりがな」枠の5つです。そして「学年・クラス・番号」の結合欄を作成します。
 セルから文字がはみ出るのを防ぐため、セルの書式は「縮小して全体を表示する※」に設定しておきます。

 ※セル範囲を「右クリック」→「セルの書式設定」→「配置」→「縮小して全体を表示する」にチェック


数式の挿入

 次に数式を挿入していきます。
 数式の処理は、データが入力されたら、「データを結合」→「結合データを基に検索・表示」の2ステップです。 では最初に、データを結合するために、結合欄に数式を挿入します。

◆データの結合
 データを結合しますが検索時のエラーを防ぐため、この段階で「学年・クラス・番号が入力されているか?」「結合されたデータが存在するか?」の確認し、2つの条件を満たした場合にのみ結合データを表示するようにします。

1.データの結合
 結合欄セル[A2]にセル[B2][C2][D2](学年・クラス・名前)の結合データを表示する。
 =B2&C2&D2 ※CONCATENATE関数でも可能
  このままでは「存在しないデータ」を入力した場合、検索時にエラーになります。それを防ぐために
 MATCH関数でデータの有無を確認します。

2.データの有無確認
 結合したデータ(B2&C2&D2)が[Per_Data]に存在するかをMATCH関数で調べる。
 =MATCH(B2&C2&D2,Per_Data,0)
 データが存在しない場合、MACTHの結果はエラーになります。

3.エラー処理
 IF関数とIS関数を用い、MATCH関数の戻り値で条件分岐。
 データがなければ(MATCHの結果がERROR値なら)空白、あればセル[B2][C2][D2]を結合し表示。
 =IF(ISERROR(MATCH(B2&C2&D2,Per_Data,0)),"",B2&C2&D2))

  これでデータの誤入力によるエラーを回避します。




◆結合データを基に検索・表示
 「データの結合」が正常に行われた場合、その「行」に氏名とふりがな(セル[E2][F2])を表示します。ここでは検索にVLOOKUP関数を使用します。

1.検索・表示
 VLOOKUP関数の検査値にはセル[A2]の結合データを指定し、セル範囲「Data_Area」の先頭列を
 検索し「Data_Area」5列目(氏名列)※の同じ行の値を返す。※ふりがなは6列目
 =VLOOKUP(A2,Data_Area,5,0)

2.エラー処理
 「1.」の数式だけだと結合データ(セル[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関数で該当する行の位置を返しています。


 

サンプルファイル

 これまで説明したもののファイルです。先にも記述しました通り「仕組みの理解」に重点を置いているため実用的な作りにはなっていませんので、自分なりに改変して下さい。尚、ファイルによる問題が生じないよう気をつけておりますが、万が一、本ファイルの使用によって損害等が生じたとしても、責任は取りませんので、悪しからずご了承下さい。

サンプルファイル「委員会・部活動名簿作成」・・・[sample_meibo.xls] 127kb

Excel Tips for Teachers

Copyright (C) 2009 坂江 保 All Rights Reserved.