小さな会社や個人では、アプリや専用ソフトを使って商品管理とかはコストが見合わず、エクセルで商品管理していませんか?
このエクセルによる商品管理でリストの中にある期限切れ商品が気づかずに放置されて困ったことありませんか?
エクセルファイルを開いたら自動的に通知させれば、簡単に解決しますよ!
方法は簡単で、エクセル関数とエクセルVBAを組み合わせて、ユーザーフォーム上に簡単なお知らせ機能を持たせるだけで完成しますので、作り方をご紹介します。
エクセル関数の設定
“とりっぷぼうる”サイト内で紹介しているフリーパスも、全てが通年販売だと一度掲載すればそのままでいいのですが、当然ながら期間限定フリーパスが圧倒的に多いです。
その為に期限切れになったら削除しないといけないので、サイト内で管理は難しいのでエクセルにリストを作成して管理しています。
見ての通り、実際にこのような単純なリストで管理しています。
それではまず始めにエクセル関数を使って、残り何日有効なのかが分かるようにしましょう。
TODAY関数で本日の日付を自動取得
今日の日付は当然ながら日々変わっていくので、わざわざ毎回手入力するなんて面倒です。
そこで、エクセルを開いたら今日の日付を自動的に取得して表示させる関数が「TODAY関数」です。
上記画像にTODAY関数はセルF2に記入しています。
このTODAY関数を私自身は様々なエクセルファイルで使用してるぐらい、使用頻度の高いエクセル関数と言ってもいいと思います。
しかも使い方もすごく簡単でセルF2にはこのように入力しているだけです。
=TODAY()
それでは次に、残り何日なのかが分かるように計算式を設定しましょう。
期限終了日から今日の日付を引いて残数表示
上記画像に計算式はD列に記入しています。
ごくごく普通の引き算なので、終了日から今日の日付を引くと残り日数が表示され、例えばセルD2には下記のように数式を入れると・・・
=C2-F1
となりますが、あまりにもこのままでは単純すぎて、C列が空欄(まだリストにデータが無い状態)だと、エラーになり#####と表示されてしまいます。
このままでは並べ替えなどができないので、空欄の時は「0」と表示されるようにIF関数を使いましょう。
=IF(C2=””,0,C2-F1)
これで、フリーパス(商品)が未登録の時は「0」となり、エラー表示が無くなり見た目もスッキリしますね。
この式をD列の3行目以降にも入力すれば完成ですが、ここでの場合は200行目まで入力してあれば、問題ないと思って入れてあります。
このIF関数はとても便利で条件に応じて結果を自由に変えることができるので、絶対にマスターしなくちゃいけないエクセル関数です。
エクセル関数での準備はこれでおしまいなので、残りは全てエクセルVBAで作業となります。
ワークシート上の見栄えなどの書式などは、ここでは省略します。
エクセルVBAの設定
ここで行うエクセルVBAを使わないで毎回の手作業であれば、特に必要が無くなってしまうのですが、エクセルVBAでの設定を行うと下記手順が全て自動化されますので、エクセルファイルを開いただけで完成します。
- リストの並べ替えが自動化され期限切れに近い順に表示されます
- 期限切れになったリスト見つけて文字色を赤字に変えて目立たせる
この2つの作業が一瞬で行われるのでとても便利なので、下記の手順で設定していきましょう。
並べ替えVBAコード
エクセルファイルを開くと同時に様々な作業を行うには、VBA画面のThisWorkbookに自動化させたい作業1,2の順番にコードを書いて行けば完成します。
今回は、分かりやすくするために最初に、作業1と作業2を別々に分けてご紹介して最後に1つにまとめたVBAコードをご紹介します。
まず最初に、並べ替えVBAコードを記述するとこにより商品リストの終了日が古い順(小さい)に並べ替えることができます。
先ほどご案内した通り、ここでは200行目までとします!
Private Sub Workbook_Open()
Range(“A2:C200”).Sort Key1:=Range(“C2”), Order1:=xlAscending
End Sub
- エクセルファイルを開いたら
- 範囲内(セルA2:C200)をC列の値が小さい順に並べ替える
- マクロ記録終了
このままでも、エクセルファイルを開くと自動的に期限切れに近くなってきた順に上から表示されるので、手動で並べ替えを行うよりは確実になると思います。
ただ、せっかくなので目立つように期限切れになった時には、文字色を赤字にして目立たせましょう。
参考までに、エクセルVBAでの並べ替え項目は3つまでとなっています。
期限切れリストを見つけたら赤文字
先ほど並べ替えを行うVBAコードを書いた次の行に、残日数(期限)が切れたら(マイナス表示になったら)文字色を赤に表示するというVBAコードを書き足します。
For i = 2 To 200
If Cells(i, 4) <= 0 Then
Cells(i, 4).Font.ColorIndex = 3
Else
Cells(i, 4).Font.ColorIndex = 1
End If
Next i
- 繰り返し処理の変数「i」を宣言して変数は2行目~200行目にする
- もしセル(i行目の4列目)が0よりも小さかったら(マイナス)
- セル(i行目の4列目)の文字色を色コード3にする(赤色)
- そうでなかったら(大きかったら)
- セル(i行目の4列目)の文字色を色コード1にする(黒色)
- IFステートメント終了
- 繰り返す(200行目まで)
このようにVBAコードを書くと、セルD2がマイナスなら赤文字、次にセルD3をチェック、次にセルD4をチェックと繰り返し200行目(セルD200)まで繰り返しチェックしてくれます。
完成したVBAコード
下記が1,2を組み合わせて完成したコードを、ThisWorkbookに書いています。
Private Sub Workbook_Open()
Range(“A2:C200”).Sort Key1:=Range(“C3”), Order1:=xlAscending
For i = 2 To 200
If Cells(i, 4) <= 0 Then
Cells(i, 4).Font.ColorIndex = 3
Else
Cells(i, 4).Font.ColorIndex = 1
End If
Next i
End Sub
いかがでしたか?そんなに難しくなかったでしょ。
VBAコードは自由にコピペして、自分なりにアレンジして使ってもらって構いませんよ。
関連記事
-
日付・時刻関数
エクセルで昭和・平成・令和を西暦に変換するフォームを作成方法
昭和●●年って西暦で何年?調べるの結構面倒だと思ったことありませんか? 令和の時代になり和暦を使わずに西暦を基本に使うようになっていますが、簡単に変換できるフォ… -
データの操作
エクセルVBAでシートの移動と同時に表のデータの並べ替えをする
成績表やデータ表などに付き物の並べ替え作業って地味に面倒だと思ったことありませんか? 優先順位をつけて並べ替える時に、同時にエクセルシートも切替出来たら作業が… -
ファイルの操作
VBAで1クリックで上書き保存してエクセルファイルを閉じる方法
Excelを使っていて作業が終わったら、上書き保存ボタンを押してファイルを閉じるのが面倒だと思ったことありませんか? たった1クリック1秒でできたら作業効率がアップ… -
ラベル
VLOOKUPをVBAラベルを活用してデータ検索・修正をする方法
【ラベル(Label)にVBAコードを記述する】 ボウリング成績表でも使っているユーザーフォームに配置した文字を表示するための「ラベル(Label)」にも、実はVBAコードを… -
コマンドボタン
コマンドボタンの色をクリックするたびに黒色と赤色を入れ替える方法
ユーザーフォームに配置したコマンドボタンの文字色を条件に応じて変更したいと思いませんか? Excel-VBAのForeColorプロパティとStaticステートメントを使うことで、コ… -
セルの操作
エクセルVBAで複数セルを一括で結合・解除を繰り返し処理する方法
エクセルの表を印刷するには見栄えの為にセルの結合を行ったら、並べ替えをするとエラーになって困ったことありませんか? Mergeメソッドを書き加えれば、自動的にセル… -
ユーザーフォーム
Excel-VBAタイマー機能で数秒後にユーザーフォームを表示させる方法
エクセルを使って成績などを入力して作成した成績表で、入力漏れがなかったかどうかなどをチェックするのに数秒間だけ指定したページや範囲を表示させたいと思ったこと… -
データの操作
エクセルVBAで不要な行や列を非表示ができない時の対処方法と順番確認
データ抽出などで不要な行や列を自動的に非表示にできるExvel-VBAのHiddenプロパティですが、コードは正しく書かれているのにコマンドボタンを押すとエラーになったこと… -
エクセル関数
エクセル関数とVBAで和暦西暦対応カレンダー作成
エクセル関数を基本として作成し、ユーザーフォームで日付選択ができるようにすることで複雑なマクロが分からなくても、自由にカスタマイズして自分のエクセルに組み込…
紅葉スポット
-
縁起だるま発祥の少林山達磨寺境内の真っ赤に染まる紅葉景色へ
【紅葉見頃】11月下旬~12月上旬 高崎市内循環バス「ぐるりん」バスのフリーエリア内にあるので、縁起だるま発祥の少林山達磨寺へ紅葉の時期になったら訪れてみてはい… -
日光穴場!中禅寺湖展望台へ紅葉の中禅寺湖からバスで滞在時間30分
【紅葉見頃】10月中旬~11月上旬 紅葉の見頃となる10月中旬~11月上旬になると、いろは坂から延々華厳の滝や竜頭の滝までとにかく大渋滞して混雑が当たり前ですが、い… -
古峯神社の天狗の御朱印と紅葉名所へ鹿沼駅からバスで行く
御朱印を集める趣味が流行っていますが、ここ古峯神社の御朱印は天狗をイメージしたデザインで何種類あるかわかないぐらい様々あります。 そして、紅葉見頃となる10月中… -
紅葉の寸又峡コバルトブルーの夢の吊橋へ滞在時間2時間
サンドウィッチマンとゲストがバスで旅するバスサンドの放送を見て、ビックリするほど綺麗なコバルトブルーの湖にかかる吊り橋がとても気になったので、夏に青春18きっ… -
伊香保温泉365段の石段の先に紅葉ライトアップされた河鹿橋絶景へ
石段と温泉街で古くから観光客が多く訪れる伊香保温泉には、群馬県の紅葉スポット人気1位・全国でもトップクラスの紅葉スポットがあります。 石段をゆっくりと登ってい… -
塩原温泉・紅の吊橋の紅葉絶景へ那須塩原駅や黒磯駅からバスで行く
【紅葉見頃】10月下旬~11月中旬 塩原温泉郷と言えば、箒川(ほうきがわ)沿いに温泉旅館が点在している首都圏からも近い温泉街ですが、紅葉の時期になるとカエデやも…