エクセルを開くと期限切れ商品リストを自動通知する方法

スポンサーリンク

小さな会社や個人では、アプリや専用ソフトを使って商品管理とかはコストが見合わず、エクセルで商品管理していませんか？

このエクセルによる商品管理でリストの中にある期限切れ商品が気づかずに放置されて困ったことありませんか？

エクセルファイルを開いたら自動的に通知させれば、簡単に解決しますよ！

方法は簡単で、エクセル関数とエクセルVBAを組み合わせて、ユーザーフォーム上に簡単なお知らせ機能を持たせるだけで完成しますので、作り方をご紹介します。

エクセル関数を最初に作成

“とりっぷぼうる”サイト内で紹介しているフリーパスも、全てが通年販売ではありません。

当然ながら期間限定のフリーパスが多くありますので、エクセルでフリーパスを管理して期限切れになったら、エクセル上に表示してサイト内を修正するように心がけています。

実際にこのような単純なリストで管理しています。

見ての通り、エクセルVBAでまだなにも設定していないので、登録した順番に入力しただけで、エクセル関数を入れているセルは2か所です。

TODAY関数で本日の日付を自動取得

上記画像にTODAY関数はセルF2に記入しています。

このTODAY関数を私自身は様々なエクセルファイルで使用してるぐらい、使用頻度の高いエクセル関数と言ってもいいと思います。

しかも使い方もすごく簡単でセルF2にはこのように入力しているだけです。

ホントにたったこれだけで自動的に本日の日付を自動的に取得して、入力したセル（ここではセルF2）に表示します。

ちなみに、今日の日付をどこから取得するのかというと、パソコンには時計機能があり、おそらく画面右下に時間と日付が表示されていると思いますが、ここから取得しているんです。

その為にエクセルファイルを開くと自動的に日付を取得して表示できるんですね。

期限終了日から今日の日付を引いて残日数を自動表示

上記画像に計算式はD列に記入しています。

ごくごく普通の引き算なので、終了日から今日の日付を引くと残り日数が表示され、例えばセルD2には下記のように数式を入れると・・・

となりますが、あまりにもこのままでは単純すぎて、C列が空欄（まだリストにデータが無い状態）だと、エラーになり#####と表示されてしまいます。

このままでは並べ替えなどができないので、空欄の時は「0」と表示されるようにIF関数を使いましょう。

これで、フリーパス（商品）が未登録の時は「0」となり、エラー表示が無くなり見た目もスッキリしますね。

この式をD列の3行目以降にも入力すれば完成ですが、ここでの場合は200行目まで入力してあれば、問題ないと思って入れてあります。

このIF関数はとても便利で条件に応じて結果を自由に変えることができるので、絶対にマスターしなくちゃいけないエクセル関数です。

VBA画面に切り替えてVBAコードを書いて作成

ワークシート上の見栄えなどの書式などはここでは無視したら、商品リストとしては完成です。

あとは使い勝手などは、全てエクセルVBAで行うとエクセルファイルを開くと同時に作業が一瞬で終了しますが、準備が大切なので作業の手順を再確認しましょう。

1. 終了日（期限切れ）に近い順に並べ替える
2. 期限切れになったリストの文字色を赤字に変えて目立たせる

ThisWorkbookに並べ替えコードを入力

エクセルファイルを開くと同時に様々な作業を行うには、VBA画面のThisWorkbookに上記１，２の順番にコードを書いて行けば完成します。

まずは、このコードを記述するとこにより商品リストの終了日が古い順（小さい）に並べ替えることができます。

このままでも、エクセルファイルを開くと自動的に期限切れに近くなってきた順に上から表示されるので、手動で並べ替えを行うよりは確実になると思いますが、せっかくなので目立つように期限切れになった時には、文字色を赤字にして目立たせましょう。

期限切れリストを見つけたら文字色を赤字にする

先ほど並べ替えを行うVBAコードを書いた次の行に、残日数（期限）が切れたら（マイナス表示になったら）文字色を赤に辞するというコードを書き足します。

このようなコードで、セルD2がマイナスなら赤文字、次にセルD3をチェック、次にセルD4をチェックと繰り返し200行目（セルD200）まで繰り返しチェックしてくれます。

下記が１，２を組み合わせた完成したコードで、ThisWorkbookに書いています。