如何把表格中有删除线的数值不列入统计中

Question

如题所述

Answer 1

需要VBA编写宏命令实现，你要做什么样的统计运算？可以给你写一个自定义的统计函数：
Public Function 剔除双划线的统计(RNG As Range, CalType As String) As Double
Dim MyRng As Range, Rng1 As Range
For Each Rng1 In RNG
If Not Rng1.Font.Underline = xlUnderlineStyleDouble Then
If MyRng Is Nothing Then Set MyRng = Rng1 Else Set MyRng = Application.Union(MyRng, Rng1)
End If
Next
Select Case CalType
Case "平均值"
剔除双划线的统计 = Application.WorksheetFunction.Average(MyRng)
Case "求和"
剔除双划线的统计 = Application.WorksheetFunction.Sum(MyRng)
Case "计数"
剔除双划线的统计 = Application.WorksheetFunction.Count(MyRng)
Case "最大值"
剔除双划线的统计 = Application.WorksheetFunction.Max(MyRng)
Case "最小值"
剔除双划线的统计 = Application.WorksheetFunction.Min(MyRng)
Case Else
剔除双划线的统计 = Application.WorksheetFunction.Sum(MyRng)
End Select
End Function
以上自定义函数的使用方法是
=剔除双划线的统计(单元格区域,"统计方法名称")
统计方法名称有"平均","求和","计数","最大值","最小值"五种，默认是"求和"方式

Answer 2

可能用代码读取识别出来还有可能做。

Answer 3

这个excel没有自带的功能，删除线只是标识作用，想排除，函数公式中是没有这样的函数的
通过VBA来判断是可以排除的，但是新手还是不推荐的。