ＳＵＭPRODUCTでエラーになってしまいます。

質問

関数を少しづつ勉強しながら集計表作成の業務を同時進行しています。
初心者です。

今作ってるのは複数条件の合計金額を求めたくて、

SUMPRODUCT(('4月'!D4:D38="*"&$C135&"*")*('4月'!$H4:H38=C58)*('4月'!E4:E38))
を作ったのですが、どうやらSUMPRODUCTだと「～を含む」の機能が使えないみたいなので
色々調べて

SUMPRODUCT((ISNUMBER(FIND($C$135,'4月!$D$4:$D$38)))*('4月!H4:H38=C61)*('4月!E4:E38))
が完成しました！

そして、更に右にコピーするとシートの月も５月、6月・・と変わるようにしたかったので
前回ここで教えてもらった関数を参考に
SUMPRODUCT((ISNUMBER(FIND($C$135,INDIRECT(MOD(COLUMN(C1),12)+1&"月!$D$4:$D$38))))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!H4:H38=C61))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!E4:E38)))

を作りました。

でも・・・エラーになってしまうのです。
セルにエラー表示がされるのではなく正しく直るまで「修正しなさい」って出るバージョンの
エラーです。。。。
もう何がいけないのかさっぱりで・・・・・

あと一番上の原型の関数からなのですが、セル番地を列で指定したいのですが、
D:DとかH:Hにするとセルのエラーになってしまいます。
こちらも何でエラーになるのかわからなくて困っています。

どなたか詳しい方教えて下さい。

回答No.1

MackyNo1 · ベストアンサー

>前回ここで教えてもらった関数を参考に
SUMPRODUCT((ISNUMBER(FIND($C$135,INDIRECT(MOD(COLUMN(C1),12)+1&"月!$D$4:$D$38))))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!H4:H38=C61))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!E4:E38)))
を作りました

数式そのものが成立していないためです。

INDIRECT関数の引数は文字列ですので「”」と「”」で囲む必要があります（文字列なのでオートフィルしても同じセル番地が指定できますので、以下のような相対参照の数式でもOKです。

=SUMPRODUCT(ISNUMBER(FIND($C$135,INDIRECT(MOD(COLUMN(C1),12)+1&"月!D4:D38")))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!H4:H38")=C61)*INDIRECT(MOD(COLUMN(C1),12)+1&"月!E4:E38"))

お礼

何度もお付き合いいただきありがとうございました！
また質問させていただく事が出てくると思いますが、
お時間有りましたらお付き合いよろしくお願い致します。
ありがとうございました。

補足

ご回答ありがとうございます。
早速この数式をそのまま使わせていただいたのですが・・・
＃VALUEのエラーになってしまいます。
もしかして私の元々の指定番地がおかしいのかもです・・
もう少し探ってみます！

あと追質問で申し訳ありません。
二つ目の条件のC61を複数にする事も可能ですか？
例えば、C61：C70とかです。

回答No.10

某HN クロメート(Chromate)（@CoalTar）

#2です。INDIRECT関数の中に関数を入れた場合配列にならないと思いましたが、
解決策が出されましたね。
INDIRECT関数はできれば使いたくないので、(関係しない新たな別ブックを開いて編集しても計算が走る)
1～12月と有限なので CHOOSE関数で通してみました。
=IF($C$135="","",SUMPRODUCT(ISNUMBER(FIND($C$135,
CHOOSE(COLUMN(A1),'1月'!$D$4:$D$38,'2月'!$D$4:$D$38,'3月'!$D$4:$D$38,'4月'!$D$4:$D$38,'5月'!$D$4:$D$38,'6月'!$D$4:$D$38,
'7月'!$D$4:$D$38,'8月'!$D$4:$D$38,'9月'!$D$4:$D$38,'10月'!$D$4:$D$38,'11月'!$D$4:$D$38,'12月'!$D$4:$D$38)))
*ISNUMBER(MATCH(CHOOSE(COLUMN(A1),'1月'!$H$4:$H$38,'2月'!$H$4:$H$38,'3月'!$H$4:$H$38,'4月'!$H$4:$H$38,'5月'!$H$4:$H$38,'6月'!$H$4:$H$38,
'7月'!$H$4:$H$38,'8月'!$H$4:$H$38,'9月'!$H$4:$H$38,'10月'!$H$4:$H$38,'11月'!$H$4:$H$38,'12月'!$H$4:$H$38),$C$61:$C$71,0)),
CHOOSE(COLUMN(A1),'1月'!$E$4:$E$38,'2月'!$E$4:$E$38,'3月'!$E$4:$E$38,'4月'!$E$4:$E$38,'5月'!$E$4:$E$38,'6月'!$E$4:$E$38,
'7月'!$E$4:$E$38,'8月'!$E$4:$E$38,'9月'!$E$4:$E$38,'10月'!$E$4:$E$38,'11月'!$E$4:$E$38,'12月'!$E$4:$E$38)))
配列は1次元なので少しは軽いのかもしれませんが、計算が重いことに変わりはないと思います。
CHOOSE関数部分を「名前の定義」で名前付けしてやれば、もう少し軽くなります。
やはり、作業用のシートなどに各月のデータを引っ張っておいたほうが良いのではないでしょうか？
作業用のセルを使うことが計算速度を上げるポイントです。

お礼

ご回答ありがとうございます。
なるほど!
使う関数によって同じ計算でも重さが変わるんですね！
はい・・・
データを引っ張ってやってみることも試みてるのですが、
どうも頭が固いようでどこで段階をつければうまくまとまるのか
よくわからなくなってしまうんです・・・

すこしづつ慣れていけるようにします！！
ありがとうございました！

回答No.9

kagakusuki

回答番号ANo.4です。

>「&"月!"&」　＋　セル範囲で訂正してやってみたのですが、今度は＃REF！になってしまいました・・・

　申し訳御座いません。
　御質問の内容を少々勘違いしておりました。
　この内容ですと、ANo.1様の方法が正解ではないかと最初は思いましたが、どうやらSUMPRODUCT関数には、D4:D38の様に縦方向に並んだセル範囲と、COLUMN(C1)の様に列番号を取り出す関数とを併用しますと、「指定しているセル範囲が正しくない」と判断してしまうというバグがある様です。
　ですから、COLUMN関数ではなく、COLUMNS関数を使用して、月数を指定されると良いと思います。
　例えば、4月の合計金額を表示させるセルがA列に存在している場合には、次の様な関数となります。

=SUMPRODUCT(ISNUMBER(FIND($C$135,INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!D4:D38")))*(INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!H4:H38")=C61)*INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!E4:E38"))

　尚、次の様な関数

=SUMPRODUCT(ISNUMBER(FIND($C$135,INDIRECT(MOD(COLUMNS($A:C),12)+1&"月!D4:D38")))*(INDIRECT(MOD(COLUMNS($A:C),12)+1&"月!H4:H38")=C61)*INDIRECT(MOD(COLUMNS($A:C),12)+1&"月!E4:E38"))

でも、同じ結果を得る事は一応出来ますが、関数が入力されているセルの列番号と、参照先の列との関係を考えますと、最初に述べた

COLUMNS($A:A)+2

を使った関数の方が望ましいのではないかと、私には思えます。

　又、それとは別個に、各月のシート中のE4:E38の範囲内の何処かに、文字列データが入力されているセルか、或いは、関数を使用して空欄を表示させているセルが、含まれている場合にも、

*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!E4:E38))

の部分で、

(ISNUMBER(FIND($C$135,INDIRECT(MOD(COLUMN(C1),12)+1&"月!$D$4:$D$38))))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!H4:H38=C61))

に対して、数値ではなく、文字列を掛け算する事になるため、#VALUE! エラー値が表示される事になります。（関数によってではなく、単純に何も入力されていないだけのセルは、数値の0と同じ扱いになりますから、問題ないと思います）
　ですから、各月のシート中のE4:E38の範囲を全てチェックして、文字列が表示されない様に対処して下さい。
　又、どうしてもE4:E38の範囲内に、関数によって空欄となるセルが出来てしまう場合の対策としては、次の様な関数とされれば良いと思います。

=SUMPRODUCT(ISNUMBER(FIND($C$135,INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!D4:D38")))*(INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!H4:H38")=C61)*("0"&INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!E4:E38")))

　そして、上記の関数を列で指定する様にしますと、次の様な関数となります。

=SUMPRODUCT(ISNUMBER(FIND($C$135,INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!D4:D"&MATCH(9^99,INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!E:E")))))*(INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!H4:H"&MATCH(9^99,INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!E:E")))=C61)*("0"&INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!E4:E"&MATCH(9^99,INDIRECT(MOD(COLUMNS($A:A)+2,12)+1&"月!E:E")))))

お礼

ご回答ありがとうございます。
お忙しい沢山の解決策をありがとうございます。
今回のものは空白は出ないのですが、他の表で出るものも
あるので使い分けが出来て助かります！
ありがとうございました。

回答No.8

MackyNo1

＞二つ目の条件のC61を複数にする事も可能ですか？
例えば、C61：C70とかです。

これを実行するには、列配列を行配列に変換（2次元配列の利用）する、以下のような、さらに複雑な配列数式を使う必要があります。

=SUMPRODUCT(ISNUMBER(FIND($C$135,INDEX(INDIRECT(MOD(COLUMN(C4),12)+1&"月!D4:D38"),)))*(INDEX(INDIRECT(MOD(COLUMN(C4),12)+1&"月!H4:H38"),)=TRANSPOSE($C$61:$C$71))*INDEX(INDIRECT(MOD(COLUMN(C4),12)+1&"月!E4:E38"),))

入力後、Ctrl+Shift+Enterで確定してください。

ただし、このような数式を多用すると、再計算に時間がかかり確実にシートの動きが重くなりますので、実際に関数を入力すときや多数の関数を入力したシートを編集する場合は、セルの計算方法を「手動」にしておいて数式を入力し、必要に応じてF9キーで再計算すると良いと思います。

あるいは、数式は1つだけ入力しておいて、計算したい場合だけ数式をオートフィルコピーし、その部分は、最終的に「値」に変換しておく運用方法もあります。

お礼

何度もすみません。ありがとうございます。
Ctrl+Shift+Enter←こんな技もあるんですね！！
すごい！
あとは重くなってしまうところの改善・・・
手動の方法試してみます！
ありがとうございました！

回答No.7

MackyNo1

No1の回答の補足と訂正です。

回答時に数式を良く検証していなかったのですが、INDIRECT関数を利用した場合にその範囲を配列として認識させるにはINDEX関数を併用する必要があります。

したがって、提示した数式は以下のようになります。

=SUMPRODUCT(ISNUMBER(FIND($C$135,INDEX(INDIRECT(MOD(COLUMN(C1),12)+1&"月!D4:D38"),)))*(INDEX(INDIRECT(MOD(COLUMN(C1),12)+1&"月!H4:H38"),)=$C$61)*INDEX(INDIRECT(MOD(COLUMN(C1),12)+1&"月!E4:E38"),))

お礼

出来ました出来ました！
INDIRECTとINDEXは組み合わせ方とかが難しいですね・・・
前にMackyNo1さんから教えてもらった
　INDEX('4月'!$E:$Z,,ROW(A1))と
SUMIF(INDIRECT(MOD(COLUMN(C1),12)+1&"月!$D:$D"),"*電池*",INDIRECT(MOD(COLUMN(C1),12)+1&"月!$E:$E"))
を置き換えで繋げられないかとやってみたのですがCtrl＋Vで結局どこに置けばいいのか
判らず仕舞いでした。。。
もう少し理解してからぢゃないと自分でつくるのは難しいですね・・

回答No.6

kagakusuki

回答番号ANo.4です。

　原因が判りましたが、今ちょっと回答を入力している時間がないため、後でまた回答させて頂きますので、質問は締め切らずにお待ちください。

お礼

はい！
お時間ある時で大丈夫ですのでよろしくお願い致します。
お待ちしております。

回答No.5

MackyNo1

>重くなるのであれば毎回範囲指定していくほうが良さそうです！

あまり大きな範囲を指定することは避けたほうが良いのですが、毎回異なるセル範囲を指定する必要はありません（予想される最大行数のセル範囲で指定すればOKです）。

>前回教えてもらった関数ですが、依然・・エラーを直すことが出来ず、この検証機能を使ってみたのですが、何が間違っているのかまでは出せず・・状態です。

実際に入力した数式を、数式バーからそのままコピー貼り付けして、その時にセルに表示されているエラー表示（#VALUEや#REFなど）を提示されれば、原因がわかると思います。

お礼

度々ご回答ありがとうございます。
>(予想される最大行数のセル範囲で指定すればOKです）。
これ！できました！
そうですよね・・・大体の最大を出せばよかったんですね！

回答No.4

kagakusuki

>セルにエラー表示がされるのではなく正しく直るまで「修正しなさい」って出るバージョンのエラーです。。。。

　関数の中の

「&"月!」　＋　セル範囲

の箇所の記述方法が間違っています。

「&"月!"&」　＋　セル範囲

の形式に訂正されて、

&"月!"&$D$4:$D$38
&"月! "&$H$4:$H$38
&"月!"&$E$4:$E$38

と言う様になさって下さい。

>セル番地を列で指定したいのですが、

　ANo.3様が仰る様に、Excel2007以降のバージョンであれば可能ですが、Excel2007よりも前のバージョンでは、SUMPRODUCT関数で扱う範囲を、列で指定する事は出来ません。
　もし、データが入力されている最下段の行が何行目になるのかが不明な場合には、次の様な関数とされると良いと思います。

=SUMPRODUCT((ISNUMBER(FIND($C$135,INDIRECT(MOD(COLUMN(C1),12)+1&"月!"&$D$4:INDEX($D:$D,MATCH("゛",$D:$D,-1))))))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月! "&$H$4:INDEX($H:$H,MATCH("゛",$D:$D,-1))=C61))*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!"&$E$4:INDEX($E:$E,MATCH("゛",$D:$D,-1)))))

　これは、

MATCH("゛",$D:$D,-1)

の部分で、D列で文字列が入力されているセルの中で、最も下にあるセルの行番号を求めた上で、「INDEX(列:列」と組み合わせる事で、「その列において『D列で文字列が入力されているセルの中で、最も下にあるセルと同じ行』にあるセル」を指定しています。
　因みに、"゛"の部分はD列に入力される可能性がない文字列であれば何でも良いのですが、濁点や半濁点が単独で入力される可能性はないため（故意に入力する場合は別ですが）、他の文字列と比べて確実性が高いと思います。
　例えば仮に、D列には、途中に空欄を挟みながら、D38セルまで文字列が入力されていた場合には、

MATCH("゛",$D:$D,-1)

の値は38となり、

INDEX($H:$H,MATCH("゛",$D:$D,-1))

の参照先はH38セルになりますから、

$H$4:INDEX($H:$H,MATCH("゛",$D:$D,-1))

の部分は、

$H$4:$H$38

と同じ事になります。
　尚、

MATCH("゛",$D:$D,-1)

の部分は、数値データが入力されているセルの行番号を取り出す事は出来ませんから、例えば文字列データはD38セルまでにしかなくとも、D99セルに数値データが入力されている、という様な場合では、数値データは無視されますから、

MATCH("゛",$D:$D,-1)

で求められる値は、99ではなく38になります。
　もし、数値データが入力されている最下段の行番号を求める場合には、

MATCH(9^99,$D:$D)

或いは

MATCH(MAX($D:$D)+1,$D:$D)

という具合に、その列に入力されている、どの数値よりも大きな数値を、検索値として用います。（こちらは逆に、文字列データが入力されているセルを無視します）

お礼

ご回答ありがとうございます。
「&"月!"&」　＋　セル範囲で訂正してやってみたのですが、
今度は＃REF！になってしまいました・・・
これは何が原因なんでしょう・・・
セル番地も列範囲も合ってるのですが、ダメです。。

皆さんのお礼に追質問で載せていただいたのですが、C61の答えの部分も
複数にしたいのですが、こちらもお時間ありましたらご伝授いただけると
助かります。

あと、セル番地の詳しいご説明ありがとうございます。
今使っているのは2003です。

教えてくださった関数をそのままコピーして使ってみたのですが、
こちらもREFエラーです・・・・
2003だとダメなんでしょうか？

回答No.3

MackyNo1

補足と参考までに。

＞D:DとかH:Hにするとセルのエラーになってしまいます。

Excel2003以前のバージョンを使用されているのでしょうか？

Excel2007以降のバージョンでは列全体を配列として指定することができますが、2003以前のバージョンではエラーとなります（行指定はできます）。

ただし、配列を使用した数式は、対象範囲を大きくすると、再計算に時間がかかり、シートの動きが重くなるなどの問題がありますので、2007以降のバージョンを使用する場合でも必要以上にセル範囲を大きくしないようにする必要があります。

また、複雑な数式作成の際のポイントは、簡単な数式の一部(セルの値やセル範囲など）に、計算式を当てはめる場合は、検索置換の機能を利用することをお勧めします。

すなわち、検索する文字列の部分や、置換後の文字列の部分は、数式の必要部分を数式バー上から選択して、Ctrl+Cでコピーして、置換ダイアログにCtrl+Vで貼り付けるなどの操作を行うと良いかもしれません（置換箇所が1か所の場合は、置換後の文字列部分をCtrl+Cでコピーし、Escキーからセル選択しなおして数式上で置換対象の部分を選択して、そのままCtrl＋Vで貼り付けます）。

また、複雑な数式の検証には、数式バー上で検証したい数式部分をドラッグして、F9キーを押すとその部分の数式の結果（配列の場合は複数のデータ）が表示されますので（ただし結果が大きな配列の場合は表示できません）、この操作で数式の問題点などを見つけることができます（解除はEscキー）。

お礼

再度ご回答ありがとうございます。
はい。使用しているのは2003です。
毎月データの量が違うので列指定がいいなぁ・・・と思っていたのですが、
重くなるのであれば毎回範囲指定していくほうが良さそうです！

そしてF9の検証機能！すごいですね！
こんな便利機能があったんですね！

前回教えてもらった関数ですが、依然・・エラーを直すことが出来ず
この検証機能を使ってみたのですが、何が間違っているのかまでは
出せず・・状態です。
もしお時間ございましたらご伝授いただけると助かります。
ありがとうございました。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

=SUMPRODUCT((ISNUMBER(FIND($C$135,
INDIRECT(MOD(COLUMN(C1),12)+1&"月!D4:D38"))))
*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!H4:H38")=C61)
*(INDIRECT(MOD(COLUMN(C1),12)+1&"月!E4:E38")))
で数式は入力できますが、INDIRECT関数に関数を使うと配列として返さないようです。

＞D:DとかH:Hにするとセルのエラーになってしまいます。
SUMPRODUCT関数は、配列を入力する必要があり、列範囲を入力できません。
早い話、列指定はできないってこと。

＞SUMPRODUCT(('4月'!D4:D38="*"&$C135&"*")
='4月'!D4="*あいうえお*"
としても、ワイルドカードの役目を果たしません。

=SUMPRODUCT(ISNUMBER(FIND($C$135,'4月'!$D$4:$D$38))
*('4月'!$H$4:$H$38=C61)*'4月'!$E$4:$E$38)
の参照があっているとして、
=SUMPRODUCT(ISNUMBER(FIND($C$135,
CHOOSE(COLUMN(A1),'1月'!$D$4:$D$38,'2月'!$D$4:$D$38,'3月'!$D$4:$D$38,'4月'!$D$4:$D$38,'5月'!$D$4:$D$38,'6月'!$D$4:$D$38,
'7月'!$D$4:$D$38,'8月'!$D$4:$D$38,'9月'!$D$4:$D$38,'10月'!$D$4:$D$38,'11月'!$D$4:$D$38,'12月'!$D$4:$D$38)))
*(CHOOSE(COLUMN(A1),'1月'!$H$4:$H$38,'2月'!$H$4:$H$38,'3月'!$H$4:$H$38,'4月'!$H$4:$H$38,'5月'!$H$4:$H$38,'6月'!$H$4:$H$38,
'7月'!$H$4:$H$38,'8月'!$H$4:$H$38,'9月'!$H$4:$H$38,'10月'!$H$4:$H$38,'11月'!$H$4:$H$38,'12月'!$H$4:$H$38)=C61)*
CHOOSE(COLUMN(A1),'1月'!$E$4:$E$38,'2月'!$E$4:$E$38,'3月'!$E$4:$E$38,'4月'!$E$4:$E$38,'5月'!$E$4:$E$38,'6月'!$E$4:$E$38,
'7月'!$E$4:$E$38,'8月'!$E$4:$E$38,'9月'!$E$4:$E$38,'10月'!$E$4:$E$38,'11月'!$E$4:$E$38,'12月'!$E$4:$E$38))
4月から始めたい場合は、CHOOOSE関数の第2引数以降の順番を入れ替えてください。

お礼

ご回答ありがとうございます。
この関数で出せました！

ですが・・
私肝心なところを飛ばしていて、C61の条件を複数にしたかったのです。
C61～C71という感じです。
普通にC61：C71と入れると計算してくれない様で・・・
もしお時間ございましたらご伝授いただけると助かります。