google-sheets - 基于 [日期]+X 的 Google 表格中的条件格式单元格

Question

请在此处查看动态日历：请复制并粘贴以进行测试

我正在将在度假酒店预订的客人登记入住列表转换为动态日历。（上面共享链接中日历右侧的列表/数据显示为灰色。）

该物业空置了几个星期，因此一些入住日期之间存在间隔。退房是从 计算的=[check-in]+[Nights]。临界转换 (CCO) 由以下公式计算=IFERROR(IF(AND(VLOOKUP([Check out of departing guest],[Check In_range],1,0),LEN([Check in of departing guest])>0,LEN([Check in of arriving guest])>0),[Check in of departing guest],""),"")

可以更改月份和年份（下方绿色圆圈）以在每个抵达日期动态更改客人姓名。

如果月份发生变化，重要的变化会在日历中动态突出显示（下面用深红色圈出）。

我现在需要使条件格式动态化，以便日期单元格（01、02 等）中的颜色根据抵达日期和客人入住的晚数自动设置单元格样式（浅红色）。每个日期有 3 个单元格，一个左侧、一个中间和一个右侧。当客人到达/离开时，有 3 个帮助识别事件。说明所需效果的最佳方式来自下图。

重要的是，在 12 月 14 日，有一个关键的转换，中心单元格被深红色阴影。

我可以使用什么公式使颜色动态变化？

1. 必须考虑深红色的关键转换
2. 在关键转换不相关的情况下，绿色应该占主导地位。

当客人从右侧的列表中停留并相应地设置单元格时，我正在努力想一种方法来计算日期。

标签： google-sheetsexcel-formulagoogle-sheets-formulaconditional-formattinggs-conditional-formatting

Answer 1

在工作表上水平和垂直放置多个合并单元格的工作表上应用条件格式是一场噩梦。

话虽如此，如果您愿意展示一些工艺，您就可以完成工作。

假设您有以下命名范围：

• Start_Date是查找表中的所有开始日期；
• End_DateStart_Date + Nights是查找表中的所有结束日期（即）；
• CCO是所有关键转换日期，如果您已将列命名为如下表所示，则可以使用以下公式找到：=IF(MATCH([@[End Date]],[Start Date],0)>0,[@[End Date]],"")

然后按照步骤设置条件格式。请注意以下是在 Excel 中执行的，而不是在 google-sheets 中执行的。

要设置关键转换日期的格式，您可以在工作表上突出显示以下范围：=$U$5,$U$12,$U$19,$U$26,$U$33,$U$40，然后使用以下公式作为格式规则：

=MATCH(V5,CCO,0)>0

要突出显示日历上的日期，您可以突出显示工作表上的以下范围：=$B$5:$D$5,$B$12:$D$12,$B$19:$D$19,$B$26:$D$26,$B$33:$D$33,$B$40:$D$40，然后使用以下公式作为格式规则：

=COUNTIFS(Start_Date,"<="&$D5,End_Date,">="&$D5)>0

然后您需要突出显示B5:D40，使用格式刷将格式复制并粘贴到以下范围，一次一个范围：E5:G40, H5:J40, K5:M40, N5:P5,Q5:S5

然后，您需要$D5在每个范围的格式规则中手动替换为$G5, $J5, $M5, $P5, $S5。

最后一天（星期六）的格式有点复杂，因为实际上有三个单独的列：T、U 和 V，并且和的规则Column T不同Column V。

要设置 的格式Column T，您需要突出显示以下范围：=$T$5,$T$12,$T$19,$T$26,$T$33,$T$40，然后使用以下公式作为格式规则：

=COUNTIFS(Start_Date,"<="&$S5,End_Date,">="&$S5)>0

要设置 的格式Column V，您需要突出显示以下范围：=$V$5,$V$12,$V$19,$V$26,$V$33,$V$40，然后使用以下公式作为格式规则：

=COUNTIFS(Start_Date,"<="&$V5,End_Date,">="&$V5)>0

Column U前面已经解释了与关键转换日期相关的格式。

在上面的演示中，我还为一周内的关键更改添加了条件格式，而不仅仅是星期六。如果这不是必需的，您可以忽略。如果您也想进行该设置，您可以借用设置格式的想法，将Column UC、F、I、L、O 和 R 列中的相关单元格一起突出显示，并使用相同的公式但替换V5为D5.

如果您有任何问题，请告诉我。干杯:)