|
- Option Explicit
- Function Sumifs_visible(qiuhehangshu_first As Long, qiuhe_liebiao As String, tiaojianquyu1 As Range, tj1 As String, _
- Optional tiaojianquyu2 As Range, Optional tj2 As String, Optional tiaojianquyu3 As Range, Optional tj3 As String)
- '可以用本代码实现 sumifs 函数功能,同时实现 subtotal 不统计隐藏行功能,最多限制条件为三个
- 'qiuhehangshu_first 求和开始行数
- 'qiuhe_liebiao 要求和的列,例如 A、B、C......列
- Dim quyushuzu1
- Dim quyushuzu2
- Dim quyushuzu3
- Dim xunhuan_hangshu
- Dim lieshu
- Dim qiuhe
-
- '求取列数
- If Len(UCase(qiuhe_liebiao)) = 1 Then lieshu = Asc(UCase(UCase(qiuhe_liebiao))) - 64 '确认 列表为 字母 A 到 Z
-
- If Len(UCase(qiuhe_liebiao)) = 2 Then '确认 列表为 字母 AA 到 ZZ
- lieshu = (Asc(Mid(UCase(UCase(qiuhe_liebiao)), 1, 1)) - 64) * 26 + (Asc(Mid(UCase(UCase(qiuhe_liebiao)), 2, 1)) - 64) Mod 26 '确认 列表为 字母 AA 到 ZZ
- If Len(UCase(qiuhe_liebiao)) = 2 And ((Asc(Mid(UCase(UCase(qiuhe_liebiao)), 2, 1)) - 64) Mod 26) = 0 Then lieshu = (Asc(Mid(UCase(UCase(qiuhe_liebiao)), 1, 1)) - 64) * 26 + 26
- End If
-
- If Len(UCase(qiuhe_liebiao)) = 3 Then '确认 列表为 字母 AAA 到 XFD
- lieshu = ((Asc(Mid(UCase(UCase(qiuhe_liebiao)), 1, 1)) - 65) Mod 26) * 676 + (((Asc(Mid(UCase(UCase(qiuhe_liebiao)), 2, 1)) - 64) Mod 26) + 26) * 26 + (Asc(Mid(UCase(UCase(qiuhe_liebiao)), 3, 1)) - 64) '确认 列表为 字母 AAA 到 XFD
- If Len(UCase(qiuhe_liebiao)) = 3 And ((Asc(Mid(UCase(UCase(qiuhe_liebiao)), 2, 1)) - 64) Mod 26) = 0 Then lieshu = ((Asc(Mid(UCase(UCase(qiuhe_liebiao)), 1, 1)) - 65) Mod 26) * 676 + (26 + 26) * 26 + (Asc(Mid(UCase(UCase(qiuhe_liebiao)), 3, 1)) - 64) '确认 列表为 字母 AAA 到 XFD
- End If
- '执行加总
- If tiaojianquyu2 Is Nothing Then
-
- quyushuzu1 = tiaojianquyu1
-
- For xunhuan_hangshu = 1 To UBound(quyushuzu1)
-
- If tj1 = "<>" And quyushuzu1(xunhuan_hangshu, 1) <> "" Then quyushuzu1(xunhuan_hangshu, 1) = "<>" '防止统计不为 空值的时候出错,将 非空值单元格全部替换为 "<>"
-
- If quyushuzu1(xunhuan_hangshu, 1) = tj1 And Cells(qiuhehangshu_first + xunhuan_hangshu - 1, lieshu).EntireRow.Hidden = False Then
- qiuhe = qiuhe + Cells(qiuhehangshu_first + xunhuan_hangshu - 1, lieshu)
- Else
- qiuhe = qiuhe + 0 '如果单元格所在的行已经隐藏,则不加总数字,将其作为 数字 0 加总执行一次循环
- End If
-
- Next xunhuan_hangshu
-
- ElseIf tiaojianquyu3 Is Nothing Then
-
- quyushuzu1 = tiaojianquyu1
- quyushuzu2 = tiaojianquyu2
-
- For xunhuan_hangshu = 1 To UBound(quyushuzu1)
-
- If tj1 = "<>" And quyushuzu1(xunhuan_hangshu, 1) <> "" Then quyushuzu1(xunhuan_hangshu, 1) = "<>"
- If tj2 = "<>" And quyushuzu2(xunhuan_hangshu, 1) <> "" Then quyushuzu2(xunhuan_hangshu, 1) = "<>"
-
- If quyushuzu1(xunhuan_hangshu, 1) = tj1 And quyushuzu2(xunhuan_hangshu, 1) = tj2 And _
- Cells(qiuhehangshu_first + xunhuan_hangshu - 1, lieshu).EntireRow.Hidden = False Then
- qiuhe = qiuhe + Cells(qiuhehangshu_first + xunhuan_hangshu - 1, lieshu)
- Else
- qiuhe = qiuhe + 0
- End If
-
- Next xunhuan_hangshu
-
- Else '如果是多条件三个
-
- quyushuzu1 = tiaojianquyu1
- quyushuzu2 = tiaojianquyu2
- quyushuzu3 = tiaojianquyu3
-
- For xunhuan_hangshu = 1 To UBound(quyushuzu1)
-
- If tj1 = "<>" And quyushuzu1(xunhuan_hangshu, 1) <> "" Then quyushuzu1(xunhuan_hangshu, 1) = "<>"
- If tj2 = "<>" And quyushuzu2(xunhuan_hangshu, 1) <> "" Then quyushuzu2(xunhuan_hangshu, 1) = "<>"
- If tj3 = "<>" And quyushuzu3(xunhuan_hangshu, 1) <> "" Then quyushuzu3(xunhuan_hangshu, 1) = "<>"
-
- If quyushuzu1(xunhuan_hangshu, 1) = tj1 And quyushuzu2(xunhuan_hangshu, 1) = tj2 And quyushuzu3(xunhuan_hangshu, 1) = tj3 And _
- Cells(qiuhehangshu_first + xunhuan_hangshu - 1, lieshu).EntireRow.Hidden = False Then
- qiuhe = qiuhe + Cells(qiuhehangshu_first + xunhuan_hangshu - 1, lieshu)
- Else
- qiuhe = qiuhe + 0
- End If
-
- Next xunhuan_hangshu
- End If
- Sumifs_visible = qiuhe
-
- End Function
复制代码 |
评分
-
查看全部评分
|