Worksheet function

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

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