1.原因
xloookup在查到的值为空值的单元格会返回0。
这个问题大老火的地方在于填一些公司的表格他不需要显示0值,而是需要显示空值。
比如我就遇到了如下图所示的这种搞笑情况:
2.用空值代替0值的方案
2.1 用T函数解决
T函数:手册
T函数的问题在于,如果值是文字或引用文字,则 T 返回值。 如果值未引用文字,则 T 返回 “”(空文字)。这个就取决于你的值是不是文字。如果是数字,T函数也会把它抹成空值。
原函数:
=XLOOKUP(E2,表1[查找列],表1[值列],"")
返回0值
新函数:
=T(XLOOKUP(E2,表1[查找列],表1[值列],""))
返回空值
2.2 让公式连接一个空字符
连接符放在前面和后面都没有影响。
原函数:
=XLOOKUP(E2,表1[查找列],表1[值列],"")
返回0值
新函数:
=XLOOKUP(E2,表1[查找列],表1[值列],"")&""
返回空值
新函数:
=""&XLOOKUP(E2,表1[查找列],表1[值列],"")
返回空值
这个方案比T函数的方案的好处是数值不会被抹去,但相对应的,数值格式会变成文本而无法保留原格式。
if函数解决
if函数:手册
if函数的解决方案比上面两个方案好的地方在于,自定义的程度稍微高一点,毕竟你可以一个真值和一个假值的返回都可以自行定义。坏处也很明显,公式长度很长。
原函数:
=XLOOKUP(E2,表1[查找列],表1[值列],"")
返回0值
新函数:
=IF(XLOOKUP(E2,表1[查找列],表1[值列],"")="","",XLOOKUP(E2,表1[查找列],表1[值列],""))
返回空值
if函数中嵌套len函数或者isblank函数的解法
这点我觉得其实没有必要,对于我目前的应用场景属实有点画蛇添足,但是还是说一下
len函数:手册
isblank函数:手册
用len函数判断单元格中字符的长度,毕竟空值的长度肯定是0。
原函数:
=XLOOKUP(E2,表1[查找列],表1[值列],"")
返回0值
if函数:
=IF(XLOOKUP(E2,表1[查找列],表1[值列],"")="","",XLOOKUP(E2,表1[查找列],表1[值列],""))
返回空值
新函数:
=IF(LEN(XLOOKUP(E2,表1[查找列],表1[值列]))=0,"",XLOOKUP(E2,表1[查找列],表1[值列]))
返回空值
用isblank就是在if判断的时候,真值就是空值。
原函数:
=XLOOKUP(E2,表1[查找列],表1[值列],"")
返回0值
嵌套len函数:
=IF(LEN(XLOOKUP(E2,表1[查找列],表1[值列]))=0,"",XLOOKUP(E2,表1[查找列],表1[值列]))
返回空值
嵌套isblank函数:
=IF(ISBLANK(XLOOKUP(E2,表1[查找列],表1[值列])),"",XLOOKUP(E2,表1[查找列],表1[值列]))
返回空值