Попытка получить данные, отсортированные и подсчитанные по календарным кварталам в Excel

Question

Я пытаюсь настроить формулу в Excel, которая будет рассматривать диапазон данных, даты которых указаны в c7:c30, а категории Active- "A" и Completed- "C" в L7:L30. Я пытаюсь по-особенному взглянуть на каждую строку по отдельности и сравнить, находятся ли даты между датами квартала, указанными 1/1/12 (B38) и 3/31/12 (C38) (первый квартал года), и если это условие выполнено, затем подсчитайте те, которые отмечены как "А" для активных.

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

{=(ЕСЛИ (($ C $ 7:$ С30> = $ B $ 39)* ($ C $ 7:C $ 30 <= $ С $ 39), СЧЕТЕСЛИ ($ L $ 7:$ L30, "а"), 0))}

Пожалуйста, помогите мне выяснить, что не так с этой формулой, что она не будет рассматривать каждую строку по отдельности и вместо этого будет считать все, обозначенные буквой A, если первая дата будет найдена верной, в сравнении с представленным оператором if.

Извините, не очень хорошо в Excel, любая помощь приветствуется.

Спасибо!

Answer 1

Я думаю, что у вас могут быть проблемы с тем, как вы структурировали формулу массива. Просто распаковав ваше утверждение, вы создадите массив, который будет иметь столько строк, сколько у вас есть, и каждая строка будет содержать результат COUNTIF($ L $ 7:$ L30, "a"), если выполняется условие даты или 0.

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

Альтернативный способ подойти к этому будет следующим:

Ваши три теста (для каждого ряда):

• дата> = B38
• дата <= C38
• условие = "а"

и вы хотите только посчитать, где все три верны.

Достаточно удобно, что истинные значения оцениваются в 1, а ложные в 0, поэтому мы можем использовать операцию умножения, чтобы получить то, что вы хотите.

Итак, для одной строки вы хотите сделать следующее

(C7 >= B38) * (C7 <= C38) * (L7="a")

(и, очевидно, расширив его до C7:C30 и т. д., чтобы покрыть интересующие вас строки)

Чтобы затем свернуть это в одно значение, вы хотите обернуть весь лот в инструкцию SUM(), которая просто пройдет по списку и сложит все значения вместе.

SUM( (C7:C30>B39) * (C7:C30<=C39) * (L7:L30="a") )

Кроме того, просто если вам было интересно посмотреть, вернитесь к своей исходной формуле и оберните ее оператором sum(), и вы получите результат, показывающий, сколько раз ваш статус установлен на "a", умножается на количество строк, которые соответствуют условию даты.

(обратите внимание: для формул массива сначала нужно немного поцарапать голову, но как только вы обдумаете их, они вроде как работают. Первоначально я читал о них по адресу http://www.cpearson.com/excel/ArrayFormulas.aspx, что все еще является довольно хорошим объяснением, если это все еще сбивает с толку ..)