聚热点 juredian

别找了,你要的Excel求和问题妙解都在这里了

继续送书!今天送3本《Excel 跟卢子一起学 早做完,不加班》,从留言区随机抽奖。

全套总共90个案例,超级全面。坚持看完,你的水平肯定会更上一层楼。如果点赞数超过100个,明天继续分享。

第一批:花了1年时间整理的90个公式,学完你就能碾压99%的同事

第二批:第二批!花了1年时间整理的90个公式,学完你就能碾压99%的同事

第三批:Excel中最牛的查找函数是VLOOKUP、LOOKUP,还是SUMIFS,谁最厉害?

1.计算文本表达式的和

在统计数据的时候忘记输入=号,如何计算这些文本表达式的结果呢?

STEP01 单击B2单元格,再单击“公式”选项卡的“定义名称”图标,在弹出的“新建名称”对话框,如果名称:表达式,引用位置为下面的公式,单击“确定”按钮。

=EVALUATE(A2)

STEP 02 在B2单元格输入公式,并向下复制。

原理分析

EVALUATE属于宏表函数。宏表函数是早期低版本Excel中使用的,现在已由VBA顶替它的功能。但仍可以在工作表中使用,不过要特别注意的是:不能直接在单元格中、只能在“定义的名称”中使用。

EVALUATE函数语法如下:

EVALUATE(表达式)

对以文字表示的一个公式或表达式求值,并返回结果。

在使用宏表函数或者VBA的时候,必须将工作簿另存为:Excel 启用宏的工作簿,否则功能会失效。

补充说明,在WPS表格中,可以直接使用EVALUATE得到结果。

=EVALUATE(A2)

2.对带颜色的项目进行求和

我们在使用Excel时,会遇到这样的情况:一个工作表中某些单元格填充为某种颜色,要求将填充了某种颜色的单元格进行快速求和。现在保价金额被分别填充成红色跟绿色背景色,如何分别对这两种背景色进行求和?

STEP 01 单击G2单元格,再单击“公式”选项卡的“定义名称”图标,在弹出的“新建名称”对话框,如果名称:颜色,引用位置为下面的公式,单击“确定”按钮。

=GET.CELL(63,D2)

STEP 02 在G2单元格输入公式,并向下复制。

=颜色

STEP 03 将颜色产生的数字依次填入H2跟H3。

STEP 04 在I2单元格输入公式,并向下复制。

=SUMIF(G:G,H2,D:D)

STEP 05 将工作簿另存为:Excel 启用宏的工作簿。

原理分析

SUMIF函数虽然可以进行条件求和,但不知直接对颜色进行条件求和。需要借助宏表函数GET.CELL获取背景色对应的数字,然后才能求和。

GET.CELL函数中的参数“63”的意思是:单元格填充颜色(背景)编码数字。

3.对产品进行统计并引用自定义数字格式

产品从不同国家购买,因此使用的货币格式不一样,如果对产品进行条件求和, 并引用B列原有的货币格式?

STEP 01 单击E2单元格,再单击“公式”选项卡的“定义名称”图标,在弹出的“新建名称”对话框,如果名称:格式,引用位置为下面的公式,单击“确定”按钮。

=GET.CELL(7,INDEX(Sheet1!$B:$B,MATCH(Sheet1!$D2,Sheet1!$A:$A,0)))

STEP 02 在E2单元格输入公式,并向下复制。

=TEXT(SUMIF(A:A,D2,B:B),格式)

STEP 03 将工作簿另存为:Excel 启用宏的工作簿。

原理分析

在名称“格式”中,使用INDEX函数与MATCH函数配合,查询D2在A列对应的值,并返回B列的单元格引用。使用GET.CELL函数,取得INDEX返回单元格的数字格式。

使用TEXT函数将SUMIF函数求得的和返回为“格式”的格式,“格式”是指原理B列对应的格式。

GET.CELL函数中的参数“7”的意思是:用于返回单元格的数字格式。

4.动态统计金额

这是一份每天出差花费清单,经常要在总金额上面插入行。用SUM函数直接统计有时不会对新增加的金额进行统计,该如何处理?

STEP 01 单击C18单元格,再单击“公式” 选项卡的“定义名称”图标,在弹出的“新建名称”对话框,如果名称:上一行,引用位置为下面的公式,单击“确定”按钮。

=C17

STEP 02 在C18单元格输入公式。

=SUM(C2:上一行)

原理分析

这个“上一行”的引用为相对引用,每插入一行引用位置就会动态变化,所以插入行也会自动汇总进去。

