Excel2007高级使用技巧

1、 二：公式与函数的应用及案例1、Excel2007的数据录入技巧；2、自定义数字格式；3、Excel2007条件格式设置；4、数据有效性及案例；5、EXCEL多工作表链接、冻结与并排；6、EXCEL工作表的数据保护功能；说明性，解释性的数据描述，非数值类型的。如， 员工编号、姓名、出生年月、手机号码常常用于各种数学计算的数据。如：工资、学生成绩、销售额等 是以“=”号开头，它可以是简单的数学式，也可以是包含各种Excel函数的式子数值文本公式数据录入技巧 灵活使用TAB、ALT、ENTER键 录入分数：零+空格+分数 录入文本格式数字（邮编、电话等） ：单引号+录入内容 输入当前日期、时间：C

2、trl+; 和 Ctrl+Shift+; 录入特殊字符（对号、平方）; 不连续区域的填充：Ctrl+Enter 填充柄录入（序列填充） 插入批注数据录入技巧 引入动态数据导入WEB资料 导入外部数据 大型工作表中单元格（区域）选择 使用智能标记进行输入 使用工作表组加快多工作表创建和数据输入速度 数据朗读7快捷键实现操作Ctrl+鼠标滚轮缩放当前视图Ctrl+Shift+方向键选择连续系列数据 Ctrl+A选择全部数据Ctrl+; / Ctrl+Shift+;输入当前日期/时间Shift+F11新建工作表Alt+Enter在单元格内强制换行快捷键功能描述Shift+箭头键通过按箭头键可逐个单元

3、格地扩展选择范围。Ctrl+箭头键移到数据的边缘。F5打开“定位”对话框。F2编辑活动单元格，并将插入点放到单元格内容末尾。Ctrl+End将插入点移到工作表中最后一个使用的单元格。Ctrl+Home将插入点移动到工作表的开头。Page Up向上移动一屏。Page Down向下移动一屏。Alt+Page Up向左移动一屏。Alt+Page Down向右移动一屏。Ctrl+Shift+Page Up选中当前工作表和上一个工作表。Ctrl+Shift+Page Down选中当前工作表和下一个工作表。（1）在数字后面自动添加单位（如：元、千克等） 将文本字符括在双引号 ( ) 内或在单个字符前面添加

4、一个反斜杠 ()（2）输入身份证的前几位 51102519（3）输入条形码前的某些重复数字 “999999999”（4）定义小数点对齐、分数对齐 ?.? # ?/?（5）自定义各种特殊日期格式 mmmm d, yyyy (dddd) December 30,2008 (Tuesday) yyyy“年年”m“月月”d“日日”aaaa 2008年12月30日星期四 选择性粘贴 公式 数值 格式 批注 转置 粘贴链接格式刷1、EXCEL表格的排版技巧；2、EXCEL条件格式v 文字的对齐与旋转 合并单元格 使用工具栏上的快捷按钮 格式单元格对齐合并单元格 自动换行 格式单元格对齐自动换行 强制换行

5、ALT+ENTER 文字旋转 格式单元格对齐文字方向v 设定边框和底纹n使用工具栏上的“边框”“底纹”快捷按钮n格式单元格边框图案http:/什么是条件格式 当单元格的值满足预先设定的条件时，自动将单元格设置成预先设定的格式多条件格式的规则 按从上到下的顺序判断条件并执行 最多指定三个条件根据公式自动设置格式 在条件格式中使用公式和函数 实现隔行变色等的特殊格式 掌握ROW和MOD函数的用法 = =mod(row(),2) 案例简析提高数据录入准确性-有效性检查。什么是数据的有效性？ 数据有效性是一个包含帮助你在工作表中输入资料提示信息的工具. 它有如下功能： (1) 给用户提供一个选择列表

6、(2) 限定输入内容的类型或大小 (3) 自定义设置 数据有效性的设置 提供下拉式菜单的输入 设置输入提示 设置出错警告 自定义有效性条件在Excel多个工作表间快速切换 技巧一：用快捷键快速导航工作表 Ctrl+Page Up 移到上一个工作表 Ctrl+Page Down 移到下一个工作表 技巧二：右键菜单准确定位工作表 右击导航状态按钮选择所需工作表标签名冻结工作表窗口大型工作表数据的分组、分级显示命名单元格或单元格区域，以便能够快速返回到这些工作表区域组合工作表，以便同时在多张工作表中输入共用数据并排比较数据 两个工作簿将在同一个窗口中打开。当一个工作簿滚动时，Excel 将使第二个工

