二维码 购物车
部落窝在线教育欢迎您!

两列数据在同一单元格,这种奇葩设置该如何求和?

 

作者:郅龙来源:部落窝教育发布时间:2021-11-12 18:01:22点击:456

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:


Excel圈子里混久了总是能见到各种奇葩问题,比如下面这个小伙伴就遇到了奇葩老板提出的奇葩要求。两列数据在同一个单元格,请问该如何求和?下面就跟小编一起,来学习一下吧!

 

每个客户的成本和金额明明是应该分开在两个格子里记录的,但这个老板不知道是哪来的习惯,非得合在一起,还说好对比。请问哪里好对比了?

 

 

最后还要求和,这下麻烦大了,所有的求和公式都没法用。

好在这位同学知道有个功能叫分列,所以他每次都先分列,然后分别求和再合并起来。

但日子久了这也挺烦人的,于是小伙伴来求助了,能不能直接得到结果啊,实在是不想折腾了。

解决这个问题也不是很难,按照分列、求和、合并的思路就能搞定。

最终的公式为:=SUM(--LEFT(B2:B7,FIND("/",B2:B7)-1))&"/"&SUM(--MID(B2:B7,FIND("/",B2:B7)+1,9))

 

 

注意这是个数组公式,需要按着Ctrlshift和回车键完成输入,使用的时候按照实际的数据区域修改公式里的范围就行。

但是对于爱学习的小伙伴来说,还想了解这个公式的原理,下面就来说道说道。

首先得把成本提取出来求和,也就是=SUM(--LEFT(B2:B7,FIND(“/”,B2:B7)-1))这部分。

关键是LEFTFIND这个组合,FIND(“/”,B2)得到的是分隔符/在单元格中的位置。

 

 

例如7就表示/b2这个单元格的第七个字,换句话说,把b2单元格最左边的6个字提取出来就是成本了,所以公式为:=LEFT(B2,FIND("/",B2)-1)

 

 

LEFT函数的功能就是从单元格的最左边提取出指定长度的内容,FIND("/",B2)-1刚好就是需要提取的长度。

将公式中的B2单元格改成一个区域,得到的就是一组成本,这里要注意一点,LEFTMIDRIGHT这几个提取函数得到的都是文本型结果,如果要求和的话,需要转换一下才行,一般是用减负运算来转换,也就是在公式里使用“--”实现。

 

 

类似的,还需要把金额提取出来求和,这次要用到MID函数,MID函数的功能是从单元格的第几个字开始提取几个字,公式为:=MID(B2,FIND("/",B2)+1,9)

 

 

还是用FIND函数来确定/的位置,从/后面的一个字开始,提取9个字。

注意这个地方的9,并不是说非要提取9个字,假如不够9个字的话,那就有几个算几个。从示例的数据来看都是7个字,但是万一出现超过7个字的数据怎么办,用9的意思就是多预留一点空间,如果你觉得9不放心,那么用1999都行。

公式=SUM(--MID(B2:B7,FIND("/",B2:B7)+1,9))实现的就是提取金额并求和。

 

 

最后把这两部分求和的结果以及分隔符用&符号进行链接,就得到了老板所需要的。

通过这个例子,希望大家对LEFT+FINDMID+FIND这两个组合加深理解,同时对于公式中使用一个区域的这种用法也能有所了解,有时候使用数组公式也挺方便的。

 

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

将excel中的数据按照指定数量进行重复,非得用VBA才能实现吗?

如何在特定位置批量插入空行等12种实用办公技

4种删除excel重复值的小妙招,速收藏

Power Query的数据替换技巧比Excel函数更万能!

版权申明:

本文作者郅龙;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。