Excel2010で色分けしたセルの合計と平均

質問

エクセルの表で
色分けしたセルの合計と平均値をだしたいのですが

関数のみでというのは可能でしょうか

表をこのままプリントアウトしたいので
テーブルで色分け識別しての合計方法ではない
やり方があれば教えてください

また、この表は年度が替わると
曜日色分けも変わるようにしているので
そこには影響が出ないようにしたいのですが・・・

わかりにくい文章ですみません

回答No.2

kagakusuki · ベストアンサー

色々と不明な点が多いため、仮の話として、「AM」と入力されているセルがE4セルであり、「PM」の右隣に入力されている項目名が「1F」であるものとします。
　又、画像の右端（B列）に表示されている日付はシリアル値データであり、その日付が表示されているセルの書式設定の表示形式は、[ユーザー定義]のdであるものとします。
　又、祝日や振替休日の場合には、D列に祝日の名称が表示されるものとします。

　まず、曜日欄の中で最も上の所にあるC5セルに次の関数を入力して下さい。

=IF(ISERROR(1/(YEAR($B5)>1904)),"",TEXT($B5,"aaa"))

　次に、C5セルをコピーして、C6～C35の範囲に貼り付けて下さい。

　上記の準備を済ませますと、御要望にある各種の合計値や平均値は以下の様な関数で求める事が出来ます。

平日のAMの合計値を求める関数

=SUMIFS($E$5:$E$35,$C$5:$C$35,"<>土",$C$5:$C$35,"<>日",$D$5:$D$35,"")

平日のPMの合計値を求める関数

=SUMIFS($F$5:$F$35,$C$5:$C$35,"<>土",$C$5:$C$35,"<>日",$D$5:$D$35,"")

平日の1Fの合計値を求める関数

=SUMIFS($G$5:$G$35,$C$5:$C$35,"<>土",$C$5:$C$35,"<>日",$D$5:$D$35,"")

土曜のAMの合計値を求める関数

=SUMIF($C:$C,"土",$E:$E)

土曜のPMの合計値を求める関数

=SUMIF($C:$C,"土",$F:$F)

土曜の1Fの合計値を求める関数

=SUMIF($C:$C,"土",$G:$G)

日曜のAMの合計値を求める関数

=SUMIF($C:$C,"日",$E:$E)

日曜のPMの合計値を求める関数

=SUMIF($C:$C,"日",$F:$F)

日曜の1Fの合計値を求める関数

=SUMIF($C:$C,"日",$G:$G)

祝日のAMの合計値を求める関数

=SUMIF($D:$D,"*?",$E:$E)

祝日のPMの合計値を求める関数

=SUMIF($D:$D,"*?",$F:$F)

祝日の1Fの合計値を求める関数

=SUMIF($D:$D,"*?",$G:$G)

月のAMの合計値を求める関数

=SUM($E:$E)

月のPMの合計値を求める関数

=SUM($F:$F)

月の1Fの合計値を求める関数

=SUM($G:$G)

平日のAMの平均値を求める関数

=AVERAGEIFS($E$5:$E$35,$C$5:$C$35,"<>土",$C$5:$C$35,"<>日",$D$5:$D$35,"")

平日のPMの平均値を求める関数

=AVERAGEIFS($F$5:$F$35,$C$5:$C$35,"<>土",$C$5:$C$35,"<>日",$D$5:$D$35,"")

平日の1Fの平均値を求める関数

=AVERAGEIFS($G$5:$G$35,$C$5:$C$35,"<>土",$C$5:$C$35,"<>日",$D$5:$D$35,"")

土曜のAMの平均値を求める関数

=AVERAGEIF($C:$C,"土",$E:$E)

土曜のPMの平均値を求める関数

=AVERAGEIF($C:$C,"土",$F:$F)

土曜の1Fの平均値を求める関数

=AVERAGEIF($C:$C,"土",$G:$G)

日曜のAMの平均値を求める関数

=AVERAGEIF($C:$C,"日",$E:$E)

日曜のPMの平均値を求める関数

=AVERAGEIF($C:$C,"日",$F:$F)

日曜の1Fの平均値を求める関数

=AVERAGEIF($C:$C,"日",$G:$G)

祝日のAMの平均値を求める関数

=AVERAGEIF($D:$D,"*?",$E:$E)

祝日のPMの平均値を求める関数

=AVERAGEIF($D:$D,"*?",$F:$F)

祝日の1Fの平均値を求める関数

=AVERAGEIF($D:$D,"*?",$G:$G)

月のAMの平均値を求める関数

=AVERAGE($E:$E)

月のPMの平均値を求める関数

=AVERAGE($F:$F)

月の1Fの平均値を求める関数

=AVERAGE($G:$G)

お礼

kagakusukiさま

私の稚拙な文章から意図を汲み取っていただき
丁寧な回答を、ありがとうございました。

教えていただいた関数を活用させていただきました。

本当に助かりました。
ありがとうございます。

回答No.1

tom04

こんばんは！
画像が小さくて、詳細が判りませんが、
おそらく、土日・祝日の「合計」と「平均」を出したい！という解釈で・・・

↓の画像で（A列はシリアル値としています）
D36セルに
=SUMPRODUCT((WEEKDAY($A4:$A34,2)>5)*(D4:D34))+SUMPRODUCT((WEEKDAY($A4:$A34,2)<6)*($C4:$C34<>""),D4:D34)

D37セルに
=D36/(SUMPRODUCT((WEEKDAY($A4:$A34,2)>5)*1)+SUMPRODUCT((WEEKDAY($A4:$A34,2)<6)*($C4:$C34<>"")))

という数式を入れ列方向にオートフィルでコピーしています。

※　注意点　※
小の月の場合、月末セルを空白にしてしまうとSUMPRODUCT関数部分でエラーになりますので、

画像でA4セル（セルの表示形式はユーザー定義から　d　としています）に
=DATE(A$1,A$2,ROW(A1))
B4セル（セルの表示形式はユーザー定義から aaa　としてます）に
=A4
という数式を入れ31日の34行目までコピーしておき、
A4～C34セルを範囲指定　→　条件付き書式　→　新しいルール　→　数式を使用して・・・　→　数式欄に
=MONTH($A4)<>$A$2
という数式を入れ　→　書式　→　フォント色を「白」にして、見た目で見えなくなるようにしています。

こんな感じをご希望だったのでしょうかね？m(_ _)m

お礼

tom04さま

回答ありがとうございます。
私の稚拙な文章からいろいろと考えてくださり
ありがとうございます。

教えていただいた内容について
参考にさせていただきました

本当にありがとうございました。