|
在Excel函数使用中有很多固定的套路。学会了就可以解决工作中大部分问题，今天兰色准备了10个万能公式，新手们可要收藏好了。 1、屏蔽错误值公式 =IFERROR(公式,公式返回错误值返回的值) 例：当Vlookup查找不到结果时显示空白 =IFERROR(VLOOKUP(A9,$A$1:$D$5,3,0),'') 2、多条件判断公式 =IF(AND(条件1,条件2...条件n),同时满足条件返回的值,不满足条件返回的值) =IF(OR(条件1,条件2...条件n),同时满足任一条件返回的值,不满足条件返回的值) 例：同时满足金额小于500且B列内容为“未到期'时在C列输入”补款“ =IF(AND(A2 3、多条件求和、计数公式 多条件求和 =Sumproduct(条件1*条件2*条件3..*数据区域) 多条件计数 =Sumproduct(条件1*条件2*条件3..*1) 例：统计A产品3月的销售合计 =SUMPRODUCT((MONTH(A3:A9)=3)*(B3:B9='A')*C3:C9) 注:和sumifs相比速度虽然慢了点，但Sumproduct可以对数组进行处理后再设置条件，同时也可以对文本型数字进行计算，而Sumifs函数则不可。 4、多条件查找公式 =Lookup(1,0/((条件1*条件2*条件3...)),返回值区域） 示例：如下图所示要求，同时根据姓名和工号查找职位 =LOOKUP(1,0/(B2:B6=B9)*(A2:A6=C9),E2:E6) 5、提取任意位置字符串的数字 =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(A1,ROW(1:99),1)^0,0),ROW(1:9))*1) 注：如果字符串超过100位，就把99调大 6、截取字符串中任一段的公式 =TRIM(MID(SUBSTITUTE(字符串,'分隔符',REPT(' ',99)),(N-1)*99,99)) 例：从用“-”分隔的字符串中，截取第2个值 =TRIM(MID(SUBSTITUTE($A2,'-',REPT(' ',99)),(2-1)*99,99)) 7、分离字母和汉字 汉字在前 =LEFT(单元格,LENB(单元格)-LEN(单元格)) 汉字在后 =Right(单元格,LENB(单元格)-LEN(单元格)) 示例： =LEFT(A2,LENB(A2)-LEN(A2)) 8、计算不重复值个数的公式 =SUMPRODUCT(1/COUNTIF(区域,区域)) 例：统计B列的客户数量 =SUMPRODUCT(1/COUNTIF(B2:B19,B2:B19)) 9、多工作表同一位置求和公式 =SUM(开始工作表:结束工作表!单元格) 例：对Sheet1到shee100之间所有工作表的A1单元格求和 =SUM(Sheet1:Sheet100!A1) 10、金额大写转换公式 =TEXT(LEFT(RMB(单元格),LEN(RMB(单元格))-3),'[>0][dbnum2]G/通用格式元;[ 示例： 兰色说: 经常有同学问怎么才能快速学好Excel公式，兰色总是给出同样的4字答案：多看、多练。如今天的10个万能公式，你不要看一次就翻篇了，收藏起来有空就看看，然后再动手练练。 看完教程，记得帮兰色点一下在看哦 |
|
|
