1、单条件求和 计算出西瓜的总销量数据,在F2单元格写入公式=SUMIF(B:B,E2,C:C),回车确定。 2、多条件求和 在G2单元格中写入公式=SUMIFS(C:C,B:B,F2,A:A,E2) 3、按照日期区间求和 在H2单元格中写入公式=SUMIFS(C:C,A:A,E2,A:A,F2,B:B,G2) 4、利用通配符模糊求和 我们需要查找地区是“*西部”的对应利润,实际地区中有“西部”、“中西部”。 在B10单元格中写入公式=SUMIF(A2:A7,A10,B2:B7) 5、合并单元格的求和 6、按月份求和 输入公式 =SUM((MONTH(A2:A19)=D2)*B2:B19) 7、根据月份自动汇总1-N月份之和 选取1月,显示1月份的值 选取5月,汇总1-5月之和 选取12月,汇总1-12月之和 输入公式=SUM(OFFSET($B2,,,,MATCH($N$1,$B$1:$M$1,0))) 二、查找类公式 (屏蔽错误值查找、反向查找、多条件查找、借用COLUMN、MATCH实现多个查找,多表查询) 1、VLOOKUP+IFERROR——消除查找错误值的影响 如果不想看到查找错误值,就可以借助IFERROR函数来隐藏错误值。 公式为:=IFERROR(VLOOKUP($K2,$A:$I,3,0),"工号有误") 2、反向查找数据 通过姓名来匹配公号,可以输入公式:=INDEX(A:A,MATCH(D2,B:B,0)) 3、多条件查找 VLOOKUP的第一参数还支持用&连接多个单元格的内容,用于多条件查询: 4、借用COLUMN,搞定有序变化的返回列 如图所示,要找出A、B、D产品在1、2、3月的销量。 =VLOOKUP($A14,$A$1:$G$10,COLUMN(B2),FALSE) 5、MATCH——自动识别返回列 如果是1月、3月、5月的序列。 =VLOOKUP($A14,$A$2:$G$10,MATCH(B$13,$A$1:$G$1,0),FALSE) 6、借助INDIRECT函数可以实现多表查询数据 如下图所示: 三、判断类公式 (单条件判断、多条件判断、) 1、单条件判断 当商品库存大于等于30时,在“库存提示”处显示“充足”,小于“30”时,显示“补货”。 =IF($D5>=30,"充足","补货") 2、多条件判断 年会摸奖,摸到红色条,奖励微波炉,摸到绿色条奖励自行车,摸到蓝色条奖励数码相机。 C2中输入公式: =IF(B2="红色","微波炉",IF(B2="绿色","自行车","数码相机")) 3、且字判断 成为优秀青年有两个并列的条件:年龄小于30,成绩大于90。这个时候我们就需要使用AND函数把两个条件并列在一起。 在D2中输入公式: =IF(AND(B2<30,C2>90),"优秀青年","") 4、或字判断譬如:根据销量或盈利金额判定产品是否畅销。只有销量大于1000或者盈利大于10000的产品属于畅销产品。 用OR函数把条件组在一起。D2中输入公式: =IF(OR(B2>1000,C2>10000),"畅销","不畅销") 四、时间类函数 1、根据出生日期计算年龄 公式:=DATEDIF(D2,TODAY(),"y") 2、根据身份证号码计算年龄3、根据入职日期计算员工工龄五、统计类公式 1、基本的统计公式 如下图所示: 2、通配符模糊统计 比如,当需要查找王姓员工数量,可以输入公式=COUNTIF(A2:A14,"王*"),然后回车。 如果要查找姓名为三个字的员工数量,可以输入公式=COUNTIF(A2:A14,"???"),然后回车。 3、条件中可以嵌入函数公式 比如统计业绩大于平均值的人数,输入公式: =COUNTIF(D2:D14,">="&AVERAGE(D2:D14))即可。 4、多个条件进行统计 如上图所示,{"销售一部","销售二部"}是数组,作为COUNTIF的条件,返回两个值{5,4},然后再用SUM函数进行相加,得到结果。 5、按类别或组别编号 譬如按部门编号,如下: 6、多条件计数 譬如统计业绩>5000,<10000的人数,输入公式=SUM(COUNTIF(D24:D36,{">5000",">10000"})*{1,-1})即可。 六、提取函数 (TEXTBEFORE,TEXTAFTER) 1、按符号提取个人信息的姓名和手机号 冒号前的内容就是姓名,公式为=TEXTBEFORE(A2,":"); 冒号后的内容就是手机号,公式为=TEXTAFTER(A2,":")。 2、TEXTBEFORE和TEXTAFTER组合提取 提取QQ号的公式=TEXTBEFORE(TEXTAFTER(A2,":"),"@") 3、启动第六参数,替换掉错误值 =TEXTBEFORE(B2,"g",,1,,"非克计量") |
|