ＥＸＣＥＬ基礎講座（８）
シフト管理表の作成

さて、一通りの関数やマクロを学んだ事と思います。ここで、それらを総合的に利用してシフト管理表を作成してみましょう。

作成するのは上のようなシフト管理表です。

①時間テーブルをドラックして「予定記録」ボタンを押下すれば、テーブルに「■」でグラフが表示されて、自動的に時間が集計されます。
②スタッフのセルはリストで登録が可能です。

このようなシートを関数とマクロとで作成してみましょう。

■シートのレイアウトの作成■
まずは、シート１（Sheet1）の名称を「prn」（半角小文字）に変更しましょう。
シート１の見出しをダブルクリックして選択した状態とします。
ここで、「prn」と半角小文字で入力して、シート名称を変更しておきます。


左のように、半角小文字でシート名称が設定されました。
マクロを利用しようと考えているので、シート名称は正確に記述する必要があります。下手に全角文字の英数字を利用してしまうと、マクロ中で全角や半角の判別がつかない場合がありますので、基本的に英数字は半角を利用する・・・という姿勢を推奨します。

次に、シートのセル全体を選択します。
セル全体の選択は列を示す「Ａ」と行を示す「１」の間のボタン（左図の部分）を押下します。


メニューから「書式」→「セル」→「配置」と選択し、縦横両方向で中央揃えとなるように設定しておきます。


つづいて、セル全体の文字（フォント）を「ＭＳ明朝」「９ポイント」に設定します。
（ちなみに、ここまでは作者のフォントの好みです(笑)ご自分の好きな文字を利用されて一向にかまいません）


「Ａ４」セルに「スタッフ」
「Ｂ４」セルに「開始」、「Ｃ４」セルに「終了
「Ｄ４」セルに「時間」と入力してください。


次に、Ｂ、Ｃ列をドラックして、列の全選択を行います。
左の図のようにＢ・Ｃ列の間にカーソールを移動させて、サイズ変更のカーソール形状になったらドラックして、Ｂ、Ｃ列を幅４．２５に設定します。


Ｄ列は幅４．００に設定しましょう。



■時刻テーブルの作成■
ここで、表に２４時間分を１５分刻みで表現する時刻テーブルを作成しようと思います。
見やすくする為に、時間表示と分表示のセルを別に表現しようと思います。行３には時間、行４には分・・・と言った具合にです。そして、最終的に、行２の時間表示は必要なくなりますので、表が完成したら、非表示にしてしまいます。

Ｅ２セルに「0:00」と時刻を入力します。
Ｆ２セルに次の関数を入力します。
=+E2+TIME(0,15,0)
つまり、「Ｅ２セルに１５分を足しなさい」という意味です。
この関数を入力したＦ２セルを右へ任意の幅（２４時間になる程度）ドラックします。
ＣＶ２のセルまでドラックする事になると思います。

さて、次に、Ｅ３セルに関数を入れて、時刻のみ表示させましょう。
関数は=Hour(E2)となります。セル表示は「０」となったと思います。

ひきつづき、Ｅ４セルに次の関数を入れて、分のみ表示させましょう。
関数は=MINUTE(E2)となります。
Ｅ４セルの関数をＥ４：Ｈ４の範囲でオートフィルをかけます。

このようにしてできた、１時間分のテーブルの時間表示を２４時間分複写してゆきます。
セル範囲Ｅ３：Ｈ４をドラックします。
コピーボタンを押下し、セル範囲をクリップボードに複写します（記憶させます）。
セルＩ３をクリックし、貼り付けボタンを押下します。
次は、セルＭ３をクリックし、貼りつけボタンを押下します。次々と複写し、ＣＶ２までの時刻テーブルをすべて満たして行きます。
さて、時刻テーブルの幅を調整しましょう。ＣＶからＥまでの列を全選択して、幅の調整を行います。
ここでは、仮に幅２．００となるように調整します。
これにより、Ｅ２：ＣＶ２のセル範囲の表示が「＃＃」となってしまいますが、気にしないでください。後に非表示としますので、問題ありません。


