賞味期限管理・消費期限管理をエクセルで

目  次

1.災害対策用備蓄品の 賞味期限・消費期限管理について

長期保存可能な備蓄食料品も賞味期限・消費期限があります。

備蓄品の賞味期限・消費期限・使用期限をしっかり管理し、期限が過ぎたものは交換し、いざという時に確実に使えるようにしておくことが非常に重要です。

パソコンのエクセルで簡単にできる、賞味期限・消費期限管理表の作成方法を紹介します。



2.賞味期限・消費期限管理表をエクセルで作成

※ 紹介するエクセルのバージョンは2019です。バージョンによっては操作方法が若干異なる場合がありますのでご了承ください。

2-1.エクセルのシートの2行目に以下のように入力します。
セルの色は付けなくてもかまいません。
 



エクセル備蓄品賞味期限管理

2-2.C1セルに 「=TODAY()」 と入力します。本日の日付を表示する計算式です。
 

エクセル備蓄品賞味期限管理

2-3.表中に分類、備蓄品名、使用期限(賞味期限・消費期限)を入力します。
備蓄品の使用期限の表示が年月までの場合はその月末日を入力します。
例) 2027年7月 → 2027/07/31
 

エクセル備蓄品賞味期限管理

2-4.備蓄品リストの入力が終了したら、C列の使用期限のセルに、「使用期限の1か月前を過ぎたらセルが黄色に変わる」、「使用期限を過ぎたらセルが赤色に変わる」という書式を以下の方法で設定します。


10年保存クッキー

グリーンケミー 10年保存クッキ


2-5.まず、C3~C9セルを範囲選択し、次に「条件付き書式」をクリックし、「セルの強調表示ルール(H)」に続けて「指定の範囲内(B)」にカーソルを合わせます。
 

エクセル備蓄品賞味期限管理

2-6.そのまま「指定の範囲内(B)」をクリックすると、下のウィンドウが現れます。
表示される日付は下の図と異なっていると思いますが問題ありません。
 

エクセル備蓄品賞味期限管理

  ウィンドウの一番左の枡は、黒く反転しているままの状態でC1セルをクリックします。
すると、「=$C$1」と表示されるので、=$C$1に続けて半角で「-1」と入力し「=$C$1-1」の式を完成させます。

中央の枡にはC3セルの日付が表示されていますが、これを半角の「1」に変更します。
一番右の枡はそのままの「濃い赤の文字、明るい赤の背景」にしておきます。
 

エクセル備蓄品賞味期限管理

  上記の入力が終了したら、「OK」 をクリックします。ウィンドウが閉じます。


温めずにおいしい野菜カレー

LLヒートレスカレー 温めずにおいしい野菜カレー


2-7.同様に2番目の書式を設定します。

使用期限の1カ月前を過ぎたらセルの色と文字の色が変わる設定です。

2-6.と同様にそのままC3~C9セルが選択されている状態で、「条件付き書式」から「セルの強調表示ルール(H)」→「指定の範囲内(B)」をクリックし、現れたウィンドウの一番左の枡にはC1セルをクリックして「=$C$1」と入力、中央の枡には 「 =EDATE($C$1,1) 」と入力します。
C1セルの日付から1か月後の日付になります。( 注1 )

一番右の枡はドロップダウンリスト ドロップボタン から「濃い黄色の文字、黄色の背景」を選択します。
 

エクセル備蓄品賞味期限管理

  上の画面の入力が終わったら 「OK」 をクリックしてウィンドウを閉じます。


( 注1 )
ちょうど2か月前になったら色を変える場合は、「 =EDATE($C$1,2) 」 と入力します。

  備蓄品の使用期限がC1セルに表示されている「本日」から1か月以内になると「使用期限のセル」の文字が濃い黄色、背景色が黄色になり、
(飲料水ペットボトルの使用期限を 2023/09/30 にした例)
 

エクセル備蓄品賞味期限管理


C1セルに表示されている「本日」が、備蓄品の使用期限を過ぎると「使用期限のセル」が濃い赤の文字、明るい赤の背景になり、
(飲料水ペットボトルの使用期限を 2023/08/31 にした例)
 

エクセル備蓄品賞味期限管理

  備蓄品の使用期限(賞味期限・消費期限)の管理がとてもしやすくなります。
 


富士山麓の保存水 2L 6本入り

富士山麓の保存水 2L 6本入り


3.「期限が1週間前」の条件付き書式の追加

  下の期限管理表に期限の1週間前になったら「黄色の背景色に文字色が赤」という条件付き書式を追加してみます。

3-1.表のC3セルとC4セルは使用期限が本日を示すC1セルから1か月以内の日付に変更してあります。
セルが( 使用期限1か月前過ぎ )を示す「濃い黄色の文字、黄色の背景」になっています。
 

エクセル備蓄品賞味期限管理

3-2.追加の条件付き書式を設定する範囲(図ではC3~C9セル)を選択してから 「条件付き書式」をクリックし、「セルの強調表示ルール(H)」から「指定の範囲内(B)」をクリック。
 

