エクセル関数で困ってます。

質問

"オートフィルタ"使用中に
"SUMIF"と"COUNTIF"も使用したいのですが…

ある条件を"オートフィルタ"で
抽出した以下のような数値を、元に計算したいです。

　　　 「A」
「1」　+80
「2」　-10
「6」　+70
「9」　-20
「10」　+60
「11」　-30
「15」　+50
「18」　-40
「19」　+40
「20」　-30

※「」は行、列の意味です。
※「行」の数字が飛んでいるのは、オートフィルタで条件抽出の為
抽出された「行」のみ表示されているというイメージです。
※列は、1列のみです。

上の数値の
プラス(0以上の数値)の値
マイナス(0以下の数値)の値
プラス(0以上の数値)の平均値
マイナス(0以下の数値)の平均値
全てのデータの個数の合計
を出したいのですが
オートフィルタを使用中が条件なんです。

現在は、抽出したデータをコピーし
計算シートに貼り付けしている状態です。
出来れば、一度に出したいです。

よろしくお願いします<m(__)m>

回答No.3

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

0以上の条件で合計・個数を計算しました。
合計の場合は関係ないですが、平均・個数を考えるときは考慮してください

合計
=SUMPRODUCT((A2:A20>=0)*SUBTOTAL(3,INDIRECT("A"&ROW(A2:A20))),A2:A20)
個数
=SUMPRODUCT((A2:A20>=0)*SUBTOTAL(3,INDIRECT("A"&ROW(A2:A20))))

お礼

CoalTarさんの数式をほぼコピーし
そこから、皆様の回答を足したりしました所、何とか出来ました(>_<)

皆さんに、ベストアンサーを付けたいのですが(>_<)
CoalTarさんをベストアンサーにさせて頂きました<m(__)m>

助かりました!!

皆さんのご協力のおかげです（*^_^*）

回答No.4

noname#204879

お示しのデータは「"オートフィルタ"で抽出した」とのことですが、抽出に使った「ある条件」を示してください。（例えば、「Ｘ列のデータＹ」でなら、そのＸとＹをお知らせください。）
その「ある条件」を利用すればご希望の式を得ることができるかも。

なお、「全てのデータの個数の合計」は単に式 =SUBTOTAL(2,A1:A20) で出ませんか？

お礼

皆さんの回答を、全て合わせていったら、なんと!! 出来ました＼(^o^)／
本当に感謝です。有難うございました<m(__)m>

ANo.3のCoalTarさんの数式をほぼコピーし
そこから、皆様の回答を足したりしました所、何とか出来ました(>_<)

皆さんに、ベストアンサーを付けたいのですが(>_<)
ANo.3のCoalTarさんをベストアンサーにさせて頂きました<m(__)m>
mike_gさん、ご回答、有難うございました!!

しかし、本当にすみません<m(__)m>
皆さん、本当に、本当に、有難うございます(>_<)
助かりました!!

回答No.2

imogasi

ここに載る質問としては珍しい課題では無いかな。
ーー
まずセルの可視セルかどうかの属性は、セルの値そのものでなく（それと関係なく一時的なもので）、関数でとらえられない。
だから関数でやるのは不可能。（関数はセルの値しか判断条件、計算材料にすることが出来ない。関数の根本。）。
ただしＳＵＢＴＯＴＡＬ関数が、そういうのに唯一適した関数で、件数カウント、平均なども第１引数で指定できるが、さらに
加えて本質問では正や負の条件が付いている。これまで取り込むことは出来ないと思う。
ーー
ただし、＞全てのデータの個数の合計
は他に条件が無く、　=SUBTOTAL(2,$A$1:$A$20)
で出せる。
ーー
あとはＶＢＡで無いと出来ないと思うのでやってみた。
標準モジュールの
Sub test01()
d = Cells(65536, "A").End(xlUp).Row
s = 0: c = 0
For i = 1 To d
a = Cells(i, "A")
If Not Intersect(Cells(i, "A"), Range("A1:A" & d).SpecialCells(xlCellTypeVisible)) Is Nothing Then
If Cells(i, "A") > 0 Then
s = s + Cells(i, "A")
c = c + 1
End If
End If
Next i
Cells(d + 1, "F") = s / c
End Sub
ーー
上記の　If Cells(i, "A") > 0 Thenの部分は正の場合、負の場合で変えてください。
Cells(d + 1, "F") = s / cのＦのところも列・行指定で、
Ａ列以外で、他の質問回答計数とダブらないように適当なセルを指定してください。
ーー
結果
件数　20
プラス合計　　300
マイナス合計　-130
プラス平均　　60
マイナス平均　-26
ーーー
どうしても、関数でやりたければ、本質問では、表示セル行にだけ作業列に、コード１を振って、それを　　計算条件に取り入れる。
フィルタ条件を関数によりコード化する手もある。またはフィルタ条件までを、ＳＵＭＩＦＳ（２００７）やＳＵＭＰＲＯＤＵＣＴの条件にする（正負も加えると３条件以上になる）方法も考えられる。

