基于跨表Loc_group多条件的PERCENTILE.INC公式需求

公式实现（优先推荐） #

在原公式基础上新增Location归属Leachate组的筛选条件，利用COUNTIFS判断Sheet1中的Location是否属于Sheet2中Loc_group="Leachate"的集合，最终组合为数组公式：

=PERCENTILE.INC(
  IF(
    (Sheet1!B:B=D$1)*
    (Sheet1!D:D>43466)*
    (Sheet1!D:D<45657)*
    (COUNTIFS(Sheet2!A:A,Sheet1!A:A,Sheet2!B:B,"Leachate")>0),
    Sheet1!C:C
  ),
  0.95
)

公式说明 #

• Sheet1!B:B=D$1：匹配D1指定的参数（如Conductivity）
• Sheet1!D:D>43466/Sheet1!D:D<45657：限定日期范围（43466/45657为Excel日期序列号）
• COUNTIFS(...)>0：验证Sheet1的Location在Sheet2中对应Loc_group为Leachate
• 若使用Excel 365/2021及以后版本，直接输入公式即可；旧版Excel需按Ctrl+Shift+Enter三键完成数组公式输入

VBA实现（备选） #

若公式计算效率不足，可编写自定义函数实现：

Function GetLeachatePercentile(paramName As String, startDate As Double, endDate As Double, pct As Double) As Double
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim locDict As Object
    Dim rng As Range, cell As Range
    Dim values As Variant, i As Integer, cnt As Integer
    
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    Set locDict = CreateObject("Scripting.Dictionary")
    
    ' 先把Sheet2中Loc_group=Leachate的Location存入字典
    For Each cell In ws2.Range("A2:A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row)
        If ws2.Cells(cell.Row, "B").Value = "Leachate" Then
            If Not locDict.Exists(cell.Value) Then
                locDict.Add cell.Value, True
            End If
        End If
    Next cell
    
    ' 收集Sheet1中符合所有条件的Value
    cnt = 0
    ReDim values(1 To ws1.Cells(ws1.Rows.Count, "C").End(xlUp).Row - 1)
    For Each cell In ws1.Range("C2:C" & ws1.Cells(ws1.Rows.Count, "C").End(xlUp).Row)
        With ws1
            If .Cells(cell.Row, "B").Value = paramName _
               And .Cells(cell.Row, "D").Value > startDate _
               And .Cells(cell.Row, "D").Value < endDate _
               And locDict.Exists(.Cells(cell.Row, "A").Value) Then
                cnt = cnt + 1
                values(cnt) = cell.Value
            End If
        End With
    Next cell
    
    ' 计算95分位数
    If cnt > 0 Then
        ReDim Preserve values(1 To cnt)
        GetLeachatePercentile = WorksheetFunction.Percentile_Inc(values, pct)
    Else
        GetLeachatePercentile = CVErr(xlErrNA) ' 无符合数据时返回#N/A
    End If
End Function

使用方法 #

在工作表单元格中输入：

=GetLeachatePercentile(D$1,43466,45657,0.95)

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