3522路线检测-新浦京官方网站

3522路线检测-新浦京官方网站

工作中超好用的8个Excel公式组合

2020/3/27 19:20:16


1、分离字母/数字和汉字

汉字在前

=LEFT(单元格,LENB(单元格)-LEN(单元格))

示例:

image.png

汉字在后

=Right(单元格,LENB(单元格)-LEN(单元格))

示例:

image.png

汉字在中间

=MID(单元格,MATCH(2,LENB(MID(单元格,ROW(INDIRECT("1:"&LEN(单元格))),1)),),LENB(单元格)-LEN(单元格)),此函数为数组函数,因此在公式输入完成后,按Ctrl+Shift+Enter三键结束

示例:

image.png

注:怕麻烦可通过填写例子,然后ctrl+E快捷键智能填充。


2、提取任意位置字符串的数字

=LOOKUP(9^9,MID(数字,MATCH(1,MID(数字,ROW(1:99),1)^0,0),ROW(1:99))*1)}

(注:数组公式,需要按ctrl+shift+enter三键输入)

示例:如下图所示,提示A列中字符串中的数字

=LOOKUP(9^9,MID(A2,MATCH(1,MID(A2,ROW(1:99),1)^0,0),ROW(1:99))*1)

注:如果字符串超过100位,就把99调大

image.png


3、金额大写转换公式

=TEXT(LEFT(RMB(单元格),LEN(RMB(单元格))-3),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;") & TEXT(RIGHT(RMB(单元格),2),"[dbnum2]0角0分;;整")

示例:

image.png

 

4、多条件查找

=Lookup(1,0/((条件1*条件2*条件3...)),返回值区域)

示例:如下图所示要求,根据菜名和产地查找价格

=LOOKUP(1,0/(A2:A8=A12)*(B2:B8=B12),C2:C8)

image.png


5、计算不重复值个数的公式

=SUMPRODUCT(1/COUNTIF(区域,区域))

示例:统计A列的菜的种类有多少

=SUMPRODUCT(1/COUNTIF(A2:A15,A2:A15))

image.png


6、多条件判断公式

=IF(AND(条件1,条件2...条件n),同时满足条件返回的值,不满足条件返回的值)

=IF(OR(条件1,条件2...条件n),同时满足任一条件返回的值,不满足条件返回的值)

示例:满足菜名是芹菜,价格小于5元条件的,返回低价

image.png


7、多条件求和、计数公式

=Sumproduct(条件1*条件2*条件3...数据区域)

示例:统计产地厅内无智慧家庭体验区中牟的芹菜产量

=SUMPRODUCT((A18=A2:A15)*(B2:B15=B18)*D2:D15)

注:Sumproduct可以对数组进行处理后再设置条件,同时也可以对文本型数字进行计算,而Sumifs函数则不可。

image.png


8、多表统计公式

=Sum(sheetM:sheetN!单元格地址)

=AVERAGE(sheetM:sheetN!单元格地址)

……

可以汇总、均值工作表名1到工作表名2之间所有sheet表区域,包含sheetM和sheetN

示例: 如下图所示,汇总Sheet1到Sheet7表D2单元格。=SUM(Sheet1:Sheet7!D2)

image.png


以上,多学多看多用,你也会成为Excel达人。