① 同月に入って同月辞めた人が何人いるのか(要は8000円払った会員は何人いるのか)
② 2ヶ月会員だった人は何人いるのか
③ 3ヶ月会員は何人いるのか
を調べる公式はどのようなものになりますか。
セルには、名前、入会日、退会日(2006/4/1の形式)の3つの情報を入れています。
この調査の意図は、会員がだいたい何ヶ月継続しているかを調べたいのです。①②③の内容も適切ではなければご指摘下さい。
下記のように記入されているシートを「Sheet1」として、
それぞれ
A列=名前
B列=入会日
C列=退会日
D列=料金
とします。
料金の列のD2に
=IF(ISBLANK(C2),((MONTH(TODAY())-MONTH(B2))+1)*8000,((MONTH(C2)-MONTH(B2))+1)*8000)
と入力して、データのある行までフィルコピーします。
すると、入会月数に応じた支払い料金が算出されます。
入会日は必ず記入されているとし、退会日が未記入の場合(空白)はファイルを開いた当日まで在籍しているとして算出しています。
名前 | 入会日 | 退会日 | 料金 |
---|---|---|---|
A | 2006/4/1 | 2006/4/15 | 8000 |
B | 2006/4/1 | 2006/5/1 | 16000 |
C | 2006/4/1 | 2006/5/25 | 16000 |
D | 2006/4/15 | 2006/6/1 | 24000 |
F | 2006/5/1 | 16000 |
各在籍月数毎の人数をカウントするシートを作成します。
このシートに
A1=8000
B1=16000
C1=24000
とそれぞれ記入し、
A2=COUNTIF(Sheet1!$D:$D,A1)
と入力してB2,C2にコピーします。
これでそれぞれ8000円支払った人(当月退会)、16000円支払った人(2月退会)、24000円支払った人(3月退会)が算出されます。
料金ではなく単純に月数がよい場合は、
Sheet1のD2に入れる式を
=IF(ISBLANK(C2),((MONTH(TODAY())-MONTH(B2))+1),((MONTH(C2)-MONTH(B2))+1))
集計シートのA1、B1、C1にそれぞれ1、2、3を記入すれば算出します。
見栄えが悪ければ
Sheet1のD列と集計シートのA1:C1のセル書式をユーザー設定にして
0"ヶ月"
とすれば値を変えずに「1ヶ月」などと表示することができます。
「nヶ月間会員だった」というのは、月末を跨ぐと新たな料金が発生すると考えていいのでしょうか?
その場合、以下の式でそれぞれ計算できます。
=IF(退会日<=EOMONTH(入会日,0),"一ヶ月退会","一ヶ月以上")
=IF(退会日<=EOMONTH(入会日,1),"二ヶ月退会","二ヶ月以上")
=IF(退会日<=EOMONTH(入会日,2),"三ヶ月退会","三ヶ月以上")
この関数を使われる場合は、エクセルの「ツール」→「アドイン」をクリックし、その中にある「分析ツール」にチェックを入れておく必要があります。
初期設定では使えないはずです。
A1に入会日
B1に退会日が入っているとして
C1に
=YEAR(B1)*12+MONTH(B1) -YEAR(A1)*12-MONTH(A1)
とすれば 何ヶ月いたのか わかります。
① 結果が 0の人ですね。
② 結果が 1の人ですね。
③ 結果が 2の人ですね。
名前がA入会日がB退会日がCと仮定します
1.Dに
=if(MONTH(B)-MONTH(c)=0,"当月",(MONTH(c)-MONTH(b)))
とすれば当月退会なら当月、ちがうなら月数がでます
2.3.
は上記の関数ででた2や3をCOUNTIF等で集計すれば出ます
上記は問題ないかと思いますが、その他の細かい条件を確認させてください。
回答ありがとうございます。月ごとに会員費を頂いています。
なので、
・4/1に入会、5/1に退会では 2ヶ月とみなし16000円
・4/15に入会、5/10に退会でも2ヶ月とみなし16000円
・4/30に入会、5/1に退会でも2ヶ月とみなし16000円
です。
回答4 の者です。回答4 まで開かれている状態で回答しています。
回答2 の方の数式を利用する事で、月数が求められます。
ただ1ヶ月だと0、2ヶ月だと1となってしまうので、
下記の通り +1 を加えた方が良いかと思います。
=YEAR(B1)*12+MONTH(B1)-YEAR(A1)*12-MONTH(A1)+1
その上で、各月数分の人数を出す場合は2つの方法があります。
もし3ヶ月までしかないようであれば、COUNTIF関数が便利です。
上記数式で月数を求めた範囲が D2:D100 だとすると、
1ヶ月の人の人数は以下の数式になります。
=COUNTIF($D$2:$D$100,1)
2ヶ月の人の場合、最後の ,1) の 1 を 2 に変えます。
同様に、3ヶ月なら 3 と変更してください。
ただ、この数が多い場合、COUNTIF関数を何個も書くのは
面倒なのでピボットテーブルを利用した方が便利ですね。
ちょっと難しく感じるかもしれませんが、慣れると非常に便利な機能です。
下記のページは図入りで解説されているので、一度見ておくと分かりやすいと思います。
下記のように記入されているシートを「Sheet1」として、
それぞれ
A列=名前
B列=入会日
C列=退会日
D列=料金
とします。
料金の列のD2に
=IF(ISBLANK(C2),((MONTH(TODAY())-MONTH(B2))+1)*8000,((MONTH(C2)-MONTH(B2))+1)*8000)
と入力して、データのある行までフィルコピーします。
すると、入会月数に応じた支払い料金が算出されます。
入会日は必ず記入されているとし、退会日が未記入の場合(空白)はファイルを開いた当日まで在籍しているとして算出しています。
名前 | 入会日 | 退会日 | 料金 |
---|---|---|---|
A | 2006/4/1 | 2006/4/15 | 8000 |
B | 2006/4/1 | 2006/5/1 | 16000 |
C | 2006/4/1 | 2006/5/25 | 16000 |
D | 2006/4/15 | 2006/6/1 | 24000 |
F | 2006/5/1 | 16000 |
各在籍月数毎の人数をカウントするシートを作成します。
このシートに
A1=8000
B1=16000
C1=24000
とそれぞれ記入し、
A2=COUNTIF(Sheet1!$D:$D,A1)
と入力してB2,C2にコピーします。
これでそれぞれ8000円支払った人(当月退会)、16000円支払った人(2月退会)、24000円支払った人(3月退会)が算出されます。
料金ではなく単純に月数がよい場合は、
Sheet1のD2に入れる式を
=IF(ISBLANK(C2),((MONTH(TODAY())-MONTH(B2))+1),((MONTH(C2)-MONTH(B2))+1))
集計シートのA1、B1、C1にそれぞれ1、2、3を記入すれば算出します。
見栄えが悪ければ
Sheet1のD列と集計シートのA1:C1のセル書式をユーザー設定にして
0"ヶ月"
とすれば値を変えずに「1ヶ月」などと表示することができます。
ありがとうございます。月末を跨ぐと新たな料金が発生します。
例えば、・4/1に入会、5/1に退会では 2ヶ月とみなし16000円
・4/15に入会、5/10に退会でも2ヶ月とみなし16000円
・4/30に入会、5/1に退会でも2ヶ月とみなし16000円
です。
この場合でも、先ほどの式でよろしいでしょうか。
また、日付を2006/4/1という形式でセルに記入してますが、それもそのままで問題ないでしょうか。