エクセルで期限管理! IFS(イフエス)関数とEDATE関数と条件付き書式設定で1週間以内・1ヶ月以内・3か月以内等の表示と色分けで見やすくスケジュール管理

スポンサーリンク

エクセルでスケジュールを管理されている方は多いと思います。そんなときに、期限が迫っていることが一目でわかれば優先順位が立てやすいですよね。

今回は、IFS(イフエス)関数とEDATE関数で、「1週間以内」「1ヶ月以内」や「3か月以内」などの表示をし、それに伴ってセルの色を変更する方法です。

期限によってセルに文字を表示したりセルの色を自動的に変更する
スポンサーリンク

まずはIFS(イフエス)関数を利用して条件分岐

「D1」セルを「今日」と設定しています。下の例では2019年10月1日となっています。

ちなみにエクセルの関数で「今日」の日付を表示させるには「=today()」で取得できます。

日付によって期限のセルに表示される文字を変更する

IFS(イフエス)関数についてはこちらを参考にしてください。

Excel(エクセル)2019の新機能! 追加された新関数IFS(イフエス)の使い方 IF関数よりもシンプルに条件分岐が可能!
Excel(エクセル)2019で追加された機能Excel2019になっていろいろな機能が追加されています。CONCATIFSMAXIFSMINIFSSWICHITEXTJOIN最後にSがついている関数以外は...

まずは1週間以内を表示

まずは「1週間以内」を表示させる数式はIFS(イフエス)関数を利用します。具体的な式は下記の通りです。「今日」の日付のセル(D2)は変更してはいけないので絶対参照(列と行の前に$マークを入れる)で表しています。

=IFS(A2-$D$2<0,””,A2-$D$2<=7,”1週間以内”,TRUE,””)

1つ目の条件、 A2-$D$2<0,”” は、「今日の日付よりも過去(日付が小さい、マイナス)ならば「””(空白)」を表示させるという意味です。

そして2つ目の条件、 A2-$D$2<=7,”1週間以内”はA列のセルの日付が「今日」から「7日間」の間に入れば”1週間以内”という文字列を表示させることを意味しています(厳密に1週間以内は「<7」かもしれませんが、ここでは「<=7」としています)。

さらに3つ目の条件である TRUE,” “は、それまでの「どの条件にも当てはまらない場合」は「””(空白)」を表示させる数式になります。

ちなみにIFS(イフエス)関数は、最大127個まで定義できます。

B2セルに上記の数式を入力し、数式をドラッグしてB12セルまでコピーをします。

すると、「今日」から「1週間以内」の日付のセルの横に「1週間以内」の表示がされました。

1週間以内が表示された

1ヶ月以内はEDATE関数で1ヶ月後の正確な日付を求める必要がある

続いて「1ヶ月以内」の表示をする条件を追加します。ご存知のように、1ヶ月は月によって日数が違います。ですから、正確な1ヶ月の日数を取得するために「EDATE関数」を利用します。

エクセルの条件付き書式で期限・締め切りが迫るとセルの色が変わる設定方法 & EDATE関数で正確な2か月後、半年前などの日付を取得する方法
エクセルで、当日から締め切りが近づいてきたらセルの色が変わる、例えば今後1週間や1ヵ月以内の日付だけ色を変更するといったことがエクセルの「条件付き書式」で可能になります。エクセルの「条件付き書式」で指定した範囲の日付のセルの色を変...

EDATE関数は、「=EDATE(基準となるセル,月数)」と2つの引数が必要です。基準となる日付のセルを1つめの引数に、そこからの月数を2つ目の引数(2ヶ月後なら2、1ヶ月前なら-1)を指定します。例えば2月1日から2ヶ月後であれば4月1日(経過日数はうるう年でなければ59日後)を取得します。

EDATE関数を利用して、B2セルの数式の3つ目の条件に、下記の式を追加します。

=IFS(A2-$D$2<0,””,A2-$D$2<=7,”1週間以内”,A2<EDATE($D$2,1),”1ヶ月以内”,TRUE,””)

A2<EDATE($D$2,1),”1ヶ月以内”は、「今日」のちょうど1ヶ月後の日付を取得し、その日付よりも小さければ「1ヶ月以内」の表示をさせることを意味しています。

B2セルに「1ヶ月以内」の条件を追加し、B12セルまでドラッグで数式をコピーします。


1ヶ月以内の文字が表示された

「3か月以内」も同様にEDATE関数を利用する

「3か月以内」も同様にEDATE関数を利用して下記のような式になります。赤色アンダーラインの部分を4つ目の条件として新たに追加しています。数式の意味はもうお分かりでしょうか。

