☰

如何使用 COUNTIF 和 TODAY 函數來決定多個工作表中的工作截止日期？

tag-icon • tag-icon microsoft-excel worksheet-function
如何使用 COUNTIF 和 TODAY 函數來決定多個工作表中的工作截止日期？

我在工作中一直在使用我們的電子表格，並且已經能夠創建一個基本公式，該公式將確定今天的日期何時有需要完成的截止日期。我現在遇到的問題源自於人們想要確定他們何時完成了最後期限。

我創建的公式對於單一實例非常有效，但在存在多個截止日期時不夠聰明 - 當一個截止日期完成時，它會顯示所有截止日期都已完成。

我想盡可能避免引用每個單元格，但是如果有一種方法可以使公式識別出如果包含今天日期的單元格左側的單元格仍然為空，則工作將完成。當今天的日期輸入該儲存格時，工作就完成了。

公式的例子是：

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

S5是工作簿標籤的名稱，Column O被引用，完成後將在此列中放置今天的日期。

Column P實際上，它需要說明的是，如果在或中顯示今天的日期Q並且Column O為空，則即使當天的其他工作已完成，它仍然顯示“操作”一詞。

答案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則將回應視為 FALSE。任何其他值都被視為 TRUE。
    • 注意：如果儲存格格式未設定為日期，或新增了時間元素，則可能會傳回不正確的結果。
  5. 如果第一個IF語句的計算結果為 TRUE，則輸出字串的第一部分將為「Action」。否則，輸出字串的第一部分將為「No Action」。
  6. 公式輸出的第二部分是根據另一個IF函數的結果定義的。
  7. COUNTIF這裡使用了兩次 - 首先檢查 O 列中有多少項目與今天的日期匹配，然後再次檢查 P 和 Q 列中有多少項目與今天的日期匹配。然後比較這兩個COUNTIF值，以提供該函數的 TRUE/FALSE 狀態IF。
  8. 如果 s 的結果COUNTIF完全匹配，IF則計算結果為 TRUE，否則為 FALSE。
    • 注意：如果儲存格格式未設定為日期，或新增了時間元素，則可能會傳回不正確的結果。
  9. 當 IF 語句的計算結果為 TRUE 時，輸出字串的第二部分將為「Completed」。否則，它將是空白的。

值得注意的是，這種方法仍有一定的謬誤。特別是，如果工作記錄為今天完成但今天沒有到期（工作提前或晚於計劃完成），那麼今天的結果可能會顯示“已完成”，即使今天仍有其他工作到期。同樣，如果今天或更早到期的所有工作都在今天或更早完成和明天到期的一些工作也已完成，輸出永遠不會在明天顯示“已完成”，除非明天提前完成等量的工作。此外，這裡根本沒有邏輯來解釋逾期未清的項目。

該謬誤的一些案例：

  • 10 個項目今天到期。今天完成的項目中有 8 個。今天到期的另外 2 個項目尚未完成。昨天逾期的1個項目今天完成。明天到期的 1 個項目今天提前完成。儘管今天到期的 2 個項目仍未完成，但公式的輸出將為「操作已完成」。
  • 10 個項目今天到期。其中 3 個項目已於昨天完成。其他7個項目已於今天完成。今天沒有完成其他工作。即使今天所有的項目都已完成，輸出仍然不會顯示「已完成」。
  • 10 個項目今天到期，今天完成。今天沒有完成其他工作。然而，在今天之前還有 5 個項目尚未完成。即使仍有工作要做，狀態也會顯示「操作已完成」。

如果不更準確地了解 P 和 Q 列代表什麼，或者更熟悉您的要求，則很難想出更正確地反映準確狀態的公式。然而，這樣的公式可能會涉及使用至少一個附加列（以單獨追蹤每個截止日期完成的工作，和/或單獨評估每個行項目的狀態）以及使用COUNTIFS.

相關內容