excel 数据有效性根据条件自动获取下拉列表值

本文详细介绍了excel 数据有效性根据条件自动获取下拉列表值的教程，有需要的朋友快来看看吧！

excel数据有效制作下拉列表，问题点在于确认选项值，最简单的就是直接设置，第二个是使用offset来确认范围，offset就不讲了，大概得意思是函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数 OFFSET 可用于任何需要将引用作为参数的函数。例如，公式 SUM(OFFSET(C2,1,2,3,1)) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。

简而言之，offset是取一个区域，因此我们可以将此区域作为数据有效的序列值，这样就有了选项了。普通的设置，只需例如OFFSET(C3,2,3,1,1)就可以了。

今天要解决的问题是根据条件，也就是在一个数据中A ，对应四个值分别是1、2、3、4，而这个A是可变，可能为B，而B对应得值可能为3、6、8、9、12，因此这就需要把公式演变下

OFFSET(C3,MATCH(sheet1!$O5,sheet2!$C:$C,0)-1,3,1,COUNTIF(sheet2!$C:$C,sheet1!$O5))，原公式2，替换为MATCH(sheet1!$O5,sheet2!!$C:$C,0)-1

sheet1!$O5位值A所在位置，利用match函数在 sheet2标中寻找确认位置，这个位置是行位置，最后一个，利用COUNTIF(sheet2!$C:$C,sheet1!$O5)去查找A一共出现了几次，因而可以确认A对应得值有几个，这样就完成。

综上理解，行位置确认的，如果列位置也是动态的，那么一样使用match函数动态确认即可，这些应用智能自己推敲了。

更复杂的比如不一定在哪个表中去选定这个区域，连sheet表都可能是动态的，那么加入INDIRECT函数来实现。

