帖子主题: [学习小屋] 拒绝加班!18个工作中必会的Excel函数公式 发表于:Wed Aug 16 13:54:53 CST 2017

WANG5949_
226

主题帖

294

问答数

论坛等级: 白金会员
回帖:2697
经验:7339
鲜花:2
金币:108494
离线
0
点击:321 回复:2    楼主

Excel函数公式大全你可能看的多了,但值得你收藏和实用的公式却不多。今天把工作中最常用对新手又有些难度的18个公式整理出来,个个都很实用,分享给同学们。


1 可能有负值的完成率公式(为了写这个公式研究了一天时间,下面的公式是简化后的

【例1】如下图所示,要求根据B的实际和C列的预算数,计算完成率。

E2 =IF(C3<0,2-B3/C3,B3/C3)

2 同比增长率公式

【例2】如下图所示,B列是本年累计,C列是去年同期累计,要求计算同比增长率。(D列是直接相除的,D4结果明显不对

E2 =(B2-C2)/IF(C2>0,C2,-C2)

3 Vlookup函数模糊查找

【例2】查找包含AAA名称的产品价格

=VLOOKUP("*"&A10&"*",A2:B6,2,0)


4 Vlookup区间查找公式

【例4】如下图所示,要求根据左表的提成比率表,在右表中根据销售额在G列查找适用的比率。

=VLOOKUP(F3,$B$3:$C$11,2)


5 Lookup多条件查找公式

【例5】如下图所示要求,同时根据姓名和工号查找职位。

=LOOKUP(1,0/((B2:B6=B9)*(A2:A6=C9)),E2:E6)


6 Lookup函数匹配查找

【例6】如下图所示表1是公司的全称。要求在表2中根据公司的简称查找相对应的B列的应付账款。

B10=LOOKUP(9^9,FIND(A10,A$3:A$6),B$3:B$6)


7 index+match反向查找公式

【例7】如下图所示,要求根据产品名称,查找编号。

C13 =INDEX(B5:B10,MATCH(B13,C5:C10,0))


8 合并单元格求和

例8】:如下图所示,要求在D列对A列的类别求和。

D3=SUM(C3:C12)-SUM(D4:D12)

公式输入方法:选取D3:D11,在编辑栏中输入公式,再按ctrl+enter完成批量输入。

9 多个工作表求和(表格样式完全相同)

【例9】下图所示的工作簿中,有19个格式完成相同(产品排列也相同)的表格需要求和。

B2 =SUM(Sheet1:Sheet19!B2)

10 姓名和手机号码分隔公式。

【例10】拆分A列的姓名和电话到B列和C列。

B2 =LEFTB(A2,SEARCHB("?",A2)-1)

C2=MIDB(A2,SEARCHB("?",A2),11)

11 计算两个日期间隔的月份。

=Datedif(开始日期,结束日期,"M")


12 多条件模糊求和公式

【例11】如下图所示,要求在C10计算郑州地区电视的销量

C11= Sumifs(C2:C7,A2:A7,"*"&A11&"*",B2:B7,B11)

13 多表合并公式

【例12】有N张格式相同的日报表,工作表名称为1、2、3....

要合并到一张表中

B2:=INDIRECT(B$1&"!B"&ROW())


14 到期日推算

【例13】贷款日期是2016-6-12日,三个月后到期日是哪一天。

C4=Edate(B4,3)

注:如果是推算N天后的日期,用C4+N即可

15 工作日计算

【例13】根据入职日期和截止日计分别按双击和单休(周日休)计算工作日天数。

D2=NETWORKDAYS(B2,C2,E5:E7)

E2=NETWORKDAYS.INTL(B2,C2,11,E5:E7)


注:11的意思是周日休息,其他的你在设置公式可以看到各个参数的含义



16 间隔小时计算

【例14】根据开始时间和截止时间,计算间隔小时数

C2 =TEXT(B2-A2,"[h]")

注:加[]是为了正常显示大于24的小时数


17 合并多单元格字符公式

【例13】在C2单元格把

c2=PHONETIC(A2:A7)

说明:Phonetic函数只能对字符型内容合并,数字不可以。

18 IF复合条件判断公式

如果a1的值大于60B1的值小于100,显示合格,否则显示不合格

=IF(AND(A1>60,B1<100),"合格","不合格")

如果a1的值大于60B1的值小于100,显示合格,否则显示不合格

=IF(OR(A1>60,B1<100),"合格","不合格")

注:

and()表示括号内的多个条件要同时成立

or()表示括号内的多个条件任一个成立

 

坚持才是胜利!!!将来的你,一定会感谢现在拼命的自己~~~
最美女会计

  • 央企,工作5年,年薪12万
  • 自立自强拒绝做家庭主妇
  • 美女律师弯道超车一次过税务师
  • 妈妈级考生的崛起之路!
  • 在职妈妈一年过10科
WANG5949_
226

主题帖

294

问答数

论坛等级: 白金会员
回帖:2697
经验:7339
鲜花:2
金币:108494
离线
0
1楼  
发表于:2017-08-16 13:55:07 只看该作者

坚持才是胜利!!!将来的你,一定会感谢现在拼命的自己~~~
WANG5949_
226

主题帖

294

问答数

论坛等级: 白金会员
回帖:2697
经验:7339
鲜花:2
金币:108494
离线
0
2楼  
发表于:2017-08-16 13:55:23 只看该作者
函数,我想现在很多人都怕这个。

坚持才是胜利!!!将来的你,一定会感谢现在拼命的自己~~~
会计培训
正保培训项目
快速回复主题

拒绝加班!18个工作中必会的Excel函数公式


  您尚未登录,发表回复前请先登录,或者注册
  Ctrl+Enter直接发表