从A2:M2只统计看得见的负数小于0的数据

jylai

O2 真正答案是 -51040
谢谢

ykcbf1100

参与一下。。。

1. Sub 统计可见的负数和()  '//2024.1.26
   
2.     Set rng = Range("A2:M2")
   
3.     count = 0
   
4.     For Each cell In rng
   
5.         If cell.Value < 0 And cell.EntireColumn.Hidden = False Then
   
6.             count = count + cell.Value
   
7.         End If
   
8.     Next cell
   
9.     [q4] = count
   
10. End Sub
    

复制代码

ykcbf1100

结果在[q4]。。。

niko88819

代码小小修一下

1. Function CalculateNegativeAmounts(rng As Range) As Double
   
2.     Dim cell As Range
   
3.     Dim total As Double
   
4.     total = 0
   
5.     For Each cell In rng
   
6.         If IsNumeric(cell.value) And cell.value < 0 And Not cell.EntireColumn.Hidden Then
   
7.             total = total + cell.value
   
8.         End If
   
9.     Next cell
   
10.     CalculateNegativeAmounts = total
    
11. End Function

复制代码

tmplinshi

1. =SUMPRODUCT(--IsVisible(A2:M2),--(A2:M2<0), A2:M2)

复制代码

1. Function IsVisible(rg As Range)
   
2. 
   
3.     ReDim arr(1 To rg.Count) As Boolean
   
4.     Dim r As Range
   
5.     Dim i
   
6.    
   
7.     For Each r In rg
   
8.         i = i + 1
   
9.         arr(i) = Not r.EntireColumn.Hidden
   
10.     Next
    
11.    
    
12.     IsVisible = arr
    
13. End Function

复制代码

jylai

感谢3位老师              ！