03专题:MicrosoftExcel2003函数在工作表中的商业应用.ppt
Excel 2003 经典课堂: Excel函数在工作表中的商业应用,主讲:Adam Liu Email: adamliumailgmail.com,课程大纲,Excel公式和函数基础 Excel函数的应用方式和目的 如何发现Excel中的函数 函数在Excel中的书写位置 Excel函数的商用场景 自定义Excel函数 如何实现函数的调试,【Excel公式基础】公式概述,公式的输入“=” 公式的切换【Crtl】+【】 以结果替换公式:原先是公式,转换后变成数值选择性粘贴,【Excel公式基础】公式的引用,单元格与区域的引用 表示方法“:” 区域的选择“B5:D9” 区域命名 为区域命名 命名的使用 命名的删除InsertNameDefine,【Excel函数基础】函数概述,函数与公式的区别 公式的整个运算过程由使用者进行设定; 而函数是一些设定好的公式,使用者只需要将一些参数交给函数, 函数就会根据设定好的公式返回结果。 函数的组成 函数名称+引用变量,【Excel函数基础】 函数在Excel中的作用,简化公式 实现其他方法无法实现的功能 提高编辑数据的速度 实现判断功能,【Excel函数基础】 Excel中的函数类型,财务函数 Financial 日期与时间函数 Data & Time 数学函数 Math 统计函数 Statistical 查找函数 Lookup & Reference 数据库函数 Database 文本和数据函数 Text 逻辑函数 Logical 信息函数 Information 工程函数 Engineering,【Excel函数基础】 在使用函数时应了解(1),单元格的引用问题 相对地址引用 =A1+A2+A6 绝对地址引用 =$A$1+$A$2+$A$6 混合地址引用 =A$1+$A2+A$6,【Excel函数基础】 在使用函数时应了解(2),三维地址引用 (1)相同工作簿,不同工作表间引用 =sheetName!cellName (2)不同工作簿之间数据引用 =c:workbookname.xlssheetName!cellName 运算符的优先级问题(详见下页) 实际数值与现实数值的关系 Tools Options Error Checking Inconsistence formula in region (公式在区域内部一致),【Excel函数基础】 运算符的优先级问题,运算符 运算符用来对公式中的各元素进行运算操作。Excel包含算术运算符、比较运算符、文本运算符和引用运算符4种类型。 1、算术运算符:算术运算符用来完成基本的数学运算,如加法、减法和乘法。 算术运算符有十(加)、一(减)、(乘)、(除)、 %(百分比)、(乘方)。,【Excel函数基础】 运算符的优先级问题,2、比较运算符:比较运算符用来对两个数值进行比较,产生的结果为逻辑值True(真)或False(假)。比较运算符有(等于)、(大于)、(小于)、(大于等于)、(小于等于)、(不等于)。 3、文本运算符:文本运算符“”用来将一个或多个文本连接成为一个组合文本。例如“Micro”“soft”的结果为“Microsoft”。 4、引用运算符:引用运算符用来将单元格区域合并运算。,【Excel函数基础】 运算符的优先级问题,引用运算符为: 区域(冒号)表示对两个引用之间,包括两个引用在内的所有区域的单元格进行引用,例如,SUM(BI:D5)。 联合(逗号)表示将多个引用合并为一个引用,例如,SUM(B5,B15,D5,D15)。 交叉(空格)表示产生同时隶属于两个引用的单元格区域的引用。,【Excel函数基础】 运算符的运算顺序,如果公式中同时用到了多个运算符,Excel将按下面的顺序进行运算: 如果公式中包含了相同优先级的运算符,例如公式中同时包含了乘法和除法运算符,Excel将从左到右进行计算。,【Excel函数基础】 运算符的运算顺序,如果要修改计算的顺序,应把公式需要首先计算的部分括在圆括号内。 公式中运算符的顺序从高到低依次为:(冒号),(逗号)、(空格)、负号(如-)、%(百分比)、(乘幂)、*和(乘和除)、+和-(加和减)、&(连接符)、比较运算符。,【Excel函数基础】 挖宝,函数在哪里?,【Excel函数基础】 在Excel中插入函数的方法,手工输入函数 在录入时可以访问到该函数的帮助 Excel将自动补充函数末尾的括号 使用Crtl+Shift+A可以看到函数的参数说明 插入函数的对话框 可以根据需求查找函数 将给出函数的参数对话框,【Excel函数基础】 如何在函数中输入参数,不带参数Rand() 使用名称作为参数Sum(Sales) 整行或整列作为参数Sum(C:C) 使用值作为参数Left(A1,1)截取字符串 表达式作为参数Sqrt(A1+A2) 函数作为参数Sin(Radians(B1) 数组作为参数Or(A1=2,4,6),【Excel函数基础】 函数出现在Excel中的位置,单元格中 (最常见的应用) 条件格式对话框中 (例如隔行显示不同的颜色) 数据有效性的验证对话框中 (例如验证输入数据的唯一性和准确性) 图表的选项中 (例如图表与数据表自动实现动态变更),Excel函数应用案例1,统计某一部门的加班费 SUMIF函数 函数格式:SUMIF (range, criteria, sum_range),Excel函数应用案例2,查找函数 LOOKUP 按照数组的维数查找 =lookup(lookup_value,lookup_vector,result_vector) =lookup(lookup_value,array),Excel函数应用案例2,思考:既然有LOOKUP,为什么会产生VLOOKUP和HLOOKUP呢? VLOOKUP 在第一列进行查找(Vertical) =vlookup(lookup_value,table_array,col_index_num,range_lookup) HLOOKUP 在第一行进行查找(Horizontal),Excel函数应用案例2,=hlookup(lookup_value,table_array,row_index_num,range_lookup) 示例:按照商品编号查找售价 示例:按照商品名称查找成本 示例:对学生的语文成绩进行等级自动划分(LOOKUP函数的另类形式),Excel函数应用案例3,计算员工的年龄 使用相关的日期函数Year() 找不到的函数 DateDif(start_date,end_date,code) Code类型 Y返回整年数 M返回整月数 D返回天数,Excel函数应用案例4,统计单元格区域中的唯一值 COUNTIF() 可以将其应用在条件格式中 可以将其应用在数据有效性中,Excel函数应用案例5,对单元格区域中的n个最大值或最小值进行运算 LARGE() SMALL(),Excel函数应用案例6,根据趋势进行预测 GROWTH()线性拟和 示例:预估计投资和收益之间的关系 示例:预计下月的销售情况,Excel函数应用案例7,在数据库表单中进行统计 数据库函数 DGET() 示例:统计每个季度销售业绩最好的员工,Excel函数应用案例8,文本函数 Left() Right(),在Excel中可能出现的错误值,#DIV/0! 不能够除以0,为了避免这样的错误可使用IF函数 #N/A 数据无效或没有定义 #NAME? 包含了没有定义的范围或单元格 #NULL! 试图引用没有重叠区域的2个单元格 #NUM! 无效参数,或者返回的数值超出Excel定义范围 #REF! 无效的单元格引用 #VALUE! 数据类型错误,或在输入函数时出现输入错误,Excel中的函数审核工具,跟踪单元格 引用单元格 从属单元格 跟踪错误 Excel公式求值,保护Excel中的函数,设置单元格属性 开启工作表保护 设置工作表报保护选项,adamliumailgmail.com,