7、作簿自动滚动相应数量的单元格 工作表间的数据链接 创建引导型页面 创建链接按钮 创建超级链接及编辑超级链接 设置工作表及行号列标的隐藏同时比较两个工作簿可能很困难同时比较两个工作簿可能很困难保护工作表、工作簿（1）为工作簿加密码（2）以隐藏方式保存工作簿（3）为工作表加密码（4）隐藏工作表，并阻止其他人随意取消隐藏（5）设置不同区域不同密码（6）设置只有部分单元格可编辑（7）隐藏敏感公式设置表格的锁定保护 将允许用户更改的单元格区域设置为解除锁定状态 隐藏单元格的公式 先选中全表格式单元格保护-把选定和隐藏上的两个勾去掉 。再选中你要隐藏的单元格，格式单元格保护-把选定和隐藏前的两个勾打上 。

8、再选工具-保护-保护工作表 将工作表进行保护的设置(加密)创建Excel图表2.1编辑Excel图表2.2打印输出及其它设置2.3柱形图：用来显示一段时间内数据的变化或者描述各个项目之间数据比较图表线性图：主要用于以等时间间隔显示数据的变化趋势，强调的是时间性和变动率，而不是变动量饼形图：显示数据系列中的项目和该项目数值总和的比例关系。它只能显示一个系列的数据比例关系组合图确定分析目标明确表格数据的取值范围图表向导使用一键成图创建图表常规编辑技巧 改变图表大小 移动图表位置 图表内其他对象编辑图表的特殊编辑 向图表添加数据 删除图表内数据v 文字数据修饰v 线和阴影修饰v 背景修饰v 设置图表

9、的序列格式v 综合案例分析页面设置 工作表的页眉、页脚分页符和打印选项 工作表参数设置 设置打印区域 自动打印统一表头 打印指定页面 打印连续页码 一次打印多个工作表 使某个工作表不能被打印公式的基本用法3.1函数分类与常用函数应用举例3.2双击填充柄复制公式区别公式中各种单元格引用方式 绝对引用 相对引用 混合引用 三维引用跨工作表或工作簿的数据计算 不同工作表间数据的计算 不同工作簿间数据的计算自动求和与自动计算功能的使用 函数：是预定义的公式，通过使用一些称为参数的特定值来安特定的顺序和结构执行计算函数的使用方式=函数名称（参数1，参数2，参数3)函数的嵌套特性 一个函数可以引用其他函数

10、的结果作为参数 Excel最多支持七层嵌套函数逻辑文本类函数应用日期时间函数应用查询引用类函数应用统计计数类函数应用其他常用函数应用财务函数应用IFANDORLENMID LEFT RIGHTCONCATENATEEXACT判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值语法：语法：IF(logical_test,value_if_true,value_if_false)条件为真时条件为真时条件为假时条件为假时AND函数的作用：是对多个逻辑值进行交集计算，返回逻辑值语法：AND(logical1,logical2)OR函数的作用：是对多个逻辑值进行并集计算，返回逻辑值语法：O

11、R(logical1,logical2,)MIDB函数作用自文字指定位置开始向右提取指定长度的字符串，操作对象均为文本，MID以字符为单位，MIDB以字节为单位截取语法：MID (text,start_num,num_bytes)LEFT返回文本字符串中的第一个或前几个字符，RIGHT返回文本字符串中的最后一个或后几年字符语法：LEFT(text,num_chars)语法：RIGHT(text,num_chars)CONCATENATE函数的作用是将几个字符串合并为一个文本字符串语法：CONCATENATE(text1,text2,)也可使用“”运算符代替实现文本的合并EXACT函数的作用：测

