第 4 章 函数与公式

1. 第4章 函数与公式 1

2. 提 纲 • 4.1 Excel实例介绍 • 4.2 Excel中数据的输入 • 4.3 Excel中函数与公式 • 4.4 Excel中数组的使用 • 4.5 Excel的函数介绍 2

3. 4.1 Excel实例介绍 • 实例背景： • 张某在“淘宝网”开了一家网上商店，主要销售手机、相机、MP3、MP4等数码产品 。随着店铺宣传的开展，小张的网店生意越来越红火，往来账务也随之增加，导致了小张越来越苦恼该如何管理好所有店铺中的信息数据，并对有效的数据进行分析。 • 如何使用Excel对他的商品销售记录进行数据分析？如何有效记录每天卖出的商品？如何计算每月获得的销售利润？如何得知什么货销售量好，容易赚钱？…… 3

4. 4.1.1 Excel的建立 • 进货清单 — 主要记录每次店铺进货的商品相关信息 • 销售清单 —主要记录每次销售的记录 • 库存清单— 主要记录现库存中的存货情况 • 销售统计—用于统计一个阶段的销售情况 • 分类统计报表（图）—对各个品牌、各种商品类别进行详细的统计，用户可以根据具体的时间品牌或类别进行条件性查询。 • 商品资料—用于存放店铺中所销售商品的基本信息资料 • 客户资料—用于存储客户的信息资料 • 其他资料设置—用于存储其他的相关信息资料 • 相关财务表（贷款偿还表，项目投资表、固定资产折旧表） 4

5. 4.1.2 Excel中数据的管理与分析 • 数据输入 • 函数与公式 • 筛选与排序 • 分类汇总 • 数据透视表（图） 5

6. 4.2 Excel中的数据输入 • 4.2.1 自定义下拉列表输入 • 4.2.2 自定义序列与填充柄 • 4.2.3 条件格式 • 4.2.4 数据输入技巧 • 4.2.5 数据的舍入方法 6

7. 4.2.1 自定义下拉列表输入 • 作用： • 使用自定义下拉列表的方式进行数据的输入，可以提高数据输入的速度和准确性。 • 建立方法：使用 “数据有效性” 命令来完成 • 普通方法设置：直接键入列表选项 • 使用名称设置：使用名称建立列表选项 7

8. 4.2.2 自定义序列与填充柄 • 自定义序列：一组数据，可按重复方式填充列。 • 创建自定义序列有以下两种方式： • 利用工作表中现有的数据项 • 采用临时输入的方式 • 自定义序列可以包含文字或带数字的文本 • 注意：如果是包含数字的自定义序列，则需要进行格式的设置，之后才能在单元格中输入序列项，选择列表并导入列表。 • 创建好自定义序列之后，采用填充柄进行填充能达到快速、有效的结果。 8

9. 4.2.3 条件格式 • 作用：通过设置数据条件格式，可以让单元格中的数据满足指定条件时就以特殊的标记（如：以红色、数据条、图标等）显示出来。 • 关于条件格式的设置主要通过“条件格式”对话框 9

10. 4.2.4 数据输入技巧 • 1．特殊数据输入 • 输入分数 • 输入负数 • 输入文本类型的数字 • 输入特殊字符 • 2．快速输入大写中文数字 • 3．自动超链接的处理 • 取消自动超链接 • 关闭自动超链接 • 取消多个的超链接 10

11. 4.2.5 数据的舍入方法 • 1．舍入到最接近的倍数 • 2．货币值舍入 • 3．使用INT和TRUNC函数 • 4．舍入为n位有效数字 • 5．时间值舍入 11

12. 4.3 Excel中函数与公式 • 4.3.1 公式的概述 • 4.3.2 单元格的引用 • 4.3.3 创建名称及其使用 • 4.3.4 SUM函数的应用 • 4.3.5 AVERAGE函数的应用 • 4.3.6 IF函数的引用 12

13. 4.3.1 公式的概述 • 公式就是对工作表中的数值进行计算的式子 • 公式由操作符和运算符两个基本部分组成。 • 操作符可以是常量、名称。数组、单元格引用和函数等。 • 运算符用于连接公式中的操作符，是工作表处理数据的指令。 13

