☰

Excel 2010 中的“数据验证”自定义公式有哪些限制？

tag-icon • tag-icon microsoft-excel data-validation
Excel 2010 中的“数据验证”自定义公式有哪些限制？

TL;WR为什么=if(column()=16, list1, list2)用作列表类型的数据验证函数时会出错？

长版本：

我整个下午都在电脑前大声叫喊，因为我无法使我的数据验证方案正常工作，并且可能已经发现了问题。

背景：

我有一张包含多列的表格。每组三列代表一个常见星期的不同特征。因此，第 1 列是“项目”，第 2 列是“完成的单位”，第 3 列是“工时”，全部为第 1 周。然后第 4 列是“项目”，第 2 列是“完成的单位”，第 3 列是“工时”，全部为第 2 周。依此类推。我希望验证此表格的输入，但“项目”的验证要求当然与“工时”不同。而且，我尝试制作一个智能验证函数，它知道当前列并相应地应用验证，而不是选择所有“项目”列并应用一个数据验证，然后选择所有“工时”列并应用另一个数据验证（这很繁琐，而且更容易出错）。

思考过程：

如果我希望对单元格执行某些操作（例如条件格式）时，使其依赖于相关单元格的值，则格式公式必须使用INDIRECT(address(row(),column()))[或者，对于非易失性选项，我通常使用INDEX($1:1000,row(),column())]。此外，由于格式公式不能直接采用 INDIRECT（更多内容请关注），我必须创建一个命名函数。很好，它起作用了。

因此，我尝试对数据验证进行同样的操作。如果我将“验证标准”设置为“自定义”，并使用公式

=IF(OR(COLUMN()=16,COLUMN()=18), INDEX($1:$1000,ROW(),COLUMN())<8)

并将它们应用到单元格$P$26:$R$26，我得到了预期的结果：$P$26 可以是 7，但不能是 8；$Q$26 不能是任何东西；$R$26 可以是 7，但不能是 8。太棒了。

因为我希望验证是不同的列表，所以我研究了如何使列表选择动态化。因此，我尝试了一个简单的示例：命名函数list.sel = if( Table_Weekly[@1]="Bolts", list_bolts, list_nuts )，将“验证标准”设置为“列表”，公式为=list.sel。运行良好，符合预期。

问题：

然后，我尝试更改命名函数：list.sel = if( column()=16, list1, list2 )其他一切都相同，它告诉我“源当前评估为错误。”我知道我没有打错字，因为我可以=index(list.sel,1)在第 16 列的单元格 a 中输入，它正确地给出了 list1 中的第一个项目。

那么，到底是怎么回事呢？这和公式求值的上下文有关吗？对于条件格式，Excel 知道它正在为哪个单元格求值格式化函数，因此它知道如何处理 row() 和 column() 函数。数据验证不知道吗？整个下午，我都在为这个问题和其他各种问题绞尽脑汁，对此感到厌倦。

答案1

使列表动态并与列相关的一种方法：

  • 使用与列标题一致相关的名称来命名各种列表（如果您不使用列标题作为命名范围，甚至可以使用相同的名称）

    • 例如： Column: Items ValidList: listItems

         Column:  ManHours   ValidList:  listManHours

  • 然后使用验证公式（带列标题项目在J1中)：

    J2: =INDIRECT("list" & J$1)

这将使用命名范围的内容填充下拉列表listItems

根据需要，将验证复制/粘贴到其他列。

相关内容