Excel常用函数简介.docx

《Excel常用函数简介.docx》由会员分享，可在线阅读，更多相关《Excel常用函数简介.docx（9页珍藏版）》请在三一办公上搜索。

1、 Excel2000常用函数简介Excel2000提供了九大类，近400个函数，包括：数学与三角函数、统计函数，数据库函数、逻辑函数等。由于函数在电子表格中起着很重要的作用，要想熟练地、较深入地使用Excel2000，就必须对函数有一定的了解，因此我们用一点篇幅，对Excel2000常用函数作以简单介绍。如果在实际应用中需要使用本节没有介绍的其他函数及函数的详细使用方法，用户可以参阅Excel2000的“帮助”系统或其他参考手册和资料。（1）数学函数 取整函数INT（X）取数值X的整数部分，即不超过X的最大整数。如：INT（56.87）的运算结果值为56，INT(-56.87)的运算结果为-5

2、7。 截取整函数TRUNC（XI，X2）将数字X1的小数部分保留X2位，其余全部截去。X2默认为0，且可省略。 例如，TRUNC(8.9)等于8，TRUNC(-8.329，2)等于-8.32。说明：函数TRUNC和函数INT类似，都能返回整数。函数TRUNC直接去除指定位数之后部分，而函数INT则是依照给定的数，取不超过该数的最大整数。函数INT和函数TRUNC在处理负数时会有不同：TRUNC(-4.3)返回-4，但INT(-4.3)返回-5。 四舍五入函数ROUND（X1，X2）将数值X1四舍五入，小数部分保留X2位。如：ROUND(536.8175,3)等于536.818。 求余数函数MO

3、D（x，y）返回数字X除以y得到的余数。如：MOD(5,2)等于1。 圆周率函数PI( )取圆周率的近似值3.141592654（没有参数）。 随机数函数RAND（ ）产生一个0和1之间的随机数（没有参数）。 求平方根函数SQRT（X）返回正值X的平方根。如：SQRT（9）等于3。（2）统计函数 求平均值AVERAGE（X1，X2，）返回所列范围中所有数值的平均值。最多可有30个参数，参数X1，X2可以是数值、单元格区域或区域名字。例如，AVERAGE（5,3,10,4,6,9）等于6.166667。AVERAG(A1:A5,C1:C5)返回从区域A1:A5和区域C1:C5中的所有单元格数值的

4、平均值。 COUNT（XI，X2，）返回所列参数（最多30个）中数值的个数。函数COUNT在计数时，把数字、文本、空值、逻辑值和日期计算进去，但是错误值或其他无法转化成数据的内容则被忽略。这里的“空值”是指函数的参数中有一个“空参数”，和工作表单元格的“空白单元”是不同的。例如，COUNT(”ABC”,1,3,True, ,5)中就有一个“空值”，计数时也计算在内，该函数的计算结果为5；而COUNT（H15:H27）是计算范围为H15到H27中非空白的数字单元格的个数。注意！空白单元格不计算在内。 COUNTA（X1，X2，）返回所列参数（最多30个）中数据项的个数。在这里，“数据”是广义的概

5、念，计数值可以是任何类型，它们可以包括空字符(”)。例如，COUNTA(”ABC”,l,2, , ”,False)的计算结果为6。如果参数是单元格引用，则引用中的空白单元格也被忽略。 COUNTIF（X1, X2）计算给定区域X1满足条件X2的单元格的数目。条件X2的形式可以为数字、表达式或文本，如32、”32”、”32”、”apples”等。例如，假设A3:A6中的内容分别为”男”、”女”、”男”、”男”，则 COUNTIF（A3:A6，”男”）等于 3。假设B3:B6中的内容分别为32、54、75、86，则C0UNTIF(B3:B6,”55”)等于2。 COUNTBLANK(x)计算指定区

6、域X中空白单元格的数目。 说明：含有返回值为 ”（空文本）的公式单元格也计算在内，但包含零值的单元格不计算在内。例如：假设工作表中的区域B2:C5中有两个空单元（没有输入任何内容），则COUNTBLANK(B2:C5)等于2。 求最大值函数MAX（List）返回List中的最大数值，List可以是一数值、单元格、单元格区域、公式。例如，MAX(98,A8,B1:B5), MAX(D1:D88)等等。 MIN（List）返回List中的最小数值。List的意义同MAX。例如，MIN(C2:G28)，MIN(2,A5,C3:D6)等等。 求和函数SUM（X1，X2，）返回包含在引用中的值的总和。X

7、1，X2等可以是单元格、单元格区域或一个数值。如：SUM(A1:A5, C6:C8)返回区域A1至A5和C6至C8中的值的总和。 SUMIF(XI,X2,X3)根据指定条件x2对若干单元格求和。其中：X1为用于条件判断的单元格区域。X2为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为95、”76”、”60”、”wmen”、”男”等等。X3为需要求和的实际单元格。只有当X2中的相应单元格满足条件时，才对X3中的单元格求和。如果省略X3。则直接对X1中的单元格求和。例如，假设A1:A4的内容分别为：100,000，200,000，300,000，400,00

