excel函数模板.docx
- 文档编号:16977358
- 上传时间:2023-07-20
- 格式:DOCX
- 页数:10
- 大小:21.17KB
excel函数模板.docx
《excel函数模板.docx》由会员分享,可在线阅读,更多相关《excel函数模板.docx(10页珍藏版)》请在冰点文库上搜索。
excel函数模板
竭诚为您提供优质文档/双击可除
excel函数模板
篇一:
excel函数公式图文说明教程
excel函数公式实例教程
excel教程珍藏版,简单明了,包你学会,欢迎转载!
教程在陆续上传添加中,敬请期待!
1、
excel中peRcentile函数实例:
求百分
比数值点
[日期:
20xx-08-09]来源:
it部落窝作者:
it部落窝阅读:
8560次[字体:
大中小]
excel中peRcentile函数的用法是:
返回区域中数值的第k个百分点的值。
peRcentile函数实例:
求百分比数值点
下面是it部落窝某几天的一个流量表,详见下图。
现在要统计出90%、80%、70%、60%、50%对应的百分比数值点。
我们使用peRcentile函数设计公式来求取。
操作步骤如下:
第一步,选中b17单元格,输入公式:
=peRcentile(c2:
c14,0.9),确定,得到90%处的对应的百分比数值点。
第二步,选中b18单元格,输入公式:
=peRcentile(c2:
c14,0.8),确定,得到80%处的对应的百分比数值点。
第三步,选中b19单元格,输入公式:
=peRcentile(c2:
c14,0.7),确定,得到70%处的对应的百分比数值点。
第四步,选中b20单元格,输入公式:
=peRcentile(c2:
c14,0.6),确定,得到60%处的对应的百分比数值点。
第五步,选中b21单元格,输入公式:
=peRcentile(c2:
c14,0.5),确定,得到50%处的对应的百分比数值点。
2、
frequency函数实例:
统计一组数据出
现的次数
[日期:
20xx-08-06]来源:
it部落窝作者:
it部落窝阅读:
5390次[字体:
大中小]
下表中统计了公司员工被投诉的记录。
问题是统计出指定的员工编号被投诉的出现次数。
我们使用excel中frequency函数可以实现出现次数统计。
frequency函数用于计算数值在某个区域内的出现频率次数,然后返回一个垂直数组。
操作步骤如下:
首先在c列建立需要参与统计投诉出现次数的员工编号,然后选中d5:
d8单元格区域,在编辑栏输入公式:
=FRequency(b2:
b11,c5:
c8),然后按下“ctrl+shift+enter”组合键,即可一次性统计出各个编号在b2:
b11单元格区域中出现的次数。
3、
excel中frequency函数用法介绍
[日期:
20xx-08-06]来源:
it部落窝作者:
it部落窝阅读:
6622次[字体:
大中小]
excel中frequency函数的用法是:
为一组给定的值和一组给定的纸盒(或间隔),频率分布统计的每个时间间隔中发生的值的数量。
FRequency函数的语法是:
FRequency(data_array,bins_array)
FRequency函数的参数说明:
返回在data_array参数中指定了在bins_array参数中指定的时间间隔内的元素数。
FRequency函数所返回的频率数组包含一个值,多个您区间数组中的值的数目。
例如对于如果区间数组中有三个数字,FRequency函数将返回四个值。
4、
excel中mode函数实例:
统计出现次数
最多的值
[日期:
20xx-08-06]来源:
it部落窝作者:
it部落窝阅读:
3727次[字体:
大中小]
下表中统计了某公司客服人员被投诉的记录,问题是统计出被投诉次数最多的客服编号。
mode函数就是统计数组中出现频率最高的值。
mode函数实例:
统计出现次数最多的值
操作步骤如下:
选中c2单元格,输入公式:
=mode(b2:
b11),确定后,即可统计出被投诉次数最多的客服编号。
5、
excel中mode函数的用法介绍
[日期:
20xx-08-06]来源:
it部落窝作者:
it部落窝阅读:
3519次[字体:
大中小]
excel中mode函数的用法是:
传回在一阵列或范围的资料中出现频率最高的值。
mode函数的语法是:
mode(number1,[number2],...])
mode函数语法具有下列参数:
第一:
number1:
是要计算众数的第一个数字引数。
第二,number2:
可选参数,是第2个到第255个您要计算众数的数字引数。
也可以使用单一阵列或阵列参照,来取代以逗点分隔的引数。
参数说明:
引数可以是数值或包含数值的名称、阵列或参照。
引数可以是数值或包含数值的名称、阵列或参照。
如果阵列或参照引数包含文字、逻辑值或空白储存格,则忽略这些数值;但包含零值储存格。
如果阵列或参照引数包含文字、逻辑值或空白储存格,则忽略这些数值;但包含零值储存格。
若引数为错误值或无法转换成数字的文字,则会产生错误。
若引数为错误值或无法转换成数字的文字,则会产生错误。
如果组中不包含重复的资料点,mode函数将传回#n/a的错误值。
6、
excel中rank函数排名实例:
只显示满
足条件的排名
篇二:
excel表格公式大全(苦心整理)
excel表格公式大全
来源:
鲍利的日志
1、查找重复内容公式:
=iF(countiF(a:
a,a2)>1,"重复","")。
2、用出生年月来计算年龄公式:
=tRunc((days360(h6,"20xx/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)——对k2k56这一区域求平均数;
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," 说明:
countiF函数也可计算某一区域男、女生人数。
如:
=countiF(c2:
c351,"男")——求c2到c351区域(共350人)男性人数;
9、优秀率:
=sum(k57:
k60)/55*100
10、及格率:
=sum(k57:
k62)/55*100
11、标准差:
=stdeV(k2:
k56)——求k2到k56区域(55人)的成绩波动情况(数值越小,说明该班学生间的成绩差异较小,反之,说明该班存在两极分化);
12、条件求和:
=sumiF(b2:
b56,"男",k2:
k56)——假设b列存放学生的性别,k列存放学生的分数,则此函数返回的结果表示求该班男生的成绩之和;
13、多条件求和:
{=sum(iF(c3:
c322="男",iF(g3:
g322=1,1,0)))}——假设c列(c3:
c322区域)存放学生的性别,g列(g3:
g322区域)存放学生所在班级代码(1、2、3、4、5),则此函数返回的结果表示求一班的男生人数;这是一个数组函数,输完后要按ctrl+shift+enter组合键(产生“{}”)。
“{}”不能手工输入,只能用组合键产生。
14、根据出生日期自动计算周岁:
=tRunc((days360(d3,now()))/360,0)
———假设d列存放学生的出生日期,e列输入该函数后则产生该生的周岁。
15、在word中三个小窍门:
①连续输入三个“~”可得一条波浪线。
②连续输入三个“-”可得一条直线。
连续输入三个“=”可得一条双直线。
一、excel中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色比如:
a1〉1时,c1显示红色
0 a1 方法如下:
1、单元击c1单元格,点“格式”>“条件格式”,条件1设为:
公式=a1=1
2、点“格式”->“字体”->“颜色”,点击红色后点“确定”。
条件2设为:
公式=and(a1>0,a1 3、点“格式”->“字体”->“颜色”,点击绿色后点“确定”。
条件3设为:
公式=a1 点“格式”->“字体”->“颜色”,点击黄色后点“确定”。
4、三个条件设定好后,点“确定”即出。
二、excel中如何控制每列数据的长度并避免重复录入
1、用数据有效性定义数据长度。
用鼠标选定你要输入的数据范围,点"数据"->"有效性"->"设置","有效性条件"设成"允许""文本长度""等于""5"(具体条件可根据你的需要改变)。
还可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点"确定"。
2、用条件格式避免重复。
选定a列,点"格式"->"条件格式",将条件设成“公式=countiF($a:
$a,$a1)>1”,点"格式"->"字体"->"颜色",选定红色后点两次"确定"。
这样设定好后你输入数据如果长度不对会有提示,如果数据重复字体将会变成红色。
三、在excel中如何把b列与a列不同之处标识出来?
(一)、如果是要求a、b两列的同一行数据相比较:
假定第一行为表头,单击a2单元格,点“格式”->“条件格式”,将条件设为:
“单元格数值”“不等于”=b2
点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。
用格式刷将a2单元格的条件格式向下复制。
b列可参照此方法设置。
(二)、如果是a列与b列整体比较(即相同数据不在同一行):
假定第一行为表头,单击a2单元格,点“格式”->“条件格式”,将条件设为:
“公式”=countiF($b:
$b,$a2)=0
点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。
用格式刷将a2单元格的条件格式向下复制。
b列
可参照此方法设置。
按以上方法设置后,ab列均有的数据不着色,a列有b列无或者b列有a列无的数据标记为红色字体。
四、excel中怎样批量地处理按行排序
假定有大量的数据(数值),需要将每一行按从大到小排序,如何操作?
由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。
所以,这一问题不能用排序来解决。
解决方法如下:
1、假定你的数据在a至e列,请在F1单元格输入公式:
=laRge($a1:
$e1,column(a1))
用填充柄将公式向右向下复制到相应范围。
你原有数据将按行从大到小排序出现在F至j列。
如有需要可用“选择性粘贴/数值”复制到其他地方。
注:
第1步的公式可根据你的实际情况(数据范围)作相应的修改。
如果要从小到大排序,公式改为:
=small($a1:
$e1,column(a1))
五、巧用函数组合进行多条件的计数统计
例:
第一行为表头,a列是“姓名”,b列是“班级”,c列是“语文成绩”,d列是“录取结果”,现在要统计“班级”为“二”,“语文成绩”大于等于104,“录取结果”为“重本”的人数。
统计结果存放在本工作表
的其他列。
公式如下:
=sum(iF((b2:
b9999="二")*(c2:
c9999>=104)*(d2:
d9999="重本"),1,0))
输入完公式后按ctrl+shift+enter键,让它自动加上数组公式符号"{}"。
六、如何判断单元格里是否包含指定文本?
假定对a1单元格进行判断有无"指定文本",以下任一公式均可:
=iF(countiF(a1,"*""!
a1",index(x,Row()))
将公式向下填充,直到出错为止,目录就生成了。
篇三:
excel常用函数大全
第一章成绩查询常用函数
1.求和函数sum
语法:
sum(number1,number2,...)。
参数:
number1、number2...为1到30个数值(包括逻辑值和文本表达式)、区域或引用,各参数之间必须用逗号加以分隔。
注:
number1可以是b1:
h3,这样的形式也可以是整个表格,并且number2也可以和number1一样,他们这间需要用逗号隔开,number2还可以是另一个表格的数据。
注意:
参数中的数字、逻辑值及数字的文本表达式可以参与计算,其中逻辑值被转换为1,文本则被转换为数字。
如果参数为数组或引用,只有其中的数字参与计算,数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。
应用实例一:
跨表求和(高)
使用sum函数在同一工作表中求和比较简单,如果需要对不同工作表的多个区域进行求和,可以采用以下方法:
选中excelxp“插入函数”对话框中的函数,“确定”后打开“函数参数”对话框。
切换至第一个工作表,鼠标单击“number1”框后选中需要求和的区域。
如果同一工作表中的其他区域需要参与计算,可以单击“number2”框,再次选中工作表中要计算的其他区域。
上述操作完成后切换至第二个工作表,重复上述操作即可完成输入。
“确定”后公式所在单元格将显示计算结果。
应用实例二:
sum函数中的加减混合运算
财务统计需要进行加减混合运算,例如扣除现金流量表中的若干支出项目。
按照规定,工作表中的这些项目没有输入负号。
这时可以构造“=sum(b2:
b6,c2:
c9,-d2,-e2)”这样的公式。
其中b2:
b6,c2:
c9引用是收入,而d2、e2为支出。
由于excel不允许在单元格引用前面加负号,所以应在表示支出的单元格前加负号,这样即可计算出正确结果。
即使支出数据所在的单元格连续,也必须用逗号将它们逐个隔开,写成“=sum(b2:
b6,c2:
c9,-d2,-d3,d4)”这样的形式。
应用实例三:
及格人数统计
假如b1:
b50区域存放学生性别,c1:
c50单元格存放某班学生的考试成绩,要想统计考试成绩及格的女生人数。
可以使用公式“=sum(iF(b1:
b50=〃女〃,iF(c1:
c50>=60,1,0)))”,由于它是一个数组公式,输入结束后必须按住ctrl+shift键回车。
公式两边会自动添加上大括号,在编辑栏显示为“{=sum(iF(b1:
b50=〃女〃,iF(c1:
c50>=60,1,0)))}”,这是使用数组公式必不可少的步骤。
2.条件求和函数sumiF
语法:
sumiF(range,criteria,sum_range)。
参数:
range是用于条件判断的单元格区域,criteria是由数字、逻辑表达式等组成的判定条件,sum_range为需要求和的单元格、区域或引用。
应用实例:
及格平均分统计
假如a1:
a36单元格存放某班学生的考试成绩,若要计算及格学生的平均分,可以使用公式“=sumiF(a1:
a36,〃>=60〃,a1:
a36)
/countiF(a1:
a36,〃>=60〃)。
公式中的“=sumiF(a1:
a36,〃>=60〃,a1:
a36)”计算及格学生的总分,式中的“a1:
a36”为提供逻辑判断依据的单元格引用,“>=60”为判断条件,不符合条件的数据不参与求和,a1:
a36则是逻辑判断和求和的对象。
公式中的countiF(a1:
a36,〃>=60〃)用来统计及格学生的人数。
3.逻辑函数iF
语法:
iF(①logical_test,②value_if_true,③value_if_false)。
参数:
logical_test是结果为true(真)或false(假)的数值或表达式;value_if_true是logical_test为true时函数的返回值,如果logical_test为ture并且省略了value_if_true,则返回true。
而且value_if_true可以是一个表达式;value_if_false是logical_test为false时函数的返回值。
如果logical_test为false并且省略value_if_false,则返回false。
Value_if_false也可以是一个表达式。
注:
红字的内容为,当①为真时则将2的值返回,如果为假则将3的值返回。
2,3可以是表达式或值,还可以是if语句,这就是if嵌套。
应用实例一:
个人收入调节税计算
假设个人收入调节税的稽征办法是:
工资在1000元以下的免征调节税,工资1000元以上至1500元的超过部分按5%的税率征收,1500元以上至2000元的超过部分按8%的税率征收,高于2000元的超过部分按30%的税率征收。
假如工作表的b列存放职工姓名,c列存放工资,选中d列的空白单元格输入公式“=iF(c2 *0.05,iF(c2-1500)2000,(c2-2000)*0.3))))”,回车后即可计算出c2工资应缴纳的收入调节税。
公式中的iF语句是逐次计算的,如果第一个逻辑判断c2 用sum函数计算工资总额等问题时,若引用的全部参数均为空白单元格,公式仍然会计算出一个“0”。
这样打印出来的报表不仅很不美观。
为此可将计算公式设计为“=iF(sum(a1:
b1,d2:
e8)0,sum(a1:
b1,d2:
e8),〃〃)”,即是当sum(a1:
b1,d2:
e8)计算结果不等于零时,公式所在单元格显示sum(a1:
b1,d2:
e8)的计算结果,否则显示一个空格。
上面这个问题在财务计算中依然存在,如公式“=a1-a6-a9”有可能为零或显示负数。
为了避免打印零或负数,可以设计公式“=iF(a2-a6-a9=0,〃〃,iF(a2-a6-a9 假设c1:
c460区域内存放着职工的职称,d1:
d460区域存放着职工的性别。
如果要统计具有高级职称的男性职工总数,可以使用公式
“=sum(iF(c1:
c460=〃高级〃,iF(d1:
d460=〃男〃,1,0)))”。
这是一个数组公式,输入结束后按住ctrl+shift回车即可计算出结果。
4.平均值函数aVeRage
语法:
aVeRage(number1,number2,...)。
参数:
number1、number2...是需要计算平均值的1~30个参数。
注意:
参数可以是数字、包含数字的名称、数组或引用。
数组或单元格引用中的文字、逻辑值或空白单元格将被忽略,但单元格中的零则参与计算。
如果需要将参数中的零排除在外,则要使用特殊设计的公式,下面的介绍。
应用实例一:
跨表计算平均值
标签名为“一班”、“二班”和“三班”的工作表存放各班学生的成绩,则它们的总平均分计算公式为“=aVeRage(一班!
c1:
c36,三班!
c1:
c32,三班!
c1:
c45)”。
式中的引用输入方法与sum跨表求和时相同。
跨表求值:
最前面写标签名后面接!
再后面接所要求值的区域。
应用实例二:
忽略零的平均值计算公式
假设a1:
a200随机存放包括零在内的48个数值,在aVeRage参数中去掉零引用很麻烦,这种情况可以使用公式“=aVeRage(iF(a1:
a2000,a1:
a200,〃〃)”。
公式输入结束后按住ctrl+shift回车,即可对a1:
a200中的数值(不包括零)计算平均值。
5.快捷方式或链接创建函数hypeRlink
语法:
hypeRlink(link_location,friendly_name)。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 函数 模板