どの会社でも毎月やっている【帳票の日付更新】
めんどくさいと思いながら毎月やっていませんか？
(日付や曜日の打ち変えや休みの日の色変更など．．．）
企業次第では毎月数ファイル打ち換えているなんてざらじゃないでしょうか？
こんな無駄な作業に数十分時間を取られたくないですよね。
今回はそんな【帳票の日付更新】を自動化する方法を以下3つの順序で書きます。
①日付更新の自動化 ←日付だけでいい人はこれだけ
②曜日更新の自動化
③休日の色変更の自動化
完成イメージ
今から作るものとしては
年/月を打ち換えると
(①/②)自動で帳票の日付が更新されて、
(③)土日休みと祝日などの不定休も設定一つで自動色替えできるという機能です。
一つずつやっていきましょう！
①日付更新の自動化 ←日付だけでいい人はこれだけ
まず使用する機能が
・DATE関数 ←月初め計算用
・EOMONTH関数 ←月終わり計算用
※少し雑な表示でいい人は上2つでOKです
・IF関数 ←次月分を表示させない為
・IFFERROR関数 ←エラー表示出さない為
以上4つです！
・・・多いな。と感じたかもしれないですが
どれもシンプルな関数ですので一つずつやっていきましょう！
まずは打ち換えできるようにする為に使用している帳票シートの
文字(年/月)と数字(2022/3)の欄を分ける
皆さんが使用している帳票のシートとは別に
「日付」シートを作りましょう
日付シートに使用している帳票の日付(あるなら曜日も)をB5にコピー
次は日付シートの日付(あるなら曜日も)を帳票シートの個所にリンク貼り付け
リンク貼り付け説明
リンク貼り付けはコピーしたセルの値と貼り付け先の値を常に同じにする機能。
(CTRL+C➡右クリックの貼り付けのオプションから「リンク貼り付け」
日付シートに月初め/月終わり欄を作成(C列は空欄でOK)
日付シートのC2にDATE関数を使用し月初めを表示する
DATE関数説明
=DATE(年,月,日)
=DATE(2022,3,1)
・参照するのは帳票シートの年のセルと月のセルなので
コードはこのようになる(日の個所は月初めなので１)
=DATE(帳票!B3,帳票!D3,1)
これで月初めの自動表示はOKです。
次は月終わり(C3)の自動表示です
日付シートのC3にEOMONTH関数を使用し月初めを表示する
EOMONTH関数説明
=EOMONTH(開始日,何か月後の月末か)
・開始日は月初め(C2)で0カ月後の月末なので
=EOMONTH(C2,0)
これで月終わりの自動表示も完了です。
つぎはようやく日付の自動表示です!
長かった．．．
まず日付の初日(B6)は月初めの日(C2)を参照します
=C2
2日目(B7)以降は1つ上のセルに+1していけばいいので
=B6+1
B7より下にオートフィルでコピーしてください。
すると．．．
次の月が表示されてしまいます(↑写真赤枠)
これで問題ない方は次へ飛んで大丈夫です！
次の月を表示させたくない方は
IF関数を使用して対策します。
IF関数に関しては
【IF関数の使い方】で詳しくまとめているので参照ください
IF関数で月終わり(C3)を超えたものは空欄("")表示にしていきます。
=IF(日付!$C$3>=(B6+1),(B6+1),"")
ですがここでまたオートフィルをすると
空白行に対してIF関数の条件式が成り立たなくなる為エラー表示されます
そこで最後にIFEEROR関数を使用してエラー表示を消していきます。
IFERROR関数説明
IFERROR関数は対象のセルがエラー表示された際に別の表示に変えることができる関数です。
=IFERROR(対象セル,エラーの際の表示)
=IFERROR(A1,"エラーです")
先ほどのIF関数をIFERROR関数の中へ入れてエラーの際は空欄("")表示にしていきます。
=IFERROR(IF(日付!$C$3>=(B6+1),(B6+1),""),"")
エラー表示がなくなったので
これで日付変更のの自動化は終了です！
②曜日更新の自動化
次の曜日の自動化はすぐ終わります。(笑)
まず日付シートの日付欄から曜日欄へリンク貼り付けして
曜日欄を選択しセルの書式設定からユーザー設定の下写真の赤枠欄に
「aaa」と打つだけ。
※()つけたいなら「"("aaa")"」ダブルクォーテーションで囲む。
③休みの日の色変更の自動化
帳票シートの題名以外のシート全域を選択
ホームタブの条件付き書式を選択
新しいルールを選択
数式を使用して書式設定するセルを決定
WEEKDAY関数で曜日番号を読みだす
WEEKDAY関数説明
WEEKDAY関数は対象のセルを曜日番号で表示する関数です。
※曜日始め番号➡１：日曜始め、２：月曜始め
=WEEKDAY(対象セル,曜日始め番号)
=WEEKDAY(A1,2)
曜日始め番号は月曜始めの２にして
対象セルは今選択しているのがB6:E36でB列のみ参照したいので
絶対参照を使い「$B6」とする
絶対参照説明
・参照セルを固定にしてオートフィルを使いたい時には絶対参照を使用する
=IF($A$1="東京",$A1,A$1)
参考：$A$1はA1を固定,$A1はAを固定,A$1は1を固定
まず土曜日(曜日番号６)の色を変えたいので
=WEEKDAY($B6,2)=6
上記コードを数式欄に打ち、
書式ボタンから変えたい書式を選択しOK。
次は日曜日(曜日番号7)の色を変えたいので
=WEEKDAY($B6,2)=7
先ほどと同じ手順で書式設定。
次は祝日等の不定休な休みの色替えです
日付シートのデータシートに休日欄を作成
また同じように帳票シートの題名以外のシート全域を選択し
ホームタブの条件付き書式を選択
新しいルールを選択
数式を使用して書式設定するセルを決定
式には日付シートの休日欄の題名以外のシートを参照したいのでD6:D36となる
そこで「D」のみ固定の「$D6」を参照して式を作成
=日付!$D6="休み"
上記コードを数式欄に打ち、
書式ボタンから変えたい書式を選択しOK。
これにより日付シートの休み欄に「休み」と打てば
帳票シートの色が変わるように設定できた。
まとめ
長い記事を最後まで読んでくださりありがとうございます。
作成に時間がかかりますが一度作れば「年/月」を打ち換えるだけで
同じブック内のすべての日付に反映できる強力な自動化TOOLになりますので
これを機会に挑戦してみてください。