如何用Excel自动化计算SCON进度达25%对应的实际日期？

一、Excel公式实现（单表快速计算） #

假设你的表格中：

• 计划日期（Planned）在A列
• 实际日期（Actuals）在B列

使用以下数组公式（旧版Excel需按Ctrl+Shift+Enter确认，新版直接回车）：

=INDEX(B:B,SMALL(IF(B:B<>"",ROW(B:B)),INT(COUNTA(A:A)*0.25)))

公式拆解： #

• COUNTA(A:A)：统计Planned列的非空日期总数
• INT(COUNTA(A:A)*0.25)：计算25%对应的n值（取整数，匹配你手动计算的逻辑）
• IF(B:B<>"",ROW(B:B))：提取Actuals列所有非空单元格的行号
• SMALL(...,n)：筛选出第n个非空行号
• INDEX(B:B,...)：根据行号返回对应的实际日期

如果需要对小数位向上取整（如总数45时，11.25取12），可将INT替换为ROUNDUP：

=INDEX(B:B,SMALL(IF(B:B<>"",ROW(B:B)),ROUNDUP(COUNTA(A:A)*0.25,0)))

二、VBA宏批量处理（50+表格高效自动化） #

如果需要批量处理同一工作簿的多个工作表，或文件夹中的多个独立工作簿，用VBA宏更高效。

1. 批量处理同一工作簿内的所有工作表 #

Sub Calculate25PercentDate()
    Dim ws As Worksheet
    Dim totalPlanned As Integer
    Dim n As Integer
    Dim resultDate As Variant
    
    For Each ws In ThisWorkbook.Worksheets
        '统计Planned列（A列）非空总数
        totalPlanned = Application.WorksheetFunction.CountA(ws.Range("A:A"))
        n = Int(totalPlanned * 0.25)
        
        If n = 0 Then
            ws.Range("C1").Value = "无足够计划日期"
            GoTo NextSheet
        End If
        
        '提取第n个非空Actuals日期（B列）
        resultDate = Application.WorksheetFunction.Index(ws.Range("B:B"), _
            Application.WorksheetFunction.Small( _
                Application.WorksheetFunction.If(ws.Range("B:B") <> "", ws.Range("B:B").Row), n))
        
        '将结果写入C1单元格（可自行修改输出位置）
        ws.Range("C1").Value = resultDate
        ws.Range("C1").NumberFormat = "dd/mm/yyyy"
NextSheet:
    Next ws
End Sub

使用步骤：

1. 打开包含所有表格的工作簿
2. 按Alt+F11打开VBA编辑器
3. 右键点击工作簿→插入→模块，粘贴上述代码
4. 修改代码中的A:A（Planned列）、B:B（Actuals列）、C1（结果输出位置）为你的实际列/单元格
5. 按F5运行宏

2. 批量处理文件夹中的所有Excel文件 #

Sub BatchProcessWorkbooks()
    Dim folderPath As String
    Dim fileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim totalPlanned As Integer
    Dim n As Integer
    Dim resultDate As Variant
    
    '选择目标文件夹
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "选择包含表格的文件夹"
        If .Show <> -1 Then Exit Sub
        folderPath = .SelectedItems(1) & "\"
    End With
    
    '遍历文件夹内所有xlsx文件
    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        Set wb = Workbooks.Open(folderPath & fileName)
        Set ws = wb.Worksheets(1) '假设每个工作簿只有1个工作表，多表需加循环
        
        totalPlanned = Application.WorksheetFunction.CountA(ws.Range("A:A"))
        n = Int(totalPlanned * 0.25)
        
        If n = 0 Then
            ws.Range("C1").Value = "无足够计划日期"
        Else
            resultDate = Application.WorksheetFunction.Index(ws.Range("B:B"), _
                Application.WorksheetFunction.Small( _
                    Application.WorksheetFunction.If(ws.Range("B:B") <> "", ws.Range("B:B").Row), n))
            ws.Range("C1").Value = resultDate
            ws.Range("C1").NumberFormat = "dd/mm/yyyy"
        End If
        
        wb.Save
        wb.Close
        fileName = Dir
    Loop
End Sub

注意事项：

• 确保所有表格的Planned/Actuals列位置一致，否则需调整代码中的列标
• 批量处理文件夹文件时，关闭其他无关Excel文件，避免冲突
• 如果Actuals列存在空值，公式和宏会自动跳过，只统计非空日期的顺序

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