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

月間スケジュールを作ろうPart2Excel
この記事は約7分で読めます。

Part2 1週目の作成

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

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)

ポイントは2つ

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

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

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

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

=WEEKDAY($A$1,1)

WEEKDAY関数の結果

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

WEEKDAY関数の戻り値から1引く

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

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

A1セルから②の結果を引いた日付

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

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

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

=A4+1

月曜日

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

Ctr+Rの前

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

Ctr+Rの後

ショートカット:Ctr+R

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

A5セルからG5セルに曜日を設定する(計算式+表示形式)

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

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

=A4

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

曜日の表示形式設定前

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

曜日の表示形式設定前

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

曜日の表示形式設定中

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

曜日の表示形式設定後

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

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

Ctr+R

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

1週目の装飾を設定する

罫線を引く

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

1週目のセルを選択

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

セルの書式設定で外枠と内側縦を選択
罫線で外枠と内側縦を設定

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

セルの書式設定で下線
罫線完了

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

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

日だけ選択

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

Ctrキーを押しながら飛び地のセル選択

③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を選択して、ホーム(リボン)の条件付き書式>新しいルールをクリックします。

条件付き書式設定前
条件付き書式設定前2

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

条件付き書式設定前3

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

=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関数の検索範囲となる祝日リストを絶対参照($付)とし、検索対象の値を相対参照としています。

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

⑤書式ボタンを押してセルの書式設定画面を表示します。

書式の設定前

⑥塗りつぶしタブでオレンジを選択してOKボタンを押します。

書式の設定中

⑦新しい書式ルールの画面に戻り、OKボタンを押します。

書式の設定中2

⑧4/29がオレンジ色に変わります。

条件付き書式の設定後

Part2のまとめ

ここでは以下の関数やテクニックを使っています。

  • WEEKDAY関数
  • VLOOKUP関数
  • ISERROR関数
  • NOT関数
  • 表示形式を使って曜日を表示する
  • 罫線を引く
  • 背景色を付ける
  • 文字の中央揃え
  • 条件付き書式

ここまでくればあとちょっとです。Part3で完結します。頑張りましょう。

コメント

  1. […] 月間スケジュールPart2 […]

  2. […] 月間スケジュールPart2 […]

タイトルとURLをコピーしました