14. 4.3.1 公式的概述 • 公式元素 运算符、单元格引用、值或字符串、函数其参数、括号 • 运算符 算术运算符 、逻辑运算符 、文本运算符 、引用运算符 • 运算符的优先级 14

15. 4.3.2 单元格的引用 • 相对引用 • 总是以当前单元格位置为基准，在复制公式时，当前单元格改变了，在单元格中引入的地址也随之发生变化。 • 相对地址引用的表示是，直接写列字母和行号，如A1，D8等。 • 绝对引用 • 在复制公式时，不想改变公式中的某些数据，即所引用的单元格地址在工作表中的位置固定不变，它的位置与包含公式的单元格无关 • 绝对地址引用的表示是，在相应的单元格地址的列字母和行号前加“$”符号 15

16. 4.3.2 单元格的引用 • 混合引用 • 公式中参数的行采用相对引用、列采用绝对引用，或者列采用相对引用、行采用绝对引用，如$A1、A$1 。 • 三维引用 • 引用工作簿中多个工作表的单元格 • 三维引用的一般格式为：工作表标签！单元格引用 • 循环引用 • 一个公式直接或者间接引用了自己的值，即出现循环引用 16

17. 4.3.3 创建名称及其使用 • 在 Excel 中，可以通过一个名称来代表工作表、单元格、常量、图表或公式等。如果在Excel中定义一个名称，就可以在公式中直接使用它 • 名称的创建 • 名称的使用 17

18. 4.3.4 SUM函数的应用 • 功能：返回指定参数所对应的数值之和 • 格式：SUM（number1，number2，……） • number1，number2等这些是指定所要进行求和的参数 • 注意：函数中可以包含的参数个数为1到30个之间，参数类型可以是数字、逻辑值和数字的文字表示等形式 • SUMIF函数：用于计算符合指定条件的单元格区域内的数值进行求和 ，格式为：SUMIF（range，criteria，sum_range） • range 表示的是条件判断的单元格区域； • criteria 表示的是指定条件表达式； • sum_range 表示的是需要计算的数值所在的单元格区域 18

19. 4.3.5 AVERAGE函数的应用 • 功能：返回指定参数所对应数值的算术平均数 • 格式：AVERAGE（number1，number2，……） • number1，number2等是指定所要进行求平均值的参数 • 注意：该函数只对参数的数值求平均数，如区域引用中包含了非数值的数据，则AVERAGE不把它包含在内。 19

20. 4.3.6 IF函数的应用 • IF函数是一个条件函数 • 格式：IF（logical_test，value_if_true，value_if_false） • logical_test：当值函数的逻辑条件 • value_if_true：当值为“真”时的返回值 • value_if_false：当值为“假”时的返回值 • 功能：能为对满足条件的数据进行处理，条件满足则输出value_if_true，不满足则输出value_if_false • 注意：在IF函数中三个参数中可以省略value_if_true或value_if_false，但不能同时省略 • 在IF函数中使用嵌套函数，最多可用嵌套7层 20

21. 4.4 Excel中数组的使用 • 4.4.1 数组的概述 • 4.4.2 使用数组常数 • 4.4.3 编辑数组 • 4.4.4 数组公式的应用 21

22. 4.4.1 数组的概述 • 数组就是单元的集合或是一组处理的值的集合. • 数组公式：输入一个单个的公式，它执行多个输入操作并产生多个结果。 • 一个数组公式可以占用一个或多个单元区域，数组的元素可多达6500个。 • 与单值公式的区别：它可以产生一个以上的结果 • 数组公式的创建 22

23. 4.4.2 使用数组常数 • 数组常量：输入数值的数组 • 常量可以是数字、文本、逻辑值和错误值等。 • 数字：其类型可以是整数型、小数型和科学计数法形式 • 文本：必须使用引号引起来 ，例如：“星期一” • 同一个数组常量中可以使用不同类型的值 • 数组常量中的值必须是常量，不可以是公式 • 注意：1.数组常量不能含有货币符号、括号或百分比符号 2. 所输入的数组常量不得含有不同长度的行或列 • 数组常量可以分为一维数组与二维数组，常用逗号将一行内的元素分开，用分号将各行分开。 23

