SUBTOTAL関数の不思議

質問

SUBTOTAL関数で期待する合計が出せません。

Ａ列　　　Ｂ列　　　Ｃ列　　　Ｄ列
品名　　　数量　　　単価　　　金額
Ａ　　　　　1　　　　100　　　　100
Ｂ　　　　　2　　　　200　　　　400
Ｃ　　　　　3　　　　300　　　　900
小計　　　　　　　　　　　　　　　0　→ここが期待どおりでない

Ｄ２セルの式は
=IF(A2="品名",SUBTOTAL(9,INDIRECT("D$2:D"&ROW()-1)),B2*C2)
でＤ５までコピーしました。
つまりＡ列が"小計"でないときは数量×単価を、"小計"と入力されていたらD2:D4をSUBTOTALで集計するつもりなのに結果が0になってしまいます。

ちなみにＤ５に
=SUBTOTAL(9,D2:D4)
と手入力しても結果は0でしたが、Ｄ列に金額を直接入力すれば小計は正しく表示されます。

理由がおわかりの方、教えてくださいませんか

回答No.1

Wendy02 · ベストアンサー

こんにちは。

もともと、SUBTOTALは、その計算範囲の中の数式に、SUBTOTALを感知すると、それを計算しないという特性を持っています。

=IF(A2="品名",SUBTOTAL(9,INDIRECT("D$2:D"&ROW()-1)),B2*C2)

それと、その式は「品名」ではなく、「小計」だとは思いますが、こういう統一式で、フィルダウンで、コピーしようとしても、そのような計算は使えません。

たとえばこのようにするには、

品名　　数量　　単価　　金額
A　　　　1　　100　　　　100
B　　　　2　　200　　　　400
C　　　　3　　300　　　　900
小計　　　　　　　　　　1,400
E　　　　4　　200　　　　800
D　　　　4　　300　　　1,200
E　　　　3　　400　　　1,200
F　　　　2　　500　　　1,000
小計　　　　　　　　　　5,600　（ただし累計です）

統一式で行おうとするなら、

D2に、
=IF(A2="小計",TEXT(SUM($D$1:D1),"#,##0"),B2*C2)
のようにして、D列を右揃えにして、フィルでドラッグコピーしてあげないと出来ません。

本来は、小計の部分にのみ、SUBTOTAL(9,...)　を使います。

お礼

回答ありがとうございます。またお察しのように
=IF(A2="品名",SUBTOTAL(9,INDIRECT("D$2:D"&ROW()-1)),B2*C2)
です

なるほどセルにSUBTOTALの文字があるだけで「ネストしている」と認識されてしまうのですね。

教えていただいた方法＋自行より上の小計累積を引き算する方法を考えてみます

回答No.3

nanndemoii

ご質問内容の数式とサンプルで確認したところ、長い数式も短い数式も答えとして
「1400」が戻りましたので、数式自体は問題ないのかな？と思ってみたり。

意表をついて計算方法が「手動」になっていませんか？

ツール→オプション→計算方法→「自動」にチェックは入っていますか？

お礼

「手動」ではなかったようです
回答ありがとうございました

回答No.2

mu2011

HELPを見ると次の条件がありましたので、範囲内にSUBTOTAL関数が使用されている為、計数されないのではないでしょうか。

※範囲内に他の集計値が挿入されている場合、ネストされている集計値は、計算の重複を防ぐために無視されまます。

案ですが、SUBTOTALをSUM関数に変更すれば計数しますが如何でしょうか。

お礼

回答ありがとうございました
SUBTOTAL関数はSUBTOTAL関数の値を無視することは知っていましたが、IF文で分岐してSUBTOTAL関数ではない計算式で求めた値は合計してくれるものと早合点していました。