教你如何使用Excel函数

1、第三章 函 数n在Excel中，函数就是预定义的内置公式，它使用参数并按照特定的顺序进行计算。n函数的参数是函数进行计算所必须的初始值。用户把参数传递给函数，函数按特定的指令对参数进行计算，把计算的结果返回给用户。n函数的参数可以是数字、文本、逻辑值或者单元格的引用，也可以是常量公式或其他公式。n无论什么类型的参数都必须能产生有效数值。3. 1 函数的分类与函数调用Excel函数的分类n数据库工作表函数：分析数据清单中的 数值是否符合特定条件n日期与时间函数：在公式中分析和处理 日期值和时间值n工程函数：用于工程分析n信息函数：确定存储在单元格中数据的类型n财务函数：进行一般的财务计算3. 1

2、 函数的分类与函数调用n逻辑函数：进行逻辑判断或进行复合检验n统计函数：对数据区域进行统计分析n查找和引用函数：在数据清单中查找特定数据或者查找一个单元格的引用n文本函数：在公式中处理字符串n数学和三角函数：进行数学计算3. 1 函数的分类与函数调用Excel中函数的调用n函数的调用格式 =函数名（自变量）或 =函数名（参数）n有两种方法在公式中键入函数直接键入使用粘贴函数：“插入”-“函数”，或单击工具栏的“fx”(粘贴函数）按钮，弹出“粘贴函数”对话框。3. 1 函数的分类与函数调用n一个函数可以有0到30个自变量（参数） ，自变量之间用逗号隔开，最后不用逗号。n可以通过联机帮助系统学习函

3、数的使用3.2 常用函数的含义及用法数学与三角函数绝对值函数-ABS（数值型）功能：ABS函数返回指定数值的绝对值例：ABS（2）等于2，ABS（-2）等于2取整函数-INT（数值型）功能： INT函数返回实数向下取整后的整 数值。例：INT（7.6)等于7，INT（-7.6)等于 -83.2 .1 数学与三角函数求余函数-MOD(数值型，数值型）功能：MOD函数返回两数相除的余数，第一个自变量为被除数，第二个自变量为除数。例：MOD（15，4）等于3 MOD（-13，-4）等于 1 MOD （-13 ，4）等于 3 MOD（13，-4）等于-3 MOD （12.8，4.3）等于4.23.2

4、.1 数学与三角函数随机函数-RAND( )功能：RAND( )是大于0且小于1的随机数。如果要使用随机整数可以将取整函数和随机函数连用。例：INT（RAND（）*11）返回结果是0到10之间的随机整数。四舍五入函数-ROUND（数值，数位）功能： ROUND函数根据给定数位将指定数值四舍五入。负值表示小数点前对应的位数，小于这些位数的值四舍五入，而该位取0。RAND( ):返回大于 0 小于 1 的均匀分布随机数，每次计算工作表时都将返回一个新的数值。如果要生成 a，b 之间的随机实数，请使用 RAND()*(b-a)+a 如果要使用函数 RAND 生成一随机数，并且使之不随单元格计算而改变

5、，可以在编辑栏中输入=RAND()，保持编辑状态，然后按 F9 键，将公式永久性地改为随机数。例：ROUND（837.1649, 2）等于837.16 ROUND（837.1649, -2）等于800 ROUND（837.1649,0）等于837符号函数-SIGN（数值）功能： SIGN函数返回自变量指定的数值的符号，自变量为正数值时返回值是1，自变量为负数值时返回值是-1，自变量为0时函数返回值是0。 例：SIGN（-9）返回值是-1。 3.2 .1 数学与三角函数3.2 .1 数学与三角函数求和函数-SUM（第1参量，第2参量，第30参量）功能：SUM函数用于计算自变量中所有数值型数据项之

6、和。例：SUM（10，20）等于30 SUM(A1:B10) 求A1:B10单元格区域之和 3.2 .1 数学与三角函数条件求和函数-SUMIF（范围，条件，数据区域）功能： SUMIF函数对符合指定条件的单元格求和范围：用于条件判断的单元格区域条件：确定哪些单元格符合相加的条件，其形式可以是数字、表达式或文本。数据区域：需要求和的实际单元格区域。3.2 .1 数学与三角函数n例：设A1：A4中的数据是10，20，30，40，而B1：B4中的数据是100，200，300，400，那么 SUMIF(A1 : A4，“15”, B1 : B4)等于900因为A2，A3，A4中的数据满足条件，所以相

7、应地对B2，B3，B4进行求和。nExcel中提供6个逻辑函数：与、或、非（反）、真、假和条件判断函数n逻辑函数真和假是无参数的，与、或、非的参数都是逻辑值，条件判断函数的第一个参数是逻辑值。几个简单的逻辑函数n真-TRUE()，不需要参数，返回的结果为逻辑值TRUE；n假-FALSE()，不需要参数，返回的结果为逻辑值FALSE3.2.2 逻辑函数3.2.2 逻辑函数n与-AND( 逻辑值1，逻辑值2，.),对参数进行逻辑与，所有参数逻辑值都是真，返回真，如果有一个参数为假，返回假。n或-OR(逻辑值1，逻辑值2，.),对参数进行逻辑或，任意一个参数逻辑值是真，返回真，所有参数逻辑值都为假，

8、返回假；n非-NOT(逻辑值），对参数的逻辑值求反，参数的逻辑值为TRUE时，返回逻辑值FALSE，参数的逻辑值为FALSE时，返回逻辑值TRUE。3.2.2 逻辑函数n建立逻辑函数真值表的步骤：打开Excel应用程序，建立一个新工作簿选定工作表1为当前工作表，在A3：E3输入项目名称输入逻辑值1和逻辑值2，.3.2.2 逻辑函数条件判断函数-IF（判断的条件，条件真时所返回的值，条件假时所返回的值）功能：IF函数对第一个自变量给出的条件进行判断，如果条件为真则返回第二个自变量的值，如果条件为假则返回第三个自变量的值。后两个自变量的值可以是数值型、文本型等，若省略则返回TRUE或FALSE。例

9、：IF（C5=100，275，400） 如果C5中的数值等于100，则返回结果是275，否则函数值是400。3.2.2 逻辑函数n返回值也可以使用字符串，如：=IF（B21000，“credit,approved”, “Credit denied”)nIF函数可以嵌套，最多可以使用七层。例：计算个人调节稅。规定工资在800元以上开始计征个人所得税，对于超过部分500元以内扣5%，在5002000元以内扣10%减25元，20005000元扣15%减125元，500020000元扣20%减375元，20000元扣25%。3.2.3 统计函数nExcel 提供了许多统计函数，对大量的数据进行统计运算

10、，可以直接使用常量，也可以使用单元格或单元格区域。求平均值函数-AVERAGE（参数1，参数2，.， 参数30）功能：求自变量指定的一系列数值的平均值。n参数可以是数字，或者是涉及数字的名称、数组或引用。3.2.3 统计函数n如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。例：=AVERAGE（6，12，15，18）的值是12.75。 =AVERAGE（B5：B15）则是求从B5单元到B10单元中所有数值的平均值。 3.2.3 统计函数n例：在单元格中输入公式：“=AVERAGE(6,12,15, “ abc ”)”，确认后结果为“#VALU

11、E！”n例：在单元格中输入公式：“=AVERAGE(6,12,15,abc)”，确认后结果为“#NAME！”n在单元格B1B4中分别输入6，12，15，abc，输入公式“=AVERAGE（B1：B4）”，确认后结果为11。3.2.3 统计函数计数函数-COUNT（参数1，参数2，)n功能：COUNT函数的返回值是自变量数据中数值型数据的个数。参数可以是单元格或区域引用、名字等，含有的数据类型任意，但只有数值型数据才被计数。n如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。3.2.3 统计函数求最大值函数-MAX(参数1,参数2,.)

12、求最小值函数-MIN(参数1,参数2,.)功能：返回自变量中所有数值型数据的最大值或最小值。n参数可以是数字、空白单元格、逻辑值或表示数值的文字串。如果参数中有错误值或无法转换成数值的文字时，将引起错误。n如果参数是数组或引用，则函数 仅使用其中的数字、数组或引用中的空白单元格，逻辑值、文字或错误值将忽略。3.2.3 统计函数n例：=MIN（120，180，900）的值120。 =MAX（B5，B15）返回工作表中从B5到B15的单元范围内数值型数据的最大值。但自变量所指的单元格内的文本型数据不参加比较。标准偏差函数-STDEV(参数,参数2,. )功能：返回自变量表列中数据的标准偏差。平均值

13、(Xi )/ N， S=(Xi M)2)/ (N-1)Ni=1Ni=13.2.3 统计函数统计满足条件的单元格个数函数-COUNTIF(范围，条件）功能：在指定范围内，统计满足第二个自变量给定的条件的单元格个数。例：统计学生成绩表的平均成绩，使用COUNTIF函数统计优秀、良好、及格和不及格的人数。3.2.3 统计函数频率函数-FREQUENCY（数据数组，数据接收区间）功能：函数的第一个自变量为数据数组，是一个数组或对一组数值的引用，用来计算频率，如果数据数组中不含有数值，函数FREQUENCY返回零数组。第二个自变量为数据接收区间，为一组数据或对数组区域的引用，设定对第一个自变量进行频率统

14、计计算的分段点。 如果数据接收区间中不包含任何数值，函数FREQUENCY返回数据数组里全体元素的数目。3.2.3 统计函数n操作说明： 首先选定用于存放频率分布结果的相邻单元格区域，然后以数组公式的形式输入函数 FREQUENCY。n函数 FREQUENCY 将忽略空白单元格和文本值。n对于返回结果为数组的公式，必须以数组公式的形式输入。（Ctrl+Shift+Enter)3.2.3 统计函数n注意： 返回的数组中的元素个数比 bins_array（数组）中的元素个数多 1。返回的数组中所多出来的元素表示超出最高间隔的数值个数。例如，如果要计算输入到三个单元格中的三个数值区间（间隔），请一定

