今回は実際の事務作業でありそうなエクセルを使った様式作成において、
ずっと使える勤務表やカレンダーが作れるようになる関数について紹介していきます。
万年カレンダーなんて呼ばれますが、特定の場所(セル)に年や月を入力すれば、自動で日と曜日を更新してくれるようになります。
毎月の初めに原本ファイルとなっている勤務表やカレンダーの日にちや曜日を手動で更新しているのであれば、こちらの記事を参考にこれらの業務による負担を軽くしていきましょう。
この記事はこんな人にオススメです。
日付と曜日が自動更新できるようにしておくと、繰り返し作業がなくなるので便利です。簡単な関数だけで作れますので是非気軽に挑戦していきましょう。
Excel(エクセル)でつくる勤務表の完成イメージ
まずは完成予定図をイメージできるようにみておきましょう。
このシートの左上にある年や月の値の部分を更新すると日や曜日の値を自動で書き換えてくれるようにします。
また、始業時刻や就業時刻と休憩を入力すると稼働時間を自動で計算してくれるようにしていますのでこちらも後で紹介しておきます。
文章だけだと分かりにくいので、以下でどのような動きになるか図でみていきましょう。
上の図の赤色枠で囲われている部分を変更すると、緑色に着色した部分が赤枠の値に応じて更新されるといった内容になります。
 |  |
