☰

具有 2 个条件的数据验证

tag-icon • tag-icon conditional-formatting microsoft-excel
具有 2 个条件的数据验证

我正在寻找一个条件格式公式，可以验证另一张表中的两个条件。

我有 Sheet1 和 Sheet2：Sheet2 有工人的每日上班和下班时间记录，Sheet2 包含所有工人和特定日期的列表。

我想找到一种方法来为 Sheet1 中的单元格着色，如果 1）有特定工人的记录并且 2）它在 Sheet2 中具有相同的日期。

示例：C4 被着色为橙色，因为 Sheet2（B4）中存在 B4（工人 A）和 C2（2023-11-1）的记录；C5 未被着色，因为 Sheet2 中没有 2023-11-1 工人 B 的记录。

在此处输入图片描述

在此处输入图片描述

答案1

这里最好的选择是重组 Sheet2 上的数据。

将日期放在名称左侧的一列中。如下所示：

在此处输入图片描述

如果您这样做，并且假设 Sheet2 被称为 Sheet2New，则条件格式规则的公式将是以下变体（根据需要编辑范围地址）：

=NOT(ISNA(XMATCH(C$2&$B4,Sheet2New!$B$3:$B$6&Sheet2New!$C$3:$C$6)))

例如：

在此处输入图片描述

对于 Google Sheets，公式如下：

=NOT(ISNA(MATCH(C$2&$B4,ARRAYFORMULA(INDIRECT("Sheet2New!$B$3:$B$6")&INDIRECT("Sheet2New!$C$3:$C$6")),0)))

如果您可以将 Sheet2 中的数据放在与条件格式相同的工作表上，则无需使用 INDIRECT。

答案2

由于数据不正确structured因此，你有两个选择，要么restructure数据，否则使用two helper columns以便Sheet2​Conditional Formatting可应用于所需领域Sheet1。

我有点晚了弹性数据先生已经给了你正确的方法来重组数据和使用Conditional Formatting，因此我选择第二种方案，即使用two helper columns在Sheet2，这也是可以做到的。

由于我不确定您的情况，并且假设根据发布的标签Excel Version没有约束，那么您可以尝试以下步骤。Excel

在此处输入图片描述

可以看出，Sheet2最右边添加了两个辅助列，并应用了以下公式：

• 单元格中使用的公式J3

=SCAN(0,B3:B9,LAMBDA(x,y,IF(ISTEXT(y),x,y)))

&，

• 单元格中使用的公式K3

=SCAN(0,B3:B9,LAMBDA(x,y,IF(ISNUMBER(y),"",y)))

在Sheet1选择范围中C4:I8点击ALT+++H打开，选择最后一条规则-->并在规则描述中输入以下公式L。NNew Formatting RuleUse A Formula To Determine Which Cells To Format

=XMATCH($B4&"|"&C$2,Sheet2!$K$3#&"|"&Sheet2!$J$3#)

从选项卡中选择所需的格式填充颜色，然后按OK两次。

如果你不使用MS365那么你需要使用以下公式代替上面的公式Sheet2

在此处输入图片描述

• 单元格中使用的公式J3

=IF(ISTEXT(B3),J2,B3)

&，

• 单元格中使用的公式K3

=IF(ISNUMBER(B3),"",B3)

以上两个公式都需要填写！

在规则Sheet1中Conditional Formatting。

=MATCH($B4&"|"&C$2,Sheet2!$K$3:$K$9&"|"&Sheet2!$J$3:$J$9,0)

除了公式不同之外，所有使用步骤Conditional Formatting都相同。请注意根据您的需要更改单元格范围和引用。此外，强烈建议遵循正确的数据重组。

在Google-Sheets相同的公式中，不需要additional helper columns：

需要输入的公式Sheet1和需要向下填充和向右填充：

在此处输入图片描述

=IFERROR(ARRAYFORMULA(XMATCH($B4&"|"&TEXT(C$2,"mmddyyyy"),
 SCAN(0,Sheet2!$B$3:$B$9,LAMBDA(x,y,if(ISNUMBER(y),"",y)))&"|"&
 TEXT(SCAN(0,Sheet2!$B$3:$B$9,LAMBDA(x,y,if(ISTEXT(y),x,y))),"mmddyyyy"))),"")
  • 要设置Conditional Formatting选择范围C4:I8
  • 转到Format标签并选择Conditional Formatting
  • 选择后，它会在工作表右侧打开一个面板，选择Format Rules并Greater Than输入0下一步。
  • Formatting Style在“选择两者”Fill和Text“颜色相同”中按。Done这应该可以正常工作。
  • 此外，由于该表是公式化的，因此您可以制作该表，Protected以便没有人对公式进行任何更改来弄乱算法。

在此处输入图片描述

答案3

另一种解决方案，这次无需重组数据。
您应该定义一个名称（在名称管理器中）并将其命名为result。
然后，您将定义一个条件格式规则：
=result
公式显示在下面的屏幕截图中。条件格式区域中的工作表中的公式仅用于说明。
解决方案未在 Google 表格中测试。
工人名单

相关内容