エクセルでスケジュールを管理されている方は多いと思います。そんなときに、期限が迫っていることが一目でわかれば優先順位が立てやすいですよね。
今回は、IFS（イフエス）関数とEDATE関数で、「1週間以内」「1ヶ月以内」や「3か月以内」などの表示をし、それに伴ってセルの色を変更する方法です。
まずはIFS（イフエス）関数を利用して条件分岐
「D1」セルを「今日」と設定しています。下の例では2019年10月1日となっています。
ちなみにエクセルの関数で「今日」の日付を表示させるには「=today()」で取得できます。
IFS（イフエス）関数についてはこちらを参考にしてください。
まずは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ヶ月以内はEDATE関数で1ヶ月後の正確な日付を求める必要がある
続いて「1ヶ月以内」の表示をする条件を追加します。ご存知のように、1ヶ月は月によって日数が違います。ですから、正確な1ヶ月の日数を取得するために「EDATE関数」を利用します。
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か月以内」も同様に表示されました。
期限によって「条件付き書式」でセルの色を変える
続いて、期限によって「条件付き書式」でセルの色を変える方法です。
文字列による「条件付き書式」の設定は簡単
期限によってセルの色を変えたいセル範囲（ここではB2～B12）を選択し、「ホーム」「条件付き書式」「新しいルール」をクリックします。
「新しい書式ルール」で「指定の値を含むセルだけを書式設定」、セルの値を「次の値に等しい」、条件を「1週間以内」とし、「書式」から「セルの塗りつぶし」で赤色を選択します。
ここでいう条件「1週間以内」とは、セルに表示されている文字列が「1週間以内」であればセルを赤色にする、という条件を設定しています。
確認後、「OK」をクリックします。
すると、下図のようにセルに「1週間以内」の文字列があるセルが赤色に変化しているのが分かります。
同様に、再び「条件付き書式」「新しいルール」をクリックして、下記のように「1ヶ月以内」が表示されているセルを「黄色」に設定します。
きちんと色分けされていることが確認できます。必要であれば、「3か月以内」もセルの色を変化させることができます。
日付の条件分岐はやや複雑に
文字列での書式設定の分岐は簡単にできましたが、日付の計算による条件分岐はやや複雑になります。
色を変えたい範囲（セルA2～A12）までを選択して、「ホーム」「条件付き書式」「新しいルール」をクリックします。
「新しい書式ルール」で「指定の値を含むセルだけを書式設定」「次の値の間」の左のボックスに「=$d$2」（今日の日付）、右のボックスに「=$d$2+7」を入力します（今日の日付から＋7日の間を設定）。入力後、「書式設定」でセルの塗りつぶしを赤に設定後、「OK」をクリックします。
「今日」から1週間のセルの色が変更されました。
続いて、「今日」から「1ヶ月以内」の設定です。
「新しい書式ルール」で「指定の値を含むセルだけを書式設定」「次の値の間」の左のボックスに「=$d$2」（今日の日付）、右のボックスに「=edate($d$2,1)-1」を入力します（今日の日付から1ヶ月後から1日減らす設定）。さらに「セルの塗りつぶし」を黄色に設定後、「OK」をクリックします。
ところが、ご覧のようにすべて黄色になってしまいます。
「条件付き書式」のルールでは優先順位があり、下記のように先に1ヶ月以内のセルを黄色に変えてしまいます。
「ホーム」「条件付き書式」「ルールの管理」をクリックします。
御覧のように、セルの色を黄色に変える条件が上に来ていますので、下矢印ボタンで優先順位を変更します。
先に「1週間以内」のルール、その後に「1ヶ月後」のルールになるように優先順位を変更して「OK」ボタンをクリックです。
以下のようにうまくいきました。
今日の日付を2月にしてもちゃんと月間日数28日で計算されます。
日付の計算に必須なEDATE関数
日付の計算は非常に複雑です。ただエクセルはちゃんとうるう年も考えてシリアル値を取得します。
シリアル値とは、「1900年1月1日」を「1」とし、そこからの経過日数で管理されている数値のことです。
それでも月ごとに日数の違う月をまたいだ計算は非常に大変です。EDATE関数は、そのような月をまたいだ計算で非常に役立つ関数ですのでぜひマスターしておくことをおすすめします。
コメント