Practical Excel
応用:時間割表の作成

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

時間割表

 ここでは関数を応用し「時間割表」を作っていきます。関数をセルと入力規則の2か所で使用していきます。
 ここでは仕組みを知る事に重点を置くため、最もシンプルな形で作成していきます。
 尚、ここで紹介する時間割表のサンプルファイルはダウンロード出来ます。


時間割表の設計

 まず、どのような時間割表を作成するか考えます。ここで作る時間割表を簡単に説明をします。

◆設計概要
 ・時間割枠でドロップダウンリストから教科を選択すると「担当」と「備考」が表示される。
 ・教科は選択科目をあわせても30未満なので、教科入力欄は30作成。
 ・時間割表は平日は6コマ、土曜は4コマの計34コマで作成。

◆使用シート
 「教科・担当」シート・・・教科名・担当・備考を入力するシート。欄は30作成。
 「時間割表」シート・・・教科の選択と時間割表の印刷を行うシート。34コマ用。

 データの誤入力を防ぐため「教科・担当」シートと「時間割表」シートとは別に作成します。


「教科・担当」シートの作成

 まず「教科・担当」シートを作成し、そこに「教科・担当・備考」欄を作っていきます。検索には一意のデータ(場合によっては昇順)が必要なので、昇順の「通し番号」と「作業列1,2※」を付け加えます。

 ※作業列は入力規則のリストを「可変リスト」にするため使用します。
  作業列に関する説明は「可変リストの作成」にあるため、ここでは割愛します。
  詳細→「可変リストの作成」

「通し番号」・・・A列。検索で使用。
「教科」・・・教科名を入力する欄。
「担当」・・・担当者を入力する欄。
「備考」・・・備考(場所など)を入力する欄。特になければ空白。
「作業列1,2」・・・可変リストのための作業列。



 次に、セル範囲に名前を定義します。

教科名欄(セル[$B$2:$B$31]):[Subject]
教科・担当・備考欄(セル[$B$2:$D$31]):[Data_Area]
作業列1(セル[$E$2:$E$31]):[Oper_Col]
作業列2(セル[$F$2:$F$31]):[Sub_Data]



「時間割表」シートの作成

 次に「時間割表」シートを作成し、そこに「時間割枠」を作っていきます。
 作成するファイルは、教科、担当、備考の3つを表示できるように罫線で区切ります。また、枠内に文字を納めるため、セル範囲の書式には「縮小して全体を表示する※」を設定しておきます。
 ※セル範囲を「右クリック」→「セルの書式設定」→「配置」→「縮小して全体を表示する」にチェック



関数の挿入

 次に関数を挿入していきます。
 処理は「リストに教科を表示」し、それに応じて「担当者・備考を表示」の2つです。

◆入力規則のリストに教科を表示
 「教科名欄(薄黄色部)を選択すると、リストに教科名を表示する」ためには、入力規則と関数を組み合わせます。「データ」→「入力規則」→「設定」→「入力値の種類」を「リスト」に設定した状態で、「元の値」に次の数式を挿入します。

 =INDEX(Sub_Data,1):INDEX(Sub_Data,COUNT(Oper_Col))

 これは、INDEX関数の「セルの参照を返す」特性を利用し、作業列2「Sub_Data」の1行目から「COUNT関数で数えたデータの個数」行目までを表示するように設定する、といった意味になります。



◆担当者・備考の表示
 担当者・備考の表示には次のような数式を使用します。尚、次の数式はセル[C4]用です。

 =IF(ISERROR(MATCH(C3,Subject,0)),"",VLOOKUP(C3,Data_Area,2,0))

 この数式の意味は、次の通りです。

 1.セル[C3]の値がセル範囲[Subject]内にあるかMATCH関数で調べIF・IS関数で分岐
   (データがなかったら(MATCHの結果がエラーなら)表示なし。あれば先に進む)

 2.VLOOKUP関数でセル範囲[Data_Area]からセル[C3]の値を検索し結果(担当者)を返す

 補足1:「担当者」と「備考」の数式は、VLOOKUP関数の第3引数(列の指定)が異なります。
 補足2:VLOOKUP関数の結果が空白だと「0」が返ります。




「0」ゼロ値の非表示

 前項の補足2に書いた通り、VLOOKUP関数は結果が空白だと「0」を返します。見栄えが悪いので、「0」を非表示に設定します。

EXCEL2003:「ツ-ル」→「オプション」→「表示」→「ゼロ値」のチェックボックスをOFF
EXCEL2007:「Officeボタン」→「Excelのオプション」→「詳細設定」→
         「次のシートで作業するときの表示設定」→「ゼロ値」のチェックボックスをOFF



 先のゼロ値が非表示になったのがわかります。



簡易時間割表

 ここまで作業を進めたら、必要でしたらシートに保護をかけ完成になります。

◆シート保護

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

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



サンプルファイル

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

サンプルファイル「時間割作成」・・・[sample_jikanwari.xls] 33kb

Excel Tips for Teachers

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