Zoranta読みもの
CertAlert

CertAlert › 読みもの

資格管理表をエクセルで作る — 列・残日数の式・色分けの手順

資格管理をエクセルでするなら、1行に1つの資格を書き、有効期限の列から「残り何日か」と「状態」を式で出し、状態に応じて行に色を付けます。開いたときに赤い行があれば動く、というだけの表にすると続きます。

下の列の例と式は、そのままエクセルにもGoogleスプレッドシートにも貼れます。自分ひとりの資格でも、家族や小さなチームの分でも同じ作り方です。

革の書類入れに並んだ資格証と、それを撮影するスマートフォン

資格管理表(エクセル)の列の例

A列から順に、次のように見出しを作ります。式が入るのは「残日数」と「状態」と「次の期限の案」の3列だけで、残りは手で書く列です。

列見出し入れるもの
A資格名正式な名前
B発行元協会・都道府県など
C登録番号免状・登録証の番号
D取得日日付
E有効期限日付。期限がなければ空けておく
F残日数式(下で説明)
G状態式(下で説明)
H更新の周期(年)数字。周期がなければ空けておく
I次の期限の案式(下で説明)
J更新に要るもの講習・書類・写真など
K更新費用円
Lメモ前回の更新で困ったことなど

日付の列(D・E)は、セルの書式を「日付」にしておきます。文字として入っていると式が動きません。

CertAlert

CertAlert は表を開かなくても、期限の何日前に知らせるかを選んで通知を置け、更新を記録すると同じ周期で次の有効期限を提案します。 Google Play で手に入れよう

残日数と状態を出す式

2行目が最初の資格だとして、F2とG2に次の式を入れ、下の行へコピーします。

セル式意味
F2(残日数)=IF(E2="","",E2-TODAY())期限までの日数。期限が空なら空のまま
G2(状態)=IF(E2="","期限なし",IF(F2<0,"期限切れ",IF(F2<=30,"30日以内",IF(F2<=90,"90日以内","有効"))))残日数で4つと「期限なし」に分ける

F列の書式は「標準」か「数値」にします。日付の書式のままだと、残日数が日付のように表示されます。TODAY関数はファイルを開くたびに今日の日付で計算し直すので、毎日書き換える必要はありません。

30日・90日の区切りは例です。講習の予約に時間のかかる資格が多いなら、60日・180日のように広げてもかまいません。区切りの考え方は資格の期限管理のやり方の「何日前に動くかの目安」を参考にしてください。

条件付き書式で行に色を付ける

状態の文字だけでは見落とすので、行ごと色を付けます。エクセルでの手順です。

  1. 色を付けたい範囲(例: A2からL100)を選ぶ。
  2. 「ホーム」→「条件付き書式」→「新しいルール」を開く。
  3. 「数式を使用して、書式設定するセルを決定」を選ぶ。
  4. 数式に =$G2="期限切れ" と入れ、「書式」で塗りつぶしを赤にする。
  5. 同じ手順で =$G2="30日以内" をオレンジ、=$G2="90日以内" を水色のルールとして足す。
  6. 「ルールの管理」で、期限切れ → 30日以内 → 90日以内 の順に並んでいるか確かめる。

G列の前に $ を付けるのがポイントです。$ が無いと、列ごとに見る場所がずれて、行の一部にしか色が付きません。Googleスプレッドシートでは「表示形式」→「条件付き書式」→「カスタム数式」で同じ式が使えます。

最後に、F列(残日数)で小さい順に並べ替えると、期限の近い資格が上に集まります。期限切れの行はマイナスの日数になるので、いちばん上に来ます。

更新したら次の期限を出す(EDATE関数)

決まった周期で更新する資格は、H列に周期の年数を入れておくと、次の有効期限の案を式で出せます。

セル式意味
I2(次の期限の案)=IF(OR(E2="",H2=""),"",EDATE(E2,H2*12))今の有効期限から周期の年数だけ先の日付

I列の書式は「日付」にします。EDATE関数は「何か月後」を出す関数なので、年数に12を掛けています。月末の日付から計算すると、月の日数の違いで日付が前にずれることがあります。

これはあくまで案です。資格によっては、新しい期限が「更新した日から」数えるものや、「講習を受けた日から」数えるもの、誕生日を基準にするものがあります。更新が済んだら、新しい証や通知に書かれた日付でE列を書き換え、案と違っていればそれを正とします。

エクセル管理で困りやすいこと

会社で社員の資格をまとめて管理する場合は、氏名と所属の列を左に足し、資格名のかわりに「氏名+資格名」で1行にすると同じ式が使えます。

CertAlert

表の代わりにスマホで知らせてもらう

CertAlert は資格を期限の近さで赤・オレンジ・青・緑に色分けして並べ、更新費用と資格証の写真も一緒に残せる資格の期限管理アプリです。

Google Play で手に入れよう

iPhone 版は準備中です

ほかの記事

記事の一覧へ

このページは CertAlert の開発元 Zoranta が書いています。