エクセル：週合計、月合計を出したいのですが・・・

質問

月曜日～金曜日の　週、月合計を出したいのですがうまくできません＞＜
どなたかご教授いただけないでしょうか。
　A、B、C、D列には日付、残高、店の名前、売上が記入してあります。

以下のようなデータ表を作成したいですのですが・・・
http://www.fastpic.jp/images/961/8116491177.png

例
A　　　　　　　　　　B　　　　　　C　　　　　D
日付　　　　　　残高　　　　店の名前　売上
2012/01/01　　　　　　　　　　A店　　　100
2012/01/02　　　　　　　　　　B店　　　200
~~
2012/12/30
2012/12/31 　　　　　　　　　　C店　　 300

事前に、勉強したところ、月合計は
　=SUMPRODUCT((MONTH($A$2:$A$1000)=E2)*$D$2:$D$1000)
でいいのかな？と思ったのですがうまくできなかったです。

画像が奇麗にあげれないかもしれないのでfastpicに載せておきます。
画像のような表を作成したいのですが。。。
http://www.fastpic.jp/images/961/8116491177.png

どなたか、お力をお貸しいただけたら、うれしく思います。

回答No.6

MackyNo1 · ベストアンサー

>しましたところ、以下のようになりました。
ダウンロードパスワード　:　1234

このような質問サイト一般に言えることですが、上記のような他のサイトに誘導する（ソフトをインストールさせる）ような補足の仕方は好ましくありません。

例示レイアウトなら問題なく表示できることを検証していますので、もう一度回答を良く見直して再チャレンジしてみて下さい。

ちなみに、例示のレイアウトなら、E9セルには月を表す数字「1」を入力し（表示形式で0"月利益"とする）、F10セルに例示の式を入力して下方向に５つオートフィルすることになります。

回答No.7

tom04

No.2・4です！

MackyNo1さんが仰っているように別サイトを覗くような方法は本来好ましくないと思います。

ただ・・・
ダウンロードして圧縮ファイルを解凍してみました。
手元にExcel2003しかないせいかどうかわかりませんが、
意味不明のSheetが表示されてしまいました。
（一応互換ソフトはインストールしています）

そこで憶測での回答になります。
エラーではなく、「0」が表示されたのですよね？
というコトは、SUMPRODUCT関数のどこかの列が「FALSE」になっています。

作業列はちゃんと1～5（6が表示されることはないと思います）がちゃんと表示されているとすると
怪しいのは「○月の利益」のセルと「○週目」のセルです。

画像では「1月の利益」・「1週目」と表示されていますが、
あくまで「1」という数値のみを入力し、セルの表示形式をユーザー定義によって
見た目にあのようにしているだけです。

そこが原因であれば余計なお世話を焼いてしまったようなので
単に1・２・・・という数値に変更してみてください。

他の原因ならごめんなさいね。m(_ _)m

回答No.5

MackyNo1

週ごとの集計をする場合、通常は各月第5週までありますが、日曜から土曜までの範囲のデータを月始めと月末の週は月を分離して集計するなら以下のような数式になります。

例えばE9セルに1月なら「１」と入力してあるなら以下の式を入力して下方向にオートフィルしてください。

=SUMPRODUCT((MONTH($A$2:$A$1000)=$E$9)*(INT((DATE(2012,$E$9,1)-1)/7)+ROW(A1)-1=INT(($A$2:$A$1000-1)/7)),$D$2:$D$1000)

お礼

返信ありがとうございます！

すみません。一か月は５週あるのですね。
てっきり、４週だと思っていました。
本当に私アホですね＞＜

MackyNo1さんの式を元にエクセルを作成してみたのですが。
うまくオートフィルができませんでした；；
まず、E9に１月の第一週に式を入れ、
　　オートフィルで下に６月まで伸ばし、　
　　月を１，２，３，４，５、６と変更し
次にE9のデータを７月の第一週にコピーし
　　７月から１２月へ下にオートフィルし、
　　月を１→７，８，９，１０，１１，１２と月を変更しました。

しましたところ、以下のようになりました。
ダウンロードパスワード　:　1234
http://ux.getuploader.com/okwave123/download/1/MackyNo1%E3%81%95%E3%82%93.xlsx

回答No.4

tom04

No.2です！
たびたびごめんなさい。
前回の投稿で間違いがありました。
前月末と今月初日が同じ週になる場合の処理が大きく違っていました。
もう一度画像をUPさせていただきます。
今回は作業列は1行だけとします。
↓の画像で作業列E2セルに
=IF(MONTH(A2)=1,WEEKNUM(A2),IF(WEEKNUM(EOMONTH(A2,-1))=WEEKNUM(EOMONTH(A2,-1)+1),WEEKNUM(A2)-WEEKNUM(EOMONTH(A2,-1))+1,WEEKNUM(A2)-WEEKNUM(EOMONTH(A2,-1))))
という数式を入れオートフィルでずぃ～～～！っと下へコピー！

「月ごとの利益」は前回同様でOKで、
画像の「○月の利益」のセルの表示形式はユーザー定義から
0月の利益
として単に数値のみを入力
H2セルに
=SUMPRODUCT((MONTH($A$2:$A$1000)=G2)*($D$2:$D$1000))
としてそのままオートフィルで下へコピー！
J2セルはH2セルの数式をそのままコピー＆ペーストしてオートフィルで下へコピー！
これは前回同様です。

