SUMPRODUCTで月毎の計の時に#VALUE!

質問

いつもお世話になります。
WIN7 EXCELL2010 です。
例えば添付図でB14に日付を入力するとJ列に月が替わるところで#VAUE!が表示されます。
ただしD列の個数を入力するとD列は正しく表示されます。
この#VALUE!を表示しない方法はないでしょうか。

３の行の各列には、
A =IF(B3="","",TEXT(B3,"mm"))

B C D は手入力

E =IF(OR(B3="",C3=""),"",TEXT(B3,"yymm")&C3)

F =IF(C3="","",VLOOKUP(C3,商品管理!$A$2:$B$211,2,FALSE)) & " "

G =IF(C3="","",VLOOKUP(C3,商品管理!$A$2:$C$211,3,FALSE)) & ""

H =IF(G3="","",D3*G3)

I =IF(ISBLANK(D3),"",SUM($H$2:$H3))

J =IF(MONTH(B3)=MONTH(B4),"",SUMPRODUCT((MONTH(OFFSET($B$2,0,0,COUNT(B:B)))=MONTH(B3))*OFFSET($H$2,0,0,COUNT(B:B))))

以上ですがご指導を仰ぎたいのですがよろしくお願いします。

回答No.4

kagakusuki · ベストアンサー

H列の値が関数で""となっている場合は、そのセルの値は0などの数値と扱う事が出来なくなりますから、文字列に対して掛け算をした場合と同様の状態となってしまい、#VAUE!が表示されます。
　但し、Excelでは

"0"

の様な数値を文字列データとしたものと同じ文字列に対してならば、四則演算は可能になります。
　これを利用して、次の様にしますとエラーとはならずに済ます事が出来ます。

=IF(MONTH(B3)=MONTH(B4),"",SUMPRODUCT((MONTH(OFFSET($B$2,0,0,COUNT(B:B)))=MONTH(B3))*(0&OFFSET($H$2,0,0,COUNT(B:B)))))

　但し、このままですと、例えばB3セルが1月の日付で、B4セルが空欄の場合、関数に拠らない純粋な空欄は数値の0、即ち1900年1月0日を表すシリアル値になりますから、J3セルには何も表示されない事になります。
　又、COUNT(B:B)で行数を求めたのでは、B列の途中に空欄のセルか、文字が入力されているセルがある場合には、日付が入力されている最下段の行を正しく求める事は出来ません。
　これらの弊害を排除するには、次の様にする方法があります。

=IF(TEXT($B3,"yyyy/m")=TEXT($B4,"yyyy/m"),"",SUMPRODUCT((TEXT($B$2:INDEX($B:$B,MATCH(9E+99,$B:$B)),"yyyy/m")=TEXT($B3,"yyyy/m"))*(0&$H$2:INDEX($H:$H,MATCH(9E+99,$B:$B)))))

　MATCH関数の照合の型を省略しますと、「『検査範囲内において、検査値よりも少しでも小さな数値が入力されているセルの中で、最後尾のセル』が検査範囲内で何番目のセルになるのか」を示す数が返されます。
　ですから、検査値に「『検査範囲内に含まれている最大値』よりも大きな数」を指定しますと、「『検査範囲内に含まれている最大値』よりも大きな数」よりも少しでも小さな数が入力されている最下段の行、即ち、数値が入力されている最下段の行が、検査範囲内で数えて上から何番目の行にあたるのかを示す数が返される事になりますから、これをCOUNT(B:B)の代わりに使う訳です。

　しかしながら、SUMPRODUCT関数は「繰り返し計算」を行う関数であるため、指定する行範囲に含まれる行数（或いは列範囲に含まれる列数）が多くなりますと、計算処理が重くなり、計算結果が出るまでに時間が掛かるという短所があります。
　このため、SUMPRODUCT関数を使わずに済む場合には、SUMPRODUCT関数の使用は避けた方が無難です。
　御質問の件に関しましても、SUMPRODUCT関数を使わずに、次の様な関数としますと、計算が重くならずに済みますので、こちらの方を使用される事を御勧め致します。（「B3セルの日付の翌月の1日よりも前の日付」の合計額から、「B3セルと同月の1日よりも前の日付」の合計額を差し引いた額を求める関数です）

=IF(TEXT($B3,"yyyy/m")=TEXT($B4,"yyyy/m"),"",SUMIF($B:$B,"<"&DATE(YEAR($B3),MONTH($B3)+1,1),$H:$H)-SUMIF($B:$B,"<"&DATE(YEAR($B3),MONTH($B3),1),$H:$H))

お礼

いつもお世話になります。
お示しいただいた数式で試しましたが
下記の数式を採用させていただきました。
=IF(TEXT($B3,"yyyy/m")=TEXT($B4,"yyyy/m"),"",SUMIF($B:$B,"<"&DATE(YEAR($B3),MONTH($B3)+1,1),$H:$H)-SUMIF($B:$B,"<"&DATE(YEAR($B3),MONTH($B3),1),$H:$H))

こういう時は当たり前的に　SUMPRODUCTを良く使うのですがこの数式では思いつきませんでした。
詳細にご説明していただき本当にありございました。
今後ともご指導よろしくお願いします。

回答No.3

MarcoRossiItaly

ああ、そうですね。No.1さんのおっしゃるとおり H 列で「""」を返しているから、このままじゃエラーですね。次式にすると、H 列にゼロを表示する代わりに、J 列のエラーは解消されるかもしれません。ベストアンサーは辞退します。

H列　=IF(G3="",,D3*G3)

お礼

早速ご回答いただきありがとうございます。

私のミスで画像を添付していなくて下記のアドレスで再質問させていただき御迷惑をおかけしました。
お許しください。
http://oshiete.goo.ne.jp/qa/7852709.html

回答No.2

MarcoRossiItaly

数式だけ見ててもよく分かりませんが、なんか月が変わるタイミングでだけ、その月の H 列にある何かの合計を数えてるのですね。

よく分かりませんが、SUMPRODUCT 関数で「#VALUE!」というエラー値が出る原因としてよくあるのは、「SUMPRODUCT(配列*配列)」という書式と「SUMPRODUCT(配列,配列)」の違いによって発生するものです。前者では文字列が配列の一部に含まれているとエラーになりますが、後者では文字列のある行を無視するのでエラーになりません。

この考えでご相談の数式を修正するなら、式中の「*」を「*1,」に直してみてください。ただし、あえてエラーを表示したい場合は、修正しないというのもアリかと。

今回の原因がこのせいだったとしたら、問題の文字列が存在しているのは、H 列でしょうね。

お礼

早速ご回答いただきありがとうございます。

私のミスで画像を添付していなくて下記のアドレスで再質問させていただき御迷惑をおかけしました。
お許しください。
http://oshiete.goo.ne.jp/qa/7852709.html

回答No.1

MackyNo1

#VALUE!エラーが表示されるのはH列に空白「文字列があるためです。
空白文字列を無視して（0とみなして）計算するにはJ列のSUMPRODUCT式の部分を以下のように変更する必要があります。

SUMPRODUCT((MONTH(OFFSET($B$2,0,0,COUNT(B:B)))=MONTH(B3))*1,OFFSET($H$2,0,0,COUNT(B:B)))

お礼

早速ご回答いただきありがとうございます。
ご指導いただいた数式で試してみました。
バッチリでしたが勝って言いますと空白部分かないのが非常に残念です。
私のミスで画像を添付していなくて下記のアドレスで再質問させていただき御迷惑をおかけしました。
お許しください。
http://oshiete.goo.ne.jp/qa/7852709.html