8、O；B1:B4的内容为；7,00O，14,00O，21,00O，28,000。则SUMIF(A1:A4，”160,000”,B1:B4)等于63,00O，即B2、B3、B4之和。（3）字符函数 LEFT（S，X） 返回参数S中包含的最左的X个字符。S可以是一字符串（用引号括住）、包含字符串的单元格地址或字符串公式。X缺省时为1。例如，单元格R8中为字符串“Beijing Sport University”，则LEFT(B8,7)返回“Beijing”。 MID（S, X1, X2）返回字符串S中从第X1个字符位置开始的X2个字符。S的意义同上。例如，MID（”北京体育大学运动训练系”，3，4）

9、返回“体育大学”。 RIGHT（S，X）返回参数S中包含的最右边的X个字符。X的意义与LEFT中的相同。若X为0，则不返回字符；若X大于整个字符串的字符个数，则返回整个字符串。X缺省时为1。例如，单元格G8中为字符串“Hello World”，则 RIGHT（B8,5）返回“World”。 RIGHT(”北体大教育学院”,5)则返回“大教育学院”。 SEARCH（X1,X2,X3） 返回在X2中第1次出现X1时的字符位置，查找顺序为从左至右。如果找不到X1，则返回错误值VALUE！。查找文本时，函数SEARCH不区分大小写字母。其中 x1中可以使用通配符？和* 。X3为查找的起始字符位置。从左

10、边开始计数，表示从该位置开始进行查找。X3缺省时为1。例如，SEARCH（”e”,”Statements”,1）等于5；SEARCH（”e”,”Statements”,6）等于7，SEARCH（”e”,”Statements”,8）返回错误值“#VALUE！”。如果单元格B17为单词“margin”，单元格A14为“Profit Margin”，则：SEARCH($B$17,$A$14)等于8。 FIND（X1,X2,X3）返回在X2中第1次出现X1时的字符位置，X3为开始查找位置，顺序为从左至右。但是函数FIND在查找时区分大小写字母，并且不允许使用通配符。如果X1没有在X2中出现，函数FI

11、ND返回错误值#VALUE！。例如，FIND(”M”, ”Miriam McGovern”)等于 1；FIND(”m”, ”Miriam McGovern”)等于 6 省略X3，则从第一个位置开始查找。FIND(”M”, ”Miriam McGovern”,2)等于8 为了跳过第一个“M”，X3取2。FIND(”M”, ”Miriam McGovern”,9)等于#VALUE。（4）日期和时间函数 在工作表中，日期和时间可以用用户所熟悉的方式来显示。但是如果把单元格的格式设定为“数值”，则日期就显示成一个数值。如果日期中包含时间，则会被显示为一个带有小数的数值。这是因为，Excel把1900年

12、1月1日定为1，在其以后的日期就对应着一个序列数。如 2002年9月 30日就是37529。 Excel又把一天作为 1，再把每天的时间折算为十进制数，因此2002年9月 30日9月30日上午6:00，就被转换为37529.25了。 日期和时间都是数值，因此它们也可以进行各种运算。例如，如果要计算两个日期之间的差值，可以用一个日期减去另一个日期。通过将单元格的格式设置为“常规”格式，可以看到以数字或小数显示的日期或时间。 当在Excel2000中输入日期，如果为年份输入的数字在00到29之间， Excel将其解释为 2000到 2029年。例如，如果键人 19/9/28，Excel就认为这个日

13、期是2019年9月28日。 如果为年份输入的数字在30到99之间，Excel将其解释为1930到1999年。例如，如果键人 99/9/28， Excel认为这个日期是 1999年 9月 28日。 DATE(year年,month月,day日)返回指定日期的序列数。（1900year2078）。例如，DATE（2002，1，1）返回 37257。 DAY（X1）返回日期X1对应的一个月内的序数，用整数1到31表示。X1不仅可以为数字，还可以是字符串（日期格式，用引号引起来）。例如， DAY(“15-Apr-2002”)等于 15； DAY(“2002811”)等于11。 MONTH（XI）返回日

14、期X1对应的月份值。该返回值为介于1和12之间的整数。例如，MONTH（“18-May”）等于5；MONTH(345)等于12。 YEAR（X1）返回日期X1对应的年份。返回值为1900到2078之间的整数。例如，YEAR(“2002/7/5”)返回2002。 NOW（）返回当前日期和时间所对应的数值。例如，计算机的内部时钟为2002年7月12日中午15点59分，则：NOW（）等于37449.67。 TIME（X1，X2，X3）返回X1小时X2分X3秒所对应的数值。返回值为一个纯小数。例如，TIME（16，48， 10）返回数值0.700115741，等价于4:48:10 PM。（5）条件函数

