エクセルでフィルターを使用時に使う式=SUBTOTAL(3,A5:A50)や=SUBTOTAL(9,A5:A50)

質問

エクセルでフィルター機能を使用して合計金額や件数を確認する式で=SUBTOTAL(3,A5:A50)や=SUBTOTAL(9,A5:A50)あります。
エクセルで作成した表でA列に営業所名、B列に得意先名、C列に売上げを入れた表があります。返品があった場合はC列にマイナスが発生します。
営業所毎にフィルターをかけた場合、マイナスの得意先がカウントされないようにするには、どのように数式を入れたらよいのでしょうか？

回答No.4

imogasi

フィルタをかける目的は、選択して、該当する明細を見たいということがあります。ですので下記は目的に合致するかわからないのですが
本来の条件に戻り
Ａ列がa,B列が正の条件で、集計する
=SUMPRODUCT((B2:B10>0)*(A2:A10="a")*(B2:B10))
で
結果
X y
a 1
a 3
a -3
a 4
がフィルタ結果場合で
８です。
ーー
私は＃１の方のおっしゃることが最もと思いますが。
ーー
＞マイナスの得意先がカウントされないようにするには
はどこまで考えて質問しているのか良くわからない。
Ａ　　２
Ａ　　３
Ａ　－６
Ｂ　　３
Ｂ　　２
Ｂ　－４
のときＡは合計はー１、Ｂは合計１であるが、マイナスでないＢだけ表示とか合計しろということなら、再質問してください。

回答No.3

Wendy02

こんばんは。

>マイナスの得意先がカウントされないようにするには、
得意先ごとの合計なら、当然、マイナス売上げも計上するのではありませんか？
余計なお世話ですね。（^^;

そのままで計算するには、補助列を設けるしかないのではないでしょうか？

= If(C2<0,C2, "")

というような数式を置いて、フィルハンドルでコピーをします。そうすると、数字として出てくるのは、マイナスのみになります。そして、やはり、表示しているものだけが出てきますね。

た上記の数式をF列だすれば、その集計を、SUBTOTAL関数で取ります。

=SUBTOTAL(2,F5:F50)　　←件数を出すなら、ここでの引数は、3　ではなくて、2 ですから、間違えないでください。

=SUBTOTAL(9,F5:F50)

そして、C列の合計に（マイナス値を）足せばよいです。

回答No.2

zap35

１）A列とC列にオートフィルタをかけて、A列は営業所、C列はオプションで「０より大きい」を条件で絞り込む

２）表示されている行の０以上のセルだけカウント、合計するユーザ定義関数を用いる。ちょっとお遊びで作ってみました。
以下のマクロをALT＋F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。マクロの実行はワークシート画面に戻って
　=irrSubtotal(2,A5:A50)
　=irrSubtotal(3,A5:A50)
　=irrSubtotal(9,A5:A50)
のいずれかの式を入力してみてください。COUNTA関数相当の計算方法は多少意図するものとは違うかもしれません。そのときは補足して下さい

Function irrSubtotal(ByVal fnc As Integer, ByVal rng As Range) As Single
'この関数は０以上のセルを対象にします。
Dim r As Range
For Each r In rng
　　If r.EntireRow.Hidden = False Then
　　　　Select Case fnc
　　　　　　Case Is = 2
　　　　　　　　If IsNumeric(r.Value) And r.Value >= 0 Then
　　　　　　　　　　irrSubtotal = irrSubtotal + 1
　　　　　　　　End If
　　　　　　Case Is = 3
　　　　　　　　If r.HasFormula Or r.Value <> "" Then
　　　　　　　　　　If IsNumeric(r.Value) And r.Value < 0 Then
　　　　　　　　　　Else
　　　　　　　　　　　　irrSubtotal = irrSubtotal + 1
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　Case Is = 9
　　　　　　　　If IsNumeric(r.Value) And r.Value >= 0 Then
　　　　　　　　　　irrSubtotal = irrSubtotal + r.Value
　　　　　　　　End If
　　　　　　Case Else
　　　　　　　　irrSubtotal = ""
　　　　End Select
　　End If
Next r
End Function

回答No.1

Kame_LABO

あらかじめ、マイナスを非表示にすればよいのでは？