エクセル関数とVBAを組み合わせて商品期限切れ通知を表示する方法

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

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

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

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

エクセル関数の設定

“とりっぷぼうる”サイト内で紹介しているフリーパスも、全てが通年販売だと一度掲載すればそのままでいいのですが、当然ながら期間限定フリーパスが圧倒的に多いです。

その為に期限切れになったら削除しないといけないので、サイト内で管理は難しいのでエクセルにリストを作成して管理しています。

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

波乗りアヒル

それではまず始めにエクセル関数を使って、残り何日有効なのかが分かるようにしましょう。

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

今日の日付は当然ながら日々変わっていくので、わざわざ毎回手入力するなんて面倒です。

そこで、エクセルを開いたら今日の日付を自動的に取得して表示させる関数が「TODAY関数」です。

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

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

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

それでは次に、残り何日なのかが分かるように計算式を設定しましょう。

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

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

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

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

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

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

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

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

使用頻度の高いIF関数

もし～だったらのエクセルIF関数はORやANDも併せて使用頻度トップ エクセルで条件に応じて答えを振り分ける必要は必ずあるので、使えるようになりたいと思っていませんか？ 使用頻度が高い10種類のひとつ、IF関数をマスターすれば無駄な…

エクセル関数での準備はこれでおしまいなので、残りは全てエクセルVBAで作業となります。

ワークシート上の見栄えなどの書式などは、ここでは省略します。

エクセルVBAの設定

ここで行うエクセルVBAを使わないで毎回の手作業であれば、特に必要が無くなってしまうのですが、エクセルVBAでの設定を行うと下記手順が全て自動化されますので、エクセルファイルを開いただけで完成します。

1. リストの並べ替えが自動化され期限切れに近い順に表示されます
2. 期限切れになったリスト見つけて文字色を赤字に変えて目立たせる

この2つの作業が一瞬で行われるのでとても便利なので、下記の手順で設定していきましょう。

並べ替えVBAコード

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

波乗りアヒル

今回は、分かりやすくするために最初に、作業１と作業２を別々に分けてご紹介して最後に１つにまとめたVBAコードをご紹介します。

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

先ほどご案内した通り、ここでは200行目までとします！

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

ただ、せっかくなので目立つように期限切れになった時には、文字色を赤字にして目立たせましょう。

参考までに、エクセルVBAでの並べ替え項目は3つまでとなっています。

4つの項目を並べ替える方法

Key4は使えないけど4つ以上項目の並べ替えをVBAコードでやる方法 エクセルVBAを使ってデータの並べ替えを行うのにSortメソッドを使うのですが、４つ目の条件までの並べ替えををVBAコードを使ってやってみたら、エラーになった経験あり…

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

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

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

完成したVBAコード

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

波乗りアヒル

いかがでしたか？そんなに難しくなかったでしょ。
VBAコードは自由にコピペして、自分なりにアレンジして使ってもらって構いませんよ。

関連記事