☰

COUNTIF 関数と TODAY 関数を使用して複数のワークシートの作業期限を識別するにはどうすればよいでしょうか?

tag-icon • tag-icon microsoft-excel worksheet-function
COUNTIF 関数と TODAY 関数を使用して複数のワークシートの作業期限を識別するにはどうすればよいでしょうか?

職場のスプレッドシートをいじっていて、今日の日付に完了しなければならない期限があるかどうかを識別する基本的な数式を作成することができました。現在私が抱えている問題は、人々が期限をいつ完了したかを特定したいということから生じています。

私が作成した数式は、単一のインスタンスに対してはうまく機能しますが、期限が複数ある場合はそれを検出できるほど巧妙ではありません。1 つの期限が完了すると、すべての期限が完了したものとして表示されます。

可能な限りすべてのセルを参照することは避けたいのですが、今日の日付を含むセルの 1 つ左のセルがまだ空白の場合、作業が完了するはずであると数式で識別する方法があれば教えてください。そのセルに今日の日付を入力すると、作業が完了します。

数式の例は次のとおりです。

=(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. 数式出力の 2 番目の部分は、別の関数の結果に従って定義されますIF。
  7. COUNTIFここでは 2 回使用されています。最初は O 列の項目のうち今日の日付と一致する項目の数をチェックし、次に P 列と Q 列の項目のうち今日の日付と一致する項目の数を再度チェックします。次に 2 つCOUNTIFの を比較して、関数の TRUE/FALSE ステータスを提供しますIF。
  8. sの結果がCOUNTIF完全に一致する場合はIFTRUE と評価され、それ以外の場合は FALSE と評価されます。
    • 注意: セルが日付としてフォーマットされていない場合、または時間要素が追加されている場合は、誤った結果が返される可能性があります。
  9. IF ステートメントが TRUE と評価されると、出力文字列の 2 番目の部分は「Completed」になります。それ以外の場合は空白になります。

この方法にはまだ誤りがあることは注目に値します。特に、作業が今日完了したと記録されているが、今日が期限ではなかった（予定より早くまたは遅れて完了した）場合、今日期限の作業がまだあるにもかかわらず、今日の結果には「完了」と表示されることがあります。同様に、今日またはそれ以前に期限が切れていたすべての作業が今日またはそれ以前に完了した場合、そして明日までに完了しなければならない作業も完了した場合、明日までに同じ量の作業が完了していない限り、出力には明日「完了」と表示されることはありません。また、未完了の期限切れの項目を考慮するロジックはまったくありません。

誤りの事例をいくつか挙げます。

  • 今日が期限のプロジェクトが 10 件あります。今日のプロジェクトのうち 8 件は今日完了しました。今日が期限の残りの 2 件のプロジェクトは完了していません。昨日から期限が過ぎていた 1 件のプロジェクトは今日完了しました。明日が期限だった 1 件のプロジェクトは今日予定より早く完了しました。今日が期限の 2 件のプロジェクトがまだ完了していないにもかかわらず、数式の出力は「アクション完了」になります。
  • 今日が期限のプロジェクトが 10 件あります。そのうち 3 件は昨日完了しました。他の 7 件は今日完了しました。他の作業は今日完了していません。今日のプロジェクトはすべて完了していますが、出力には「完了」と表示されません。
  • 10 件のプロジェクトが今日期限を迎え、今日完了しました。他の作業は今日完了していません。ただし、今日より前に期限を迎えた 5 件のプロジェクトがまだ完了していません。まだ作業が残っているにもかかわらず、ステータスには「アクション完了」と表示されます。

P 列と Q 列が何を表しているかをより正確に把握していないか、要件をよく理解していないと、正確なステータスをより適切に反映する数式を作成することは少し難しいでしょう。ただし、そのような数式では、少なくとも 1 つの追加列 (各期限に完了した作業を個別に追跡するため、および/または各行項目のステータスを個別に評価するため) と の使用が必要になる可能性がありますCOUNTIFS。

関連情報