☰

Excel 2007 帮助：如何在任意七天期间查找四个或更多实例？

tag-icon • tag-icon microsoft-excel calendar
Excel 2007 帮助：如何在任意七天期间查找四个或更多实例？

有一个关于 Excel 的问题。我向 Excel 大神求助，祈求您的仁慈帮助！

每当员工打喷嚏时，我都会将其记录在下面的图表上。如果有四个或者更多打喷嚏任何七天期限，则视为一次违规。

我需要知道每个员工有多少次违规。 （也就是说，我需要帮助填写下面的 C 列。下面的截图是手动完成的。）

以下是一些基本规则：

  1. 每一行都表示一次打喷嚏，即使第三列为空白。（“VIOLATION” 一词表示四次或四次以上打喷嚏中第一次打喷嚏。“sneeze” 一词表示打喷嚏次数计入 VIOLATION。）
  2. 七天包括第一天和第七天。例如，1 月 1 日星期五到 1 月 7 日星期四是一个七天的时间。
  3. 一天内打多次喷嚏也算作“四次或以上”（参见下面第 17-23 行，其中七天内打了 8 次喷嚏，但其中 7 次是同一天打的。）
  4. A单身的违规行为定义为在任意七天内打喷嚏四次或以上。例如：一天打喷嚏四次，而接下来六天没有打喷嚏，则属于单身的违反。例如：连续七天，每天打十次喷嚏，就单身的违反。
  5. 七天期间可能会有重叠的日子。这对我来说是个大问题。请参见下面的第 31-36 行。Cara Cocci 在 6 月 24 日至 7 月 1 日的八天内打了 6 次喷嚏。这构成了两个七天期间，一个从 6 月 24 日开始，另一个从 6 月 25 日开始。如果她在 7 月 1 日没有打喷嚏，那么就只有一次违规，因为只有一个七天期间。
  6. 七天内打喷嚏的次数不定，但一定都在四次或更多。

我想确认慎重连续七天打喷嚏或打喷嚏四次以上。 我需要列出 Arnie 和 Cara 有两次违规行为，而 Fofi 有一次。如果我能完全按照图示重新创建 C 列，那就太理想了：“违规”显示七天内第一次打喷嚏四次或以上的情况，“打喷嚏”显示计入“四次或以上”的支持情况。

我会根据您的评论来修改我的问题。非常感谢！

在此处输入图片描述

name,date,violation
ARNIE AARON,5/6/2014
ARNIE AARON,5/13/2014,VIOLATION
ARNIE AARON,5/14/2014,sneeze
ARNIE AARON,5/16/2014,sneeze
ARNIE AARON,5/19/2014,sneeze
ARNIE AARON,5/19/2014,sneeze
ARNIE AARON,5/21/2014
ARNIE AARON,6/9/2014
ARNIE AARON,8/29/2014
ARNIE AARON,10/3/2014
ARNIE AARON,10/10/2014
ARNIE AARON,10/14/2014
ARNIE AARON,12/31/2014
ARNIE AARON,1/20/2015
ARNIE AARON,1/23/2015
ARNIE AARON,3/13/2015,VIOLATION
ARNIE AARON,3/13/2015,sneeze
ARNIE AARON,3/13/2015,sneeze
ARNIE AARON,3/13/2015,sneeze
ARNIE AARON,3/13/2015,sneeze
ARNIE AARON,3/13/2015,sneeze
ARNIE AARON,3/13/2015,sneeze
ARNIE AARON,3/16/2015,sneeze
ARNIE AARON,4/16/2015
BART SEEBER,2/17/2014
BART SEEBER,2/17/2014
BART SEEBER,4/7/2014
BART SEEBER,4/30/2014
CARA COCCI,6/6/2014
CARA COCCI,6/24/2014,VIOLATION
CARA COCCI,6/24/2014,sneeze
CARA COCCI,6/25/2014,VIOLATION
CARA COCCI,6/25/2014,sneeze
CARA COCCI,6/25/2014,sneeze
CARA COCCI,7/1/2014,sneeze
DODI DEDDER,10/4/2013
DODI DEDDER,2/26/2014
DODI DEDDER,3/28/2014
EVIE EELANDE,10/31/2013
EVIE EELANDE,10/31/2013
EVIE EELANDE,1/12/2015
FOFI FAFARAZA,6/4/2013,VIOLATION
FOFI FAFARAZA,6/4/2013,sneeze
FOFI FAFARAZA,6/4/2013,sneeze
FOFI FAFARAZA,6/4/2013,sneeze

答案1

为了解决您的问题，您需要使用一组函数IF，COUNTIFS并巧妙地结合目标和相对参考。

我的公式遵循你的问题的重新表述逻辑（结果将是相同的）：

  1. 如果在给定日期已经有VIOLATION给定人员，则实际行将包含喷嚏
  2. 否则，如果七天内给定人员至少有 3 行列出（第四个实例始终是当前行）则返回违反
  3. VIOLATION否则，如果前七天内有针对该人的，则返回喷嚏
  4. 否则保留单元格空白

=IF(COUNTIFS($A$1:A1,A2,$B$1:B1,B2,$C$1:C1,"VIOLATION")>0,"sneeze",IF(COUNTIFS(A3:$A$46,A2,B3:$B$46,"<"&B2+7)>=3,"VIOLATION",IF(COUNTIFS($A$1:A1,A2,$B$1:B1,">"&B2-7,$C$1:C1,"VIOLATION")>0,"sneeze","")))

在此处输入图片描述

相关内容