Practical Excel応用：座席表の作成 2009.02.06
ここでは関数を組み合わせ「座席表」を作っていきます。座席表はデータと検索関数の組み合わせで作成でき、「データ入力→結果表示」の典型的な例です。その仕組みを理解すると、他のケースでも活かせるはずです。
そこで、仕組みの理解に重点を置き、最もシンプルな形で、座席表を作成していきます。
サンプルファイル「座席表作成」 ダウンロード
書籍紹介本を執筆しました
好評販売中！
「教師のExcel ～校務（個人業務＋チーム業務）カイゼンのためのデジタルリテラシー～」
基本の学びなおしから校務をサポートするシステム作りまで（技術評論社）
教員みんなができる「チーム業務」のポイントを丁寧にわかりやすく解説しています。また「個人業務」を更に効率化するための学び直しと応用テクニックを解説しています。
この書籍の活用してもらうことで、先生方が児童・生徒に向き合う時間を、更に確保できることを願っています！
■ 詳細（技術評論社） ■ 内容紹介：note ■ 購入：amazon
座席表の設計
始めに行うのは設計です。どのような座席表を作成するのか、完成作品をイメージすることが大切です。では、ここで作成していく座席表を簡単に説明をします。
設計概要
・座席表の枠に出席番号を入力すると、隣に名前とフリガナが表示されるようにする。
・生徒の人数は最大でも４０人なので、４０人分の名列欄を作る。
・クラスの席次は、横が６列と７列の場合があるので、縦７列×横７列に対応する。
使用シート
「名簿」シート・・・生徒の出席番号・氏名・フリガナの入力用シート。４０人分作成。
「座席表」シート・・・座席表を印刷するためのシート。座席は１×１～７×７まで対応。
データの誤入力を防ぐため「名簿」シートと「座席表」シートとは別に作成します。
名列データ欄の作成
まず「名簿」シートを作成し、そこに名列データ欄を作ります。
名列データ欄に必ず必要なモノは、検索用の一意の番号（場合によっては昇順）と氏名です。それ以外にも必要に応じて「氏名」欄の隣に欄を付け加えていきます。
「番号」・・・今回は出席番号を使用します。
「氏名」・・・今回は書式を統一し、名字と名前の間は全角スペースで区切っています。
「フリガナ」・・・今回は、上記2つに加えフリガナ欄を追加しています。
座席枠の作成
次に「座席表」シートを作成し、そこに「座席枠」を作ります。
出席番号、氏名、フリガナの３つを表示できるように罫線で区切ってあります。
枠から文字がはみ出るのを防ぐため、座席枠のセル範囲の書式は「縮小して全体を表示する※」に設定しておくと良いでしょう。
※セル範囲を「右クリック」→「セルの書式設定」→「配置」→「縮小して全体を表示する」にチェック
関数の挿入
次に、出席番号を入力すると「氏名・フリガナ」が表示されるよう関数を挿入します。
「番号欄」・・・出席番号を入力する欄
「氏名」・・・「番号欄」に入力された番号を基に氏名を表示
「フリガナ」・・・「番号欄」に入力された番号を基にフリガナを表示
今回はLOOKUP関数を用いて検索と表示を行っています。LOOKUP関数の検査範囲は昇順（1,2,3...）である必要があります（この場合は出席番号のセル範囲[名簿!$A$2:$A$41]）。
ここで、LOOKUP関数の引数を見てください。名簿!$A$2:$A$41のように、シートの参照と絶対参照が組み合わさっており、非常に見づらくなっています。見やすくする方法はないでしょうか？
セル範囲に名前を定義
参照先のセル範囲が固定されているのであれば、セル範囲に名前を定義すると良いでしょう。
名前を定義するメリットは、「データの扱いが容易になる」「数式が見やすくなる」「他シートのセル範囲でも名前で指定できる」といった事が挙げられます。
さて、名前を定義する方法は２つあります。１つは「セル・セル範囲を選択後、数式バー左の名前ボックスに任意の名前を入力」、もう一つは「数式」タブ→「名前の管理」の「新規作成」で行います。
今回は「名簿」シートの３つのセル範囲にそれぞれ名前を定義しました。
・出席番号のセル範囲[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関数の第４引数は「検索の型」です。これは「検査値」と完全一致の値を検索するか、近似値を含めて検索するかを設定するものです。
近似値を含めた検索に設定の場合、「検査値」と完全一致する値が見付からない場合に近似値を検索します。
今回のような一意の番号を検索する場合、この「検索の型」を「近似値を含めた検索」に設定すると誤りの原因になるので気をつけて下さい。
座席表や成績処理など特定の一人を指定する場合は、完全一致（引数の値は「0」）が良いでしょう。
尚、完全一致の場合、出席番号が昇順である必要はありません。
INDEX関数とMATCH関数での作成
次は、INDEX関数とMATCH関数を利用したケースを紹介します。
INDEX関数もVLOOKUP関数と同様に、出席番号・氏名・フリガナの全てを含むセル範囲を指定します。そのセル範囲[A2:C41]に「Data_Area」と名前を定義しました。
尚、MATCH関数には、VLOOKUP関数と同様に「検索の型」があります。
上図の赤下線部は列の指定です。VLOOKUP関数と同様にセル範囲[Data_Area]の何列目であるかを指しています。この部分が「1」だと、出席番号が表示されます。
サンプルファイル
これまで説明したもののファイルです。先にも記述しました通り「仕組みの理解」に重点を置いているため実用的な作りにはなっていませんので、自分なりに改変して下さい。尚、ファイルによる問題が生じないよう気をつけておりますが、万が一、本ファイルの使用によって損害等が生じたとしても、責任は取りませんので、悪しからずご了承下さい。
サンプルファイル「座席表作成」 ダウンロード
