SUMIFS関数の質問

質問

エクセルで
A列のあたいがE1セルに一致し、B列の値が文字列"00"でないC列の数値を合計したい場合、

これまでエクセル2003の時は
=SUMPRODUCT((A2:A15=E1)*(B2:B15<>"00")*C2:C15)
のように書いてきました。
これをエクセル2010で、SUMIFS関数にしようと思い
=SUMIFS(C:C,A:A,E1,B:B,<>"00")
としてみました。
ところが、テストでわずか14行でやってみると答えが違うのです。

わたしのSUMIFS関数の理解があやまっているのでしょうか？
教えてください。

テストに使った2行目から15行はこんな感じです。E1セルには文字列 A があり、
=SUMPRODUCT((A2:A15=E1)*(B2:B15<>"00")*C2:C15)　　は27を
=SUMIFS(C:C,A:A,E1,B:B,"<>00")　　は28を返します。

A 00 1
A 01 2
A 02 3
A 03 4
A 04 5
A 05 6
A 06 7
B 07 8
B 08 9
B 09 10
B 10 11
C 11 12
D 12 13
D 13 14

回答No.3

bunjii · ベストアンサー

>わたしのSUMIFS関数の理解があやまっているのでしょうか？
SUMIFS関数の不具合（バグ）かも知れません。
条件2の"<>00"が目的通りに比較できないようです。
条件2が"00"のように等価の場合は正常に比較して値を返しますので、次のように変更すれば目的に合います。
=SUMIF(A2:C15,E1,C2:C15)-SUMIFS(C2:C15,A2:A15,E1,B2:B15,"00")

お礼

>等価の場合は正常に比較

こんなことがあるんですね、たしかにバグですね。
ご教示の式で目的を達成しました。
ありがとうございます。

回答No.4

real beatin（@realbeatin）

こんにちは。

COUNTIF関数やSUMIF関数の条件に
文字列式を指定する際は、
数式バーに書き込む数式をイメージすると
解り易い場合があります（数式バーでは”?*"等は使えませんが）。
例えば、
　"<>00"
という場合、
　=B1<>00
という数式の４桁め以降が条件にとしての文字列式
だと考えて、
この数式を数式バーに入力して確定したらどうなるでしょう。
　=B1<>0
という風にしか確定できません。
つまり、
　"<>00"
は、「数値としての 0 以外」を指す条件式です。
なので、"00"という文字列を除外することが出来ません。
残念なことに、
　"<>""00"""
のような条件式を試してみても、意味は同じようです。

ただ、
=SUMIFS(C:C,A:A,E1,B:B,"<>00*")
のように"*"を付け加えると文字列を判別してくれるようです。
今の処、このような数式しか思いつきませんが、
この課題の条件として、
「Ｂ列には３桁以上の文字は無い」
ということだったりすれば、十分使えるとは思います。

元々、文字列値を条件判別する目的で開発した関数ではないのでしょうね。
ただ、関数を新しくした時に、仕様を拡充する方向で考えてくれたら
良かったのになぁ、とは思います。
バグ、というのは寧ろ不自然でしょうね。
Excel数式とはかけ離れた高度な正規表現を使えるようにすれば、
不満の声は減るでしょうけれど、
難しくなっちゃってこの関数を使う人も減るのでしょうから。

以上です。

お礼

お礼がおそくなり、もうしわけありません。
=SUMIFS(C:C,A:A,E1,B:B,"<>00*")で判別してくれるんですね。
質問にあげた例ではOKでした。
ただ、実際には001や002もありうるので使えないのです。
ありがとうございました。

回答No.2

Chiquilin

SUMIF関数などの条件式は 数値化できる文字列は数値にしてから計算します。
昔からの仕様です。

=SUM(SUMIFS(C:C,A:A,E1,B:B,{"*",0})*{1,-1})

お礼

あろがとうございます。
ごご提示の数式で正しく27が返りました。
ただ、数式の意味が理解できないでおります。

回答No.1

mshr1962

A 00 1 というデータが加算されてるようですね。
B列は文字列でしょうか？

=SUMIFS(C:C,A:A,E1,B:B,"<>00")
のように、数値の文字列の比較の場合SUMIF、COUNTIF等の関数では
数値と文字両方で比較して、どちらかで一致するとTRUE（真）とみなしてしまうようです。
試しに、"<>00"を"<>01"に変えても、結果は28でした。
条件部分が文字列で記載する為のバグのようですね。

お礼

ありがとうございます。
バグですか・・・・。

補足

> B列は文字列でしょうか？

はい、数字もありますが、01や02があるのですべて数値ではなく文字列にしております。