■表の修正■
だんだん表が完成に近づいてきました。
ここで、スタッフの項目の前に「Ｎｏ」を挿入したいと思います。
セルＡ４をクリックして選択します。
メニューから、「挿入」→「列」をクリックしますと、画面は右へシフトし、１列挿入されます。関数の参照セル式の内容も同時にシフトされるので問題はありません。
Ａ４に「No」と入力し、セル幅を２．００に設定しましょう。
次に、Ａ５：Ａ２４までを１からの連番で満たしておきましょう。（１～２０まで）

■画面の分割■
さて、結構、大きな表になっているので、画面枠固定を行い、左と上部の表題部が常に画面に表示されるように設定を変更しましょう。
セルＦ５をクリックして、メニューから「ウィンドウ」→「ウィンドウ枠の固定」をクリックすれば、表題部が固定されて画面が使いやすくなります。


■マクロの記録■
ドラックされた範囲を「■」の文字で埋めるマクロを記録しましょう。
まず、表の、Ｐ６：Ｗ６の範囲をドラックしてください。ここからマクロの記録を開始します。
メニューから「ツール」→「マクロ」→「新しいマクロの記録」とクリックすればマクロの記録が開始されます。
マクロの記録を開始してからドラックをしますと、ドラックする（範囲指定する）と行為自体が記録されてしまいます。それでは、別の範囲を■で埋める事ができなくなりますので、必ず、範囲を指定した後に（＝ドラックした後に）マクロの記録を開始します。

マクロの記録を開始しますと、マクロ名を尋ねてきます。左のダイアログＢＯＸ表示がそれです。
ここではマクロ名に「予定記録」と入力して「ＯＫ」を押しましょう。マクロの記録がスタートします。

そのまま「■」を入力します。
キーボードから「しかく」と入力し、［変換］キーで漢字変換しますと、「■」が表示されます。表示された漢字は［Ｅｎｔｅｒ］を押して確定させます。
そのまま［Ｃｔｒｌ］＋［Ｅｎｔｅｒ］を押します。（［Ｃｔｒｌ］キーを押しながら［Ｅｎｔｅｒ］キーを押す）
この操作で、選択された複数のセル範囲に対して、一度に「■」をすべて埋めることができます。
マクロの記録を終了させましょう。
ボタンに「記録終了」のボタンがあるはずですので、押下します。
これで、選択された範囲を「■」で満たす・・・というマクロが記録されました。



■マクロのテスト■
記録されたマクロが期待通りのものであるかどうかを確かめるためにテストを行ってみます。
今度はセル範囲Ｉ８：Ｐ８をドラックします。
メニューから「ツール」→「マクロ」→「マクロ」をクリックします。
マクロの選択ダイアログが表示されますので、そのまま「実行」ボタンを押下します。
この状態では、マクロは１つしか記録されていません。その為１つしか記録されていないマクロは選択状態となっていますから、「実行」ボタンを押下するだけです。複数のマクロが記録されている場合は、リスト内のマクロを選択してから、「実行」ボタンを押下します。
ちゃんと、次に指定した範囲も「■」で埋められた筈です。

■マクロボタンの作成■
さて、ここで、マクロ実行のボタンを作成しましょう。
マクロ実行ボタンにマクロの動作を設定すると、マクロボタンを押下するだけで、設定されたマクロが実行されるようになります。ここでは、シートのＡ１：Ｅ４までのセル範囲がウィンドウ枠の固定により常時表示されている形となりますので、そこにマクロボタンを作成します。
マクロボタンを作成するには、準備として「フォーム」というツールバーの表示を行う必要があります。
メニューから「表示」→「ツールバー」→「フォーム」をクリックします。


左のように「フォーム」という名のツールバーが表示されますので、そこから［ボタン］をクリックします。


そして、シートのＡ１：Ｅ４の範囲内の適当な所をドラックして、ボタンの大きさを決めます。


ドラックを終了させますと、左のように、「ボタン1」と書かれたボタンが表示されます。
かつ「マクロの登録」ダイアログＢＯＸが現れます。
『このボタンに登録するのはどのマクロですか？』と尋ねてきた訳です。


マクロの一覧の中から「予定記録」をクリックして選択し、［ＯＫ］ボタンを押下します。

