[方案]Excel电子表格函数实例教程.docx.pdf

《[方案]Excel电子表格函数实例教程.docx.pdf》由会员分享，可在线阅读，更多相关《[方案]Excel电子表格函数实例教程.docx.pdf（13页珍藏版）》请在三一文库上搜索。

1、Excel 电子表格函数实例教程 Excel 电子表格函数实例教程 1. 求和函数 SUM 语法： SUM (number 1, number2, .)=6O, 1,0)”, 由 于它是一个数组公式，输入结束后必须按住Ctrl+Shift 键回不。公式两边会口动添加上大 括号, 在编辑栏显示为“匸SUM (IF (Bl:B50=“ 女“, IF (Cl:C50=60, 1, ()”，这 是使用数 组公式必不可少的步骤。 2. 平均值函数 AVERAGE 语法： AVERAGE (numbcrl, numbcr2 参数： number 1 number2.是需要计算平均值的1? 30 个参数。

2、注意：参数可以是数字、包含数字的名称、数纟R 或引用。数纟R 或单元格引用屮的文字、 逻辑值或空口单元格将被忽略，但单元格屮的零则参与计算。如果需要将参数中的零排除 在外, 则耍使用特殊设让的公式，下面的介绍。 应川实例一：跨表计算平均值 标签名为“一班”、“二班”和“三班”的工作表存放各班学生的成绩，则它们的总平均分 计算公式为 AVERAGE ( 一班!C1:C36,三班!C1:C32,三班!C1:C45) ” 。式屮的引用输入方法 与 SUM 跨表求和时相同。 应用实例二：忽略零的平均值计算公式 假设 Al:A200 随机存放包括零在内的48 个数值，在AVERAGE 参数中去掉零引用很

3、麻烦， 这种情况可以使用公式“ -AVERAGE (IF (Al:A200v0, Al:A200,“ )”。公式输入结 束后按住 Ctrl+Shift 回车，即可对Al:A200 中的数值 ( 不包 JS零) 计算平均值。 3. 逻辑函数 IF 语法： IF (logical_test, value_if_true, value_if_false) o 参数：logical_test 是结果为 true( 真) 或 false( 假) 的数值或表达式； value_if_true 是 logical_test 为 true 吋函数的返回值，如果logical_test 为 ture并L 省略了

4、value_if_true,则返回 true。而 H. value_if_true 口 J以是一个表达式； valuc_if_false 是 logical_test 为 false 时函数的返回值。 如果logical_test 为 false 并且省略value_if_false,则返回falseo Value_if_false 也可以是一个 表达式。 应用实例一：个人收入调节税计算 假设个人收入调节税的稽征办法是：工资在1000 元以卜 -的免征调节税，工资1000 元以上 至1500元的超过部分按5%的税率征收 ，1500元以上至 2000元的超过部分按8%的税率征 收, 高于 200

5、0元的超过部分按30%的税率征收。 假如工作表的B 列存放职工姓名， C 列存放工资，选中 D 列的空白单元格输入公式“=IF (C2v= 1()0(), “ “ , IF (C2-1000) 2000, (C2- 2000) *0.3)”，回车后即可计算出C2 I 资应缴纳的收入调节税。 公式中的 IF 语句是逐次计算的， 如果第一个逻辑判断C20, SUM (A1:B1, D2:E8), ) ”, 即是当 SUM (A1:B1, D2:E8) 计算结果 不等于零时，公式所在单元格显示SUM (A1:B1, D2:E8) 的计算结果，否则显示一个空格。 上面这个问题在财务计算中依然存在，如公

6、式 u =A 1 -A6-A9 ” 有可能为零或显示负数。为了避免打印零或负数，可以设计公式“=1F (A2-A6-A9=0, “ “， IF (A2-A6-A9=60“ ) /COUNTA (C1:C42) ” 回车, 即可计算出该列成绩的及格率( 即分 数为 60及 以上的人数占总人数的百分比)。 6.最人值函数 MAX 、最小值两数MIN 语法： MAX (number 1, number2,. MIN (number 1, number2,. )0 参数: numberl, number2.是需要找出最大值 ( 最小值 )的 1 至 30 个数值、数组或引用。 注意：函数屮的参数可以是

7、数字、空口单元格、逻辑值或数字的文本形式，如果参数是不能 转换为数字的内容将导致错误。如果参数为数组或引用，则只有数组或引用中的数字参与计 算，空白单元格、逻辑值或文木则被忽略。 应用实例：查询最高分( 最低分 ) 假如 Cl： G42 存放着42 名学生的考试成绩，则选屮一个空白单元格，在编辑栏输入公式 “=MAX (C1:C42) ”，回车后即可计算出其中的最高分是多少。 如果将上述公式中的函数名改为M1N,其他不变，就可以计算出Cl： G42 区域中的最低分。 7. 条件求和函数SUMIF 语法： SUMIF (range, criteria, sum_range)o 参数： range

8、 是用于条件判断的单元格区域，criteria 是由数字、逻辑表达式等组成的判定条 件，sum.range为需耍求和的单元格、区域或引用。 应川实例：及格平均分统计 假如 A1:A36 单元格存放某班学生的考试成绩，若要计算及格学生的平均分，可以使用公式 “二 SUMIF(A1:A36, “ =60“ , A1:A36)/COUNTIF(A1:A36, “ =60 7 ) 。公式中的 “=SUMIF (A1:A36, “ =60“ , A1:A36) ” 计算及格学生的总分，式中的“A1:A36”为提供逻辑判断依据的 单元格引川，“ =60” 为判断条件，不符合条件的数据不参与求和，AI:A3

9、6 则是 逻辑判断和 求和的对象。公式中的COUNTIF (A1:A36, “ =60“) 用来统计及格学生的人数。 8.贷款偿还计算函数PMT 语法： PMT (rate, nper, pv, fv, type). 参数：如今贷款买房子或车子的人越来越多，计算某一贷款的月偿还金额是考虑贷款的重要 依据， Excel XP 提供的 PMT 函数是完成这一任务的好工貝。语法中的rate是贷款利率；叩 er 为贷款偿还期限； pv 是贷款本金； fv 为最后一次付款后剩余的贷款金额，如果省略fv,则 认为它的值为零； ( ype为() 或 1,用来指定付款时间是在月初还是月末。如果省略type,则

10、 假 设其值为零。 应川实例：购房还款金额 假如你为购房贷款I 力元，如果年利率为7%,每月末还款。采用十年还清方式时，月还款额 计算公式为“ =PMT (7%/12, 120, - 100000)” 。其结果为 ￥ -1, 161.08,就是你每月须偿还贷款 1161.08元。 9.样本的标准偏差函数STDEV 语法： STDEV (number!, numbcr2,. 参数： number 1, number2,.为对应于总体样本的1到 30 个参数，它们可以是数值、引用或 数组。 注意：STDEV 函数的参数是总体中的样木，并忽略参数中的逻辑值( tec或 false)和文本。 如 果需

11、要用全部数据计算标准偏差，则应使用STDEVP 函数。如果参数中的逻辑值和文本不 能忽略，请使用STDEVA 函数。 应用实例一：成绩离散度估计假设某班共有36名学牛 : 参加考试，随机抽取的五个分数为 Al=78 、A2=45、A3=90、A4=12 和 A5=85o 如果要估算本次考试成绩相对平均分的离散程 度，即学生的考试成绩偏离平均分的多少，可以使用公式“二STDEV (A1:A5)”。其计算 结果为 33.00757489,标准偏差的数值越人成绩越分散。 应用实例二：质量波动估计 质量控制等场合也能用到STDEV 函数，如从一批钢丝绳屮随机抽岀若干进行试验，分别测 出它们的抗拉强度。

12、根据STDEV 函数的计算结果即可判断钢丝绳的抗拉强度是否分散，如 果计算的标准偏差比较小，说明抗拉强度的一致性好，质量比较稳定。反Z 说明钢丝绳的质 量波动较大，抗拉强度不够一致。 10.排序函数 RANK 语法： RANK (number, ref, order)0 参 number是需要计算具排位的一个数字；ref 是包含一纽数字的数组或引用( 具屮的非数值 型参数将被忽略 )；order 是用来说明排序方式的数字( 如果 order 为零或省略，则以降序方 式给出结果，反Z 按升序方式 )。 应用实例：产值排序 假如 E2、E3、E4单元格存放一季度的总产值。 计算各布间产值排名的方法

13、是：在F2单元格内输入公式 “=RANK (E2, $E$2:$E$4) ”，敲回车 即可计算出铸造车间的产值排名是2。再将 F2 中的公式复制到剪贴板，选中 F3、F4单 元格 按 Ctrl+V, 就能计算出其余两个车间的产值排名3 和 1。如果 Bl 单元格屮输入的公式为“二 RANK (E2, $E$2:$E$4, 1) ”,则计算出的序数按升序方式排列, 即 2、1 和 3。 需要注意的是：和同数值用RANK 函数计算得到的序数( 名次) 相同，但会导致后续数字的 序数空缺。假如上例屮F2 单元格存放的数值与F3 相同，则按本法计算出的排名分别是3、 3 和 1 ( 降序时 ) 。即

14、176.7出现两次吋，铸造和维修车间的产值排名均为3,后续金工车间的 排名就是 1 (没冇 2)。 11.四舍五入函数 语法： ROUND (number, num_digits)o 参数：number是盂要四舍五入的数字； num_digits 为指定的位数， number将按此位数进行四 舍五入。 注意：如果 num_digits 大于 0,则四舍五入到指定的小数位；如果num_digits 等于 0,则四舍 五入到最接近的整数；如果num_digits 小于 0,则在小数点左侧按指定位数四舍五入。 应用实例：消除计算误差 假设 Excel I 作表中有 D2=356.68、E2=128.

15、12,需要将 D2 与 E2之和乘以 0.1,将计算结果四 舍五入取整数，再将这个结果乘以1.36 ( 取两位小数 ) 得到最终结果。 一般用户的做法是选屮某个单元格(如 F2),使用“单元格”命令将它的小数位数设为零， 然后在其中输入公式“F2 二(D1+E1) *0.1 ” 。再将 G2 单元格的小数位数设成两位，最后把 F2U36 的结果存入其中就可以了。从表面上看，上述方法没有什么问题。因为(D1+E1) *0.1 在 F2单元格显示 48 ( 注意：是显示48),如果 F2 单元格的小数位数为零，( D1+E1) *0 经四 舍五入后的结果就是48。接下去却岀了问题，因为F2*1.3

16、6 的计算结果是65.90, 根本不是 48*1.36 的正确结來 65.28,其中 65.90是(D2+E2) *0.1 未经四舍五入直接乘以1.36的结果。 以上计算结果说明：“单元格格式”、“数字”选项卡设置的“小数位数”，只能将单元 格数值的显示结果进行四舍五入，并不能对所存放的数值进行四舍五入。换句话说，单元 格数值的显示结果与实际存放结果并不完全一致，如果不注意这个问题，计算工资等敏感 数据就会出现错误。例如在上例中，F2 单元格内的数值虽然显示为48,但实际存放的却是 48.45,自 然得出了48.45*1.36=65.90 的结果 ( 按计算耍求应为65.28)。 要解决这个问

17、题并不难，你只须在G2 单元格内输入公式“ = (ROUND (D2+E2) *0.1, () *1.36 ”，就可以按要求计算出正确结果65.28o式屮的 ROUND 函数按指定位数对 “ ( D2+E2) *0.1 ”进行四舍五入 , 函数中的参数0将“ ( D2+E2) *0.1”四舍五入到最接近的整数。 12.条件计数两数COUNTIF 语法： COUNTIF (range, criteria)o 参 range为需要统计的符合条件的单元格区域；criteria 为参与计算的单元格条件，其形式 可以为数字、表达式或文本( 如 36、“ 160“ 和“男“等)。条件屮的数字可以直接写入,

18、 表达 式和文本必须加引号。 应用实例：男女职工人数统计 假设 A1:A58 区域内存放着员工的性别, 则公式 “=COUNTIF (A1:A58,“女“) ”统计其中的女 职工数量，“二COUNTIF(A1:A58,“ 男“) ”统计其中的男职工数量。 COUNTIF 函数还可以统计优秀或及格成绩的数量，假如Cl： G42 存放着 42 名学生的考试 成绩，则公式 COUNTIF (C2 ： G2, “ =85“ )” 可以计算出其中高于等于85分的成绩数目。 如将公式改为“ =COUNTIF (C2 ： G2, “=60“)”，则可以计算出及格分数的个数。 13.百分排位预测函数PERCE

19、NTILE 语法： PERCENTILE (array, k) 。 参数： array为定义和対位置的数值数组或数值区域，k 为数组中需要得到一其百分排位的 值。 注意：如果 array所在单元格为空白或数据个数超过8191,则返回 #NUM! 错误。如果 kvO 或 kl,则返回 #NUM! 错误。如果 k 不是 1/ (n-1)的倍数，该函数使用插值法确定其百分排位。 应用实例：利润排行预测 假设 Cl:C60 区域存放着几I?个公司的利润总额。如果你想知道某公司的利润达到多少， 才 能进入排名前10%的行列 , 可以使用公式 U=PERCENTILE (Cl:C60, 0.9) ” 。如

20、果计算结果 为 9867 Jj元，说明利润排名要想进入前10%,则该公司的利润至少应当达到9867万元。 14.数值探测函数ISNUMBER 语法： ISNUMBER (value )o 参数：如果 value 为数值时函数返冋ture (真) ，否则返冋 false ( 假)。 应用实例一：无错i 吴除法公式如果工作表为C1设计了公式 “=A1/B1” ，一旦 Bl 单元格没 有输入除数，就会在C1 屮显示错谋信息“ #DIV/()!”。这不仅看起来很不美观，一旦作为 报表打印还可能引起误会。为避免上面提到的问题出现，可将C1 单元格中的公式设计成 “=IF (ISNUMBER (Bl),

21、Al/Bl,“式中的 ISNUMBER 函数对 Bl 单元格进行探测 , 当 B1 被填入数 值时返回 true (真) ，反之返回 false (假) 。为真时 IF 函数执行 A1/B1 的运算，为假时在 C1 单元格中填入空格。 应用实例二：消除无意义的零 使用 SUM 函数计算工资总额时，若引用的单元格区域没有数据，Excel 仍然会计算出一个 结果“0”。这样打印出来的报表不符合财务规定，为此可将公式设计成“=IF (ISNUMBER (A1:B1), SUM (A1:B1),“ 式I 1 ISNUMBER 函数测试 SUM 函数所引用的单元格区域是否全部为空，当不为空时返冋 tru

22、e ( 真) ，反之返回 fales ( 假) 。为真时 IF 函数执行 SUM (A1:BI), 为假时在存放计算结果 的 F1单元格屮填入空格，这样即可避免在F1 单元格中出现 “0”。 15.零存整取收益函数PV 语法： PV (rate, nper, pmt, fv, type)o 参数：nite 为存款利率； 叩 er为总的存款时I可，对于三年期零存整取存款來说共有3*12=36 个力；pmt 为每月存款金额，如果忽略pmt 则公式必须包含参数fv； fv 为最后一次存款后希 望得到的现金总额，如果省略了fv 则公式中必须包含pmt 参数； type 为数字 0 或 1,它指 定 存

23、款时间是月初还是月末。 应用实例：零存整取收益函数PV 假如你每刀初向银行存入现金500 元，如果年利 2.15% ( 按刀计息，即刀息2.15%/12)o 如 果 你想知道 5 年后的存款总额是多少，可以使用公式“二FV (2.15%/12, 60, -500, 0, 1) ” 计算, 其结果为 ￥ 31, 698.67o 式中的 2.15%/12为月息；60 为总的付款时间， 在按月储蓄的情况下为储蓄月份的总和；-500 为每月支出的储蓄金额 (表示支出 )；0 表示储蓄开始时账户上的金额，如果账户上没有一 分钱，则为0 否则应该输入基础金额；1 表示存款时间是月初还是月末，1 为月初 0

24、 或忽略 为月末。 16.内部平均值函数TRIMMEAN 语法： TRIMMEAN (array, percent)o 参 array为需要去掉若T?数据然后求平均值的数组或数据区域；percent为计算时需要除去 的数据的比例，如果percent = 0.2,说明在 20 个数据中除去4 个，即头部除去2 个尾部除去 2 个。如果 percents. 1,则 30 个数据点的 10%等于 3,函数 TRIMMEAN将在数据首尾各去掉 一个数据。 应用实例：评比打分统计 歌唱比赛采用打分的方法进行评价，为了防止个别人的极端行为，一般计算平均分数要去掉 若干最高分和最低分。假如Bl:B10 区域

25、存放某位歌手的比赛得分，则去掉一个最高分和一 个最低分后的平均分计算公式为“二TRIMMEAN (Bl:B10, 0.2) ”。公式中的 0.2表示 10 个 数 据中去掉 2 个(10X0.2),即一个最高分和一个最低分。 17.口期年份函数YEAR 语法： YEAR (serial_number)o 参数： serial_number为待计算年份的日期。 应川实例：“虚工龄”计算所谓“虚工龄”就是从参加工作算起，每过一年就增加一年工 龄，利用 YEAR 函数计算工龄的公式是 “=YEAR (Al) -YEAR (Bl) ” 。公式中的 Al 和 Bl 分别 存放工龄的起止日期, YEAR

26、(Al) 和 YEAR (Bl) 分別计算出两个Fl 期对应的年份，相减后得 出虚工龄。 18. 起止天数函数DAYS360 语法： DAYS360 (start_date, end_date, method )o 参数： start_datc和 cnd_datc是用于计算期间天数的起止口期，可以使用带引号的文本串( 如 “1998/01/30”)、系列数和嵌套函数的结果。如果start.date在 end_date之后, 则 DAYS360 将 返回一个负数。 19. 取整函数 TRUNC 语法： TRUNC (number, num_digits)0 参数： number是需要截去小数部分

27、的数字，num_digits 则指定保留到儿位小数。 应用实例：“实工龄”计算 实际工作满一年算一年的工龄称为“实工龄”, 如 1998年 6 月 1 日至 2001年 12 月 31日的 工龄为3 年。计算“实工龄”的公式是“二TRUNC(DAYS36()(“ 1998/6/ “，” 2001/12/31“) /360, 0)”,公式中的 DAYS360 (“ 1998/6/1 w “ 2001/12/31“) 计算两个日期相差的天 数, 除以 360 后算出日期相差的年份(小数 )。最后 TRUNC 函数将 (DAYS360 (Al, Bl) /360 的计算结果截去小数部分，从而得出“实

28、工龄”。如果计算结果需要保留一位小数，只须 将 公式修改为“二TRUNC (DAYS360 (” 1998/6 门“, “2001/12/31“ ) /360, 1)” 即可。 如果你要计算参加工作到系统当前吋间的实工龄，可以将公式修改为“ =TRUNC (DAYS360 (“1998/6/L , NOW () /360, 0)”。其中 NOW () 函数返回当前的系统日期和时 间。 20.字符提取函数MID 语法： MID (text, start_num, num_chars)或 MIDB (text, start_num, num_bytes)o 参数： text 是含冇要待提取字符的文

29、木，start_num是要提取的第一个字符的位置(其中第一 个字符的 start_num为 1以此类推 ) ，num_chars指定 MID 从文本中提取的字符个数， Num_bytes 扌旨定 M1DB 从文本中提収的字符个数(按字节计算 ) 。 应用实例：姓氏提取和统计假如工作表 C 列存放着职工的姓名，如果想知道某一姓氏的职 工人数，可以采用以下方法。首先使用公式“ =MID (Cl, 1, 1) ”或 “=MIDB (Cl, 1, 2),从 Cl 中提取出职工的姓氏, 采用拖动或复制的方法即可提取其他单元格屮的职工姓氏。然后使 用公式“二 COUNTIF (D1:D58,“张“) ”统

30、计出姓张的职工数量，随后改变公式小的姓氏就可以统计出其他姓氏 职工的数量。 21、逻辑运算符 所谓逻辑运算符也就是与、或、思非、真、假等等条件判断符号。这里介绍六条。 LAND 用途：所有参数的逻辑值为真吋返回TRUE(真); 只要有一个参数的逻辑值为假，则返回 FALS 玖假 )。 语法： AND(logicall, Iogical2, )。 参数: Logical】, logical, ? 为待检验的 1? 3()个逻辑表达式， 它们的结论或为TRUE(真) 或为 FALSE( 假) 。参数必须是逻辑值或者包含逻辑值的数组或引用，如果数组或引用内含冇 文字或空白单元格， 则忽略它的值。 如

31、果指定的单元格区域内包括非逻辑值，AND 将返冋 错 误fi#value!o 实例：如果 Al=2、A=6,那么公式“二AND(A1A2) ” 返回 FALSE。如果 B4=104,那么 公 式 “=IF(AND(1 2.FALSE 用途：返冋逻辑值FALSEo 语法： FALSE0 参数：该两数不需要参数 实例：如果在 A1 单元格内输入公式“ =FALSE()”，回车后即可返回FALSEo 若在单元 格或公式中输入文字FALSE, Excel 会 H 动将它解释成逻辑值FALSE。 3.IF 用途：执行逻辑判断，它町以根据逻辑表达式的真假，返回不同的结果，从而执行数值 或公式的条件检测任务

32、。 语 V 去：IF(logical_tcst, valuc_ifLtruc, value f_falsc)。 参数： Logical_test 计算结果为 TRUE 或 FALSE 的任何数值或表达式;value_if_true是 Logical_test 为 TRUE 时函数的返回值，如果logical_test 为 TRUE 并 J1 省略了 value_if_(nie, 则返冋TRUEo 而且 value_if_true 可以是一个表达式 ;value_if_false 是 Logical_test 为 FALSE 时函数的返回值。如果logical_test 为 FALSE 并且省略

33、 value f_false,则返冋 FALSE。 value_if_false 也可以是一个表达式。 实例 : 公式“ =IF(C2=85, “A”，IF(C2=70, “B”，IF(C2=60, ”C ”，IF(C2=85 成立，则 D2 单元格被赋值 “A”; 如果笫一个逻辑判断表达式C2=85 不成立，则计算第二个IF 语句“IF(C2=70 ” ；以 此类推直至计算结束，该函数广泛用于需要进行逻辑判断的场合。 4.N0T 用途 : 求出一个逻辑值或逻辑表达式的相反值。如果您要确保一个逻辑值等于其相反 值, 就应该使用 NOT 函数。 语法： NOT(logical) 参数： Logi

34、cal 是一个可以得 !H TRUE 或 FALSE 结论的逻辑值或逻辑表达式。如果逻 辑 值或表达式的结果为FALSE,则 NOT 函数返回 TRUE;如果逻辑值或表达式的结果为 TRUE, 那么 NOT 函数返回的结果为FALSEo 实例：如果 Al=6 A2=8, 那么公式“二NOT(A1 5.0R 用途：所有参数屮的任意一个逻辑值为真时即返回TRUE(真) 。 语法 : OR(logicall, logical2, .) 参数： Logical 1, logical, . 是需耍进行检验的1 至 30 个逻辑表达式，具结论分别为 TRUE 或 FALSEo 如呆数组或引用的参数包含文本

35、、数字或空口单元格，它们将被忽略。 如果指定的区域中不包含逻辑值，OR 函数将返回错误 #valuc!o 实例：如果Al=6、A2=8,则公式“=OR(A1+A2A2, A1=A2) ” 返回 TRUE;而公式 “=OR(A1A2, A1=A2) ” 返回 FALSEo 6.TRUE 用途：返回逻辑值TRUE。 语法： TRUE() 参数：该函数不需要参数 实例：如果在A1 单元格内输入公式“二TRUE()”，冋车示即可返冋TRUEo 若在单元 格 或公式小输入文字TRUE, Excel 会白动将它解释成逻辑值TRUEo 函数 TRUE 主要用于 与 其它电子表格程序兼容。 原文来源：世纪家园 原文地址 : http:/ 1296 原文來源：世纪家园 专业好文档精心整理欢迎下载