为什么 Excel 表格会破坏 VLOOKUP 公式

为什么 Excel 表格会破坏 VLOOKUP 公式

我有一个工作表,其中的原始数据未被 Excel 识别为表格(即简单地将值放在相邻的单元格中),而在我的工作表的另一部分,公式在该数据中进行查找,例如

VLOOKUP(D$1, Config!$C$4:$E$8,2,FALSE)

一切进展顺利,直到我决定将原始数据声明为表格(Ctrl-T)以获得一些额外的功能,例如自动格式化和自动表格扩展。

一旦声明了表格,我的所有公式都会在该表中找到#N/A,无论我将搜索区域作为单元格引用(Config!$C$4:$E$8)还是通过其新表格名称(通过名称管理器修改)来引用。

线索(我不知道它的价值)是查找键(D$1在我的公式中)和数据键(的最左列Config!$C$4:$E$8)是数字(值如2015)。Excel 似乎在表格内部使用文本查找,并且无法将2015(表格中的)与"2015"(来自D$1)匹配。这只是一个线索,我可能错了。

我该怎么做才能开始使用表格(自动扩展非常有用)而不破坏我的公式?

编辑 :当查找键 ( D$1) 开始成为表格的一部分时,事情就会发生改变:2015 年现在被公式视为“2015”

答案1

正如 sicarius92 在他的评论中指出的那样,通过“强制转换”来处理改变的数据类型对D$1 + 0我来说是可行的。

不过,这只是一种解决方法,并不能解释为什么将数据放入表中会神奇地改变数据类型。

答案2

在 Excel 2010 中(不确定它在 2007 年是如何工作的),将区域转换为表格会将标题内容转换为文本,与内容无关。如果您确实希望标题中包含数字,则必须将单元格格式标记为“数字”或“常规”(或您需要的任何格式),然后重新插入以前的数据,因为 Excel 显然还禁用了对不兼容数据类型的检查(以及自动解决方案、小绿色箭头及其菜单)。

答案3

法律宽恕吧,伙计们……

一个简单的方法IFERROR()解决了这个问题。假设您知道查找表中的查找值可能是数字,但在查找值单元格中不是数字,只需执行以下操作:

=IFERROR(VLOOKUP(D$1,Config!$C$4:$E$8,2,FALSE),  VLOOKUP(VALUE(D$1),Config!$C$4:$E$8,2,FALSE))

以上只是概括公式。如果您不想将其复制到其他地方,那么您知道查找值是一个被视为文本的数字,并且您只需要公式的“失败”一半。如果您不确定,或者您根本不知道,但想证明它能解决此类问题,请使用整个公式。

反过来也一样。假设您有一个数字查找值(例如,Excel 认为它是数字 {此处,Excel 认为查找值是文本})。只需使用TEXT()而不是VALUE()即可解决它:

=IFERROR(VLOOKUP(D$1,Config!$C$4:$E$8,2,FALSE),  VLOOKUP(TEXT(D$1,"@"),Config!$C$4:$E$8,2,FALSE))

(如果都无法解决问题,那么您将面临在某个地方寻找垃圾字符的噩梦......由于标题通常不是来自安全、受控世界之外的数据,因此请先尝试查找范围材料。除非将标题导入到标题所在的任何地方(您知道,就像导入了查找值所来自的整个材料)。或者如果您导入了两组数据。)

相关内容