Excel价格计算公式编写求助：按公寓租户数量计费

公式方案 #

假设数据表头在第1行，列对应关系如下：

• A列：公寓编号（Apartment Number）
• B列：账号（Account Number）
• D列：总费用（Total Fee）

方案1：按公寓首次出现行计算总价 #

在D2单元格输入以下公式后下拉填充：

=IF(COUNTIF($A$2:A2,A2)=1, 2*COUNTIF($A:$A,A2)+2, 0)

• COUNTIF($A$2:A2,A2)=1：判定当前行是该公寓在列表中的首次出现行
• COUNTIF($A:$A,A2)：统计对应公寓的总租户数（每行对应一位租户）
• 2*总租户数+2：适配收费规则（1位收4美元，每新增1位加2美元，公式可简化为2n+2，n为租户总数）
• 非首行直接返回0

方案2：按账号格式判定首行（仅非xxx-A格式行计算总价） #

如果首行判定规则为账号不含"-A"（即非第3、4位租户行），使用以下公式：

=IF(NOT(ISNUMBER(SEARCH("-A",B2))), 2*COUNTIF($A:$A,A2)+2, 0)

• NOT(ISNUMBER(SEARCH("-A",B2)))：筛选出账号不是xxx-A格式的行
• 仅这些行计算总费用，xxx-A格式的行返回0

VBA方案（适合大数据量或自动化需求） #

以下代码可批量计算费用，支持两种首行判定规则，按需切换：

Sub CalculateApartmentFees()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim apartmentDict As Object
    Dim i As Long
    Dim currentApartment As String
    Dim tenantCount As Integer
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set apartmentDict = CreateObject("Scripting.Dictionary")
    
    ' 统计每个公寓的租户总数
    For i = 2 To lastRow
        currentApartment = ws.Cells(i, "A").Value
        apartmentDict(currentApartment) = apartmentDict(currentApartment) + 1
    Next i
    
    ' 计算费用：按需切换下方的判定规则
    For i = 2 To lastRow
        currentApartment = ws.Cells(i, "A").Value
        tenantCount = apartmentDict(currentApartment)
        
        ' 规则1：按公寓首次出现行计算总价
        If Application.WorksheetFunction.CountIf(ws.Range("$A$2:A" & i), currentApartment) = 1 Then
            ws.Cells(i, "D").Value = 2 * tenantCount + 2
        Else
            ws.Cells(i, "D").Value = 0
        End If
        
        ' 规则2：按账号格式判定（取消注释即可使用）
        'If InStr(ws.Cells(i, "B").Value, "-A") > 0 Then
        '    ws.Cells(i, "D").Value = 0
        'Else
        '    ws.Cells(i, "D").Value = 2 * tenantCount + 2
        'End If
    Next i
    
    Set apartmentDict = Nothing
    Set ws = Nothing
End Sub

使用说明：

1. 打开Excel文件，按下Alt+F11打开VBA编辑器
2. 插入新模块，粘贴上述代码
3. 选择目标工作表，运行宏即可完成计算

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