24. 4.4.3 编辑数组 • 一个数组包含数个单元格，这些单元格形成一个整体，所以，数组中的单元格不能单独的进行编辑、清除和移动，也不能插入或删除单元格。在对数组进行操作（编辑、清楚、移动单元格，插入、删除单元格）之前，必须先选取整个数组，然后进行相应的操作。 24

25. 4.5 Excel的函数介绍 • 4.5.1 财务函数 • 4.5.2 文本函数 • 4.5.3 日期与时间函数 • 4.5.4 查找与引用函数 • 4.5.5 数据库函数 • 4.5.6 其他类型函数 25

26. 4.5.1 财务函数 • 财务函数是财务计算和财务分析的专业工具，有了这些函数的存在，可以很快捷方便地解决复杂的财务运算，在提高财务工作效率的同时，更有效的保障了财务数据计算的准确性。 • 主要介绍以下几个财务函数： • 1、 PMT函数 2、IPMT函数 3、FV函数 4、PV函数 5、SLN函数 26

27. 4.5.1 财务函数-PMT函数 • 功能：基于固定利率及等额分期付款方式，返回贷款的每期付款额 • 格式：PMT(rate，nper，pv，fv，type) • rate：贷款利率 • nper：该项贷款的总贷款期限或者总投资期 • pv：从该项贷款（或投资）开始计算时已经入账的款项，或一系列未来付款当前值的累积和 • fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0 • type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末，其默认值为0 27

28. 4.5.1 财务函数-IPMT函数 • 功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期限内的利息偿还额 • 格式：IPMT(rate，per，nper，pv，fv) • rate：各期利率 • per：用于计算利息数额的期数，介于1～nper之间 • nper：总投资（或贷款）期，即该项投资（或贷款）的付款期总数 • pv：从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和 • fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0 28

29. 4.5.1 财务函数-FV函数 • 功能：基于固定利率及等额分期付款方式，返回某项投资的未来值 • 格式：FV (rate，nper，pmt，pv，type) • rate：各期利率 • nper：总投资（或贷款）期，即该项投资（或贷款）的付款期总数 • pmt：各期所应支付的金额 • pv：现值，即从该项投资开始计算时已经入账的款项，或一系列未来付款的当前值的累积和，也称为本金 • type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末，其默认值为0 29

30. 4.5.1 财务函数-PV函数 • 功能：一系列未来付款的当前值的累积和，返回的是投资现值 • 格式：PV(rate，nper，pmt，fv，type) • rate：贷款利率 • nper：该项贷款的总贷款期限或者总投资期 • pmt：各期所应支付的金额 • fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0 • type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末，其默认值为0 30

31. 4.5.1 财务函数-SLN函数 • 功能：某项资产在一个期间中的线性折旧值 • 格式：SLN(cost，salvage，life) • cost：资产原值 • salvage：资产在折旧期末的价值，即资产残值 • life：折旧期限，即资产的使用寿命 31

32. 4.5.2 文本函数 • 文本函数可以处理公式中的文本字符串 • 主要介绍以下几个文本函数： • EXACT函数 • CONCATENATE函数 • SUBSTITUTE函数 • REPLACE函数 • SEARCH函数 32

33. 4.5.2 文本函数-EXACT函数 • 功能：用来比较两个文本字符串是否相同。如果两个字符串相同，则返回“TRUE”，反之，则返回“FALSE” • 格式：EXACT（text1，text2） • text1 和 text2：两个要比较的文本字符串 • 注意：1、在字符串中如果有多余的空格，会被视为不同 2、EXACT函数在判别字符串的时候，会区分英文的大小写，但不考虑格式设置的差异 33

34. 4.5.2 文本函数-CONCATENATE函数 • 功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中 • 格式：CONCATENATE(text1，text2，……) • text1，text2，……：需要连接的字符文本或引用的单元格 • 注意：1、该函数最多可以附带30个参数 2、如果其中的参数不是引用的单元格，且为文本格式的，请给参数加上英文状态下的双引号 如果将上述函数改为使用“&”符连接也能达到相同的效果 34

