![Excel 2010 中的“数据验证”自定义公式有哪些限制?](https://linux22.com/image/1583692/Excel%202010%20%E4%B8%AD%E7%9A%84%E2%80%9C%E6%95%B0%E6%8D%AE%E9%AA%8C%E8%AF%81%E2%80%9D%E8%87%AA%E5%AE%9A%E4%B9%89%E5%85%AC%E5%BC%8F%E6%9C%89%E5%93%AA%E4%BA%9B%E9%99%90%E5%88%B6%EF%BC%9F.png)
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
根据需要,将验证复制/粘贴到其他列。