教師の道具箱

生活と学習の記録の曜日や土日祝の色分けは、手作業でおこなっていたのですが、これを自動化してみたいと思います。色分けは、白黒印刷で使うので、ここでは土日祝の色の区別をしません。

最初にセルA1からA3まで、「年・月・日」と文字で入れます。
セルA2とB2には、年・月を数字で入れます。
セルC4から下向きに、1から連続した数字を31までオートフィルで入力します。

セルD4には、「=DATE($A$2,$B$2,C4)」という数式を入れます。日付が表示されますが、エクセル内部では、1900年1月1日からの通算日数の数字になっています。

セルE3に「週末」と文字で入れます。
セルE4に「=MAX(WEEKDAY(D4,2)-5,)」という数式を入れます。この数式により、月曜日から金曜日までは0、土曜日は1、日曜日は2となるのですが、あとで、0より大きければ色を付けるという処理をします。

セルE4の数式を詳しく説明すると、
「WEEKDAY(D4)」で、日付を曜日の通し番号を返します。しかし、日曜日が1、月曜日が2、土曜日が7という通し番号になり、土日がつながりません。そこで「WEEKDAY(D4,2)」とすることで、月曜日が1、火曜日が2、土曜日が6、日曜日が7と、土日を最後に持っていくことができます。

「WEEKDAY(D4,2)-5」と5を引き算することで、月曜日が-4、火曜日が-3、水曜日が-2、木曜日が-1、金曜日が0、土曜日が1、日曜日が2となります。

「=MAX(WEEKDAY(D4,2)-5,)」では、MAX関数を使うことで、最大値を求めます。ただし、最後のカッコの手前にカンマを入れることで、マイナス値は0の扱いになります。つまり、月曜日から金曜日までは0、土曜日は1、日曜日は2となります。

「=IF((WEEKDAY(D4,2)>5,1,0)」とすることもできますが、条件分岐はパフォーマンスが落ちるので、できるだけ使わないようにしています。

祝日のことは後まわしにして、曜日の自動表示と色分けをしたいと思います。

セルF3に「祝日」、セルG3に「長期」、セルH3に「計」と文字で入れます。
セルH4に「=SUM(E4:G4)」という数式を入れます。このシートでは、土日祝の区別はしないので、土日と祝日と夏季休業などの数字の合計が0か1以上かの区別をするだけです。

セルJ4に「=C4」という数式を入れます。書式設定をしてからオートフィルをしたいので、数式にします。
セルK4に「=DATE($A$2,$B$2,J4)」という数式を入れます。セルの書式設定で、ユーザー定義の「aaa」とすると曜日が表示されます。


セルJ4とセルK4を選択し、条件付き書式を実行します。
「数式を使用して…」を選び、「=$H4>0」と入力します。0より大きければ色が付きます。「=H4>0」ではなく「=$H4>0」とするのがポイントです。

あとは、セルJ4とセルK4をオートフィルを使って、下までコピーします。

祝日の判定をしていきます。

シート2に祝日を入れていきます。
セルA2には、「=Sheet1!A2」を入れます。

セルA4以下には、祝日名、セルB4以下には、月、セルC4以下には、日を入れます。
セルD4には、「=DATE($A$2,B4,C4)」を入れて、オートフィルでコピーします。

シート1に戻り、
セルF4に、「=COUNTIF(Sheet2!$D$4:$D$21,Sheet1!D4)」を入力します。

セルF4をオートフィルを使って、下までコピーします。

長期休業の色分けをしたいと思います。ここまで自動化しなくてもいいと思うのですが、一応[パソコン講座]ですので。

ちょっと気づいたのが、2020年度の3学期は2021年に変わることです。冬季休業は、年をまたぐので、1月は13、2月は14、3月は15と入力することで対処したいと思います。

そのため、シート1のセルJ1の数式を「=MOD(B2-1,12)+1」に入れ替えます。B2に入力した月から1を引いて、12で割った数字に1を足すことで、1月は1、2月は2、3月は3と表示させます。

前回の祝日も置き換えます。なお、画像の成人式や天皇誕生日の振替休日は間違っています。確認してから使ってください。

シート3に長期休業の一覧を作ります。開始日と終了日を入れると、長期休業中の日付一覧を自動作成します。この長期休業もこのとおりにはならないと予想されます。

セルB8に「=DATE($A$2,B$4,B$5)」
セルB9に「=DATE($A$2,B$6,B$7)」を入力します。

セルA10から下に0から始まる連続した数字をオートフィルで埋めておきます。
セルB10に「=IF(B$9-(B$8+$A10)>=0,B$8+$A10,0)」を入力します。日付が長期休業日の終了日よりも小さい場合は、その日付を表示させます。それ以外は「1900/1/0」と表示されますが、それが嫌なら条件付き書式で、文字を白にしてください。

シート1のセルG4に「=COUNTIF(Sheet3!$B$10:$E$47,Sheet1!D4)」と入力し、下にコピーします。

ついでなので、大の月、小の月の処理も入れたいと思います。

シート1のセルI4に「=IF(D4-EOMONTH(D$4,0)>0,1,0)」と入れます。
EOMONTH関数は、その月の最終日を返します。日付から、固定したセルD4の最終日を引き算して、プラスになれば、次の月になっていると判断させます。


あとは、条件付き書式で、日付の文字を白にします。画像では、背景と文字を灰色にしていますが、印刷時にエクセルが気を利かせて印字させてしまうので、文字を白にするのがいいでしょう。