これで、マクロのボタンへの登録は終了しました。でも、「ボタン1」という名では何を行うマクロであるかよく分かりません。
マクロボタンは選択された状態であると思いますので、そのまま「ボタン１」とかかれている文字をドラックし［ＤＥＬ］ボタンで削除してしまいます。（※マクロボタンが選択されていない状態であれば、マクロボタン上で右クリックを行います。右クリックメニューが表示されますが、無視して、マクロボタンの文字のところをクリックしますと文字編集モードとなります。）

文字の入力の前に［ＤＥＬ］キーを押した直後に、フォントサイズの設定を変更して「８」ポイントにしておきましょう。


「予定記録」と入力してください。
８ポイント、ＭＳＰゴシックの文字でボタンが作成されました。


どこか、他のセルをクリックしてから、マクロボタンの上にマウスカーソールを置きますと、カーソールの形状は指の形に変わります。
「押せる」マクロボタンが設定された事がこれで判断できます。

さて、試しに、セル範囲Ｍ７：Ｒ７をドラックしてから、このボタンを押下してみましょう。ちゃんと「■」で埋められた筈です。

■作成されたマクロの内容の確認■
マクロがどのような形で記録されたかを確認することができます。
ＥＸＣＥＬのシートを選択状態にしておいて、［Ａｌｔ]を押しながらファンクション［Ｆ１１］キーを押下します。
左の図のように「MicrosoftVisualBasic」とタイトルのあるウィンドウが開きます。


そのメニューから「表示」→「プロジェクトエクスプローラー」をクリックします。


プロジェクトウィンドウが開きます。
「モジュール」をダブルクリックし、下位フォルダを表示させます。


「Module1」をダブルクリックしますと、右の余地に、コードウィンドウが開きます。
コードウィンドウの最大化ボタンで最大化します。


左の図のように記録されたコードが表示されます。

記録されたマクロを確認してみましょう。
Option Explicit

Sub 予定記録()
'
' 予定記録 Macro
' ﾏｸﾛ記録日 : 2003/3/15 ﾕｰｻﾞｰ名 : ＸＸＸ
'

'
Selection.FormulaR1C1 = "■"
End Sub

上のようなマクロが記録されています。
「Option Explicit」これは、まじないのようなものです。（笑）今回は説明しません。
「Sub 予定記録()」はマクロのプログラムスタートの宣言の記述です。
「 '
' 予定記録 Macro
' ﾏｸﾛ記録日 : 2003/3/15 ﾕｰｻﾞｰ名 : ＸＸＸ
'

'」までは、ＥＸＣＥＬが自動的に付加した作成ユーザーの情報などです。
「Selection.FormulaR1C1 = "■"」この、僅か１行が今回のマクロの記録であるプログラムの本体の記述です。
「End Sub」これは、マクロプログラムの終了の宣言の記述です。

マクロの記録後は、このように常に内容を確認するようにしましょう。なんとなく、だんだん、マクロの中身が分かってくるはずです。
今回のマクロ記録でも、記録した動作と照合すれば、なんとなく分かる筈です。
記録した動作は
「選択範囲に■をすべて入力する」です。
「Selection」おそらくこの記述が選択範囲を表している・・・とは思いませんか？
「.FormulaR1C1 = "■"」そこにすべて■を入力する動作は、これで記述されている・・・とは思いませんか？

■削除のボタンの作成■
さて、練習です。ここで、予定記録を削除するボタンである「予定消去」のマクロボタンも作成しておきましょう。
今度は、簡単だと思いますので、手順のみ記載します。
１）範囲をドラックしておく。
２）メニューから「ツール」→「マクロ」→「新しいマクロの記録」を選択する。
３）マクロ名を「予定削除」と入力し［ＯＫ］ボタンを押下。
４）［ＤＥＬ］キーを押下。（範囲の「■」が削除されたのを確認します）
５）マクロ記録停止のボタンを押下。
６）フォームから「ボタン」を選択し、先ほどと同様にして「予定削除」マクロの登録を行う。
７）ボタンのタイトル等を変更する。

Sub 予定削除()
'
' 予定削除 Macro
' ﾏｸﾛ記録日 : 2003/3/15 ﾕｰｻﾞｰ名 :
'

'
Selection.ClearContents
End Sub
このようにして、上記のマクロが作成されます。

