请求将Excel 365专属公式转换为Excel 2021（Mac）可用的VBA代码

功能说明 #

基于名为Purchase的表格，筛选Unit No.为62的记录，计算每位符合条件客户的首次与末次购买日期差，最终输出平均天数到指定单元格。

VBA代码实现 #

Sub CalculateAvgCustomerLifetime()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim customerDict As Object
    Dim cell As Range
    Dim unitNoCol As ListColumn
    Dim customerCol As ListColumn
    Dim dateCol As ListColumn
    Dim customerID As String
    Dim purchaseDate As Date
    Dim firstDate As Date
    Dim lastDate As Date
    Dim totalDays As Double
    Dim customerCount As Integer
    
    ' 设置工作表和表格对象
    Set ws = ThisWorkbook.ActiveSheet ' 可替换为具体工作表名，如Sheets("Sheet1")
    Set tbl = ws.ListObjects("Purchase")
    Set customerDict = CreateObject("Scripting.Dictionary")
    
    ' 获取对应列（需根据实际表格列名调整，确保列名匹配）
    Set unitNoCol = tbl.ListColumns("Unit No.")
    Set customerCol = tbl.ListColumns("Customer ID") ' 替换为实际客户ID列名
    Set dateCol = tbl.ListColumns("Purchase Date") ' 替换为实际购买日期列名
    
    ' 遍历表格数据行
    For Each cell In unitNoCol.DataBodyRange
        ' 筛选Unit No.为62的记录
        If cell.Value = 62 Then
            customerID = customerCol.DataBodyRange(cell.Row - tbl.HeaderRowRange.Row).Value
            purchaseDate = dateCol.DataBodyRange(cell.Row - tbl.HeaderRowRange.Row).Value
            
            ' 记录每个客户的首次和末次购买日期
            If Not customerDict.Exists(customerID) Then
                customerDict.Add customerID, Array(purchaseDate, purchaseDate)
            Else
                firstDate = customerDict(customerID)(0)
                lastDate = customerDict(customerID)(1)
                
                If purchaseDate < firstDate Then
                    customerDict(customerID)(0) = purchaseDate
                End If
                If purchaseDate > lastDate Then
                    customerDict(customerID)(1) = purchaseDate
                End If
            End If
        End If
    Next cell
    
    ' 计算总天数和客户数量
    totalDays = 0
    customerCount = 0
    For Each customerID In customerDict.Keys
        totalDays = totalDays + (customerDict(customerID)(1) - customerDict(customerID)(0))
        customerCount = customerCount + 1
    Next customerID
    
    ' 输出平均值到指定单元格（示例为A1，可替换为目标单元格）
    If customerCount > 0 Then
        ws.Range("A1").Value = Round(totalDays / customerCount, 0)
    Else
        ws.Range("A1").Value = 0 ' 无符合条件客户时返回0
    End If
    
    ' 释放对象
    Set customerDict = Nothing
    Set tbl = Nothing
    Set ws = Nothing
End Sub

使用说明 #

• 打开Excel文件，按Alt + F11打开VBA编辑器
• 插入新模块：右键点击工作簿名称 → 插入 → 模块
• 将上述代码粘贴到模块中
• 根据实际表格的列名，修改代码中Customer ID和Purchase Date为表格对应的列名
• 调整输出单元格（代码中ws.Range("A1")）为你需要的目标单元格
• 运行宏：回到Excel界面，按Alt + F8选择CalculateAvgCustomerLifetime并执行

示例验证 #

针对示例数据（客户A生命周期13天，客户B9天），运行代码后输出结果为11，符合预期。

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