数据管理与决策第七讲.ppt

1、,EXCEL培训,理解函数 字符函数 日期函数 统计函数 数学函数 课后习题,EXCEL函数应用,理解函数,数 据,规则,什 么 是 函 数？,结果,函 数 的 格 式,SUM(1，3，5) = 9,函 数 名,数 据,结 果,EXCEL函数的结构,Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。,Excel 函数结构:,也有一些函数是没有参数的,如ROW(),左右括号成对出现,单一结构,嵌套结构,参数与参数之间使用半角逗号进行分隔,使用公式,公式中的运算符 算术运算符,使用公式,公式中的运算符 2. 文本运算符 “ 2. 含有运算符和运算对象; 3. 能产生结果 公式的

2、3个基本组成部分: 数值:包括数字和文本 单元格引用 操作符,创建公式,创建公式的方法 使用编辑栏 直接在单元格中输入 创建3种类型的公式 创建简单公式; 2. 创建包含引用或名称的公式 3. 创建包含函数的公式,使用函数,函数是指预定义的内置公式,可以进行数学、文本和逻辑的运算或者查找工作表的信息。 函数是一个已经提供给用户的公式,并且有一个描述性的名称。 函数结构 函数名称：如果要查看可用函数的列表,可以单击一个单元格并按Shift+F3键； 参数：参数可以是数字、文本、逻辑值（TRUE或FALSE）、数值、错误值（如#N/A）或单元格引用。 参数工具提示：在输入函数时,会出现一个带有语法

3、和参数的工具提示。 输入公式：“插入函数”对话框。 函数的参数 函数的参数是一个函数体中括号内的部分,逻辑值: FALSE :需要完全一致 TRUE: 相似值亦可转换,使用函数,函数的类型 1. 常用函数: 常用函数指经常使用的函数,如求和或计算算术平均值等.包括SUM,AVERAGE, ISPMT,IF,HYPERLINK,COUNT,MAX,SIN, SUMIF和PMT.它们的语法和作用如下表:,使用函数,函数的类型 2. 财务函数: 财务函数用于财务的计算,它可以根据利率、贷款金额和期限计算出所要支付的金额,它们的变量紧密相互关联。 如：PMT(Rate,Nper,Pv,Fv,Type)

4、 =PMT（0.006,360,-300000) 其中,0.006是每一笔贷款项的利率,360是款项的数目,-300000是360笔款项的当前金额(在计算支付金额时,表示当前和将来金额的变量至少有一个通常是负数),注: Rate 贷款利率。 Nper 该项贷款的付款总数。 Pv 现值,或一系列未来付款的当前值的累积和,也称为本金。 Fv 为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv 则假设其值为零,也就是一笔贷款的未来值为零。 Type 数字 0 或 1,用以指定各期的付款时间是在期初还是期末。,使用函数,函数的类型 3. 时间和日期函数:,主要用于分析和处理日期值和时间值,

5、包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR等。,例：函数DATA 语法：DATE(year,month,day),在单元格中插入当前日期和时间 插入静态的日期和时间 当前日期 选取一个单元格,并按 Ctrl+; 当前时间 选取一个单元格,并按 Ctrl+Shift+； 当前日期和时间 选取一个单元格,并按 Ctrl+;,然后按空格键,最后按 Ctrl+Shift+; 插入会更新的日期和时间 使用 TODAY 和 NOW 函数完成这一工作。,小贴士,使用函数,函数的类型 4. 数学与三角函数: 5. 统计函数: 统计函数是用来对数据区域

6、进行统计分析。,主要用于进行各种各样的计算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN 和TUUNC等。,例：ABS函数 语法：ABS(number),函数是一种特殊的公式,因此所有的函数要以“=”开始。 使用方法如下：1.选中要输入函数的单元格 2.单击Insert菜单中的 命令,弹出如图所示的对话框：,使用函数,3.在上图的对话框中,选择函数分类下面的函数类型及 函数名下面的对应函数名。 4. 单击确定按钮,弹出如图所示的相应的调用 函数对话框：,使用函数,函数与公式通用技巧,限制：,突破：,函数与公式通用技巧改变工作表的计算精度,在实际工作中，许

