Excel 月間スケジュールを作ろうPart2

Part2 1週目の作成
1週目の作成
Part2のまとめ

Part2 1週目の作成

Part1ではタイトル部分を作成してもらいました。おそらく以下のようになっていると思います。

Part1をもう一度見たい方はこちらです。

Part1では「1週目」を作成します。

1週目部分は「A1セルに設定された日付を含む週」とします。これを手入力ではなくA1セルの日付から自動計算して1週目の1日目（日曜）を算出します。

ちょっと難易度が上がりましたが、これをすることでA1セルに日付を入れるだけで自動でカレンダーが作成されるので使い勝手がよくなります。

ここでは以下を実施します。

A4セルにA1セルの日付がある週の1日目（日曜）を設定する（計算式）
B4セルからG4セルに日付を設定する（計算式）
A5セルからG5セルに曜日を設定する（計算式＋表示形式）
1週目の装飾を設定する
平日（月から金）が祝日の場合に背景色が自動で変わるように設定する

盛りだくさんですね。この月間スケジュールを作ろうの山場ですが一つずつやっていけば問題なくクリアできますので頑張っていきましょう。

1週目の作成

A4セルにA1セルの日付がある週の1日目（日曜）を設定する（計算式）

週の1日目（日曜）を計算式を使って求めます。

=A1-(WEEKDAY($A$1,1)-1)

ポイントは２つ

日付は整数値を減算することで〇〇日前の日付を求められます。
WEEKDAY関数で日付の曜日を１から７の整数値で求められます。

この２つを利用して計算します。A1セルの曜日を求めて日曜日が何日前かがわかれば1週目の1日目が計算できます。

こちらも一つずつ見ていきましょう。

①まず対象の日付（A1）は何曜日かをWEEKDAY関数で求めます。

=WEEKDAY($A$1,1)

WEEKDAY関数の第2引数「種類」は1（日曜～土曜を1から7の整数で返す）を指定します。そのため「6」は金曜日を指します。

②次に直近の日曜日が何日前かを求めます。

「2020/5/1」は金曜日なので「6」が返却され、日曜日が「1」なのでその差分が「6-1」で「5」となります。つまり「2020/5/1」の5日前を求めれば日曜が計算できます。

WEEKDAY関数の戻り値から「1」（日曜）を引くことで何日前を求めるかが決まります。

=WEEKDAY($A$1,1)-1

③A1セルの日付から②で求めた日数分遡る

=A1-(WEEKDAY($A$1,1)-1)

「②の計算式」をカッコで囲います。計算式の優先順で減算は左から順に行われてしまうため、カッコがないとA1から「7」引く形になってしまいます。カッコを忘れずに設定しましょう。

B4セルからG4セルに日付を設定する（計算式）

B4セルはA4セルの翌日となるように以下の計算式を入力します。

=A4+1

B4セルからG4セルを選択します。

選択した状態で「Ctr+R」を押します。

ショートカット：Ctr+R

複数セルを選択しているときは選択セルの左端のセルを右側のセルにコピーするショートカットです。１つのセルだけ選択しているときは対象セルの左側のセルの内容をコピーします。覚えておくと時短につながる便利なショートカットです。

A5セルからG5セルに曜日を設定する（計算式＋表示形式）

今回は日曜から土曜の固定のため固定文字でも問題ないですが、折角なので表示形式を使って日付から曜日を表示させましょう。

①A5セルに以下の計算式を入力します。

=A4

この状態ではA4と同じ内容が表示されます。

②ショートカット：Crt+1で「セルの書式設定」を呼び出し、表示形式タブを開きます。

③分類「ユーザ定義」を選択し、種類に「aaa」と入力します。

④OKボタンを押下すると「日」と表示されます。

※セルの書式設定で種類に「aaaa」と入力すると「〇曜日」と表示されます。詳しくは以下のページを参照ください。

⑤A5セルからG5セルを選択して、「Ctr+R」を押します。

これで1週目の日付と曜日が揃いました。次は装飾をしましょう。

1週目の装飾を設定する

罫線を引く

①A4からG9までを選択して罫線を引きます。

②Ctr+1でセルの書式設定を呼び出し、罫線タブを表示します。外枠と内側縦線を選択してOKボタンを押します。

③A5セルからG5セルを選択して「セルの書式設定」計算タブで下線を引きます。

4-5行目の日付欄に背景色を設定します。

①A4からA5を選択します。

②Ctrキーを押しながらG4からG5を選択します。

③Ctr+1で「セルの書式設定」を開き、塗りつぶしタブを表示します。色パネルよりオレンジ色を選択してOKを押します。

④B4からF5を選択してCtr+1で「セルの書式設定」塗りつぶしタブを開きます。

⑤青を選択しOKボタンを押します。

表示形式を変更します。

①A4からG4を選択し日付の表示形式を変更します

②Ctr+1で「セルの書式設定」を開き、表示形式タブを表示します。さらに分類：日付、種類：3/14を選択します。

文字を中央に揃える

①A4からG5を選択します。

②Ctr+1で「セルの書式設定」を開き、フォントタブを表示します。さらに横位置に「中央揃え」を選択してOKボタンを押します。

装飾は一旦ここまでとします。あとで2-6週目を作成後に列幅などの全体調整をします。

平日（月から金）が祝日の場合に背景色が自動で変わるように設定する

土日をオレンジに設定しましたが、平日が祝日となった場合にオレンジになるようにします。祝日がいつになるかはExcelはわからないので今回は祝日のリストを自分で用意してそのリスト内の日付と同じ日の背景色がオレンジになるようにします。

①I3からI13セルに祝日リストを作成します。

②B4からF4を選択して、ホーム（リボン）の条件付き書式＞新しいルールをクリックします。

③新しい書式ルール画面で「数式を使用して、書式設定するセルを決定」を選択します。

④「次の数式を満たす場合に値を書式設定」欄に以下の式を入力する

=NOT(ISERROR(VLOOKUP(B4,$I$4:$I$13,1,FALSE)))

初心者には少し難しいかと思いますので上記計算式をコピペして⑤に進んでください。

関数のネストを使用しています。使用する関数は以下の3つです。

関数	内容
NOT	引数がFALSEの場合はTRUE、TRUEの場合はFALSEを返す
ISERROR	引数がエラーの場合にTRUEを返す
VLOOKUP	指定された範囲の1列目で特定の値を検索し、指定した列と同じ行にある値を返します。

祝日リストの日付と同じ日付の場合に書式を変更したいというのがやりたいことです。

関数のそれぞれの動作は以下となります。

VLOOKUP関数で祝日リストに対象セルの値が存在する場合は日付を返し、存在しない場合はエラーとなります。
ISERROR関数ではVLOOKUP関数がエラーになっているかどうかを判定し、エラーの場合はTRUEを返し、エラーではない場合はFALSEを返します。
最後にNOT関数でTRUE/FALSEを反転して結果を返します。

結果としては祝日リストにB4セルの日付が存在する場合に書式が変更されることになります。

注意事項としては複数のセルを選択しているがここでは複数選択の起点となっているB4セルを対象とした式となっています。B4以外のセルは相対参照により変動することを意識して計算式を設定する必要があります。今回の例ではVLOOKUP関数の検索範囲となる祝日リストを絶対参照($付)とし、検索対象の値を相対参照としています。

相対参照と絶対参照については以下を参照してください。