有人试过用SUM(区域)就能自动扩展区域,统计正确就认为任何情况下都可以,但实际上某些情况下还是不会自动扩展区域。几年前我吃过一个亏,有一天验证金额的时候,发现问题,金额最后一行没有统计,也就是少统计一个产品的金额。

小心驶得万年船,宁愿麻烦一点,也要保证数据的准确性。

5.包含单位的金额求和

这是一份每天出差花费清单,在输入金额的时候在后面输入单位,导致用SUM函数直接求和得不到正确答案,怎么样才能让含有单位的金额可以求和呢?

输入公式,按Ctrl+Shift+Enter三键结束。

=SUM(--SUBSTITUTE(C2:C17,"元",""))

原理分析

因为包含单位元,需要将单位去除掉才能求和。用SUBSTITUTE函数将元替换成空文本,也就是只提取数字。

SUBSTITUTE函数属于文本函数,所以得到的数字也属于文本, 这里叫做文本数字。数字有两种类型,一种是文本数字,一种是真正的数字,就是数值。数值可以直接求和,而文本不能求和。如账簿上的数字跟墙上的数字是不同,前者我们可以用这些数字进行各种分析,后者只能当欣赏用。

那有什么办法还原数字的本质呢?

把文本型转换成数值型,有专用的转换函数 VALUE。

=VALUE("25"),它的结果就是一个数值。

=VALUE("25")=25,它的结果就是TRUE了。

在函数或公式中,运算过程会自动把文本转换为数值(一个隐含过程),再与数值进行运 算,负值运算(-)也是一种运算,能把文本转换成数值:

-"25"=-25

还记得负负得正吧?

-(-"25")=-(-25)=25

简写为:

--"25"=25

--可以把文本转换为数值,但它不是标准的转换方式,是借用负运算的隐含功能。

6.含姓名求总金额

金额跟姓名混合在一起,这样的金额又该如何统计呢?

输入公式,按Ctrl+Shift+Enter三键结束。

=SUM(--RIGHT(A2:A14,2*LEN(A2:A14)-LENB(A2:A14)))

原理分析

因为姓名跟金额的字符数都不确定,所以不能直接提取金额出来。不过可以利用汉字是双字节,数字是单字节的特点来提取金额。

金额在右边可以用RIGHT函数,汉字的个数就是,总字节减去总字符数,而数字的个数,就是总字符数减去汉字的字符数,也就是:

=LEN(A2)-文字数=LEN(A2)-(LENB(A2)-LEN(A2))=2*LEN(A2)-LENB(A2)

提取出来的数字都是文本数字,不能够直接求和,需要要--将文本数字转换成真正的数字才可以求和。

当然转换成数字也可以用1*、0+、/1、-0等方法,只要让文本数字运算即可。

7.含错误值求总数量

数量是从别的地方引用过来,导致有部分数据是错误值,如何避开这些错误值进行求和呢?

=SUMIF(C:C,"<9E+307")

原原理分析

Excel允许的最大数值是15位,9E+307是比最大数值还大的值,条件<9E+307就是包含所有数字,这样就可以排除错误值求和。数量中包含逻辑值、文本这些也照样可以用这种方法,因为最大的数字都比逻辑值、文本值、错误值还小。

8.根据姓氏统计产量

明细表记录了每个操作人员的产量,如何根据姓氏统计产量?比如张,就是姓名第一个字是张的所有人员。

=SUMIF(A:A,D2&"*",B:B)

原理分析

D2&"*"就是以D2开头的作为条件统计,*是通配符,代表全部。

9.统计销量前5名的和

明细表记录着每个人的销售量,如何统计销量前5名的和?

输入公式,按Ctrl+Shift+Enter三键结束。

=SUM(LARGE(B2:B17,ROW(1:5)))

原理分析

最大值用MAX函数,前几大用LARGE函数,函数语法如下:

LARGE(区域,N)

比如第2大,N就是2。现在要前5名,也就是ROW(1:5)。

作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

搜索建议:
热闻

 直播销售在东南亚呈上升趋势

线上销售渠道的热度逐渐升高, 直播销售 越来越吸引消费者。Facebook上品牌包包或衣服的直播让许多女性毫不犹豫地购买产品。同样在TikTok平台上,也有火爆...(展开)

热闻

 计件工资管理办法?

问:计件工资管理办法?律师解答:计件工资制是指按照生产的合格品的数量(或作业量)和预先规定的计件单价,来计算报酬,而不是直接用劳动时间来计量的一种工资制度。那么...(展开)