売上を平日、土日、祝 重複を避けたい

質問

いつもお世話になります。
WIN7 EXCELL2010 です。

問題点は(例えば)、
F19のセルに「1,000」を入力した時、
この日は祝日で月曜日です。
この「1,000」の値が(参照図の赤枠)　R13 に又、R6　の両方に加算されます。
この例で言うと「祝　R13」のみに加算させたいのですが是非ご指導いただけませんでしょうか。

参考に
A6 =IF(ISNA(VLOOKUP(B6,祭日!$A$6:$D$53,4,FALSE)),"",VLOOKUP(B6,祭日!$A$6:$D$53,4,FALSE))
B6 =TEXT($D6,"yyyymmdd")
C6 =IF(ISNA(VLOOKUP(B6,祭日!$A$6:$C$53,3,FALSE)),"",VLOOKUP(B6,祭日!$A$6:$C$53,3,FALSE))
D6 =IF(ROW(A1)>DAY(EOMONTH(A$1,0)),"",$A$1+ROW(A1)-1)
E6 =MID("日月火水木金土",WEEKDAY(D6),1)
J6 =IF(A6="○",F6,"")
R6 =SUMIF($S$6:$S$36,WEEKDAY($Q6),$F$6:$F$36)
S6 =WEEKDAY(D6)

回答No.2

kagakusuki · ベストアンサー

EXCELL2007以降であれば、A列、B列、S列の作業列や、G6～J36のセル範囲内の金額データなど使わずとも、集計処理を行う事が十分可能です。（但し、祭日シートの祭日表は必要です）

　今仮に、質問者様が御質問文に添えておられる添付画像に写っている表が存在しているシートのシート名が、Sheet1であるものとします。
　又、祭日シートのA列に祝日や振替休日の日付が入力されていて、祭日シートのD列にはそれらの祝日及び振替休日の名称が入力されているものとします。

　まず、Sheet1の
Q6セルに　　　月
Q7セルに　　　火
Q8セルに　　　水
Q9セルに　　　木
Q10セルに　　金
Q11セルに　　土
Q12セルに　　日
Q13セルに　　祝日
と入力して下さい。
　次に、D6セルの書式設定の表示形式を[ユーザー定義]の

d"日"

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

=IF(ISNUMBER(($C$3&"/"&$C$4&"/"&ROWS($6:6))+0),($C$3&"/"&$C$4&"/"&ROWS($6:6))+0,"")

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

=IF($D6="","",TEXT($D6,"aaa"))

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

=IF(AND(ISNUMBER($D6),COUNTIF(祭日!$A:$A,$D6)),VLOOKUP($D6,祭日!$A:$D,4,FALSE)&CHAR(1),"")

　次に、Sheet1のC6～E6セルに対して以下の様な操作を行って、条件付き書式を設定して下さい。

Sheet1のC6～E6のセル範囲をまとめて範囲選択
　　↓
Excelウィンドウの左上の辺りにある[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=AND(WEEKDAY($D6)=7,ISNUMBER($D6))

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「色」欄をクリック
　　↓
現れた色のサンプルの中にある青色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=AND(WEEKDAY($D6)=1,ISNUMBER($D6))

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「色」欄をクリック
　　↓
現れた色のサンプルの中にある赤色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=AND($C6<>"",ISNUMBER($D6))

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「背景色」欄の[色なし]ボタンをクリック
(※セルの地色を全て塗り潰してしまいますと、使用するプリンターやコピー機によっては、モノクロ印刷の際や、モノクロコピーを行った際に、セル内が全て黒く塗り潰されてしまうおそれがありますから、網掛を使った方が無難です)
　　↓
「パターンの色」欄をクリック
　　↓
現れた色のサンプルの中から、適当な濃さのピンク色の四角形を選択してクリック
　　↓
「パターンの種類」欄をクリック
　　↓
現れた網掛パターンのサンプルの中から適当なものを選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた[クリア]ボタンをクリック
　　↓
「色」欄をクリック
　　↓
現れた色のサンプルの中にある赤色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックス上に表示されている3種類の条件付き書式の内で、「書式」にピンク色の網掛が入っているものが、1番上になっている事を確認
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[OK]ボタンをクリック

　次に、Sheet1のC6～E6の範囲をコピーして、Sheet1のC7～E36の範囲に貼り付けて下さい。

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

=COUNT($D$6:$D$36)

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

=COUNTIFS($C$6:$C$36,"",$E$6:$E$36,"<>土",$E$6:$E$36,"<>日")

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

=COUNTIFS($C$6:$C$36,"",$E$6:$E$36,"土")

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

=COUNTIFS($C$6:$C$36,"",$E$6:$E$36,"日")

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

=COUNTIF($C$6:$C$36,"*?")

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

=SUMIFS($F$6:$F$36,$C$6:$C$36,"",$E$6:$E$36,$Q6)

　次に、Sheet1のR6セルをコピーして、Sheet1のR7～R12の範囲に貼り付けて下さい。

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

=SUMIF($C$6:$C$36,"*?",$F$6:$F$36)

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

=SUM($F$6:$F$36)

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

=SUMIFS($F$6:$F$36,$C$6:$C$36,"",$E$6:$E$36,"<>土",$E$6:$E$36,"<>日")

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

=$R$11

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

=$R$12

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

=$R$13

　これで、曜日や祝日別の売上金額の集計が自動的に行われる様になります。
　尚、祭日シートに関してですが、「春分の日と秋分の日」以外の、国民の祝日や振替休日の日付を関数を使って自動的に表示させる方法もあります。
　只、それを書いたのでは、このサイトの回答欄に入力可能な文字数の制限を超えてしまいますので、国民の祝日や振替休日の日付を自動的に表示させる方法に関しましては、次回の回答で述べさせて頂きます。

お礼

ご丁寧なご回答をいただき誠に有難うございます。
早速試させていただき完成することができました。
この後祝日表を作成したいと思っています。

回答No.4

mt2008

こんなので良いのでは？

R6に↓を入れてR12までコピー
=SUMIFS(F$6:F$36,S$6:S$36,WEEKDAY(Q6),C$6:C$36,"")

R13には↓
=SUM(F6:F36)-SUM(R6:R12)

お礼

御指導をいただき感謝しています。
ありがとうございました。

回答No.3

kagakusuki

回答No.2です。

　祭日シートの祝日と振替休日の表を関数を使って自動的に作成する方法です。
　但し、春分の日と秋分の日の日付に関しては、国立天文台が天体観測を行って得た地球の軌道や地軸の傾き等のデータを基にして、閣議によって決定されるものであり、それらの天体観測のデータは、多数の惑星と太陽の重力的な相互作用によって変動するため、その様な値を求める事は方程式を使って行う事は出来ず、Excelの関数を使って求めようとしましても、どうしても日付にずれが生じてしまう年が出て来ますので、春分の日とと秋分の日に関してだけは日本政府が発表した日付を手作業によって入力する必要があります。
　それと、祝日の決め方が現在の方式になったのは2007年からであり、2006年以前には祝日の決め方が現在とは異なっていますので、以下の方法で祝日や振替休日を求める事が出来るのは、2007年以降の年に関してのみである事をお断わりしておきます。

　まず、祭日シートのA列の書式の表示形式を[日付]にして下さい。（回答No.2の添付画像では、[ユーザー定義]のyyyy/m/d aaaにしております）
　次に、祭日シートのD6セルに「元旦」と入力して下さい。
　次に、祭日シートのA6セルに次の関数を入力して下さい。

AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,1,1),”“)

