【Excel】DATEDIF関数を使って加入期間に応じてランク分けする方法

会員制のサービスなどでは加入年数、加入月数に応じてランクが上がる制度がある場合があります。Excelでは、DATEDIF関数を使ってそのようなランク分けを自動で行うことができます。

DATEDIF関数

DATEDIF関数とは、指定した単位で2つの日付の期間を計算することができる関数です。

数式	役割
DATEDIF(開始日,終了日,単位)	開始日から終了日までの期間を計算する。単位は、年、月、日などから選択することができる。 年や月の場合、端数は切り捨てで表示される。

単位は以下のように指定することができます。

単位の種類	表示形式
“Y”	年
“M”	月
“D”	日
“YD”	端数の月数（～年～か月の「～か月」の部分）

以下ではDATEDIF関数を使って、加入期間に応じてランク分けを行いたいと思います。今回は加入年数と加入月数でランク分けするパターンを紹介します。

加入年数に応じてランク分けする方法

まず、加入年数とランクを表示させるための「会員リスト」と加入年数と会員ランクの対応を示す「ランク表」を用意します。ランク表の加入年数を参照してランクを判定するので、H列の加入年数は数値データとして記載するようにします。

なお、今回はわかりやすさのため同じシートにランク表を記載していますが、別のシートでも問題ございません。

まず、加入日に応じて現在の加入年数を調べるため、本日の日付をセルに表示させておきます。どこでも良いですが、今回はセルE1に以下のように入力します。

=TODAY()

そうすると本日の日付が表示されます。この値は日付で自動的に変更されるので、都度更新する必要はありません。

次に加入年数を表示させたいセルに以下の数式を入力します。

=DATEDIF(C4,$E$1,”Y”)