このように、マクロは一般的に、自動記録を使って利用します。マクロの自動記録を行う場合は次のように配慮します。
１）あらかじめ、マクロで記録する操作を練習しておきましょう。（ミスなく記録する為です）
２）その後にマクロの記録をスタートさせます。
　　　・セルのクリックやシートあるいはウィンドウの移動も当然記録されるので、余計なクリックは行わないようにしましょう。
　　　・操作手順によっては複数の記録が発生する場合があります。たとえば、セルの書式設定等で数回に渡って書式設定のダイアログを呼び出せば、セットで記録されてしまいます。（文字の中央揃えで１度ダイアログを呼出、再度フォントサイズ設定でダイアログを呼び出したりすると、１つの設定が数回記録されます。ダイアログの呼出設定などは１度で全てを設定するように配慮しましょう。）
３）必ず、ＶＢＡのプロジェクトを表示させて、コードを確認しておきましょう。（マクロの上達につながります）


■時間の計算■
では、ここで、シートのＥ５：Ｅ２４に適用させる時間を関数で計算してみましょう。
■１個が１５分を示していますでの、
１）■の個数を数える。
２）１５分を乗算して、時間の合計を「分」の単位で計算する。
３）分の単位から「時間」と「分」を計算する
このような形で計算できそうです。

実際に■のある、Ｅ６セルを選択します。
■を数えましょう。
関数を検索してみますと「Countif」という関数が利用できそうです。
『指定した＜範囲＞に含まれる空白以外のセルのうち、＜検索条件＞に一致するセルの個数を返す。』
と説明があります。
記述はこのようにあります『Countif(＜範囲＞,＜検索条件＞)』
これに合わせて数式を入力してみましょう。
まずセルＥ６をクリックします。
数式バーに直接「=countif(」と入力します。
次に範囲指定をドラックして行います。この場合は時間テーブルのＦ６：ＣＷ６の範囲です。（大きな表のドラックは、右端から左へと逆にドラックする方が簡単です。逆にドラックしてもちゃんと小大順で数式バーには表示されます。）
「,」半角のカンマで区切ります。「"」半角のダブルクォーテーションを入力します。「■」を入力します。もう一度、「"」半角のダブルクォーテーションでくくり、「)」閉じカッコでくくります。［Ｅｎｔｅｒ］キーで確定します。
Ｅ６セルに、「■」が入ったセルの個数が表示された筈です。（また、入力された式は、半角小文字で入力しても自動的に半角大文字に変換されている筈です。）
■１個は１５分を示していますので、この数式に１５を乗算すれば、合計時間の分数が求められます。
さて、再度、数式バーの数式の末尾をクリックします。
「＊１５」と１５の乗算のための式を入力し［Ｅｎｔｅｒ］で確定します。

たとえば、■の数が８個ならば「１２０」という具合に分数が表示された筈です。

次に、これを時間の表示にします。ここでは、ｉｎｔ関数とｍｏｄ関数を利用してみましょう。
『ＩＮＴ関数：数値を指定した数値よりも０に近い整数に丸めた値を返します。』とあります。つまり切り捨てを行ってくれるという事です。
１時間は６０分ですから、計算された分数（ふんすう）を６０で割って、これを切り捨てした値とすれば時間が求められます。
つまり時間を導く計算式は「int(＜合計した分＞/60)」です。＜合計した分＞は先ほどの「=countif(f6:cw6,"■")*15」ですから、時間を求める数式は「INT(COUNTIF(F6:CW6,"■")*15/60)」です。

次に分数（ふんすう）を求めてみましょう。
『ＭＯＤ関数：数値を除算した剰余を返します。』とあります。つまり、ある数値Ａを数値Ｂで割った余りの値を返す・・・という意味です。
したがって、６０分で計算された分数（ふんすう）を割った余りは、端数の分数（ふんすう）に違いないはずです。
時間を処理した後の、余りの分数（ふんすう）を計算するには「mod(＜合計した分＞,60)」で求められます。
つまり「MOD(COUNTIF(F6:CW6,"■")*15,60)」が残りの分数です。

これらを時刻を表現しているように、文字の接続を行って表現します。
　　※文字の接続は「&」で行います。
「INT(COUNTIF(F7:CW7,"■")*15/60) & ":" & MOD(COUNTIF(F7:CW7,"■")*15,60)」これで「ＸＸ：ＸＸ」といった具合で時間と分にセパレーターとして「：」を用いた表現となります。
左の数式が文字として表現された、合計の時間表現です。

