エクセルには関数や数式がありますが、これをうまく利用することで入力作業を最低限にできます。
会社や職場などで、毎日あるいは、毎月くり返し入力し、提出しなければならない書類がエクセルファイルで作られていた場合、毎回の同じことを入力する手間をなるべくはぶきたいところです。
会社や職場には、日ごと、月ごとに提出する書類が多くありますが、本記事では、エクセル関数や数式をつかって、日付や曜日が自動で入力される方法を、勤務表のサンプルをつくりながら紹介しています。
ここで紹介した内容は他の事務書類の様式を作成する上でも使えるものですので、エクセルをつかって事務書類を作成・運用している人は、おぼえておくとルーチンワークの負担をへらすことができます。
なお、ここで紹介したものと異なる方法でも、同じ様式を作成できます。実現する方法は1つではありませんので、あくまで1つの例として参考にしてもらえたら幸いです。
エクセル関数や数式をつかって、日付や曜日を自動で表示する手順は以下のとおりです。
1.年や月を入力するセルをつくる
2.1に入力された年や月を引数にして、日付を返すIF関数とDATE関数を組み合わせたものを指定セルに入力する
3.2を引数にして月の末日を返すEOMONTH関数を指定セルに入力する
4.2を引数にして数式をつかって日付を返す数式を指定セルに入力する
5.日付を入力するセルを引数として曜日を返すTEXT関数を指定セルに入力する
日付と曜日が自動更新できるようにしておくと、繰り返し作業がなくなるので便利です。簡単な関数と設定だけで作れますので、ぜひ気軽にチャレンジしていきましょう。
Excel(エクセル)日付や曜日を自動入力する勤務表の完成イメージ
まずは完成予定図をイメージできるようにみておきましょう。
このシートの左上にある年や月の値を更新すると、日や曜日の値を自動で書き換えてくれるようにします。
文章だけだと分かりにくいので、以下でどのような動きになるか図でみていきましょう。
上の図の赤線で囲われているセルの値を変更すると、緑色のセルが自動で更新されるようになります。
エクセル関数(IF関数とDATE関数)を組みあわせて日付を求める
さっそくですが、日付と曜日を自動で表示するために、セルに入力する数式をみていきましょう。
緑色に着色している部分(画像だとA7セル)に”1“を表示させたいので、A7セルの中に数式を書いていきます。
入力する関数(IF関数とDATE関数)の入力手順と内容の解説
A7セルに記載する数式は以下のとおり。
=IF(OR($A$5="",$D$5=""),"",DATE($A$5,$D$5,1))エクセルでセルに数式を書くときのポイントとしては先頭に【 ＝ 】を書いてください。
この数式で使用している関数は以下の3つで、これらを組み合わせています。
日付を求める関数|IF関数で条件判定をする
IF関数
説明
条件分岐時に使います。
（【はい】であった場合は〇〇を実行、【いいえ】であった場合は▲▲を実行など）
記入例
=IF(A1=”はい”,”〇〇”,”▲▲”)
日付を求める関数|OR関数をIF関数の条件に組み合わせる)
OR関数
説明
条件を組みわせる場合などに使います。
(A1セルが空白または、B1セルが空白であった場合など真偽値(TrueかFalse)を返します。）
記入例
=OR(A1=””,B1=””)
日付を求める関数|DATE関数で要素から日付を表示する
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)だった場合の結果は、A7セルは空白のままです。
今度はD5セルが空白です。
A5セル(年が入力されてるセルに何も入力されていない)
または、D5セル(月が入力されてセルに何も入力いない)
※何も入力されていない、空白だったらを条件指定するときの書き方は、対象セル番地=””となります。
一方、A5セルとD5セルに年と月が入力されている場合は、IF関数の判定結果はFalseとなりDATE関数の処理になります。改めて数式を確認してみましょう。
=IF(OR($A$5="",$D$5=""),"",DATE($A$5,$D$5,1))下線の箇所ですが、それぞれの処理をカンマ【 , 】で区切って実行したい処理を入力します。
指定した条件に当てはまった場合は、””ですので、セルに何も入力(空白)しません。
つまり条件に当てはまらなかった場合は、DATE関数の結果が返ってきます。
それでは、DATE関数の部分だけを取り出して見ていきましょう。
＝DATE($A$5,$D$5,1)勤務表のA5セルに” 2022 “と、D5セルに” 10 “と入力されているものと仮定して、DATE関数をリテラル値で表現した場合は下記のとおりとなります。
数式内のセル番地指定の”$”は絶対参照と言い、数式をコピーしても指定したセルは変えたくない場合につかいます。
＝DATE(2022,10,1)
DATE関数は、要素(引数)から日付を返すので、結果として2022/10/1がセルに表示されることになります。
A5セルと、D5セルに入力された”年”と”月”から日付を自動で入力するようになったよ。
エクセルワークシートのセルの表示設定を変更する
次に記載したDATE関数にて、日付が返ってきた値(2022/10/1)ですが、ワークシート上でセルの表示設定を変更していない場合は、2022/10/1がA7セルに表示されることになります。
こちらのサンプル勤務表では、日付部分である”1“のみを表示して欲しいので、画像のように表示設定に変更していきましょう。
尚、エクセルのバージョンなどによって操作方法は異なる場合があるので画像で紹介します。
表示設定を変更したいセルを選択して、右クリックをするとメニューが表示されるので「セルの書式設定」を選択してください。画像のようなメニューが表示されるので、表示形式タブの分類から「ユーザー定義」を選択し、種類の枠の中に d を記入してください。
※ｄはDayの頭文字なので、日付を表示する設定となります。
31日分のセル範囲(A7セル～A37セル)の表示設定をまとめて変更すると良いよ。
IF関数で日付を表示するか判定する
ここまでで、A7セルに1日を表示する数式(関数)入力とセルの表示設定の変更が完了しました。
ここからは、A8セルからA37セルに2日以降の日付を表示する処理をしてきましょう。
A8セルに入力する数式は以下のとおりです。
=IF($A$7="","",A7+1)こちらの数式(関数)もIF文を使っています。
今度は、A7セルに何も入力されていない(空白)のときは、A8セルは何も表示せず、A7セルに何か入力されているときはA7セルの値に＋1をした値をA8セルに表示する数式です。つまり、A7セルに” 1 “が入力されているときは、指定セルに数値が表示される数式となっています。
実際に運用されている勤務表で1日が入力されていないのに、2日目以降の日付が入っているのは様式としても現実味がないためだね。
ここでのポイントは、数式(関数)の最初に出てくる$A$7には、”$”がついていて絶対参照としているが、末尾側のA7セルは”$”を使わずに相対参照としている点です。
これは、A8セルの数式をコピーして、A9～A37にペーストできるようにするための工夫です。絶対参照にすることでコピペした数式でも参照するセル(A7)を固定しています。一方、数値の計算の引数で使用したA7セルは固定をしていないため、コピペした数式でも参照が変動します。
これを簡単にまとめると以下のようになります。
A8セルの数式
A7セルが空白のときは空白で返す。
A7セルに値が入力されていれば、A7セルの値に1を足した数値を表示する。
A9セルの数式
A7セルが空白のときは空白で返す。
A7セルに値が入力されていれば、A8セルの値に1を足した数値を表示する。
A10セルの数式
A7セルが空白のときは空白で返す。
A7セルに値が入力されていれば、A9セルの値に1を足した数値を表示する。
1日から順に日付が入力されていくようになったね。
EOMANTH関数を利用して指定した月の末日に対応する
さて、ここまでで日付の入力を関数や数式で自動で入力されるようになりました。
しかし、このままでは考慮できていない課題がのこっています。それは月間の日数の違いに対応できていないことです。サンプルで入力した、10月は31日までありますが、11月は30日までです。さらに2月では、平年は28日ですが、うるう年では29日が末日となります。
現在の状態は、末日である30日、または31日などの値が表示されたセルの下のセルから翌月の1日以降が入力されてしまうため、月をまたいで表示します。一般的な勤務表は、月ごとにまとめられているため、これだと違和感があるものとなってしまいます。
ここでは、月の末日に応じた日付を表示することに対応していきます。まずはざっくりとした手順を以下にまとめています。
●月末の日付を返してくれるEOMANTH関数を使って月末の日付を求める
●月末の日付はフォント色を白に変更して、見えないように設定する
●EOMANTH関数で返ってきた値を引数として利用する
EOMANTH関数をつかって月末の日付を求める
月末の日付を返してくれるEOMANTH関数を使います。引数は日付またはシリアル値を指定します。
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 | 2 |
2024年はうるう年で、2月29日まであるね。
EOMANTH関数で返ったきた値を白色フォントに変更する
続いて、A4セルに返ってきた値を見えないように変更します。
※EOMANTH関数で求めた値(A4セル)は、他のセルに入力する数式の引数として利用するので、見えないようにしていても問題ありません。
フォント色の変更方法は以下のとおりです。
フォント色を変更したいセルを選択して、画面上部の【 ホーム 】タブからフォント色を変更するアイコンをクリックします。変更したい色(ここでは白)をえらびましょう。
EOMANTH関数で返ってきた値を引数として利用する
日付を自動で入力する処理の仕上げとして、EOMANTH関数で返ってきた値(A4セル)を引数として、A8からA37セルの数式を書きかえていきます。
各セル、以下のように数式を書きかえましょう。
A8セルの数式
=IF($A7="","",IF(A7+1>$A$4,"",A7+1))A37セルの数式
=IF($A36="","",IF(A36+1>$A$4,"",A36+1))書きかえた数式の解説
IF文をつかって数値の判定をしています。セルに表示する日付が、EOMANTHで返ってきた値(A4セル)よりも大きくなってしまうときは空白を返す処理で、小さいもしくは同じ数値であったときは、たし算の結果を表示します。
少しわかりにくいので、2022年10月のA37セルの例で解説します。
A37セルの数式は、2022年10月の末日である31が表示されることが望ましい結果です。数式にある A36+1>$A$4 で、A36セルには、前日の日付である2022年10月30日が入っています。この前日の値に＋1日後の日付が、末日である2022年10月31日よりも大きい数値となるか判定します。
結果はFalseです。したがって、10月30日の1日後である10月31日が返ってきます。
しかし、2022年11月であった場合はどうでしょうか。
11月は30日が末日であるため、IF文での判定結果はTrueとなり、A7セルは空白が返ってきます。
月末の日付を越えてしまったら空白。そうでなければ前日に1日を足すんだね。
エクセル・ワード・パワーポイントのオンライン学習
料金負担が安いのになんど見ても追加費用は不要！
安心の環境でしっかりとスキルをつけたいならPCHack
TEXT関数で引数から曜日を表示する
ここからは曜日を自動で表示する処理をしていきます。曜日の表示にはTEXT関数を使います。
TEXT関数(数式)の入力手順と内容の解説
数値の表示形式を変更するTEXT関数を利用して曜日を表示する
TEXT関数
説明
数値に書式設定を適用することで、数値の表示方法を変更することができます。
記入例
(セルを指定する場合)
=TEXT(A1,”aaa”)
(値を指定する場合)
=TEXT(”2022/10/1”,”aaa”)
※リテラルで日付を入力する時は【 ” 】で囲み、年・月・日を【 / 】で区切って表記してください。
具体的に数値(シリアル値)から、曜日を表示するようにTEXT関数を入力していきます。
サンプル勤務表のB7セルに入力する数式
=IF(A7="","",TEXT(A7,"aaa"))曜日を表示するコードと実際の表示は以下のとおり。
| 表示コード | 実際の表示 |
|---|---|
| “aaa” | 月 |
| “(aaa)” | (月) |
| “aaaa” | 月曜日 |
| “ddd” | Mon |
| “dddd” | Monday |
ここに載ってない表示コードは、「セルの書式設定」のユーザー定義で確認できるよ。
あとはこれを画像の緑色に着色したB8からB37セルまでコピーすれば完了です。
エクセル関数や数式で日付・曜日を自動で入力する方法まとめ
ここまでに使った機能や関数を整理しておきましょう。
- 関数
- IF関数
条件式で判定して結果によって異なる処理をする関数 - OR関数
条件式を組み合わせることで複雑な条件にすることができる関数 - DATE関数
指定した要素から日付を表す数値を表示することができる関数 - EOMANTH関数
指定した引数(開始日、月)から月末の日付を返してくれる関数 - TEXT関数
数値に書式設定を適用することで、数値の表示方法を変更することができる関数
- IF関数
- 機能
- セルの表示設定よりユーザー定義にて”d”を入力する
さいごに
今回は日付や曜日を自動で更新してくれる勤務表を作るといった具体的な業務を想定して解説してみました。
ここに書いている数式は、今回作成した勤務表だけでなく、業務でエクセルを使う場面で応用できることは多いと思います。ぜひ身つけて色々な様式を作成するときに取り入れてみてください。
便利な勤務表ができたら、毎月の入力作業で負担が軽くできるよ。
以下、今回の解説に使ったエクセルファイルをダウンロードできるようにしておきました。
解説と合わせて見てもらえるともっと理解がしやすいと思います。
サンプル勤務表(2022年10月分)
※別途指定なければダウンロードフォルダに保存されます。
コダマのもりブログはにほんブログ村に登録しています。
ブログの記事が役に立ったと感じて頂けたら、フォローお願いいたします。
コメント