Excel宏生成透视表时Metric2列中位数计算失败求助

背景与目标 #

宏编程新手，通过录制宏编写数据清洗自动化脚本，目标是生成能计算中位数及其他统计量的数据透视表。

核心问题 #

Metric2列无法计算中位数，报错提示：MEDIAN does not support expressions of type string/boolean/date。手动筛选发现，清洗后的Metric2列按字母排序，说明尽管单元格格式显示为数字，但实际存储为文本类型。

宏操作及排查细节 #

1. 先整理连续数值数据，通过VLOOKUP从其他工作表获取值后，复制粘贴为数值断开公式关联，避免唯一标识符变更影响数据。
2. 清除0和40值的代码：

Range("Y:Y").Select
Selection.Replace What:="40", Replacement:="", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Range("X:AB").Select
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

3. 清洗后设置列格式为数字：

Range("S:AB").Select
Selection.NumberFormat = "0.0"

4. 处理后Metric2列大部分为空白，其他列可正常计算中位数，仅Metric2报错。

数据样本（Metric2为问题列） #

Metric1 Metric2
-1.2    
-0.8    
-0.8    
-0.7    
-0.9    
-1.9    
-1.6    
-0.6    
-0.7    
-1.2    
-1.1    
-1.7    
-1.2    
-0.6    
-1.7    
-1.7    
-0.9    
-1.0    
-0.9    
-1.0    
-1.2    
-1.3    
-1.0    
-1.9    
-1.0    
-1.2    
-1.1    
-1.3    
-1.3    
-1.1    
-1.1    
-1.2    
-0.9    
-1.3    
-1.2    
-1.4    
-1.3    
-0.9    
-1.2    
-0.7    
-1.1    
-0.8    
20.5    29.6
-0.7    
-1.2    
-0.3    
-0.7    
-0.9    

解决方法 #

1. 强制将文本转换为数值 #

仅设置单元格格式无法改变数据存储类型，需添加代码强制转换：

' 针对Metric2列（假设为Y列，根据实际列调整）
With Range("Y:Y")
    .NumberFormat = "0.0"
    .Value = .Value ' 触发文本转数值
End With

或者用分列功能更稳妥：

Range("Y:Y").TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

2. 优化清除值的操作 #

避免用文本替换的方式清空单元格，直接设置单元格值为空，减少格式异常：

' 清除Y列的40值
For Each cell In Range("Y:Y").SpecialCells(xlCellTypeConstants)
    If cell.Value = 40 Then cell.Value = vbNullString
Next cell

' 清除X:AB列的0值
For Each cell In Range("X:AB").SpecialCells(xlCellTypeConstants)
    If cell.Value = 0 Then cell.Value = vbNullString
Next cell

（注：使用SpecialCells可跳过空单元格，提升运行效率）

3. 透视表前验证数据类型 #

添加检查代码，提前发现非数值单元格：

' 检查Metric2列数据类型
For Each cell In Range("Y:Y").SpecialCells(xlCellTypeConstants)
    If VarType(cell.Value) <> vbDouble And VarType(cell.Value) <> vbInteger Then
        MsgBox "单元格" & cell.Address & "不是数值类型，请检查"
    End If
Next cell

内容的提问来源于stack exchange，提问作者JY Fung