我正在尝试创建一个有限数量的培训(大约 50 次)的时间表,这些培训分布在有限数量的场地(大约 15 个)之间,并且我正在寻找一种比我目前使用的方法更智能的方法。
该时间表由两个电子表格组成:
电子表格 1 包含培训数据。每项培训占一行。列包括培训场地名称、开始日期和结束日期。
电子表格 2 应该是电子表格 1 中数据的直观表示,可以适应原始数据的变化。到目前为止,它包含一年中的每一天的一列和每个训练场地的一行。因此,每个单元格代表特定训练场地的特定日期。
目标是,如果工作表 1 显示培训在场地举行,AZ
从1 April 2017
到,则工作表 2 中培训场地行中代表从到 的27 April 2017
日期的单元格将通过条件格式进行标记。 1 April
27 April
AZ
我当前对代表训练地点的行使用的条件格式函数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 部分。