Excel2010高级使用技巧.ppt

Excel2010使用技巧1Contents一、数据录入和格式化工作表一、数据录入和格式化工作表一、数据录入和格式化工作表一、数据录入和格式化工作表二、公式与函数的应用及案例二、公式与函数的应用及案例三、图表及打印输出及其它设置三、图表及打印输出及其它设置三、图表及打印输出及其它设置三、图表及打印输出及其它设置四、数据分析及管理四、数据分析及管理四、数据分析及管理四、数据分析及管理1、Excel2010的数据录入技巧；的数据录入技巧；2、自定义数字格式；、自定义数字格式；3、Excel2010条件格式设置；条件格式设置；4、数据有效性；数据有效性；5、EXCEL多工作表链接、冻结与并排；多工作表链接、冻结与并排；6、EXCEL工作表的数据保护功能；工作表的数据保护功能；一、数据录入和格式化工作表1.1、数据录入的相关技巧说明性，解释说明性，解释性的数据描述，性的数据描述，非数值类型的。非数值类型的。如，如，员工编号、员工编号、姓名、出生年姓名、出生年月、手机号码月、手机号码常常用于各种常常用于各种数学计算的数数学计算的数据。如：工资、据。如：工资、学生成绩、销学生成绩、销售额等售额等是以是以“=”号开号开头，它可以是头，它可以是简单的数学式，简单的数学式，也可以是包含也可以是包含各种各种Excel函函数的式子数的式子数值数值文本文本公式公式数据录入技巧数据录入技巧灵活使用灵活使用TAB、ALT、ENTER键键录入分数录入分数：零：零+空格空格+分数分数录入文本格式数字（邮编、电话等）录入文本格式数字（邮编、电话等）：单引号：单引号+录入内容录入内容输入当前日期、时间：输入当前日期、时间：Ctrl+;和和Ctrl+Shift+;录入特殊字符（对号、平方）录入特殊字符（对号、平方）;不连续区域的填充：不连续区域的填充：Ctrl+Enter填充柄录入（序列填充）填充柄录入（序列填充）插入批注插入批注实例实例01数据录入数据录入2021/5/275 案例分析案例分析（1）在数字后面自动添加单位（如：元、千克等）将文本字符括在双引号将文本字符括在双引号()内或在单个字符前面添加一个反斜杠内或在单个字符前面添加一个反斜杠()（2）输入身份证的前几位51102519（3）输入条形码前的某些重复数字“999999999”（4）定义小数点对齐、分数对齐?.?#?/?（5）自定义各种特殊日期格式mmmmd,yyyy(dddd)December30,2008(Tuesday)yyyy“年年”m“月月”d“日日”aaaa2008年年12月月30日星期四日星期四 1.2、自动限定数据格式实例实例02自定义数字格式自定义数字格式1、EXCEL表格的排版技巧；2、EXCEL条件格式1.3、Excel2010条件格式设置；1.3.1、单元格格式设置v文字的对齐与旋转文字的对齐与旋转合并单元格合并单元格使用工具栏上的快捷按钮使用工具栏上的快捷按钮格式格式单元格单元格对齐对齐合并单元格合并单元格自动换行自动换行格式格式单元格单元格对齐对齐自动换行自动换行强制换行强制换行ALT+ENTER文字旋转文字旋转格式格式单元格单元格对齐对齐文字方向文字方向v设定边框和底纹设定边框和底纹n使用工具栏上的使用工具栏上的“边框边框”“底纹底纹”快捷按钮快捷按钮n格式格式单元格单元格边框边框图案图案实例实例03对齐技巧与边框底纹对齐技巧与边框底纹2021/5/2781.3.2、条件格式管理器设置条件格式管理器设置什么是条件格式什么是条件格式当单元格的值满足预先设定的条件时，自动将单元格设置成预先设定的当单元格的值满足预先设定的条件时，自动将单元格设置成预先设定的格式格式多条件格式的规则多条件格式的规则按从上到下的顺序判断条件并执行按从上到下的顺序判断条件并执行最多指定三个条件最多指定三个条件根据公式自动设置格式根据公式自动设置格式在条件格式中使用公式和函数在条件格式中使用公式和函数实现隔行变色等的特殊格式实现隔行变色等的特殊格式掌握掌握ROW和和MOD函数的用法函数的用法=mod(row(),2)实例实例04条件格式条件格式2021/5/279提高数据录入准确性提高数据录入准确性-有效性检查。什么是数据的有效性？有效性检查。什么是数据的有效性？数据有效性是一个包含帮助你在工作表中输入资料提示信息的工具.它有如下功能：(1)给用户提供一个选择列表给用户提供一个选择列表(2)限定输入内容的类型或大小限定输入内容的类型或大小(3)自定义设置自定义设置数据有效性的设置提供下拉式菜单的输入提供下拉式菜单的输入设置输入提示设置输入提示设置出错警告设置出错警告自定义有效性条件自定义有效性条件1.4、数据的有效性实例实例05数据有效性数据有效性1.5、如何处理大型工作表在Excel多个工作表间快速切换技巧一：用快捷键快速导航工作表Ctrl+PageUp 移到上一个工作表移到上一个工作表Ctrl+PageDown移到下一个工作表移到下一个工作表 技巧二：右键菜单准确定位工作表右击导航状态按钮选择所需工作表标签名右击导航状态按钮选择所需工作表标签名冻结工作表窗口大型工作表数据的分组、分级显示命名单元格或单元格区域，以便能够快速返回到这些工作表区域组合工作表，以便同时在多张工作表中输入共用数据实例实例06冻结窗格冻结窗格2021/5/2711并排比较数据并排比较数据两个工作簿将在同一个窗口中两个工作簿将在同一个窗口中打开。当一个工作簿滚动时，打开。当一个工作簿滚动时，Excel将使第二个工作簿自动将使第二个工作簿自动滚动相应数量的单元格滚动相应数量的单元格 同时比较两个工作簿可能很困难同时比较两个工作簿可能很困难同时比较两个工作簿可能很困难同时比较两个工作簿可能很困难实例实例07-1并排比较数据并排比较数据1实例实例07-2并排比较数据并排比较数据22021/5/27121.6、数据保护功能、数据保护功能保护工作表、工作簿保护工作表、工作簿（1）为工作簿加密码）为工作簿加密码（2）为工作表加密码）为工作表加密码（3）设置不同区域不同密码）设置不同区域不同密码（4）设置只有部分单元格可编辑）设置只有部分单元格可编辑2021/5/2713二、公式与函数的应用及案例公式的基本用法公式的基本用法2.1函数分类与常用函数应用举例函数分类与常用函数应用举例2.2双击填充柄复制公式区别公式中各种单元格引用方式绝对引用、相对引用、混合引用、三维引用跨工作表或工作簿的数据计算不同工作表间数据的计算、不同工作簿间数据的计算自动求和与自动计算功能的使用2.1 公式的基本用法实例实例08使用相对和绝对引用计算产品销售额与利润使用相对和绝对引用计算产品销售额与利润实例实例9-1跨工作表数据计算跨工作表数据计算实例实例9-2跨工作簿数据计算跨工作簿数据计算函数：是预定义的公式，通过使用一些称为参数的特定值来安特定的顺序和结构执行计算函数的使用方式=函数名称（参数1，参数2，参数3)函数的嵌套特性一个函数可以引用其他函数的结果作为参数Excel最多支持七层嵌套函数逻辑文本类函数应用日期时间函数应用查询引用类函数应用统计计数类函数应用财务函数应用2.2 函数应用与常用函数应用举例实例实例10函数综合实例函数综合实例IFANDORLENMIDLEFTRIGHT2.2.1逻辑文本类函数判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值IF函数语法：语法：IF(logical_test,value_if_true,value_if_false)条件为真时条件为真时条件为假时条件为假时AND OR 函数AND函数的作用：是对多个逻辑值进行交集计算，返回逻辑值语法：AND(logical1,logical2)OR函数的作用：是对多个逻辑值进行并集计算，返回逻辑值语法：OR(logical1,logical2,)2021/5/2719MIDB函数作用自文字指定位置开始向右提取指定长度的字符串，操作对象均为文本，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：返回当前日期的序列编号语法：TODAY()注意：序列号是MicrosoftExcel日期和时间计算使用的日期-时间代码。MicrosoftExcel可将日期存储为可用于计算的序列号。默认情况下，1900年1月1日的序列号是1，而2008年1月1日的序列号是39448，这是因为它距1900年1月1日有39448天。2.2.2日期与时间函数：LOOKUPHLOOKUPVLOOKUP2.2.3查询和引用类函数LOOKUP使用LOOKUP函数是在单行区域或单列区域中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值LOOKUP(lookup_value,lookup_vector,result_vector)说明：lookup_vector为第一个包含一行或一列的区域；result_vector为第二个包含一行或一列的区域，其大小必须与参数lookup_vector相同2021/5/2723HLOOKUP实现水平查找使用HLOOKUP函数可以在表格的首行查找指定的数值,并由此返回指定行处与该数值同一列的数值，当比较值位于数据表首行时，可使用此函数语法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)2021/5/2724VLOOKUP_实现竖直查找在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时,可以使用此函数语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)2021/5/2725COUNTCOUNTIFSUMIFINTMOD2.2.4统计计数类函数COUNT COUNT函数的作用：返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数。语法：COUNT(value1,value2,.)参数：Value1,value2,.为包含或引用各种类型数据的参数（1到30个），但只有数字类型的数据才被计算。说明函数COUNT在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。2021/5/2727COUNTIF函数可累计满足某一条件的对象在单元格区域中出现的次数，该条件可以是数值、文本或表达式，在人事、工资和成绩统计中有广泛用途。COUNTIF函数语法：语法：COUNTIF（range,criteria)SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式。SUMIF函数语法：语法：SUMIF（range,criteria,sum_range)INT INT函数的作用：将数字向下舍入到最接近的整数。语法：INT(number)参数：number是需要进行向下舍入取整的实数。MOD 返回两数相除的余数。结果的正负号与除数相同。返回两数相除的余数。结果的正负号与除数相同。语法语法MOD(number,divisor)；Number为被除数；为被除数；Divisor为除数。为除数。注解注解如果如果divisor为零，函数为零，函数MOD返回错误值返回错误值#DIV/0!。函数函数MOD可以借用函数可以借用函数INT来表示来表示:MOD(n,d)=n-d*INT(n/d)2021/5/2730PMT（）贷款函数；基于固定利率及等额分期付款方式，返回贷款的每期付款额。语法语法PMT(rate,nper,pv,fv,type)Rate贷款利率。Nper该项贷款的付款总数。Pv现值，或一系列未来付款的当前值的累积和，也称为本金。Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零，也就是一笔贷款的未来值为零。Type数字0或1，用以指定各期的付款时间是在期初还是期末。注解注解PMT返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。应确认所指定的rate和nper单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。2.2.6财务函数及案例应用三、图表及打印输出及其它设置创建创建Excel图表图表3.1编辑编辑Excel图表图表3.2打印输出及其它设置打印输出及其它设置3.3柱形图：用来显示一段时间内数据的变化或者描述各个项目之间数据比较图表线性图：主要用于以等时间间隔显示数据的变化趋势，强调的是时间性和变动率，而不是变动量饼形图：显示数据系列中的项目和该项目数值总和的比例关系。它只能显示一个系列的数据比例关系图表在数据分析中的作用确定分析目标明确表格数据的取值范围图表向导使用一键成图创建图表3.1 创建分析图表实例实例11图表类型汇总图表类型汇总常规编辑技巧改变图表大小改变图表大小移动图表位置移动图表位置图表内其他对象编辑图表内其他对象编辑图表的特殊编辑向图表添加数据向图表添加数据删除图表内数据删除图表内数据3.2 图表的编辑、美化技巧v文字数据修饰文字数据修饰v线和阴影修饰线和阴影修饰v背景修饰背景修饰v设置图表的序列格式设置图表的序列格式v综合案例分析综合案例分析页面设置 工作表的页眉、页脚分页符和打印选项 工作表参数设置 设置打印区域自动打印统一表头打印指定页面 打印连续页码一次打印多个工作表使某个工作表不能被打印3.3打印输出及其它设置四、数据分析及管理数据的筛选数据的筛选4.2数据的分类汇总数据的分类汇总4.3数据透视表数据透视表4.44.1数据的数据的排序排序4.14.1 排序数据排序时，一般不要选择数据范围，以防止破坏数据的结构针对多列进行排列使用排序对话框进行相应操作简单排序多字段排序按行排序自定义序列排序实例实例12数据排序数据排序2021/5/27384.2 数据的筛选分析 自动筛选高级筛选条件区域的设置原则工作表中指定一个空白区域作条件区域条件区域第一行是条件名(字段名)行，第二行是输入的条件条件区域中不能包含有空行或空列，单元格中不能包含空格字符字段的条件可以使用通配符*和?注意：在具体写条件时，我们要分析好条件之间是与关系还是或关系。若是与关系，条件要写到同一行中，若是或关系，条件要写到不同行中实例实例13高级筛选高级筛选2021/5/2739分类汇总之前必须之操作注意：使用“分类汇总”功能时，首先要确定分类的依据，然后按选定的分类依据将数据排序！建立分类汇总使用分级功能查看汇总结果4.3 数据分类汇总实例实例14分类汇总分类汇总4.4 数据透视表l透视表的布局及更新l数据刷新l添加字段l数据的分组l格式化报表l创建数据透视图实例实例15数据透视表数据透视表2021/5/2741潘潘潘潘 志志志志 飞飞飞飞 PP42