7、多用户在计算数字时会设置单元格格式为2为小数的数值、货币格式或者是整数值。但在使用公式进行数据处理的过程中，经常会发现几个单元格之和与合计单元格显示的数据之和不相等。,因为excel将数值按照15位计算精度带入计算所致。如图，在excel选项中勾选“将精度设为所显示的精度”，可以解决上述情况的发生，但这样也降低了工作薄中所有工作表的计算精度，但不影响其他工作薄。 此外，也可以用ROUND函数对数值进行修约后再求和。,函数与公式通用技巧分布查看公式计算结果,方法一： 选中包含公式的单元格，单击“公式”选项卡中的“公式求值”按钮，弹出“公式求值”对话框，然后多次单击“求值”按钮，“求值”域中将按照

8、公式计算的顺序逐步地显示公式的计算过程。,方法二： 功能键除了可以重算工作薄外，还可以用于查看公式的运算结果。 当一个函数公式比较长时，可以用键对各个部分进行分段计算，以便公式差错和更正。在分段选择时，注意要包含一个完整的运算对象。,数据的筛选,通配符 以下通配符可作为筛选以及查找和替换内容时的比较条件:,分类汇总的概述 分类汇总是对数据清单进行数据分析的一种方法. Data/ Subtotal,分类汇总,确保要进行分类汇总的数据为下列格式：第一行的每一列都有标志，并且同一列中应包含相似的数据，在区域中没有空行或空列。 单击要分类汇总的列中的单元格。在上面的示例中，应单击“运动”列（列 B）中

9、的单元格。 单击“升序排序” 或“降序排序”。 在“数据”菜单上，单击“分类汇总”。 在“分类字段”框中，单击要分类汇总的列。在上面示例中，应单击“运动”列。 在“汇总方式”框中，单击所需的用于计算分类汇总的汇总函数。 在“选定汇总项”框中，选中包含了要进行分类汇总的数值的每一列的复选框。在上面的示例中，应选中“销售”列。,分类汇总,要分类汇总的列 分类汇总,步骤:,如果想在每个分类汇总后有一个自动分页符，请选中“每组数据分页”复选框。 如果希望分类汇总结果出现在分类汇总的行的上方，而不是在行的下方，请清除“汇总结果显示在数据下方”复选框。 单击“确定”。 注释 可再次使用“分类汇总”命令来添

10、加多个具有不同汇总函数的分类汇总。若要防止覆盖已存在的分类汇总，请清除“替换当前分类汇总”复选框。,分类汇总,要分类汇总的列 分类汇总,删除分类汇总 删除分类汇总时，Microsoft Excel 也删除分级显示以及随分类汇总一起插入列表中的所有分页符。 在含有分类汇总的列表中，单击任一单元格。 在“数据”菜单上，单击“分类汇总”。 单击“全部删除”。,分类汇总,删除分类汇总 删除分类汇总时，Microsoft Excel 也删除分级显示以及随分类汇总一起插入列表中的所有分页符。 在含有分类汇总的列表中，单击任一单元格。 在“数据”菜单上，单击“分类汇总”。 单击“全部删除”。,分类汇总,字符

11、函数,Left/Right: 截取字符串函数,用途: 根据指定的字符数返回文本串中的第一个或前几个字符。 语法：Left(文本，返回的字符数) Right(文本，返回的字符数) 参数：返回的字符数必须大于或等于0 实例：返回客户姓名中的姓氏 A1单元格字符串“张三”,使用Left(A1,1) 得到结果“张”,字符函数,Left/Right:综合应用,返回身份证号中的“出生年份”信息 例如：A1单元格有身份证号码 使用Right(Left(A1,10),4) 得到结果1982,字符函数,Mid: 截取字符串函数,用途: Mid返回文本串中从指定位置开始的特

