1、完成率公式(可能有负值) 【例15】如下图所示,要求根据B的实际和C列的预算数,计算完成率。 E2 =IF(C3<>B3/C3,B3/C3) 2、同比增长率公式 【例16】如下图所示,B列是本年累计,C列是去年同期累计,要求计算同比增长率。 E2 =(B2-C2)/IF(C2>0,C2,-C2) 3、Vlookup函数模糊查找 【例1】查找包含AAA名称的产品价格 =VLOOKUP('*'&A10&'*',A2:B6,2,0) 4、Vlookup区间查找公式 【例2】如下图所示,要求根据左表的提成比率表,在右表中根据销售额在G列查找适用的比率。 =VLOOKUP(F3,$B$3:$C$11,2) 5、Lookup多条件查找公式 【例3】如下图所示要求,同时根据姓名和工号查找职位。 =LOOKUP(1,1/(B2:B6=B9)*(A2:A6=C9),E2:E6) 6、Lookup函数匹配查找 【例4】如下图所示表1是公司的全称。要求在表2中根据公司的简称查找相对应的B列的应付账款。 B10=LOOKUP(9^9,FIND(A10,A$3:A$6),B$3:B$6) 7、index+match反向查找公式 【例5】如下图所示,要求根据产品名称,查找编号。 C13 =INDEX(B5:B10,MATCH(B13,C5:C10,0)) 8、合并单元格求和 【例6】:如下图所示,要求在D列对A列的类别求和。 D3=SUM(C3:C12)-SUM(D4:D12) 公式输入方法:选取D3:D11,在编辑栏中输入公式,再按ctrl+enter完成批量输入。 9、多个工作表求和(表格样式完全相同) 【例7】下图所示的工作簿中,有19个格式完成相同(产品排列也相同)的表格需要求和。 B2 =SUM(Sheet1:Sheet19!B2) 10、姓名和手机号码分隔公式。 【例8】拆分A列的姓名和电话到B列和C列。 B2 =LEFTB(A2,SEARCHB('?',A2)-1) C2=MIDB(A2,SEARCHB('?',A2),11) 11、计算两个日期间隔的月份。 =Datedif(开始日期,结束日期,'M') 12、多条件模糊求和公式 【例10】如下图所示,要求在C10计算郑州地区电视的销量 C11= Sumifs(C2:C7,A2:A7,'*'&A11&'*',B2:B7,B11) 13、多表合并公式 【例11】有N张格式相同的日报表,工作表名称为1、2、3....
B2:=INDIRECT(B$1&'!B'&ROW())
【例14】贷款日期是2016-6-12日,三个月后到期日是哪一天。 C4=Edate(B4,3) 注:如果是推算N天后的日期,用C4+N即可 15、合并多单元格字符公式 【例13】在C2单元格把 c2=PHONETIC(A2:A7) 说明:Phonetic函数只能对字符型内容合并,数字不可以。 16、IF复合条件判断公式 如果a1的值大于60且B1的值小于100,显示合格,否则显示不合格 =IF(AND(A1>60,B1<> 如果a1的值大于60或B1的值小于100,显示合格,否则显示不合格 =IF(OR(A1>60,B1<> 注:and()表示括号内的多个条件要同时成立 or()表示括号内的多个条件任一个成立 |
|