15、在四个单元格中输入 FREQUENCY 函数计算的结果。多出来的单元格将返回 data_array 中大于第三个间隔值的数值个数。3.2.3 统计函数复制学生成绩表到一个新的工作表中选定单元区域I4：I7为当前活动单元区域，输入接收区间，即将平均成绩进行分组的区间值59,74,84,100。选定单元区域J4：J8为当前活动单元区域，输入FREQUENCY函数，弹出下拉菜单：3.2.4 文本函数字符串长度函数-LEN（字符串）功能：LEN函数返回自变量的字符串的长度，即字符串所含字符的个数。字符指英文、数字、符号、汉字等，每个汉字是一个字符。空格也计入字符串的长度。例：LEN（ “english

16、 book”)的计算结果是12LEN（“”）的计算结果是0LEN（“北京”）的计算结果是23.2.4 文本函数n字符串长度函数-LENB（字符串）n功能：LENB函数返回自变量的字符串的长度，即字符串所含字节数。一个半角的英文、数字、符号等占1字节，一个汉字占2个字节。n例：LENB(“北京“）的结果为4。3.2.4 文本函数取子字符串函数-MID（正文，起始，字符个数）功能：MID函数从字符串中抽取子字符串。例：=MID（“first second third” , 7 ,6)返回字符“second”。数值转文本函数-TEXT(数值，格式）功能：TEXT函数将第一个自变量指定的一个数值，变成

17、第二个自变量指定格式的字符串。3.2.4 文本函数n例：=TEXT（12345.67, “$#,#0,00”) 返回结果是$12,345.67。 =TEXT（12.5*5 , “0.00”)的返回结果是62.50。 =TEXT（.63680556,“HH:MM:SS”) 的返回结果是15:17:00 。 =TEXT（36150 ，“mm-dd-yyyy”)返回结果是12-21-1998。3.2.4 文本函数文本转数值函数-VALUE(“字符串”）功能：VALUE函数将字符串转换成数值。字符串必须由数字、日期或时间组成。例：=VALUE（“1234.567”)返回值1234.567 =VALUE

18、（“2002-09-25”）返回值是37524。 =VALUE（“15：17”）返回值是0.63680556。3.2.5 查找和引用函数按列查找函数-VLOOKUP（查找值，数据区域，返回的列标，匹配类型）功能：VLOOKUP函数在第二个自变量给定的数据区域（table_array）的最左列中查找第一个自变量给定的值（lookup_value)，然后将第三个自变量指定列（col_index_num）值返回到单元中。3.2.5 查找和引用函数n自变量1是查找值，可以是常量数值，文字串或单元格引用；n自变量2数据区域是查找数据的信息表，一般是一个区域；n自变量3指定匹配值从中返回的列标。为1时，返

