Worksheet function

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

ページトップへ戻る
Copyright(C) 2009- 坂江 保 All Rights Reserved.