Excel边学边用，持续更新

1.利用Excel VBA找出一列数据中，既不是数字，也不是空值的数据，并将其单元格标注为红色：

Sub FindNonNumericValuesInColumnsIToZ()
    Dim last_row As Long
    Dim i As Long
    Dim j As Integer

    last_row = Cells(Rows.Count, "A").End(xlUp).Row

    For j = 1 To 26
        For i = 1 To last_row
            If Not IsNumeric(Cells(i, j)) And Cells(i, j) <> "" Then
                Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next i
    Next j
End Sub

2.利用Excel VBA，根据统计学方法，找出一列数据中的异常值，并标注为黄色：

Sub FindOutliersInColumnsHToBV()
    Dim last_row As Long
    Dim i As Long
    Dim j As Integer
    Dim mean As Double
    Dim std_dev As Double
    Dim curr_value As Variant

    last_row = Cells(Rows.Count, "H").End(xlUp).Row
    mean = Application.WorksheetFunction.Average(Range("H1:H" & last_row))
    std_dev = Application.WorksheetFunction.StDev(Range("H1:H" & last_row))

    For j = 8 To 71
        If j <> 42 And j <> 43 Then
            For i = 1 To last_row
                curr_value = Cells(i, j)
                If IsNumeric(curr_value) Then
                    If Abs(curr_value - mean) > 3 * std_dev Then
                        Cells(i, j).Interior.Color = RGB(255, 255, 0)
                    End If
                End If
            Next i
        End If
    Next j
End Sub

3.使用excel VBA对表格进行分割，将C列内容相同的数据单独保存到以C列数据命名的新excel文件中：

Sub SplitData()

Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim LastRow As Long
Dim NewWb As Workbook

Set ws = ThisWorkbook.Sheets("Sheet1")
LastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

Set rng = ws.Range("C2:C" & LastRow)

For Each cell In rng
    Set NewWb = Workbooks.Add
    ws.Range("A1:C" & LastRow).AutoFilter Field:=3, Criteria1:=cell.Value
    ws.Range("A2:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy
    NewWb.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
    NewWb.SaveAs ThisWorkbook.Path & "\" & cell.Value & ".xlsx"
    NewWb.Close
Next cell

ws.AutoFilterMode = False

End Sub