月曜日~金曜日の 週、月合計を出したいのですがうまくできません><
どなたかご教授いただけないでしょうか。
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ベストアンサー
- 回答日時:
>しましたところ、以下のようになりました。
ダウンロードパスワード : 1234
このような質問サイト一般に言えることですが、上記のような他のサイトに誘導する(ソフトをインストールさせる)ような補足の仕方は好ましくありません。
例示レイアウトなら問題なく表示できることを検証していますので、もう一度回答を良く見直して再チャレンジしてみて下さい。
ちなみに、例示のレイアウトなら、E9セルには月を表す数字「1」を入力し(表示形式で0"月利益"とする)、F10セルに例示の式を入力して下方向に5つオートフィルすることになります。
No.7
- 回答日時:
No.2・4です!
MackyNo1さんが仰っているように別サイトを覗くような方法は本来好ましくないと思います。
ただ・・・
ダウンロードして圧縮ファイルを解凍してみました。
手元にExcel2003しかないせいかどうかわかりませんが、
意味不明のSheetが表示されてしまいました。
(一応互換ソフトはインストールしています)
そこで憶測での回答になります。
エラーではなく、「0」が表示されたのですよね?
というコトは、SUMPRODUCT関数のどこかの列が「FALSE」になっています。
作業列はちゃんと1~5(6が表示されることはないと思います)がちゃんと表示されているとすると
怪しいのは「○月の利益」のセルと「○週目」のセルです。
画像では「1月の利益」・「1週目」と表示されていますが、
あくまで「1」という数値のみを入力し、セルの表示形式をユーザー定義によって
見た目にあのようにしているだけです。
そこが原因であれば余計なお世話を焼いてしまったようなので
単に1・2・・・という数値に変更してみてください。
他の原因ならごめんなさいね。m(_ _)m
No.5
- 回答日時:
週ごとの集計をする場合、通常は各月第5週までありますが、日曜から土曜までの範囲のデータを月始めと月末の週は月を分離して集計するなら以下のような数式になります。
例えばE9セルに1月なら「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)
返信ありがとうございます!
すみません。一か月は5週あるのですね。
てっきり、4週だと思っていました。
本当に私アホですね><
MackyNo1さんの式を元にエクセルを作成してみたのですが。
うまくオートフィルができませんでした;;
まず、E9に1月の第一週に式を入れ、
オートフィルで下に6月まで伸ばし、
月を1,2,3,4,5、6と変更し
次にE9のデータを7月の第一週にコピーし
7月から12月へ下にオートフィルし、
月を1→7,8,9,10,11,12と月を変更しました。
しましたところ、以下のようになりました。
ダウンロードパスワード : 1234
http://ux.getuploader.com/okwave123/download/1/M …
No.4
- 回答日時:
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さんのを参考に作ってみました。
どうでしょうか・・・?
これで5週単位で記録が作れます><
ダウンロードパスワード:1234
http://ux.getuploader.com/okwave123/download/3/% …
tom04さん
何度も何度もお手数おかけいたします。
返信が遅くなりすみません。
tom04さんいう通りにやってみたのですが
結果が0となり
思うような結果にはならなかったのですが、、、
なにがいけなかったのでしょうか。。。
ダウンロードパスワード:1234
http://ux.getuploader.com/okwave123/download/2/t …
No.3
- 回答日時:
>ただ、残念な事に本番のエクセルには売上に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
- 回答日時:
こんばんは!
一例です。
↓の画像で説明すると
「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
- 回答日時:
》 事前に、勉強したところ、…
惜しい!もう少しでしたね。
》 月合計は・・・でいいのかな?
はい、そのとおりです。でも次の条件があります。
1.A列は日付形式で入力(例: セル A2 は 2011/1/1)し、年は跨らないこと(2011年のみ、とか、2012年のみ)
2.範囲 E2:E7 および G2:G7 の書式は下記のように設定し、1~12 の数値のみを入力のこと
G/標準"月の売上"
この回答への補足
if(d12="","",b11-b12)
を
if(d12="",0,....)
と0にしたところ。治りましたありがとうございます。
月合計は無事に解決しました。
ありがとうございます!
練習のエクセルではできました(感謝
ただ、残念な事に本番のエクセルには
売上にif(d12="","",b11-b12)
のような計算式が入っており。
#value!エラーとなってしまいます。
また、週単位の計算はどのようにしたら、できるのでしょうか、
お忙しいと思いますが是非ともお力を。。。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
文字列から英数字のみを抽出す...
-
エクセルで、A2のセルにA3...
-
エクセル1行おきのセルを隣の...
-
条件付き書式の色付きセルのカ...
-
自分の左隣のセル
-
エクセル関数またはVBAについて
-
SUMIF関数で、「ブランク以外を...
-
エクセルで、指定の値よりも大...
-
EXCELのcountif関数での大文字...
-
EXCELでマイナス値の入ったセル...
-
【Excel】4つとばしで合計する方法
-
何時から何時までを○○、何時か...
-
同一セル内の重複文字を削除し...
-
エクセルで特定のセル内にだけ...
-
セルを結合した時のエクセル集...
-
検索関数を使って検索窓を作成...
-
Excelでなぜこのような式をつか...
-
エクセルでエンターを押すと任...
-
エラー「#REF」の箇所を置き換...
-
AVERAGE関数とINDIRECT関数を使...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
SUMIF関数で、「ブランク以外を...
-
文字列から英数字のみを抽出す...
-
エクセル1行おきのセルを隣の...
-
自分の左隣のセル
-
エクセルで、指定の値よりも大...
-
セルを結合した時のエクセル集...
-
excelで、空白を除いてデータを...
-
エクセルで、A2のセルにA3...
-
エクセルで特定のセル内にだけ...
-
同一セル内の重複文字を削除し...
-
EXCELでマイナス値の入ったセル...
-
条件付き書式の色付きセルのカ...
-
エクセルでエンターを押すと任...
-
【Excel】4つとばしで合計する方法
-
EXCELのcountif関数での大文字...
-
エクセルに入力後、別シートの...
-
Excelで大量のセルに一気に関数...
-
週の労働時間を計算するエクセル
-
エクセルで年月日から月日のみへ
-
エラー「#REF」の箇所を置き換...
おすすめ情報