Excel2010高级使用技巧学习培训模板课件.ppt

1、ContentsContents 二、公式与函数的应用及案例二、公式与函数的应用及案例 1 1、Excel2010Excel2010的数据录入技巧；的数据录入技巧；2 2、自定义数字格式；、自定义数字格式；3 3、Excel2010Excel2010条件格式设置；条件格式设置；4 4、数据有效性；数据有效性；5 5、EXCELEXCEL多工作表链接、冻结与并排；多工作表链接、冻结与并排；6 6、EXCELEXCEL工作表的数据保护功能；工作表的数据保护功能；一、1.1、数据录入的相关技巧说明性，解释说明性，解释性的数据描述性的数据描述，非数值类型，非数值类型的。如，的。如，员工员工编号、姓名、

2、编号、姓名、出生年月、手出生年月、手机号码机号码常常用于各种常常用于各种数学计算的数数学计算的数据。如：工资据。如：工资、学生成绩、学生成绩、销售额等销售额等 是以是以“=”号开号开头，它可以是头，它可以是简单的数学式简单的数学式，也可以是包，也可以是包含各种含各种Excel函数的式子函数的式子数值数值文本文本公式公式 数据录入技巧数据录入技巧 灵活使用灵活使用TABTAB、ALTALT、ENTERENTER键键 录入分数录入分数：零：零+空格空格+分数分数 录入文本格式数字（邮编、电话等）录入文本格式数字（邮编、电话等）：单引号：单引号+录入内录入内容容 输入当前日期、时间：输入当前日期、时

3、间：Ctrl+;Ctrl+;和和 Ctrl+Shift+;Ctrl+Shift+;录入特殊字符（对号、平方）录入特殊字符（对号、平方）;不连续区域的填充：不连续区域的填充：Ctrl+EnterCtrl+Enter 填充柄录入（序列填充）填充柄录入（序列填充）插入批注插入批注实例实例01 01 数据录入数据录入（1）在数字后面自动添加单位（如：元、千克等）将文本字符括在双引号将文本字符括在双引号 ()()内或在单个字符前面添加一个反斜杠内或在单个字符前面添加一个反斜杠 ()()（2）输入身份证的前几位 5110251951102519（3）输入条形码前的某些重复数字 “999999999”“99

4、9999999”（4）定义小数点对齐、分数对齐?.?.?#?/?#?/?（5）自定义各种特殊日期格式 mmmmmmmm d,d,yyyyyyyy(dddddddd)December 30,2008(Tuesday)December 30,2008(Tuesday)yyyy“yyyy“年年”m“”m“月月”d“”d“日日”aaaa”aaaa 2008 2008年年1212月月3030日星期四日星期四 1.2、自动限定数据格式实例实例02 02 自定义数字格式自定义数字格式1、EXCEL表格的排版技巧；2、EXCEL条件格式1.3、Excel2010条件格式设置；v 文字的对齐与旋转文字的对齐与旋

5、转 合并单元格合并单元格 使用工具栏上的快捷按钮使用工具栏上的快捷按钮 格式格式单元格单元格对齐对齐合并单元格合并单元格 自动换行自动换行 格式格式单元格单元格对齐对齐自动换行自动换行 强制换行强制换行 ALT+ENTER 文字旋转文字旋转 格式格式单元格单元格对齐对齐文字方向文字方向v 设定边框和底纹设定边框和底纹n使用工具栏上的使用工具栏上的“边框边框”“”“底纹底纹”快捷按钮快捷按钮n格式格式单元格单元格边框边框图案图案实例实例03 03 对齐技巧与边框底纹对齐技巧与边框底纹什么是条件格式什么是条件格式 当单元格的值满足预先设定的条件时，自动将单元格设置成预先设定当单元格的值满足预先设定

6、的条件时，自动将单元格设置成预先设定的格式的格式多条件格式的规则多条件格式的规则 按从上到下的顺序判断条件并执行按从上到下的顺序判断条件并执行 最多指定三个条件最多指定三个条件根据公式自动设置格式根据公式自动设置格式 在条件格式中使用公式和函数在条件格式中使用公式和函数 实现隔行变色等的特殊格式实现隔行变色等的特殊格式 掌握掌握ROW和和MOD函数的用法函数的用法=mod(row(),2)实例实例04 04 条件格式条件格式 提高数据录入准确性提高数据录入准确性-有效性检查。什么是数据的有效性？有效性检查。什么是数据的有效性？数据有效性是一个包含帮助你在工作表中输入资料提示信息的工具.它有如下