週ごとのH10セルに
=SUMPRODUCT((MONTH($A$2:$A$1000)=1)*($E$2:$E$1000=G10),$D$2:$D$1000)
として下へコピー！

2月のH15セルはH10セルの数式をそのままコピー＆ペーストし
「1」の部分を「2」に変更し下へコピー！

この操作の繰り返しでどうでしょうか？

※　検証せずに投稿してごめんなさいね。m(_ _)m

お礼

tom04さん
何度も何度もお手数おかけいたします。
返信が遅くなりすみません。

tom04さんいう通りにやってみたのですが
結果が0となり
思うような結果にはならなかったのですが、、、
なにがいけなかったのでしょうか。。。

ダウンロードパスワード：1234
http://ux.getuploader.com/okwave123/download/2/tom04%E3%81%95%E3%82%93.xlsx

補足

tom04さんのを参考に作ってみました。
どうでしょうか・・・？
これで５週単位で記録が作れます＞＜

ダウンロードパスワード:1234
http://ux.getuploader.com/okwave123/download/3/%E5%AE%8C%E6%88%90%EF%BC%9F.xlsx

回答No.3

MackyNo1

>ただ、残念な事に本番のエクセルには売上にif(d12="","",b11-b12)のような計算式が入っており。
#value!エラーとなってしまいます

$D$2:$D$1000に文字列（空白文字列を含む）が入っていると掛け算できないため"VALUE!エラーが出ます。
このようなときは、文字列を無視する以下のようなSUMPRODUCT関数にします。

=SUMPRODUCT((MONTH($A$2:$A$1000)=E2)*1,$D$2:$D$1000)

また週ごとの集計とは、月がまたがる場合は（例えば日曜だけの週などが出ますが）同じ週を2つにわけてよいのでしょうか？

回答No.2

tom04

こんばんは！
一例です。
↓の画像で説明すると
「1月の利益」・・・のセルは表示形式をユーザー定義から
0月の利益
として単に「1」のように数値のみを入力しています。

I2セルに
=SUMPRODUCT((MONTH($A$2:$A$1000)=H2)*($D$2:$D$1000))
という数式を入れオートフィルで下へコピー！

K2セルはI2セルの数式をそのままコピー＆ペーストしてこれも下へコピー！
これで画像のような感じになります。

次に「週ごとの利益」に関して、Excelはその月の何週目という概念はないと思います。
そこで「WEEKNUM」関数を利用してみてはどうでしょうか？

WEEKNUM関数はシリアル値がその年の何週目になるか？という関数ですので、
（日曜日から始めるか？月曜日から始めるか？は選択できます。とりあえず日曜日からというコトで）

作業用の列を2列設けています。
作業列1のE2セルに
=IF(MONTH(A2)=1,WEEKNUM(A2),WEEKNUM(A2)-WEEKNUM(EOMONTH(A2,-1)))
という数式を入れオートフィルでずぃ～～～！っと下へコピー！

そして先月末と今月初めが同じ週になる場合がありますので、その場合は「0」が作業列1に入ってしまうと思います。
「0」を第1週とするために作業列2のF2セルに
=IF(E2=0,E2+1,E2)
としてこれもオートフィルでずぃ～～～！っと下へコピー！

画面の「○週目」のセルも表示形式はユーザー定義から　
0週目
として単に1～4・5までのから数値を入れています。

I10セルに
=SUMPRODUCT((MONTH($A$2:$A$1000)=1)*($F$2:$F$1000=H10),$D$2:$D$1000)
としてその月分だけオートフィルで下へコピー！

各月の1週目にはこの数式をコピー＆ペーストしても良いのですが、
仮に2月1週目のI15セルにそのまま貼り付けると
=SUMPRODUCT((MONTH($A$2:$A$1000)=1)*($F$2:$F$1000=H15),$D$2:$D$1000)
という数式になりますので、
この「1」の部分を「2」に変更し
=SUMPRODUCT((MONTH($A$2:$A$1000)=2)*($F$2:$F$1000=H15),$D$2:$D$1000)
として下へコピー！

この作業を各月で行っていきます。

※　前月末・今月初めが同じ週になる場合は考慮していません。
仮に前月末が水曜日・今月初めが木曜日となった場合は
前月最終週は　日～水まで
今月第1週目は　木～土まで
としています。
というコトは第5週目まである月も出てきます。

長々とかきましたが
参考になりますかね？m(_ _)m

回答No.1

noname#204879

》 事前に、勉強したところ、…

惜しい！もう少しでしたね。

》 月合計は・・・でいいのかな？

はい、そのとおりです。でも次の条件があります。
１．Ａ列は日付形式で入力（例: セル A2 は 2011/1/1）し、年は跨らないこと（2011年のみ、とか、2012年のみ）
２．範囲 E2:E7 および G2:G7 の書式は下記のように設定し、1～12 の数値のみを入力のこと
　　　　G/標準"月の売上"

お礼

ありがとうございます！
練習のエクセルではできました（感謝

ただ、残念な事に本番のエクセルには
売上にif(d12="","",b11-b12)
のような計算式が入っており。
#value!エラーとなってしまいます。

また、週単位の計算はどのようにしたら、できるのでしょうか、
お忙しいと思いますが是非ともお力を。。。

補足

if(d12="","",b11-b12)
を
if(d12="",0,....)
と0にしたところ。治りましたありがとうございます。
月合計は無事に解決しました。