いまさら聞けないExcelの使い方講座

【Excel】入会年数に応じた会員ランク付けを自動化！知らなきゃ損するエクセルのDATEDIF関数活用テク

トップスタジオ

2018年10月5日 06:55

会員ステータスを名簿に載せるための年数の計算が大変……！

　会員登録した顧客に対して、登録からの年数に応じていろいろな優待や特典を設定するというのはよくある話ですよね。会員のランクをいくつかに分け、一定期間が経ったら次のランクに、さらに一定期間が経ったらその次のランクにアップ……と、会員のステータスを段階的に変化させることもあります。

　会員登録した顧客の名簿をExcelで作っている場合、会員登録日を名簿に載せていても、登録日から今日で何年が経過しているのか、パッとわかりづらいですよね。会員数が多い場合は、すべての会員の登録年数を計算するのは本当に大変です。会員登録日からの年数を計算し、条件に応じて会員ステータスを名簿に表示させることはできないのでしょうか？

　このような時には、「IF関数」と「DATEDIF関数」を組み合わせるのが便利です。今回は、Excelで会員登録日からの年数を計算し、年数に応じてランクアップする会員ステータスを名簿に表示するテクニックを解説します。

IF関数とDATEDIF関数を組み合わせて会員ステータスを表示する

　Excelで作成した「杜野エクセル教室会員名簿」があるとします。この名簿のG列には各会員の会員登録日（①）が入力されています。この教室では、会員登録からの年数に応じて会員ステータスが変わるものとします。今回は、セルF1に入力された日付（②）の時点での会員ステータスをH列（③）に表示する例を考えてみましょう。

　ここでは、会員登録日から3年以上が経過している会員が「シルバー会員」と表示されるようにしてみます。ステータスを表示する欄の一番上にあるセル（ここではセルH3）をクリックし（④）、［数式］タブ（⑤）→［関数の挿入］（⑥）をクリックします。

　［関数の挿入］ダイアログボックスが表示されます。今回の例では、IF関数の中にDATEDIF関数が入っている数式を作成するので、ダイアログボックス中央の［関数名］の一覧から「IF」（⑦）を選択し、［OK］（⑧）をクリックします。

　［関数名］の一覧に「IF」が表示されない場合は、ダイアログボックスの上の方にある［関数の検索］に「IF」と入力して［検索開始］をクリックすると、関数が検索され、ダイアログボックスに表示されます。この［関数の挿入］ダイアログボックスを使うと、関数を使った数式のうち自分で設定する必要のある部分だけを入力するだけで正しい書式の数式を作ることができるので、数式作成に自信のない時や、複雑な数式を作りたい時にとても役立ちます。

　すでに本連載ではIF関数を何度か解説しているのでおわかりの方もいらっしゃると思いますが、IF関数は条件によって行う処理を分けたい時に使う関数で、「=IF(論理式,値が真の場合,値が負の場合)」という書式で使います。「論理式」は、処理を分けるための条件で、今回はここでDATEDIF関数を使います。DATEDIF関数は、2つの日付の間の日数や月数、年数を求める関数で、「DATEDIF(開始日,終了日,単位)」という書式で使います。数式の中の「単位」で、どのような単位で日付の計算を行うかを指定します。単位について詳しくは以前の記事で解説しているので、参照してください。今回の例では満年数を計算するので、単位は「"Y"」とすれば良いですね。

　ではさっそく、ダイアログボックスに必要な情報を入力していきましょう。「論理式」には「DATEDIF($G3,$F$1,"Y")>=3」（⑨）と入力します。この数式は、「セルG3とセルF1に入力された日付の間の満年数が3年以上の場合」という意味です。数式中で「$」を入力する位置を間違えると、あとで数式をコピーした時に正しい結果が出ないので、注意してくださいね。「値が真の場合」の欄には、「論理式」で入力した条件を満たす場合の処理を入力します。ここでは、満年数が3年以上の場合は「シルバー会員」と表示したいので、「"シルバー会員"」（⑩）と入力します。文字列を表示したい場合は半角の「"」で囲みます。「値が偽の場合」は、条件を満たさない場合に行う処理を入力します。今回は、登録後3年未満の会員には何もステータスを表示しないので、「""」（⑪）と入力します。

　ここまで入力できたら、［OK］（⑫）をクリックします。

　ダイアログボックスが閉じ、セルH3に数式が入力されます。今回の例では、3行目に入力された会員は登録から3年以上が経過しているので「シルバー会員」と表示されていますが、3年経過していない会員であればここには何も表示されません。この数式をH列のほかのセルにもオートフィルでコピーしましょう。セル右下のフィルハンドルをセルH17までドラッグ（⑬）します。

　数式がコピーされ、会員登録日から3年以上経過している会員のデータが入力されている行には「シルバー会員」と表示されるようになりました（⑭）。

　ここで入力された数式なら、セルF1に入力する日付を変更するだけで、すべての会員の会員ステータスが自動で計算し直されます。一度数式を作成すれば、会員ステータスが変わる条件そのものに変更がない限り、数式を修正する必要はありません。また、新たな会員のデータを追加した場合は、数式をオートフィルでコピーすれば、新たな会員の会員ステータスも計算されるようになります。

会員ステータスが2段階の場合は……？

　ここまでは、「会員登録日から3年以上経過でシルバー会員になる」という条件でセルに数式を入力しました。では、「会員登録日から3年以上経過でシルバー会員に、5年以上経過でゴールド会員になる」というように会員ステータスが2段階になっている場合はどうすれば良いのでしょうか。

　このような場合も、IF関数とDATEDIF関数を組み合わせるのは先ほどの場合と同じです。今回のように条件が2つ以上になる場合は、IF関数の「値が偽の場合」にもう一度IF関数とDATEDIF関数を使った数式を入力します。

　今回の場合は、まず会員登録から5年以上経った会員を「ゴールド会員」とし、ゴールド会員でない会員のうち登録から3年以上経った会員を「シルバー会員」とすれば、正しくセルに会員ステータスを表示できます。

　先ほど数式を作成する時に使った［関数の引数］ダイアログボックスで、「論理式」に「DATEDIF($G3,$F$1,"Y")>=5」（①）と入力します。これで「会員登録から5年以上経っている場合」という条件を設定できます。「値が真の場合」には「"ゴールド会員"」（②）と入力します。さらに、「値が負の場合」に「IF(DATEDIF($G3,$F$1,"Y")>=3,"シルバー会員","")」（③）と入力することで、2つの条件で会員ステータスを分けることができます。

　ここまで入力できたら［OK］（④）をクリックします。

　ダイアログボックスが閉じて、会員登録日からの経過年数に応じてH列に「ゴールド会員」と「シルバー会員」の2段階の会員ステータスが表示されるようになりました（⑤）。

　数式バーに「=IF(DATEDIF($G3,$F$1,"Y")>=5,ゴールド会員,IF(DATEDIF($G3,$F$1,"Y")>=3,"シルバー会員",""))」という長い数式が入力されているのが見えると思いますが、これが先ほど［関数の引数］ダイアログボックスを使って作成した数式の内容です。もちろん最初からセルにこの数式を入力してもOKです。