首页 > 化工知识 > 带单位的数字直接参与计算的3个实用技巧解读,绝对的硬核干货!

带单位的数字直接参与计算的3个实用技巧解读,绝对的硬核干货!

时间:2021-03-09 来源:Excel函数公式 浏览:

 


Excel最强的功能就是计算功能,一般情况下,参与计算的值都是没有单位的,但也有部分数据是带有单位的,如下图:

当你遇到此类情况时,是如何计算的?


一、数值带单位计算:Lookup+Left+Row函数法。

目的:计算每种商品的“销售额”。

方法:

在目标单元格中输入公式:=LOOKUP(9^9,LEFT(C3,ROW($2:10))*1)*LOOKUP(9^9,LEFT(D3,ROW($2:10))*1)&"元"。

解读:

1、计算的原理大家都明白,就是从单元格中提取数值然后进行运算。

2、公式总体上由3部分构成,第一部分为:LOOKUP(9^9,LEFT(C3,ROW($2:10))*1),第二部分为:LOOKUP(9^9,LEFT(D3,ROW($2:10))*1),第三部分为:&"元"。

3、第一第二部分的作用是相同的,就是从指定的单元格中提取数字,第三部分很好理解,就是添加后缀“元”。

4、公式第一部分的计算过程解读。

(1)在公式中Row($2:10)的返回结果为{2;3;4;5;6;7;8;9;10},“2”的确定个技巧为当前列中数值位数的最小值,“10”的确定技巧为当前数据总行数+1。

(2)LEFT(C3,ROW($2:10))需要查找的值为“66个”,分别返回长度2、3、4、5、6、7、8、9、10时的值,返回结果为:“66”、“66个”、“66个”、“66个”、“66个”、“66个”、“66个”、“66个”、“66个”。

(3)返回的值从第二位开始都是“66个”的原因是由Left函数的特性决定的,当指定的字符串长度达不到指定的长度时,只返回实际长度的字符串,暨Left(C3,2)的返回结果为“66”;Left(C3,3)的返回结果为“66个”;Left(C3,4)的返回结果也为“66个”……以此类推。

(4)LEFT(C3,ROW($2:10))*1的返回结果为:“66”、"#VALUE!"、"#VALUE!"、"#VALUE!"、"#VALUE!"、"#VALUE!"、"#VALUE!"、"#VALUE!""#VALUE!"。

(5)公式=LOOKUP(9^9,LEFT(C3,ROW($2:10))*1)采用的模糊查询,当需要查询的值无法精准找到时,自动“向下”匹配,原则为小于查询值的最大值。而在Excel中,最大的值为9^9,数据范围为“66”和"#VALUE!"组成的数组,所以最终的返回结果为“66”。

5、第一部分返回“66”、第二部分返回“2574”,相乘并在结尾附加“元”,达到最终的目的。


二、数值带单位计算:Ctrl+E法。

目的:计算每种商品的“销售额”。

方法:

1、在第一个目标单元格中输入a66*2574&"元"。

2、选中所有目标单元格区域,包括第一个输入值的单元格。

3、快捷键Ctrl+E填充其他单元格。

4、快捷键Ctrl+H打开【查找和替换】对话框,在【查找内容】中输入a,在【替换为】中输入=,单击【全部替换】。

解读:

1、操作中的a可以是任何系统能够识别的字符。

2、操作的关键在于用Ctrl+E智能提取数值,然后进行替换。


三、数值带单位计算:Left+Len函数法。

目的:计算每种商品的“销售额”。

方法:

在目标单元格中输入公式:=LEFT(C3,LEN(C3)-1)*LEFT(D3,LEN(D3)-1)&"元"。

解读:

1、函数Len的作用为:返回文本字符串中字符的个数,语法结构为:=Len(字符串)。

2、参与计算的数值中单位的长度为1,所以总长度减去1的长度就为数字的长度,然后用Left函数提取参与计算即可。


结束语:

对于带单位参与计算的关键在于提取数字,可以用Lookup函数、也可以用Left,或者用Ctrl+E智能填充。对于不同的方法都要注意其使用技巧,如Lookup模糊查询时,当查询不到需要的值时,自动向下匹配;Left函数提取字符串的长度大于实际字符串长度时,返回字符串本身等等……这些技巧必须Get到。在学习的过程中如果有任何疑问,欢迎在留言区留言讨论哦!


 

版权:如无特殊注明,文章转载自网络,侵权请联系cnmhg168#163.com删除!文件均为网友上传,仅供研究和学习使用,务必24小时内删除。
相关推荐