数据清洗与标准化

数据清洗是Excel使用中最常见也最耗时的操作。以下VBA代码可以一键完成常见清洗任务。

一键删除所有空白行

Sub DeleteBlankRows()
    Dim r As Range
    For Each r In Selection.Rows
        If Application.CountA(r) = 0 Then r.Delete
    Next
    MsgBox "空白行已删除！"
End Sub

去除所有单元格的前后空格

Sub TrimAllCells()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange
        If cell.HasFormula = False And cell.Value <> "" Then
            cell.Value = Application.Trim(cell.Value)
        End If
    Next
    MsgBox "空格已清理完成！"
End Sub

批量将文本型数字转为数值

Sub ConvertTextToNumber()
    With ActiveSheet.UsedRange
        .NumberFormat = "General"
        .Value = .Value
    End With
    MsgBox "转换完成！"
End Sub

标准化日期格式

Sub StandardizeDateFormat()
    Dim cell As Range
    For Each cell In Selection
        If IsDate(cell.Value) Then
            cell.Value = Format(cell.Value, "yyyy-mm-dd")
        End If
    Next
End Sub