Practical Excel
応用:座席表の作成

【当サイト人気ソフト】
ナンプレ無双:問題作成・解析・印刷・プレイができる無料ナンプレソフト。雑誌掲載多数
ナンプレ無双プラス:ナンプレ無双に特別な機能をプラスしたシェア版。問題に磨きをかける機能他

座席表

 ここでは関数を組み合わせ「座席表」を作っていきます。座席表はデータと検索関数の組み合わせで作成でき、「データ入力→結果表示」の典型的な例です。その仕組みを理解すると、他のケースでも活かせるはずです。

 そこで、仕組みの理解に重点を置き、最もシンプルな形で、座席表を作成していきます。尚、紹介する座席表のサンプルファイルはダウンロード出来ます。

座席表の設計

 始めに行うのは設計です。どのような座席表を作成するのか、完成作品をイメージすることが大切です。では、ここで作成していく座席表を簡単に説明をします。

◆設計概要
 ・座席表の枠に出席番号を入力すると、隣に名前とフリガナが表示されるようにする。
 ・生徒の人数は最大でも40人なので、40人分の名列欄を作る。
 ・クラスの席次は、横が6列と7列の場合があるので、縦7列×横7列に対応する。

◆使用シート
 「名簿」シート・・・生徒の出席番号・氏名・フリガナの入力用シート。40人分作成。
 「座席表」シート・・・座席表を印刷するためのシート。座席は1×1~7×7まで対応。

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

名列データ欄の作成

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

「番号」・・・今回は出席番号を使用します。
「氏名」・・・今回は書式を統一し、名字と名前の間は全角スペースで区切っています。
「フリガナ」・・・今回は、上記2つに加えフリガナ欄を追加しています。



座席枠の作成

 次に「座席表」シートを作成し、そこに「座席枠」を作ります。
 出席番号、氏名、フリガナの3つを表示できるように罫線で区切ってあります。

 枠から文字がはみ出るのを防ぐため、座席枠のセル範囲の書式は「縮小して全体を表示する※」に設定しておくと良いでしょう。
 ※セル範囲を「右クリック」→「セルの書式設定」→「配置」→「縮小して全体を表示する」にチェック



関数の挿入

 次に、出席番号を入力すると「氏名・フリガナ」が表示されるよう関数を挿入します。

 「番号欄」・・・出席番号を入力する欄
 「氏名」・・・「番号欄」に入力された番号を基に氏名を表示
 「フリガナ」・・・「番号欄」に入力された番号を基にフリガナを表示



 今回はLOOKUP関数を用いて検索と表示を行っています。LOOKUP関数の検査範囲は昇順(1,2,3...)である必要があります(この場合は出席番号のセル範囲[名簿!$A$2:$A$41])。

 ここで、LOOKUP関数の引数を見てください。名簿!$A$2:$A$41のように、シートの参照と絶対参照が組み合わさっており、非常に見づらくなっています。見やすくする方法はないでしょうか?


セル範囲に名前を定義

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

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

 今回は「名簿」シートの3つのセル範囲にそれぞれ名前を定義しました。
 ・出席番号のセル範囲[A2:A41]:「Num_Data」
 ・氏名のセル範囲[B2:B41]:「Name_Data」
 ・フリガナのセル範囲[C2:C41]:「Kana_Data」



 セル範囲に名前を定義したので、先の数式のセル参照範囲を名前に変更します。




 随分見易くなりましたよね。


エラー処理

 これで、番号欄(薄緑部)の値を元に、検索ができる状態になりました。しかしこれで終わりではありません。何故なら、このままだと番号欄に値が入っていない場合に#N/Aのエラーになるからです。



 このような状況を想定し、エラー回避処理を入れておくことも大切です。では代表的なエラー回避処理を追加します。

◆IF関数で条件分岐

 IF関数は条件に応じて処理を分岐します。
 IF関数を用い「(番号欄が)空白だったら、何も表示しない」処理を数式に追加します。

 IFの構成は=IF(論理式,真の場合,偽の場合)です。ここに条件を文字で当てはめると次になります。
 =IF(番号欄セル[B2]が空白?,(空白の場合)何も表示しない,(空白でない場合)氏名表示処理)

 上記を基に数式を作成すると次のようになります。
 =IF(B2="","",LOOKUP(B2,Num_Data,Name_Data))



