☰

tag-icon • tag-icon microsoft-excel worksheet-function

我正在处理类似这样的 Excel 文件（为了保护隐私已更改/简化）：

在此处输入图片描述

命令 ＃ 任务 洗车是作为
此订单的一部分吗？                                                                      
1 审查 是的
1 更换轮胎 是的
1 洗车 是的
2 审查 不
2 更换轮胎 不
2 更换机油 不

不同的任务可以共享相同的命令 ＃。

我需要一个公式来填充第三列（图像中的黄色），其中“是”表示任务是命令 ＃其中包括任务“洗车”，如果不是，则返回“否”。

我在网上搜索过解决方案，但没有成功。

答案1

以下是 5 个公式的递进。每个公式都基于其之前的公式。希望以这种方式递进可以让后面的公式更容易理解。

[ 公式 1/5 ]  基础公式

=IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "yes"),"no")
  1. 如果函数用途匹配就像它一样logical_test。
    • 匹配依次使用每个订单作为search_key
    • 该range参数使用筛选函数返回任务为“洗车”的订单列表
  2. 这如果函数的value_if_true参数是"Yes"
  3. value_if_false被省略，因为logical_test使用只能返回TRUE或#N/A
  4. 错误信息用以下方式替换错误"No"

[ 公式 1/5 ] 基础公式

[ 公式 2/5 ]  忽略空行

=IF(A2:A7<>0, IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "Yes"),"No"),"")
  1. 要忽略空白行，如果函数被添加value<>0为logical_test。它将返回：
    • TRUE对于任何包含非零数字的单元格以及任何文本字符串，甚至包含数字的单元格。例如("0"<>0)=TRUE
    • FALSE对于 Excel 强制为零的数字0以及空单元格。例如，如果OR(ISBLANK(A1),A1=0)那么(A1<>0)=FALSE
  2. 这基本公式用作value_if_true
  3. value_if_false是一个空字符串""。
    此方法跳过返回，"No"同时防止数据间隙导致的行偏移。

[ 公式 2/5 ] 忽略空行

[ 公式 3/5 ]  标题行上的公式

将公式移动到标题行可能会防止它被覆盖。

=VSTACK("Wash on Order?",
  IF(A2:A7<>0, IFERROR(IF(
     MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
     "Yes"),"No"),""))
  1. 堆栈允许垂直堆叠阵列
  2. 使用的第一个数组是标题"Wash on Order?"
  3. 第二个数组是前面的公式。

[ 公式 3/5 ] 标题行上的公式

[ 公式 4/5 ]  添加了 LET 函数

尽管让单独使用不会给前一个公式增加很多内容，但在此步骤中使用它来阐明变换。随着公式变得越来越复杂，让变得更加清晰。

=LET(rng,A2:B7,
   a,DROP(rng,,-1), b,DROP(rng,,1),
   VSTACK("Wash on Order?",
     IF(a<>0, IFERROR(IF(
       MATCH(a, FILTER(a, b="Wash car"), 0),
       "Yes"), "No"), "")))
  1. 这让函数允许将值和公式存储在变量中以供重复使用。这可以减少代码重复和公式长度，还可以使复杂的公式更易于理解和管理。
  2. 源范围存储在 中rng。
  3. 使用以下方式返回A 列a和 B 列b降低派遣不必要的队伍。
  4. a现在在公式b中用和代替和A2:A7。B2:B7

[ 公式 4/5 ] 添加了 LET 函数

[ 公式 5/5 ]  自动调整范围大小

允许数据增长而无需修改公式。接受完整列，然后根据填充的数据根据​​需要调整数组大小。让正在全面展示。

=LET(rng,A:B,  
  arr,DROP(FILTER(rng, ROW(rng)<=
    MAX((rng<>0)*(ROW(rng)))),1),
  a,DROP(arr,,-1), b,DROP(arr,,1),
  VSTACK("Wash on Order?",
    IF(a<>0, IFERROR(IF(MATCH(a,
      FILTER(a, b="Wash car"), 0),
      "yes"),"no"),"")))
  1. 筛选返回一个arr行数组，其行号是<=包含订单 ID 的最后一行的行号。
  2. 最后一行是通过应用最大限度到行号数组，该数组的行也包含非零值。星号*是“与”运算符：MAX(ROW(rng)*rng<>0)
  3. 生成的数组包括标题（第 1 行）、订单以及任何间隙。包含间隙是为了确保有意或无意的间隙不会导致结果偏离原始范围。
  4. 这降低函数用于删除标题行。或者，也可以通过添加筛选状况ROW(rng)<>1

[ 公式 5/5 ] 自动调整范围大小

答案2

以下解决方案似乎满足了您的样本数据中设定的目标。

  1. 在空白列中C，将每行的A和B列与=concat(A2,B2)（在单元格中C2）连接起来。

  2. 在空白列中D，从 开始添加以下公式D2。这是一个数组公式，因此您需要CTRL+SHIFT+ENTER同时按下 才能使其起作用。

    =IF(OR($A$1:$A$6=A1)*(OR($C$1:$C$6=CONCAT(A1,"Wash car"))),"Yes","No")

  3. 复制D2到剩余的行。

如果我忽略了某些内容（我觉得我可能忽略了，因为我的样本数据集非常有限），请告诉我，我会尝试纠正。如果此解决方案最终解决了您的问题，请将其标记为答案。

相关内容