Excel, как получить среднее значение для столбца для строк, которые соответствуют нескольким критериям

Question

Я хотел бы знать средние дни между датами открытия и закрытия для элемента с датой закрытия в конкретном месяце.

Таким образом, из приведенного ниже примера в январе 2013 года пункты 2,5 и 6 были закрыты («Закрыто» может иметь статус «РАЗРЕШЕНО» или «ОТМЕНЕНО»), каждый из них был открыт на 26, 9 и 6 дней соответственно. Таким образом, для заданий с закрытой датой в январе 2013 года (с 01.01.2013 по 13.02.13) среднее время открытия (между датой открытия и закрытия) составляет от 13,67 дней до 2 дней.

Я попробовал несколько способов заставить это работать, и я думаю, что проблема, которую я имею, связана с функцией СРЕДНЕГО.

Item_ID  Open_Date  Status      Close_Date

1        1/06/2012   RESOLVED   16/07/2012

2        20/12/2012  RESOLVED   16/01/2013

3        2/01/2013   IN PROGRESS

4        3/01/2013   CANCELLED  7/05/2013

5        3/01/2013   RESOLVED   12/01/2013

6        4/01/2013   RESOLVED   10/01/2013

7        1/02/2013   RESOLVED   15/02/2013

8        2/02/2013   OPEN

9        7/02/2013   CANCELLED  26/02/2013

Answer 1

Добавьте вспомогательный столбец, в котором вы вычисляете разницу между открытым и закрытым. Добавьте еще один вспомогательный столбец, в котором вы извлекаете номер месяца. Затем вы можете использовать AverageIfs() для нескольких условий.

Если вы не хотите использовать вспомогательные столбцы, вы можете использовать формулу Sumprodcut().

Формулы для вспомогательных столбцов, начиная со строки 2, скопированы

E2 =IF(Sheet1!$D2>0,Sheet1!$D2-Sheet1!$B2,"")

F2 =MONTH(Sheet1!$B2)

На снимке экрана ячейка H2 использует вспомогательные столбцы с формулой

=AVERAGEIF(Sheet1!$F$2:$F$10,1,Sheet1!$E$2:$E$10)

Ячейка H4 не использует вспомогательные столбцы. Формула

=SUMPRODUCT(--(MONTH(B2:B10)=1),--(D2:D10<>0),(D2:D10)-(B2:B10))/SUMPRODUCT(--(MONTH(B2:B10)=1),--(D2:D10<>0))

[Изменить] Я только что понял, что вы хотите усреднить на основе месяца, когда запрос был закрыт, поэтому вы хотите изменить F2 на

=MONTH(Sheet1!$D2)

и / или формула в H4 для

=SUMPRODUCT(--(MONTH(D2:D10)=1),--(D2:D10<>0),(D2:D10)-(B2:B10))/SUMPRODUCT(--(MONTH(D2:D10)=1),--(D2:D10<>0))

Результат 14 для обоих случаев.