7、功能：(1)(1)给用户提供一个选择列表给用户提供一个选择列表(2)(2)限定输入内容的类型或大小限定输入内容的类型或大小(3)(3)自定义设置自定义设置 数据有效性的设置 提供下拉式菜单的输入提供下拉式菜单的输入 设置输入提示设置输入提示 设置出错警告设置出错警告 自定义有效性条件自定义有效性条件1.4、数据的有效性实例实例05 05 数据有效性数据有效性1.5、如何处理大型工作表 在Excel多个工作表间快速切换 技巧一：用快捷键快速导航工作表 Ctrl+Page UpCtrl+Page Up 移到上一个工作表移到上一个工作表 Ctrl+Page Down Ctrl+Page Down 移

8、到下一个工作表移到下一个工作表 技巧二：右键菜单准确定位工作表 右击导航状态按钮选择所需工作表标签名右击导航状态按钮选择所需工作表标签名 冻结工作表窗口 大型工作表数据的分组、分级显示 命名单元格或单元格区域，以便能够快速返回到这些工作表区域 组合工作表，以便同时在多张工作表中输入共用数据实例实例06 06 冻结窗格冻结窗格 并排比较数据并排比较数据 两个工作簿将在同一个窗口中两个工作簿将在同一个窗口中打开。当一个工作簿滚动时，打开。当一个工作簿滚动时，Excel Excel 将使第二个工作簿自动将使第二个工作簿自动滚动相应数量的单元格滚动相应数量的单元格 同时比较两个工作簿可能很困难同时比较

9、两个工作簿可能很困难实例实例07-1 07-1 并排比较数据并排比较数据1 1实例实例07-2 07-2 并排比较数据并排比较数据2 2保护工作表、工作簿保护工作表、工作簿（1）为工作簿加密码）为工作簿加密码（2）为工作表加密码）为工作表加密码（3）设置不同区域不同密码）设置不同区域不同密码（4）设置只有部分单元格可编辑）设置只有部分单元格可编辑二、公式与函数的应用及案例公式的基本用法公式的基本用法2.1函数分类与常用函数应用举例函数分类与常用函数应用举例2.2 双击填充柄复制公式 区别公式中各种单元格引用方式 绝对引用、相对引用、混合引用、三维引用 跨工作表或工作簿的数据计算 不同工作表间数

10、据的计算、不同工作簿间数据的计算 自动求和与自动计算功能的使用2.1 公式的基本用法实例实例08 08 使用相对和绝对引用计算产品销售额与利润使用相对和绝对引用计算产品销售额与利润实例实例9-1 9-1 跨工作表数据计算跨工作表数据计算实例实例9-2 9-2 跨工作簿数据计算跨工作簿数据计算 函数：是预定义的公式，通过使用一些称为参数的特定值来安特定的顺序和结构执行计算 函数的使用方式=函数名称（参数1，参数2，参数3)函数的嵌套特性 一个函数可以引用其他函数的结果作为参数 Excel最多支持七层嵌套函数 逻辑文本类函数应用 日期时间函数应用 查询引用类函数应用 统计计数类函数应用 财务函数应

11、用2.2 函数应用与常用函数应用举例实例实例10 10 函数综合实例函数综合实例 IF ANDOR LEN MID LEFT RIGHT2.2.1逻辑文本类函数 判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值IF函数语法：语法：IF(logical_test,value_if_true,value_if_false)条件为真时条件为真时条件为假时条件为假时AND OR 函数 AND函数的作用：是对多个逻辑值进行交集计算，返回逻辑值语法：AND(logical1,logical2)OR函数的作用：是对多个逻辑值进行并集计算，返回逻辑值语法：OR(logical1,logica

12、l2,)MIDB函数作用自文字指定位置开始向右提取指定长度的字符串，操作对象均为文本，MID以字符为单位，MIDB以字节为单位截取语法：MID(text,start_num,num_bytes)LEFT返回文本字符串中的第一个或前几个字符，RIGHT返回文本字符串中的最后一个或后几年字符语法：LEFT(text,num_chars)语法：RIGHT(text,num_chars)MID(MIDB)LEFT RIGHT函数 DATE：返回代表特定日期的序列号 语法：DATE(year,month,day)NOW：返回计算机内部时钟的当前日期和时间 语法：NOW()TODAY：返回当前日期的序列编

13、号 语法：TODAY()注意：序列号是 Microsoft Excel 日期和时间计算使用的日期-时间代码。Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。2.2.2日期与时间函数：LOOKUPHLOOKUPVLOOKUP2.2.3查询和引用类函数LOOKUP 使用LOOKUP函数是在单行区域或单列区域中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值LOOKUP(lookup_value,lo

14、okup_vector,result_vector)说明：lookup_vector为第一个包含一行或一列的区域；result_vector为第二个包含一行或一列的区域，其大小必须与参数lookup_vector相同HLOOKUP实现水平查找 使用HLOOKUP函数可以在表格的首行查找指定的数值,并由此返回指定行处与该数值同一列的数值，当比较值位于数据表首行时，可使用此函数 语法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP_实现竖直查找 在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中