お礼

皆さんの回答を、全て合わせていったら、なんと!! 出来ました＼(^o^)／
本当に感謝です。有難うございました<m(__)m>

ANo.3のCoalTarさんの数式をほぼコピーし
そこから、皆様の回答を足したりしました所、何とか出来ました(>_<)

皆さんに、ベストアンサーを付けたいのですが(>_<)
ANo.3のCoalTarさんをベストアンサーにさせて頂きました<m(__)m>
imogasiさん、超専門的な、ご回答、有難うございました!!
マクロでしたか…。
少し挑戦してみましたが、すぐに出来るものでも無いですね(>_<)
わざわざ、プログラミングして頂き本当に有難うございました。
しかし、本当にすみません<m(__)m>
皆さん、本当に、本当に、有難うございます(>_<)
助かりました!!

補足

ご回答、有難うございます<m(__)m>
ANo.1の方と、補足が多少、重なってしましますが

A列の合計を出すのは"SUBTOTAL"を使ってます。
なので、オートフィルタで、条件を抽出すると
その抽出した、合計は"SUBTOTAL"で一発で自動で出ます。
しかし、私の技量では、この"SUBTOTAL"の合計しか出せません。

実際の数式のコピペです↓
=SUBTOTAL(9,U11:U1852)

なので、マイナスの平均値や、プラスの平均値などの詳細を出す時は
オートフィルタした数字をコピーして、別の計算用のセルに貼り付けて計算してます。
↑
コレを、コピペせず自動で、別のセル(1か所)に出せないでしょうか？

あるセルに、以下の数式を設定てありますので
オートフィルタで抽出した数字を(※)に貼り付けしてます。

※0以上の個数の合計　　　=COUNTIF(AE1866:AE2416,">0")
※0以下の個数の合計　　　=COUNTIF(AE1866:AE3096,"<=-0.001")
※データの個数の合計　　　=COUNT(AE1866:AE3099)
※0以上の個数の平均値　　=SUMIF(AE1866:AE3096,">0")/COUNTIF(AE1866:AE3096,">0")
※0以下の個数の平均値　　=SUMIF(AE1866:AE3096,"<0")/COUNTIF(AE1866:AE3096,"<=-0.001")

現在は、上記のようにやってます。

私の中では…
"オートフィルタ"使用中に
"SUMIF"と"COUNTIF"の両方を計算したものを
1か所のセルに出す…という事ですが…。

例)　
※0以上の個数の合計を　→　A22　に出す
※0以下の個数の合計を　→　A23　に出す
※データの個数の合計を　→　A24　に出す
※0以上の個数の平均値を　→　A25　に出す
※0以下の個数の平均値を　→　A26　に出す

＞ＳＵＭＰＲＯＤＵＣＴ
この関数は、やってみましたが、中々うまくいかず、エラーが多いです。

＞ＶＢＡ
初めてお聞きした状態で、頭の中がパニック状態です。
現在、VBAで、調べてますが、補足をご覧頂き
関数で、可能であれば、教えてください。
よろしくお願いします<m(__)m>

