excel函数图文解说教程.docx
《excel函数图文解说教程.docx》由会员分享，可在线阅读，更多相关《excel函数图文解说教程.docx（44页珍藏版）》请在冰豆网上搜索。
excel函数图文解说教程
1、PERCENTILE函数实例：
求百分比数值点
Excel中PERCENTILE函数实例：
求百分比数值点
Excel中PERCENTILE函数的用法是：
返回区域中数值的第K个百分点的值。
PERCENTILE函数实例：
求百分比数值点
现在要统计出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函数实例：
统计一组数据出现的次数
frequency函数实例：
统计一组数据出现的次数
下表中统计了公司员工被投诉的记录。
问题是统计出指定的员工编号被投诉的出现次数。
我们使用excel中frequency函数可以实现出现次数统计。
frequency函数用于计算数值在某个区域内的出现频率次数，然后返回一个垂直数组。
操作步骤如下：
首先在C列建立需要参与统计投诉出现次数的员工编号，然后选中D5：
D8单元格区域，在编辑栏输入公式：
=FREQUENCY（B2:
B11,C5:
C8），然后按下“Ctrl+Shift+Enter”组合键，即可一次性统计出各个编号在B2：
B11单元格区域中出现的次数。
3、frequency函数用法介绍
Excel中frequency函数用法介绍
Excel中frequency函数的用法是：
为一组给定的值和一组给定的纸盒（或间隔），频率分布统计的每个时间间隔中发生的值的数量。
FREQUENCY函数的语法是：
FREQUENCY（data_array,bins_array）
FREQUENCY函数的参数说明：
返回在data_array参数中指定了在bins_array参数中指定的时间间隔内的元素数。
FREQUENCY函数所返回的频率数组包含一个值，多个您区间数组中的值的数目。
例如对于如果区间数组中有三个数字，FREQUENCY函数将返回四个值。
4、MODE函数实例：
统计出现次数最多的值
Excel中MODE函数实例：
统计出现次数最多的值
下表中统计了某公司客服人员被投诉的记录，问题是统计出被投诉次数最多的客服编号。
MODE函数就是统计数组中出现频率最高的值。
MODE函数实例：
统计出现次数最多的值
操作步骤如下：
选中C2单元格，输入公式：
=MODE（B2:
B11），确定后，即可统计出被投诉次数最多的客服编号。
5、MODE函数的用法介绍
Excel中MODE函数的用法介绍
Excel中MODE函数的用法是：
传回在一阵列或范围的资料中出现频率最高的值。
MODE函数的语法是：
MODE（number1,[number2],...]）
MODE函数语法具有下列参数：
第一：
Number1：
是要计算众数的第一个数字引数。
第二，Number2：
可选参数，是第2个到第255个您要计算众数的数字引数。
也可以使用单一阵列或阵列参照，来取代以逗点分隔的引数。
参数说明：
引数可以是数值或包含数值的名称、阵列或参照。
引数可以是数值或包含数值的名称、阵列或参照。
如果阵列或参照引数包含文字、逻辑值或空白储存格，则忽略这些数值；但包含零值储存格。
如果阵列或参照引数包含文字、逻辑值或空白储存格，则忽略这些数值；但包含零值储存格。
若引数为错误值或无法转换成数字的文字，则会产生错误。
若引数为错误值或无法转换成数字的文字，则会产生错误。
如果组中不包含重复的资料点，MODE函数将传回#N/A的错误值。
6、rank函数排名实例：
只显示满足条件的排名
Excel中rank函数排名实例：
只显示满足条件的排名
在下面表中，显示了不同部门的业绩考评结果，现在我们利用rank函数来显示只满足条件的排名情况，即只对1部门的业绩考评结果进行排名。
rank函数排名实例“只显示满足条件的排名”的操作步骤如下：
单击D2单元格，输入公式：
=IF（$A$2:
$A$11=$A$2,RANK（C2,$C$2:
$C$11）,""），确定完成输入。
此公式的含义是：
首先判断A2单元格的值是否满足条件，如果满足条件就返回其中的排名，如果不满足条件即为空。
再次选择D2单元格，并向下复制公式，即可依次判断A列中的类别，然后按条件返回排名。
7、Excel中不同列的数据统一排名
Excel中不同列的数据统一排名
在以前的文章教程中我们相继讲了excel中利用rank函数或几个函数套用来实现不同情况的排名。
在本文我们再讲一个关于在Excel中不同列的数据实现统一排名的实例。
在下表中分别有两个部门的业绩考评，1、2部门考评结果分别显示在C列和H列中，下面将C列和H列中的考评业绩进行统一排名。
下面是Excel中不同列的数据统一排名的操作步骤：
第一步，选中D2单元格，输入公式：
=RANK（C2,（$C$2:
$C$11,$H$2:
$H$11））+COUNTIF（$C$2:
C2,C2）-1，确定后，即可得到张菲的排名情况。
第二步，选中D2单元格，向下复制公式，完成C列业绩的排名。
第三步，选中I2单元格，输入公式：
=RANK（H2,（$C$2:
$C$11,$H$2:
$H$11））+COUNTIF（$C$2:
C2,C2）-1，确定后，即可得到张蓝的排名情况。
第四步，选中I2单元格，向下复制公式，完成H列业绩的排名。
8、Excel中rank函数同分同名次的解决办法
Excel中rank函数同分同名次的解决办法
使用excel中rank函数进行排位时，当出现相同名次时，则会少一个名次。
比如出现两个第4名，则会自动省去名次5，我们可以按如下方法设置公式来彻底解决当出现同分同名次时缺省名次数的问题。
本文我们通过excel的COUNTIF、IF、SUM函数套用来解决出现同分同名次时排位相同，并且序号依然能够依次排列。
实例详见下图：
从表中我们可以看到戴亚和肖亚军的成绩都是9.25，相同的分数，两个都排在第4名，然后谢芳就是6名，名次中就没有了第5名。
在实际中这样的排名不符合常规，所以我们重新设计一下公式，操作方法如下。
选中D2单元格，在编辑栏输入公式：
=SUM（IF（$B$2:
$B$11<=B2,"",1/（COUNTIF（$B$2:
$B$11,$B$2:
$B$11））））+1，然后同时按下“Ctrl+Shift+Enter”组合键，即可得到张菲的排名，然后向下复制公式，可以看到结果出现两个第4名，名次都显示为4，而且紧接着谢芳排名第5，而不是像rank函数排出来的名次为6。
通过以上的公式设计，就可以彻底解决Excel中rank函数同分同名次的弊端，大家在输入完公式后，一定记住按下必须按此ctrl+shift+enter组合键，数组公式才能得到正确结果，否则会出错的。
9、Rank函数实例：
对不连续单元格排位（名次）
Rank函数实例：
对不连续单元格排位（名次）
本文利用excel的rank函数来对不连续单元格进行排位（名次）。
实例详见下图，本表中统计了各个月份以及每季度的销售额，现在需要分别对4个季度进行销售额排名。
以下是使用excel的rank函数来对不连续单元格（4个季度），进行排位（名次）的操作步骤：
第一步，单击E7单元格，在编辑栏输入公式：
=RANK（B5,（$B$5,$B$9,$B$13,$B$17）），确定，即可求出B5单元格的值（即1季度合计值）在B5,B9,B13,B17这几个单元格数值中的排位。
第二步，单击E8单元格，在编辑栏输入公式：
=RANK（B9,（$B$5,$B$9,$B$13,$B$17）），可得到2季度的排名。
第三步，单击E9单元格，在编辑栏输入公式：
=RANK（B13,（$B$5,$B$9,$B$13,$B$17）），可得到第3季度的排名。
第四步，单击E10单元格，在编辑栏输入公式：
=RANK（B17,（$B$5,$B$9,$B$13,$B$17）），就得到了第4季度的排名。
Excel中rank函数排位（名次）的关联文章请参考办公软件栏目其余文章教程。
10、TRIMMEAN函数实例：
评委打分计算最后得分
TRIMMEAN函数实例：
评委打分计算最后得分
在前一篇文章我们详细介绍了excel中TRIMMEAN函数的用法。
本文我们再给出一个实例来巩固TRIMMEAN函数的使用方法。
TRIMMEAN函数实例：
评委打分计算最后得分
实例说明:
在跳高技能竞技赛中，10位评委分别为前三名的选手打分，计算出选手的最后得分。
详见下图。
第一步，选中B13单元格，在编辑栏输入公式：
=TRIMMEAN（B2:
B11,0.2），确定后，即可得到评委为戴品选手的打分，最后得分为9.20。
第二步，选中B13单元格，向右复制公式，就可以得到评委为其余两位选手的打分，最后得分分别为：
8.85和9.05。
11、Excel中TRIMMEAN函数的用法介绍
Excel中TRIMMEAN函数的用法介绍
Excel中TRIMMEAN用法是：
返回数据集的内部平均值。
TRIMMEAN函数先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。
当希望在分析中剔除一部分数据的计算时，可以使用TRIMMEAN函数。
TRIMMEAN函数的语法是：
TRIMMEAN（array,percent）
TRIMMEAN函数具有下列参数：
第一，Array：
需要进行整理并求平均值的数组或数值区域。
第二，Percent：
计算时所要除去的数据点的比例，例如，如果percent=0.2，在20个数据点的集合中，就要除去4个数据点（20×0.2）：
即头部除去2个，尾部除去2个。
TRIMMEAN函数相关说明：
第一，如果percent<0或percent>1，TRIMMEAN函数返回错误值#NUM!
。
第二，TRIMMEAN函数将除去的数据点数目向下舍入为最接近的2的倍数。
第三，如果percent=0.1，30个数据点的10%等于3个数据点。
函数TRIMMEAN将对称地在数据集的头部和尾部各除去一个数据。
12、ROW函数实例：
自动控制要显示的行数
ROW函数实例：
自动控制要显示的行数
本文我们利用excel中的IF函数和ROW函数来自动控制excel工作表要显示的行数。
在建立工作表时，有时需要通过公式控制单元格的显示行数。
比如，本文的工作表中显示了贷款金额、贷款年限等数据，现在在根据贷款年限计算各期偿还金额，因此需要在工作表中建立“年份”列，进而进行计算。
当贷款年限发生变化时，“年份”列的单元格显示行数也相应改变。
ROW函数实例：
自动控制要显示的行数。
实例详见下图所示：
当前工作表的B2单元格中显示了借款年限。
选中A5单元格，在编辑栏中输入公式：
=IF（ROW（）-ROW（$A$4）<=$B$2,ROW（）-ROW（$A$4）,""），按键确定，然后再单击A5单元格，向下复制公式（可以根据可能长的借款年限向下多复制一些单元格），但是实际显示年份值与B2单元格中指定期数相等。
如果更改B2单元格的借款年限，比如我们改为20，“年份”下面会自动显示出相应的年份值。
13、excel中ABS函数在数据比较中的应用
excel中ABS函数在数据比较中的应用
下面表格中统计了两个卖场的销售金额。
本文利用excel中ABS函数来比较两个卖场的销售金额，并相应的加上“多”或“少”字样。
如下图：
第一，选中F2单元格，在编辑栏输入公式：
=IF（E2>C2,"多","少"）&ABS（E2-C2），确定，即可比较出两个卖场的女式连衣裙的销售情况。
第二，选中F2单元格，并向下复制公式，就可以快速比较出两个卖场的产品销售情况。
14、Excel中SMALL函数的用法及实例
Excel中SMALL函数的用法及实例
Excel中SMALL函数的用法是：
返回数据集中第k个最小值。
使用此函数可以返回数据集中特定位置上的数值。
SMALL函数的语法是：
SMALL（array,k）
SMALL函数的参数说明：
第一，Array：
为需要找到第k个最小值的数组或数字型数据区域。
第二，K：
为返回的数据在数组或数据区域里的位置（从小到大）。
SMALL函数使用需注意：
如果array为空，函数SMALL返回错误值#NUM!
。
如果k≤0或k超过了数据点个数，函数SMALL返回错误值#NUM!
。
如果n为数组中的数据点个数，则SMALL（array,1）等于最小值，SMALL（array,n）等于最大值。
Excel中SMALL函数和LARGE函数是一对相反的函数，都是属于excel的排名函数，SMALL函数是返回排名从小到大的值，LARGE函数是返回排名从大到小的值。
由于使用方法都一样，所以就不再举例，SMALL函数的实例请参考文章：
《Excel中large函数实例：
返回排名前三名数据》，具体使用时，只需将LARGE改为SMALL即可。
15、Excel中large函数实例：
返回排名前三名数据
Excel中large函数实例：
返回排名前三名数据
在前一篇文章详细介绍了Excel中LARGE函数的用法。
本文，给出一个成绩表，利用LARGE函数，统计出总成绩分数在前三名的分数分别为多少。
详见下图：
large函数实例：
进行排名，操作步骤如下：
第一，选中I2单元格，在编辑栏输入公式：
=LARGE（$F$2:
$F$7,H2），确定后，即可返回总分成绩排名第一的分数。
第二，选中I2单元格，向下复制公式，就可以开始返回排名第二和第三的分数。
16、Excel中large函数的使用方法介绍
Excel中large函数的使用方法介绍
Excel中LARGE函数的用法是：
传回资料组中第k个最大的数值。
可以用这个函数来指定选取排在第几位的值。
例如，可以使用LARGE函数传回最高、第二高或第三高的分数。
LARGE函数的语法是：
LARGE（array,k）
LARGE函数语法具有下列参数：
第一，Array：
是要找出第k个最大值之数值资料的阵列或范围。
第二，K：
是要传回之资料阵列或储存格范围中的位置（由最大值算起）。
LARGE函数说明：
如果array是空值，则LARGE函数传回错误值#NUM!
。
如果k≦0或k大于资料点的个数，则LARGE函数传回错误值#NUM!
。
如果n是范围中资料点的个数，则LARGE（array,1）传回最大值，而LARGE（array,n）传回最小值。
17、PMT函数实例：
年、季度、月偿还金额计算
PMT函数实例：
年、季度、月偿还金额计算
本文我们以一个PMT函数实例来巩固其知识点。
本例详见下图，我们已知贷款的年利率，贷款年限，贷款总金额，现在分别计算年偿还金额、季度偿还金额、月偿还金额。
第一，单击B4单元格，输入公式：
=PMT（A2,B2,C2），确定，即可计算年偿还金额。
第二，单击B5单元格，输入公式：
=PMT（A2/4,B2*4,C2），确定，即可计算季度偿还金额。
第三，单击B6单元格，输入公式：
=PMT（A2/12,B2*12,C2），确定，即可计算月偿还金额。
18、
19、Excel中PMT函数的用法介绍
Excel中PMT函数的用法介绍
Excel中PMT函数的用法是：
基于固定利率及等额分期付款方式，返回贷款的每期付款额。
PMT函数的语法是：
PMT（rate,nper,pv,[fv],[type]）
PMT函数语法具有下列参数：
第一，Rate：
贷款利率。
第二：
Nper：
该项贷款的付款总数。
第三，Pv：
现值，或一系列未来付款的当前值的累积和，也称为本金。
第四，Fv：
可选参数，未来值或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为0（零），也就是一笔贷款的未来值为0。
第五，Type：
可选参数。
数字0（零）或1，用以指示各期的付款时间是在期初还是期末。
PMT函数相关说明
PMT函数返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。
另外，应确认所指定的rate和nper单位的一致性。
例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。
如果要计算贷款期间的支付总额，用PMT返回值乘以nper。
20、Excel中REPT函数的用法介绍
Excel中REPT函数的用法介绍
Excel中REPT函数的用法是：
按照给定的次数重复显示文本。
可以通过函数REPT来不断地重复显示某一文本字符串，对单元格进行填充。
REPT函数的语法：
REPT（text,number_times）
REPT函数语法具有下列参数：
第一，Text参数：
需要重复显示的文本。
第二，Number_times参数：
用于指定文本重复次数的正数。
说明：
如果number_times为0，则REPT返回""（空文本）。
如果number_times不是整数，则将被截尾取整。
REPT函数的结果不能大于32,767个字符，否则，REPT将返回错误值#VALUE!
。
21、Excel中REPT函数实例：
进行等级评定
Excel中REPT函数实例：
进行等级评定
Excel中REPT函数的用法是按照给定的次数重复显示文本。
我们本例使用REPT函数为成绩结果标明等级。
详见下图所示：
第一，选中G2单元格，在编辑栏输入公式：
=IF（F2<200,REPT（"★",1）,IF（F2<300,REPT（"★",2）,REPT（"★",3））），按下回车键，即可根据F2单元格中的分数，自动返回指定数目的“★”号。
第二，再向下复制公式，即可根据F列中的销售额自动返回指定数目的“★”号。
22、多人同时编辑excel文档的方法
多人同时编辑excel文档的方法
如果一个excel工作表需要录入的数据量很大，文件又很急用的话，大家可以尝试excel为我们提供的一个强大工具：
“允许多用户同时编辑，同时允许工作簿合并”。
下面就是多人同时编辑excel文档的方法介绍
第一步:
打开运行Excel，新建一个工作簿文档，取名保存。
第二步:
执行“工具→共享工作簿”，打开“共享工作簿”对话框，选中“允许多用户同时编辑，同时允许工作簿合并”选项，确定返回。
第三步:
将上述工作簿文档保存在局域网上某台电脑的一个共享文件夹中。
第四步:
局域网内用户同时打开上述工作簿，大家约定好输入的单元格位置（如A用户在A列输入内容，B用户在C列输入内容……）。
第五步:
选定约定好的相应的单元格，在其中输入内容。
第六步:
单击工具栏上的“保存”按钮，将上述输入内容保存一下，对方只要再按一下“保存”按钮，即可看到上述输入的内容。
通过以上操作步骤，我们就可以多人同时录入一个Excel文件，提高工作效率。
这个应用主要利用了Excel的“允许多用户同时编辑，同时允许工作簿合并”功能，在实际工作中，可以用做多人共同录入一个Excel表格，Excel会自动保持信息不断更新。
比如:
有A、B、C、D四个用户分工合作共同完成2005.xls文件的录入，首先打开这个文件，并按上面的操作勾选“允许多用户同时编辑，同时允许工作簿合并”，最后点击“文件→另存为”，将其保存在E电脑的D盘中。
接下来四个用户就可以同时在这个文件中录入了。
有些歪脑筋的朋友，也使用此方法在Excel共享工作表里实现另类聊天，哈哈！
小编还是提醒大家工作第一，娱乐其次，呵呵！
23、Excel实例：
电话号码区号和号码分离
Excel实例：
电话号码区号和号码分离
小编所在的部门，拿到一份客户的电话号码资料，客户来自各个区域，所以电话号码区位和后面的号码位数都不尽相同，区号有三位的、有四位的，号码有七位的、有八位的。
如何快速的分离出区号与号码呢？
实例：
分离混合显示的7位和8位电话号码的区号与号码，详见下图。
我们可以利用excel中的RIGHT、LEN、FIND、MID函数来配套使用，从而快速的将区号与电话号码分离出来。
第一步，选中B2单元格，输入公式：
=MID（A2,1,FIND（"-",A2）-1），确定，向下复制公式，即可快速提取到所有的区号。
第二步，选中C2单元格，输入公式：
=RIGHT（A2,LEN（A2）-FIND（"-",A2）），确定，向下复制公式，即可快速提取到所有的电话号码。
24、REPLACE函数实例：
将手机号码后4位替换为特定符号
REPLACE函数实例：
将手机号码后4位替换为特定符号
单位在举行年终抽奖活动时会屏蔽手机中将号码的后4位，我们可以在excel中利用REPLACE函数来实现这种效果。
实例：
REPLACE函数实例：
将手机号码后4位替换为特定符号（星号）
操作步骤如下：
第一，选中C2单元格，在编辑栏输入公式：
=REPLACE（B2,8,4,"****"），按下回车键确定，就可以得到第一个屏蔽后的手机号码。
第二，再次单击C2单元格，向下复制公式，可以快速得到多个屏蔽后的手机号码。
关联阅读：
关于REPLACE函数的使用方法，请参考文章《excel一次性删除一列中最后一个字》中的介绍。
25、Excel自动排名次和显示排名第一的姓名
Excel自动排名次和显示排名第一的姓名
在下面的excel表中，已有姓名和成绩两列数据，求名次，以及排名第一最高分的姓名。
第一步，单击C2单元格，输入公式：
=RANK（B2,B:
B），然后将公式向下复制填充即可完成学生成绩自动排名。
第二步，求最高分的姓名。
首先增加一辅助列，把A列的所有姓名复制到E列。
然后在D2单元格输入公式：
=VLOOKUP（LARGE（B:
B,1）,B:
E,4,FALSE），按下回车键确定，即可求出排名第一最高分对
升级会员 