35. 4.5.2 文本函数-SUBSTITUTE函数 • 功能：实现替换文本字符串中的某个特定字符串 • 格式：SUBSTITUTE（text，old_text，new_text，instance_num） • text：原始内容或是单元格地址 • old_text：要被替换的字符串 • new_text：替换old_text的新字符串 • 如果字符串中含有多组相同的old_text时，可以使用参数instance_num来指定要被替换的字符串是文本字符串中的第几组。如果没有指定instance_num的值，默认的情况下，文本中的每一组old_text都会被替换为new_text。 35

36. 4.5.2 文本函数-REPLACE函数 • 功能：将某几位的文字以新的字符串替换。其替换功能与SUBSTITUTE函数大致类似 • 格式：REPLACE（old_text，start_num，num_chars，new_text） • old_text：原始的文本数据 • start_num：设置要从old_text的第几个字符位置开始替换 • num_chars：设置共有多少字符要被替换 • new_text：要用来替换的新字符串 36

37. 4.5.2 文本函数-SEARCH函数 • 功能：用来返回指定的字符串在原始字符串中首次出现的位置 • 格式：SEARCH（find_text，within_text，start_num） • find_text：要查找的文本字符串 • within_text：要在哪一个字符串查找 • start_num：从within_text的第几个字符开始查找。 • 注意：在find_text中，可以使用通配符，例如：问号“？”和星号“*”。其中问号“？”代表任何一个字符，而星号“*”可代表任何字符串。如果要查找的字符串就是问号或星号，则必须在这两个符号前加上“～”符号。 37

38. 4.5.3 日期与时间函数 • 日期与时间函数可以用来分析或操作公式中与日期和时间有关的值 • 主要介绍以下几个日期与时间函数： • DATE函数 • DAY函数 • TODAY函数 • TIME函数 38

39. 4.5.3 日期与时间函数-DATE函数 • 功能：计算某一特定日期的系列编号 • 格式：DATE（year，month，day） • year：指定年份 • month：每年中月份的数字 • day：在该月份中第几天的数字 • 注意：1、若year是介于0～1899之间，则Excel会自动将该值加上1900，再计算year ；若year是介于1900～9999之间，则Excel将使用该数值作为year 。 2、如果所输入的月份month值大于12，将从指定年份一月份开始往上累加 。 3、如果所输入的天数day值大于该月份的最大天数时，将从指定月数的第一天开始往上累加 。 39

40. 4.5.3 日期与时间函数-DAY函数 • 功能：返回指定日期所对应的当月中的第几天的数值，介于1～31之间 • 格式：DAY（serial_number） • serial_number：指定的日期或数值 • DAY函数的使用有两种方法： • 参数serial_number使用的是日期输入 • 参数serial_number使用的是数值的输入：在Excel中，系统将1900年1月1日对应于序列号1，后面的日期都是相对于这个时间进行对序列号的进行累加 40

41. 4.5.3 日期与时间函数-TODAY函数 • 功能：返回当前系统的日期 • 格式：TODAY（） 其语法形式中无参数，若要显示当前系统的日期，可以在当前单元格中直接输入公式TODAY（）。 41

42. 4.5.3 日期与时间函数-TIME函数 • 功能：返回某一特定时间的小数值，它返回的小数值从0～0.99999999之间，代表0:00:00(12:00:00A.M) ~ 23:59:59 (11:59:59P.M)之间的时间 • 格式：TIME（hour，minute，second） • hour：0～23之间的数，代表小时 • minute：0～59之间的数，代表分 • second：0～59之间的数，代表秒 42

43. 4.5.4 查找与引用函数 • 在一个工作表中，可以利用查找与引用函数功能按指定的条件对数据进行快速查询、选择和引用。查找与引用函数用于查找（查看）列表或表格中的值 • 主要介绍以下几个查找与引用函数： • VLOOKUP函数 • HLOOKUP函数 • LOOKUP函数 43

44. 4.5.4 查找与引用函数-VLOOKUP函数 • 功能：从一个数组或表格的最左列中查找含有特定值的字段，再返回同一列中某一指定单元格中的值 • 格式： VLOOKUP（lookup_value，talbe_array，col_index_num，range_lookup） • look_value：要在数组中搜索的数据，它可以是数值、引用地址或文字字符串 • table_array：要搜索的数据表格、数组或数据库 • col_index_num：一个数字，代表要返回的值位于table_array中的第几列。 • rang_lookup：一个逻辑值，如果其值为“TRUE”或被省略，则返回部分符合的数值；如果该值为“FALSE”时，函数只会查找完全符合的数值，如果找不到，则返回错误值“＃N/A”。 • 如果range_lookup为“TRUE”，则table_array第一列的值必须以递增次序排列 44

