excel中函数的应用.docx
《excel中函数的应用.docx》由会员分享，可在线阅读，更多相关《excel中函数的应用.docx（14页珍藏版）》请在冰豆网上搜索。
excel中函数的应用
excel数据引用公式的使用方法
现实中excel的功能已经能够帮助我们解决很多问题。
今天一个同事问我了一个问题，说能否建立一个excel模板文件自动检测其他的excel文件，检测其中的数据，如果受检测的excel文件做任何数据的改动，这个模板文件会自动更新数据。
想完成这一目的，使用excel的引用即可。
excel数据引用分为四种，我们给大家简单介绍下：
1、excel数据相对引用。
这种最为简单，通常大家都知道，就是普通的公式计算。
简单介绍几个公式，相信大家都很清楚。
1）、在d2单元格中需要计算：
数据一和数据二的平均数与数据三的差。
就可以输入
=（A2+B2）/2-C2
或者输入
=sum（A2:
B2）/2-C2
其中，括号必须是英文状态或者是半角中文状态，其中的列号ABC输入大小写没有关系，都会自动变成大写。
如果求和的数据过多则不要用加号了，用sum求和函数较为方便。
（求括号内英文冒号两侧的单元格之间的数据之和）sum函数不但可以求横向的行之和、竖向的列之和，还可以求对角线所辖区域的矩形区域之和。
2）、计算：
数据一、数据二、数据三的最大值与最小值的差，可以输入：
=max（a2:
d2）-min（a2:
d2）
其中，max是求最大值函数，min是求最小值函数。
和sum一样，这两个函数都支持对角线所辖区域矩形的数据运算。
3）、计算：
如果数据一大于1500，则综合数据是数据一、数据二、数据三求和，若数据一介于1000至1500之间则计算数据一和数据二之和，若数据一低于1000，则只记录数据一的结果。
（这个例子在公司计算工资的时候很常用，但是要非常小心边界的判断，需要的时候可以用>=表示大于等于，用<=来表示小于等于）关于这个if判断语句的写法要非常小心括号的运用，我们可以在有图早期的一篇个人所得数计算公式中看到多次嵌套的运用。
=IF（A2>1500,SUM（A2:
C2）,IF（（A2<1000）,A2,SUM（A2:
B2）））
这三个例子主要是介绍大家常见的三种模式：
直接运算，用excel的函数进行运算，用程序语句进行运算。
（这种提法是我自己随便想的，不一定有真正的三中模式之说。
）
2、excel数据的绝对引用。
参加了办公自动化培训班的朋友和自学excel的朋友应该对他不陌生。
举例说明一下，还以上面的那些数据为例：
计算数据一中每个数据与最大值的差。
（呵呵，也很常用的，就是计算平级的人或者部门之间的差距）
我们一般会在d2单元格输入
=MAX（A2:
A6）-A2
可是我们无法通过拖动，生成但d3、d4、d5等下面单元格的数据，当我们拖动的时候，在d3单元格变成了
=MAX（A3:
A7）-A3
这显然不是我们要的，我们在d3单元格想要的是
=MAX（A2:
A6）-A3
所以，我们可以使用$来固定那些需要不变的数据，如上面a2单元格的公式就可以写成
=MAX（A$2:
A$6）-A2
随你怎样拖动，max计算的区域就不会变化了。
如果你想固定的更牢靠一点，可以在列标号前加$。
3、同文件内excel表间引用。
同文件内不同表之间的数据引用和计算通常是使用英文状态的引号表名结合而来的，例如本例中：
引用表1当中的a列数据，可以在a2单元格输入
='1'!
a2
其中英文单引号中为表的名字，表和单元格名称间用英文的!
分割开来。
4、不同文件间的excel引用。
使用英文的中括号[]来引用文件，如我们将正在操作的文件保存到d盘，默认名字为book1.xls新建一个excel文件，引用book1.xls文件中第一列的数据：
=[book1.xls]1!
a2
此例和上面的例子中英文的单引号'可要可不要，不过excel默认都会给加上，另外本例中，当我们输入完公式，打回车键之后，excel会自动加上文件地址，如本例中可能会变为：
='d:
\[book1.xls]1'!
a2
excelcount函数
count函数是计算个数函数。
意思是统计出现数字的单元格个数，它出现在excel函数的常用函数、统计函数中。
count函数语法形式：
COUNT（value1,value2,...）
count函数应用注意事项：
1、函数会将参数所包含的数字、文本格式的数字、日期计算在内；
2、如果参数内是单元格引用则只会统计数字；如果要统计引用单元格中的逻辑值、文字或错误值，请使用函数COUNTA；
count函数示例：
样表
A
1
10
2
3
false
4
0
5
2011-1-1
上表中A为列号，左侧1-5为行号。
在某单元格输入以下公式的结果：
例1、=count（1,a1:
a3）结果为2，因为1作为参数也参与了统计；
例2、=count（a1:
a5）结果为3，此处的0，和时间2011-1-1都会被统计；
excelrank函数
rank函数是排名函数。
rank函数最常用的是求某一个数值在某一区域内的排名。
rank函数语法形式：
rank（number,ref,[order]）
函数名后面的参数中number为需要求排名的那个数值或者单元格名称（单元格内必须为数字），ref为排名的参照数值区域，order的为0和1，默认不用输入，得到的就是从大到小的排名，若是想求倒数第几，order的值请使用1。
下面给出几个rank函数的范例：
示例1：
正排名
此例中，我们在B2单元格求20这个数值在A1:
A5区域内的排名情况，我们并没有输入order参数，不输入order参数的情况下，默认order值为0，也就是从高到低排序。
此例中20在A1:
A5区域内的正排序是1，所以显示的结果是1。
示例2：
倒排名
此例中，我们在上面示例的情况下，将order值输入为1，发现结果大变，因为order值为1，意思是求倒数的排名，20在A1:
A5区域内的倒数排名就是4。
示例3：
求一列数的排名
在实际应用中，我们往往需要求某一列的数值的排名情况，例如，我们求A1到A5单元格内的数据的各自排名情况。
我们可以使用单元格引用的方法来排名：
=rank（a1,a1:
a5），此公式就是求a1单元格在a1:
a5单元格的排名情况，当我们使用自动填充工具拖拽数据时，发现结果是不对的，仔细研究一下，发现a2单元格的公式居然变成了=rank（a2,a2:
a6）这超出了我们的预期，我们比较的数据的区域是a1:
a5，不能变化，所以，我们需要使用$符号锁定公式中a1:
a2这段公式，所以，a1单元格的公式就变成了=rank（a1,a$1:
a$5）。
excelsum函数
sum函数是求和函数。
它是excel函数中最为常用的函数之一，sum函数分别出现在数学函数、全部函数两个类别中，默认的“常用函数”中也有。
sum函数的语法形式为：
sum（number1,number2,...）
sum函数的注意事项：
1、函数的语法中number1，number2等参数，最多有30个；
2、函数的语法中number1，number2等参数，既可以是数字（例1），也可以是逻辑值（例3），也可以是表达式（例3），也可以是单元格名称，也可以是连续单元格的集合（例2），也可以是单元格区域名称，并且以上所列类别将会被计算；
3、如果number1等参数为单元格名称、连续单元格集合、单元格区域名称则只计算其中的数值和函数公式数值结果部分，不计算逻辑值、表格中的文字表达式（例4）；
sum函数示例：
样表
A
1
3
2
1
3
1+2
4
（1+2=3）
5
3d
上表中A为列号，左侧1-5为行号。
若在A6单元格输入以下公式的结果：
例1、=sum（1,2,3）结果为6，计算1、2、3三个数字的和；
例2、=sum（a1:
a2）结果为4，计算a1到a2单元格之和；
例3、=sum（（1+2=3）,（1+2）,（a1:
a2）结果为8，因为（1+2=3）表达式的结果为真，在电脑中的结果为1，1+2表达式的结果3会被计算，a1到a2单元格之和4会被计算，所以最后的结果为8；
例4、=sum（a1:
a5）结果为4，不计算引用单元格中的文字表达式（1+2）、逻辑表达式（（1+2=3））、不计算字符；
excelaverage函数
average函数是求平均数函数。
它和sum函数一样是excel函数中使用频率较高的常用函数，出现在统计函数中，默认的常用函数中也有。
average函数语法：
AVERAGE（number1,number2,...）
average函数应用注意事项：
1、语法中的number1等参数可以是数字也可以是单元格的名称或者是连续单元格的集合；
2、average函数只计算参数或参数所包含每一个数值单元格（或通过公式计算得到的数值）的平均数，不计算非数值区域；
3、为空的单元格不会被计算，但为0的单元格会被计算（例2、例3）；
average函数示例：
样表
A
1
10
2
5
3
4
0
上表中A为列号，左侧1-5为行号。
在某单元格输入以下公式的结果：
例1、=average（1,3,a2）结果为3，计算1、3和a2单元格的5这三个数的平均数；
例2、=average（a1,a4）结果为5，计算a1、a4单元格数值的平均数（a4单元格值为0，参与计算）；
例3、=average（a1:
a4）结果为5，计算a1连续到a4单元格的平均数（a3单元格为空，不参与计算）
excelmax函数
max函数是求最大值函数。
它是excel函数中使用频率较高的常用函数，例如用来计算学生最高成绩、员工最高工资、以及最大积分等。
max函数语法：
max（number1,number2,...）
max函数应用注意事项：
1、其中的参数number1、number2等可以是数字，单元格名称，连续单元格区域，逻辑值；
2、若是单元格名称、连续单元格区域等数据引用，通常只计算其中的数值或通过公式计算的数值部分，不计算逻辑值和其它内容；
3、如果max函数后面的参数没有数字，会返回0（例2）；
max函数示例：
样表
A
1
5
2
15
3
true
4
3g
5
0
上表中A为列号，左侧1-5为行号。
在某单元格输入以下公式的结果：
例1、=max（a1:
a2,a5,true）结果为15，此例中用到了连续单元格引用a1:
a2，用到了逻辑值true（在此处true的值被看作1）；
例2、=max（a3:
a5）结果为0，此例中的逻辑值true、字符串3g不被计算；
excelsin函数
sin函数是计算正弦值函数。
它出现在excel函数的默认常用函数、数学与三角函数中。
sin函数语法形式：
sin（number）
sin函数应用注意事项：
1、number参数通常以弧度表示，若用度数则需要乘以PI（）/180或使用RADIANS函数以将其转换为弧度；
2、number可以是数字也可以是某一单元格名称；
3、number值可以是数字、逻辑值、日期；
sin函数示例：
样表
A
1
1
2
false
3
0
4
1900-1-1
上表中A为列号，左侧1-4为行号。
在某单元格输入以下公式的结果：
例1、=sin（a1）结果约等于0.84147
例2、=sin（a2）结果同例1，因为true的值被看作1；
例3、=sin（a4）有结果，结果和例1一样，大家自己试试，因为日期在excel中与数字相关联的，1900-1-1被看作是1，但直接使用公式=sin（1900-1-1）则得到另一个结果；
例4、=sin（30*PI（）/180）结果为30度正弦函数结果0.5；
例5、=sin（RADIANS（30））结果如同例4；
excelif函数if函数嵌套用法
excel函数中if函数的使用非常广泛，特别是在单条件判断的时候，用好if函数可以帮我们完成很多功能。
最简单的excelif函数应用
例子：
下图数据在d列显示如下结果：
如果数据1大于60则显示合格，否则显示不合格。
那么在d2单元格输入以下公式：
=if（a2>60,"合格","不合格"）
然后向下拖拽，自动生成数据，如下图D列效果。
if函数必须的条件：
每一个if函数必须使用英文的括号括起来；
括号内为三个数据，第一个数据是条件（如上例中的a2>60），第二数据为满足第一个数据后返回的结果，通常使用英文的引号括起来，第三个数据是不满足第一个数据时需要返回的结果；（如果不输入第三个数据可以吗，当然可以，返回什么结果自己试试吧）
经常出现的错误：
其中的符号如逗号和引号皆为英文（也就是所谓的半角）；
if的右括号放在了条件的后面；（这是在多个条件使用if函数进行嵌套时非常容易犯的错误）
if函数嵌套用法
例子：
下图数据，在e列显示如下结果：
如果数据1小于60则显示不合格，如果大于等于60而小于80则显示合格，如果大于等于80而小于90显示良好，如果大于等于90则显示优秀。
这是经典的if嵌套应用例子，需要我们使用if函数的嵌套。
if嵌套书写前，首先你要理解要求，并将要求数学化，也就是使用数学的模式表达出来，if函数多重嵌套一般情况下我们可以将它看做分段函数，那么问题就很容易解决了。
例子可以在E2单元格使用如下代码：
=if（a2<60,"不合格",if（a2<80,"合格",if（a2<90,"良好","优秀"）））
当数据1小于60时，显示不合格，这时在“不合格”逗号的右侧默认就是>=60的情况，那么根据题意，只需再满足<80即可显示合格，于是我们将最简单的if函数的第三个数据变成了一个if函数，依次类推，每一次可以将一个if函数作为每一个基本函数的第三个数据，从而形成多种嵌套。
（图例中多余在最后一个if前后加了一个括号，当然这种方法也正确，但不是最简单的。
）
其实还有另一种写法，也就是将嵌套的if写在基本if函数的第二个数据的位置，如下图，不过这种写法不常用，也比较不好理解，并且容易写错，不推荐大家使用。
if函数的高级用法
if函数除了可以引用单元格的数据还可以引用函数值或者其他表格甚至是文件的数据。
下面举个小例子：
下图数据1中，如果超过平均值的显示合格，达不到平均值的显示不合格。
函数代码如下：
=if（a2>average（$a$2:
$a$9）,"合格","不合格"）
这句话是这样理解的，当数据a2大于a2到a9所有数据的平均数时，返回合格，否则返回不合格。
其中average（）是求平均数函数，而$a$2:
$a$9是绝对定位a2到a9的区域，如果不加$这个符号，我们在拖动单元格自动生成数据时a2就会变成a3，如果横向拖动时a2就会变成b2，这可不是我们想要的。
关于这一点你可以自己尝试，如想了解更多关于excel绝对引用的知识可以参考这里，这篇文章还讲到了对其他表格的引用，有兴趣可以看看。
excelvlookup函数使用方法
今天在XX知道的时候，看到旁边有人问excel中条件查找vlookup的问题，有几位高手都知道使用vlookup作答，可惜都是没有经过测试，直接复制别人的答案。
有图详细解答一下这个问题：
问题：
如下图，已知表sheet1中的数据如下，如何在数据表二sheet2中如下引用：
当学号随机出现的时候，如何在B列显示其对应的物理成绩？
首先我们介绍下使用的函数vlookup的几个参数，vlookup是判断引用数据的函数，它总共有四个参数，依次是：
1、判断的条件
2、跟踪数据的区域
3、返回第几列的数据
4、是否精确匹配
根据问题的需求，这个公式应该是：
=vlookup（a2,sheet1!
$a$2:
$f$100,6,true）
详细说明一下在此vlookup函数例子中各个参数的使用说明：
1、a2是判断的掉条件，也就是说如果sheet2表中a列对应的数据和sheet1表中的数据相同方能引用；
2、sheet1!
$a$2:
$f$100是数据跟踪的区域，因为需要引用的数据在f列，所以跟踪的区域至少在f列，$是绝对引用（关于绝对引用可以参考这里）；
3、6这是返回什么数的列数，如上图的物理是第6列，所以应该是6，如果要求英语的数值，那么此处应该是5
4、是否绝对引用，如果是就输入true如果是近似即可满足条件那么输入false（近似值主要用于带小数点的财务、运算等）
5、vlookup是垂直方向的判断，如果是水平方向的判断可使用Hlookup函数
结果如下图：
升级会员 