☰

Формула массива дат Countif

tag-icon • tag-icon microsoft-excel conditional-formatting array countif
Формула массива дат Countif

Я пытаюсь создать таблицу для отслеживания соответствия обучению. Мне нужно создать несколько полей сводки, которые показывают, сколько процедур человек проходит в настоящее время, сколько у него просроченных и скольким выдали новые процедуры с момента обучения.

Итак, для данных, показанных ниже, мне нужно:

  • один расчет, который рассматривает три даты рядом с человеком А и выясняет, сколько из них позже, чем дата 1 и дата 2 над ними
  • один расчет, который рассматривает три даты рядом с человеком А и вычисляет, сколько из них находятся между датой 1 и датой 2 над ними
  • один расчет, который рассматривает три даты рядом с человеком А и выясняет, сколько из них более ранние, чем дата 1 и дата 2 над ними

Я пробовал формулу массива, используя нижеприведенное, но она, похоже, не работает так, как я надеялся. Она работает только в том случае, если дата рядом с человеком A больше, чем каждая отдельная дата рядом с Датой 1 и Датой 2, а не только в столбце над ней.

=COUNTIFS($G65:$GX65,">="&$G$10:$GX$10,$G65:$GX65,">="&$G$6:$GX$6)

Я также пробовал добавлять символ к дате рядом с именем человека А, используя условное форматирование, но функция СЧЁТЕСЛИ не находит этот символ, если он добавлен с помощью форматирования.

Какие-нибудь советы?

введите описание изображения здесь

решение1

Если, как в вашем примере, Date 1всегда позже, чем Date 2, вы можете использовать эти формулы:

  Later:  =SUMPRODUCT(--($E6:$G6>$E$2:$G$2))
Between:  =SUMPRODUCT(($E6:$G6<=$E$2:$G$2)*($E$6:$G$6>=$E$3:$G$3))
Earlier:  =SUMPRODUCT(--($E$6:$G$6<$E$3:$G$3))

введите описание изображения здесь

Если Дата 1НЕТвсегда более поздняя дата, формулы просто нужно немного расширить, чтобы сравнивать правильную дату

решение2

Так как ОП упомянул о расчете, три даты рядом с Person A, и вычисляет, сколько из них больше later, between and earlier, чем обе Date 1и Date 2, над ними

Поэтому я использовал COUNTIF& COUNTIFSдля сравнения дат в G69:I69с date 1& 2в G65:I66.

введите описание изображения здесь

  • Формула массива (CSE) в ячейке D69:

    {=COUNTIF($G$69:$I$69,">"&$G$65:$I$66)}

Даты указаны 01-Jan-20& 15-Oct-19.

  • Формула массива (CSE) в ячейке E69:

    {=COUNTIF($G$69:$I$69,"<"&$G$65:$I$66)}

Даты — 01-Sep-19.

  • Формула массива (CSE) в ячейке F69:

    {=COUNTIFS($G$69:$I$69,">="&MIN($G$65:$I$66),$G$69:$I$69,"<="&MAX($G$65:$I$66))}

Даты указаны 01-sep-19& 15-Oct-19.

Примечание.

  • Закончить формулу D69, E69 & F69с помощьюCtrl+Shift+Enter.

  • При необходимости откорректируйте ссылки на ячейки в формуле.

Связанный контент