Как использовать функции СЧЁТЕСЛИ и СЕГОДНЯ для определения сроков выполнения работ на нескольких рабочих листах?

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, то первая часть выходной строки будет "Action". В противном случае первая часть выходной строки будет "No Action".
6. Вторая часть вывода формулы определяется в соответствии с результатами другой IFфункции.
7. COUNTIFиспользуется здесь дважды - сначала для проверки того, сколько элементов в столбце O соответствуют сегодняшней дате, затем для повторной проверки того, сколько элементов в столбцах P и Q соответствуют сегодняшней дате. COUNTIFЗатем сравниваются два s, чтобы предоставить статус ИСТИНА/ЛОЖЬ для IFфункции.
8. ЕСЛИ результаты s COUNTIFточно совпадают, IFбудет оценено как ИСТИНА, в противном случае — ЛОЖЬ.
   • Примечание: Если ячейки не отформатированы как даты или добавлены элементы времени, могут быть возвращены неверные результаты.
9. Если оператор IF оценивается как TRUE, вторая часть выходной строки будет "Completed". В противном случае она будет пустой.

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

Вот несколько примеров этого заблуждения:

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

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