Excel 公式与函数.docx
- 文档编号:7973672
- 上传时间:2023-01-27
- 格式:DOCX
- 页数:20
- 大小:165.61KB
Excel 公式与函数.docx
《Excel 公式与函数.docx》由会员分享,可在线阅读,更多相关《Excel 公式与函数.docx(20页珍藏版)》请在冰豆网上搜索。
Excel公式与函数
公式和函数
1公式
1.1公式输入方法
1.1.1公式是在工作表中对数据进行分析的等式。
它可以对工作表数值进行加法、减法或乘法等运算。
还可以引用同一工作表中的其他单元格、同一工作薄不同工作表中的单元格、或者其它工作薄的工作表中的单元格。
1.1.2公式由运算符、常量、单元格引用值、名称和工作表函数等元素构成
1、公式中使用的运算符
根据公式所处理的数据类型及运算结果,可将常用运算符号分为:
算术运算符、文字运算符和比较运算符。
2·算术运算符
包括:
+(加)、-(减)、*(乘)、/(除)、^(乘幂)、%(百分号,即求百分数)等。
例:
8^3*25%表示8的立方再乘以0.25,结果为128。
日期/时间型数据也可以参以简单运算,例如,A1+80(假设A1为日期型数据)表示日期A1加上80天。
3·文字运算符
只有一个运算符&。
其作用是把两个文字连接起来而生成一个新的文字值。
例:
A1&B2;“计算机”&“电脑”→计算机电脑
4·比较运算符
包括:
=(等于)、>(大于)、<(小于)、>=(大于等于)、<=(小于等于)、<>(不等于)。
比较运算的结果是逻辑值,即TRUE(真)、FALSE(假)。
4.1Excel具有强大的计算功能,为分析数据和处理工作表中的数据提供了极大的方便。
在公式中,可以对工作表数值进行加、减、乘、除等运算。
只要输入正确的计算公式之后,就会立即在单元格中显示计算结果。
如果工作表中的数据有变动,系统会自动将变动之后的答案算出,使用户能随时观察到正确的结果。
52、输入公式
5.1.1直接键入的方法:
⑴选定需要输入公式的单元格。
⑵在所选的单元格中输入等号"=",如果单击了"编辑公式"(编辑栏)按钮或"粘贴函数"(常用工具栏)按钮,这时将自动插入一个等号。
⑶输入公式内容。
如果计算中用到单元格中的数据,可用鼠标单击所需引用的单元格,如果输入错了,在未输入新的运算符之前,可再单击正确的单元格;也可使用手工方法引用单元格,即在光标处键入单元格的坐标。
⑷公式输入完后,按Enter键,Excel自动计算并将计算结果显示在单元格中,公式内容显示在编辑栏中。
⑸按Ctrl+`(位于数字键左端),可使单元格在显示公式内容与公式结果之间进行切换。
5.1.2从上述步骤可知,公式的最前面必须是等号"=",后面是计算的内容。
5.2例如,要在G4中建立一个公式来计算E4+F4的值,则在G4中输入:
=E4+F4
输入公式后按回车键确认,结果将显示在G4单元格中。
5.2.1在编辑栏里输入公式的步骤为:
5.2.2单击要输入公式的单元格(即需要结算结果的单元格)。
5.2.3输入等号“=”或单击编辑栏中的编辑公式按钮“=”。
5.2.4在编辑栏里输入公式(运算符和数据),如果公式中的数据项是其他单元格的数据,可以直接单击该单元格来输入数据。
5.2.5输入完毕后,按回车键或单击编辑栏中的输入按钮“√”。
6
(二)公式使用技巧
1.复制
复制是将公式应用于其它单元格的操作,最常用的有以下几种方法:
(1)拖动复制
操作方法是:
选中存放公式的单元格,移动空心十字光标至单元格右下角。
待光标变成小实心十字时,按住鼠标左键沿列(对行计算时)或行(对列计算时)拖动,至数据结尾完成公式的复制和计算。
公式复制的快慢可由小实心十字光标距虚框的远近来调节:
小实心十字光标距虚框越远,复制越快;反之,复制越慢。
(2)输入复制
此法是在公式输入结束后立即完成公式的复制。
操作方法是:
选中需要使用该公式的所有单元格,用上面介绍的方法输入公式,完成后按住Ctrl键并按回车键,该公式就被复制到已选中的所有单元格。
(3)选择性粘贴
操作方法是:
选中存放公式的单元格,单击Excel工具栏中的“复制”按钮。
然后选中需要使用该公式的单元格,在选中区域内单击鼠标右键,选择快捷选单中的“选择性粘贴”命令。
打开“选择性粘贴”对话框后选中“粘贴”命令,单击“确定”,公式就被复制到已选中的单元格。
2.编辑
(1)打开公式选项板
如果公式是由几个Excel函数组合而成,而你又要用公式选项板修改某个函数的参数,则可用鼠标单击函数名称或括号内的参数,再单击编辑栏左端的“=”按钮,即可打开这个函数的公式选项板。
修改完毕后单击“确定”将其关闭。
(2)公式与结果切换
单元格中的公式执行后显示的是计算结果,按Ctrl+′键(位于键盘左上角),可使公式在显示公式内容与显示公式结果之间切换,方便了公式编辑和计算结果查看。
(3)单元格引用
单元格引用的目的在于确定计算范围。
鼠标拖动引用单元格既快捷又不易出错。
以SUM函数为例,如果你使用了公式选项板,可让光标停在“Number”框内,然后用鼠标拖过需要计算的单元格。
若这些单元格是不连续的,可以按住Ctrl键分别拖过它们。
如果你是用手工输入的公式,可用鼠标在SUM字样后的括号内单击,然后按相同方法拖过需要计算的单元格区域,则被引用的单元格区域及其分隔符会自动出现在公式中。
7二函数
7.1.1.1什么是函数
7.2Excel中所提的函数其实是一些预定义的公式,它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。
用户可以直接用它们对某个区域内的数值进行一系列运算,如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。
例如,SUM函数对单元格或单元格区域进行加法运算。
7.3函数是否可以是多重的呢?
也就是说一个函数是否可以是另一个函数的参数呢?
当然可以,这就是嵌套函数的含义。
所谓嵌套函数,就是指在某些情况下,您可能需要将某函数作为另一函数的参数使用。
例如下图中所示的公式使用了嵌套的AVERAGE函数,并将结果与50相比较。
这个公式的含义是:
如果单元格F2到F5的平均值大于50,则求F2到F5的和,否则显示数值0。
8
8.1在学习Excel函数之前,我们需要对于函数的结构做以必要的了解。
函数的结构以函数名称开始,后面是左圆括号、以逗号分隔的参数和右圆括号。
如果函数以公式的形式出现,请在函数名称前面键入等号(=)。
如:
=SUM(A1:
B4,F3,G5:
H10)+D6;
8.2在创建包含函数的公式时,公式选项板将提供相关的帮助。
8.3公式选项板--帮助创建或编辑公式的工具,还可提供有关函数及其参数的信息。
单击编辑栏中的"编辑公式"按钮,或是单击"常用"工具栏中的"粘贴函数"按钮之后,就会在编辑栏下面出现公式选项板。
整个过程如下图:
8.3.1
9
(二)用函数的步骤
在Excel中如何使用函数呢?
1.单击需要输入函数的单元格,如图所示,单击单元格C1,出现编辑栏
10
10.1.12.单击编辑栏中"编辑公式"按钮,将会在编辑栏下面出现一个"公式选项板",此时"名称"框将变成"函数"按钮,如图所示。
11
11.1.13.单击"函数"按钮右端的箭头,打开函数列表框,从中选择所需的函数;
11.1.24.当选中所需的函数后,Excel2000将打开"公式选项板"。
用户可以在这个选项板中输入函数的参数,当输入完参数后,在"公式选项板"中还将显示函数计算的结果;
11.25.单击"确定"按钮,即可完成函数的输入;
11.36.如果列表中没有所需的函数,可以单击"其它函数"选项,打开"粘贴函数"对话框,用户可以从中选择所需的函数,然后单击"确定"按钮返回到"公式选项板"对话框。
12在了解了函数的基本知识及使用方法后,请跟随笔者一起寻找Excel提供的各种函数。
您可以通过单击插入栏中的"函数"看到所有的函数。
13
13.1函数的种类
13.2Excel函数一共有11类,分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。
13.2.1.1.1数据库函数--当需要分析数据清单中的数值是否符合特定条件时,可以使用数据库工作表函数。
例如,在一个包含销售信息的数据清单中,可以计算出所有销售数值大于1,000且小于2,500的行或记录的总数。
MicrosoftExcel共有12个工作表函数用于对存储在数据清单或数据库中的数据进行分析,这些函数的统一名称为Dfunctions,也称为D函数,每个函数均有三个相同的参数:
database、field和criteria。
这些参数指向数据库函数所使用的工作表区域。
其中参数database为工作表上包含数据清单的区域。
参数field为需要汇总的列的标志。
参数criteria为工作表上包含指定条件的区域。
13.2.1.1.2日期与时间函数--通过日期与时间函数,可以在公式中分析和处理日期值和时间值。
13.2.1.1.3工程函数--工程工作表函数用于工程分析。
这类函数中的大多数可分为三种类型:
对复数进行处理的函数、在不同的数字系统(如十进制系统、十六进制系统、八进制系统和二进制系统)间进行数值转换的函数、在不同的度量系统中进行数值转换的函数。
13.2.1.1.4财务函数--财务函数可以进行一般的财务计算,如确定贷款的支付额、投资的未来值或净现值,以及债券或息票的价值。
财务函数中常见的参数:
13.2.2未来值(fv)--在所有付款发生后的投资或贷款的价值。
13.2.3期间数(nper)--投资的总支付期间数。
13.2.4付款(pmt)--对于一项投资或贷款的定期支付数额。
13.2.5现值(pv)--在投资期初的投资或贷款的价值。
例如,贷款的现值为所借入的本金数额。
13.2.6利率(rate)--投资或贷款的利率或贴现率。
13.2.7类型(type)--付款期间内进行支付的间隔,如在月初或月末。
13.2.7.1.1信息函数--可以使用信息工作表函数确定存储在单元格中的数据的类型。
信息函数包含一组称为IS的工作表函数,在单元格满足条件时返回TRUE。
例如,如果单元格包含一个偶数值,ISEVEN工作表函数返回TRUE。
如果需要确定某个单元格区域中是否存在空白单元格,可以使用COUNTBLANK工作表函数对单元格区域中的空白单元格进行计数,或者使用ISBLANK工作表函数确定区域中的某个单元格是否为空。
13.2.7.1.2逻辑函数--使用逻辑函数可以进行真假值判断,或者进行复合检验。
例如,可以使用IF函数确定条件为真还是假,并由此返回不同的数值。
13.2.7.1.3查询和引用函数--当需要在数据清单或表格中查找特定数值,或者需要查找某一单元格的引用时,可以使用查询和引用工作表函数。
例如,如果需要在表格中查找与第一列中的值相匹配的数值,可以使用VLOOKUP工作表函数。
如果需要确定数据清单中数值的位置,可以使用MATCH工作表函数。
13.2.7.1.4数学和三角函数--通过数学和三角函数,可以处理简单的计算,例如对数字取整、计算单元格区域中的数值总和或复杂计算。
13.2.7.1.5统计函数--统计工作表函数用于对数据区域进行统计分析。
例如,统计工作表函数可以提供由一组给定值绘制出的直线的相关信息,如直线的斜率和y轴截距,或构成直线的实际点数值。
13.2.7.1.6文本函数--通过文本函数,可以在公式中处理文字串。
例如,可以改变大小写或确定文字串的长度。
可以将日期插入文字串或连接在文字串上。
下面的公式为一个示例,借以说明如何使用函数TODAY和函数TEXT来创建一条信息,该信息包含着当前日期并将日期以"dd-mm-yy"的格式表示。
13.2.7.1.7用户自定义函数--如果要在公式或计算中使用特别复杂的计算,而工作表函数又无法满足需要,则需要创建用户自定义函数。
这些函数,称为用户自定义函数,可以通过使用VisualBasicforApplications来创建。
13.2.7.1.8以上对Excel函数及有关知识做了简要的介绍,在以后的文章中笔者将逐一介绍每一类函数的使用方法及应用技巧。
但是由于Excel的函数相当多,因此也可能仅介绍几种比较常用的函数使用方法,其他更多的函数您可以从Excel的在线帮助功能中了解更详细的资讯。
14附常用的函数:
15ABS函数
15.1函数名称:
ABS
15.2主要功能:
求出相应数字的绝对值。
15.3使用格式:
ABS(number)
15.4参数说明:
number代表需要求绝对值的数值或引用的单元格。
15.5应用举例:
如果在B2单元格中输入公式:
=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100)。
15.6特别提醒:
如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值“#VALUE!
”。
16AND函数
16.1函数名称:
AND
16.2主要功能:
返回逻辑值:
如果所有参数值均为逻辑“真(TRUE)”,则返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。
16.3使用格式:
AND(logical1,logical2,...)
16.4参数说明:
Logical1,Logical2,Logical3……:
表示待测试的条件值或表达式,最多这30个。
16.5应用举例:
在C5单元格输入公式:
=AND(A5>=60,B5>=60),确认。
如果C5中返回TRUE,说明A5和B5中的数值均大于等于60,如果返回FALSE,说明A5和B5中的数值至少有一个小于60。
16.6特别提醒:
如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!
”或“#NAME”。
17AVERAGE函数
17.1函数名称:
AVERAGE
17.2主要功能:
求出所有参数的算术平均值。
17.3使用格式:
AVERAGE(number1,number2,……)
17.4参数说明:
number1,number2,……:
需要求平均值的数值或引用单元格(区域),参数不超过30个。
17.5应用举例:
在B8单元格中输入公式:
=AVERAGE(B7:
D7,F7:
H7,7,8),确认后,即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。
17.6特别提醒:
如果引用区域中包含“0”值单元格,则计算在内;如果引用区域中包含空白或字符单元格,则不计算在内。
18COLUMN函数
18.1函数名称:
COLUMN
18.2主要功能:
显示所引用单元格的列标号值。
18.3使用格式:
COLUMN(reference)
18.4参数说明:
reference为引用的单元格。
18.5应用举例:
在C11单元格中输入公式:
=COLUMN(B11),确认后显示为2(即B列)。
18.6特别提醒:
如果在B11单元格中输入公式:
=COLUMN(),也显示出2;与之相对应的还有一个返回行标号值的函数——ROW(reference)。
19CONCATENATE函数
19.1函数名称:
CONCATENATE
19.2主要功能:
将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。
19.3使用格式:
CONCATENATE(Text1,Text……)
19.4参数说明:
Text1、Text2……为需要连接的字符文本或引用的单元格。
19.5应用举例:
在C14单元格中输入公式:
=CONCATENATE(A14,"@",B14,".com"),确认后,即可将A14单元格中字符、@、B14单元格中的字符和.com连接成一个整体,显示在C14单元格中。
19.6特别提醒:
如果参数不是引用的单元格,且为文本格式的,请给参数加上英文状态下的双引号,如果将上述公式改为:
=A14&"@"&B14&".com",也能达到相同的目的。
20COUNTIF函数
20.1函数名称:
COUNTIF
20.2主要功能:
统计某个单元格区域中符合指定条件的单元格数目。
20.3使用格式:
COUNTIF(Range,Criteria)
20.4参数说明:
Range代表要统计的单元格区域;Criteria表示指定的条件表达式。
20.5应用举例:
在C17单元格中输入公式:
=COUNTIF(B1:
B13,">=80"),确认后,即可统计出B1至B13单元格区域中,数值大于等于80的单元格数目。
20.6特别提醒:
允许引用的单元格区域中有空白单元格出现。
21DATE函数
21.1函数名称:
DATE
21.2主要功能:
给出指定数值的日期。
21.3使用格式:
DATE(year,month,day)
21.4参数说明:
year为指定的年份数值(小于9999);month为指定的月份数值(可以大于12);day为指定的天数。
21.5应用举例:
在C20单元格中输入公式:
=DATE(2003,13,35),确认后,显示出2004-2-4。
21.6特别提醒:
由于上述公式中,月份为13,多了一个月,顺延至2004年1月;天数为35,比2004年1月的实际天数又多了4天,故又顺延至2004年2月4日。
22DATEDIF函数
函数名称:
DATEDIF
22.1主要功能:
计算返回两个日期参数的差值。
22.2使用格式:
=DATEDIF(date1,date2,"y")、=DATEDIF(date1,date2,"m")、=DATEDIF(date1,date2,"d")
22.3参数说明:
date1代表前面一个日期,date2代表后面一个日期;y(m、d)要求返回两个日期相差的年(月、天)数。
22.4应用举例:
在C23单元格中输入公式:
=DATEDIF(A23,TODAY(),"y"),确认后返回系统当前日期[用TODAY()表示)与A23单元格中日期的差值,并返回相差的年数。
22.5特别提醒:
这是Excel中的一个隐藏函数,在函数向导中是找不到的,可以直接输入使用,对于计算年龄、工龄等非常有效。
23DAY函数
23.1函数名称:
DAY
23.2主要功能:
求出指定日期或引用单元格中的日期的天数。
23.3使用格式:
DAY(serial_number)
23.4参数说明:
serial_number代表指定的日期或引用的单元格。
23.5应用举例:
输入公式:
=DAY("2003-12-18"),确认后,显示出18。
23.6特别提醒:
如果是给定的日期,请包含在英文双引号中。
24DCOUNT函数
24.1函数名称:
DCOUNT
24.2主要功能:
返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。
24.3使用格式:
DCOUNT(database,field,criteria)
24.4参数说明:
Database表示需要统计的单元格区域;Field表示函数所使用的数据列(在第一行必须要有标志项);Criteria包含条件的单元格区域。
24.5应用举例:
如所示,在F4单元格中输入公式:
=DCOUNT(A1:
D11,"语文",F1:
G2),确认后即可求出“语文”列中,成绩大于等于70,而小于80的数值单元格数目(相当于分数段人数)。
25
26特别提醒:
如果将上述公式修改为:
=DCOUNT(A1:
D11,,F1:
G2),也可以达到相同目的。
27FREQUENCY函数
27.1函数名称:
FREQUENCY
27.2主要功能:
以一列垂直数组返回某个区域中数据的频率分布。
27.3使用格式:
FREQUENCY(data_array,bins_array)
27.4参数说明:
Data_array表示用来计算频率的一组数据或单元格区域;Bins_array表示为前面数组进行分隔一列数值。
27.5应用举例:
如所示,同时选中A1至A12单元格区域,输入公式:
=FREQUENCY(A2:
A12,C4:
C7),输入完成后按下“Ctrl+Shift+Enter”组合键进行确认,即可求出B2至B31区域中,按D2至D36区域进行分隔的各段数值的出现频率数目(相当于统计各分数段人数)。
28
29
29.1特别提醒:
上述输入的是一个数组公式,输入完成后,需要通过按“Ctrl+Shift+Enter”组合键进行确认,确认后公式两端出现一对大括号({}),此大括号不能直接输入。
30IF函数
30.1函数名称:
IF
30.2主要功能:
根据对指定条件的逻辑判断的真假结果,返回相对应的内容。
30.3使用格式:
=IF(Logical,Value_if_true,Value_if_false)
30.4参数说明:
Logical代表逻辑判断表达式;Value_if_true表示当判断条件为逻辑“真(TRUE)”时的显示内容,如果忽略返回“TRUE”;Value_if_false表示当判断条件为逻辑“假(FALSE)”时的显示内容,如果忽略返回“FALSE”。
30.5应用举例:
在C29单元格中输入公式:
=IF(C26>=18,"符合要求","不符合要求"),确信以后,如果C26单元格中的数值大于或等于18,则C29单元格显示“符合要求”字样,反之显示“不符合要求”字样。
30.6特别提醒:
本文中类似“在C29单元格中输入公式”中指定的单元格,读者在使用时,并不需要受其约束,此处只是配合本文所附的实例需要而给出的相应单元格,具体请大家参考所附的实例文件。
31INDEX函数
31.1函数名称:
INDEX
31.2主要功能:
返回列表或数组中的元素值,此元素由行序号和列序号的索引值进行确定。
31.3使用格式:
INDEX(array,row_num,column_num)
31.4参数说明:
Array代表单元格区域或数组常量;Row_num表示指定的行序号(如果省略row_num,则必须有column_num);Column_num表示指定的列序号(如果省略column_num,则必须有row_num)。
31.5应用举例:
如所示,在F8单元格中输入公式:
=INDEX(A1:
D9,4,3),确认后则显示出A1至D11单元格区域中,第4行和第3列交叉处的单元格(即C4)中的内容。
31.6特别提醒:
此处的行序号参数(row_num)和列序号参数(column_num)是相对于所引用的单元格区域而言的,不是Excel工作表中的行或列序号。
32
33INT函数
33.1函数名称:
INT
33.2主要功能:
将数值向下取整为最接近的整数。
33.3使用格式:
INT(number)
33.4参数说明:
number表示需要取整的数值或包含数值的引用单元格。
33.5应用举例:
输入公式:
=INT(18.89),确认后显示出18。
33.6特别提醒:
在取整时,不进行四舍五入;如果输入的公式为=INT(-18.89),则返回结果为-19。
34ISERROR函数
34.1函数名称:
ISERROR
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 公式与函数 公式 函数