Excel:条件格式,简化从一系列行中检索数据

Excel:条件格式,简化从一系列行中检索数据

我正在尝试创建一个有限数量的培训(大约 50 次)的时间表,这些培训分布在有限数量的场地(大约 15 个)之间,并且我正在寻找一种比我目前使用的方法更智能的方法。

该时间表由两个电子表格组成:

电子表格 1 包含培训数据。每项培训占一行。列包括培训场地名称、开始日期和结束日期。

电子表格 2 应该是电子表格 1 中数据的直观表示,可以适应原始数据的变化。到目前为止,它包含一年中的每一天的一列和每个训练场地的一行。因此,每个单元格代表特定训练场地的特定日期。

目标是,如果工作表 1 显示培训在场地举行,AZ1 April 2017到,则工作表 2 中培训场地行中代表从到 的27 April 2017日期的单元格将通过条件格式进行标记。 1 April27 AprilAZ

我当前对代表训练地点的行使用的条件格式函数AZ是:

=IF(OR(AND(Sheet1!$C$5="AZ";Sheet1!$F$5>=B$6;Sheet1!$E$5<=B$6);AND(Sheet1!$C$6="AZ";Sheet1!$F$6>=B$6;Sheet1!$E$6<=B$6); ...... AND(Sheet1!$C$50="AZ";Sheet1!$F$50>=B$6;Sheet1!$E$50<=B$6));TRUE;FALSE)

因此,重复的 AND 部分会针对每个单元格检查其列(包含在第 6 行)的日期是否在 Sheet 1 中任何培训的开始和结束日期范围内,以及此培训是否在培训地点进行AZ。如果 Sheet 1 中的任何培训都是如此,则 IF 函数为真,并标记单元格。

令人惊讶的是,到目前为止,这种方法是有效的。然而,这是一个非常耗时且不够优雅的解决方案。所以现在我正在寻找一种方法来避免对 15 个训练场地中的每一个重复 50 次函数的 AND 部分。

经过广泛的谷歌搜索,我发现数组公式可能是一种可行的方法,并且条件格式默认被视为数组公式,因此我尝试了:

=IF(AND(Sheet1!C3:C54="AZ";AND(B$6>=Sheet1!E3:E54;B$6<=Sheet1!F3:F54));TRUE;FALSE)

=IF(OR(AND(Sheet1!C3:C54="AZ";AND(B$6>=Sheet1!E3:E54;B$6<=Sheet1!F3:F54)));TRUE;FALSE)

但到目前为止还没有任何运气。我想我可能还没有完全掌握数组公式的工作原理。因此,如果您发现我的函数中存在任何明显的错误,对我可以尝试的方法有任何想法,或者可以建议我正在尝试做的任何替代方法,请告诉我。

否则,我可能会疯狂地复制和改编这个血腥的 AND 部分。

相关内容