比如=OFFSET(INDIRECT("'"&sheet1!Q5& amp;"'!$D$1"),MATCH(sheet1!$O5,INDIRECT("'"&sheet1!Q5&"'!$C:$C"),0)-1,,COUNTIF(INDIRECT("'"&sheet1!Q5&"'!$C:$C"),sheet1!$O5

核心语句：

  在二级下拉列表的数据有效性区域中通过函数实现动态显示与一级下拉菜单选择值相对应的二级下拉菜单内容，

OFFSET($C$40,MATCH($D8,$D$40:$D$9999,0)-1,0,COUNTIF($D$40:$D$9999,$D8))

说明：  
  MATCH 函数用于返回数据库中第一个与被搜索值匹配的单元格的位置；
  COUNTIF函数用于返回与被搜索值相匹配的单元格的数量；
  OFFSET函数用于返回数据有效性的区域，以MATCH 函数的值开始计算COUNTIF函数返回值个单元格的区域；
  
  通过两级菜单的选择确定“投标人名称”后通过下面语句在数据库中查找并自动在指定单元格内自动填充与之相对应的”开户行“及“账号”等信息，从而实现高效无误的完成投标保证金的自动录入。

IF(C8="","",VLOOKUP($C8,$C$40:$I$99,6,FALSE))

说明：
  VLOOKUP函数用于返回数据库制定区域中与指定数值匹配的记录中的相关信息；

可以验证的数据类型
Excel 使您可以为单元格指定以下类型的有效数据：

数值 指定单元格中的条目必须是整数或小数。您可以设置最小值或最大值，将某个数值或范围排除在外，或者使用公式计算数值是否有效。

日期和时间 设置最小值或最大值，将某些日期或时间排除在外，或者使用公式计算日期或时间是否有效。

长度 限制单元格中可以输入的字符个数，或者要求至少输入的字符个数。

值列表 为单元格创建一个选项列表（例如小、中、大），只允许在单元格中输入这些值。用户单击单元格时，将显示一个下拉箭头，从而使用户可以轻松地在列表中进行选择。

可以显示的消息类型
对于所验证的每个单元格，都可以显示两类不同的消息：一类是用户输入数据之前显示的消息，另一类是用户尝试输入不符合要求的数据时显示的消息。如果用户已打开 Office 助手，则助手将显示这些消息。

输入消息 一旦用户单击已经过验证的单元格，便会显示此类消息。您可以通过输入消息来提供有关要在单元格中输入的数据类型的指令。

错误消息 仅当用户输入无效数据并按下 Enter 时，才会显示此类消息。您可以从以下三类错误消息中进行选择：

信息消息 此类消息不阻止输入无效数据。除所提供的文本外，它还包含一个消息图标、一个“确定”按钮（用于在单元格中输入无效数据）和一个“取消”按钮（用于恢复单元格中的前一个值）。
警告消息 此类消息不阻止输入无效数据。它包含您提供的文本、警告图标和三个按钮：“是”用于在单元格中输入无效数据；“否”用于返回单元格进一步进行编辑；“取消”用于恢复单元格的前一个值。
停止消息 此类消息不允许输入无效数据。它包含您提供的文本、停止图标和两个按钮：“重试”用于返回单元格进一步进行编辑）；“取消”用于恢复单元格的前一个值。请注意，不能将此类消息作为一种安全措施：虽然用户无法通过键入和按 Enter 输入无效数据，但是他们可以通过复制和粘贴或者在单元格中填写数据的方式来通过验证。
如果未指定任何信息，则 Excel 会标记用户输入数据是否有效，以便您以后进行检查，但用户输入的数据无效时，它不会通知用户。

有效性验证的示例
在操作中进行检验是了解数据有效性验证功能的最佳方法。如果您下载本文附带的示例工作簿，您将获得所有有效性验证类型的示例和所有消息类型的示例。工作簿包括一些指令，用于查看每个被验证的单元格所使用的设置，并将有效性验证设置复制到您自己的工作簿（您可以在此工作簿中根据需要修改这些设置）。

设置数据有效性验证
确定要在工作簿中使用的有效性验证后，您可以使用“数据”菜单中的“有效性”命令对其进行设置。您将发现，从本文下载的示例工作簿中的指令也可以通过 Excel 2002 的帮助获得。以下是该过程的一般概述：

设置工作表 通过在工作表中输入数据和公式开始。如果您要使用有效选项列表，请输入列表并为其命名。
定义单元格的设置 从要验证的第一个单元格开始，使用“数据有效性”对话框指定所需的有效性验证类型、输入信息（如果需要）和错误信息（如果需要）。
设置其他单元格的有效性验证 将有效性验证设置从第一个单元格复制到其他单元格，然后对设置进行修改，这样做通常可以节省时间。
测试有效性验证规则 尝试在单元格中输入有效和无效数据，以确保设置正常工作并且消息如期显示。使用“有效性”命令对这些设置进行更改。在一个单元格中更改有效性验证后，您可以将这些更改自动应用至其他具有相同设置的所有单元格中。
设置有效选项列表 如果您使用了有效选项列表并且不希望用户查看和更改列表，您可以将列表置于另一个工作表中，设置有效性验证，隐藏包含该列表的工作表，然后使用密码保护工作簿。工作簿密码将有助于保护包含列表的工作表。
在需要时应用保护 如果要保护工作表或工作簿，请在设置完有效性验证后进行。保护工作表之前，请确保“解除锁定”任何已经过验证的单元格，否则，用户将无法在单元格中输入内容。
在需要时共享工作簿 如果您要共享工作簿，请在完成有效性验证和保护的设置后执行。共享工作簿后，除非您停止共享，否则将不能更改有效性验证的设置，但是在共享工作簿时，Excel 将继续验证指定的单元格。
检查无效数据的结果 用户在工作表中输入数据后，您可以按照本文后面的介绍检查无效数据。
在验证的单元格中输入数据
以下是用户输入数据的过程。您可以使用输入消息和错误消息，提供一些用户需要了解的指令，使用户了解您如何设置工作表以确保数据正确。要查看此过程，请下载本文附带的示例工作簿，并查看“消息”工作表中的示例。

查看输入消息 用户单击经过验证的单元格或使用箭头键移至单元格时，您输入的消息将以“助手”气球或单独的消息框方式显示。如果您为单元格提供了下拉列表，则单元格右侧将显示下拉箭头。

键入数据 当用户键入数据或单击下拉箭头从列表中选择值时，输入的消息将停留在屏幕上（下拉列表可能会覆盖部分消息）。

输入有效数据 如果用户输入有效数据并按下 Enter，则数据将被输入到单元格并且不会出现特殊情况。

输入无效数据 如果用户输入的数据不符合条件，并且您为无效数据指定了错误消息，则错误消息将显示在“助手”气球或单独的消息窗口中。用户可以阅读消息，并决定如何处理。

对于信息消息，用户可以单击“确定”以输入无效数据，或者单击“取消”重新开始。
对于警告消息，用户可以单击“是”以输入无效数据，单击“否”继续编辑单元格，或者单击“取消”重新开始。
对于停止消息，用户将无法输入无效数据，但可以单击“重试”编辑单元格，或者单击“取消”重新开始。
如果未提供任何消息，则对于用户来说，在经过验证的单元格中输入数据与一般的 Excel 数据输入相同。然而，Excel 会标记包含无效条目的单元格，以便于您查找这些内容。