ただ、このままではＥＸＣＥＬは、時間としてその値を判断できません。そこで、さらに、時刻表現された文字列を日付シリアル値に変換する関数を利用します。この処理を行いますと、ＥＸＣＥＬはその値を日付や時間として内部的に取り扱ってくれます。
左の図の関数です。「TIMEVALUE」がそれにあたります。


そこで、完成した関数を「TIMEVALUE()」でくくってしまいます。
「TIMEVALUE(INT(COUNTIF(F7:CW7,"■")*15/60) & ":" &M OD(COUNTIF(F7:CW7,"■")*15,60))」が最終の数式となります。
ところが、表示は「０．０８３３」とか数値になってしまいます。
ご心配なく、これが正しいのです。日付・時刻にシリアル値を表示しているのです。１日を「１」としています。「０．０８３３」はたぶん２時間程度の値です。

そこで、上の図のように表示形式を設定して、時刻表示にします。
ここでは、ユーザー定義を選択し、＜種類＞に手入力で「H:MM」（半角小文字）で登録しました。
書式で「H」は時間、「MM」は０を補った２桁での分数（ふんすう）の表示書式です。


ちゃんと、「２：００」といった具合で表示が直ったはずです。
これをＥ５：Ｅ２４の範囲に複写します。

このようにして、Ｅ列に時間の合計の計算ができるようになりました。


■開始時刻を得る関数■
今度は開始時刻を関数で導きましょう。
セルＣ５にその関数を登録したいと思います。
数式はちょっと難しいのですが、配列式で「=MIN(IF($F5:$CW5="■",COLUMN($F5:$CW5),"")) 」のようになります。
これは、Ｆ５：ＣＷ５のセル範囲の中で、もしも「■」があれば、セルの列位置（COLUMN）の値を返しなさい。
かつその内、最小の値（MIN）を返しなさい。という関数です。
セル範囲を配列として扱う必要があるので、数式バーに入力した後に［Ｃｔｒｌ］＋［Ｓｈｉｆｔ］を押したまま［Ｅｎｔｅｒ］を押して確定する必要があります。
実際に入力してみましょう。
左図のように、数式バーに直接入力してください。


入力が終了したら、前述のように、［Ｃｔｒｌ］＋［Ｓｈｉｆｔ］を押したまま［Ｅｎｔｅｒ］を押下します。
数式バーの数式が「｛｝」で囲まれて、配列式となります。

Ｃ５セルには「１５」と値が表示されました。これは、指定範囲のなかで先頭の「■」は１５列目にあるという事を意味しています。これで開始時刻が計算できます。
０：００が「６」個目のセルです。「１５－６」の９個のセルが最初の「■」までにあるはずです。つまり、時刻テーブルの最初の時間＋９＊１５分が開始時刻になるはずです。
ところが、配列式にはそれ以上、数式を追加する事ができません。
そこで、となりのＤ列全体を選択し、メニューから「挿入」→「セル」をクリックし、Ｃ列とＤ列の間に１列増やしてしまいます。
　　　・１行増やした為にセルＣ５の関数で指定した範囲＄Ｆ＄５：＄ＣＷ＄５は自動的に１アドレスずれて＄Ｇ＄５：＄ＣＸ＄５に変化します。
あらたに追加されたＤ５セルに数式として「=TIME(0,+(C5-7)*15,0)」を入力します。
これは、最初に使った数式で、
１）Ｃ５セルで数えた最初の列位置－０：００のセル位置を計算します。
２）その値に１５分を乗算した分数（ふんすう）を時間として表現するものです。
そして、これに、０：００の値を絶対番地として登録します。
数式は「=$G$2+TIME(0,+(C5-7)*15,0)」このようになります。
　　　・Ｇ２の値を加算しているのは、時間テーブルの最初の値が午後１２：００と変更されても正常に処理可能とする為です。
　　　・７を減算するのは、なぜでしょう。それはＣ５セルで数えたセルの位置は行見出しであるＡ４～Ｆ４までの６つのセルを加えた値だからです。たとえば０：００分から■マークがあるとします。このセル位置は、Ｇ５ですから、Ｃ５には７が表示されている筈です。そこで、０：００であると計算させる為、見出しに値する６に、植木算と等しく始まりを規定するための１を加えた７を減算します。