◆入力規則による入力値制限

 IF関数による条件分岐で、番号欄が空白の場合のエラー回避処理を追加しました。
 しかし、番号欄に予定外の値が入力された場合は、以前としてエラーが起こります。そこで、入力値の制限をします。入力値の制限は、Excelの機能の「入力規則」を活用します。

 入力値を制限するセル(ここではセル[B2])を選択し、「データ」→「入力規則」をクリックします。
そうすると、「データの入力規則」ダイアログボックスが表示されます。

 その中の「設定」タブを選択し、「入力値の種類」に「リスト※」を指定します。
 ※リストを指定し「ドロップダウンリストから選択する」のチェックボックスをONにすると、ドロップダウンリストから値を選択する形になります。

 「元の値」にリストに載せる値(ここでは出席番号の1~40)を指定します。セル範囲参照の指定も可能なので、名前を定義した出席番号のセル範囲「Num_Data」を指定※します。
 ※セル範囲を参照するの場合は「=」の後に参照範囲を指定します。

 こうすることで、入力値に制限を設けられます。



◆シート保護

 これで入力値によるエラーは回避出来るようになりました。
 あと気をつけなければならないのが、データや数式の誤消去です。何かの弾みでの誤消去がエラーに繋がります。その防止には「シートの保護」機能が有効です。

 「シートの保護」は文字通りシートを保護しますが、保護状態でも許可したセルのみ値の変更可に設定出来ます。

 シート保護時でも値の変更を可能にするセル(ここでは番号欄)を選択し、
「右クリック」→「セルの書式設定」→「保護」→「ロック」のチェックボックスをOFFにします。

 その後、「ツール」→「保護」→「シートの保護」→(保護にパスワードをかけるなら「シートの保護を解除するためのパスワード」を入力)→「OK」



簡易座席表

 ここまで作業を進めたら、後はセル範囲[B2:C3]をコピーし、該当セル範囲に貼り付けで完了です。



 さて、下図は別のパターンの座席表です。これまでの応用で作成できます。試してみましょう。



VLOOKUP関数での作成

 ここまでは、LOOKUP関数で検索・表示を行いました。では次にVLOOKUP関数で、検索表示する方法を紹介します。

 VLOOKUP関数使用には、「名簿」シートの出席番号・氏名・フリガナ全てを含むセル範囲を指定します。事前準備として、そのセル範囲[A2:C41]に「Data_Area」と名前を定義しました。





 VLOOKUP関数の第4引数は「検索の型」です。これは「検査値」と完全一致の値を検索するか、近似値を含めて検索するかを設定するものです。
 近似値を含めた検索に設定の場合、「検査値」と完全一致する値が見付からない場合に近似値を検索します。

 今回のような一意の番号を検索する場合、この「検索の型」を「近似値を含めた検索」に設定すると誤りの原因になるので気をつけて下さい。
 座席表や成績処理など特定の一人を指定する場合は、完全一致(引数の値は「0」)が良いでしょう。
 尚、完全一致の場合、出席番号が昇順である必要はありません。


INDEX関数とMATCH関数での作成

 次は、INDEX関数とMATCH関数を利用したケースを紹介します。

 INDEX関数もVLOOKUP関数と同様に、出席番号・氏名・フリガナの全てを含むセル範囲を指定します。そのセル範囲[A2:C41]に「Data_Area」と名前を定義しました。
 尚、MATCH関数には、VLOOKUP関数と同様に「検索の型」があります。



 上図の赤下線部は列の指定です。VLOOKUP関数と同様にセル範囲[Data_Area]の何列目であるかを指しています。この部分が「1」だと、出席番号が表示されます。
 

サンプルファイル

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

サンプルファイル「座席表作成」・・・[sample_zaseki.xls] 88kb

Excel Tips for Teachers

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