エクセルの計算

質問

質問させていただきます。

Aの列に飛び飛びでランダムの日付があり、Bの列に曜日が縦に並んでいます。
A列の日付を入力したら曜日が自動で出るようにB列に以下を入れました。

=IF(A1<>"",MID("日月火水木金土",WEEKDAY(A1),1),"")

このとき、土曜は青、日曜は赤と条件をつけました。

そしてC列に人数、D列に合計金額の欄があり、人数×単価をDに入れました。

このとき、条件として土曜日曜はプラス１，０００円となります。
IFとORで対処し、一日の計算はどうにかできました。

１期間の合計（平日は平日の欄、土日は土日の欄）を下に入れるのですが、上記に書いたとおり、日付はカレンダーではなくランダムで入っているので、土日だけの計算は手作業で計算させています。
これを土日は土日、平日は平日で計算させる方法はありませんでしょうか？

たとえば土か日と記入されているものだけを集めて計算させるとか。

説明に不足があればどんどんご指摘下さい。

回答No.1

zap35 · ベストアンサー

いくつか方法が考えられます。

１）SUMIF関数の利用
土曜日と日曜日の合計は
　=SUMIF(B1:B20,"土",D1:D20)+SUMIF(B1:B20,"日",D1:D20)
で求められます。平日の合計は
　=SUM（D1:D20)-SUMIF(B1:B20,"土",D1:D20)+SUMIF(B1:B20,"日",D1:D20)
になります。やや原始的ですが理解しやすいと思います

２）SUMPRODUCT関数の利用
WEEKDAY関数で「WEEKDAY(A1,2)」にすれば「月,火,水,木,金,土,日」が「1,2,3,4,5,6,7」になります。
ですから土日の合計は
　=SUMPRODUCT((A1:A20<>"")*(WEEKDAY(A1:A20,2)>5)*(D1:D20))
平日の合計は
　　=SUMPRODUCT((A1:A20<>"")*(WEEKDAY(A1:A20,2)<6)*(D1:D20))
で求められます

お礼

とてもわかりやすい説明有難うございます！
原始的な方法だと私でも理解しやすいですねｗ

質問重ねさせていただきますが、日付を入れたら自動で曜日が出て、土曜は青、日曜は赤と条件付き書式をしているのですが、祝日を赤色で自動で出す方法はありますでしょうか？
そして土曜と日曜の合計の中に祝日も入れたいのです。土日祝がプラス1000円なので。。
色で判断させれないなら、特別料金という欄に○でも出る関数をして、丸のある場所だけを計算させる方法ができないかなと思いまして。

回答No.5

zap35

＃０１です
Sheet2のA1:A10に「2007/12/24」のような祝日や振り替え休日の日付を入力しておきます

平日の合計は
　=SUMPRODUCT((A1:A20<>"")*(WEEKDAY(A1:A20,2)<6)*(COUNTIF(Sheet2!A1:A10,A1:A20)=0)*(D1:D20))

土日・祝日の合計は
　=SUMPRODUCT((A1:A20<>"")*((WEEKDAY(A1:A20,2)>5)+COUNTIF(Sheet2!A1:A10,A1:A20)>0)*(D1:D20))

で求められます。祝日もカレンダの色を変えたいなら条件付き書式の条件に「=COUNTIF(Sheet2!A1:A10,A1)>0」の時に「背景色を赤にする」を追加してください。「日曜日判定」が条件１、「祝日判定」が条件２、「土曜日判定」が条件３です。
「祝日判定」を条件３にしてしまうと、土曜日の青色が祝日よりも優先されてしまいますので…

回答No.4

noname#79209

土日の合計を求めたいなら、
=SUMPRODUCT((WEEKDAY(A1:A18,2)>=6)*D1:D18)
平日なら、
=SUMPRODUCT((WEEKDAY(A1:A18,2)<6)*D1:D18)
でよいのでで、
曜日を表示しなくてよいなら、B列は不要です。
曜日を表示したい場合でも、B列は「=A2」として、表示書式を「aaa」にすれば「土」「日」と表示されます。
或いは、A列の表示書式を「yyyy/mm/dd"("aaa")"」でもよいでしょう。

表のデザインによってはDGET関数でも可能です。

お礼

SUMPRODUCTを使ったらできました！ありがとうございます！

回答No.3

imogasi

