[[20140811224311]]　『月別合計する方法』（もみじ坂）　ページの最後に飛ぶ

『月別合計する方法』（もみじ坂）

度々すみません。
複数条件で月別の合計を出したいのですが、

関数がいいのか、マクロがいいのかは悩むところです。
何せデーターが多いので、どちらが適切でしょうか？　
5000行ほど。。。。。

意見を頂けますとうれしいですが。

分かりましたら教えてください。
可能でしたら素人でも編集できるレベルですともっと助かります。
よろしくお願いします。

シート6　の　日付は　日付型です。
シート5　月は　数字型です。「月」は書式設定で付けました。

シート6

 	[a]	[b]     	[c]	[d]	[e]	[f]	[g]	[h]	[i]	[j]	[k]
 	NO	日付     	TEL	名前	NO	品名	数量	単価	ｃｓ	ｐｃ	金額
 [1]	140101 	2014年1月1日	8768 		A02					10 	
 [2]	140101 	2014年1月2日	8768 		A02					20 	
 [3]	140101 	2014年1月5日	8768 		A03					30 	
 [4]	140101 	2014年2月5日	2913 		A02					40 	
 [5]	140102 	2014年2月5日	2913 		A02					50 	
 [6]	140102 	2014年2月6日	2913 		A04					60 	
 [7]	140102 	2014年3月5日	2913 		A02					70

★ヤリタイこと
シート5　Ｂ1　が空欄の時　月別＆商品別の合計を

 	[a]	[b]	[c]	[d]	[e]	[f]	[g]	[h]	[i]	[j]	[k]	[l]	[m]	[n]
 [1]														
 [2]	NO	商品名前									1 月	2 月	3 月	4 月
 [3]	A01					30 								
 [4]	A02					30 				   	          30 	90 	70 	
 [5]	A03					30 					          30 			
 	A04					30 						        60

★シート5　Ｂ1　に　2913（ＴＥＬＮＯ）　を入力されたとき
ＴＥＬ別＆商品別＆月別　の合計

 	[a]	[b]	[c]	[d]	[e]	[f]	[g]	[h]	[i]	[j]	[k]	[l]	[m]	[n]
 [1]		2913												
 [2]	NO	商品名前									1 月	2 月	3 月	4 月
 [3]	A01					30 								
 [4]	A02					30 				　　		         90 	70 	
 [5]	A03					30 								
 	A04					30 					                 60

こんな風に結果を出せる方法ありますか？
分かりましたら教えてくださいよろしくお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 コメントが付かないですね。

 K3以降だけ作成されれば良いのですよね？

 数式でやるなら、基本的にはSUMPRODUCT関数。
 それが重い様なら、やはりVBAでしょうか。

 シート5の2行目に入力されているデータと同じになるように
 MONTH関数やTEXT関数を使ってシート6のB列を変換して比較してみてください。

 まずは、月別＆商品別の合計を出す式を作って埋め込んでみられてはどうでしょう？

 SUMPRODUCT関数で合計を出すには、こちらをご参考に。
http://www.excel.studio-kazu.jp/lib/e3h/e3h.html
 ライブラリ『SUMIFとSUMPRODUCT』
 　
（HANA） 2014/08/12(火) 14:25

（HANA）様

回答をありがとうございます　・:*(〃・ｪ・〃人)*:・
返信遅くなってすみません。

やりたいことが上手くまとまらず糸がこんがらがってるとき出題したので、
丸なげ感がでてレスが付かなかったんでしょか　(*´ノД｀)

