顧客のデータを分析する際、年齢から20歳代、30歳代、40歳代というようにグループに分けたい場合があります。そのような場合、VLOOKUP関数を使うことで効率的にグループ分けを行うことができます。
なお、この記事ではピボットテーブルを使用せずにグループ分けする方法を紹介しています。ピボットテーブルを使用した方法は下記記事をご覧ください。
VLOOKUP関数を使って効率的に20歳代、30歳代、40歳代というようにグループ分けする方法
たとえば、以下のような顧客データに○歳代を表示させる場合を考えます。
まず、IF関数を使って条件分岐させることが思いつきますが、この方法では数式が長く複雑になってしまいます。
=IF(C2<20,”10歳代”,IF(C2<30,”20歳代”,IF(C2<40,”30歳代”,IF(C2<50,”40歳代”,IF(C2<60,”50歳代”,IF(C2<70,”60歳代”,”70歳代以上”))))))
そこで、VLOOKUP関数を使用します。VLOOKUP関数は特定の値を指定するだけでなく、「～以上」という範囲で検索して、該当する値を表示させることも可能です。その際、VLOOKUPの検索方法は「TRUE」とします。
| VLOOKUP(検索値,範囲,列番号,[検索方法]) |
|---|
| 検索値で指定した値を、範囲の左端列から検索する。検索値と一致するデータの左から列番号目の値を抽出し表示する。検索方法に「FALSE」を指定すれば、完全に一致するデータを検索する。「TRUE」を指定すれば、「～以上」と数値を区切って該当する区分を特定する。 |
まず、どこでもいいので、別表でグループ分けしたい年代を数値で記載します。
「Ctrl+1」でセルの書式設定を開き、表示形式でこの数値を「○歳代」というように表示されるように設定します。この数値がVLOOKUP関数の検索対象であり、かつ、表示内容になります。
年代を表示させたいセルD2に以下のように数式を入力します。検索方法には「TRUE」を指定することで、20歳以上、30歳以上というように数値を区切って該当の区分を特定します。
=VLOOKUP(C2,$I$2:$I$7,1,TRUE)
検索値の「39歳」は30歳以上なので、「30」と表示されます。
下に数式をコピーすれば、年齢に対応する年代の数値が表示されます。
D列も「0″歳代”」と表示形式を設定します。
これで年齢に応じて20歳代、30歳代、40歳代というようにグループ分けを行うことができます。
このように年代別にグループ分けすることで、年代別の集計が可能になります。たとえば、先ほど作成した年代の区切りの表の右側に年代別の購入金額を表示させます。
ここではSUMIF関数を使って集計を行います。範囲に先ほどグループ分けした「年代」の列を使用し、検索条件を各年代と一致することとします。合計範囲には「購入金額」の列を指定します。
=SUMIF($D$2:$D$21,I2,$G$2:$G$21)
これで20歳代の購入金額の合計「7,600円」が表示されます。
この数式を下にコピーすれば、年代別の購入金額の合計が表示されます。
この年代を利用すれば、COUNTIF関数やAVERAGEIF関数を使った集計も簡単に行うことができます。
コメント