今天分享一个数据处理的实用技巧，即如何利用VBA来实现我们日常制式表格的快速预览和管理。

背景：

季报、月报、周报等等，常常需要对业务和考评进行趋势跟踪和管理。为了简化这一过程，我们引入了一种较为简便的机械操作方法-Vba。

视频为方法讲解，下文为简码

Sub ProcessData()

    Dim ws As Worksheet
    Dim wsPrev As Worksheet
    Dim lastRow As Long
    Dim lastRowPrev As Long
    Dim currentRow As Long
    Dim count1 As Long, count2 As Long
    Dim sumFrom1 As Long, sumFrom2 As Long
    Dim resultRow As Long
    Dim changeCurStatus As Long, changeSeqStat As Long
    
    Dim seqStat1BValues As String
    Dim seqStat2BValues As String
    
    ' 初始化
    seqStat1BValues = ""
    seqStat2BValues = ""

    ' 假设“当期表”在“当期表”，“上期表”在“上期表”
    Set ws = ThisWorkbook.Sheets("当期表") ' 当期表
    Set wsPrev = ThisWorkbook.Sheets("上期表") ' 上期表
    ' 获取当期表的最后一行
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' 获取上期表的最后一行
    lastRowPrev = wsPrev.Cells(wsPrev.Rows.Count, "A").End(xlUp).Row

    ' 初始化计数器
    count1 = 0    ' 当期1计数
    count2 = 0    ' 当期2计数
    count3 = 0    ' 上期1计数
    count4 = 0    ' 上期2计数
    sumFrom1 = 0
    sumFrom2 = 0
    countFrom1 = 0
    countFrom2 = 0

    ' 遍历当期表（当期表）的数据
    For currentRow = 2 To lastRow ' 假设从第2行开始
        ' 直接判断单元格的值是否等于数字1
        If ws.Cells(currentRow, 3).Value = "达标" Then ' 假设Cur_status在第3列
            count1 = count1 + 1
        End If
        ' 直接判断单元格的值是否等于数字2
        If ws.Cells(currentRow, 3).Value = "不达标" Then ' 假设Cur_status在第3列
            count2 = count2 + 1
        End If
    Next currentRow
    
    For currentRow = 2 To lastRowPrev ' 假设从第2行开始
        If wsPrev.Cells(currentRow, 4).Value = "达标" Then   ' 假设Cur_status在第3列
            count3 = count3 + 1
        End If
        If wsPrev.Cells(currentRow, 4).Value = "不达标" Then ' 假设Cur_status在第3列
            count4 = count4 + 1
        End If
        
        If wsPrev.Cells(currentRow, 4).Value = "从1转出" Then ' 假设Cur_status在第3列
            countFrom1 = countFrom1 + 1
        End If
        If wsPrev.Cells(currentRow, 4).Value = "从2转出" Then ' 假设Cur_status在第3列
           countFrom2 = countFrom2 + 1
        End If
        
        ' 收集 Seq_stat 为 "从1转出" 和 "从2转出" 序列值，假设Seq_stat在第4列
        If wsPrev.Cells(currentRow, 4).Value = "从1转出" Then
            seqStat1BValues = seqStat1BValues & wsPrev.Cells(currentRow, 2).Value & vbCrLf ' 假设B列是第2列
            ' 上期表收集值，换行符
        End If
        If wsPrev.Cells(currentRow, 4).Value = "从2转出" Then
            seqStat2BValues = seqStat2BValues & wsPrev.Cells(currentRow, 2).Value & vbCrLf ' 假设B列是第2列
        End If
    Next currentRow
    
    
'sumFrom1 = sumFrom1 + ws.Cells(currentRow, 3).Value ' 假设Cur_status在第3列
'sumFrom2 = sumFrom2 + ws.Cells(currentRow, 3).Value
    
        ' 输出 Seq_stat 为 "从1转出" 和 "从2转出" 对应的 B 列值
    If Len(seqStat1BValues) > 0 Then
        MsgBox "从达标中晋级名单：" & vbCrLf & seqStat1BValues
    Else
        MsgBox "当期没有Seq_stat'从达标中晋级'"
    End If

    If Len(seqStat2BValues) > 0 Then
        MsgBox "从不达标退出名单：" & vbCrLf & seqStat2BValues
    Else
        MsgBox "当期没有Seq_stat'从不达标退出"
    End If
    
    
    ' 在结果表中输出统计信息
    Set ws = ThisWorkbook.Sheets("Result") ' 假设结果输出在“Result”工作表
    resultRow = 2 ' 从第二行开始输出
    
    
    '设置列宽
    With ws.Columns("A:C").ColumnWidth = 30    '设置第一列和第二列的列宽
    End With

    ws.Cells(resultRow, 1).Value = "上期达标（人）"
    ws.Cells(resultRow, 2).Value = count3
    resultRow = resultRow + 1

    ws.Cells(resultRow, 1).Value = "当期达标（人）"
    ws.Cells(resultRow, 2).Value = count1
    resultRow = resultRow + 1

    ws.Cells(resultRow, 1).Value = "上期不达标（人）"
    ws.Cells(resultRow, 2).Value = count4
    resultRow = resultRow + 1
    
    ws.Cells(resultRow, 1).Value = "当期不达标（人）"
    ws.Cells(resultRow, 2).Value = count2
    resultRow = resultRow + 1
    
    ws.Cells(resultRow, 1).Value = "较上期从达标中晋级（人）"
    ws.Cells(resultRow, 2).Value = countFrom1
    resultRow = resultRow + 1

    ws.Cells(resultRow, 1).Value = "较上期从不达标退出（人）"
    ws.Cells(resultRow, 2).Value = countFrom2
    resultRow = resultRow + 1

    ' 计算当期和上期的变化（基于Cur_status和Seq_stat列）
    'changeCurStatus = ws.Cells(lastRow, 3).Value - wsPrev.Cells(lastRowPrev, 3).Value ' 假设Cur_status在第3列
    'changeSeqStat = ws.Cells(lastRow, 4).Value - wsPrev.Cells(lastRowPrev, 4).Value ' 假设Seq_stat在第4列

    ' 输出变化数据
    ws.Cells(resultRow, 1).Value = "达标人数变化"
    ws.Cells(resultRow, 2).Value = count1 - count3

    resultRow = resultRow + 1

    ws.Cells(resultRow, 1).Value = "不达标人数变化"
    ws.Cells(resultRow, 2).Value = count2 - count4
    resultRow = resultRow + 1

    ' 输出确认信息，包括转出数量
    MsgBox "1、请确认上期表和本期表已粘贴更新。" & vbCrLf & _
           "2、请确认往期历史记录已存储。" & vbCrLf & _
           "3、运算完成，结果为：" & vbCrLf & vbCrLf & vbCrLf & vbCrLf & _
           "从1类转出数量为：" & countFrom1 & "，达标人数变化：" & count1 - count3 & vbCrLf & _
           "从2类转出数量为：" & countFrom2 & "，不达标人数变化：" & count2 - count4



End Sub