毎月の日付/曜日更新を自動化　Excel

どの会社でも毎月やっている``【帳票の日付更新】``
めんどくさいと思いながら毎月やっていませんか？
(日付や曜日の``打ち変え``や休みの日の``色変更``など．．．）
企業次第では``毎月数ファイル``打ち換えているなんてざらじゃないでしょうか？
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/56da9554-1659-2fef-d5d2-58e267edea70.png)
こんな``無駄な作業に数十分``時間を取られたくないですよね。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/75c15468-889e-1e6b-c81d-b6b7000a386c.png)

今回はそんな【帳票の日付更新】を自動化する方法を以下3つの順序で書きます。
①日付更新の自動化　←日付だけでいい人はこれだけ
②曜日更新の自動化
③休日の色変更の自動化

# 完成イメージ
今から作るものとしては
年/月を打ち換えると
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/c535f47c-580c-4872-84f6-eca206777056.png)
(①/②)自動で帳票の日付が更新されて、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/a4a81412-6038-3faa-8b3d-bf1790d2af93.png)
(③)土日休みと祝日などの不定休も設定一つで自動色替えできるという機能です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/32febb2e-11f1-e433-20bf-85a926c0e276.png)
一つずつやっていきましょう！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/6ce56207-2640-4759-6ff5-8a990627117f.png)

# ①日付更新の自動化　←日付だけでいい人はこれだけ
まず使用する機能が
　・DATE関数　　　　　←月初め計算用
　・EOMONTH関数　　 ←月終わり計算用
　※少し雑な表示でいい人は上2つでOKです
　・IF関数　　　　　 　←次月分を表示させない為
　・IFFERROR関数　　　←エラー表示出さない為
以上4つです！
・・・多いな。と感じたかもしれないですが
どれもシンプルな関数ですので一つずつやっていきましょう！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/51068c06-a968-1c29-7a2d-82a3a75b36be.png)
まずは打ち換えできるようにする為に``使用している帳票シート``の
``文字(年/月)``と``数字(2022/3)``の欄を分ける
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/48de9f18-237d-cdd7-cdfb-cfdcd6c0a65f.png)
皆さんが使用している帳票のシートとは別に
``「日付」シート``を作りましょう
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/0d483754-5657-df9a-4387-ae309dddd159.png)
日付シートに使用している帳票の``日付(あるなら曜日も)``をB5にコピー
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/7a46fc76-4b0a-3372-91b4-b7352bd9eb19.png)

次は日付シートの``日付(あるなら曜日も)``を帳票シートの個所にリンク貼り付け
<details><summary>リンク貼り付け説明</summary>


リンク貼り付けはコピーしたセルの値と貼り付け先の値を``常に同じにする``機能。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/fb9a7a6f-46d4-3b87-2e07-09cf566ba5f8.png)

</details>

(CTRL+C➡右クリックの貼り付けのオプションから``「リンク貼り付け」``
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/16b11421-3264-075d-5da5-3c121dad8909.png)


日付シートに``月初め/月終わり``欄を作成(C列は空欄でOK) 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/0e4cfc1b-93d0-5189-643e-15c16726d63d.png)
日付シートのC2に``DATE関数``を使用し``月初め``を表示する

<details><summary>DATE関数説明</summary>


```ruby:DATE関数
=DATE(年,月,日)
=DATE(2022,3,1)
```
</details>


　・参照するのは帳票シートの``年のセル``と``月のセル``なので
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/9c9e6e01-795c-58c5-cc84-771ee9e64956.png)
コードはこのようになる(日の個所は月初めなので１)
```ruby:年/月の参照
=DATE(帳票!B3,帳票!D3,1)
```
これで月初めの自動表示はOKです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/8d6610d7-90be-2f94-f78d-e7521fbf407c.png)
次は月終わり(C3)の自動表示です
日付シートのC3に``EOMONTH関数``を使用し``月初め``を表示する
<details><summary>EOMONTH関数説明</summary>



```ruby:EOMONTH関数
=EOMONTH(開始日,何か月後の月末か)
```
</details>

　・開始日は``月初め(C2)``で``0カ月後``の月末なので
```ruby:月終わりの表示
=EOMONTH(C2,0)
```
これで月終わりの自動表示も完了です。
つぎはようやく```日付の自動表示```です!
長かった．．．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/c9d990a5-fb03-33ff-888c-1aa2df94c0b3.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/dc46e643-4a44-3542-fc3d-ed7f327a18ef.png)

まず``日付の初日(B6)``は``月初めの日(C2)``を参照します
```ruby:初日(B6)
=C2
```
2日目(B7)以降は1つ上のセルに+1していけばいいので
```ruby:2日目(B7)
=B6+1
```
``B7より下にオートフィル``でコピーしてください。
すると．．．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/824ed6a1-07a3-511a-cb97-db405b9fc6b2.png)