12、试两个字符串是否完全相同。如果它们完全相同，则返回TRUE;否则返回FALSE。EXACT函数能区分大小写，但忽略格式上的差异语法：EXACT(text1，text2)参数：Text1是待比较的第一个字符串，Text2是待比较的第二个字符串DATE：返回代表特定日期的序列号 语法：DATE(year,month,day)NOW：返回计算机内部时钟的当前日期和时间 语法：NOW( )TODAY：返回当前日期的序列编号 语法：TODAY( )注意：序列号是 Microsoft Excel 日期和时间计算使用的日期-时间代码。Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况

13、下，1900 年 1 月 1 日的序列号是 1 ，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。LOOKUPHLOOKUPVLOOKUPINDEXMATCH使用LOOKUP函数是在单行区域或单列区域中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值LOOKUP(lookup_value,lookup_vector,result_vector)说明：lookup_vector为第一个包含一行或一列的区域；result_vector为第二个包含一行或一列的区域，其大小必须与参数lookup_vector相同使用HLO

14、OKUP函数可以在表格的首行查找指定的数值,并由此返回指定行处与该数值同一列的数值，当比较值位于数据表首行时，可使用此函数语法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时,可以使用此函数语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)INDEX函数用于返回表或者区域中的值或值的引用语法：INDEX(reference,row_num,c

15、olumn_num,area_num)ref:表示对一个或多个单元格区域的引用，如是多个区域需用括号括起来；rowcolumn：表示引用中某行列的序号；area：表示选择引用中的一个区域（对于多区域引用）MATCH用来返回在指定方式下与指定数值相匹配的数组中元素的相应位置(返回的不是数值本身)语法：MATCH(lookup_value,lookup_array,match_type)L_value：为需要在数据表中查找的数值L_array：表示包含参数L_value的连续单元格区域M_type：指明如何查找值，可取-1、0或1三个值：-1：查找大于或等于Value的最小值，array须降序排列

16、0：查找等于Value的第一个值，array可按任意顺序排列1：查找小于或等于Value的最大值，array须按升序排列COUNTCOUNTIFSUMIFFREQUENCYRANKCOUNT函数的作用：返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。语法：COUNT(value1,value2,.)参数：Value1, value2, . 为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数据才被计算。说明 函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转

17、换成数字的文字将被忽略。 如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。COUNTIF函数可累计满足某一条件的对象在单元格区域中出现的次数，该条件可以是数值、文本或表达式，在人事、工资和成绩统计中有广泛用途。语法：语法： COUNTIF（range,criteria)SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式。语法：语法： SUMIF（range,criteria,sum_range)以一列垂直数组返回某个区域中数据的频率分布。例如，使用该函数可以计算在给定的分数范围内测验分数的个数。由于函

18、数返回一个数组结果，所以必须以数组公式的形式输入公式。语法：FREQUENCY(data_array,bins_array)RANK函数返回一个数字在数字列表中的排位语法：RANK(number,ref,order)order：为0或省略，返回对ref降序排列的列表INTMODROUNDRANKINT函数的作用：将数字向下舍入到最接近的整数。语法：INT(number)参数：number是需要进行向下舍入取整的实数。返回两数相除的余数。结果的正负号与除数相同。语法MOD(number,divisor)Number 为被除数。Divisor 为除数。注解如果 divisor 为零，函数 MOD

19、返回错误值 #DIV/0!。函数 MOD 可以借用函数 INT 来表示:MOD(n, d) = n - d*INT(n/d) ROUND函数的作用：按指定位数四舍五入某个数字。语法：ROUND(number，num_digits)参数：Number是需要四舍五入的数字;Num_digits为指定的位数，Number按此位数进行处理注意:如果num_digits大于0，则四舍五入到指定的小数位;如果num_digits等于0，则四舍五入到最接近的整数;如果num_digits小于0，则在小数点左侧按指定位数四舍五入。RANK函数返回一个数字在数字列表中的排位语法：RANK(number,ref,

20、order)order：为0或省略，返回对ref降序排列的列表PMT（）贷款函数；基于固定利率及等额分期付款方式，返回贷款的每期付款额。语法PMT(rate,nper,pv,fv,type) Rate 贷款利率。 Nper 该项贷款的付款总数。 Pv 现值，或一系列未来付款的当前值的累积和，也称为本金。 Fv 为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。 Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。注解 PMT 返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。 应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12% 的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果