エクセルで年代別顧客単価を出すには?


売上の分析や投資家向けの資料を作成する際に、顧客年代別の平均単価を出す場合がありますよね。

エクセルでの資料作成などは、シートが煩雑になってしまい、ミスが多発するもの。

にも関わらずマーケティング部、経営企画部で作成する資料は全社的な意思決定を行うために必要な資料を作っているかと思います。ミスは許されません。

今回はこの「年代別に平均単価」を出来るだけ「簡単に」計算する方法をご紹介します。

では早速始めましょう。もともとの売上データは以下のようなものとします。

売上げデータ

顧客ごとに、年齢と購入したモノやサービスの代金が表示されているデータです。

このデータを元に、

年代別顧客単価

を完成させましょう。10代、20代から始まり、70代までの、顧客の平均単価を出します。

これを計算するためには、「AVERAGEIFS関数」を使います。セルで使うには、以下のように設定します。

=AVERAGEIFS(平均範囲,検索条件範囲1,条件1,検索条件範囲2,条件2…)

AVERAGEIFS関数は平均を計算してくれます。しかもその際の条件を複数設定することが出来ます。「年代ごとの顧客単価」といった場合、10代、つまり「10歳以上20歳未満の顧客単価の平均」となるので、条件が10歳以上、20歳未満と、複数必要になります。ちなみに「AVERAGEIFS関数」のSがない「AVERAGEIF関数」は条件が1つの場合に利用できます。

設定する項目は、上のエクセルで言えば、平均範囲が購入代金のエリア、検索条件範囲1,2,3…が年齢のエリアとなります。そして条件の部分が年代に関する部分となります。

ここで、条件の書き方ですが、不等号を含む形での書き方は

“不等号の記号”&条件となる値が入っているセル

の様に書きます。条件が「以上」の場合、”>=”、「より大きい」なら”>”、「より小さい」なら”<“となります。等しい場合には、”=”は必要なく、条件となる値が入っているセルのみでOKです。今回の例で言えば、10歳以上20歳未満なので、

“>=”&10

“<“&20

となります。

averageifs

これを他の行にも適用すれば完了です。下が結果の値とグラフになります。

年代別顧客単価グラフ

 

如何でしょうか?比較的短い数式で、簡単に計算ができたのではないでしょうか?

エクセルのバージョンが2007より以前の場合には、AVERAGEIFS関数がありません。その場合にはDSUM関数を使って計算をすることが出来ます。


COMMENTS