☰

基于另一个单元格和两列的数据验证

tag-icon • tag-icon microsoft-excel spreadsheet
基于另一个单元格和两列的数据验证

我有一份食物列表，每种食物都有一种颜色。食物可能有多种颜色。例如，下面，香蕉可以是蓝色、绿色和黄色，橙色只能是绿色。

在另一个单元格中，用户可以使用下拉菜单选择食物。数据验证相当简单。但在另一个单元格中，用户选择所选食物的颜色。我正在努力显示基于特定食物可用颜色的颜色数据验证。换句话说，如何根据所选食物动态更改有效颜色的范围？

在此处输入图片描述

答案1

建议调整原始数据的布局，如下图所示。这样我们可以更方便地选择单元格来定义名称，从而影响下拉列表。

在此处输入图片描述

步骤1选择从 A1 到 A5 的单元格，转到公式 > 定义名称组 > 从选择创建。

第2步选择从 C1 到 F4 的单元格，按 Ctrl + G > 转到 > 特殊 > 常量 > 确定（清除空白）。按照步骤 1 中的相同方式定义名称。

在此处输入图片描述

当转到“定义名称”组中的“公式”>“名称管理器”时，我们将看到如下所示的名称。

在此处输入图片描述

步骤3选择“选择食物”下的某些单元格，转到数据>数据工具>数据验证，设置以下选项。

在此处输入图片描述

步骤4选择“选择颜色”下的某些单元格，转到数据 > 数据工具 > 数据验证。输入“=INDIRECT($H2)”作为来源。

在此处输入图片描述

然后我们就会得到“香蕉”的掉落列表。

在此处输入图片描述

答案2

您需要两步解决方案：

步骤1：

  • 获取独特的水果名称列表。

  • 单元格中的数组（CSE）公式F93：

    {=IFERROR(INDEX(F$83:F$91,MATCH(0,COUNTIF($F$92:F92,F$83:F$91),0)),"")}

  • 在单元格中创建下拉列表H83，对于，。ListSource=$F$93:$F$97

第2步：

  • 单元格中的数组（CSE）公式J83，用于获取下拉菜单中选择的水果的颜色名称。

    {=IFERROR(INDEX($G$83:$G$91, SMALL(IF(COUNTIF($H$83, $F$83:$F$91), ROW($F$83:$G$91)-MIN(ROW($F$83:$G$91))+1), ROW(A1)), COLUMN(A1)),"")}

在此处输入图片描述

注意：

  • 用以下两个数组公式完成Ctrl+Shift+Enter，并向下填充。
  • 继续从下拉菜单中选择水果，以在列中获取相关颜色J。
  • 根据需要调整公式中的单元格引用。
  • 为了整洁，稍后您可以隐藏数据F93:F97。

相关内容