EXCELでリピート率を算出したい

質問

EXCELであるサービスを受けたユーザーの1年分の履歴情報があり、サービスを1回受けると1レコードずつ記録される仕組みになっています。A列には個人を特定するユニークコードとB列にサービスを受けた日付があります。そこで関数を使ってこのサービスを1年間で2回以上受けたリピーターの割合を算出したいのですが、どのようにすればよいか分かる方教えてください。

回答No.2

DoragonFang · ベストアンサー

ピボットテーブルを使う方が色々応用が利いて
いいように思いますが、とりあえず目的のリピーターの人数をカウントする関数を。

適当なセル（たとえばD1とか）に

=SUMPRODUCT((1/COUNTIF($A$1:$A$100,A1:A100))) ・・・(A)

とすると、ユーザーの総数がわかります（データが入っているセルを A1:A100 と仮定していますので、必要なセル範囲に置き換えて下さい）。

次に２回以上サービスを受けたユーザー数は、

=SUMPRODUCT((1/COUNTIF($A$1:$A$100,A1:A100))*(COUNTIF($A$1:$A$100,A1:A100)>1))　　・・・(B)

で計算されます。先ほどの総数を得る関数に >1 という条件を加えただけです。

で、あとは、(B)÷(A)　で割合が求められます。

補足

ご回答ありがとうございます。よく理解しました。
更にご存知でしたらお聞きしたいのですが、同じ個人コードで同じ日付の場合、リピータとしてカウントしたくないのですが方法はありますか？

回答No.1

nak_goo

1)ピボットテーブルで集計する
　[個人コード]ごとの、[日付の個数]を集計します。
　これで、ユーザーごとの回数が表示されます。

2)　1)で作られる表を元に計算する
　[個人コード]ごとの、[日付の個数]が出ているわけですから、
　[日付の個数]の列(ここではC3からC12までと仮定します)の最下部に

　=COUNT($C$3:$C$12)　　　という式を入力すれば、
　個人コードの数、すなわちユーザーの総数(*1)が計算されます。また、

　=COUNTIF($C$3:$C$12,">=2")　　　という式を入力すれば、
　2以上の個人コードの数、すなわちサービスを1年間で2回以上受けたリピーターの総数(*2)が計算されます。

　あとは　　*2 ÷ *1　　で、全体に対するリピーターの割合が出ます。

補足

ご回答ありがとうございます。よく理解しました。
更にご存知でしたらお聞きしたいのですが、同じ個人コードで同じ日付の場合、リピータとしてカウントしたくないのですが方法はありますか？