SUMPRODUCT　も　マクロも　調べてますよ～　(´ﾟдﾟ`)

SUMPRODUCT　の場合は　月の部分　参照方法教えていただけますか？

Dictionary　で　合計しても　やはり　月のところが　分からなかったです。

SUMIFはまだ試していないので、やってみます。でも見た感じ月のところで
ひっかかりそうです

理解度がとろいですが、頑張りますので、お付き合いいただけますとうれしいです　(。´･ω･)
（もみじ坂） 2014/08/13(水) 09:04

 ＞SUMPRODUCT　の場合は　月の部分　参照方法教えていただけますか？
 それぞれのセルがどの様に入力されているかによって数式が変わります。

 シート6のB列はシリアル値（数式バーに　2014/1/1　と表示される）だと思いますが
 シート5のK2セルはどの様に入力されているでしょうか？

 SUMPRODUCT関数を使う場合
 =SUMPRODUCT((NOが一致するか)*(月が一致するか),個数セル範囲)
 って感じになると思います。

 NOが一致するか？　の部分は (シート6のA列のセル範囲=A3)　で疑問は無いと思います。
 月が一致するか？　の方は
 　K2に「1」と入っている場合　　　　　MONTH(シート6のB列のセル範囲)=K2
 　K2に「1月」と入っている場合　　　　TEXT(シート6のB列のセル範囲,"m月")=K2 とか MONTH(シート6のB列のセル範囲)&"月"=K2 等
   K2にもシリアル値で入っている場合　 MONTH(シート6のB列のセル範囲)=MONTH(K2)
 と言った感じになると思います。

 「=」で比較するので、同じとみなしたいセルの値～2014年1月1日～が
 ～1 月～と表示されているセルに入力されている値と同じになる様にして下さい。

 Dictionaryを使う場合も同様に考えてもらうと良いと思います。
 　　同じとみなしたいセルの値～2014年1月1日～が、シート5の何列目に有るのかを調べるには
 　　シート5の2行目に入力されているのと同じ値に変換してから　調べるのが手っ取り早いと思います。

 実際のセルの値は何か？といった点の説明が不足しているのと
 パソコンのスペック・使用頻度・その他の処理との兼ね合い・作成者の性格
 等によって「何が適切か」は変わってくると思いますので
 レスがつきにくかったのかもしれません。
 　
（HANA） 2014/08/13(水) 09:32

（HANA）様

 ありがとうございます～　ヾ(嬉　ω　嬉)ノ
 分かりやすい説明で、大分わかってきました。
 でも、結果は伴っていません　il||li _|￣|〇 il||liガクッ

 ＞シート6のB列はシリアル値（数式バーに　2014/1/1　と表示される）だと思いますが
 ＞シート5のK2セルはどの様に入力されているでしょうか？
 説明不足だったんですね　(*´ノД｀)

 すみません追記しました。

 シート6　（Ｂ列）の　日付は　日付型です。 
 シート5（Ｋ2）　月は　数字型です。「月」は書式設定で付けました。

 自分でチャレンジしてみましたが、エラーでしたので、質問をしたんです
 テストデータでは＃Ｎ/Ａ
 元データーでは＃ＶＡＬＵＥ！

 =SUMPRODUCT((MONTH(Sheet6!$B$2:$B$8)=K2)*(Sheet6!$E$2:$E$8=A3)*(売上!$J2:$J5000))

 データーは毎月500行ぐらい追加する感じです。

 Dictionaryは関数が成功しましたら教えていただけますとうれしいです　(´｡　ω　｡｀)

 日付は苦手意識があるので、いまいち考えが浮かばないです　(´ﾟдﾟ`)
（もみじ坂） 2014/08/13(水) 21:22

 ＞自分でチャレンジしてみましたが、エラーでしたので、質問をしたんです
 その時の数式と共に、その様に書いておいてもらえたらよかったと思います。
 「全然違う！！」なんて厳しい言葉を受けるかもしれませんが
 無駄な時間は減ると思いますので。

 ＞=SUMPRODUCT((MONTH(Sheet6!$B$2:$B$8)=K2)*(Sheet6!$E$2:$E$8=A3)*(売上!$J2:$J5000))
 の数式は変ですよね？

 最初の二つは、Sheet6シートの2～8行目なのに　最後の一つだけ　売上シートの2～5000行になっています。

 ＞テストデータでは＃Ｎ/Ａ
 それぞれのセルの個数があっていなかったのではないでしょうか？
 あるいは、元データに　#N/A　エラーがある。

 ＞元データーでは＃ＶＡＬＵＥ！
 売上!$J2:$J5000　に、文字が混ざっていませんか？
 たとえば、=IF(○○,"",××)なんて式が入っていた場合
 ○○がTRUEの時「""」に分岐しますが、「""」は文字です。
 なので「*」で掛け算させようとすると値ではないので #VLALUE!エラーが出ます。

 まずは　簡単な例をご自身で作って　数式の雰囲気をわかってから
 実際のデータに合わせていくのが良いと思います。

 元データとして　A列に日付、B列にNO、C列に数値
 条件として　D1に月、D2にNO　を入れることにします。

 すると、数式は =SUMPRODUCT((MONTH(A2:A9)=D1)*(B2:B9=D2),C2:C9) 
		[A]	[B]	[C]	[D]
	[ 1]	日付	NO	値	1
	[ 2]	1月1日	A	1	A
	[ 3]	1月2日	B	2	
	[ 4]	1月3日	A	3	
	[ 5]	1月4日	B	4	
	[ 6]	12月1日	A	5	
	[ 7]	12月2日	B	6	
	[ 8]	12月3日	A	7	
	[ 9]	12月4日	B	8	
	[10]				
 この例だと、2行目と4行目が条件に合うので「4」が返されます。

 この数式は
 E2に =MONTH(A2)=D$1
 F2に =B2=D$2
 G2に =E2*F2
 H2に =PRODUCT(G2,C2)
 9行目までフィルドラッグして、=SUM(H2:H9)　の結果と一致します。

 分解して一つずつ確認してみると、どこでエラーが出ているのかも
 わかりやすくなると思います。
 　