45. 4.5.4 查找与引用函数-HLOOKUP函数 • 功能：可以用来查询表格的第一行的数据 • 格式： HLOOKUP（lookup_value，table_array，row_index_num，range_lookup） • look_value：要在表格第一行中搜索的值 • table_array：要搜索的数据表格、数组或数据库 • row_index_num：要返回的值位于table_array列中第几行rang_lookup：一个逻辑值，如果其值为“TRUE”或被省略，则返回部分符合的数值；如果该值为“FALSE”时，函数只会查找完全符合的数值，如果找不到，则返回错误值“＃N/A”。 • 如果 range_lookup 为 “TRUE” ，则 table_array 第一列的值必须以递增次序排列，如果 rang_lookup 是 “FALSE” ，则table_array不需要先排序。 45

46. 4.5.4 查找与引用函数-LOOKUP函数 • LOOKUP函数有两种语法形式：向量（较常用）和数组。 • 向量形式的格式： LOOKUP（lookup_value，lookup_vector，result_vector） • look_value：要录找的数据 • lookup_vector：一个单行或单列范围，内容可以是文字、数字或逻辑值，但要以递增方式排列，否则不会返回正确的值 • result_vector：一个单行或单列范围，大小应与lookup_vector相同 • 查询时，若LOOKUP函数无法找到完全符合的lookup_value,则会采用在lookup_value中仅次于lookup_value的值。 46

47. 4.5.5 数据库函数 • 数据库函数是用于对存储在数据清单或数据库中的数据进行分析，判断其是否符合特定的条件。 • 根据各自函数所具有的功能不同，可分为两大类： • 数据库信息函数：直接获取数据库中的信息 • 数据库分析函数：分析数据库的数据信息 • 数据库函数格式为： 函数名称（database，field，criteria） • database：构成数据清单或数据库的单元格区域 • field：指定函数所使用的数据列 • criteria：一组包含给定条件的单元格区域 47

48. 4.5.5 数据库函数 • 数据库信息函数 • DCOUNT函数 ：返回列表或数据库中满足指定条件的记录字段（列）中包含数值的单元格的个数 • DGET函数：从列表或数据库的列中提取符合指定条件的单个值 • DCOUNTA函数：返回列表或数据库中满足指定条件的记录字段（列）中非空单元格的个数 • 数据库分析函数 • DAVERAGE函数：计算列表或数据库的列中满足指定条件的数值的平均值 • DMAX函数：返回列表或数据库的列中满足条件的最大值 • DPRODUCT函数：返回列表或数据库中满足指定条件的记录字段（列）中数值的乘积 • DSUM函数是用来返回列表或数据库中满足指定条件的记录字段（列）中的数字之和 48

49. 4.5.6 其他类型的函数 • 在Excek2003函数库中，除了以上介绍了财务函数、文本函数、日期与时间函数、查找与引用函数、数据库函数之外，还有统计函数、信息函数、逻辑函数、数学与三角函数以及工程函数 。 • 主要介绍以下几种函数： • IS类函数 • TYPE函数 • COUNT 函数 • ROUND 函数 • MAX 函数、MIN函数 49

50. 4.5.6 其他类型函数-IS类函数 • 功能：测试单元格中的内容是否为目标内容或者格式。 • 格式：IS(xxx)（value ） • value：测试的值或单元格地址 • IS类函数有以下一些： • ISBLANK（value）：是否为空白单元格 • ISERR（value）：是否为 #N/A之外的任何一种错误值 • ISERROR（value）：是否为任何一种错误值 • ISLOGICAL（value）：是否为逻辑值 • ISNA（value）：是否为错误值 #N/A • ISNONTEXT（value）：是否为任何非文本或空单元格 • ISNUMBER（value）：是否为数字 • ISREE（value）：是否为引用 • ISTEXT（value） ：是否为文本 50