日づけが自動で変更されるようにする方法
さっそくですが、日と曜日が自動で更新されるように日と曜日を表示したいセルの中身をみていきましょう。
緑色に着色している部分(画像だとA7セル)に”1“を表示させたいので、A7セルの中に数式を書いていきます。
関数(数式)の入力手順と内容の解説
A7セルに記載する数式は以下のとおり。
=IF(OR($A$5=””,$D$5=””),””,DATE($A$5,$D$5,1))
エクセルでセルに数式を書くときのポイントとしては先頭に【 ＝ 】を書いてください。
この数式で使用している関数は以下の3つで、これらを組み合わせています。
IF関数
説明
条件分岐時に使います。（【はい】であった場合は〇〇を実行、【いいえ】であった場合は▲▲を実行など）
記入例
=IF(A1=”はい”,”〇〇”,”▲▲”)
OR関数
説明
条件を組みわせる場合などに使います。(A1セルが空白または、B1セルが空白であった場合など真偽値(TrueかFalse)を返します。）
記入例
=OR(A1=””,B1=””)
DATE関数
説明
指定した要素から日付を表す数値を表示したい場合に使います。(指定する要素(引数)は【 年 】と【 月 】と【 日 】となります)
記入例
=DATE(2022,10,1) ➡ 2022/10/1がセルに表示されます
IF関数についてはこちらでも解説していますので参考にしてください。
続いて具体的に数式の内容について解説します。
=IF(OR($A$5=””,$D$5=””),””,DATE($A$5,$D$5,1))
まずは、下線を引いたIF文にの条件部分について見てみましょう。
IF文は条件に応じて処理を変更する際に書く関数になります。
今回の場合、OR関数が中に入れ子(ネスト)されているため、どちらかの条件が指定どおり(True)であれば、〇〇という処理をOR関数の条件をどちらも指定どおりではない(False)ときは、▲▲という処理を分岐して実行することになります。
今回の数式で具体的に言えば、以下のどちらかのセルが空白であればという条件を指定しています。
この条件に対して指定どおり(True)だった場合の結果は以下の画像で確認できます。
A5セル(年が入力されてるセルに何も入力されていない)
または、D5セル(月が入力されてセルに何も入力いない)
※何も入力されていない、空白だったらを条件指定するときの書き方は、対象セル番地=””となります。
画像の場合はA5セルに「2022」、D5セルに「10」が入力されているのでこちらの条件分岐ではFalseの結果となりますので、Falseであった場合の処理が実行されることになります。
改めて数式を確認してみましょう。
=IF(OR($A$5=””,$D$5=””),””,DATE($A$5,$D$5,1))
下線の箇所になりますが、それぞれの処理をカンマ【 , 】で区切って実行したい結果を入力します。
条件に当てはまった場合は赤文字のとおり、何も入力(空白)しません。
一方、条件に当てはまらなかった場合は、青文字の部分の数式の結果が返ってくることになります。
それでは、青文字部分だけを取り出して見ていきましょう。
＝DATE($A$5,$D$5,1)
今、勤務表のA5セルには【 2022 】とD5セルには【 10 】と入力されているので、数式を値で表現した場合は下記のとおりとなります。
数式内のセル番地指定の”$”は絶対参照と言い、数式をコピーしても指定したセルは変えたくない場合につかいます。
＝DATE(2022,10,1)
DATE関数は要素(引数)から日付を返すので、結果として2022/10/1がセルに表示されることになります。
セルの表示設定を変更する
先に記載したDATE関数にて、日付が返ってくることになりますが表示設定を変更していない場合は、2022/10/1がA7セルに表示されることになります。
これを画像の様に日付である【 1 】のみを表示する設定に変更していきましょう。
尚、エクセルのバージョンなどによって操作方法は異なる場合があるので画像で紹介します。
上の画面の左側より分類にて「ユーザー定義」を選択して、種類の枠の中に d を記入してください。
※ｄはDayの頭文字なので、日付を表示する設定となります。
31日分のセル範囲(今回の勤務表ならA7セル～A37セル)を選択して表示設定を変更しておきましょう。
2日以降の日付を表示する
A7セルに1日を表示する数式(関数)入力とセルの表示設定の変更が完了したら、2日以降の処理をしてきましょう。
A8セルに入力する数式は以下のとおりです。
=IF($A$7=””,””,A7+1)
こちらの数式(関数)もIF文を使っています。
今度はA7セルに何も入力されていない(空白)のときはA8セルは何も表示せず、A7セルに何か入力されているときはA7セルの値に＋1をしたものをA8セルに表示するといったものになります。
ここでのポイントは数式(関数)の最初に出てくる$A$7には$がついていて絶対参照としているが、末尾側のA7セルは$を使わずに相対参照としている点です。
これは、IF文の条件に指定したA7セルはコピーしても動かさずに参照して欲しいためです。
A7セルに【 1 】日が表示されていないのに、A8セルに【 2 】日が表示されることは日付を入力する上で想定していないからとなります。
ここまできたら、A8セルの内容をコピーしてA9～A37セルまでペースト(貼り付け)すれば、日付入力の自動化は完了となります。
月末日付が月ごとで異なる部分に対応する
ここでは、月ごとの月末日付が31日や30日があったり、2月28日とうるう年に合わせて日付を表示するように数式を用いて設定していきましょう。
本部分に対応するために下記の準備をしておきましょう。
- 月末の日付を返してくれる関数を使って月末日付を求める
- 1.を返してくれるセルは印刷範囲外もしくはフォント色を白にして見えなくする
- この関数で求めた月末の日を引数として日付を求める関数を使う
それでは順番にみていきましょう。まずは月末の日付を返してくれる関数については以下のとおり
EOMANTH関数
説明
指定した引数(開始日、月)から月末の日付を返してくれる関数となります。
記入例
（セルを指定する場合）
=EOMONTH($A$1,0)
※引数として指定するセル($A$1)には日付またはシリアル値が入力されているようにしてください。
（値を指定する場合）
=EOMONTH(”2022/10/1”,0)
※44865(シリアル値)を表示設定を日付に変更すると”2022/10/31”が返ってきます。
上記を参考にA4セルに【 EOMANTH 】関数を入力していきましょう。
=EOMONTH(A7,0)
画像の勤務表と同じようにA5セルに2022、D5セルに10と入力されているのであればA7セルには【 44865 (2022/10/31)】が返ってくるはずです。
これでA5セルの【 年 】と、D5セルの【 月 】に合わせて月末の日付が求められました。
※試しに以下を入力してみると応じた数値が返ってくるのでわかりやすいですよ。
| 書き換える対象のセル | 現在の値 | 更新後の値 |
|---|---|---|
| A5セル | 2022 | 2024 |
| D5セル | 10 | 10 |
続いて、A7セルを見えないように加工しておきましょう。
※A7セルはあくまで他のセルで使う数式の引数として扱いたいので、勤務表を使う人にとっては特に変更する必要も見えている必要もありません。
表示の設定方法は以下のとおりです。
フォント色を変更したセルを選択した状態で画面上部の【 ホーム 】タブからフォント色を変更するアイコンを選択し、指定の色(ここでは白)を選択すればOKです。
さいごにA7セルで返ってきた値を引数として2日以降の日付を表示する数式を書き換えていきます。
画像の勤務表で言えば、A8セルからA37セルの数式を書き換えていきます。
A8セルの数式
=IF($A7=””,””,IF(A7+1>$A$4,””,A7+1))
A37セルの数式
=IF($A36=””,””,IF(A36+1>$A$4,””,A36+1))
※行が相対参照となっているのでA8セルの数式ではA7セルを参照。A37セルの数式ではA36セルを参照しています。
IF文をネスト(入れ子)して数式を書いていきます。
- 外側のIF文
- 条件式がA7セルが空白かどうか？
- [はい]空白の場合はA8セルは空白で結果を返す
- [いいえ]空白でない場合は内側のIF文の条件式に移る
- 条件式がA7セルが空白かどうか？
- 内側のIF文
- 条件式が【A7セル＋1】の結果が、A4セル(EOMANTH関数の結果の値)の数値よりも大きいかどうか？
- [はい]大きい場合は空白で結果を返す
- [いいえ]大きくない場合はA7セル＋1の結果を返す
- 条件式が【A7セル＋1】の結果が、A4セル(EOMANTH関数の結果の値)の数値よりも大きいかどうか？
A7セルは10月1日が入っているため、数値が入っていれば今度は「10月1日の翌日」が10月の月末日である10月31日を超過していないかを調べて大丈夫なら「10月2日」を表示する数式です。
曜日が自動で変更されるようにする方法
今度は曜日の数式を書いていきましょう。ここまでくれば後の手順はすごくシンプルです。
関数(数式)の入力手順と内容の解説
TEXT関数
説明
数値に書式設定を適用することで、数値の表示方法を変更することができます。
記入例
(セルを指定する場合)
=TEXT(A1,”aaa”)
(値を指定する場合)
=TEXT(”2022/10/1”,”aaa”)
※日付入力時は【 ” 】で囲み、年・月・日を【 / 】で区切って表記してください。
それでは具体的に数値データから曜日を表示する数式にしてみましょう(日付セルから曜日に変換する数式になります)。
画像の勤務表でB7セルに入力する数式
=IF(A7=””,””,TEXT(A7,”aaa”))
※IF文で判定しているのはA7セル(左隣りのセル)が空白かどうかです。
あとはこれを画像の緑色に着色したB8からB37セルまでコピーすれば完了です。
「日・曜日を自動更新する勤務表の作り方をやさしく解説」まとめ
ここまでに使った機能や関数を整理しておきましょう。
- 関数
- IF関数
条件式で判定して結果によって異なる処理をする関数 - OR関数
条件式を組み合わせることで複雑な条件にすることができる関数 - DATE関数
指定した要素から日付を表す数値を表示することができる関数 - EOMANTH関数
指定した引数(開始日、月)から月末の日付を返してくれる関数 - TEXT関数
数値に書式設定を適用することで、数値の表示方法を変更することができる関数
- IF関数
- 機能
- セルの表示設定よりユーザー定義にて”d”を入力する
さいごに
今回は日付や曜日を自動で更新してくれる勤務表を作るといった具体的な業務を想定して解説してみました。
ここに書いている数式は今回作成した勤務表だけでなく、業務でエクセルを使う場面で応用できることは多いと思います。
是非身つけて繰り返し業務の負担軽減につなげていってください。
以下、今回の解説に使ったエクセルファイルをダウンロードできるようにしておきました。
解説と合わせて見てもらえるともっと理解がしやすいと思います。
サンプル勤務表(2022年10月分)
※別途指定なければダウンロードフォルダに保存されます。
 にほんブログ村 |  |  |
コメント