excel函数与公式.ppt
第4章 函数与公式,4.1 Excel实例介绍4.2 Excel中数据的输入4.3 Excel中函数与公式4.4 Excel中数组的使用4.5 Excel的函数介绍,提 纲,实例背景：张某在“淘宝网”开了一家网上商店，主要销售手机、相机、MP3、MP4等数码产品。随着店铺宣传的开展，小张的网店生意越来越红火，往来账务也随之增加，导致了小张越来越苦恼该如何管理好所有店铺中的信息数据，并对有效的数据进行分析。如何使用Excel对他的商品销售记录进行数据分析？如何有效记录每天卖出的商品？如何计算每月获得的销售利润？如何得知什么货销售量好，容易赚钱？,4.1 Excel实例介绍,进货清单 主要记录每次店铺进货的商品相关信息销售清单 主要记录每次销售的记录 库存清单 主要记录现库存中的存货情况 销售统计用于统计一个阶段的销售情况分类统计报表（图）对各个品牌、各种商品类别进行详细的统计，用户可以根据具体的时间品牌或类别进行条件性查询。商品资料用于存放店铺中所销售商品的基本信息资料 客户资料用于存储客户的信息资料 其他资料设置用于存储其他的相关信息资料 相关财务表（贷款偿还表，项目投资表、固定资产折旧表）,4.1.1 Excel的建立,数据输入 函数与公式 筛选与排序 分类汇总 数据透视表（图）,4.1.2 Excel中数据的管理与分析,4.2.1 自定义下拉列表输入4.2.2 自定义序列与填充柄4.2.3 条件格式4.2.4 数据输入技巧4.2.5 数据的舍入方法,4.2 Excel中的数据输入,作用：使用自定义下拉列表的方式进行数据的输入，可以提高数据输入的速度和准确性。建立方法：使用“数据有效性”命令来完成普通方法设置：直接键入列表选项使用名称设置：使用名称建立列表选项,4.2.1 自定义下拉列表输入,自定义序列：一组数据，可按重复方式填充列。创建自定义序列有以下两种方式：利用工作表中现有的数据项采用临时输入的方式自定义序列可以包含文字或带数字的文本注意：如果是包含数字的自定义序列，则需要进行格式的设置，之后才能在单元格中输入序列项，选择列表并导入列表。创建好自定义序列之后，采用填充柄进行填充能达到快速、有效的结果。,4.2.2 自定义序列与填充柄,作用：通过设置数据条件格式，可以让单元格中的数据满足指定条件时就以特殊的标记（如：以红色、数据条、图标等）显示出来。关于条件格式的设置主要通过“条件格式”对话框,4.2.3 条件格式,1特殊数据输入输入分数输入负数输入文本类型的数字输入特殊字符2快速输入大写中文数字3自动超链接的处理取消自动超链接关闭自动超链接 取消多个的超链接,4.2.4 数据输入技巧,1舍入到最接近的倍数2货币值舍入3使用INT和TRUNC函数4舍入为n位有效数字5时间值舍入,4.2.5 数据的舍入方法,4.3.1 公式的概述4.3.2 单元格的引用4.3.3 创建名称及其使用4.3.4 SUM函数的应用4.3.5 AVERAGE函数的应用4.3.6 IF函数的引用,4.3 Excel中函数与公式,公式就是对工作表中的数值进行计算的式子公式由操作符和运算符两个基本部分组成。操作符可以是常量、名称。数组、单元格引用和函数等。运算符用于连接公式中的操作符，是工作表处理数据的指令。,4.3.1 公式的概述,公式元素 运算符、单元格引用、值或字符串、函数其参数、括号运算符 算术运算符、逻辑运算符、文本运算符、引用运算符 运算符的优先级,4.3.1 公式的概述,相对引用总是以当前单元格位置为基准，在复制公式时，当前单元格改变了，在单元格中引入的地址也随之发生变化。相对地址引用的表示是，直接写列字母和行号，如A1，D8等。绝对引用在复制公式时，不想改变公式中的某些数据，即所引用的单元格地址在工作表中的位置固定不变，它的位置与包含公式的单元格无关 绝对地址引用的表示是，在相应的单元格地址的列字母和行号前加“$”符号,4.3.2 单元格的引用,混合引用公式中参数的行采用相对引用、列采用绝对引用，或者列采用相对引用、行采用绝对引用，如$A1、A$1。三维引用引用工作簿中多个工作表的单元格 三维引用的一般格式为：工作表标签！单元格引用循环引用一个公式直接或者间接引用了自己的值，即出现循环引用,4.3.2 单元格的引用,在 Excel 中，可以通过一个名称来代表工作表、单元格、常量、图表或公式等。如果在Excel中定义一个名称，就可以在公式中直接使用它 名称的创建名称的使用,4.3.3 创建名称及其使用,功能：返回指定参数所对应的数值之和 格式：SUM（number1，number2，）number1，number2等这些是指定所要进行求和的参数注意：函数中可以包含的参数个数为1到30个之间，参数类型可以是数字、逻辑值和数字的文字表示等形式 SUMIF函数：用于计算符合指定条件的单元格区域内的数值进行求和，格式为：SUMIF（range，criteria，sum_range）range 表示的是条件判断的单元格区域；criteria 表示的是指定条件表达式；sum_range 表示的是需要计算的数值所在的单元格区域,4.3.4 SUM函数的应用,功能：返回指定参数所对应数值的算术平均数 格式：AVERAGE（number1，number2，）number1，number2等是指定所要进行求平均值的参数 注意：该函数只对参数的数值求平均数，如区域引用中包含了非数值的数据，则AVERAGE不把它包含在内。,4.3.5 AVERAGE函数的应用,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层,4.3.6 IF函数的应用,4.4.1 数组的概述4.4.2 使用数组常数 4.4.3 编辑数组 4.4.4 数组公式的应用,4.4 Excel中数组的使用,数组就是单元的集合或是一组处理的值的集合.数组公式：输入一个单个的公式，它执行多个输入操作并产生多个结果。一个数组公式可以占用一个或多个单元区域，数组的元素可多达6500个。与单值公式的区别：它可以产生一个以上的结果数组公式的创建,4.4.1 数组的概述,数组常量：输入数值的数组常量可以是数字、文本、逻辑值和错误值等。数字：其类型可以是整数型、小数型和科学计数法形式 文本：必须使用引号引起来，例如：“星期一”同一个数组常量中可以使用不同类型的值数组常量中的值必须是常量，不可以是公式注意：1.数组常量不能含有货币符号、括号或百分比符号 2.所输入的数组常量不得含有不同长度的行或列 数组常量可以分为一维数组与二维数组，常用逗号将一行内的元素分开，用分号将各行分开。,4.4.2 使用数组常数,一个数组包含数个单元格，这些单元格形成一个整体，所以，数组中的单元格不能单独的进行编辑、清除和移动，也不能插入或删除单元格。在对数组进行操作（编辑、清楚、移动单元格，插入、删除单元格）之前，必须先选取整个数组，然后进行相应的操作。,4.4.3 编辑数组,4.5.1 财务函数4.5.2 文本函数4.5.3 日期与时间函数4.5.4 查找与引用函数4.5.5 数据库函数4.5.6 其他类型函数,4.5 Excel的函数介绍,财务函数是财务计算和财务分析的专业工具，有了这些函数的存在，可以很快捷方便地解决复杂的财务运算，在提高财务工作效率的同时，更有效的保障了财务数据计算的准确性。主要介绍以下几个财务函数：1、PMT函数 2、IPMT函数 3、FV函数 4、PV函数 5、SLN函数,4.5.1 财务函数,功能：基于固定利率及等额分期付款方式，返回贷款的每期付款额 格式：PMT(rate，nper，pv，fv，type)rate：贷款利率nper：该项贷款的总贷款期限或者总投资期pv：从该项贷款（或投资）开始计算时已经入账的款项，或一系列未来付款当前值的累积和fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末，其默认值为0,4.5.1 财务函数-PMT函数,功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期限内的利息偿还额 格式：IPMT(rate，per，nper，pv，fv)rate：各期利率 per：用于计算利息数额的期数，介于1nper之间 nper：总投资（或贷款）期，即该项投资（或贷款）的付款期总数 pv：从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和 fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0,4.5.1 财务函数-IPMT函数,功能：基于固定利率及等额分期付款方式，返回某项投资的未来值格式：FV(rate，nper，pmt，pv，type)rate：各期利率 nper：总投资（或贷款）期，即该项投资（或贷款）的付款期总数 pmt：各期所应支付的金额 pv：现值，即从该项投资开始计算时已经入账的款项，或一系列未来付款的当前值的累积和，也称为本金 type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末，其默认值为0,4.5.1 财务函数-FV函数,功能：一系列未来付款的当前值的累积和，返回的是投资现值 格式：PV(rate，nper，pmt，fv，type)rate：贷款利率nper：该项贷款的总贷款期限或者总投资期 pmt：各期所应支付的金额 fv：未来值，或在最后一次付款后希望得到的现金余额，如果忽略该值，将自动默认为0 type：一个逻辑值，用以指定付款时间是在期初还是在期末，1表示期初，0表示期末，其默认值为0,4.5.1 财务函数-PV函数,功能：某项资产在一个期间中的线性折旧值 格式：SLN(cost，salvage，life)cost：资产原值salvage：资产在折旧期末的价值，即资产残值life：折旧期限，即资产的使用寿命,4.5.1 财务函数-SLN函数,文本函数可以处理公式中的文本字符串主要介绍以下几个文本函数：EXACT函数 CONCATENATE函数SUBSTITUTE函数 REPLACE函数 SEARCH函数,4.5.2 文本函数,功能：用来比较两个文本字符串是否相同。如果两个字符串相同，则返回“TRUE”，反之，则返回“FALSE”格式：EXACT（text1，text2）text1 和 text2：两个要比较的文本字符串 注意：1、在字符串中如果有多余的空格，会被视为不同 2、EXACT函数在判别字符串的时候，会区分英文的大小写，但不考虑格式设置的差异,4.5.2 文本函数-EXACT函数,功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中 格式：CONCATENATE(text1，text2，)text1，text2，：需要连接的字符文本或引用的单元格 注意：1、该函数最多可以附带30个参数 2、如果其中的参数不是引用的单元格，且为文本格式的，请给参数加上英文状态下的双引号如果将上述函数改为使用“&”符连接也能达到相同的效果,4.5.2 文本函数-CONCATENATE函数,功能：实现替换文本字符串中的某个特定字符串 格式：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。,4.5.2 文本函数-SUBSTITUTE函数,功能：将某几位的文字以新的字符串替换。其替换功能与SUBSTITUTE函数大致类似 格式：REPLACE（old_text，start_num，num_chars，new_text）old_text：原始的文本数据start_num：设置要从old_text的第几个字符位置开始替换num_chars：设置共有多少字符要被替换new_text：要用来替换的新字符串,4.5.2 文本函数-REPLACE函数,功能：用来返回指定的字符串在原始字符串中首次出现的位置 格式：SEARCH（find_text，within_text，start_num）find_text：要查找的文本字符串within_text：要在哪一个字符串查找start_num：从within_text的第几个字符开始查找。注意：在find_text中，可以使用通配符，例如：问号“？”和星号“*”。其中问号“？”代表任何一个字符，而星号“*”可代表任何字符串。如果要查找的字符串就是问号或星号，则必须在这两个符号前加上“”符号。,4.5.2 文本函数-SEARCH函数,日期与时间函数可以用来分析或操作公式中与日期和时间有关的值 主要介绍以下几个日期与时间函数：DATE函数 DAY函数 TODAY函数TIME函数,4.5.3 日期与时间函数,功能：计算某一特定日期的系列编号 格式：DATE（year，month，day）year：指定年份month：每年中月份的数字day：在该月份中第几天的数字 注意：1、若year是介于01899之间，则Excel会自动将该值加上1900，再计算year；若year是介于19009999之间，则Excel将使用该数值作为year。2、如果所输入的月份month值大于12，将从指定年份一月份开始往上累加。3、如果所输入的天数day值大于该月份的最大天数时，将从指定月数的第一天开始往上累加。,4.5.3 日期与时间函数-DATE函数,功能：返回指定日期所对应的当月中的第几天的数值，介于131之间 格式：DAY（serial_number）serial_number：指定的日期或数值 DAY函数的使用有两种方法：参数serial_number使用的是日期输入 参数serial_number使用的是数值的输入：在Excel中，系统将1900年1月1日对应于序列号1，后面的日期都是相对于这个时间进行对序列号的进行累加,4.5.3 日期与时间函数-DAY函数,功能：返回当前系统的日期 格式：TODAY（）其语法形式中无参数，若要显示当前系统的日期，可以在当前单元格中直接输入公式TODAY（）。,4.5.3 日期与时间函数-TODAY函数,功能：返回某一特定时间的小数值，它返回的小数值从00.99999999之间，代表0:00:00(12:00:00A.M)23:59:59(11:59:59P.M)之间的时间格式：TIME（hour，minute，second）hour：023之间的数，代表小时 minute：059之间的数，代表分 second：059之间的数，代表秒,4.5.3 日期与时间函数-TIME函数,在一个工作表中，可以利用查找与引用函数功能按指定的条件对数据进行快速查询、选择和引用。查找与引用函数用于查找（查看）列表或表格中的值 主要介绍以下几个查找与引用函数：VLOOKUP函数 HLOOKUP函数 LOOKUP函数,4.5.4 查找与引用函数,功能：从一个数组或表格的最左列中查找含有特定值的字段，再返回同一列中某一指定单元格中的值 格式：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第一列的值必须以递增次序排列,4.5.4 查找与引用函数-VLOOKUP函数,功能：可以用来查询表格的第一行的数据 格式：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不需要先排序。,4.5.4 查找与引用函数-HLOOKUP函数,LOOKUP函数有两种语法形式：向量（较常用）和数组。向量形式的格式：LOOKUP（lookup_value，lookup_vector，result_vector）look_value：要录找的数据lookup_vector：一个单行或单列范围，内容可以是文字、数字或逻辑值，但要以递增方式排列，否则不会返回正确的值 result_vector：一个单行或单列范围，大小应与lookup_vector相同 查询时，若LOOKUP函数无法找到完全符合的lookup_value,则会采用在lookup_value中仅次于lookup_value的值。,4.5.4 查找与引用函数-LOOKUP函数,数据库函数是用于对存储在数据清单或数据库中的数据进行分析，判断其是否符合特定的条件。根据各自函数所具有的功能不同，可分为两大类：数据库信息函数：直接获取数据库中的信息 数据库分析函数：分析数据库的数据信息 数据库函数格式为：函数名称（database，field，criteria）database：构成数据清单或数据库的单元格区域 field：指定函数所使用的数据列 criteria：一组包含给定条件的单元格区域,4.5.5 数据库函数,数据库信息函数 DCOUNT函数：返回列表或数据库中满足指定条件的记录字段（列）中包含数值的单元格的个数 DGET函数：从列表或数据库的列中提取符合指定条件的单个值 DCOUNTA函数：返回列表或数据库中满足指定条件的记录字段（列）中非空单元格的个数 数据库分析函数DAVERAGE函数：计算列表或数据库的列中满足指定条件的数值的平均值 DMAX函数：返回列表或数据库的列中满足条件的最大值 DPRODUCT函数：返回列表或数据库中满足指定条件的记录字段（列）中数值的乘积 DSUM函数是用来返回列表或数据库中满足指定条件的记录字段（列）中的数字之和,4.5.5 数据库函数,在Excek2003函数库中，除了以上介绍了财务函数、文本函数、日期与时间函数、查找与引用函数、数据库函数之外，还有统计函数、信息函数、逻辑函数、数学与三角函数以及工程函数。主要介绍以下几种函数：IS类函数TYPE函数 COUNT 函数ROUND 函数MAX 函数、MIN函数,4.5.6 其他类型的函数,功能：测试单元格中的内容是否为目标内容或者格式。格式：IS(xxx)（value）value：测试的值或单元格地址 IS类函数有以下一些：ISBLANK（value）：是否为空白单元格ISERR（value）：是否为#N/A之外的任何一种错误值ISERROR（value）：是否为任何一种错误值ISLOGICAL（value）：是否为逻辑值ISNA（value）：是否为错误值#N/AISNONTEXT（value）：是否为任何非文本或空单元格ISNUMBER（value）：是否为数字ISREE（value）：是否为引用ISTEXT（value）：是否为文本,4.5.6 其他类型函数-IS类函数,功能：返回测试值的数据类型 格式：TYPE（value）value:：任何数据值，如数字、文本、逻辑值等 返回结果情况：如果测试值value是数字，则函数会返回1如果测试值value是文本，则函数会返回2如果测试值value是逻辑值，则函数会返回4如果测试值value是错误值，则函数会返回16如果测试值value是数组型，则函数会返回64,4.5.6 其他类型函数-TYPE函数,功能：用于返回数字参数的个数，即统计数组或单元格区域中含有数值类型的单元格个数 格式：COUNT(value1，value2，)value1，value2，表示包含或引用各类型数据的参数 注意：1、函数可以最多附带上130个参数 2、只有数字类型的数据才能被统计类似函数：1、COUNTA函数：返回参数组中非空值的数目，即计算数组或单元格区域中数据项的个数 2、COUNTBLANK函数：计算某个单元格区域中空白单元格的数目 3、COUNTIF函数：计算区域中满足给定条件的单元格的个数,4.5.6 其他类型函数-COUNT函数,功能：根据指定的位数，将数字四舍五入 格式：ROUND（number，num_digits）number：将要进行四舍五入的数字num_digits：用户希望得到的数字的小数点后的位数 类似函数：1、ROUNDDOWN函数：按指定位数舍去数字指定位数后面的小数 2、ROUNDUP函数：按指定位数向上舍入指定位数后面的小数,4.5.6 其他类型函数-ROUND函数,功能：MAX函数：求参数列表中对应数字的最大值 MIN函数：求参数列表中对应数字的最小值 格式：MAX（number1，number2，）MIN（number1，number2，）number1、number2、：从中找出最大值或最小值的130个数字参数 类似函数：MAXA（value1，value2，）MINA（value1，value2，）区别：MAXA和MINA函数的参数类型可以是数字、空参数、逻辑值或数字的文本表示等形式。,4.5.6 其他类型函数-最值函数,
