エクセル関数についての質問です。

質問

エクセル関数についての質問です。

添付画像の表の中の土曜日の各セル[現金販売・月極販売・合計金額]にそれぞれの合計(週の合計：月曜日から金曜日)を出したいのです。
日付は自動入力にしています。
自分なりに調べてみたのですが、関数初心者の為答えにたどりつけません。
別シートには毎日入力する表を作り、添付している表に入力されるようにSUMIF関数を入れています。毎月曜日は変動するので金額入力欄へは全て関数が入っています(もちろん土曜日の所へも)その関数は消さずに土曜日の場合は週の合計をだしたいのです。

会社への提出書類なのですが、未だに手書き書類です。
自分の日々の売上管理をパソコンでしてみようと思いました。この表は提出書類なので形式を変えること事は出来ません。

エクセル2003を使っています。
宜しくお願いいたします。

回答No.3

KURUMITO · ベストアンサー

土曜日が6日よりも前にあるのでしたら1日からの集計を、そうでない場合には月曜日から金曜日までの集計をということでしたらＣ3セルに次の式を入力して、Ｋ３セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(MOD(COLUMN(A1)-1,3)<>0,"",IF(AND($B3="土",COUNT($A$3:$A3)<=6),SUM(C2:C$3),IF(AND($B3="土",COUNT($A$3:$A3)>6),SUM(OFFSET(C3,-5,0):C2),これまでにＣ3セルに入力していた式)))
上記の式はＢ列の曜日で表示形式で土と表示しているのではなく、文字列として土のように表示されている場合です。
もしも、日にちが2010/1/1を1の表に表示させているのでしたら、例えば$B3="土"は次のような式に置き換えて使用すればよいでしょう。
WEEKDAY($A3)=7

お礼

補足の欄へお礼を書いておりました

すみません。

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

補足

ご回答ありがとうございます。
説明下手な文で本当に申し訳ないです。

ご回答頂いたとおりしてみました！！
ありがとうございます！　ありがとうございます！！
問題解決いたしました(涙)
実はこの問題で１週間近く足止めをくらっておりました(涙涙…)

関数の意味をひとつずつ調べてご回答頂いた関数を理解しようとしたのですが難しかったです（すみません）　お聞きしてもいいですか？
=IF(MOD(COLUMN(A1)-1,3)<>0,"",IF(AND($B3="土",COUNT($A$3:$A3)<=6),SUM(C2:C$3),IF(AND($B3="土",COUNT($A$3:$A3)>6),SUM(OFFSET(C3,-5,0):C2),これまでにＣ3セルに入力していた式)))