（HANA） 2014/08/13(水) 23:17

（HANA）様

ありがとうございます　・:*(〃・ｪ・〃人)*:・
お陰様で欲しい結果を経ることができました。
いくつか条件を追加しないと完成しないですが、
今のところは大満足です。

＞最初の二つは、Sheet6シートの2～8行目なのに　最後の一つだけ　売上シートの2～5000行になっています。

へんですね　(´ﾟдﾟ`)　コピペ―して使用したので、見逃してしまいました。

＞元データーでは＃ＶＡＬＵＥ！
なぜかわかりませんが、データの途中に＃ＶＡＬＵＥがあったんです。
それを消しましたら、合計がちゃんんとできました。

＞分解して一つずつ確認してみると～
そうなんですね、理由がわかってすっきりしました。
大文字や小文字の違いとかも計算されないのですね　(*´ノД｀)

データーをチェックして直さないといけないかもです。
でも、これさえできればマクロも少しは楽になります。
大変助かりました　(´｡　ω　｡｀)　ありがとうございます。

こんな感じで、マクロも教えていただけますか？

（もみじ坂） 2014/08/14(木) 08:50

 できましたか、良かったです。
 あとは、番号入力セルに入力が有るか無いかで
 IF関数で分岐させて下さい。

 マクロの方は、どこまでできてますか？

 最初に、それぞれのNOが  シート5の何行目に出てくるか
 登録が必要と思います。
 月の方も  登録しても良いと思いますし、
 順番に並んでいると思うのでその必要は無いかもしれません。

 (HANA)

＞マクロの方は、どこまでできてますか？

う～んどこていいますか。理解から始めないといけない気がします。
まとめるのに少し時間をください。

その前に
Excelの　Match　と　VLookup　関数は　　　
（値を検索する系）
マクロに置き換えると
Application.Match
Application.VLookup

はあるのに
合計する関数　SUMPRODUCT　ですとかは

マクロに置き変えることはできないのですね　（？？）
調べましたが、見つかりませんでした。

ないものなんですか？

（もみじ坂） 2014/08/14(木) 11:32

 ヘルプに、VBAで使えるワークシート関数の一覧があります。
 そこに載ってないものは、「Application.」をつけても使えません。

 ただ、EVALUATE関数と組み合わせると使えます。
 過去ログにもありますので、探してみて貰えると良いと思います。

 Dictionaryは、集計をするツールではないので、
 先に使わないマクロを考えてみてはどうでしょう？

 具体的には、マッチ関数で  シート5のA列を探すと
 該当のNOが何行目に出てくるかわかります。

 月がK列から1月を先頭に並んでるなら
 K列は11列目なので  MONTH(該当の日)+10  で列が特定できます。

 交差する所に  値を足して行けば良いです。

 (HANA)

(HANA)様

ありがとうございますヾ(嬉￫ω￩嬉)ノ

探してみますね
そして新しいレスをたてますので、
そちらともよろしくお願いします。
（もみじ坂） 2014/08/14(木) 19:26

コメント返信：

[ 一覧(最新更新順) ]

[[20140811224311]] 『月別合計する方法』（もみじ坂） ページの最後に飛ぶ

[[20140811224311]]　『月別合計する方法』（もみじ坂）　ページの最後に飛ぶ