12、定数目的字符，该数目可以由用户指定. 语法：Mid(文本串，开始位置，返回字符的个数) 参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为1，以此类推。 例如：A1单元格有身份证号码 使用Mid(A1,7,4)得到结果”1982”,字符函数,LEN: 计算字符串长度,用途: LEN返回文本的字符数 语法：LEN(文本) 参数：此函数用于双字节字符，且空格也将作为字符进行统计。 例如：检查身份证号码是否输入了18位。 A1单元格有身份证号码 使用LEN(A1)得到结果18 类似的函数LENB

13、，返回单字节字符。 例如：LENB(“ABC”) 返回 3，LENB(“张三”)返回4,字符函数,TRIM: 删除指定字符串多余的前导和后缀的空格,用途: 删除前导和后缀的空格 语法：TRIM(文本) 参数：字符串 例如：A1单元格有姓名“ 张三 ” 使用TRIM（A1）得到结果“张三”,字符函数,Replace: 替换指定位置处的任意文本,用途: REPLACE使用其他文本串并根据所指定的字符数替换另一文本串中的部分文本。 语法：Replace(需要替换其部分字符的文本，被替换字符的起始位置，替换的字符个数，用于替换到原文本中去的字符) 例如：A1单元格有公司名称“广东XXX有限公司”，将“

14、广东”替换成“广东省”。 使用Replace(A1,1,2,”广东省”)得到结果”广东省XXX有限公司”,字符函数,SUBSTITUTE: 替换指定文本,用途: 在文字串中用新文本替代旧文本。如果需要在一个文字串中替换指定文本，可以使用SUBSTITUTE；如果需要在某一字符串中替换指定位置处的任意文本，就应该使用函数REPLACE。 语法： SUBSTITUTE(需要替换其部分字符的文本，需被替换旧文本，用于替换的文本，第几次出现的旧文本) 参数：如果不输入第四个参数将替换所有旧文本。 例如：A1单元格有客户编码”G000G001”,将第二个”G”替换成”0”. 使用SUBSTITUTE(A

15、1,G,“0,2)得到结果”G0000001” 使用SUBSTITUTE(A1,G,“0)得到结果”00000001”,字符函数,Replace、 SUBSTITUTE综合应用,案例说明：将下图中凌乱的编码进行整理,使用函数：=REPLACE(SUBSTITUTE(A1, ,),2,1,M ),字符函数,Rept: 补位函数,用途: 按照给定的次数重复显示文本。可以通过REPT函数对单元格进行重复填充。 语法：Rept(需要重复显示的文本，重复显示的次数) 参数：重复显示的次数需要为正数。 实例：Rept（”很好”,2）返回“很好很好”。,字符函数,Rept函数与LEN函数的综合应用,使用函数

16、：=REPT(0,6-LEN(A1) & A1,日期函数,NOW/TODAY(): 返回当前日期,用途: 返回当前电脑的日期和时间 语法：NOW() 参数：此函数为无参数函数 实例：=NOW() 返回 “2013-11-05 11:00” 相对应的函数：TODAY()返回“2013-11-05”,YEAR: 返回给定数值的年份,用途: 返回给定数值年份信息 语法：YEAR（值） 参数：参数值应为日期类型的单元格 实例：例如：A1单元格有日期“2013-11-5” YEAR（A1）返回 值“2013”,日期函数,MONTH: 返回给定数值的月份,用途: 返回给定数值月份信息 语法：MONTH（值） 参数：参数值应为日期类型的单元格 实例：例如：A1单元格有日期“2013-11-5” MONTH（A1）返回 值“11”,日期函数,DAY: 返回给定数值的天信息,用途: 返回给定数值月份天信息 语法：DAY（值） 参数：参数值应为日期类型的单元格 实例：例如：A1单元格有日期“2013-11-5” DAY（A1）返回 值“5” 相类似的函数：HOUR（时）、MINUT（分）、SECOND(秒),日期函数,WEEKDAY: 返回某日期的星期数,用途: 在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数 语法：DAY（值，返回类型） 参数：值是要