エクセルで業務チェックリストを作る 令和対応、曜日指定、最終日など

スポンサーリンク
コンピュータ

エクセルで作成された業務チェックリストを良く見かけますが、元号も変わった事だし見直しも兼ねて出来るだけ自動化しようと思った流れで、日付に関する覚書として記事にしてみました。

今回は、一か月毎のチェックリストを作成する上での書式設定をまとめてます。

日付の書式設定

一般的な日付の書式設定

セルに「2019/5/1」が入力されているとします。Windows10では、KB4501835で元号対応されている様です。令和表示されない場合、WindowsUpdateを行って下さい。

2019 年 5 月 2 日 — KB4501835 (OS ビルド 17763.439)
機能強化と修正プログラム、既知の問題、更新プログラムの入手方法など、更新プログラム KB4501835 について詳しく説明します。

令和元年で表示する

元年表示させる方法として2つ紹介します。
① 書式設定で元年以外の範囲を指定する。
② 書式設定で和暦表示設定を行い、条件付き書式設定で1年を元年にする。

表記中の「J3」は対象セルです。

①ではシリアル値で指定しているので令和元年には対応しますが、平成元年やその他の元年には対応していません。対応させる為には範囲指定をダラダラと追加するしかありません。②は1年を元年にしているので、すべての元年に対応しますが、2か所で設定する必要があります。

日付を連動させる

基準となる年月から日付、曜日を連動させます。

①1つのセルに日付を入力し、セルB3に「=AD3」で転記してます。但し、AD2のセルには月初日を入力する必要があります。月初日以外でも良い「=DATE(YEAR(AD2),MONTH(AD2),1)」の方が良いかも知れません。

②基準日をフルで入力するのが面倒なので、年と月を分離させ、年は和暦で入力するようにしました。元号は「=DATE(AA2+2018,AD2,1)」で年(+2018加算)、月、日(月初日=1)で、元号のみ表示してます。元号に月初日が入力されているので、セルB3「=Y2」で転記してます。

③31日無い月では翌月が表示されるので条件付き書式設定で空白にします。

 

 

特定日付の書式設定

下記チェックリストについて書式設定を行います。

日曜日のセルに塗りつぶしを行う

設定するセルを選択(D3:AH22)し、条件付き書式を行います。

条件付き書式 : =WEEKDAY(D$4)=1

ここで重要なのは、「=WEEKDAY(D$4)=1」です。$が無いと日付のみ塗りつぶしが行われ、$D$4だとどこも塗りつぶされなかったです。

毎週金曜日のセルに塗りつぶしを行う

設定するセルを選択(D18:AH18)し、条件付き書式を行います。

条件付き書式 : =WEEKDAY(D4)=6

最終日曜日のセルに塗りつぶしを行う

設定するセルを選択(D19:AH19)し、条件付き書式を行います。

条件付き書式 : =EOMONTH($AA$2,0)-MOD(WEEKDAY(EOMONTH($AA$2,0))+6,7)=D4

ここで重要なのは、「日曜日のセルに塗りつぶしを行う」と条件が重なります。条件付き書式の順番として今回の条件が上段になる様にします。

第1水曜日のセルに塗りつぶしを行う

第1水曜日は、1~7日になるのでセルを選択(D20:J20)し、条件付き書式を行います。

条件付き書式 : =WEEKDAY(D4)=4

第1週(1~7)、第2週(8~14)、第3週(15~21)、第4週(22~28)、第5週(29~)になるので、それぞれの範囲に対して条件付き書式を設定します。

月末日のセルに塗りつぶしを行う

月末日は、28~31日になるのでセルを選択(AE22:AH22)し、条件付き書式を行います。

条件付き書式 : =EOMONTH($AA$2,0)=AE3

翌月部分の表示と塗りつぶしを無効にする

日付枠として31日分あるので月によっては翌月の日付や項目の塗りつぶしが行われます。翌月部分の表示を無くし、塗りつぶしも無効(色なし)にします。

翌月が表示されるのは、29~31日の枠になるのでセルを選択(AF3:AH22)し、条件付き書式を行います。

条件付き書式 : =NOT(MONTH($AA$2)=MONTH(AF$3))

シートの保護を行う

個人で使うのであれば良いのですが、共有する場合、せっかく設定した関数や書式が、コピーや貼り付け等で崩れる可能性があります。この様な状況を回避する為にシートの保護を行います。

シートの左上をクリックし、シート全体を選択し、書式設定「保護」タブのロックと表示しないにチェックを入れます。

年月は入力したいので、年(AC2:AD2)、月(AF2:AG2)を選択し、書式設定「保護」タブのロックのチェックを外します。

校閲タブにあるシートの保護をクリックし、シートの保護を行います。簡易的なガードなのでパスワードは空白のままで良いと思います。

コメント

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