Excel（エクセル）活用コミュのsumifs関数　合計対象範囲が2次元

タイトルの件、解が#valueとなる原因は、
sumifs関数の合計対象範囲が2次元に対応していないからでしょうか？

具体的には下記計算式例。

=SUMIFS($B$2:$Z$14,$A$2:$A$14,$AA2,$B$1:$Z$1,">"&$AB$1,$B$1:$Z$1,"<="&$AC$1)

行方向Ａ列に条件範囲１（例えば部品番号）、
列方向1行目（タイトル行）に条件範囲（例えば、日付）２、３　があるＤＢ形式のエクセル２００７で、

合計対象範囲を2次元、例えば$B$2:$Z$14
条件範囲１を$A$2:$A$14、条件１　"="&$AA2

条件範囲2を$B$1:$Z$1、条件2 ">"&$AB$1

条件範囲3を$B$1:$Z$1、条件2 "<="&$AC$1

ご教授頂ければ幸いです。

コメント(16)

最初
全て
最新の40件

[1] mixiユーザー 11月19日 12:58

これを2次元と呼ぶのかは知りませんが、引数の指定の仕方に間違いがあるからですね。

sum関数などと違い、大半の関数では複数範囲を指定するためにカンマで区切ってしまうと、本来入るべき種類の引数ではないとしてエラーになります。

また、sumif系の条件は一つの値を基準にしますので、(これを次元と呼んでるのかな？)範囲にある複数の値を指定すると、わかってもらえません。(ワイルドカードは別)

質問の意図をくめてるか分かりませんが、原因としてはそういったところです。

[2] mixiユーザー 11月19日 13:35

伊蔵さま

次元の定義はご察しの通りで、質問の意味もご理解頂いていると思います。

>引数の指定の仕方に間違い・・・
sumifs関数で今回の例では、どのように引数を指定すれば解が得られますか？
それとも、sumifs関数では解が得られないということでしょうか？

[3] mixiユーザー 11月19日 16:21

試してはいませんが、SUMPRODUCT関数を使えば簡単に出来るかもしれませんよ

[4] mixiユーザー 11月19日 17:29

マリ男さま

=SUMPRODUCT($B$2:$Z$14,$A$2:$A$14=AA2,$B$1:$Z$1>$AB$1,$B$1:$Z$1<=$AC$1)

結果は#value　となってしまいました・・・

下記、配列関数では解が得られましたが、この関数、ブランクが配列内にあると、たまに？エラーとなってしまうことがあるので、sumifs関数でトライしている次第です。

{=SUM(($B$2:$Z$14)*($A$2:$A$14=AA2)*($B$1:$Z$1>$AB$1)*($B$1:$Z$1<=$AC$1))}

[5] mixiユーザー 11月19日 17:33

SUMPRODUCT関数の使い方は調べられましたか？

=SUMPRODUCT(($B$2:$Z$14)*($A$2:$A$14=$AA2)*($B$1:$Z$1>$AB$1)*($B$1:$Z$1<=$AC$1))

SUM関数でも
=SUM(INDIRECT(ADDRESS(MATCH($AA2,$A$1:$A$14,0),MATCH($AB$1,$A$1:$Z$1,0)+1)&":"&ADDRESS(MATCH($AA2,$A$1:$A$14,0),MATCH($AC$1,$A$1:$Z$1,0))))

[6] mixiユーザー 11月19日 21:36

>>2
昼の回答は私の勘違いですね。帰宅してPCで見たら指定自体はおかしくありませんでした。
ご推測のとおり、行方向と列方向からの指定によって対応すべきセルが見つけられない状態になっているようです。
勘違いでお気を悪くさせてしまい、大変申し訳ありません。

ところで、これは素朴な疑問なのですが、例示されている図では行列ともに重複項目がないように見えますが、
本来は重複項目がある作りになっているのですか？

ふと、ある日のある部品を探すだけならindexでいいんじゃないか？などと思ったもので…。

[7] mixiユーザー 11月20日 03:12

マリ男さま

sumproduct関数の区切り、勘違いしていました。

sum関数にアドレス関数、マッチ関数の組み合わせ、
このような関数組み合わせは使ったことがないので、
大変参考になりました。

ただ、条件範囲（日付）が連続していないので、偶々存在しない日付を
条件にしてしまうと、エラーとなってしまいますが・・・

[9] mixiユーザー 11月20日 03:19

伊蔵さま

重複はありません。
指定期間内の部品番号別合計を出すのが目的です。

[10] mixiユーザー 11月20日 03:28

>ただ、条件範囲（日付）が連続していないので、偶々存在しない日付を
>条件にしてしまうと、エラーとなってしまいますが・・・
それは、日付の部分のMATCH関数の照合の種類を変更すれば・・・

[11] mixiユーザー 11月20日 03:31

>>８
迅速な回答、驚きました。私は偶々夜更かししていますが・・

さて、ご指摘の件、それで済めばいいのですが、実は
指定期間内の合計を出すのが目的で、
この開始日と終了日は条件範囲に存在するか否かに関係なく
優先的に指定されるもです。
それでも合計を算出する必要があるので、この場合、sumproduct関数の方が、
適しているように思いますが・・・

[13] mixiユーザー 11月20日 03:47

>11
さすがに、この時間では頭が回らなかったようで
先程読み返してみて気付きました
なので、>10で書いたようにMATCH関数をいじれば出来ると思いますよ

どちらの式が適しているかは判断して下さい

[14] mixiユーザー 11月20日 08:46

DSUM使った方が早いかも。

[15] mixiユーザー 11月20日 15:07

> 列方向1行目（タイトル行）に条件範囲（例えば、日付）２、３　があるＤＢ形式のエクセル２００７で

この表はデータベース形式ではありませんよ。その点も確認されると、いいかもしれません。

存在しない日付が指定された場合でも、エラーが出ないようにとのことですが、[5]のマリ男さんのSUM関数のほうは、MATCH関数の第3引数を「1」にすると、一見良さそうに見えたのですが、期間の開始日のほうも存在しない日付の場合は、期間より前の日付が含まれてしまうようでした。
私は、OFFSETとMATCH関数を利用しようと思ってやろうと思いましたが、同じ原因でうまく行きませんでした。何か解決法があるのかなぁ？

で、SUMPRODUCTのほうは、本来の利用法以外の使い方ですから、難しいですよね。私の場合は、次のような書式で利用します。ちゃんと答えが出ますでしょうか？　お試しください。

=SUMPRODUCT(($A$2:$A$14=$AA2)*($B$1:$Z$1>=$AB$1)*($B$1:$Z$1<=$AC$1),$B$2:$Z$14)

[16] mixiユーザー 11月20日 16:06

まじんさま

MATCH関数の第3引数を「1」にしても、うまく行かないので、悩んでいました。

ご提案頂いたsumproduct関数、上手く行きました。

有り難うございます。

ログインすると、残り2件のコメントが見れるよ