エクセル 月別に条件付きでカウントする方法

質問

いつもお世話になっています。
毎日行っている小テストの点数をつけている表で条件にあう点数をとったのは
何回かという数値を返すにはどうすればいいのでしょうか。
日付　　　点数
2015/12/1　85
・
・
2015/12/20　95
2016/1/7　62
・
・
2016/1/29　57
日付と点数が縦に並んでいるデータで
2015年の12月は85点以上何回、85点～50点は何回、50点未満　何回
2016年の1月・・・・・
というような条件下でカウントする方法はありますか。
COUNTIFを使えばできそうな感じはするのですが上手く数値を返してくれません。
よろしくお願いします。

回答No.4

msMike · ベストアンサー

１．セル E1、F1 にそれぞれ数値 50、85 を入力し、セル G1 に文字列で
＿＿「85以上」を入力
２．セル E1、F1 をそれぞれ次のように“ユーザー定義”で書式設定
＿＿ E1: G/標準"未満"
＿＿ F1: "50以上"G/標準"未満"
３．セル D2、D3 にそれぞれ「2015/12/1」、「2016/1/1」を入力
４．範囲 D2:D3 を次のように“ユーザー定義”で書式設定
＿＿ yyyy/mm
５．セル E2、F2、G2 にそれぞれ下記の式を入力
＿＿ E2: =SUMPRODUCT(($A$2:$A$100>=$D2)*($A$2:$A$100<=EOMONTH($D2,0))*($B$2:$B$100>0)*($B$2:$B$100<E$1))
＿＿ F2: =SUMPRODUCT(($A$2:$A$100>=$D2)*($A$2:$A$100<=EOMONTH($D2,0))*($B$2:$B$100>=E$1)*($B$2:$B$100<F$1))
＿＿ G2: =SUMPRODUCT(($A$2:$A$100>=$D2)*($A$2:$A$100<=EOMONTH($D2,0))*($B$2:$B$100>=F$1))
６．範囲 E2:G2 を下方にオートフィル

お礼

ご回答ありがとうございました。
まさしく望んでいた関数です。関数は奥が深いです。
助かりました。

回答No.5

kagakusuki

カウント結果だけではなく、年月も自動的に表示される様にする方法です。

　まず、G2セルとH2セルの書式設定の表示形式を[ユーザー定義]の

0"点未満"

に設定して下さい。
　次に、F3セルとG3セルの書式設定の表示形式を[ユーザー定義]の

0"点以上"

に設定して下さい。
　次に、F3セルとG2セルに数値の85を入力して下さい。
　次に、G3セルとH2セルに数値の50を入力して下さい。
　次に、E4セルの書式設定の表示形式を[日付]の

2001年3月

に設定して下さい。
　次に、E4セルに次の関数を入力して下さい。

=IF(AND(E3="",ROWS(E$4:E4)>1),"",IF(COUNTIFS($A$4:INDEX($A:$A,MATCH(9E+307,$A:$A)),">="&EOMONTH(SUM(E3),0)+1,$A$4:INDEX($A:$A,MATCH(9E+307,$A:$A)),"<=9999/12/31"),TEXT(SMALL($A$4:INDEX($A:$A,MATCH(9E+307,$A:$A)),COUNTIF($A$4:INDEX($A:$A,MATCH(9E+307,$A:$A)),"<"&EOMONTH(SUM(E3),0)+1)+1),"yyyy年m月")+0,""))

　次に、F4セルに次の関数を入力して下さい。

=IF($E4="","",COUNTIFS($A$4:INDEX($A:$A,MATCH(9E+307,$A:$A)),">="&TEXT($E4,"yyyy/m")+0,$A$4:INDEX($A:$A,MATCH(9E+307,$A:$A)),"<"&EOMONTH($E4,0)+1,$B$4:INDEX($B:$B,MATCH(9E+307,$A:$A)),"<"&IF(ISNUMBER(F$2),F$2,9E+307),$B$4:INDEX($B:$B,MATCH(9E+307,$A:$A)),">="&SUM(F$3)))

　次に、F4セルをコピーして、G4～H4のセル範囲に貼り付けて下さい。
　次に、E4～H4のセル範囲をコピーして、E列～H列の5行目以下に貼り付けて下さい。

　以上です。

回答No.3

chie65536（@chie65535）

追記。

COUNTIFSでは「月ごとの集計」をする際に「範囲を手で修正しないとならない（2015年12月の範囲を、手動で指定しないとならない）」ので、該当月の実施日の個数が変わると、式を毎回手直ししないといけません。

当方の方式のように「とりあえず表の全ての行を同じ式で埋めてしまって、月が変わる行か、表の最終行だけ表示する」と、日付がどのように変化しても、表の中の式を修正する必要が無くなります。

表に行を追加する場合（新しいテストを実施した場合）も、最後の行を新しい行にコピーするだけで済みます。

なお、集計の途中経過に使った、C～E列は、幅を０にするか非表示にして、隠してしまうと良いです。

回答No.2

chie65536（@chie65535）

Ａ列に日付が、Ｂ列に点数が入っていると仮定します。

Ｃ列に「８５点以上なら１を、そうでないなら０を返す式」を書きます。

Ｃ２セルに

=(B2>85)*1

という式を書いて、表の一番下までコピーして下さい。

Ｄ列に「８５点未満、かつ、５０点以上なら１を、そうでないなら０を返す式」を書きます。

Ｄ２セルに

=AND(B2<85,B2>=50)*1

という式を書いて、表の一番下までコピーして下さい。

Ｅ列に「５０点未満なら１を、そうでないなら０を返す式」を書きます。

Ｅ２セルに

=(B2<50)*1

という式を書いて、表の一番下までコピーして下さい。

次に、Ｆ列に「８５点以上の回数を集計する式」を書きます。

Ｆ２セルに

=IF(OR(ISBLANK($A3),MONTH($A2)<>MONTH($A3)),SUMIF($A:$A,">="&TEXT($A2,"yyyy/mm")&"/01",C:C)-SUMIF($A:$A,">="&TEXT(DATE(YEAR($A2),MONTH($A2)+1,1),"yyyy/mm")&"/01",C:C),"")

という式を書きます（コピーはしません）

次に、Ｇ列、Ｈ列に「８４点以下～５０点以上の回数、５０点未満の回数を集計する式」をコピーします。

Ｆ２セルをコピーして、Ｇ２とＨ２セルに貼り付けて下さい。

次に、Ｆ２～Ｈ２の３つのセルを範囲指定してコピーして、３行目から表の最下行まで貼り付けて下さい。

すると、添付画像のように、月ごとに集計されます。

お礼

ご回答ありがとうございました。

回答No.1

aokii

COUNTIFS関数を使ってみてください。