``次の月``が表示されてしまいます(↑写真赤枠)
これで問題ない方は次へ飛んで大丈夫です！
次の月を``表示させたくない``方は
IF関数を使用して対策します。
IF関数に関しては
【[IF関数の使い方](https://qiita.com/hitaro0205/items/7831ebe7e819a39c53b3 "IF関数記事")】で詳しくまとめているので参照ください

IF関数で月終わり(C3)を超えたものは空欄("")表示にしていきます。
```ruby:2日目(B7)
=IF(日付!$C$3>=(B6+1),(B6+1),"")
```
ですがここでまたオートフィルをすると
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/28d99649-e7e1-9f5a-f700-524f56807a8f.png)
空白行に対してIF関数の``条件式が成り立たなくなる為``エラー表示されます
そこで最後に``IFEEROR関数``を使用して``エラー表示を消して``いきます。

<details><summary>IFERROR関数説明</summary>



IFERROR関数は対象のセルがエラー表示された際に別の表示に変えることができる関数です。
```ruby:IFERROR関数
=IFERROR(対象セル,エラーの際の表示)
=IFERROR(A1,"エラーです")
```
</details>

先ほどのIF関数を``IFERROR関数の中へ``入れて``エラーの際は空欄("")表示``にしていきます。

```ruby:エラー表示変更(空欄)
=IFERROR(IF(日付!$C$3>=(B6+1),(B6+1),""),"")
```
エラー表示がなくなったので
これで日付変更のの自動化は終了です！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/f5f9437c-b34d-8766-3093-d9447011c68e.png)

# ②曜日更新の自動化
次の曜日の自動化はすぐ終わります。(笑)
まず日付シートの``日付欄``から``曜日欄``へ``リンク貼り付け``して
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/e508e0bb-537b-2ee7-7333-0dd762220cf7.png)
曜日欄を選択しセルの書式設定からユーザー設定の下写真の赤枠欄に
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/88562f33-f933-9930-a64c-f1fe5a80c0e1.png)
「aaa」と打つだけ。
　※()つけたいなら「"("aaa")"」ダブルクォーテーションで囲む。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/f66133be-5d58-4aa4-9351-c131830b4faa.png)

# ③休みの日の色変更の自動化
帳票シートの``題名以外のシート全域``を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/75591508-3c48-163a-8832-21354cedcf4e.png)

ホームタブの``条件付き書式``を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/e89159a2-3ce6-a1e4-7ec1-f30ab8c11ebf.png)
``新しいルール``を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/706e4a2d-e583-3e66-3114-f28ed24eb8bd.png)
``数式を使用して書式設定するセルを決定``
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/385dc092-3cf5-32ea-0747-2e891414bd44.png)

``WEEKDAY関数``で曜日番号を読みだす
<details><summary>WEEKDAY関数説明</summary>



WEEKDAY関数は対象のセルを曜日番号で表示する関数です。
※曜日始め番号➡１：日曜始め、２：月曜始め
```ruby:WEEKDAY関数
=WEEKDAY(対象セル,曜日始め番号)
=WEEKDAY(A1,2)
```
</details>

曜日始め番号は月曜始めの２にして
対象セルは今選択しているのがB6:E36でB列のみ参照したいので
絶対参照を使い「$B6」とする
<details><summary>絶対参照説明</summary>



・参照セルを固定にしてオートフィルを使いたい時には絶対参照を使用する
```ruby:絶対参照
=IF($A$1="東京",$A1,A$1)
参考：$A$1はA1を固定,$A1はAを固定,A$1は1を固定
```
</details>

まず土曜日(曜日番号６)の色を変えたいので
```ruby:WEEKDAY関数
=WEEKDAY($B6,2)=6
```
上記コードを``数式欄``に打ち、
``書式ボタン``から変えたい書式を選択し``OK``。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/d53592ee-565e-4cd0-5ef9-40b70b3ee335.png)
次は日曜日(曜日番号7)の色を変えたいので
```ruby:WEEKDAY関数
=WEEKDAY($B6,2)=7
```
先ほどと同じ手順で書式設定。

##### ``次は祝日等の不定休な休みの色替えです``

日付シートのデータシートに休日欄を作成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/660f57dc-70bf-ba88-50ff-18893c46ae41.png)

また同じように帳票シートの``題名以外のシート全域``を選択し
ホームタブの``条件付き書式``を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/e89159a2-3ce6-a1e4-7ec1-f30ab8c11ebf.png)
``新しいルール``を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/706e4a2d-e583-3e66-3114-f28ed24eb8bd.png)
``数式を使用して書式設定するセルを決定``
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/385dc092-3cf5-32ea-0747-2e891414bd44.png)
式には日付シートの休日欄の``題名以外のシート``を参照したいので``D6:D36``となる
そこで``「D」のみ固定``の``「$D6」``を参照して式を作成
```ruby:不定休の色変え
=日付!$D6="休み"
```
上記コードを``数式欄``に打ち、
``書式ボタン``から変えたい書式を選択し``OK``。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/d53592ee-565e-4cd0-5ef9-40b70b3ee335.png)
これにより日付シートの休み欄に「休み」と打てば
帳票シートの色が変わるように設定できた。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552137/b54b5a59-39af-15f5-ec56-cec9989750cf.png)

# まとめ
長い記事を最後まで読んでくださりありがとうございます。
作成に時間がかかりますが一度作れば「年/月」を打ち換えるだけで
同じブック内のすべての日付に反映できる強力な自動化TOOLになりますので
これを機会に挑戦してみてください。