=IFS(A2-$D$2<0,””,A2-$D$2<=7,”1週間以内”,A2<EDATE($D$2,1),”1ヶ月以内”,A2<EDATE($D$2,3),”3ヶ月以内”,TRUE,””)

上記の式をB2セルに追加入力し、B12セルまでドラッグでコピーすると、下図のように「3か月以内」も同様に表示されました。

3か月以内も表示された

期限によって「条件付き書式」でセルの色を変える

続いて、期限によって「条件付き書式」でセルの色を変える方法です。

文字列による「条件付き書式」の設定は簡単

期限によってセルの色を変えたいセル範囲(ここではB2~B12)を選択し、「ホーム」「条件付き書式」「新しいルール」をクリックします。

「条件付き書式」「新しいルール」

「新しい書式ルール」で「指定の値を含むセルだけを書式設定」、セルの値を「次の値に等しい」、条件を「1週間以内」とし、「書式」から「セルの塗りつぶし」で赤色を選択します。

ここでいう条件「1週間以内」とは、セルに表示されている文字列が「1週間以内」であればセルを赤色にする、という条件を設定しています。

確認後、「OK」をクリックします。

新しい書式ルール

すると、下図のようにセルに「1週間以内」の文字列があるセルが赤色に変化しているのが分かります。

条件にあったセルが赤色に変化

同様に、再び「条件付き書式」「新しいルール」をクリックして、下記のように「1ヶ月以内」が表示されているセルを「黄色」に設定します。

「1ヶ月以内」のセルを黄色に

きちんと色分けされていることが確認できます。必要であれば、「3か月以内」もセルの色を変化させることができます。

期限によって色分けされた

日付の条件分岐はやや複雑に

文字列での書式設定の分岐は簡単にできましたが、日付の計算による条件分岐はやや複雑になります。

エクセルの条件付き書式を使って令和元年を表示する方法(ついでに平成元年も可) Excel 365・2019・2016・2010・2007対応
先日、セルの書式設定で「令和1年」表示を「令和元年」表示にする方法をご紹介しました。今回は、条件付き書式で「令和元年」、ついでに「平成元年」の表記を同時にする方法の紹介です。条件付き書式の条件の数は、エクセル2007より無制限にな...

色を変えたい範囲(セルA2~A12)までを選択して、「ホーム」「条件付き書式」「新しいルール」をクリックします。

日付の条件分岐はやや複雑に

「新しい書式ルール」で「指定の値を含むセルだけを書式設定」「次の値の間」の左のボックスに「=$d$2」(今日の日付)、右のボックスに「=$d$2+7」を入力します(今日の日付から+7日の間を設定)。入力後、「書式設定」でセルの塗りつぶしを赤に設定後、「OK」をクリックします。

今日から1週間を指定

「今日」から1週間のセルの色が変更されました。

今日から1週間のセルが赤に

続いて、「今日」から「1ヶ月以内」の設定です。

「新しい書式ルール」で「指定の値を含むセルだけを書式設定」「次の値の間」の左のボックスに「=$d$2」(今日の日付)、右のボックスに「=edate($d$2,1)-1」を入力します(今日の日付から1ヶ月後から1日減らす設定)。さらに「セルの塗りつぶし」を黄色に設定後、「OK」をクリックします。

EDATE関数を利用して1ヶ月の範囲を設定

ところが、ご覧のようにすべて黄色になってしまいます。

全てのセルが黄色に

「条件付き書式」のルールでは優先順位があり、下記のように先に1ヶ月以内のセルを黄色に変えてしまいます。

「ホーム」「条件付き書式」「ルールの管理」をクリックします。

御覧のように、セルの色を黄色に変える条件が上に来ていますので、下矢印ボタンで優先順位を変更します。

優先順位を変更

先に「1週間以内」のルール、その後に「1ヶ月後」のルールになるように優先順位を変更して「OK」ボタンをクリックです。

優先順位を確認後、OK

以下のようにうまくいきました。

条件の優先順位には注意が必要

今日の日付を2月にしてもちゃんと月間日数28日で計算されます。

月によって日数が違ってもちゃんと取得可能

日付の計算に必須なEDATE関数

日付の計算は非常に複雑です。ただエクセルはちゃんとうるう年も考えてシリアル値を取得します。

シリアル値とは、「1900年1月1日」を「1」とし、そこからの経過日数で管理されている数値のことです。

それでも月ごとに日数の違う月をまたいだ計算は非常に大変です。EDATE関数は、そのような月をまたいだ計算で非常に役立つ関数ですのでぜひマスターしておくことをおすすめします。

スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言

コメント

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