- ?
Excel 表格的所有公式用法……帮你整理齐了!
inic
展开
批量输入公式批量修改公式快速查找公式显示公式部分计算结果保护公式隐藏公式显示所有公式把公式转换为数值显示另一个单元格的公式把表达式转换为计算结果快速查找公式错误原因
1批量输入公式
选取要输入的区域,在编辑栏中输入公式,按CTRL+ENTER即可批量填充公式。
2批量修改公式
如果是修改公式中的相同部分,可以直接用替换功能即可。
3快速查找公式
选取表格区域 - 按Ctrl+g打开定位窗口 - 定位条件 - 公式,即可选取表中所有的公式
4显示公式部分计算结果
选取公式中的要显示的部分表达式,按F9键
按F9键后的结果
想恢复公式,按esc退出编辑状态即可。
5保护公式
选取非公式的填写区域,取消单元格锁定。公式区域不必操作。
设置单元格格式后,还需要保护工作表:审阅 - 保护工作表。
6隐藏公式
隐藏公式和保护公式多了一步:选取公式所在单元格区域,设置单元格格式 - 保护 - 勾选“隐藏” - 保护工作表。
隐藏公式效果:
7显示所有公式
需要查看表中都设置了哪些公式时,你只需按alt+~组合键(或 公式选项卡 - 显示公式)
把公式转换为数值
8把公式转换为数值
公式转换数值一般方法,复制 - 右键菜单上点123(值)
9显示另一个单元格的公式
如果想在B列显示A列的公式,可以通过定义名称来实现。
公式 - 名称管理器 - 新建名称:G =get.cell(6,sheet3!a4)
在B列输入公式=G,即可显示A列的公式。
excel2013中新增了FORMULATEXT函数,可以直接获取公式文本。
10把表达式转换为计算结果
方法同上,只需要定义一个转换的名称即可。
zhi =Evaluate(b2)
在B列输入公式 =zhi即可把B列表达式转换为值
11快速查找公式错误原因
当一个很长的公式返回错误值,很多新手会手足无措,不知道哪里出错了。兰色介绍排查公式错误的技巧,3秒就可以找到错误。
下面兰色做了一个简单的小例子
【例】:如下图所示,单元格的公式返回值错误。要求排查出公式的哪部分出现了错误。
操作方法:
1、 打开单元格左上角绿三角,点“显示计算步骤”
2、在打开的“公式求值”窗口中,求值会自动停在即将出错的位置。这时通过和编辑栏中的公式比对,就可以找出产生错误的单元格。(D7)
如果公式中有多处错误,可以先修正前一次,然后再点显示计算步骤,查找下一处错误。
- ?
Excel函数公式:含金量极高的简单实用函数公式,直接套用
班霍尔姆
展开
职场中的人少不了与数据打交道,Excel中的函数公式无疑是提高工作效率的神器,但是Excel中的函数公式太多,一下记不住,但我们如果掌握常用的函数公式,则会提高工作效率。
一、IF+COUNTIF:查找重复值。
方法:
在目标单元格中输入公式:=IF(COUNTIF($E$3:$E$9,E3)>1,"重复","")。
二、IF+COUNTIF:重复两次以上提示。
方法:
在目标单元格中输入公式:=IF(COUNTIF(E$3:E3,E3)>1,"重复","")。
备注:
重复提示和重复两次及以上提示,COUNTIF函数的第一个单数,暨范围不一样,重复提示的范围是当前所有范围的绝对引用。而重复两次以上提示是第一条记录到当前记录的相对引用。
三、TEXT+MID:提取出生日期。
方法:
1、选定目标单元格。
2、在单元格中输入公式:=TEXT(MID(C3,7,8),"0-00-00")。
3、Ctrl+Enter填充。
四、DATEDIF:计算年龄。
方法:
1、选定目标单元格。
2、输入公式:=DATEDIF(D3,NOW(),"y")。
3、Ctrl+Enter填充。
五、IF+MOD+MID:提取性别。
方法:
1、选定目标单元格。
2、输入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。
3、Ctrl+Enter填充。
六、RANK:排名。
方法:
1、选定目标单元格。
2、输入公式:=RANK(E3,$E$3:$E$9,1)。
3、Ctrl+Enter填充。
七、SUMPRODUCT+COUNTIF:中国式排名。
排名过程中,我们经常会遇到成绩或数值相同的情况,如果用RANK函数来时排名,其计算的结果不符合我们的实际需求。于是就有了中国式排名。
方法:
1、选定目标单元格。
2、输入公式:=SUMPRODUCT(($E$3:$E$9>E3)/COUNTIF($E$3:$E$9,$E$3:$E$9))+1。
3、Ctrl+Enter填充。
八、SUMPRODUCT+COUNTIF:统计不重复个数。
方法:
在目标单元格输入公式:=SUMPRODUCT(1/COUNTIF(B3:B9,B3:B9))。
九、INDEX+MATCH+COUNTIF:提取不重复的值。
方法:
1、在目标单元格输入公式:=INDEX($B$3:$B$9,MATCH(,COUNTIF($J$2:J2,$B$3:$B$9),))&""。
2、快捷组合键:Ctrl+Shift+Enter填充。
备注:
填充时,用快捷组合键Ctrl+Shift+Enter,而不是Ctrl+Enter。因为此公式是数组公式。
- ?
excel技巧:只需3秒,找出excel公式哪里错了
忆当年
展开
EXCEL当我们辛辛苦苦做了一个很长的公式需要计算出成果时却返回错误值给我们了,很多新手会手足无措,不知道哪里出错了。今天就为大家带来一个轻轻松松搞定公式错误的解决方法,3秒钟就可以找到错误。
下面给大家做了一个简单的小例子:
【例】:如下图所示,这就说明我们单元格的公式返回值出现了错误。要求排查出公式到底是哪部分出现了错误,我们来具体看一下
操作方法吧!
1、 我们现在需要打开单元格左上角的绿三角,然后点“显示计算步骤”
22、在打开的“公式求值”窗口中,求值会自动停在即将出错的位置。这时只要通过和编辑栏中的公式对比一下,就可以找出产生错误的单元格。(D7)
3怎么样今天的知识分享大家学会了吗?如果在我们的公式中还有多处的错误,可以先修正前一次,然后再选择显示计算步骤,查找下一处错误,直到正确为止。
好了,今天的分享就到这里了,如果您还没有关注我们就请点击关注吧,如果您觉得的此文非常有用就为我们点赞哦!您有更好的方法也希望您在评论区与大家一起分享出你的技术,关注后每天学会更多办公软件技巧,我们一起来提高吧!
- ?
excel 小技巧 你竟然,写了那么长的IF公式
毒药
展开
图文/ 小龙,兰色整理
作者简介:小龙, Excel精英讲师团函数班辅导、讲师。精通Excel函数应用。
昨天遇到一位同学问如下问题
如下图所示:
当A列数据为整数时,该数乘2当数据为小数时,小数部分小于等于0.5,则该数乘2加1,小数部分大于0.5时,该数乘2加2
最初写的答案是用IF函数
=IF(INT(A2)=A2,A2*2,IF(A2-INT(A2)<=0.5,INT(A2)*2+1,IF(A2-INT(A2)>0.5,INT(A2)*2+2)))
马上有同学提出来了,最后一个If是多余的,没有任何意义,因为当小数部分不小于等于0.5,那么必然会大于0.5,因此
=IF(INT(A2)=A2,A2*2,IF(A2-INT(A2)<=0.5,INT(A2)*2+1,INT(A2)*2+2))
好象是大功告成,其实....看了下面的公式,你会如何感想:
=CEILING(A2,0.5)*2
这么牛叉的函数,不学怎么行。下面就来学习一下
Ceiling函数
=ceiling(number,significance)
返回将参数 number 向上舍入(沿绝对值增大的方向)为最接近的指定基数的倍数。它有两个参数,第一参数为需要舍入的数值,第二参数为一个倍数关系。例如:
=CEILING(2.5,1)数值2.5对于1来说,向上最接近的1的倍数,就是3,因此函数结果为3
如果任何一个参数是非数值型,则 CEILING 返回 #VALUE! 错误值。不论参数 number 的符号如何,数值都是沿绝对值增大的方向向上舍入。 如果 number 正好是 significance 的倍数,则不进行舍入。如果number 和 significance 都为负,则对值按远离 0 的方向进行向下舍入。如果number 为负,significance 为正,则对值按朝向 0 的方向进行向上舍入。
如果你觉得该剧终,就错了,原来公式还可以再简化:
=CEILING(A2*2,1)
好吧,借用大家的留言:老太太倒地我都不扶,就扶你。
对于新手来说,公式还是以易懂为主,长一点没关系。但如果能象小龙老师写出这么简捷公式,是不是更帅呢?
- ?
Excel公式大全,很实用
Owendo
展开
1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。
2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式:=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。
4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式:
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。
1、求和: =SUM(K2:K56) ——对K2到K56这一区域进行求和;
2、平均数: =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数;
3、排名: =RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名;
4、等级: =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))
5、学期总评: =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩;
6、最高分: =MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分;
7、最低分: =MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分;
8、分数段人数统计:
(1) =COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数;假设把结果存放于K57单元格;
(2) =COUNTIF(K2:K56,">=95")-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格;
(3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格;
(4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ——求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格;
(5)=COUNTIF(K2:K56,">=70")-SUM(K57:K60) ——求K2到K56区域70~84.5分的人数;假设把结果存放于K61单元格;
(6)=COUNTIF(K2:K56,">=60")-SUM(K57:K61) ——求K2到K56区域60~69.5分的人数;假设把结果存放于K62单元格;
(7) =COUNTIF(K2:K56,"
- ?
这2个Excel公式,太好用了!
夏天
展开
今天的Excel公式和平时用的不一样,不知道是哪位高人想出的。
1、公式中加备注
【例1】 如果公式中加入一个数字,可能会让别人不清楚它的来历,如:
=SUM(B6:M6)+10000
加上备注后就明白了
=SUM(B6:M6)+10000+N("注:10000是赔付受伤工人款项")
原理:N可以把字符串转换为0,+N()后公式原值不变
2、带名子的excel公式
【例2】如下面的经营分析表中,净利润的公式计算比较复杂,你能一眼看出利润是怎么计算出的吗?
=B8+B14+B20+B26+B32+B38
我们给各个费用所在的单元格分别定义个名称:
选取单元格 - 在名称框中输入名称 - 按enter完成
然后重新设置公式后,会发现单元格引用已自动显示成定义的名称:
=汽车销售+维修+二手车+保险+上牌+加装
公式马上变得一目了然。
如果你想看某个项目的具体利润值,选中项目名称按F9键即可。
很多高手做的Excel表格中,你都可以看到用定义的名称代替单元格,以前觉得很迷惑,学会今天的技巧,以后只需打开 公式 - 名称管理器,就可以看出公式的含义了。
- ?
Excel函数公式:含金量超高的SUBSTITUTE函数实用技巧解读
无施
展开
Excel中,查找和替换功能应用的非常普遍,但我们都是通过【查找替换】功能来实现的……其实,除了【查找替换】功能外,SUBSTITUTE函数也可以实现替换功能。
一、SUBSTITUTE函数作用及语法结构。
作用:将字符串中的部分字符串以新字符串进行替换。
语法:=SUBSTITUTE(字符串或单元格引用,被替换字符串,替换字符串,替换起始位置)。
当参数“替换其实位置”省略时,标识替换所有的旧字符串。
目的:将“姓名”中的“、”替换为空值。
方法:
在目标单元格中输入公式:=SUBSTITUTE(C3,"、","")。
二、SUBSTITUTE扩展应用。
目的:和LEN函数配合使用统计人数。
方法:
在目标单元格中输入公式:=LEN(C3)-LEN(SUBSTITUTE(C3,"、",""))+1。
解读:
1、LEN函数的主要作用是提取字符串的长度,用函数SUBSTITUTE将字符串中的“、”替换后,字符串的长度将减短。
2、当前字符串的长度减去替换后字符串的长度,再加1便得到实有人数。
结束语:
利用LEN函数和SUBSTITUTE来计算实际人数或间隔数,非常的实用,而且也不会出错。是年终统计获奖人数等需求的最佳选择函数。
- ?
Excel技巧:5个极度烧脑,但极其实用的Excel函数公式!
Douglas
展开
今天和大家分享的几条函数公式,一个比一个烧脑,但又非常实用。因为烧脑,所以计算原理和过程我们就不解释了。因为实用,所以建议大家收藏,用到的时候可以直接拿去套用。
具体操作如下:
1、文本格式的时间转换,烧脑指数★
如下图所示,将A列的文本时间转换成分钟。
B2公式:=SUM(("0"&TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"分钟","/1440"),"小时","/24"&REPT("",99)),"天","/1"&REPT("",99)),{0,1,2}*99+1,99))))*1440
2、数据模糊匹配,烧脑指数★★
如下图所示,需要根据D:E列的数据,计算A里人名的性别。需要注意的是,D列的人名和A列的人名并非完全对应,例如看见星光和看星光。
B2公式:=INDEX(E:E,MATCH(,MMULT(-ISERR(FIND(MID(D$1:D$5,COLUMN(A:X),1),A2)),ROW($1:$24)),))
3、 数字小写转大写,烧脑指数★★★
Excel自带的小写转换大写的自定义格式,或者函数,都无法处理角和分的问题。
B2数组公式:=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整")
4、提取单元格内数值,烧脑指数★★★★
如下图所示,需要将A列数据中的数值提取到B列,数值的分布很零散,基本无规律可循。
B2数组公式:=MID(SUM(MID("01"&A2,1+LARGE(ISNUMBER(MID(1&A2,ROW($1:$48),1))*ROW($1:$48),ROW($1:$18)),1)*10^ROW($2:$19))%,2,100)
说明:但该公式只能正确提取15个数值,超过15位……还是用自定义函数吧。
代码如下:
Function Getnum(rg As Range)
Dim reg As Object
Set reg = CreateObject("VBScript.RegExp")
With reg
.Global = True
.Pattern = "[^0-9]"
End With
Getnum = reg.Replace(rg.Value, "")
End Function
5、单元格内数值求和,烧脑指数★★★★★
如下图所示,需要将A列混合文本中的数值部分在B列汇总求和。
B2数组公式:=SUM(TEXT(LEFT(TEXT(MID(A2&"a",COLUMN($2:$2),ROW($1:$15)),),ROW($1:$15)-1),"0;-0;0;!0")*ISERR(-MID(A2,COLUMN($2:$2)-1,2)))
上面五个实用技巧,公式虽然复杂但是很实用。如有不懂之处,欢迎大家留言讨论。
期待你的关注和转发分享,更多精彩内容在持续更新中.............
往期精彩文章:
电脑桌面太乱,Excel可以快速整理电脑文件,你值得学习!
要想学好excel,这几个技巧不得不掌握!
- ?
Excel中含金量极高的几个万能公式,点进来你不会后悔!
海瑶
展开
Excel函数公式的威力强大众所周知,但很多人还是用不好,原因之一是没有将千变万化的各种形式的公式实质性看透,只懂皮毛。
为了让大家更容易理解公式的框架结构和扩展方法,今天带来的这几个万能公式,会结合Excel实例展开,并且给出不同应用场景下的扩展方法。
1 多条件计数统计
2 多条件求和统计
3 多条件查找引用数据
万能公式的万能之处,最终不在于公式本身,而在于使用公式的人。
1 多条件计数统计
按照多个条件进行计数统计,是工作中很常见的需求。
咱们先看个按照两个条件统计符合条件的个数的数据的案例吧
案例场景截图如下:
F2=COUNTIFS(A:A,E2,C:C,">500")
多条件计数统计的万能公式
=COUNTIFS(条件区域1,条件1,条件区域2,条件2.....条件区域N,条件N)
这样,即使有再多的条件,以此类推,在公式中添加参数扩展即可。
2 多条件求和统计
按照多个条件进行求和汇总统计,也是办公一族常遇到的事了。
案例场景截图如下:
G2=SUMIFS($D:$D,$A:$A,$F2,$C:$C,G$1)
注意这个公式中的单元格混合引用的应用
多条件求和统计的万能公式
=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2.....条件区域N,条件N)
利用SUMIFS,先声明求和区域,再依次罗列各种条件区域和条件,即使有再多的条件,以此类推,在公式中添加参数扩展即可。
3 多条件查找引用数据
查找引用数据的需求,几乎每天都有,如果你还只会Vlookup,那很多问题是无法解决的。
比如下面的这个多条件查找引用数据的问题。
案例场景截图如下:
=LOOKUP(1,0/(($B$2:$B$13=F2)*($C$2:$C$13=G2)),$D$2:$D$13)
多条件查找引用数据的万能公式
=LOOKUP(1,0/((查找条件1)*(查找条件2)*......*(查找条件N)),查找结果所在区域)
利用LOOKUP函数可以忽略错误值查找,以及支持区间判断查找的特性,再多的查找条件,按如上万能公式套用即可。
今天的分享就到这里,希望能够帮到大家轻松应对各种情况的工作日计算!
如果你觉得有用,就分享给朋友们看看吧~ 记得关注我哦 谢谢大家!! 明天再见
- ?
excel如何计算相对平均偏差?
晦涩
展开
1.极差不匀(又名相对极差)
极差是指一组数据的最大值与一组数据的最小值之差
相对极差一般是指极差(绝对极差)/数值平均值
可以使用公式
=ROUND((MAX(A2:A4)-MIN(A2:A4))/AVERAGE(A2:A4)*100,2)&"%"
如下图所示
2.平均差不匀(又名相对平均偏差):
可以使用公式
=ROUND(AVEDEV(A1:A13)/AVERAGE(A1:A13)*100,2)&"%"
如下图所示
3.均方差不匀(又名相对标准偏差):
可以使用公式
=ROUND(STDEV(A1:A13)/AVERAGE(A1:A13)*100,2)&"%"
如下图所示
(本文内容由百度知道网友茗童贡献)
excel极差公式
-
1、只需3秒快速实现求和
-
2、如何快速填充序号
-
3、如何自动填充序号(公式法)
-
4、数据条的神奇应用
-
5、多文本快速合并
-
6、查找与替换的不同玩法
-
7、快速定位到指定区域
-
8、数据排序、工资条制作
-
9、快速筛选(模糊、精确筛选)
-
10、快速插入空行
-
11、快速删除空行
-
12.快速跳转到天涯海角
-
13、.同时查看两个Excel文件
-
14、用条件格式扮靓报表
-
15、一键插入Excel图表
-
16、批量处理行高、列宽
-
17、利用拆分功能查看数据
-
18、批量录入相同内容
-
19、工作表快速跳转
-
20、批量录入表格模板(精品课程)
-
21、Excel函数与公式的应用、公式循环引用的查找
-
22、IF函数单条件判断同比增长
-
23、用sum函数 格式相同,连续多表数据汇总
-
24、excel快捷键
-
25、VLOOKUP函数——根据销售员匹配销售额
-
26、统计各部门销售总额
-
27、统计指定条件个数
-
28、怎样输入当前日期和时间、星期数
-
29、销售业绩排名
-
30、Sumproduct函数-万能函数(销售额汇总求和)
-
31、根据销售员,地区,商品名称汇总
-
32、批量替换PPT字体
-
33、给销售额数据批量添加万元单位
-
34、一秒快速核对两列数据
-
35、快速定位到指定单元格或区域
-
36、快速制作双行标题工资条
-
37、给你的表格做个瘦身
-
38、快速打开常用的Excel文件
-
39、快速打开多个Excel文件
-
40、利用创建组—快速隐藏/展开多列数据
-
41、快速制作下拉菜单
-
42、复制粘贴表格,如何保留数据源列宽格式一致?
-
43、两列数据位置互换
-
44、1秒钟扮靓报表——如何实现表格隔行换色
-
45、快速删除重复记录——保留唯一值
-
46、快速向下填充、向右填充,文本或公式
-
47、给Excel文件添加密码
-
48、插入带图片的批注
-
49、输入公式后不计算?
-
50、如何设置单元格缩进
-
51、快速解决Excel表格总显示货币格式
-
52、批量添加万元单位
-
53、你会四舍五入么?
-
54、用RAND函数机选彩票
-
55、冻结首行你会么?
-
56、超链接的高级应用
-
57、IFERROR函数-屏蔽错误值
-
58、批量填充颜色
-
59、录入数据
-
60、快速输入工号
-
61、快速行列转置
-
62、自定义缩放界面
-
63、多个单元格同时输入
-
64、如何计算立方米?
-
65、快速制作双行标题工资条
-
66、输入带方框的√和×
-
67、快速将姓名对齐
-
68、快速输入性别
-
69、按单位职务排序
-
70、自动计算合同到期日期
-
71、计算时间间隔
-
72、日期和时间的拆分
-
73、快速处理不规范的日期格式
-
74、快速填充合并单元格
-
75、效率加倍的快捷键
-
76、快速复制表格和对象
-
77、快速创建工作表副本
-
78、快速复制序列号
-
79、快速显示公式
-
80、多个单元格同时输入
-
81、快速调整显示比例
-
82、快速自动填充
-
83、快速填充(Ctrl+E)
-
84、Ctrl与数字键结合
-
85、快速将多列数据整理为1列
-
86、快速将1列数据拆分为多列
-
87、快速定位公式
-
88、快速录入数据
-
89、快速累计求和
-
90、身份证号码显示为0怎么办?
-
91、快速制作斜线表头
-
92、文本竖向显示
-
93、神奇的监视窗口
-
94、不一样的格式刷
-
95、快速美化图表
-
96、快速生成当前日期
-
97、快速找出循环引用
-
98、快速提取信息
-
99、二维表快速转换为一维表
-
100、快速多表合并