Как я могу использовать функции COUNTIF и TODAY для определения сроков выполнения работ на нескольких листах?

Question

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

Формула, которую я создал, отлично работает для отдельных экземпляров, но не настолько умна, чтобы ее можно было использовать при наличии нескольких крайних сроков - когда один крайний срок завершен, он показывает все крайние сроки как завершенные.

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

Пример формулы:

=(IF(COUNTIF('S5'!P:Q,"="&TODAY()),"Action","No Action")&IF(COUNTIF('S5'!O:O,"="&TODAY())," Completed",""))

S5 - это имя вкладки книги, на которую ссылается Column O и именно в этом столбце будет указана сегодняшняя дата, когда она будет завершена.

По сути, нужно сказать, что если в Column P или Q указана сегодняшняя дата, а в Column O пусто, то в нем все еще отображается слово «Действие», даже если другая работа в этот день была завершена.

Answer 1

Попробуй это:

=CONCATENATE(IF(COUNTIF(P:Q,TODAY()),"Action","No Action"),IF(COUNTIF(O:O,TODAY())=COUNTIF(P:Q,TODAY())," Completed",""))

Некоторые стилистические заметки:

• Я обычно предпочитаю функцию CONCATENATE вместо того, чтобы разбивать выходные строки вместе с амперсандом. Тем не менее, формула должна работать с любым из них.
• Для ссылок на ячейки в одном листе не требуется определять имя листа. Если вы помещаете формулу в лист, отличный от «SS», вам нужно добавить ссылку на лист.

Что делает формула:

1. Вместо использования амперсандов функция CONCATENATE используется для сбора выходных данных внутренних формул.
2. Первая часть вывода формулы определяется в соответствии с результатами функции IF .
3. COUNTIF используется для подсчета количества ячеек в столбцах P & Q, которые соответствуют сегодняшней дате.
4. Если COUNTIF возвращает ноль, IF будет обрабатывать ответ как ЛОЖЬ. Любое другое значение рассматривается как ИСТИНА.
   • Примечание. Если ячейки не отформатированы как даты или добавлены элементы времени, могут быть возвращены неверные результаты.
5. Если первый оператор IF оценивается как TRUE, первой частью выходной строки будет "Действие". В противном случае первая часть выходной строки будет "No Action".
6. Вторая часть вывода формулы определяется в соответствии с результатами другой функции IF .
7. COUNTIF используется здесь дважды - сначала для проверки того, сколько элементов в столбце O соответствует сегодняшней дате, затем для проверки еще раз, сколько элементов в столбцах P & Q соответствуют сегодняшней дате. Затем сравниваются два параметра COUNTIF , чтобы получить состояние TRUE/FALSE для функции IF .
8. Если результаты COUNTIF точно совпадают, IF оценивается как ИСТИНА, в противном случае - ЛОЖЬ.
   • Примечание. Если ячейки не отформатированы как даты или добавлены элементы времени, могут быть возвращены неверные результаты.
9. Когда оператор IF оценивается как TRUE, вторая часть выходной строки будет "Завершена". В противном случае оно будет пустым.

Стоит отметить, что в этом методе все еще есть какая-то ошибка. В частности, если работа записана как завершенная сегодня, но не была выполнена сегодня (работа была выполнена раньше или позже графика), то сегодняшний результат может показать "Завершено", хотя есть другие работы, которые еще должны быть выполнены сегодня. Точно так же, если все работы, которые должны были быть выполнены сегодня или ранее, были завершены сегодня или ранее, а некоторые работы, которые должны были быть выполнены завтра, также были завершены, выходные данные никогда не будут отображаться как "Завершенные" завтра, если завтра не будет выполнено равное количество работ заранее. Кроме того, здесь нет никакой логики для учета просроченных предметов, которые все еще не выполнены.

Некоторые примеры ошибок:

• 10 проектов на сегодня. 8 из сегодняшних проектов были завершены сегодня. Два других проекта, которые должны быть выполнены сегодня, не завершены. 1 проект, просроченный со вчерашнего дня, был завершен сегодня. 1 проект, который должен был состояться завтра, был завершен сегодня раньше времени. Результатом формулы будет "Действие завершено", хотя 2 проекта, которые должны быть выполнены сегодня, еще не завершены.
• 10 проектов на сегодня. 3 из этих проектов были завершены вчера. Остальные 7 проектов были завершены сегодня. Никакая другая работа не была завершена сегодня. Несмотря на то, что все сегодняшние проекты завершены, на выходе все равно не будет "Завершено".
• 10 проектов на сегодня, и были завершены сегодня. Никакая другая работа не была завершена сегодня. Тем не менее, есть 5 проектов, которые еще не завершены и должны были быть выполнены до сегодняшнего дня. Статус покажет "Действие завершено", хотя работа еще не завершена.

Не зная более точно, что представляют столбцы P & Q, или не зная ваших требований, было бы немного сложно найти формулу, которая более точно отражает точный статус. Однако такая формула, вероятно, будет включать использование по крайней мере одного дополнительного столбца (чтобы отдельно отслеживать работу, выполненную для каждого крайнего срока и / или индивидуальную оценку статуса каждой позиции) и некоторое использование COUNTIFS .