Excel实战与提高.ppt

OFFICE系列培训-Excel实践技能和改进，前言，5级Excel用户级别，2级，知识结构，和，Excel数据处理和操作，Excel函数和公式，Excel图表和图形，ExcelVBA和宏，Excel数据分析，3级，大纲，1级，理解Excel 2级，数据处理3级，函数和公式4级，高级图表，4级。打开文件，专业修复第三方软件：RecoveryforExcel，5。知道界面、标题栏、菜单栏、工具栏、行号、列号、标签栏、状态栏吗？什么？6，自定义工具栏，1，“视图”-“工具栏”-“自定义”2，“工具”-“自定义”，7，理解选项，8，理解选项(续)，9，理解选项(续)，10，理解选项(续)，11，大纲，1，理解Excel 2，数据处理3，函数和公式4，高级图表，12，数据处理，1，自动填充2，定义名称3，数据有效性4，自定义格式5，条件格式6，小计7，合并计算8，数据透视表，13，1，自动填充，等差填充日期填充文本填充特定填充，为什么不能填充？14，2，定义名称，1，插入名称。插入-名称-定义，2。使用名称框命名，3。指定名称。插入-名称-指定，15，2。定义名称(续)、全局定义和局部定义：1。默认情况下，全局定义2和本地定义命名方法：工作表名称半角感叹号名称命名规则：1。您可以组合数字和字母，但不能从数字开始；2.R、C、R和C不能用作名称，例如，R和C代表R1C1引用样式中的行和列；3.名称不能包含空格，可以用下划线或点号代替。4.除了下行、点和反斜杠()之外，不能使用其他符号。问号是允许的，但不能用作名称的开头；5.名称字符不能超过255个；6.字母不区分大小写。16，3，数据有效性，17，3，数据有效性(续)，停止，不输入不合格数据，点击重试重新输入，点击取消取消输入。警告，可以选择是否继续输入数据，点击是强行输入不合格数据，点击否重新输入数据，点击取消取消输入。信息，可以选择是否输入数据，点击确定直接输入数据，点击取消取消输入。18，3，数据有效性(续)，菜单“数据有效性”，19，4，自定义格式，您可以为4种类型的值指定不同的格式：正数、负数、零值和文本。“大于条件值”格式；“小于条件值”格式；“等于条件值”格式；文本格式，格式代码组合结构：注意：当没有指定具体条件值时，默认条件值为0，20，4，自定义格式(续)，#，# # 0.00；红-#，# # 0.00；格林通用格式；“*”，100#，# # 0.00；0，SUM(B1:G1)，)，函数结构，嵌套函数，函数名，三个参数用逗号分隔，思考：哪些函数没有参数？30，检查公式计算结果，使用公式审核工具将评估结果分发到=IF(A10，SUM(B1:G1)，)为例，执行公式评估，1，2，31，检查公式计算结果(续)，3，4，5，32，错误类型，常见错误值和含义，33，函数和公式，1，文本/数字/日期/财务类函数解释2，实战案例综合应用，34，文本处理函数，目的：连接字符和前后字段，认为？1，连接器，35，文本处理功能，2，大小写字母转换，小写字母转换，大写字母转换，小写字母转换，小写字母转换，字符串首字母转换，小写字母转换，小写字母转换，大写字母转换，小写字母转换，大写字母转换，大写字母转换，大写字母转换，大写字母转换，大写字母转换，大写字母转换，大写字母转换，大写字母转换，大写字母转换，大写字母转换，大写字母转换，大写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母转换，小写字母=wide char(“excel”)=“Excel”，=ASC(“我爱Excel”)=“我爱Excel”，37，文本处理函数，4，生成A-zCharChar(65)=Achar(66)=Achar(67)=C .思考：如何快速填写A-Z序列号，代码(“A”)=65，字符(97)=Achar (98)=Achar (99)=C，38，文本处理功能，5。提取字符左，右和中。注意：中间功能有3个参数，而左和右只有2个参数。为什么是3而不是2？为什么不使用正确的功能？39，数字计算函数，1，计算余数，mod (3，2)=1 mod (15，4)=3 mod (21，7)=0.申请：身份证号码判断性别？如何判断数字的奇偶性？除2后还有多少余数？除了3？40，数值计算函数，2，整数函数IntTrunc，=Int(3.2)返回不超过3.2的最大整数3=Int(-3.2)返回不超过-3.2 -4的最大整数，=Trunc(3.2)返回截断小数部分的整数3=Trunc(-3.2)返回截断小数部分的整数-3，41，数值计算函数，3，舍入，差值，42，数值计算函数，3，向上舍入，向下舍入，43，日期无参数，44，日期和时间函数，实战练习：快速计算退休日期(假设：男性60岁退休，女性55岁退休)，45，日期和时间函数，2，Datedif函数目的：计算两个日期之间的天数、月数或年数。 注意：此功能在Excel帮助文件中不可用。结构：datedif(开始日期，结束日期，单位)，46，日期和时间函数，2，Datedif函数(续)，47，财务函数，概念解释：48，财务函数，案例1: 10个月内支付年利率为8%的万元贷款，并计算月还款额。=PTM(利率，nper，pv，fv)，类型)=PTM (8%/12，10，10000)=-1037.03，案例2:以5%的年利率存款15年，存款总额达到1，500，000，并计算每月存款额。=PTM(rate，nper，pv，fv)，型)=PTM (5%/12，15，0，1500000)=-5611.9，49，金融功能，案例3:某人向一家企业投资10000元，年回报率为6%，并计算3年后的累计金额。=FV(比率、nper、pmt、pv)，类型)=PTM(6%，3，0，-10000)=11910.16，案例4:两年后将需要大量支出。从现在开始，计划每月初存2000元，年利率为2.25%，利率为每月2.25%/12，计算两年后的账户金额。=FV(rate，nper，pmt，pv)，类型)=Fv (2.25%/12，24，-2000，0，1)=49141.34，50，财务函数，案例5:有人计划在5年内获得10000元，投资回报率为10%，计算现在应该投资的金额。=PV(比率、nper、pmt、fv)，类型=PV(10%，5，0，10000)=6209.21，案例6:一个人购买一份养老保险，购买成本为60，000元，20年内每月可获得500元的回报，投资回报率为8%。计算合适吗？=PV(rate，nper，pmt，fv)，类型)=PV(8%/12，20 * 12，500)=-59777.15不适当，51，函数和公式，1，文本/数字/日期/金融类函数解释2，案例综合应用，52，1，相对单元格和绝对单元格，公式引用的单元格和公式引用的单元格之间的位置关系称为单元格引用的相对性。练习：为1999，53，2做一个乘法表。参考工作表，1。对其他工作表数据的引用=工作表名称！目标单元格示例：=sheet2！A12。数据引用打开的工作簿=工作簿名称工作表名称！目标单元格示例：=Book2.xlssheet1！$A$13，数据引用未打开的工作簿=文件路径工作簿名称工作表名称！目标单元格示例：= d: excel案例Book2.xlssheet1 ！$A$1，练习：将多个工作表中的数据汇总到一个工作表中，54，3，IF函数，目的：根据指定的条件计算满足条件的选项，55，IF函数示例，示例2:为每月出勤超过22天的员工提供“全勤”备注，并显示22天以下的实际出勤天数，=If(C1=22，“全勤”，C1)，56，4，LOOKUP函数，1。VLOOKUP，HLOOKUP函数用途：按列/行查询目标值。语法格式：=VLOOKUP(要找到的值，范围，相对列，false) 2。LOOKING函数用途：模糊和区间查询语法格式：=LOOKING(要查找的值，对应的值区域，目标值区域)，LOOKUP函数要求分类间隔按升序排列。57，5，SUMIF，COUNTIF函数，根据一定的条件进行统计计算。语法：=COUNTIF(条件区域，条件)=SUMIF(条件区域，条件，值区域)，58，6，SUMPRODUCT函数，它返回相应数组或乘积的和。语法：=SUMP积(ARRAY 1) * (ARRAY 2) * )，59，7，主数组公式，数组公式用于创建一个公式，该公式可以生成多个结果或对一组可以存储在行或列中的参数进行运算。特点：它可以执行多个操作并返回一组数据结果。输入公式后，同时按下Ctrl+Shift+Enter，公式将成为数组公式。注意：将被添加到数组公式的外部以区分它。60，综合应用，案例分析，61，大纲，1，理解Excel 2，数据处理3，函数和公式4，高级图表，62，Excel图表类型，面积图xy(散点图)条形图弯曲图条形图气泡图折线图股价图饼图圆柱图圆锥图雷达图金字塔图，有73种图表类型。63，EXCEL常用图表类型，1，柱形图，64，EXCEL常用图表类型，1-1，柱形图变形，65，高级图表，工资阶梯图，66，EXCEL常用图表类型，2，条形图，67，高级图表，人口金字塔，68，高级图表，流程甘特图，69，EXCEL常用图表类型，3，饼图，70，EXCEL常用图表类型，3-1，饼图变形，71，高级图表，双饼图，72，