Ｃ４の表題をＤ４に移動しましょう。


■終了時刻を得る■
終了時刻は、ＭＩＮの関数に対応したＭＡＸの関数で得られます。
ここでは、関数を複写しながら入力します。
まず、列Ｅ全体選択した状態で、メニューから「挿入」→「セル」で１列追加します。
　　　・ここでも、Ｃ５の数式を確認すると、１列増やした為に範囲はＨ５：ＣＹ５となって、１列シフトしているのが分かる筈です。
追加したセルＥ５にセルＣ５の数式を複写し、修正をかけて利用します。
セルＣ５を選択してください。
数式バーにはセルＣ５の数式が表示されます。

その数式バーに表示されている数式をドラックします。
ここで、［Ｃｔｒｌ］＋［Ｃ］を押下し、クリップボードに数式を複写します。

セルＥ５を選択します。
数式バーをクリックします。
今度は［ＣＴＲＬ］＋［Ｖ］で先ほど複写した数式を貼り付けます。
この操作で、セルＥ５とセルＣ５の数式は同じになる筈です。

そこで、Ｅ５セルの数式を最大値を求めるＭＡＸに変更します。
「=MAX(IF(H5:CY5="■",COLUMN(H5:CY5),""))」数式はこのようになります。
そして、これもまた、配列式とする為に、［Ｃｔｒｌ］＋［Ｓｈｉｆｔ］を押したまま［Ｅｎｔｅｒ］を押して確定する必要があります。
ここで、最終セルの（最大値）表示がおかしい場合があります。時刻形式で表示されているからです。その場合は表示形式を標準に戻しましょう。

次に終了時間の算出です。
これは複写で済む筈です。Ｄ５セルの数式をＦ５セルに複写します。
ただしく、終了時間が計算されたはずです。
数式は「=$H$2+TIME(0,+(E5-8)*15,0)」になります。
これを必要個数、下に複写します。
　　　　・今度はＥ５－８というように、８が減算されています。１列を行見出しとして増やした為、減算する値も７から８へと増加させる必要があります。
　　　　・またＤ列の数式も、先ほどまでは７を減算していました。この７はセル番地を表している訳ではないので、１列増やしたからといって自動的に増えて８になったりしません。Ｄ５の数式も訂正して、『=TIME(0,+(C5-7)*15,0)』とする必要があります。

ところが、下に複写しますと、数式がエラーとなるケースがあります。
未入力のケースで「■」が０個となるケースへの対応が必要です。
簡単ですねＩＦ文で処理してしまいます。
よって、最終的には
Ｄ５セルの数式：「=IF(C5=0,"",$H$2+TIME(0,+(C5-8)*15,0))」
Ｆ５セルの数式：「=IF(E5=0,"",$H$2+TIME(0,+(E5-8)*15,0))」
となります。
この数式を複写し直してください。

最後に、計算の途中で利用した、列Ｃ、Ｅを非表示にしてしまいましょう。
つづいて、行２も非表示にします。
表はほぼ完成です。


■入力補助■
さて、入力補助として、スタッフ名をリストから入力可能なようにしましょう。

１）リストの作成
セルＡ３０からＡ４９に、１からの一連番号を入力してください。
セルＢ２９に「スタッフ名」と登録します。
セルＣ３９には「ＴＥＬ」と登録します。
Ｂ３０：Ｂ４９の範囲に適当な人名を登録してください。
ここで、Ｂセルにこの、Ｂ３０：Ｂ４９のスタッフのリストから入力が可能なように設定します。

２）ウィンドウ枠固定の解除【ＥＸＣＥＬ２０００以降は必要ありません】
さて、リストからの登録を行えるようにする場合は、ウィンドウ枠を固定したままでは行えません。（ＥＸＣＥＬ９７では必要ですが、ＥＸＣＥＬ２０００以降はウィンドウ枠の固定を行っていても、リスト入力が可能になりました）そこで、まず、ウィンドウ枠の固定を解除します。
メニューから「ウィンドウ」→「ウィンドウ枠固定の解除」を選択します。
これで、ウィンドウの枠の固定は解除されます。

