No.2ベストアンサー
- 回答日時:
まず XXwaiwaiXX さんが書かれた計算式では分母が"8"になり、非表示にする前の計算も正しい答えは出ないと思います。
8~9行もカウントされるからです。分母は COUNTIF(A1:A10,">0") とします。
次に「例えばA2からA4までを非表示にした場合」と書かれていますが、どのような操作で非表示にするのでしょうか。推定としては次の2つの方法が考えられますが、それについてこう言う方法もありますということで書いてみます。非表示の方法がもし違ってたらごめんなさい。
(1) A2からA4を行ごと(2~4行)非表示にする場合。
A B
1 1 10
2 0 12
3 0 0
4 0 0
5 1 15
6 1 0
7 1 10
・ ・
・ ・
10 1 12
上記のようにA列の前に新たに列をもうけ例えば非表示にする行には"0"を、また表示させたい列には"1"入れる。合計を表示させるセル(B11)に次の式を入れます。
式 =SUMIF(A1:A10,1,B1:B10)/COUNTI(B1:B10,">0")
これにより2~4行を非表示にしても答えは同じ。
(2)非表示にするのにオートフィルターを用いる場合。
A B
1 a b
2 1 10
3 0 12
4 0 0
5 0 0
6 1 15
7 1 0
8 1 10
・ ・
・ ・
11 1 12
上記のように1行目の前に行を1行追加し、列の項目に名前をつける(a,b)表中の任意のセルをクリックし「データ」「フィルター」「オートフィルター」をクリックし、a の矢印をクリックし"1" を選ぶ。
B12 には次の式を入れる
=SUBTOTAL(9,B2:B11)/COUNTIF(B2:B11,">0")
非常に解りやすくご案内頂きありがとうございます。
非表示の方法ですが、1・2の両方を試してみたのですが
肝心な絞込みを理解していませんでした。
=SUMIFと=SUBTOTALの使い方が違うのですね。すいません。
こんな知識レベルで答えを求めていたので非常に助かりました。
もう一度データを整理してみます。
ありがとうございました。
No.1
- 回答日時:
こんにちは。
これは、汎用性のあるユーザー定義関数SUMTOTALです。
使用法は、 'VSUM(集計方法,範囲,ゼロオプション)
で、集計方法は、
1 は AVERAGE
2 は COUNT
3 は COUNT
9 は SUM
です。
ゼロオプションは、デフォルトはFALSE ですが、TRUE を入れると0を勘定にいれません。また、文字列も、現在の場合、勘定には入れません。(ゼロの扱いはしない、ということです)
なお、とりわけ、質問のみということではなく、汎用性や発展性を考えた関数です。
こういうスタイルをお望みでない場合は、また、ご相談ください。他の方法を考えてみます。
'-----------------------------------------
'<標準モジュール登録>
Function VSUM(集計方法 As Integer, _
範囲 As Range, _
Optional ゼロオプション As Boolean) As Double
'VSUM(集計方法,範囲,ゼロオプション)
'非表示になっている行は集計しない
Dim c As Variant, i As Long, Sum As Double, Z As Boolean
If ゼロオプション = True Then Z = True
Select Case 集計方法
Case 1 '平均
For Each c In 範囲
If c.EntireRow.Hidden = False Then
If Z = True And c.Value <> 0 And VarType(c) = vbDouble Then
Sum = Sum + c.Value
i = i + 1
ElseIf Z = False And VarType(c) = vbDouble Then
Sum = Sum + c.Value
i = i + 1
End If
End If
Next
Sum = Sum / i
Case 2 'Count
For Each c In 範囲
If c.EntireRow.Hidden = False Then
If VarType(c) = vbDouble Then
If Z = True And c.Value <> 0 Then
i = i + 1
ElseIf Z = False Then
i = i + 1
End If
End If
End If
Next
Sum = i
Case 3 'Counta
For Each c In 範囲
If c.EntireRow.Hidden = False Then
If Z = True And c.Value <> 0 Then
i = i + 1
ElseIf Z = False Then
i = i + 1
End If
End If
Next
Sum = i
Case 9 '合計
For Each c In 範囲
If c.EntireRow.Hidden = False Then
If VarType(c) = vbDouble Then
Sum = Sum + c.Value
End If
End If
Next
End Select
VSUM = Sum
End Function
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) EXCEL 複数の条件に合致する個数を求めるには? 3 2022/04/19 16:39
- Excel(エクセル) エクセルの数式について教えてください。 2 2023/02/18 11:30
- Visual Basic(VBA) EXCEL VBA データベースの内容をユーザーフォームに表示したい。 5 2023/02/14 11:40
- Excel(エクセル) Excel コンボボックス バックカラー 1 2023/02/18 08:06
- Excel(エクセル) Vba エクセルマクロで、 A列の、A1セルからA10セルに空白のある行を削除する、のは Range 3 2022/11/05 17:44
- Excel(エクセル) エクセルの数式で教えてください。 5 2023/02/10 15:11
- Excel(エクセル) エクセル 関数について質問です。 2 2022/10/03 11:14
- Excel(エクセル) エクセルでIF関数中にIFERROR関数を使いたいのですが???? 5 2022/04/08 13:24
- Excel(エクセル) If関数に関する質問です。(再掲) 3 2022/10/01 20:51
- Excel(エクセル) エクセル関数についてお教えください 3 2023/07/24 12:33
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ピボットテーブルでは時間の表...
-
エクセルで特定の色の数字だけ...
-
VBAで重複する項目を1つにまと...
-
【エクセル】オートフィルタで...
-
ピボットを更新すると数式が入...
-
アンケートの集計
-
Excelの集計結果だけをコピー貼...
-
Excel VBA で集計合計を高速化...
-
ピボットの集計方法「合計」初...
-
フィルタをしても最下行を常に...
-
エクセルの小計を自動的に色づ...
-
excelで集計の合計を降順に並べ...
-
エクセル、リソース不足エラー...
-
エクセル関数について 質問です。
-
エクセルの主軸と第2軸の0を合...
-
エクセルで円グラフに引き出し...
-
エクセルで文字が白くなる
-
Excelで、空白を表示したい
-
excelグラフでデータテーブルを...
-
DATEDIFでマイナス表示をさせたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【エクセル】オートフィルタで...
-
エクセルで特定の色の数字だけ...
-
ピボットテーブルでは時間の表...
-
フィルタをしても最下行を常に...
-
VBAで重複する項目を1つにまと...
-
ピボットの集計方法「合計」初...
-
アンケートの集計
-
ピボットを更新すると数式が入...
-
エクセルの小計を自動的に色づ...
-
エクセル、リソース不足エラー...
-
EXCELで増減率を計算したいです
-
EXCEL 重複データの集計の仕方
-
excelで集計の合計を降順に並べ...
-
VLOOKUP関数とCOUNT関数等の組...
-
ピボットテーブルで同じデータ...
-
常にタイトル行と合計行を表示...
-
Excelの集計結果だけをコピー貼...
-
いい機能だけど、毎回めんどく...
-
EXCELで0を除いた平均値...
-
エクセル 日付(年月日)の合...
おすすめ情報