スパイス在庫管理の決定版!!自動リマインダー機能つきのエクセル一覧表を作成しました。

スパイス。賞味期限内に使い切れていますか?

下の見本サンプルは私からのプレゼント。「ナツメグ」「2201」みたく、品名と賞味期限4桁を入力するだけで自動管理できるものなので、宜しければお持ち帰りくださいね♡

【関数入力済みのサンプルをここから開くことができます】
200216スパイス在庫管理見本.xlsx

題して『スパイス在庫管理の決定版!!自動リマインダー機能つきのエクセル一覧表』です。

特徴は、
1)品名と賞味期限をベタ打ちするだけで、「早く使いましょう」というスパイスに自動的に色がつく!! 目に見える!!

今が2020年2月。仮サンプルを入力したものを表示してみます。1)賞味期限から1年が経ったもの、2)賞味期限が過ぎて1か月程度のもの、賞味期限がそろそろ近づくもの、4)賞味期限は先のもの、という4パターンの仮の例が、自動的に色分けされて表示されます。

スパイス管理

賞味期限が過ぎたものほど濃い色がついています。黄色は賞味期限間近を教えてくれるリマインダーです。

2)時が経ったら、その時点で色が変わる。
↓上から何か月か経ったら、ほら!!

スパイス管理

日付を自動判定して色が変わってくれるから、「早く使いましょう」というスパイスがリアルタイムで分かります。

* * *

スパイス好きの方々やスパイスモニターの方々にとって現実にありがちなこととして、スパイスを賞味期限内に使い切れていない様子をしばしば個人のブログ等で見かけます。それはありがちなことで、例えばグレナダ料理を作ってみたい人が、メースやオールスパイスを「使いたいから購入した」としても、賞味期限内に残りを全部使えるわけじゃない。それに誰だってそうだと思うんだけど、食べ物を捨てるのは嫌なことだし、スパイスだって賞味期限内に使うほうが美味しいのです。

しかし、毎日メースばっかり使う訳にもいかない。毎日オールスパイスばっかり使う訳にもいかない。・・・と、そうやってスパイスの使用からご無沙汰してしまうほど、商品の賞味期限が近づいてしまうのではないでしょうか。

私がスパイスを使い切れる理由としては、スパイスを使うレシピをたくさん持っているだけではありません。私はこのエクセルを考案して、5年くらいこの方法で「早く使いましょう」というスパイスを、エクセルに自動的にリマインドしてもらっているからです。

つまり、在庫管理システムがあるから在庫管理が出来ているとも言えると思います。

では、以下、楽しい管理エクセルのお見本を披露します♪

* * *

さあ、家にスパイスがたくさんあるとして、なるべく賞味期限内に使っていきたいとして。

◆1)アナログ型で考えやすい対策その1は、賞味期限順に陳列することです。
でも私は苦手。スパイスは使いやすさを優先して陳列したいもの。賞味期限順に陳列しても探しにくいし、元に戻しにくい。とにかく面倒だ。

◆2)アナログ型で考えやすい対策その2は、メモを取ることです。
メモは大変だ。賞味期限順に書いて並べたとしても、途中スパイスが増えたときにそれが崩れる。

◆3)その発想のままデジタルになったのが、エクセルベタ打ちです。
↓こんな感じでしょうか。

スパイス管理

まだ面倒くさい。毎度パウダーとか入力するのも、毎度「年」「月」の文字を入力するのも面倒。

ただし、データタブのフィルター機能(Ctrl+Shift+L)を使えるならぐんと良くなりますよ。
◆4)フィルターで賞味期限を昇順

スパイス管理

ほら、こうして賞味期限順にスパイスを並び替えられるから「ガラムマサラが古くなってる。なんとかしなきゃ。」と思えるでしょう。エクセル機能は、やりたいと思うことは大概出来るから、調べて調べて構築すれば、理想の管理システムが出来て最高に便利になるんです。

私の目標は、賞味期限が近付いたものや過ぎたものを、リアルタイムでエクセルに自動判定させること。

◆5)賞味期限の列に条件つき書式を4つ設定する。

スパイス管理