３）入力規則の設定
Ｂ５セルを選択します。
メニューから「データ」→「入力規則」を選択しますと、「データの入力規則」ダイアログが表示されます。
「入力値の種類」のリストＢＯＸをクリックして、「リスト」を選択します。
「元の値」をクリックしてから、Ｂ３０からＢ４９をドラックして、範囲を選択し、「ＯＫ」ボタンを押下します。

４）入力規則の複写
「元の値」の範囲をドラックした時に、範囲のアドレス「＝＄Ｂ＄３０：＄Ｂ＄４９」には「＄」マークが付加されています。ご存知の通り、絶対番地となっていますので、そのまま必要な範囲、Ｂ５：Ｂ２４へ複写します。
Ｂ５セルを選択します。
「コピー」ボタンを押します。
範囲Ｂ６：Ｂ２４を選択します。（ドラックします）
「貼り付け」ボタンを押します。

この操作で、左の図のように、範囲に入力規則が適応された筈です。
簡単にスタッフの登録を行う事が可能になりました。


■スタッフ登録の時のみウィンドウ枠の解除が自動的に行われるようにする■
【ＥＸＣＥＬ２０００以降は必要ありません】
実は、マクロには便利な機能があります。特定のセルが選択された場合に特定の動作を自動的に行う・・・という機能のマクロが記述できます。
そのマクロを利用すれば、『Ｂ５：Ｂ２４のいづれかのセルが選択された場合、ウィンドウ枠を解除し、他のセルが選択された場合はＨ５の位置でウィンドウ枠を固定しておく・・・』というような記述ができそうです。

１）ワークシートのセル選択発見マクロの準備
このマクロは、自動記録ができません。その為、手入力で記述して準備します。
①まず、例によって［Ａｌｔ］＋［Ｆ１１］のキーを押して、マクロのウィンドウを表示させましょう。
②次に、プロジェクト・ウィンドウの中の「Ｓｈｅｅｔ１（ｐｒｎ）」をダブルクリックしましょう。


ダブルクリックしますと、左の図のように白紙のあたらしいコード表示部が右側に表示されます。


③左のように「（Ｇｅｎｅｒａｌ）」と表示されているリストＢＯＸをクリックして、「Ｗｏｒｋｓｈｅｅｔ」を選択します。


そうすると、コード部に「Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)」の記述と「End Sub」の記述が表示されます。
これがワークシートの変化を発見するマクロの入り口（？）にあたるものです。

そこで、上手のようにコードを記述します。普通のメモ帳やワープロソフトで入力する手順と同じように、上図でドラックされている範囲を入力します。（コードは「"」で囲まれているメッセージ部を除いて半角で入力してください）（以下の青文字の部分です）
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row < 5 Or Target.Row > 24 Then
Exit Sub
End If
If Target.Column <> 2 Then
Exit Sub
End If
MsgBox "リスト入力セルが選択されました"
End Sub

これは、ワークシートに何らかの変化があった場合に動作するマクロとなります。
If Target.Row < 5 Or Target.Row > 24 Then
Exit Sub
End If
もしも、選択されたセルの行が５行目未満または２５行目以上ならば何もするな（このマクロを抜けなさい）・・・という記述です。
If Target.Column <> 2 Then
Exit Sub
End If
もしも、選択されたセルの列が２列目以外ならば何もするな（このマクロを抜けなさい）・・・という記述です。
その為、該当範囲であれば
MsgBox "リスト入力セルが選択されました"
で、うまく動作するかどうかの確認のためのメッセージＢＯＸが表示されます。

２）マクロのテスト
マクロのテストを行ってみましょう。
シートに戻ってＡ５セルをクリックします。・・・何も起こりません。
次にＢ５セルをクリックします。・・・以下のメッセージＢＯＸがシート中央位置に表示される筈です。
これで、このリスト入力範囲の選択発見マクロはうまく動作している事が分かります。


