エクセル：SUMの応用に関して

質問

度々すみません。

以下の式に月別で見たい場合どの様な式を追加すれば宜しいでしょうか？

=SUM(SUMIF(Copyoftable!G2:G504,{"終了","解決"},Copyoftable!M2:M504))/SUM(COUNTIF(Copyoftable!G2:G504,{"終了","解決"}))

回答No.2

mshr1962 · ベストアンサー

補足が必要な条件

１．平均値を求めてるのだと思うけど、EXCELのバージョンはなんでしょうか？

２．月別と言うことですが、どこかの列に月または日付（年月日）があると思うけどどちらでしょうか？

－－－－－－－－－－－－－－－－－－
EXCEL2007以前でCopyoftable!B2:B504に月が入力されてる場合で4月

=SUMPRODUCT(((Copyoftable!G2:G504="終了")+(Copyoftable!G2:G504="解決"))*(Copyoftable!B2:B504=4),Copyoftable!M2:M504)/SUMPRODUCT(((Copyoftable!G2:G504="終了")+(Copyoftable!G2:G504="解決"))*(Copyoftable!B2:B504=4))

－－－－－－－－－－－－－－－－－－
EXCEL2007以前でCopyoftable!B2:B504に日付が入力されてる場合で4月

=SUMPRODUCT(((Copyoftable!G2:G504="終了")+(Copyoftable!G2:G504="解決"))*(MONTH(Copyoftable!B2:B504)=4),Copyoftable!M2:M504)/SUMPRODUCT(((Copyoftable!G2:G504="終了")+(Copyoftable!G2:G504="解決"))*(MONTH(Copyoftable!B2:B504)=4))

※年月別の場合

=SUMPRODUCT(((Copyoftable!G2:G504="終了")+(Copyoftable!G2:G504="解決"))*(TEXT(Copyoftable!B2:B504,"yyyymm)="201404"),Copyoftable!M2:M504)/SUMPRODUCT(((Copyoftable!G2:G504="終了")+(Copyoftable!G2:G504="解決"))*(TEXT(Copyoftable!B2:B504,"yyyymm)="201404"))

－－－－－－－－－－－－－－－－－－
EXCEL2007以降でCopyoftable!B2:B504に月が入力されてる場合で4月

=AVERAGEIFS(Copyoftable!M2:M504,Copyoftable!G2:G504,{"終了","解決"},Copyoftable!B2:B504,4)

－－－－－－－－－－－－－－－－－－
EXCEL2007以降でCopyoftable!B2:B504に日付が入力されてる場合で4月

=AVERAGEIFS(Copyoftable!M2:M504,Copyoftable!G2:G504,{"終了","解決"},Copyoftable!B2:B504,">="&DATE(2014,4,1),Copyoftable!B2:B504,"<"&DATE(2014,5,1))

お礼

参考になりました、有難うございます。

補足

失礼しました。

Excelバージョン：14.0.7128.5000(32ビット)

実際のシート上にはI列に日付（yyyy/mm/dd）が存在しているので、以下の通り試しました。それらしき数値は出てくるのですが、手動で平均値をだした結果と異なります。

=AVERAGEIFS(Copyoftable!M2:M504,Copyoftable!G2:G504,{"終了","解決"},Copyoftable!I2:I504,">="&DATE(2014,4,1),Copyoftable!I2:I504,"<"&DATE(2014,5,1))

回答No.3

keithin

Ａ列をＩ列に書き換えるだけですが？

再掲：
CopyoftableのＩ列に年/月/日を列記する
計算シートのＡ１に計算したい月を１から１２の数字で記入する

数式を
=SUMPRODUCT((MONTH(Copyoftable!I2:I504)=A1)*((Copyoftable!G2:G504="終了")+(Copyoftable!G2:G504="解決"))*Copyoftable!M2:M504)/SUMPRODUCT((MONTH(Copyoftable!I2:I504)=A1)*((Copyoftable!G2:G504="終了")+(Copyoftable!G2:G504="解決")))
みたいにしてみる。

それとも（相変わらずナイショの）実際のデータを見て、「月平均」じゃなくやっぱり「年月平均」を計算しなきゃならないというなら、その部分はあなたがご自分で工夫したように年月日で条件（何年何月１日から翌月１日までの大小で）を重ねれば良いです。

お礼

参考にします、有難うございました。

回答No.1

keithin

どこ列に年月日が記入してあって、それを何年何月で絞りたいのか何月だけでいいのかどうやって指定したいのか、そういった具体的な情報が全然ありません。

準備：
CopyoftableのＡ列に年/月/日を列記する
計算シートのＡ１に計算したい月を１から１２の数字で記入する

数式を
=SUMPRODUCT((MONTH(Copyoftable!A2:A504)=A1)*((Copyoftable!G2:G504="終了")+(Copyoftable!G2:G504="解決"))*Copyoftable!M2:M504)/SUMPRODUCT((MONTH(Copyoftable!A2:A504)=A1)*((Copyoftable!G2:G504="終了")+(Copyoftable!G2:G504="解決")))
みたいにしてみる。

＃ご利用のエクセルのバージョンもご相談に書かれていません。それによっては、もうちょっとだけ簡易な数式にできるかもしれません。ご相談投稿では、普段ご利用のソフトのバージョンまでキチンと明記する事を憶えて下さい。次回以降のご参考に。

お礼

参考になりました、有難うございます。

補足

失礼しました。

Excelバージョン：14.0.7128.5000(32ビット)

実際のシート上にはI列に日付（yyyy/mm/dd）が存在しているので、それを条件とし、月別にM列2~504に算出されている日数の平均値をI列の月別に算出したいです。