説明不足というより、ごたごたして意味が取りにくい。
＞Aの列に飛び飛びでランダムの日付があり、Bの列に曜日が縦に並んでいます。
A列の日付を入力したら曜日が自動で出るようにB列に以下を入れました
->行は飛び飛びでなく、日付が飛び飛びの日付だということですね
どっちにも取れる・
例データ
Ａ列　　Ｂ列　　Ｃ列　　Ｄ列
2007/12/1 土 3 3300
2007/12/2 日 2 2000
2007/12/4 火 3 3500
2007/12/7 金 2 2500
2007/12/10　月 4 4200
2007/12/12　水 3 3000
2007/12/7 金 3 3100
2007/12/8 土 2 2400
2007/12/9 日 4 4400
2007/12/10　月 5 5000
・・・
ーーー
平日合計
土曜合計
日曜合計
ーーーー
＞=IF(A1<>"",MID("日月火水木金土",WEEKDAY(A1),1),"")
これでもできるが、普通は
ーー＞=IF(A10="","",TEXT(A10,"aaa"))を使う
＞このとき、土曜は青、日曜は赤と条件をつけました
目で見て、セル色を入れたのかも知れないが、普通は「条件付書式」を使う。
＞人数、D列に合計金額の欄があり、人数×単価をDに入れました
単価はシートのセルに出ていないのか。一定で質問者には常識なのか。書いてないのは不親切。この記述不要では。
＞IFとORで対処し、一日の計算はどうにかできました。
一日の計算とはー＞日々の計算のこと？
=IF(日＝土曜日、金額＋１０００、ＩＦ（日=日、金額＋１０００、金額））とやったということ？
＞期間の合計（平日は平日の欄、土日は土日の欄）を下に入れるのですが
ー＞下の欄とは、例えば
平日合計
土曜合計
日曜合計
合計
が
例えばＡ１４：Ａ１７にあるのか
ーーー
結局上記の例データのような例示しないためにわかりにくくなっている。これだと長々の誤解もできる文章は必要ないでしょう。
ーーー
これはこの質問コーナーで良くあるところの
ＳＵＭＩＦ
ＳＵＭＰＲＯＤＵＣＴ
（最近は問題で２条件の質問が主流。）
平日合計 21300
土曜合計 5700
日曜合計 6400
合計 33400
一番上から
=D17-D15-D16
=SUMIF($B$1:$B$10,"土",$D$1:$D$10)
=SUMIF($B$1:$B$10,"日",$D$1:$D$10)
=SUM(D1:D10)
平日の合計に付いて、こういう計算をしないなら
=SUMPRODUCT((WEEKDAY(A1:A10,2)<6)*(D1:D10))
で　21300
上記土日のＳＵＭＩＦもＷｅｅｋｄａｙ関数を使い、ＳＵＭＰＲＯＤＵＣＴでできるのはわかりますね。

お礼

至らない点が多くて申し訳ありません・・。

imogasi様のとおりにしてみたらできました！あんな文章でご理解頂きほんとに感謝です。

=IF(A1<>"",MID("日月火水木金土",WEEKDAY(A1),1),"")も=IF(A10="","",TEXT(A10,"aaa"))に修正しました。

ただ、平日合計の=SUMPRODUCT((WEEKDAY(A1:A10,2)<6)*(D1:D10))をしたら、VALUEとなってしまいます。原因はなんでしょうか？

あと、質問を重ねて申し訳ないですが、祝日も自動で出したいと思っているのですが、方法はあるのでしょうか？エクセルに祭日などの判断は無理だと聞いた事があるのですが。
というのも1000円増しは土日祝なので。。もし何か方法があればご教授ください。

補足

すみません、前回のお礼の訂正です。
日付を入れたら自動で曜日が出て、土曜は青、日曜は赤と条件付き書式をしているのですが、祝日を赤色で自動で出す方法はありますでしょうか？
そして土曜と日曜の合計の中に祝日も入れたいのです。土日祝がプラス1000円なので。。
色で判断させれないなら、特別料金という欄に○でも出る関数をして、丸のある（土日祝）だけを計算させる方法ができないかなと思いまして。

回答No.2

kojikoji58

まず、月～金を平日（仮に１）
土曜（日曜含む？)（仮に２）日曜を（仮に３）
とするようにE列でもどこでもいいので
ifを使用し入れる。

そのあと、SUMIFという計算を使用したら簡単です。
SUMIFは上記の場合
=sumif(E列を選択、１、合計したい列（D)を選択）
で平日の合計が出来ます。
注意としてE列と合計したい列（D)の範囲を合わせて
ください。１～１０なら１～１０、列全部なら列全部
という感じで。

説明へたでごめんなさい。