差分

このページの2つのバージョン間の差分を表示します。

--- excel:day_by_color [2022/10/02] – administrator
+++ excel:day_by_color [不明な日付] (現在) – 外部編集 (不明な日付) 127.0.0.1
@@ 行 1: / 行 1: @@
+===== 曜日の自動表示と色分け =====
+==== 曜日や土日祝の色分けを自動化 ====
+生活と学習の記録の曜日や土日祝の色分けは、手作業でおこなっていたのですが、これを自動化してみたいと思います。色分けは、白黒印刷で使うので、ここでは土日祝の色の区別をしません。\\
+{{:excel:day_by_color01.png?nolink}}
+最初にセルA1からA3まで、「年・月・日」と文字で入れます。\\
+セルA2とB2には、年・月を数字で入れます。\\
+セルC4から下向きに、1から連続した数字を31までオートフィルで入力します。\\
+{{:excel:day_by_color02.png?nolink}}\\
+セル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以上かの区別をするだけです。\\
+{{:excel:day_by_color03.png?nolink}}\\
+セルJ4に「=C4」という数式を入れます。書式設定をしてからオートフィルをしたいので、数式にします。\\
+セルK4に「=DATE($A$2,$B$2,J4)」という数式を入れます。セルの書式設定で、ユーザー定義の「aaa」とすると曜日が表示されます。\\
+{{:excel:day_by_color04.png?nolink}}\\
+{{:excel:day_by_color05.png?nolink}}\\
+セルJ4とセルK4を選択し、条件付き書式を実行します。\\
+「数式を使用して...」を選び、「=$H4>0」と入力します。0より大きければ色が付きます。「=H4>0」ではなく「=$H4>0」とするのがポイントです。\\
+{{:excel:day_by_color06.png?nolink}}\\
+あとは、セルJ4とセルK4をオートフィルを使って、下までコピーします。
+==== 祝日の判定 ====
+祝日の判定をしていきます。
+シート2に祝日を入れていきます。\\
+セルA2には、「=Sheet1!A2」を入れます。\\
+{{:excel:day_by_color07.png?nolink}}\\
+セルA4以下には、祝日名、セルB4以下には、月、セルC4以下には、日を入れます。\\
+セルD4には、「=DATE($A$2,B4,C4)」を入れて、オートフィルでコピーします。
+シート1に戻り、\\
+セルF4に、「=COUNTIF(Sheet2!$D$4:$D$21,Sheet1!D4)」を入力します。\\
+{{:excel:day_by_color08.png?nolink}}\\
+セル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と表示させます。\\
+{{:excel:day_by_color09.png?nolink}}\\
+前回の祝日も置き換えます。なお、画像の成人式や天皇誕生日の振替休日は間違っています。確認してから使ってください。\\
+{{:excel:day_by_color10.png?nolink}}\\
+シート3に長期休業の一覧を作ります。開始日と終了日を入れると、長期休業中の日付一覧を自動作成します。この長期休業もこのとおりにはならないと予想されます。\\
+{{:excel:day_by_color11.png?nolink}}\\
+セル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の最終日を引き算して、プラスになれば、次の月になっていると判断させます。\\
+{{:excel:day_by_color12.png?nolink}}\\
+{{:excel:day_by_color13.png?nolink}}\\
+あとは、条件付き書式で、日付の文字を白にします。画像では、背景と文字を灰色にしていますが、印刷時にエクセルが気を利かせて印字させてしまうので、文字を白にするのがいいでしょう。\\
+{{:excel:day_by_color14.png?nolink}}\\