15、IF（x，n1，n2）根据逻辑值x判断，若x的值为True，则返回n1，否则返回n2。其中n2可以省略。例如，if(E289，”A”, ”B”)等。例子：假设学生考试成绩表如图0所示图0 学生考试成绩表在H列中根据三门考试成绩的平均分自动给出其等级：=86为优，76至85为良，60至75为及格，60分以下为不及格。操作步骤如下： 在H2中输入公式： =IF（G2=86,”优”,IF(G2=76,”良”,IF(G2=60,”及格”,”不及格”)）然后利用“填充柄”把H2复制到H3:H7即可。（6）逻辑函数 “非”函数 NOT(逻辑值或逻辑表达式)对参数值求反。当要确保一个值不等于某一特定值时，可

16、以使用 NOT 函数。其中的参数为一个可以计算出 True 或 False 的逻辑值或逻辑表达式。如果逻辑值为 False，函数 NOT 返回 True；如果逻辑值为 True，函数 NOT 返回 False。例如，NOT(FALSE) 等于 True；NOT(1+1=2) 等于 False “与”函数 AND（X1，X2，）所有参数的逻辑值为真（非0）时返回Teue；只要一个参数的逻辑值为假（为0）即返回False。其中，参数X1，X2为待检测的若干个条件值（最多30个），各条件值必须是逻辑值（True或False）、计算结果为一逻辑值、或者是包含逻辑值的单元格引用。如果引用的参数包含文字或

17、空单元格，则忽略其值。如果指定的单元格区域内包括非逻辑值，AND返回错误值VALUE！。例如， AND（True,True）等于True； AND（224，235）等于False。例子：假定在B4中已输入数据。如果B4的值严格介于l100之间，则在C4单元格中显示B4单元格中数值，否则就显示一条提示信息。 按题意要求，应在C4中输入公式： =IF(AND(1B4,B48585然后再在E17单元中写公式=DCOUNTA(A1:F12,1,C14:D15)。就可以得到结果。 对于DCOUNT和DCOUNA函数，第二个参数（用来指明哪一个字段）都可以用任意的数字字段；而DCOUNA第二个参数还可以用

18、非数字字段，并且两个函数都可以忽略第二个参数，只用一个逗号即可。例如：DCOUNTA(A1:F12, ,C14:D15)。图2 求满足条件的总分平均分如果要统计出数学和语文成绩都高于85分的那些学生的总分的平均值，则可以用：=DAVERAGE(A1:G12,7,C14:D15)。7表示第七列是“总分”那一列，如图2所示。（8）其他函数其他函数中只节选了频度分析函数和排名次函数，体育工作者在日常工作中会经常使用这两个函数。 频度分析函数FREQUENCY（X1，X2）将某个区域X1中的数据按一列垂直数组X2（给出分段点）进行频率分布的统计，统计结果存放在X2的右边列的对应位置。图3 做“平均分”

19、的频度分布统计 例题：如图3所示，工作表里已输入了11名考生考试成绩，并且求出了每个考生四门功课的总分和平均分。现在要统计一下平均成绩在060、6170、7180、8190、91100各区间中的人数。这是一个频度分布问题，可以应用FREQUENCY函数解决。把“平均分”所在区域H2:H129作为数据区域X1；在垂直区域K3:K6中分别输入分段点的分数60、70、80和90，作为X2。在应用函数条件后，其结果将存放在K列的相邻的L列的L3:L7(比区域X2多一个单元格)。 具体操作步骤如下：第一步 在区域K3:K6中输入分段点的分数 60、70、80和90；图4 “平均分”的频度分布统计结果第二

20、步 选定显示结果的区域L3:L7；第三步 用鼠标单击编辑栏，在编辑栏中输入公式=FREQUENCY(H2:H12,K3:K6)；第四步 按Ctrl + Shift + Enter，就得到统计结果。统计结果为0，1；3；6；1，如图4所示。注意：在输入公式之前一定要选定显示结果的区域。返回数组中的元素个数比x2的元素数目要多出一个。输入公式完毕后，要按Ctrl + Shift + Enter，不能只按Enter键。 排名次函数RANK(X1,X2,X3)返回单元格 X1在一个垂直区域 X2中的排位名次。X3是排位的方式。X3为 0或省略，则按降序排名次。X3不为0则按升序排名次。排名次函数RAN

21、K对相同数的排位相同，但相同数的存在将影响后续数值的排位。图5 按照“平均分”排名次例题：如图5所示，工作表里已输入了11名考生考试成绩，并且求出了每个考生四门功课的总分和平均分。要求按照“平均分”在I列的“名次”下由大到小排出名次。具体操作步骤如下：第一步 选定区域H2:H12，这是参加排名次数据所在的区域；第二步 单击“单元格名称框”，将区域H2:H12命名为PJF；图6 排名次最后结果第三步 选定I2单元格并在其中输入公式=RANK(H2,PJF,0)；第四步 按下编辑栏中的“确定”按钮或直接按回车键，I2单元格显示名次“4”；第五步 鼠标指针指向I2单元格的“填充柄”，按下左键向下拖拉至I12，释放鼠标左键，那么，I2:I12中显示的就是排的名次结果，如图6所示。从排名次最后处理结果看，成绩相同的“名次”也相同，但后续名次的排位受到了影响，例如“平均分”为83.5分的名次均为“4”，共有3人，但名次为“5”、“6”的没有了，这正是竞技体育中比赛结果排名次的一般规则。168