1 / 54

第 4 章 函数与公式

第 4 章 函数与公式. 提 纲. 4.1 Excel 实例介绍 4.2 Excel 中数据的输入 4.3 Excel 中函数与公式 4.4 Excel 中数组的使用 4.5 Excel 的函数介绍. 4.1 Excel 实例介绍. 实例背景: 张某在“淘宝网”开了一家网上商店,主要销售手机、相机、 MP3 、 MP4 等数码产品 。随着店铺宣传的开展,小张的网店生意越来越红火,往来账务也随之增加,导致了小张越来越苦恼该如何管理好所有店铺中的信息数据,并对有效的数据进行分析。

Download Presentation

第 4 章 函数与公式

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related