エクセル関数 非表示セルの集計をしないためには？

質問

どうしても、非表示にしたセル（行）が計算されます。計算式を教えてください（切実です）

例えば
Aシートにはもとになる表

　A　　　　　　B　　　　　C　　　　　D
日付　　　　品名　　　個数　　　金額
8/1（水）　　aaa 　　　 1 　　　 1000
8/3（金）　　ｂｂｂ　　　　2　　　1400
8/1（水）　　aaa 　　　 2 　　　 2000（非表示セル）
8/1（水）　　aaa 　　 　 2 　　　 2000
8/3（金）　　ｂｂｂ　　　　2　　　　1400（非表示セル）

Bシートには
　A　　　　　　　B　　　
日付　　　　　合計金額
8/1（水）　　　　3000
8/2（木）
8/3（金）　　　　1400

Bシートの合計金額を求める式がどうしてもうまくいきません。
SUMIFSやSUMPRODUCTなどいろいろ試したのですが、
どうしても非表示セルが計算されていまいます。

どなたか、教えてください。

回答No.2

MSZ006 · ベストアンサー

ユーザー定義関数を使ってもよいのでしたら・・・

標準モジュールに下記のコードを入力してください。
------------
Function ex_sum(検索範囲 As Range, 検索値 As Variant, 合計範囲 As Range) As Double
Application.Volatile

Dim rng1 As Range
Dim rng2 As Range
Dim key As Variant
Dim r As Range
Dim sname As String
Dim rtn As Double

rtn = 0

sname = 検索範囲.Parent.Name

Set rng1 = 検索範囲
Set rng2 = 合計範囲
key = 検索値

For Each r In rng1
If r.Height <> 0 And r.Width <> 0 Then
If r.Value = key Then
rtn = rtn + Worksheets(sname).Cells(r.Row, rng2.Column).Value
End If
End If
Next

ex_sum = rtn

End Function

-----------
これで　ex_sum()という関数が使えます。

【使い方】
ex_sum(検索範囲,検索値,合計範囲)

検索範囲：検索値を検索するセル範囲を指定します。
検索値：検索する値を指定します。検索値そのもの、もしくは検索値が入っているセルを設定します。
合計範囲：合計する値が入っているセル範囲を指定します。
※行の高さがゼロ（非表示）のセルは合計しません。

例えば、BシートのB２セルに
=ex_sum("Aシート"!$A$2:$A$6,A2,"Aシート"!$D$2:$D$6)
という感じで使います。

かなり手抜きですので、挙動不審なところがあるかもしれませんが一度お試しください。

お礼

出来ました！！

ツールバーと関数の入力するバーの間に、いつの間にか

「セキュリティの警告　マクロが無効になりました　オプション」

というのが出てきていて　オプションをクリックしてみると

「不明なコンテンツから保護する（推奨）（P）　と
　このコンテンツを有効にする（E)　」　とがあって　

「このコンテンツを有効にする（E)」にチェックを入れて

　OK　をクリックしたら、金額が出ました。

さっそく非表示にしてみたら、ちゃんと合計されました。

ありがとうございます

補足

ユーザー定義の式を教えていただきありがとうございます。

そのまま式をコピーしてペーストして使用して

関数を入れてみたのですが「＃NAME?」になってしまいました。

何がダメだったのでしょうか？

ユーザー定義の入力で「検索範囲,検索値,合計範囲」をそのまま
入力してはダメだったのでしょうか？

何かわかりましたら教えてください。

私の方でも、研究してみます。

（関数部分はちゃんと数式を入れています）

回答No.1

s806099d

エクセルの「非表示」とはあくまでも、表示しない、というだけで、
ないものとして扱うわけではありません。
別のセルに非加算フラグなどを定義してSUMIFするしかないでしょう。

お礼

回答ありがとうございました。
出来そうにないのですね。残念です。

テーブルを使用してSUBTOTALの関数を使用して計算するのは
非表示の行は計算されないのに、別シートに計算すると合計しちゃうんですね。

何かの関数を組み合わせれば出来るのだと思ったのですが
また方法を考えます。

ありがとうございました。