19、回数据区域中第一列的对应值；为2时，返回数据区域中第二列的对应值。以此类推。n自变量4是匹配类型，是一个逻辑值，指定是精确匹配还是近似匹配。3.2.5 查找和引用函数n若为TRUE或省略则返回近似值，即如果没有找到精确匹配值，则返回小于查找值的最大值；n若为FALSE，则查找精确匹配值，如果没有找到返回错误值“#N/A！”n注意：如果匹配类型为TRUE，则数据区域中的第一列必须升序排序。3.2.5 查找和引用函数n例：在已建立的商品价格数据库中，使用VLOOKUP函数制作报价表。建立商品价格数据库，将工作表1标签改成商品价格表。建立报价表，输入公式=VLOOKUP(B4,商品价格表！$A$4:

20、$B$13,2,0)在B4列输入数据库中的商品名，确认后，在编号栏和单价栏自动出现该商品的相应值。3.2.6 财务函数n每期付款金额函数-PMT（比率，日期，现值，预期值，类型）PMT函数的自变量的含义：比率为每期的利率；日期为年金付款期间的总次数；现值为未来各期年金现在价值的总和；预期值为最后一次付款完成后所能获得的现金总和，若忽略，其默认值为0；类型值只能为0或1（省略为0），表示何时付款到期，“0”表示期末，“1”表示期初。功能：PMT函数计算在规定的支付期内对贷款分期摊还的支付。3.2.6 财务函数n例:买一台分期付款的汽车,总价值200,000元,年利率为9%,三年付清,月付多少钱?