エクセル備蓄品賞味期限管理

3-3.現れたウインドウの左の桝が反転されている状態でC1セルをクリックすると「=$C$1」と表示されます。
 

エクセル備蓄品賞味期限管理

3-4.続けて中央の桝の日付を消去してからC1セルをクリックし、その後に半角で「+7」と入力し、「=$C$1+7」の数式を入力します。
 

エクセル備蓄品賞味期限管理

3-5.続けて「濃い赤の文字、明るい赤の背景」と表示されている右の桝の ドロップボタン をクリックし、別の書式「ユーザー設定の書式」を設定します。
 

エクセル備蓄品賞味期限管理

尾西の携帯おにぎり 五目おこわ

尾西の携帯おにぎり 五目おこわ


3-6.「ユーザー設定の書式」をクリックし、「フォント」タブから色(C)を赤に指定、「塗りつぶし」タブの背景色(C)から黄色を選択し、「OK」をクリックします。
 

エクセル備蓄品賞味期限管理

 

エクセル備蓄品賞味期限管理

  下の画面になったら「OK]をクリックします。
 

エクセル備蓄品賞味期限管理

  C3セルの使用期限の日付 「2023/09/06」 がC3セルの本日より一週間以内なので黄色い背景の赤文字に変わりました。
 

エクセル備蓄品賞味期限管理




4.条件付き書式の優先順位設定

4-1.条件付き書式のルールが増えると、条件が競合する場合が生じます。
その場合には条件付き書式ルールの優先順位の設定を行います。

  前述までの条件付き書式ルールの設定状況を見てみます。

  C3セルを選択し、条件付き書式から「ルールの管理(R)」にポインタを合わせると以下の画面になります。
 

エクセル備蓄品賞味期限管理

  そのまま、ルールの管理(R)...をクリックすると上から
     期限1週間前 
     期限1か月前 
     期限過ぎ   
の条件付き書式ルールが並んでいるのが分かります。
 

エクセル備蓄品賞味期限管理

4-2.一番上の「期限1週間前」という条件の期間は「期限1か月前」に含まれる期間であり、条件が競合していると言えます。

  上の場合は「期限1週間前」という条件を3つの条件の中で最後に設定したので一番上に表示され、優先順位も1番になっています。条件付き書式ルールの優先順位は上からの順位になります。
そのため、条件付き書式ルールの設定順序によっては優先順位が変わり、条件付き書式ルールが無視される場合があります。

  試しに期限2か月前になったら「濃い緑の文字、緑の背景」になる条件付き書式ルールを追加してみます。
C3セルからC9セルの範囲を選択し、「条件付き書式」→「セルの強調表示ルール(H)」→「指定の範囲内(B)」の画面で以下の様に条件付き書式ルールを設定してみます。
 

エクセル備蓄品賞味期限管理

  すると、「本日」のC1セルの日付から2か月先までの期間に含まれるC3~C5セルが全て「濃い緑の文字、緑の背景」に変わりました。
 

エクセル備蓄品賞味期限管理

4-3.ここで改めてC3セルからC9セルを選択し、「条件付き書式」→「ルールの管理(R)」をクリックすると、最後に設定した「期限2か月前」の条件付き書式ルールが一番上に表示されています。
この条件が最優先されるため、「2か月前」の期間に含まれる「1か月前」と「1週間前」の条件が無視されることになります。
 

エクセル備蓄品賞味期限管理

井村屋えいようかん

井村屋えいようかん


4-4.優先順位を変えるために以下の操作をします。

  上の画面で、一番上の期限2か月前の条件付き書式ルールを選択し、右上の ドロップボタン を3回クリックして順位を一番下まで下げます。
ドロップボタン ボタンは優先順位を変更する時に使います。
下の画面になったら「OK」をクリックして終了です。
 

エクセル備蓄品賞味期限管理

  期限1週間前と期限1か月前の条件付き書式ルールも表示されるようになりました。
 

エクセル備蓄品賞味期限管理

4-5.条件付き書式の設定をしたけれど、思うように表示されない、また、条件付き書式ルールを追加したら以前の条件付き書式ルールが表示されなくなった、というような場合は優先順位を見直してみましょう。

4-6.期限2カ月前の条件付き書式が不要になった場合は、以下の画面で最下段のルールを選択し、「削除 ルールの削除(D)」ボタンを押します。
 

エクセル備蓄品賞味期限管理




5.備蓄品項目の追加・削除と条件付き書式ルールのメンテナンス

5-1.期限管理表を使っている間に備蓄品の項目が増減すると適用先範囲が幾種類も表示されるケースが出てきます。

  行の挿入・行の削除、行の追加の場合、その方法によっては条件付き書式ルールの適用先範囲が変更されることがあります。

5-2.下の表は前記の表に5行目(4行目を下にシフトしてコピー挿入後内容を変更)と9行目(条件付き書式が設定されていない行から挿入コピー)を追加したものです。
分かり易いように水色にしています。
 

エクセル備蓄品賞味期限管理

