資格管理表(エクセル)の列の例
A列から順に、次のように見出しを作ります。式が入るのは「残日数」と「状態」と「次の期限の案」の3列だけで、残りは手で書く列です。
| 列 | 見出し | 入れるもの |
|---|---|---|
| A | 資格名 | 正式な名前 |
| B | 発行元 | 協会・都道府県など |
| C | 登録番号 | 免状・登録証の番号 |
| D | 取得日 | 日付 |
| E | 有効期限 | 日付。期限がなければ空けておく |
| F | 残日数 | 式(下で説明) |
| G | 状態 | 式(下で説明) |
| H | 更新の周期(年) | 数字。周期がなければ空けておく |
| I | 次の期限の案 | 式(下で説明) |
| J | 更新に要るもの | 講習・書類・写真など |
| K | 更新費用 | 円 |
| L | メモ | 前回の更新で困ったことなど |
日付の列(D・E)は、セルの書式を「日付」にしておきます。文字として入っていると式が動きません。
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日のように広げてもかまいません。区切りの考え方は資格の期限管理のやり方の「何日前に動くかの目安」を参考にしてください。
条件付き書式で行に色を付ける
状態の文字だけでは見落とすので、行ごと色を付けます。エクセルでの手順です。
- 色を付けたい範囲(例: A2からL100)を選ぶ。
- 「ホーム」→「条件付き書式」→「新しいルール」を開く。
- 「数式を使用して、書式設定するセルを決定」を選ぶ。
- 数式に =$G2="期限切れ" と入れ、「書式」で塗りつぶしを赤にする。
- 同じ手順で =$G2="30日以内" をオレンジ、=$G2="90日以内" を水色のルールとして足す。
- 「ルールの管理」で、期限切れ → 30日以内 → 90日以内 の順に並んでいるか確かめる。
G列の前に $ を付けるのがポイントです。$ が無いと、列ごとに見る場所がずれて、行の一部にしか色が付きません。Googleスプレッドシートでは「表示形式」→「条件付き書式」→「カスタム数式」で同じ式が使えます。
最後に、F列(残日数)で小さい順に並べ替えると、期限の近い資格が上に集まります。期限切れの行はマイナスの日数になるので、いちばん上に来ます。
更新したら次の期限を出す(EDATE関数)
決まった周期で更新する資格は、H列に周期の年数を入れておくと、次の有効期限の案を式で出せます。
| セル | 式 | 意味 |
|---|---|---|
| I2(次の期限の案) | =IF(OR(E2="",H2=""),"",EDATE(E2,H2*12)) | 今の有効期限から周期の年数だけ先の日付 |
I列の書式は「日付」にします。EDATE関数は「何か月後」を出す関数なので、年数に12を掛けています。月末の日付から計算すると、月の日数の違いで日付が前にずれることがあります。
これはあくまで案です。資格によっては、新しい期限が「更新した日から」数えるものや、「講習を受けた日から」数えるもの、誕生日を基準にするものがあります。更新が済んだら、新しい証や通知に書かれた日付でE列を書き換え、案と違っていればそれを正とします。
エクセル管理で困りやすいこと
- 開かないと気づけない: 表は開いたときにしか教えてくれません。月の初めに開く、とカレンダーに予定を入れておきます。
- ファイルが増える: 会社のパソコンと家のパソコンに別々の表があると、どちらが正しいか分からなくなります。置き場所は1か所にします。
- 写真が入れにくい: 免状や資格証の写真は、表の外のフォルダに資格名で保存し、L列にファイル名を書いておくと探せます。
- 更新費用の合計: K列の下に =SUM(K2:K100) を置くと、全部の資格の更新費用の合計が出ます。年ごとに分けたいなら、更新した年の列を足してSUMIF関数を使います。
会社で社員の資格をまとめて管理する場合は、氏名と所属の列を左に足し、資格名のかわりに「氏名+資格名」で1行にすると同じ式が使えます。
表の代わりにスマホで知らせてもらう
CertAlert は資格を期限の近さで赤・オレンジ・青・緑に色分けして並べ、更新費用と資格証の写真も一緒に残せる資格の期限管理アプリです。
Google Play で手に入れようiPhone 版は準備中です
ほかの記事
- 資格の期限管理のやり方 — 一覧を1つにして、逆算した日に動く資格の期限管理は、有効期限のある資格・免許・証明書を1つの一覧にまとめ、更新に要る準備の長さから逆算した日に通知を置くのが基本。一覧の項目、何日前に動くかの目安、見直しの手順をまとめました。
- 資格の更新を忘れないための方法と、忘れたと気づいたときの動き方資格の更新を忘れる原因は、案内のはがきに頼る・周期が長い・資格が多いの3つ。通知を期限ではなく準備の開始日に置く方法と、更新を忘れたと気づいた日にやることを順に書きました。
- 資格の失効とは — 失効したらどうなるか、戻し方と履歴書の書き方資格の失効とは、更新や届出をしなかったなどの理由で、資格者として名乗る・業務をする権利がなくなること。失効の型、失効したと気づいたときの確かめ方、失効した資格の履歴書への書き方、失効証明書の取り方をまとめました。
- 資格の更新費用は経費になる? 勘定科目・仕訳と、会社員の場合資格の更新費用は、個人事業主なら業務に必要な範囲で必要経費にでき、研修費・支払手数料などの勘定科目で仕訳します。会社員は特定支出控除の対象になることがあります。費用の内訳と記録のしかたも。
このページは CertAlert の開発元 Zoranta が書いています。