扫码申请加Excel微信群
交流心得 解决问题
验证信息:Excel
点上方关注 Excel基础学习园地 公众号“ Excel基础学习园地 ”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号,请点击上方“ Excel基础学习园地 ”添加关注,方便我们每天向您推送精彩资讯。在日常工作中,我们会遇到各种系统导出的数据,根据不同的需要,在Excel里对这些数据进行引用、汇总等等计算。看起来系统导出的数据非常规范,应该是很可靠的,可是在使用的过程中,却发现到处都是坑……
下面这个表格就是某个系统导出的数据(部分):
现在需要根据原值找到与之对应的本年已提数据,公式为:=VLOOKUP(D5,系统!A:L,12,0),然而公式得到的全部是错误值:
检查公式没有错误,问题是在数据源:
再来看看数据源的A列,所有的数字前面都有个绿色的三角,点击查看说明,才发现这些数字都是【以文本格式存储的数字】,而我们写入公式的表格中,原值一列则是数字格式,正是因为两边的格式不一致才造成了vlookup函数无法找到数据。
对于这种情况,我们需要统一格式才能得到正确的结果,将数据源的A列转换为数字比较常用的有两种方式,可以直接选择数据后利用提示中的【转换为数字】来完成:
当数据比较多的时候,这个方法显得有点卡,因此推荐使用分列来实现格式的转换:
选择整列,点击分列,直接点完成就OK!
处理以后,大部分数据都正常了,但是求和这里还是错误值:
对这列数据检查发现,还有个别的数据使用vlookup的结果是错误值,这是由于在数据源中确实没有对应的数据产生的错误,还是有两个方法来解决:
方法1:vlookup外面加一个iferror函数,公式修改为
=IFERROR(VLOOKUP(D5,系统!A:L,12,0),0)
方法2:求和时不用sum,改为=AGGREGATE(9,6,J5:J1088)
关于AGGREGATE函数,简单说一下,第一参数9表示求和(还可以选择其他的统计方式):
第二参数6表示忽略错误值,同样还有其他选项:
第三参数当然就是进行计算的单元格区域了。
想详细了解这个函数的用法可以在文末留言,如果大家都有需要,后面会发一期这个函数的教程。
虽然解决了错误值的问题,但是求和并没有得到正确的结果,反而变成了0,这又是什么鬼~~~~
再来看看数据源,我们公式引用的结果是L列,看上去还是文本格式,vlookup函数虽然可以引用过去,但是并没有对格式进行调整,而对一列文本进行求和,结果当然是0:
再给出两个方法,可以对数据源的L列进行分列转数值,也可以在vlookup后面加个小尾巴进行处理:
OK,结果终于正确了,可以保存了,谁知道……
如果点是,则会不断的弹出同样的框框,显示每一条错误信息,好吧,只能点否,然后出来这样的:
虽然本人使用的是Excel2016版,奈何系统导出的文件是Excel5.0/95版(这可是要比Excel2003还古老的版本哦,公司的系统该是多少年没升级了啊……)
继续点否,当然要用比较先进的格式保存才行:
在保存类型里,一定要选择最上边这个(Excel工作簿),确定以后,终于跳出了所有的坑。
在实际工作中,遇上的坑可能要比今天这个例子中的更加奇葩,但是记住一点,不要慌,利用自己学过的知识一点一点分析,总会跳出来的,如果真的不行,不是还有老菜鸟么……
办理终身学员享有更多福利
强调:所有学员都是在QQ群里,我们没有一对一服务!
谨防你的财产损失!!!