Office2007EXCEL高级应用技巧.doc

Office 2007 应用仅供：金龙联合汽车本文档介绍了 Excel 2007 EXCEL表格数据输入技巧EXCEL2007数据表格快速列表化EXCEL2007新增“套用表格格式”功能，可将普通数据表格快速列表化EXCEL2007除了使列表化的表格更美观外，还向用户提供了表工具集，帮助用户快速完成表格常规处理任务。（见下图）EXCEL数据分列与合并技巧数据选项卡/分列/固定宽带/坐标上拉出列分隔线（如下图） 使用如下公式 = abc & 008 ；&字符连接符案例：将车牌“沪A00003”更改为“沪A-00003” 。操作方法：先拆分后合并（如图）确保业务数据的准确性（数据有效性）限制单元格中的内容，如：数字可以限制大小，字符可限制长度，选中单元格提示信息例1：销售明细表中由于“插销”误写成“插消”所以E6单元格计算出错。如果通过有效性指定B6单元格只允许一个序列（G5:G8）下拉选择，就不会出现此类错误。如何为销售明细表设定数据有效性呢？如下图1、 确定结果存放的位置B62、 选择操作方法（数据选项卡/数据工具/数据有效性）3、 选择操作数据（参数）设置有效性条件：允许一序列的内容填入单元格，序列来源于G5:GB4、 回车/确认以上还可以结合名称，如下图例2：保证发票编号唯一例3：保证身份证号码为18个字符检查表格错误（圈释无效数据）例如：在成绩表数据单元格设置有效性，并圈释无效数据（超出有效性范围的值）1、设置C2:F11区域有效性2、 “圈释无效数据”显示违反有效性规则的数据EXCEL 公式与函数应用理解数学计算四步骤（这种操作方法可以应用在任何EXCEL数学计算中）1、选定计算结果存放的位置2、选择操作方法（公式或函数）3、选定操作数据4、回车、确认函数的通用格式（掌握它，举一反三）函数名（参数，参数，）函数名：反映了此函数的功能，如：SUM 是求和功能的函数，AVERAGE是求平均值的函数，IF是判断函数（ ）：函数名后面都有一个括号，如：SUM（），这是函数的基本特征 参数 ：括号里面一定是用于计算的参数，参数可以是一个条件（逻辑表达式）或单元格范围或一个数值，参数是用于函数计算的必要数据，在出现多个参数的情况下，必须用“,”（英文逗号）隔开。例如：=SUM（A1:B2, 3） SUM是求和函数，对括号里的参数求和。即将A1到B2单元格数值求和后的结果与3相加=IF（8590,”N”,”Y”） IF 是判断函数，对括号里的参数做出判断。即条件“8590”是否成立，如果成立答案是“N”，如果不成立答案是“Y”，所以本公式的答案是Y。遵照函数的规则可以轻松找到计算方法比如：对数值3.45四舍五入并保留一位小数，我们该如何完成呢？1、 首先你需要选定答案存放的位置（如下图）2、 选择操作方法，单步计算可以找找函数，如何找? 看下图在插入函数对话框的搜索函数中输入“四舍五入”，然后点击“转到”，类似功能的函数就会罗列在“选择函数”中，接下来你需要一个一个试，对话框下侧提供了函数的简介，如果看不明白，还可以点击“有关该函数的帮助”，那里非常全面。3、 选定操作数据（参数）4、 回车/确认=ROUND（A1, 1） ROUND是用于求四舍五入的函数，括号中的A1和1都是参数，中间是英文逗号掌握方法，举一反三几个常用函数的解释IF（）判断函数IF（条件表达式，答案1，答案2） 如果条件表达式成立，答案1将成为整个公式的结果，否则答案2做为结果例1：B1=IF（A1=”,”,”Y”） 这个公式说，如果A1单元格为空，那么B1单元格为空，否则为Y。公式中Y用了双引号是因为Y是文本，文本在公式中必须加引号。另外需要注意，公式中所有符合必须为英文符号，所以这里的双引号也是英文符号。例2：如下图，统计和企管成绩都大于70分有报考资格这里出现了AND函数的嵌套，AND函数是并且功能，函数通用格式 AND（参数，参数，）SUMIF（）满足条件求和例：求“Office XP 非常Easy”的销售总量，如下图Range 用于条件查找的范围； Criteria 指定条件； Sum_range 用于真正求和的范围VLOOKUP（）列查找引用函数例：我们有一张商品价目表（G1:I4）已知商品名称“苹果”，求单价，答案存放在E2单元格中。如下图Lookup_value指定首列查找值，即已知商品“苹果”（E1单元格）Table_array指定商品价目表G1:I4Col_index_num返回商品价目表中第3列的值（Vlookup是列引用函数，这里指定的是商品价目表中需引用的列号）Range_lookup精确查找或非精确查找，一般情况填“0”=VLOOKUP(E1,G1:I4,3,0) 用E1值（已知商品“苹果”）在G1:I4（商品价目表）中的首列查找，找到后返回商品价目表第3列的值（单价值）到存放结果的位置公式复制，相对引用和绝对引用大家知道A1+B1结果是多少吗？回答是不知道，如果看了下图，你能得出结果吗？回答是5大家必须理解A1+B1本没有结果，我们见到的5 是引用A1单元格的值与引用B1单元格的值相加的结果引用有两种基本的形式，1、相对引用（随环境变化而变化） 2、绝对引用（永远不变）这里的A1和B1都是“引用”，而且都是相对引用，他们会随环境变化例如：我们复制C1单元格的公式（=A1+B1）到C2单元格中，公式会变为（=A2+B2），这是因为复制过程中增加了一行的原故，相对引用随之增加一行。如下图什么绝对引用？如上图，B1的行和列都加了$，B1被锁定了，它在公式复制时永远不变$B$1 就是绝对引用还有一种引用称为混合引用，如$B1 仅仅锁定了列，在公式复制时列号永远不变，行号仍然随环境发生变化功能键“F4”是相对引用/绝对引用/混合引用切换的快捷键组合键ctrl+ 可切换显示公式定义和使用名称给单元格范围或一个公式定义名称，名称可以使用在EXCEL数学计算中例如：对A1:B2进行求和一一累加是一种方法，但不简便，改进一下，我们使用函数，如下图这里有个问题，假设计算结果需要存放在其他工作表中，那么我们选择操作数据是就得来回切换工作表使用名称，你会发现更简单，如下图你可以先选定A1:B2，然后在名称栏中输入data，回车确认，完成名称定义。data 就是A1:B2删除和修改名称的方法：插入菜单名称定义EXCEL 2007 甘甜图表制作要点：使用堆积条形图类型制作步骤：1、 选择如下图数据，插入图表（堆积条形图）2、 选择Y轴后右击，选择“设置坐标轴格式坐标轴选项逆序类别”3、 在图表中选择“计划开始日”序列，设置无填充无边框EXCEL 2007预算和实际费用对比图表制作要点：打开 系列“预计费用”的次要坐标轴操作步骤：1、 选定数据源；插入选项卡图表柱型图簇状柱型图2、选择图表中系列“预计费用”后点击布局设置所选内容格式2、 通过数据系列格式设置如下参数：a) 开启次坐标轴：系列选项次坐标轴b) 分类间距：系列选项50%c) 无填充调整为无d) 边框颜色设置为红色e) 边框样式设置为虚线EXCEL2007 二级选择如下图：销售员（G4）选择项的值将根据部门（E4）的选择值发生变化知识点：EXCEL名称定义，EXCEL数据有效性，INDIRECT() 函数操作步骤：一、对各部门的人员名单范围定义名称，名称值需与E4单元格的选择项相同名称定义方法：选择人员区域A2:A10后，在编辑栏中输入“销售一部”，注意敲ENTER结束名称定义；以此方法完成“销售二部”“销售三部”的名称定义（如下图） 我们可以通过点击“公式”选项卡中的“名称管理器”查看和修改名称名称管理器（如下图）二、选择E4单元格，点击“数据”选项卡“数据工具”“数据有效性”在数据有效性对话框中选择允许“序列”并在来源中输入：销售一部,销售二部,销售三部 （如下图），点击确定结束操作三、选择G4单元格，点击“数据”选项卡“数据工具”“数据有效性”在数据有效性对话框中选择允许“序列”并在来源中输入公式：=INDIRECT($E$4) （如下图），选择确认结束操作提示：INDIRECT()函数功能：可将单元格的值转换为EXCEL的名称EXCEL 2007 制作供货价格对比表提示：使用EXCEL2007以上版本设置条件格式将比之前版本更容易操作步骤：1、选择B2:E42、开始选项卡样式条件格式项目选取规则值最小的10项