☰

Excel 2013 - 数据验证 - 如果符合第二个条件，则创建一个包含值的下拉列表

tag-icon • tag-icon microsoft-excel vba
Excel 2013 - 数据验证 - 如果符合第二个条件，则创建一个包含值的下拉列表

我将尽可能简化我的问题。我在 Excel 中有两张表。在第一张表中，我创建了一个位置列表。我需要在 A 列中选择位置所在的区域，然后才能在 B 列中输入位置。在下一列 (C) 中将有一个自动创建的 ID（第一个位置的 ID 为 001，第二个位置的 ID 为 002，等等）。

在我的第二张表中，我创建了一个资产列表。因此，我再次在 A 列中选择一个区域，并在 B 列中输入资产名称。现在，我想在 C 列中有一个下拉列表，它只显示第一个表中区域相同的 ID。我试过使用 OFFSET 函数，但缺少的一点是某种“选择 if”（如 countif 或 sumif），以将 ID 列表限制为具有相同区域的 ID。

每个区域可以有多个位置和资产，但每个位置和每个资产只能映射到一个位置。

期待你们的回答！无论是 excel 解决方案还是 vba 解决方案都很棒。

答案1

我建议创建一个辅助列，其中包含要在 VLOOKUP 中使用的唯一值。例如，假设您有 Area1、Area2、Area3 和 Area1（再次），然后继续。将这些值与增量值（例如 01、02、03 等）合并，并创建 01Area1、01Area2、01Area3 和02区域1。现在您知道第二张表中每个列表项要调用什么。如果您选择区域1在 A 列，那么你需要搜索增量数&区域1. 可以通过以下方式创建增量数字计数使用扩展范围的函数：

=COUNTIF($A$2:A2,A2)

通过扩展范围，公式仅计算其所在行的值。此方法的唯一缺点是您需要在别处

这里有两个描述类似用法的链接：

  1. https://www.spreadsheetweb.com/how-to-get-nth-match-with-vlookup/
  2. https://www.spreadsheetweb.com/pricing-list-quoting-tool-part-1/

答案2

假设可以对 Locations 表进行排序，最简单的解决方案只需要一个相对较短的数据验证公式和几个命名范围。不需要辅助列或行。

设置两个示例工作表Locations和Assets，如下所示：

位置工作表的屏幕截图  资产工作表的屏幕截图

添加两个定义的名称：

  1. Areas→=Locations!$A:$A
  2. IDs→=Locations!$C:$C

C2最后，在工作表中使用以下公式添加单元格内下拉列表数据验证Assets，然后向下填充/复制粘贴单元格：

=INDEX(IDs,MATCH(A2,Areas,0)):INDEX(IDs,MATCH(A2,Areas,0)+COUNTIF(Areas,A2)-1)

该方法的缺点是：

  • 需要使用定义的名称
  • 位置表必须排序
  • Area除非保证位置表中的其他表不包含与位置表的Area值匹配的值，否则它们不能安全地与该列相交。

以下替代解决方案克服了除其他表格之外的缺点。但是，它使用 Assets 工作表中的单元格来存储下拉列表的项目。

资产工作表设置了附加列：

资产工作表的屏幕截图

这次，C2数据验证公式为：

=IF(SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))=0,$I$1,C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2)))-SUMPRODUCT(--ISNA(C2:INDEX(2:2,COLUMN(C2)-1+COUNTA(C2:INDEX(2:2,COLUMNS(2:2))))))))

输入此公式D2并填写：

=
IF(
  SUMPRODUCT(
    --ISNA(
      E2:INDEX(
        (2:2),
        COLUMN(E2)-1+MAX(1,COUNTA(E2:INDEX(2:2,COLUMNS(2:2))))
      )
    )
  )=0,
  "ERROR",
  "ok"
)

多单元格数组 - 将最后一个公式输入到单元格中，从E2区域中预期位置的最大数量开始一直到最右边（对于我所用的示例E2:I2）：

=
INDEX(
  Locations!$C:$C,
  IFERROR(
    SMALL(
      IFERROR(1/(1/(
        (Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A))=IF($A2="",NA(),$A2))
        *ROW($A$2:INDEX($A:$A,COUNTA(Locations!$A:$A)))
      )),FALSE ),
      COLUMN(INDEX(2:2,1):INDEX(2:2,MAX(2,COUNTIF(Locations!$A$2:INDEX(Locations!$A:$A,COUNTA(Locations!$A:$A)),$A2))))
    ),
    NA()
  )
)

相关内容