「条件付き書式」に4項目登録して色分けします。要は、
1)空白セルは白地、
2)賞味期限が1年以上前に過ぎたなら黒地に赤ゴシ
3)賞味期限が1か月以上過ぎたならピンク地に赤
4)賞味期限が前後1か月と至近なら黄色地に茶
としたいです。

◆6)条件つき書式設定のために日付を数値化します。

シートを1つ増やして「計算」という名称の別シートを作ります(最初のシート名は「品名と期限」という名称にしておきます)。
↓「計算シート」

スパイス管理

ピンクのB3にTODAY関数。「=TODAY()」。これで今日の日付が入ります。
色分けは、上記2~4)の3種類+非該当。すなわち4種類の条件書式設定を行うのでEDATE関数(月をずらす関数)登場。
・B4に「=EDATE(B3,-12)」
・B5に「=EDATE(B3,-2)」
・B6に「=EDATE(B3,2)」
これで黄緑のセルに
・B4には「1年前の日付」
・B5に「2か月前の日付」
・B6に「2か月後の日付」が入ります。

スパイス管理

次に紫のセルに年と月だけ表示されるようにします。在庫管理を日のレベルまでやってたら大変なので、年と月だけで十分でしょう。TEXT関数かつ抜粋です。「yy」(y=year)で西暦の下2桁、「mm」(m=month)で月を2桁で、それを連続して「yymm」として4桁数字で表記してくれます。
・C4に「=TEXT(B4,”yymm”)」と入れると、B4が2019/2/16なので、C4には「1902」と表示されます。2019年2月のことです。

C4の「1902」は「せんきゅうひゃくに」ではなくあくまで「2019年2月」。これを、あとは賞味期限の数値より大きいか小さいかを判定させたいのでVALUE関数で絶対値化します。
・D4に「=VALUE(C4)」。これでD4の「1902」は「せんきゅうひゃくに」になりました。

なお、VALUE関数で数値化しなくても目的とする比較はやれるのですが、私はシンプルにしたいのでVALUE関数をよく使い、数値に置き換えています。

さあ、大きな利点なのですが、この「計算シート」は今後動かすことはありません。もうこのシートは使わなくて大丈夫!!

またもうひとつの大きな利点は、B3のTODAY関数はリアルタイムで値が変わってくれるので、B4の「1年前の日付」やB5の「2か月前の日付」も、リアルタイムで自動的に変わってくれるのです。

◆7)「品名と期限」シートに戻り賞味期限列に条件を登録します。

スパイス管理

C列の色と関数設定。
1)賞味期限が1年以上前に過ぎたなら黒地に赤ゴシ
2)賞味期限が1か月以上過ぎたならピンク地に赤
3)賞味期限が前後1か月と至近なら黄色地に茶
4)それ以外は白。
条件付き書式ルールで、適用先を「=$C:$C」とし、4つの設定を作ります。

スパイス管理

↓1)の条件登録

スパイス管理

スパイスの賞味期限が「計算!$D$4」すなわち今日から1年前の年月の値より小さい場合にブラックレッドの色がつきます。

例1)今日が2020年2月なので「計算!$D$4」セル値は1902。ナツメグの賞味期限が2019年1月として年月値は1901。1901<1902なのでブラックレッドの色がつき、賞味期限が1年以上過ぎたと教えてくれる
例2)サフランの賞味期限が2019年3月として年月値は1903。1903<1902ではないのでブラックレッドの色はつかない。確かに賞味期限は1年は過ぎていない。

同様に、2~4)の条件は以下のように登録します。

スパイス管理

スパイス管理

スパイス管理

あとはC列の数値のユーザー定義を「00″年”00″月”」とし、4桁の数字を入力すれば自動的に「00年00月」形式で表示されるようにしておきます。1列目はデータタブからフィルターをつけておきます。

出来たっ\(^o^)/

完成っ\(^o^)/

* * *

では、使ってみます♪ 使い方は超簡単です

家にあるスパイスの品名と使用期限を入力します。コツは、
・賞味期限が202011月24日の場合、「2011」と半角か、「2011」と全角で入力。どちらも入力後Enterを押すと半角になるから全角半角の切り替え不要。自動的に年と月の文字も表示されて色も変わる。
・スパイスの形が複数ある場合はB列に書き分ける。クミンパウダーとクミンシードは、A列にクミンとだけ書き、B列にP(p=powder)かW(w=whole)の一文字だけ入れる。

