今回の記事では
日付を比較して期限切れの管理をしたいけど便利な方法って無いの？
関数を使った方法があれば教えてほしい
スケジュールなどの管理で期限を上手に管理する方法ってある？
期限までの日数や期限切れなどの進捗管理を自動化したい
こういった疑問に答えます
✔日付の差を活用する方法のテーマ
①離れたセルに年月日が設定されている場合はDATE関数を使うのが正解です
②日付の差をうまく活用すれば簡易的なスケジュール管理は簡単に出来ます
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「日付の差を活用する方法のテーマ」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
離れたセルに年月日が設定されている場合はDATE関数を使うのが正解です
年月日が一つのセルではなくそれぞれ離れたセルに別れている場合は、DATE(デイト)関数を使ってシリアル値に直してあげるのが最適解です
なぜなら、日付を計算で使うためにはシリアル値が必要だからです
それでは今回は「食品の賞味期限切れチェック表」を基に賞味期限が切れていないか自動的にチェックする方法について見ていきましょう
こんな感じで「指定の年月日と賞味期限の日付を比較」して賞味期限切れの食品をピックアップしてみましょう
作業前に前提として
年月日の表示は表示形式を工夫して「〇〇年 △△月 ××日」と表示しているのでセルの内容は「全て数字」しか入力されていません
表示形式については「日付と時刻はシリアル値で出来ている」という記事でも解説していますので、分からない場合は参考にしてみて下さい
賞味期限が設定された日付を過ぎている場合は「賞味期限切れ」過ぎていなければ「OK」と表示するように設定します
では、設定方法を順を追って解説していきます
設定内容はこんな感じです
賞味期限の日付と年月日の日付を比較して「賞味期限が過去の日付」だったら賞味期限切れという設定をしています
まず、比較してというのがポイントなので先に確認しておきましょう
Excelで比較するには「比較演算子」を使って値を比べることで実現します
比較演算子とは「>」や「=」などを使って対象を比較する記号のことです
基本的な比較演算子と意味は次の通りです
これを前提に式を見ていきましょう
| 比較演算子 | 意味 |
|---|---|
| > | 「より大きい」右側より左側(開いている側)が大きい |
| >= | 「以上」右側より左側(開いている側)が大きいか、等しい(同じ) |
| < | 「より小さい」左側より右側(開いている側)が大きい |
| <= | 「以下」左側(開いている側)より右側が大きいか、等しい(同じ) |
| = | 「等しい(等号)」左側と右側が同じ |
| <> | 「等しくない(不等号)」左側と右側が一致しない |
今回の式は「=IF(B10<DATE($B$5,$B$6,$B$7),“賞味期限切れ”,“OK”)」です
ここから、DATE(デイト)関数の部分だけ切り離してみましょう
式は「DATE($B$5,$B$6,$B$7)」です
DATE関数の基本的な使い方は「DATE(年,月,日)」これだけです
つまり、「DATE(2022,2,5)」と入力されているのと同じことですね
なぜなら「B5には年数・B6には月数・B7には日数」がそれぞれのセルに入力されているからです
この数字を日付(シリアル値)に変換してくれるのがDATE関数の機能です
では、再度今回の式を日本語に直してみましょう
IF関数とネストしていますが、難しくは無いので少しずつ理解していきましょう
「=IF(もし、B10セルの値がDATE関数の値よりも小さければ,「賞味期限切れ」と表示してね,そうじゃなかったら「OK」と表示してね)」という意味になります
今回のポイントは「日付を比較する」ということです
日付を比較するにはどうしたら良いでしょうか？
人間が比較する場合は見た目で比べることが出来ますが、Excelは計算をするために数字を使って比べるようにしています
なぜなら、特に日付や時間は色々な書き方があって計算には向いていないためです
そのためシリアル値という数字で管理・計算するのが一番効率的なんですよね
言葉では分かりづらいので日付の比較を図解してみましょう
こんな感じで日付をシリアル値として比較しているので今回のように簡単に日付の比較が出来るという訳です
DATE関数を使わないで賞味期限チェック
次はDATE関数を使わないで賞味期限をチェックする方法について解説していきます
今回の場合は年月日が1つのセルにある場合です
こんな風に日付が1つのセルにある場合は
比較演算子だけで判断できます
式は「=IF(B7<$B$4,”賞味期限切れ”,”OK”)」なのでDATE関数の代わりに「B4のセル」を参照しています
コレだとさっきよりももっとシンプルです
理屈は全く同じなので「どうしても日付を分けるような作りでないといけない場合はDATE関数」を使いそうでなければ「セルの参照」で比較出来ます
日付=シリアル値ということを理解してもらい、日付でも比べたり計算出来るんだ
ということが分かってもらえればOKです
繰り返しになりますが、年月日が一つのセルではなくそれぞれ離れたセルに別れている場合は、DATE関数を使ってシリアル値に直してあげるのが最適解です
日付の差をうまく活用すれば簡易的なスケジュール管理は簡単に出来ます
スケジュールの管理をしたい時も日付を計算することで自動化出来ます
なぜなら、日付を計算した結果によって表示する内容を変更することで管理を自動化することが出来るからです
それでは簡単なスケジュール表を基に期日の管理を自動化する方法を解説していきます
今回はこんな風に「期日を過ぎてしまった場合」「期日になった場合」「期日までの日数を表示する場合」の3パターンを設定します
今回の設定は参照先のセルの位置が違うだけで基本は一つの式で出来ています
では、その式を見てみましょう
設定した式はこんな感じです
「=IF(C8-$B$4=0,”期日です”,IF(C8-$B$4<0,”期日を過ぎています”,”期日まであと”&C8-$B$4&”日です”))」
長すぎて意味がわからないですね
まずは、最初の設定内容だけ見てみましょう
今回はIF関数を2つネストしてるので難しそうに見えるだけです
分解していけば意外と簡単なので少しずつ理解してみましょう
まずは「=IF(C8-$B$4=0,“期日です”,」です
この内容を日本語にすると「=IF(もし、C8のセルの値－B4のセルの値=0だったら,「期日です」って表示してね,」という意味です
つまり、C8セルのシリアル値「44593」からB4セルのシリアル値である「44600」を引き算します
計算式は「44593-44600=-7」つまり「7日期限を過ぎている」ということになります
今回の式の場合この計算式が「44593-44593=0」となった場合は「期日ですと表示して」という意味なので次の条件に進みます
答えが「0」ということは、「日付に差がない」つまり当日ということになりますね
ここまでは大丈夫ですか？
ここまでの動きを図解してみましょう
こんな感じで期日当日の場合は「期日です」と表示する仕組みを作っています
ただ、このままだと計算結果が「プラスの数字とマイナスの数字になった場合の表示設定」が出来ていませんよね
そこで次のIF関数の設定が必要になっていきます
まずは「IF(C8-$B$4<0,”期日を過ぎています”,”期日まであと”&C8-$B$4&”日です”)」です
これでも長いので前半と後半に分けますね
【前半】
「IF(C8-$B$4<0,“期日を過ぎています”,」ここまでを切ってみました
これはさっきとほぼ一緒で「C8のセルとB4のセルの答えが０よりも小さければ」つまり「マイナスの数字」になった場合は「期日を過ぎていますと表示してね」という意味になります
今回は、結果が「-7」だったのでこの結果が表示されているという訳です
次は、後半部分を見てみましょう
【後半】
最後の部分は「“期日まであと”&C8-$B$4&”日です”)」ですね
いずれの設定にもならなかった場合は「期日まであと〇〇日です」と表示させる設定です
複雑に見えますがコレも分解してしまえば簡単ですね
前提として「&(アンド)」は前後の文字列を結合(くっつける)ための記号なので一旦「&」を無視して考えてみましょう
「”期日まであと”」「C8-$B$4」「”日です”」分けてみるとこの3つで出来ています
最初と最後の文字は文章なので特に問題無いですよね
ポイントは「C8-$B$4」です
これは、何度も出てきていますが「期日-今日の日付」で日数を「プラスの数字」「0」「マイナスの数字」のどれになるか計算で数値を求めています
期日までの日数は今までと同じ計算で出るので前後の文章とくっつけて「期日まであと(計算結果の数字)日です」と柔軟に日数を変えて表示してくれるように設定しています
設定内容は複雑に見えますが、基本は同じなのでこの方法もチャレンジしてみて下さい
繰り返しになりますが、スケジュールの管理をしたい時も日付を計算することで自動化出来ます
よくある質問：期日を過ぎた場合は文字を赤くして分かりやすくしたいんだけど、自動的に色を変える方法って無いの？
文字色を自動的に変えたい場合は「条件付き書式に式を設定」するのが正解です
なぜなら、計算結果によって書式の設定をしてくれるので手動で塗り分ける手間が減るからです
それでは、実際の設定方法を見ていきましょう
言葉だけだと難しく感じますが、設定する数式は今までと同じで条件付き書式の設定もすごく簡単なので心配しないで大丈夫です
D8～D10セルを範囲選択します
「ホームタブ」の「スタイルグループにある条件付き書式ボタン」をクリック
「ルールの管理」をクリック
「条件付き書式ルールの管理ダイアログ」が表示されるので「新規ルールボタン」をクリック
「数式を使用して、書式設定するセルを決定」をクリック
「次の数式を満たす場合に値を書式設定」のテキストボックスに「=C8-$B$4<0」という式を入力
「書式ボタン」をクリック
「フォントタブ」の「色のリストボックス」をクリック
「標準の色の赤」をクリック
「OKボタン」をクリック
「OKボタン」をクリック
「OKボタン」をクリック
無事、「期限を過ぎています」と表示された時だけ自動で「文字色を赤」に設定してくれました
今回のポイントは、「=C8-$B$4<0」という式を設定したことです
この式を見ると分かるように「C8セル-B4セルが0より小さい場合」という条件を言葉の代わりに設定しています
つまり、設定内容は今まで設定してきた数式と全く同じです
同じ式を設定して結果が「マイナス」になった時は文字色を赤にしてということを数式で設定してあげたという訳です
ちょっと難しい設定ですが、慣れてくると分かってくるのでなるべく繰り返し練習してみて下さい
繰り返しになりますが、文字色を自動的に変えたい場合は「条件付き書式に式を設定」するのが正解です
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す