３）ウィンドウ枠固定のマクロの自動記録
さて、このマクロを改造して、入力範囲ならばＨ５セルでウィンドウ枠を固定し、それ以外ならば、ウィンドウ枠の固定の解除を行うように変更すれば良い訳です。
ウィンドウ枠の固定や解除は通常のマクロの自動記録で、記録されるはずです。ここで、自動記録を利用してその記述を抽出してみます。
１）セルＡ１を選択しておきましょう。
２）メニューから「ツール」「マクロ」「新しいマクロの記録」を選択します。
３）「マクロの記録」ダイアログが表示されますので、名称に「test」と入力しましょう。
＜＜ここから先がマクロで記録されるはずです＞＞
４）セルＨ５を選択します。
５）メニューから「ウィンドウ」→「ウィンドウ枠の固定」を選択します。
６）セルＡ１を選択します。
７）メニューから「ウィンドウ」→「ウィンドウ枠固定の解除」を選択します。
＜＜ここでマクロ記録の終了＞＞
８）マクロの記録を終了します。（「■」ボタンを押下します）
さて、記録されたマクロをマクロウィンドウの「Module1」をダブルクリックして、表示させて見ますと以下のようになっています。

Sub test()
' test Macro
' マクロ記録日 : 2003/3/19
'
'
Range("H5").Select
ActiveWindow.FreezePanes = True
Range("A1").Select
ActiveWindow.FreezePanes = False
End Sub
どうもウィンドウ枠の固定は、セル指定「Range("H5").Select」の後の、「ActiveWindow.FreezePanes = True」で設定されるようです。さらに「ActiveWindow.FreezePanes = False」で解除されていうようだと類推できます。
これで、先ほどのマクロの改造ができそうです。

４）ウィンドウ枠固定を解除するマクロの作成
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row < 5 Or Target.Row > 24 Then
Exit Sub
End If
If Target.Column <> 2 Then
Exit Sub
End If
MsgBox "リスト入力セルが選択されました"
End Sub
青文字の部分で、ウィンドウ枠の解除を行えば良いので、
「ActiveWindow.FreezePanes = False」に置き換えます。
そこで、変更したのが以下のようなマクロです。
①Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
②If Target.Row < 5 Or Target.Row > 24 Then
③Exit Sub
④End If
⑤If Target.Column <> 2 Then
⑥Exit Sub
⑦End If
⑧ActiveWindow.FreezePanes = False
⑨End Sub
⑧範囲内であるのでウィンドウ枠の固定を解除しなさい。
と・・・上のような意味に変更します。

５）ウィンドウ枠固定マクロ・ボタンの作成
ひきつづき、ウィンドウ枠固定を行うマクロを作成しましょう。
先ほど自動記録されたマクロを表示します。
マクロウィンドウのモジュール１をダブルクリックしてください。コードが再度表示されます。
左のようにマクロのタイトルである「test」をドラックして、「枠固定」と変更しましょう。

引き続き、先ほどのマクロでは、枠固定の解除を行っていた部分である左の図の記述部をドラックしてから［ＤＥＬ］ボタンを押して削除してしまいます。
これで、枠を固定するだけのマクロが完成しました。



さて、初めに行ったように、マクロ・ボタンを「枠固定」で作成し、マクロの登録を行ってください。


■表の完成■
さて、後は、タイトルと、日付等を適当に挿入します。
ここでは、タイトルとして、Ｂ１セルに「ライン担当スタッフ一覧」を１６ポイントで設定しました。
さらにセルＫ１に、当日の日付を表示させたいのですが、セル幅が小さいので「ｎｏｗ関数」で得た現在の日付時刻シリアル値を「ＴＥＸＴ関数」で文字列変換して表示させる事にしました。
セルＫ１の式は「=TEXT(NOW(),"ggge年m月d日 (aaa)")」のようになります。

このようにして完成したのが、はじめに紹介しました図の予定表です。
自由に罫線や文字修飾を行って、自分らしい表に改造してください♪

■改造のポイント■
２４時間のタイムテーブルを用意しましたが、１２時間しか必要ない・・・という場合には以下のように改造します。
　①行「１：３」をドラックして、メニューから「書式」→「行」→「再表示」で隠されている２行目を表示させます。
　②セルＨ２に先頭の時間を登録します。（午前６時からのテーブルでしたら、「６：００」と入力します。
　③６：００からの２４時間テーブルに変更されたと思います。必要の無い範囲の列をドラックして、メニューから「編集」→「削除」で削除してしまいましょう。
　④行２をクリックして２行目全体を選択します。
　⑤メニューから「書式」→「行」→「表示しない」を実行し２行目を表示させないようにします。