15、指定列处的数值。当比较值位于数据表首列时,可以使用此函数 语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)COUNTCOUNT COUNTIFCOUNTIF SUMIFSUMIF INTINT MODMOD2.2.4统计计数类函数COUNT COUNT函数的作用：返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。语法：COUNT(value1,value2,.)参数：Value1,value2,.为包含或引用各种类型数据的参数（1 到 30个

16、），但只有数字类型的数据才被计算。说明 函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。COUNTIF函数可累计满足某一条件的对象在单元格区域中出现的次数，该条件可以是数值、文本或表达式，在人事、工资和成绩统计中有广泛用途。COUNTIF函数语法：语法：COUNTIF（range,criteria)SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式。SUMIF函数语法：语法：SUMIF

17、（range,criteria,sum_range)INT INT函数的作用：将数字向下舍入到最接近的整数。语法：INT(number)参数：number是需要进行向下舍入取整的实数。返回两数相除的余数。结果的正负号与除数相同。返回两数相除的余数。结果的正负号与除数相同。语法语法MOD(number,divisor)；Number 为被除数；为被除数；Divisor 为除数。为除数。注解注解如果如果 divisor 为零，函数为零，函数 MOD 返回错误值返回错误值#DIV/0!。函数函数 MOD 可以借用函数可以借用函数 INT 来表示来表示:MOD(n,d)=n-d*INT(n/d)PMT

18、（）贷款函数；基于固定利率及等额分期付款方式，返回贷款的每期付款额。语法语法PMTPMT(raterate,npernper,pvpv,fv,type)RateRate 贷款利率。NperNper 该项贷款的付款总数。PvPv 现值，或一系列未来付款的当前值的累积和，也称为本金。FvFv 为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。TypeType 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。注解注解 PMT 返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。应确认所指定的 rate 和 n

19、per 单位的一致性。例如，同样是四年期年利率为 12%的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为 12%，nper 为 4。2.2.6财务函数及案例应用创建创建Excel图表图表3.1编辑编辑Excel图表图表3.2打印输出及其它设置打印输出及其它设置3.3 柱形图：用来显示一段时间内数据的变化或者描述各个项目之间数据比较图表 线性图：主要用于以等时间间隔显示数据的变化趋势，强调的是时间性和变动率，而不是变动量 饼形图：显示数据系列中的项目和该项目数值总和的比例关系。它只能显示一个系列的数据比例关系图表在数据分析中的作用 确定分

20、析目标 明确表格数据的取值范围 图表向导 使用一键成图创建图表3.1 创建分析图表实例实例11 11 图表类型汇总图表类型汇总 常规编辑技巧 改变图表大小改变图表大小 移动图表位置移动图表位置 图表内其他对象编辑图表内其他对象编辑 图表的特殊编辑 向图表添加数据向图表添加数据 删除图表内数据删除图表内数据v 文字数据修饰文字数据修饰v 线和阴影修饰线和阴影修饰v 背景修饰背景修饰v 设置图表的序列格式设置图表的序列格式v 综合案例分析综合案例分析 页面设置 工作表的页眉、页脚 分页符和打印选项 工作表参数设置 设置打印区域 自动打印统一表头 打印指定页面 打印连续页码 一次打印多个工作表 使某

21、个工作表不能被打印3.3打印输出及其它设置数据的筛选数据的筛选4.2数据的分类汇总数据的分类汇总4.3数据透视表数据透视表4.44.1数据的数据的排序排序 4.14.1 排序数据 排序时，一般不要选择数据范围，以防止破坏数据的结构 针对多列进行排列使用排序对话框进行相应操作 简单排序 多字段排序 按行排序 自定义序列排序实例实例12 12 数据排序数据排序4.2 数据的筛选分析 自动筛选 高级筛选 条件区域的设置原则 工作表中指定一个空白区域作条件区域 条件区域第一行是条件名(字段名)行，第二行是输入的条件 条件区域中不能包含有空行或空列，单元格中不能包含空格 字符字段的条件可以使用通配符*和?注意：在具体写条件时，我们要分析好条件之间是与关系还是或关系。若是与关系，条件要写到同一行中，若是或关系，条件要写到不同行中实例实例13 13 高级筛选高级筛选 分类汇总之前必须之操作 注意：使用“分类汇总”功能时，首先要确定分类的依据，然后按选定的分类依据将数据排序！建立分类汇总 使用分级功能查看汇总结果4.3 数据分类汇总实例实例14 14 分类汇总分类汇总4.4 数据透视表l透视表的布局及更新 l数据刷新 l添加字段l数据的分组 l格式化报表 l创建数据透视图实例实例15 15 数据透视表数据透视表