(MOD(COLUMN(A1)-1,3)<>0　←これはCOLUMN(A1)で列番号を求めて、MODで割り算の余りを出しているんですよね？　その答えと0が<>不等の場合という事ですよね？？　
【疑問?】曜日はB列なのになぜA列なのですか？
【疑問?】なぜ割り算の余りがいるのですか？

IF(AND($B3="土",COUNT($A$3:$A3)<=6),SUM(C2:C$3),←これはB3="土"かつ、COUNT($A$3:$A3)の数値データのセルを数えて1。1が6以下ならSUM(C2:C$3)。
【疑問?】C2には"売上金額"という文字しか入っていないんですが、なぜ範囲に入っているのですか？

SUM(OFFSET(C3,-5,0):C2)　←この場合もどうしてC2なんでしょうか？

理解不足で申し訳ございません。

回答No.6

KURUMITO

解答Ｎｏ3です。ご質問への回答が遅くなって済みません。
(MOD(COLUMN(A1)-1,3)<>0　←これはCOLUMN(A1)で列番号を求めて、MODで割り算の余りを出しているんですよね？　その答えと0が<>不等の場合という事ですよね？？　
【疑問?】曜日はB列なのになぜA列なのですか？
【疑問?】なぜ割り算の余りがいるのですか？
COLUMN(A1)としているのはこの式が初めにＣ3セルに入力する式ですのでその列を初めの列、すなわち、１、それはCOLUMN(A1)と書くことができます。このようにすることでＤ3セルの式はCOLUMN(B1)となって２、Ｅ3セルの式はCOLUMN(C1)で３、Ｆ3セルの式は4となります。MOD(COLUMN(A1)-1,3)はＣ3セルでは0、Ｄ3セルでは1、Ｅ3セルでは２、Ｆ3セルでは0となりますね。
お示しの表ではＣ3セルに値があり、Ｆ3セルで値があり、ということですからMOD(COLUMN(A1)-1,3)が0の値のときに計算が行われるようにすればよいわけです。このため、MOD関数の値が0以外のとき、すなわちMOD(COLUMN(A1)-1,3)<>0のときには計算が行われないようにしています。

IF(AND($B3="土",COUNT($A$3:$A3)<=6),SUM(C2:C$3),←これはB3="土"かつ、COUNT($A$3:$A3)の数値データのセルを数えて1。1が6以下ならSUM(C2:C$3)。
【疑問?】C2には"売上金額"という文字しか入っていないんですが、なぜ範囲に入っているのですか？
Ｃ3のセルに入力した式で、Ｂ3セルが土である場合にはお示しした式では誤りとなりますので後ほど訂正した式を示しますが、仮にＣ5セルの式ではＢ5セルが土であればSUM(C4:C$3)となって、5行目の式では3行目から4行目のデータが集計されることになりますね。すなわち、式が入力されているより1行上から3行までの値が集計されることになります。

SUM(OFFSET(C3,-5,0):C2)　←この場合もどうしてC2なんでしょうか？
Ｃ3セルへの入力の式では1行上までのデータを集計することになりますので、そういう式になります。

ところでＢ3セルが土の場合にはお示しした式では誤りとなりますので、Ｃ3セルへの入力の式は次のように変更してください。
=IF(OR(AND(ROW(A1)=1,$B3="土"),MOD(COLUMN(A1)-1,3)<>0),"",IF(AND($B3="土",COUNT($A$3:$A3)<=6),SUM(C2:C$3),IF(AND($B3="土",COUNT($A$3:$A3)>6),SUM(OFFSET(C3,-5,0):C2),"")))
この式では1日が土曜日であるときは売上の行は空白になるようにしています。

お礼

遅くなりましてすみません！！
ご回答ありがとうございました^0^

説明頂いた内容を何度も読んでしっかりと理解して表に関数をいれました！
無事、週の合計がでました(涙)

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

回答No.5

avanzato

#4ですが一部訂正です。
B6=TEXT(A6,"AAA")
C6=IF($B6<>"土",★現状のSUMIF★,IF(CELL("ROW",C6)<=8,SUM(C$3:E5),SUM(C1:E5)))
F6=IF($B6<>"土",★現状のSUMIF★,IF(CELL("ROW",F6)<=8,SUM(F$3:H5),SUM(F1:H5)))
I6=SUM(C6:H6)
4行目から33行目は上記6行目の数式のコピーでお願いします。
3行目は仮に1日が土曜だったとき合計を求める計算式が出来ない為土曜の判定は不要です。
3行目は現状の計算式のままで大丈夫です。

お礼

詳しいご回答ありがとうございます！！
さっそく関数を理解する勉強へ。。。

回答No.4

avanzato

こんにちは。
IFで土曜の判定をつければ問題ありません。
IF(土曜以外のとき,現状のSUMIF,1週間の合計）です。
C32が30日になるかと思いますがC32の数式を次のようにしてください。
=IF($B32<>"土",★現状のSUMIF★,IF(CELL("ROW",C32)<=8,SUM(C$3:C31),SUM(C27:C31)))
他のセル(土曜以外のセルも共通)はC32のコピーで良いと思います。
例えばC32の合計範囲はC27～C31になりますが上のほうにコピーしていくとC6でC1～C5になりC5の場合は計算式が組めなくなります。
その為、わざとC32で数式を組み上方向にコピーします。
C5にこの計算式をコピーすると#refになりますが処理上問題ありません。
尚、この数式を使うにあたりＡ列とＢ列に条件があります。
Ａ列の入力値は例えば2009/01/09のような「日付」が入力されておりセルの書式設定「ユーザー定義:d」（日にちのみ表示）にすること。
Ｂ列で例えばB3であれば=TEXT(A3,"AAA")として値を曜日にすること。
この2点が条件で上記数式が実行可能です。

お礼

ご回答ありがとうございます。
いろんな関数の使い方があるんですね。とても参考になりました。
まだまだ関数の勉強をはじめたばかりで理解するのに時間がかかってしまいますが・・・頑張って理解して使って行きたいと思います！！

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

回答No.2

MackyNo1

ご質問の内容がいま一つ不明確です。

集計表の土曜日以外のセルには現在どのようなSUMIF関数が入力されているのでしょうか？

また、集計表の最初の土曜日は、先月の月末の月曜から当月の金曜までの集計をしたいのでしょうか？

このようなケースでは、場合によて複雑な数式を使う必要がありますので、実際に利用しているデータベースシートのレイアウトと、入力されているデータ例（日付の入力形式など）を提示されたほうがよいと思います。

また、集計シートのA列、B列はどのように入力しているのでしょうか？

今回のケースでは、たとえば、どこかのセルに「１」と入力すれば、自動的にA列とB列に1月の日付と曜日（セルの色も含めて）を自動的に表示する設定にしておくのが最も簡単な対応だと思います（この設定がしてあるという前提で解凍してもよいのかお知らせください）。

お礼

補足の欄へお礼を書いていました
すみません。

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

補足

ご返答ありがとうございます。
返信が遅くなり申し訳ありません。
補足欄にも添付できれば今作っているシートを見ていただけるんですが・・・できるのでしょうか？
説明下手ではありますが、頑張って作ったシートを表現してみました。
ご回答下さったNO1さんの補足欄に記入していますので宜しくお願いします。

>集計表の土曜日以外のセルには現在どのようなSUMIF関数が入力されているのでしょうか？
　　=SUMIF(現金販売入力シート!$G$3:$G$809,DATE(月度行動実績確認表!$A$1,月度行動実績確認表!$D$1,$A9),現金販売入力シート!$H$3:$H$809)

>また、集計シートのA列、B列はどのように入力しているのでしょうか？
　　A列：日付　B列：曜日

>今回のケースでは、たとえば、どこかのセルに「１」と入力すれば、自動的にA列とB列に1月の日付と曜日（セルの色も含めて）を自動的に表示する設定にしておくのが最も簡単な対応だと思います（この設定がしてあるという前提で解凍してもよいのかお知らせください）。
　　上記の様に1(月)と入力した時に自動的にA列とB列に日付と曜日が入ります。土曜・日曜・祝日の行にはセルの色が指定した色で表示される様にしています。

宜しくお願いいたします。

回答No.1

KURUMITO

ご質問の内容で一部分からない点があります。
お示しのシートはまとめのシートで金額の欄には式が入力されているとのことですが、土曜日の行にも式があるわけですね。その式を消さずに別の式を入れるように解釈できるのですが、そのようなことは不可能ですね。実際にはどのようにしたいのでしょう。別のシートの状況も含めて具体的に示してくれませんか？

補足

ご回答ありがとうございます。
説明不足で申し訳ございません・・・。

添付した表を合わせて３シート用意しました。

【１つめ(現金販売入力)のシート】
　　　A　　　　　B　　　 　　C　　　 　　D　　　　 　　　E　　　　　　　　F　　　　　　 G　　　　　　　　H
1　 2010年1月　
2　　日付 　 曜日　　 会社名　　会社の階　　現金売上　 その他の売上　 合計　　　日付(各セル毎)
3　 1月１日　　 金　　　　　A社　　　 7階　　　直接入力　　 　直接入力　　 (=E3+F3)　　　1月１日(←=A2)
(日付：1日分を結合してます)
4　　1月１日　　　金　　　　　A社　　　5階　　　直接入力　　 　直接入力　　　(=E4+F4)　　1月1日(←=A2)

【２つめ(月極の売上入力)】
　　　　A　　　　B　　　　　　　　　C　　　 　 　　　　D　　　　　　E　　　　　F　　　　　　G　　　　　　H　
1　　　日付　　会社名　　　　お客様名　　　　商品名　　　単価　　　個数　 　　売上金額　　日付(各セル毎)
2　　1月１日　　B社　　　　BBさん　　　　　○○○　　　　500　　　　　　　　　　(=E2*F2)　　　　1月１日(←=A2)
(日付：1日分を結合してます)
3 1月１日

【3つめ(月度行動実績確認表：添付表)】
　　　A　　　B　　　　　C　　　 　　D　　　　　E　　　　F　　　　G　
1 2010　　　　　　　年　　　　　1　　　　　月

8　　日付　　曜日　　現金売上　　月極売上　合計金額
9 　　1　　　金　　　=SUMIF(現金販売入力シート!$G$3:$G$809,DATE(月度行動実績確認表!$A$1,月度行動実績確認表!$D$1,$A9),現金販売入力シート!$H$3:$H$809)

10　　2　　　土
11　　3　　　日
12　　4　　　月
13　　5　　　火
14　　6　　　水
15　　7　　　木
16　　8　　　金
17　　9　　　土

というふうにしてみました。　この補足欄にどのように記入したらいいのか分からなく、遅くなってすみません。分かりますでしょうか・・・？

>土曜日の行にも式があるわけですね。その式を消さずに別の式を入れるように解釈できるのですが、そのようなことは不可能ですね。実際にはどのようにしたいのでしょう。

私の願いは曜日の欄が"土"ならば週の合計を表示、そうでない場合なら=SUMIF(現金販売入力シート!$G$3:$G$809,DATE(月度行動実績確認表!$A$1,月度行動実績確認表!$D$1,$A9),現金販売入力シート!$H$3:$H$809)の金額を表示したいのですが・・・可能なのでしょうか？
実際配られる提出書類の土曜日欄に週の合計を記入しています。

説明下手で申し訳ございません。