「StiLL」Excel情報500　セル書式--期限が迫っているデータにメッセージを表示し、色を変える

【テーマ】
今回は納期当日、間近になったら自動でメッセージを表示し、色を変える表の作成をご紹介します。この方法を使えば納期を目立つように設定できるため、見逃し防止に役立てることができます。

【方法】
メッセージを表示するセルに「IF関数」と「DAYS関数」を設定します。
条件付き書式を使用して、条件を満たしたらセルの書式を変える設定をします。

【参考】
「DAYS関数」は、Excel2013から追加された関数になります。「NETWORKDAYS関数」を使うと、土、日、祝日を除外した日数を求めることができます。

納期が近づいてきたらセルにメッセージを表示し、色を変えて目立たせると、

納期の見逃し防止に役立てることができます。

今回は納品日または納品日が迫ったら、自動でセルにメッセージを表示し、

セルの背景色を変える方法をご紹介します。

■設定方法

下図を例に説明します。

　　　　前提として今日の日付は、2019/11/25 とします。

１．納品日当日または納品日が近づいたらメッセージを表示する設定

　　１-１．メッセージを表示する列(例ではG列)に「IF関数」「DAYS関数」を設定します。

◎DAYS関数(2つの日付の間の日数を求める関数)

使用例：

=DAYS(終了日,開始日)

下記は、G4セルに設定する数式です。

納品日当日なら「本日出荷」、納品日から5日以内なら「出荷日間近」というメッセージを表示します。

=IF(DAYS(F4,$F$1)=0,"本日出荷",IF(AND(DAYS(F4,$F$1)>=1,DAYS(F4,$F$1)<6),"出荷

　　DAYS関数の終了日を納品日(F4セル)、開始日を今日の日付(F1セル)に指定します。

　　F1セルが「$F$1」と絶対参照になっていますが、これは後で数式を下の行にコピーするためです。

数式の説明

　◎IF(DAYS(F4,$F$1)=0,"本日出荷"

納品日と今日の日付が同じなら「本日出荷」のメッセージを表示します。

　◎,IF(AND(DAYS(F4,$F$1)>=1,DAYS(F4,$F$1)<6),"出荷日間近"

「１」以上で「６」より小さければ(今日から５日以内の日付)、「出荷日間近」の

メッセージを表示します。

※

DAYS関数は「開始日」より「終了日」が前の日付の時(例では、今日の日付よりも前の日)は、

マイナス値を返すので、既に過ぎた日付を含めないように範囲を指定しています。

　◎,""))

上記に当てはまらなければ、「空白」になります。

※

IF関数の詳細については、以下のバックナンバーをご参照ください。

【バックナンバー270】関数--条件の判断IF関数　Excel2007版

※

IF関数のネスト(入れ子)については、以下のバックナンバーをご参照ください。

【バックナンバー383】関数--IF関数の代替数式で値取得

※

AND関数については、以下のバックナンバーをご参照ください。

【バックナンバー248】関数--AND、OR関数　Excel2007版

　　１-２．数式をコピーします。

G列に納品日当日には「本日出荷」、納品日の5日以内には「出荷日間近」というメッセージが表示されました。

２．納品日当日または納品日が近づいたらセルの背景色を変える設定

　　２-１

条件付き書式の設定

①

色を変えたい範囲を指定する。(例ではB4からG13セルまで)

②

[ホーム]-[条件付き書式]-[新しいルール]を選択します。

　　２-２

数式の設定

③

「数式を使用して、書式設定するセルを決定」を選択します。

④

「次の数式を満たす場合に値を書式設定」に数式を入力します。

◎

納品日当日の設定

　数式を、

=$G4="本日出荷

　と入力し、[書式]をクリックします。

例として、条件を満たしたら、セルの背景色を赤色に変える設定をします。

[塗りつぶし]タブから、「赤色」を選択後、[OK]をクリックします。

[OK]をクリックします。

◎

納品日から5日以内の設定

　　上記と同様に、条件付き書式の数式を

=$G4="出荷日間近"

　　と入力し、セルの背景色には「黄色」を設定します。

セルの背景色が、納品日当日は赤色、納品日から5日以内のデータは黄色に変わりました。

■ご参考までに

日数から「土曜日」「日曜日」「祝日」を除きたい場合は、

「NETWORKDAYS」関数(土日と祝日を除外して期間内の日数を求める)を使用します。

使用例：

=NETWORKDAYS(開始

※[祝日]は省略可能です。

祝日を除きたい場合は、予め祝日のデータを作成しておく必要があります。

NETWORKDAYS関数の[祝日]には、

祝日の日付が記載してあるセル範囲または範囲名を指定します。

例として、祝日の日付には「祝日」という名前をつけます。

今回ご紹介したG列の数式をNETWORKDAYS関数に当てはめると(祝日も除外する)、

下記のような数式になります。

=IF(NETWORKDAYS($F$1,F4,祝日)=1,"本日出荷"

,IF(AND(NETWORKDAYS($F$1,F4,祝日)>1,NETWORKDAYS($F$1,F4,祝日)<=6

,"出荷日間近",""))

数式の説明

　◎IF(NETWORKDAYS($F$1,F4,祝日)=1,"本日出荷"

納品日と今日の日付が同じなら「本日出荷」のメッセージを表示します。

　◎,IF(AND(NETWORKDAYS($F$1,F4,祝日)>1,NETWORKDAYS($F$1,F4,祝日)<=6)

　　 ,"出荷日間近"

「１」より大きくて「６」以下なら(今日から５日以内の日付)、「出荷日間近」の

メッセージを表示します。

　◎,""))

上記に当てはまらなければ、「空白」になります。