21、 使用PMT函数:月息率为9%/12=0.75% =PMT(0.75%,36,200000), 返回值为-6359.95。n贷款的偿还采用等额均还方式，本利计算公式如下： R=PI（1+I）n(1+I)n - 1每月还款额借款额I-借款月利率n-按月计算的贷 款期限3.2.7 数据库函数nExcel提供与统计函数几乎相同的数据库函数。不同之处在于数据库函数只能在一定判据下选出的数据库记录中使用这些函数。在使用数据库函数之前，必须指定数据库判据所在的范围。n数据库的函数名一般在函数名前加“D”。函数的作用域需根据数据库函数的自变量来确定。3.2.7 数据库函数n数据库函数使用的自变量：第一个自变

22、量是“数据库”自变量，标识数据库范围；第二个自变量是“区域名”(字段名）或“区域指针值”，提供函数作用的区域名称或区域指针。区域名称是用双引号括起来的列标题；区域指针值是列的号码；第三个自变量是“判据” ，标识判据所在区域的参数。3.2.7 数据库函数n数据库平均值函数- DAVERAGE(数据库，区域名，判据）或DAVERAGE(数据库，区域指针值，判据)功能：DAVERAGE从符合判据的数据库范围的记录中取得区域名指定的区域中的一系列数据，并返回这些数据的平均值。n例：数据库范围为$A$10:$G$28，判据所在的范围是从A5单元到G6单元的内容来确定，如果从A5单元到G6单元的内容如下：

23、 组名 数量 单价 种类 总价 说明 日期 接插件 表示判据的标准是在数据库区域中所有含“接插件”的记录都包含在内。在A7单元输入公式=DAVERAGE($A$10:$G$28, “数量”，$A$5:$G$6)返回结果481.29，是在绝对区域$A$10:$G$28中带有“接插件”记录的“数量”区域的数量的平均值。3.2.7 数据库函数nDSUM(数据库，区域名，判据） DSUM(数据库，区域指针值，标准）功能：DSUM函数的返回值是符合判据标准的数据库的记录中指定区域的所有数据的累计。在A8单元输入公式 =DSUM($A$10:$G$28,“总价”,$A$5:$G$6)返回值是16240，表

24、示各小组支出的所有“接插件”的总价值。3.2.7 数据库函数n数据库计数函数DCOUNT（数据库，区域名，判据）或DCOUNT（数据库，区域指针值，判据）功能：DCOUNT函数的返回值是符合判据标准的数据库记录中第二个自变量“区域名”指定的区域中所含的数值量的记录数。3.2.7 数据库函数n例：如果从A5单元到G6单元的内容如下： 组名 数量 单价 种类 总价 说明 日期第一组指定的判据是组名为第一组的所有记录，在A7单元输入公式如下： =DCOUNT($A$10:$G$28,数量,$A$5:$G$6）返回值是5，表示在该数据库中有5个符合判据几组名为第一组的记录，并且在“数量”区域中是数值3

25、.2.7 数据库函数n数据库最大值函数DMAX（数据库，区域名，判据）或DMAX（数据库，区域指针值，判据）n数据库最小值函数DMIN（数据库，区域名，判据）或DMIN（数据库，区域指针值，判据）功能：DMAX和DMIN函数分别提供在符合判据标准的数据库的记录中指定区域中数值最大值和最小值。3.2.7 数据库函数n在上例中，如果数据库范围不变，判据所在的范围仍是从A5到G6的单元的内容来确定：组名 数量 单价 种类 总价 说明 日期 组件在D7单元输入公式=DMAX($A$10:$g$28,“单价”，$a$5:$g$6)返回的结果是6.27，表示“组件”类器件的最高单价为6.27。在C8单元输入公式=DMIN($A$10:$g$28,“单价”，$a$5:$g$6)返回的结果是1.27，表示“组件”类器件的最低单价为1.27。补充：数学函数MDETERM返回数组的矩阵行列式值 格式： MDETERM（数组/array)）n 数组可以为单元格范围，例如A1：C3n也可以直接输入矩阵数组，用 括住，如1，2，3，4，5，6，7，8，9Array 是一个行列数目相等的数值数组。 Array 可以是单元格区域，例如 A1:C