　次に、祭日シートのD7セルに「正月」と入力して下さい。
　次に、祭日シートのA7セルに次の関数を入力して下さい。

AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,1,2),”“)

　次に、祭日シートのD8セルに「正月」と入力して下さい。
　次に、祭日シートのA8セルに次の関数を入力して下さい。

AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,1,3),”“)

　次に、祭日シートのD9セルに「振替」と入力して下さい。
　次に、祭日シートのA9セルに次の関数を入力して下さい。

ISNUMBER(1/(WEEKDAY(A8)=1)),A8+1,”“)

　次に、祭日シートのD10セルに「成人」と入力して下さい。
　次に、祭日シートのA10セルに次の関数を入力して下さい。

AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,1,14-WEEKDAY(DATE(Sheet1!$C$2,1,0),3)),”“)

　次に、祭日シートのD11セルに「建国」と入力して下さい。
　次に、祭日シートのA11セルに次の関数を入力して下さい。

AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,2,11),”“)

　次に、祭日シートのD12セルに「振替」と入力して下さい。
　次に、祭日シートのA12セルに次の関数を入力して下さい。

ISNUMBER(1/(WEEKDAY(A11)=1)),A11+1,”“)

　次に、祭日シートのD13セルに「春分」と入力して下さい。
　次に、祭日シートのD14セルに「振替」と入力して下さい。
　次に、祭日シートのA14セルに次の関数を入力して下さい。

ISNUMBER(1/(WEEKDAY(A13)=1)),A13+1,”“)

　次に、祭日シートのD15セルに「昭和」と入力して下さい。
　次に、祭日シートのA15セルに次の関数を入力して下さい。

AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,4,29),”“)

　次に、祭日シートのD16セルに「振替」と入力して下さい。
　次に、祭日シートのA16セルに次の関数を入力して下さい。

ISNUMBER(1/(WEEKDAY(A15)=1)),A15+1,”“)

　次に、祭日シートのD17セルに「憲法」と入力して下さい。
　次に、祭日シートのA17セルに次の関数を入力して下さい。

AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,5,3),”“)

　次に、祭日シートのD18セルに「みどり」と入力して下さい。
　次に、祭日シートのA18セルに次の関数を入力して下さい。

AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,5,4),”“)

　次に、祭日シートのD19セルに「こども」と入力して下さい。
　次に、祭日シートのA19セルに次の関数を入力して下さい。

　最後に、祭日シートのA13セルに春分の日の日付を、祭日シートのA24セルに秋分の日の日付を、それぞれ手入力して下さい。

　以上です。

回答No.1

web2525

祝日にはC列に何かしらのデータが入っているので

R6=SUMPRODUCT(($C$6:$C$36="")*($E$6:$E$36=Q6)*$F$6:$F$36)
R12までコピー

R13だけは
=SUMPRODUCT(($C$6:$C$36<>"")*$F$6:$F$36)

補足

下記は上手く数字はでてくれました。
R13だけは
=SUMPRODUCT(($C$6:$C$36<>"")*$F$6:$F$36)

下記の数式は全く数字が出ません(空白)
R6=SUMPRODUCT(($C$6:$C$36="")*($E$6:$E$36=Q6)*$F$6:$F$36)
R12までコピー

誠に恐れ入りますが再度ご指導いただけませんでしょうか。