回答No.1

keithin

B1セルに
=IF(OR(A1="",SUBTOTAL(3,A1)=0),"",A1)
と数式を入れ，下向けにコピーして入れておきます。

＃ところで
「０以上」と「０以下」がごっちゃになっていますね。
このままだとゼロのデータについて，「個数」や「平均」がダブルカウントになってしまいます。
どう計算する必要があるのか，間違えないように考えてください。
あるいはそもそも，ゼロであるデータは実は無かったのなら，お話も上述のＢ列の式も簡単になります。

＞プラス(0以上の数値)の値
の合計が欲しいなら
C1:
=SUMIF(B:B,">=0")

ゼロを含めての個数が欲しかったのなら
C2:
=COUNTIF(B:B,">=0")
ゼロを数えない個数
C3:
=COUNTIF(B:B,">0")

＞マイナス(0以下の数値)の値
合計
D1:
=SUMIF(B:B,"<0")
個数（ゼロを含めない）
D2:
=COUNTIF(B:B,"<0")
個数（ゼロを含める）
D3:
=COUNTIF(B:B,"<=0")

＞プラス(0以上の数値)の平均値
=C1/C2
またはC3を使う

＞マイナス(0以下の数値)の平均値
=D1/D2
またはD3を使う

＞全てのデータの個数の合計
=SUBTOTAL(2,A:A)

お礼

皆さんの回答を、全て合わせていったら、なんと!! 出来ました＼(^o^)／
本当に感謝です。有難うございました<m(__)m>

ANo.3のCoalTarさんの数式をほぼコピーし
そこから、皆様の回答を足したりしました所、何とか出来ました(>_<)

皆さんに、ベストアンサーを付けたいのですが(>_<)
ANo.3のCoalTarさんをベストアンサーにさせて頂きました<m(__)m>
keithinさん、一番早いご回答、有難うございました!!
しかし、本当にすみません<m(__)m>
皆さん、本当に、本当に、有難うございます(>_<)
助かりました!!

補足

ご丁寧に、本当に、有難うございます<m(__)m>

可能であれば、
＞下向けにコピーして入れておきます。
という様にせず、1つのセルに出したいのですが、無理でしょうか？

その列の合計を出すのは"SUBTOTAL"を使ってます。
なので、オートフィルタで、条件を抽出すると
その抽出した、合計は"SUBTOTAL"で一発で自動で出ます。
しかし、私の技量では、この"SUBTOTAL"の合計しか出せません。

実際の数式のコピペです↓
=SUBTOTAL(9,U11:U1852)

なので、マイナスの平均値や、プラスの平均値などの詳細を出す時は
オートフィルタした数字をコピーして、別の計算用のセルに貼り付けて計算してます。
↑
コレを、コピペせず自動で、別のセル(1か所)に出せないでしょうか？

あるセルに、以下の数式を設定てありますので
オートフィルタで抽出した数字を(※)に貼り付けしてます。

※0以上の個数の合計　　　=COUNTIF(AE1866:AE2416,">0")
※0以下の個数の合計　　　=COUNTIF(AE1866:AE3096,"<=-0.001")
※データの個数の合計　　　=COUNT(AE1866:AE3099)
※0以上の個数の平均値　　=SUMIF(AE1866:AE3096,">0")/COUNTIF(AE1866:AE3096,">0")
※0以下の個数の平均値　　=SUMIF(AE1866:AE3096,"<0")/COUNTIF(AE1866:AE3096,"<=-0.001")

現在は、上記のようにやってます。

私の中では…
"オートフィルタ"使用中に
"SUMIF"と"COUNTIF"の両方を計算したものを
1か所のセルに出す…という事ですが…。

例)　
※0以上の個数の合計を　→　A22　に出す
※0以下の個数の合計を　→　A23　に出す
※データの個数の合計を　→　A24　に出す
※0以上の個数の平均値を　→　A25　に出す
※0以下の個数の平均値を　→　A26　に出す

済みませんが、よろしくお願いします<m(__)m>