5-3.C3セルからC11セルを範囲選択して「条件付き書式」から「ルールの管理(R)」を見ると、 適用先が分断されています。
 

エクセル備蓄品賞味期限管理

  しかもC9セルには条件付き書式ルールは何も適用されていません。
 

エクセル備蓄品賞味期限管理

サバイバルフーズ 大缶単品

サバイバルフーズ 大缶単品 クラッカー


5-4.そこで、管理表の適用先を整理します。

5-5.条件付き書式ルールの適用範囲が広い場合は一番上の「書式ルールの表示(S)」の「現在の選択範囲」を「このワークシート」に変更します。

  一番上の書式ルールの適用先を「C3セルからC11セル」に修正します。
直接入力またはC3セルからC11セルをドラッグします。
 

エクセル備蓄品賞味期限管理

  同様に2番目から4番目の書式ルールの適用先範囲も変更します。
1番目の適用先のコピー貼り付けまたは直接入力でも変更できます。
 

エクセル備蓄品賞味期限管理

  ◎ 列全体への書式の設定
データが大量にあり、データの追加、入れ替えなどが頻繁に生じる場合は列全体への書式設定が便利です。
上の書式ルールの管理で、適用先を「 =$C:$C 」 と設定します。
最初に範囲を選択する時に、列の「C」を選択して条件付き書式を設定していただいてもOKです。
(C1セルにも条件付き書式が適用されます。)
 

エクセル備蓄品賞味期限管理

  行を追加挿入したり、上下に移動しても適用先の範囲は変わりません。
ただし、書式を設定していない範囲外のセルを挿入コピーした場合は適用範囲が分断されます。

5-6.重複している書式ルールや不要な書式ルールがある場合は(4-6)と同じ方法で削除します。

  不要になった書式ルールを選択して「削除 ルールの削除(D)」をクリックします。
 

エクセル備蓄品賞味期限管理

  重複している書式ルールが多い場合は右のスライドバーを押し下げて最下段から削除していくと比較的簡単に削除できます。

  重複している書式ルールの削除が全て終わったら「OK」をクリックして終了ですが、終了前に優先順位も再確認しておきましょう。




6.賞味期限、消費期限、使用期限、使用推奨期限 について

賞味期限:
「定められた方法により保存した場合において、期待されるすべての品質の保持が十分に可能であると認められる期限を示す年月日のこと」。
ただし、当該期限を超えた場合であっても、これらの品質が保持されていることがあります。このため、「賞味期限」を過ぎた食品であっても、必ずしもすぐに食べられなくなるわけではありませんので、それぞれの食品が食べられるかどうかについては消費者が個別に判断する必要があります。
製造日から賞味期限までの期間が3箇月を超えるものについては、「年月」で表示することが認められています。

消費期限:
「定められた方法により保存した場合において、腐敗、変敗その他の品質(状態)の劣化に伴い安全性を欠くこととなるおそれがないと認められる期限を示す年月日のこと」
「消費期限」を過ぎた食品は食べないようにしてください。

一般的に、品質(状態)が急速に劣化する食品には、安全性を欠くこととなるおそれがない期限である「消費期限」を、それ以外の(比較的品質が劣化しにくい)食品には、おいしく食べることができる期限である「賞味期限」を表示すべきと考えられます。

使用期限:
時間の経過と共に品質が劣化する品物(医薬品、プリンタのインク、消火器など)に設定されている。
医薬品は有効期間と使用期限が使い分けられている。
(プリンタのインク(ブラザー)は有効期限でした。海外向け表示かもしれませんね)

使用推奨期限:
「期間内に使用を開始すれば電池は正常に作動し、日本工業規格(JIS)で定められた持続時間などの電池性能を満たす」期限。(ただし未使用の状態で保管(保存)された場合)
「有効期限」は海外向けで、電池が使えなくなる期限の目安としての意味で表示される。


観音温泉水 20L 1箱

観音温泉水 20L 1箱


7.期限管理表のその他の使い方

災害対策備蓄品に手回し充電ラジオがありますが、商品によっては年に何回か手回しをして充電しないとバッテリーが劣化する商品が有ります。
このような商品の充電時期を半年に1回または4か月に1回のように決めて期限管理表に記載しておくとバッテリーの劣化を防ぐのに役立ち便利です。
期限が来て充電をしたら次の充電時期を記載しておきます。

同様に普段使わないバッテリー式の電動工具、旅行でしか使わないデジタルカメラ、ハンディ掃除機、充電式のアウトドア用品などをリストアップし、充電時期を決めて記載しておくとタイミングよく充電できて便利です。


缶deボローニャ 3種類 6缶セット

缶deボローニャ 3種類 6缶セット


8.参考 「アキモト・リマインダーサービス」

パンの缶詰「PANCAN」のメーカー、パン・アキモトで「アキモト・リマインダーサービス」というサービスを実施しています。
このサービスでは、パン・アキモトの製品のみでなく、他社製品でも登録をするとメールで「賞味期限 1ヶ月前 」の連絡をしてくれます。
   ↓ ↓ ↓
「アキモト・リマインダーサービス」