میانگین گیری بر اساس معیارهای چندگانه

Question

دیوید یک میز با چند هزار ردیف دارد. ستون A شامل تاریخ ها، ستون B شامل نام ها و ستون C شامل اعداد است. او باید آخرین 10 عدد (ستون C) را برای یک نام معین (ستون B) که قبل از تاریخ معین (ستون A) رخ می دهد، میانگین کند. او فکر می کند که آیا راهی برای انجام این کار در یک فرمول واحد وجود دارد یا اینکه آیا او نیاز به استفاده از مقادیر میانی در ستون های کمکی دارد. بخش مهم، برای دیوید، کشف 10 عدد آخر است که به ترتیب زمانی قبل از یک تاریخ مشخص اتفاق می افتند.

اگر فقط می خواهید آخرین تاریخ های زمانی را برای یک نام مشخص پیدا کنید، می توانید از تابع AVERAGEIFS استفاده کنید که نسبتاً ساده است:

=AVERAGEIFS($C$2:$C$7870,$B$2:$B$7870,$E$1,$A$2:$A$7870,">="&LARGE(IF($B$2:$B$7870=E$1,$A$2:$A$7870,"na"),10))

این فرمول فرض می کند که شما داده هایی در محدوده A2:C7870 دارید که همانطور که دیوید پیشنهاد می کند پیکربندی شده است. همچنین به نام افراد وارد شده در سلول E1 متکی است. میانگین 10 مقدار آخر را برای آن شخص برمی گرداند.

با این حال، دیوید میانگین 10 مقدار آخر را برای آن شخص قبل از یک تاریخ قطع مشخص می خواست. ساده ترین راه برای فهمیدن اینکه با استفاده از یک فرمول، تکیه بر تابع LET است، همانطور که در اینجا نشان داده شده است:

=LET(Dates, $A$1:$A$7870, Names, $B$1:$B$7870, Amounts, $C$1:$C$7870, LookupName, $E$1, LookupDate, $F$1, HowMany, $G$1, DateTest, FILTER(Amounts, Dates 

It is more complex than the earlier AVERAGEIFS-based formula, but can be understood with not too much difficulty. The majority of the LET functions parameters are used to define names and values , in this manner:

Dates, $A$1:$A$7870,
Names, $B$1:$B$7870,
Amounts, $C$1:$C$7870,
LookupName, $E$1,
LookupDate, $F$1,
HowMany, $G$1,
DateTest, FILTER(Amounts, Dates 

Each of these is what Excel refers to as a name/value pair. For instance, Dates is the name and $A$1:$A$7870 is the cell range assigned to that name. Note that each successive name/value pair can rely on names defined earlier, as in NameTest being defined as the values returned by FILTER(Names, Dates <= LookupDate). Each of these names can then be used in the next portion of the function:</p>

AVERAGE(INDEX(AmountList, SEQUENCE(HowMany, 1, COUNT(AmountList), -1) ) )

This is what actually returns the average. In order for the formula to work, there are three parameters that need to be set. The first is cell E1 which contains the name of the desired person, the second is cell F1 which contains the cutoff date, and the third is cell G1 which contains how many values you want to average.

If you prefer, you could also use sorting and filtering to get the desired information. Follow these general steps:

1. If you have not done so already, format your data as a table. (Select a cell in your data and click Format As Table on the Home tab of the ribbon.)
2. Sort the Date column in ascending order and filter it to show only those dates before your cutoff date.
3. Filter the Name column to show only those records for the name desired.
4. Enter this formula in a cell below the last record in column C:

=SUBTOTAL(101, Range)

Make sure you replace Range with the cell range for the last 10 visible rows. For instance, in my testing data this ended up being the range C7737:C7797. (I had to manually determine the range by counting the number of unfiltered rows.) The SUBTOTAL function using the parameter 101 returns the average of the unfiltered rows in that cell range.

If you would prefer a macro-based solution, this one is rather handy:

Function AvgNameBeforeDate(rRaw As Range, sName As String, dCutOff As Date) As Double
    Dim rNames As Range
    Dim rDates As Range
    Dim rNums As Range
    Dim dblAccum As Double
    Dim iCnt As Integer

    Set rDates = rRaw.Columns(1)
    Set rNames = rRaw.Columns(2)
    Set rNums = rRaw.Columns(3)

     Sort the data
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:=rNames, SortOn:=xlSortOnValues, _
      Order:=xlAscending, DataOption:=xlSortNormal
    ActiveSheet.Sort.SortFields.Add2 Key:=rDates, SortOn:=xlSortOnValues, _
      Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange rRaw
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

     Step backward through the sorted data
    dblAccum = 0
    iCnt = 0
    For J = rNames.Rows.Count To 1 Step -1
        If rNames.Cells(J) = sName Then
             Found the desired name
            If rDates.Cells(J) < dCutOff Then
                 Before the cutoff date
                dblAccum = dblAccum + rNums.Cells(J)
                iCnt = iCnt + 1
                If iCnt = 10 Then
                     Have now accumulated 10 values
                     So calculate the average and exit
                    AvgNameBeforeDate = dblAccum / iCnt
                    Exit Function
                End If
            End If
        End If
    Next J
     Return 0 because never got to 10 values to average
    AvgNameBeforeDate = 0
End Function

This is a user-defined function, so you would use it in the worksheet in this manner:

=AvgNameBeforeDate(A2:C7870,"Frank","1/1/2021")

Note that there are three parameters: the range to be analyzed (without any header rows), the name you want used, and the cutoff date. Be aware that the function sorts your data (what you specify in the first parameter), so there is an effect on that data. If you dont want your data sorted, then you should rely on the earlier LET formula, instead.