<EXCEL> 下記を求める公式を教えて下さい。月額有料で会員向けのサービスをしています。(個人エステ 1か月8000円) エクセルで4月からの会員の入会日と退会日を入力しています。このデーターをもとに


① 同月に入って同月辞めた人が何人いるのか(要は8000円払った会員は何人いるのか)
② 2ヶ月会員だった人は何人いるのか
③ 3ヶ月会員は何人いるのか

を調べる公式はどのようなものになりますか。

セルには、名前、入会日、退会日(2006/4/1の形式)の3つの情報を入れています。

この調査の意図は、会員がだいたい何ヶ月継続しているかを調べたいのです。①②③の内容も適切ではなければご指摘下さい。

回答の条件
  • 1人2回まで
  • 登録:2006/06/28 17:02:11
  • 終了:2006/06/30 16:12:35

ベストアンサー

id:rikuzai No.7

りくっち回答回数1366ベストアンサー獲得回数1412006/06/29 10:01:28

ポイント40pt

下記のように記入されているシートを「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ヶ月」などと表示することができます。

その他の回答(6件)

id:antipattern No.1

antipattern回答回数125ベストアンサー獲得回数122006/06/28 23:17:42

ポイント15pt

「nヶ月間会員だった」というのは、月末を跨ぐと新たな料金が発生すると考えていいのでしょうか?

その場合、以下の式でそれぞれ計算できます。

=IF(退会日<=EOMONTH(入会日,0),"一ヶ月退会","一ヶ月以上")

=IF(退会日<=EOMONTH(入会日,1),"二ヶ月退会","二ヶ月以上")

=IF(退会日<=EOMONTH(入会日,2),"三ヶ月退会","三ヶ月以上")

この関数を使われる場合は、エクセルの「ツール」→「アドイン」をクリックし、その中にある「分析ツール」にチェックを入れておく必要があります。

初期設定では使えないはずです。

id:ringono

ありがとうございます。月末を跨ぐと新たな料金が発生します。

例えば、・4/1に入会、5/1に退会では 2ヶ月とみなし16000円

・4/15に入会、5/10に退会でも2ヶ月とみなし16000円

・4/30に入会、5/1に退会でも2ヶ月とみなし16000円

です。

この場合でも、先ほどの式でよろしいでしょうか。

また、日付を2006/4/1という形式でセルに記入してますが、それもそのままで問題ないでしょうか。

2006/06/29 09:18:07
id:taknt No.2

きゃづみぃ回答回数13539ベストアンサー獲得回数11982006/06/28 17:17:39

ポイント15pt

A1に入会日

B1に退会日が入っているとして

C1に

=YEAR(B1)*12+MONTH(B1) -YEAR(A1)*12-MONTH(A1)

とすれば 何ヶ月いたのか わかります。

① 結果が 0の人ですね。

② 結果が 1の人ですね。

③ 結果が 2の人ですね。

id:OVA3 No.3

OVA3回答回数242ベストアンサー獲得回数02006/06/28 17:12:13

ポイント15pt

名前がA入会日がB退会日がCと仮定します

1.Dに

=if(MONTH(B)-MONTH(c)=0,"当月",(MONTH(c)-MONTH(b)))

とすれば当月退会なら当月、ちがうなら月数がでます

2.3.

は上記の関数ででた2や3をCOUNTIF等で集計すれば出ます

id:gong1971 No.4

gong1971回答回数443ベストアンサー獲得回数682006/06/28 17:11:27

ポイント20pt
  • 4/1に入会、4/30に退会は1ヶ月会員
  • 4/1に入会、4/2に退会は1ヶ月会員

上記は問題ないかと思いますが、その他の細かい条件を確認させてください。

  • 4/1に入会、5/1に退会では?
  • 4/15に入会、5/10に退会では?
  • 4/30に入会、5/1に退会では?
id:ringono

回答ありがとうございます。月ごとに会員費を頂いています。

なので、

・4/1に入会、5/1に退会では 2ヶ月とみなし16000円

・4/15に入会、5/10に退会でも2ヶ月とみなし16000円

・4/30に入会、5/1に退会でも2ヶ月とみなし16000円

です。

2006/06/29 09:15:27
id:taknt No.5

きゃづみぃ回答回数13539ベストアンサー獲得回数11982006/06/29 11:31:39

ポイント15pt

料金は

=(YEAR(B1)*12+MONTH(B1) -YEAR(A1)*12-MONTH(A1)+1)*8000

で求められます。

id:gong1971 No.6

gong1971回答回数443ベストアンサー獲得回数682006/06/29 11:07:44

回答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関数を何個も書くのは

面倒なのでピボットテーブルを利用した方が便利ですね。


  1. 月数を求めた範囲が D2:D100 だとすると、D1 にラベルとして「月数」と入力し、D1:D100までを選択します。
  2. [データ]メニューから[ピボットテーブルとピボットグラフレポート]をクリック。
  3. [次へ]ボタンを2回クリックし、[完了]ボタンをクリック。
  4. 新しいシートが作られ、[ピボットテーブル]というツールバーが表示されます。
  5. そのツールバーから[月数]と書かれた部分を「ここに行のフィールドを...]と書かれた場所までドラッグします。(マウスをクリックしたまま移動すること)
  6. 同様にもう1度「ここにデータアイテムを...」と書かれた場所までドラッグします。
  7. 出来た表の右上「合計:月数」を右クリックし[フィールドの設定]で[データの個数]を選択して[OK]をクリック。

ちょっと難しく感じるかもしれませんが、慣れると非常に便利な機能です。

下記のページは図入りで解説されているので、一度見ておくと分かりやすいと思います。

http://kokoro.kir.jp/excel/pivottable.html

id:rikuzai No.7

りくっち回答回数1366ベストアンサー獲得回数1412006/06/29 10:01:28ここでベストアンサー

ポイント40pt

下記のように記入されているシートを「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ヶ月」などと表示することができます。

  • id:rikuzai
    いるかありがとうございます。
    一応4月からのデータということでしたので、
    年度をまたぐ必要が現時点ではないとして、
    月数のみを算出する数式にしています。
    今後来年度も運用されていくことがあるようでしたら、
    他の回答者の方々が提示されているYEAR関数を使用した数式にする必要があるかと思います。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません