☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Попытка отсортировать и подсчитать данные по кварталам календаря в Excel

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

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

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

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

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

Спасибо!

решение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что все еще довольно хорошее объяснение, если все еще запутано..)

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