エクセルで備蓄品の賞味期限・消費期限管理

目  次

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

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

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

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

2.エクセルで備蓄品の賞味期限・消費期限管理表作成

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

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

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

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

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

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

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

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

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

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

6.そのまま「指定の範囲内(B)」をクリックすると、下のウィンドウが現れます。
現れたウィンドウの一番左の枡は、黒く反転しているままの状態でC1セルをクリックします。
すると、「=$C$1」と表示されるので、=$C$1に続けて「-1」と入力し、「=$C$1-1」の式を完成させます。

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

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

     ↓  ↓

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

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

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

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

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

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


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

8.C3セルの書式を「書式のコピー/貼り付け」コマンドで、C列のセル(サンプルの画面ではC4セルからC9セルまで)にコピーして完成です。

「書式のコピー/貼り付け」の方法は、C3セルをクリック後、画面左上にある ブラシのような形をした「書式のコピー/貼り付け」のアイコン(ペイントブラシ) 書式のコピー/貼り付け  をダブルクリックし、C4セルからC9セルまでドラッグします。


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

  ドラッグし終わったら再度「書式のコピー/貼り付け」アイコン 書式のコピー/貼り付け をクリックして終了です。

  1つのセルにのみ書式のコピーをする場合は、コピー元のセルをクリック後、書式のコピー/貼り付け をクリックし、続けてコピー先のセルをクリックして終了です。

  備蓄品の使用期限がC1セルに表示されている「本日」から約1か月以内になると「使用期限のセル」の色がオレンジになり、
(飲料水ペットボトルの使用期限を 2018/09/30 にした例)

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


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

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

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


霧島湧水 志布志の自然水

保存水 霧島湧水 志布志の自然水 災害備蓄水2LPETx2ケース(合計12本)


3.「期限が1週間前」の条件付き書式の追加と条件付き書式の優先順位設定

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

1.表のC3セルとC4セルは使用期限が本日を示すC1セルから1か月以内の日付を設定してあります。

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

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

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

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

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

4.続けて中央の枠の文字をドラッグして反転させてからC1セルをクリックし、その後に「+7」と入力し、「=$C$1+7」の数式を入力します。

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

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

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

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

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

     ↓  ↓

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

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

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

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

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



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

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

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

  C3セルを選択し、条件付き書式から「ルールの管理(R)」をクリックすると、

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

  上から
     期限1週間前 
     期限1か月前 
     期限過ぎ   
の条件付き書式ルールが並んでいるのが分かります。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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




5.条件付き書式ルールのメンテナンス

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

  行の挿入・行の削除の場合は条件付き書式ルールの適用先範囲はスムーズに増減されますが、行をコピーするとコピー先のセルにはコピーされた行のセルのみの適用先範囲が表示され、前後の行の適用先範囲は分断されるようになります。

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

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

3.C3セルからC11セルを範囲選択して「条件付き書式」から「ルールの管理(R)」を見ると、 適用先がバラバラになっていて、しかもC9セルには適用されていません。

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

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

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

  一番上の書式ルールの適用先をドラッグして反転させ、

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

  適用先範囲(今回の場合はC3セルからC11セル)をドラッグします。

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

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

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

6.必要な書式ルールの適用先範囲の修正が終わったら、重複している書式ルールを削除します。

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

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

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

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

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

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



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

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

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

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

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

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


観音温泉水 20L

観音温泉水 20L(1箱)


7.エクセル期限管理表のその他の使い方

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

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


アキモトPANCAN缶入りソフトパン<3種6缶セット>

パンの缶詰パン・アキモトPANCAN缶入りソフトパン<3種6缶セット>


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

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