数式	役割
DATEDIF(C4,$E$1	セルC4の日付からセルE1の日付までの期間を計算する。この場合、2021年4月5日から本日2024年11月17日までの期間を計算。 本日の日付は絶対参照とし、他の人の計算にも使用できるようにする。
“Y”	表示単位は年。

そうすると以下のように「3」と加入年数が表示されます。DATEDIF関数では、端数の月は切り捨てされます。

「Ctrl + 1」で「セルの書式設定」のダイアログボックスを開き、ユーザー定義で「0″年”」とすれば、「～年」という表示に変更することができます。

セルD4の数式を下にもコピーすれば、他の人の加入年数も表示されます。

次にこの加入年数を基準として会員ランクを分けていきたいと思います。IFS関数を使って分ける方法とVLOOKUP関数を使って分ける方法があります。

IFS関数を使ってランク分け方法

IFS関数とは複数の条件のいずれかを満たすか判断する関数です。会員ランクを表示させたいセルE4に以下のように入力します。各論理式は「～以上」であれば会員ランク「○○」を判断しています。

=IFS(D4>=$H$7,$I$7,D4>=$H$6,$I$6,D4>=$H$5,$I$5,D4>=$H$4,$I$4)

数式	役割
IFS(	複数の条件のいずれを満たすかを左から順に判断する。
D4>=$H$7,$I$7	D4の値がH7より大きければ、I7を表示。 今回は加入年数が「5年」以上であれば「ゴールド」を表示。
D4>=$H$6,$I$6	D4の値がH6より大きければ、I6を表示。 今回は加入年数が「3年」以上であれば「シルバー」を表示。
D4>=$H$5,$I$5	D4の値がH5より大きければ、I5を表示。 今回は加入年数が「1年」以上であれば「ブロンズ」を表示。
D4>=$H$4,$I$4	D4の値がH4より大きければ、I4を表示。 今回は加入年数が「0年」以上であれば「ノーマル」を表示。

そうすると以下のように加入年数に応じた会員ランクが表示されます。加入年数3年の場合、「3年」以上なので「シルバー」になります。

下にもセルE4の数式をコピーすれば、他の人の会員ランクも表示されます。

VLOOKUP関数を使ってランク分けする方法

VLOOKUP関数を使ってランク分けする方法もあります。こちらの方が数式は単純ですが、ランク表の加入年数の列は昇順、つまり小さい順に並べておく必要があります。

会員ランクを表示させたいセルE4に以下のように入力します。検索方法はTRUEになっていることに注意してください。

=VLOOKUP(D4,$H$4:$I$7,2,TRUE)

数式	役割
VLOOKUP(D4	セルD4の値で検索する
$H$4:$I$7	検索に用いたい加入年数と会員ランクの範囲を指定する。今回は加入年数で検索したいので、加入年数の列が範囲の左端に来るようにする。 他の人も同じ範囲で検索したいので、絶対参照にする。
2	検索した結果、表示させたいデータの列を指定する。今回は会員ランクを表示させたいので範囲の「2」列目を指定する。
TRUE	「～以上」と数値を区切って該当する区分を特定する。

そうすると以下のように加入年数に応じた会員ランクが表示されます。加入年数3年の場合、「3年」以上なので「シルバー」になります。

下にもセルE4の数式をコピーすれば、他の人の会員ランクも表示されます。

VLOOKUP関数によるランク分けの詳細は下記記事も参考にしてみてください。

加入月数に応じてランク分けする

加入月数に応じてランク分けする場合は、DATEDIF関数の表示単位を「月」にします。

加入月数を表示させたいセルD4に以下のように入力します。年数の場合は単位が”Y”でしたが、月数の場合は単位が”M”になります。

=DATEDIF(C4,$E$1,”M”)

数式	役割
DATEDIF(C4,$E$1	セルC4の日付からセルE1の日付までの期間を計算する。この場合、2021年4月5日から本日2024年11月17日までの期間を計算。 本日の日付は絶対参照とし、他の人の計算にも使用できるようにする。
“M”	表示単位は月。

そうすると以下のように加入月数「43」が表示されます。DATEDIF関数では、端数の日は切り捨てされます。

「Ctrl + 1」で「セルの書式設定」のダイアログボックスを開き、ユーザー定義で「0″か月”」とすれば、「～か月」という表示に変更することができます。

セルD4の数式を下にもコピーすれば、他の人の加入月数も表示されます。

次にこの加入年数を基準として会員ランクを分けていきたいと思います。IFS関数を使って分ける方法とVLOOKUP関数を使って分ける方法があります。作業内容としては、加入年数で実施したものと変わりはありません。

IFS関数を使ってランク分け方法

IFS関数とは複数の条件のいずれかを満たすか判断する関数です。会員ランクを表示させたいセルE4に以下のように入力します。各論理式は「～以上」であれば会員ランク「○○」を判断しています。

=IFS(D4>=$H$7,$I$7,D4>=$H$6,$I$6,D4>=$H$5,$I$5,D4>=$H$4,$I$4)

数式	役割
IFS(	複数の条件のいずれを満たすかを左から順に判断する。
D4>=$H$7,$I$7	D4の値がH7より大きければ、I7を表示。 今回は加入月数が「50月」以上であれば「ゴールド」を表示。
D4>=$H$6,$I$6	D4の値がH6より大きければ、I6を表示。 今回は加入月数が「30月」以上であれば「シルバー」を表示。
D4>=$H$5,$I$5	D4の値がH5より大きければ、I5を表示。 今回は加入月数が「10月」以上であれば「ブロンズ」を表示。
D4>=$H$4,$I$4	D4の値がH4より大きければ、I4を表示。 今回は加入月数が「0月」以上であれば「ノーマル」を表示。

そうすると以下のように加入月数に応じた会員ランクが表示されます。加入月数43か月の場合、「30月」以上なので「シルバー」になります。

下にもセルE4の数式をコピーすれば、他の人の会員ランクも表示されます。

VLOOKUP関数を使ってランク分けする方法

VLOOKUP関数を使ってランク分けする方法もあります。こちらの方が数式は単純ですが、ランク表の加入月数の列は昇順、つまり小さい順に並べておく必要があります。

会員ランクを表示させたいセルE4に以下のように入力します。検索方法はTRUEになっていることに注意してください。

=VLOOKUP(D4,$H$4:$I$7,2,TRUE)

数式	役割
VLOOKUP(D4	セルD4の値で検索する
$H$4:$I$7	検索に用いたい加入月数と会員ランクの範囲を指定する。今回は加入月数で検索したいので、加入月数の列が範囲の左端に来るようにする。 他の人も同じ範囲で検索したいので、絶対参照にする。
2	検索した結果、表示させたいデータの列を指定する。今回は会員ランクを表示させたいので範囲の「2」列目を指定する。
TRUE	「～以上」と数値を区切って該当する区分を特定する。

そうすると以下のように加入月数に応じた会員ランクが表示されます。加入年数43か月の場合、「30月」以上なので「シルバー」になります。

下にもセルE4の数式をコピーすれば、他の人の会員ランクも表示されます。

VLOOKUP関数によるランク分けの詳細は下記記事も参考にしてみてください。

DATEDIF関数を使えば、2つの日付の差を計算できるので、プロジェクトの稼働日を調べたり、年齢を調べたりすることもできます。下記記事も参考にしてみてください。