以下は仮サンプルですが、1)賞味期限から1年が経ったもの、2)賞味期限が過ぎて1か月程度のもの、賞味期限がそろそろ近づくもの、4)賞味期限は先のもの、という4パターンを揃えてみました。

とりあえず15個、ベタ打ちしただけで、ホラ!!スパイスの賞味期限管理が色で一発表示です!!

スパイス管理

古いもの、使わなくちゃいけないものが一目で分かりますね!!

時が経っても、賞味期限が危うくなったものや通り越したものをリアルタイムで自動判定してくれ自動的に色が変わります!!
↓仮に1年時刻を進めた。

スパイス管理

日付順に昇順で並べれば、使っていく優先順位が見えます

スパイス管理

品名順にソートすれば、重複所持が分かります。

スパイス管理

* * *

そもそも、なぜこれを作ったか。

「クミンをそろそろ使い切ろう」と思ったら、クミンを使う料理を美味しく作って食べるのが一番です。しかし、ただスパイスラックにスパイスを置いているだけでは、そのリマインダーに気が付かない。これが問題だったのです。

ここまでのエクセル画像は15品だけ入力した見本サンプルでしたが、最後に実例紹介します。今私の家にある75本(!)のスパイスのエクセルです(25行ずつ3つに横並びにしました)。

スパイス管理

黄色地に茶色文字は賞味期限が至近なものです。2月中に使う予定のスパイスが4つありますね。ひと瓶大さじ3杯程度しか入っていないので余裕です。3月中のが5つ。

色が自動的につくって、ホント見やすいです。

新しいスパイスが我が家にやってくるたびに、このエクセルを開いて入力するから、リアルタイムで「早よ使え~」というスパイスをエクセルが教えてくれる!! だから、スパイス大使としても、十分に良好に、無駄にせずに、賞味期限内に、スパイスを使っていけています。

今回の記事は、私の大好きなエクセル活用テクニックの話でしたが、こういう説明ってパッと見て分かりにくくて、なんかパッと分かりやすく紹介できずにすみませんでした。

でも、私のこういった取り組みが、スパイスをたくさん持つのが好きっていう方のお役に立てれば嬉しいです。何よりスパイスは生産地で人の手を使って丁寧に摘み取っているものだから、スパイス農家の人の努力を無駄にせず、これからもスパイスを尊敬して、美味しいうちに食べていくように努力しようと思います♪

* * *

在庫管理システムがあれば在庫管理が出来るから。

【関数入力済みのサンプルをここから開くことができます】
200216スパイス在庫管理見本.xlsx

上の見本サンプルは私からのプレゼント。「ナツメグ」「2201」みたく、品名と賞味期限4桁を入力するだけで自動管理できるものなので、とにかく、簡単にやれるように作っています。宜しければお持ち帰りくださいね♡

コメントを残す

メールアドレスが公開されることはありません。

本記事、レシピ内容及び写真の著作権はすべて管理人:松本あづさ(プロフィールは≫こちら、連絡方法は≫こちら)にあります。読んでくれた方が実際に作って下されば嬉しいですし、料理の背景やTipsなど、世界の料理情報の共有を目的として、大事に作成しています。
出典URL付記リンクを貼れば小規模な範囲でOKなこと】
・ご自身のサイト、ブログ、FacebookやTwitterにおける情報の小規模な引用や紹介。
事前連絡出典明記をお願いします】
・個人、団体、企業等の活動や出版等で、当サイトおよび当管理人のもつ料理レシピや写真を活用・使用したい場合(料金は≫こちら)。
事後連絡でもよいのでお寄せ下さい(楽しみにしています)】
・学校や大学の宿題や課題で当サイトを活用してくれた児童・生徒・学生さん。
ご遠慮ください】
・大々的なコピペや読み込み、出版物への無断転載。
・商用非商用ならびに営利非営利を問わず、個人、団体、企業等の活動や出版等での無断使用。
※免責事項:上記の引用に基づいて万が一損失・損害がありましても、対応はユーザーご自身の責任において行っていただきますようお願いいたします。