《EXCEL使用技巧(基本应用).ppt》由会员分享，可在线阅读，更多相关《EXCEL使用技巧(基本应用).ppt（38页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、EXCEL 使用技巧使用技巧研发流程管理部研发流程管理部nEXCEL格式n常用函数n数据透视表格式l锁定单元格,保护工作表EXCEl中,我们经常使用计算公式,为了防止用户误操作破坏公式.或防止用户随意修改表格,我们需要锁定单元格并保护工作表这些单元格是公式计算的,需要锁定这些单元格是用户输入的,不能锁定Excel单元格缺省是锁定的,因此首先解除对需要用户编辑的单元格的锁定,然后保护工作表;(必要时可输入保护密码)当试图修改锁定的单元格时,系统提示无法修改设置锁定：单元格格式保护锁定；设置保护:工具保护保护工作表l跨行居中设置：单元格格式对齐水平对齐跨列居中标题在这个中间,但这里的单元格却是分散
2、的,我选中了这个单元格,但为什么没有数据呢?原因就在于跨列居中?没有任何东西其实标题输入在这个单元格里面l插入文件显示为图标缺省情况下,在excel中插入文件时,只要是MS兼容的文件它都会直接显示文件内容,例如Office系列的文件图片、文本文件等。但有时后我们希望它显示为一个图标设置：插入对象由文件创建；注意选项：显示为图标、连接到文件为什么插入的这个Project文件会把文件内容显示在这里啊?更多的时候我们希望它显示成这样全局设置：工具选项视图批注个别设置：右击单元格显示/隐藏批注l批注显示这个批注为什么总是显示在这里？这个批注为什么只有鼠标移动到这个单元格时才显示？其实单元格属性中就可以
3、设置该单元格是否需要斜线（反、正都有）l表格中的斜线这条斜线是怎么来的啊？画的？设置：设置单元格格式边框将单元格的数据有效性设置为序列可以达到目的；设置：数据有效性设置允许序列l序列下拉选择通过这样的下拉框选择可以减少输入量，同时还可以保证不会填的各式各样序列的来源，常见的有3种：直接输入（用半角逗号隔开）；来源与当前sheet中的一个区域；来之其他sheet中的数据（比较复杂，后面介绍indirect函数时介绍）AltEnter（虽然很简单，但确实有些人不知道哦）l单元格内换行设置：数据分组级分级显示分组/取消分组注意：分组折叠、打开的加号缺省在下面，可以通过：数据分组级分级显示设置，取消“
4、明细数据在下方”前面的勾勾。l分组显示通过单元格格式图案可以设置更多颜色，其中我们常用的很浅的黄色就在这里才能看到。l单元格的颜色常用函数先介绍几个基本概念：Range区域：列，用字母表示，范围AIV；行，用数字表示，范围165535；例如：C4表示第C列，第4行所在单元格；C4:D6表示2列3行的一个区域（用“:”隔开）；绝对引用与相对引用：相对引用，如C4或C4:D6，公式中使用相对引用时，拖动使其他单元格也采用同样的公式时，横向上，列会自动增长，纵向拖动时，行会自动增长绝对引用，行列号前面加“$”，例如$C4、$C$4，绝对引用时，拖动公式时，行/列不会自动增长这只是一种方法，即A1样式
5、，还有一种方式是R1C1样式，例如R5C6，表示第5行第6列nIF函数IF(logical_test,value_if_true,value_if_false)Logical_test判断条件，可以是结果为TRUE或FALSE的任意值或表达式。Value_if_truelogical_test为TRUE时返回的值。Value_if_falselogical_test为FALSE时返回的值。项目最终得分的公式为：IF(SUM(D18:D20)32或apples。nIndirect函数函数INDIRECT(ref_text,a1)返回由文本字符串指定的引用。Ref_text为对单元格的引用，此单元
6、格可以包含A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用如果a1为TRUE或省略，ref_text被解释为A1-样式的引用。如果a1为FALSE，ref_text被解释为R1C1-样式的引用。实例实例1：使用：使用INDIRECT实现跨实现跨sheet的序列的序列步骤1：将序列的源数据定义一个名字，方法如下：1、选定这个区域2、在这里输入一个名字回车（名字最好有一定含义）使用使用INDIRECT实现跨实现跨sheet的序列的序列步骤2：在目的地定义序列，并使用INDIRECT函数引用定义的名字：效果就是这样：实例实例2：使用：使用INDIRECT实现跨多级序
7、列实现跨多级序列多极序列即，两个下拉选择框，前面一个选定后，决定了后面一个的选择范围如下面这个实例，部门选定了“软件部”，则项目这一列只列出软件部的项目步骤1：定义第一级序列的名字，即部门步骤2：依此定义第二级序列的名字，即项目区域的名字产品线所属项目的区域命名为“产品线”，可是有特殊含义的哦注意：将项目区域的名字定义为所属部门的名称步骤3：定义第一级下拉框这里就是用名字引用，与上一个实例是一样的步骤4：定义第二级下拉框窍门就在这里了：第二级引用indirec传入的参数是前面一级所在单元格，即B4。传B4，实际就是把B4的值传了进去现在知道为什么软件部的项目的名字要定义为“软件部了”吧！nIN
8、DEXINDEX(array,row_num,column_num)返回数组中指定单元格或单元格数组的数值。INDEX(reference,row_num,column_num,area_num)返回引用中指定单元格区域的引用。row_num相对行数；column_num相对列数下面这个例子中，评分标准的公式是：=IF(A32“”,INDEX(考核标准!$C$20:考核标准!$C$42,F32,1),“”)其中外面套的IF只是为了让保证前面为空的时候，这里也显示空，重点看INDEX(考核标准!$C$20:考核标准!$C$42,F32,1)它表示引用“考核标准!$C$20:考核标准!$C$42”
9、这个区域中的一个单元格，行值来自F32，列值固定是1；这个引用的作用在于将“评分标准”先取过来，这样算总分就容易了。行值F32实际上是用mach算出来的，后面再介绍nMATCHMATCH(lookup_value,lookup_array,match_type)返回在指定方式下与指定数值匹配的数组中元素的相应位置接着INDEX函数所提的例子，我们看看“匹配类型”，他的公式是=IF(A32“”,MATCH(A32,考核标准!$A$20:考核标准!$A$42,0),“”)，外面的IF就不讲了，直接看MATCH它实际上是在查找前面所选的类型在考核标准!$A$20:考核标准!$A$42,0这个区域中的
10、位置，即相对行值所以改行的值其实就是所选类型在考核标准的相对行数，所以前面提到的评分标准的index的行为什么是F32就清楚了吧nSUMSUM(number1,number2,.)返回某一单元格区域中所有数字之和，number1,number2,.为1到30个需要求和的参数。我们来看看它的高级应用我们来看看它的高级应用=SUM(IF(产品一线产品一线!$W$16:$W$100=1)*(产品一线产品一线!$T$16:$T$100=30%),1,0)nSUMPRODUCTSUMPRODUCT(array1,array2,array3,.)在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和我
11、们来看看它的高级应用我们来看看它的高级应用=SUMPRODUCT(产品一线!$W$16:$W$100=1)*(产品一线!$O$16:$O$100)下面这个例子演示了下面这个例子演示了SUM和和SUMPRODUCT高级一点高级一点的应用的应用数据透视表步骤1：创建数据透视表步骤2：选定原始数据步骤3：选择完成，效果如下步骤4：拖分类字段（可以多个，注意摆放的前后位置）一个分类两个分类步骤3：拖入要统计的数据项（可以多个）注意：数据项的统计方式有计数、求和等多种。当只有一个数据项是，可以在左上角直接点击修改统计方式，如果有多个数据项，则需要右击数据项，选择“字段设置”进行修改效果就是这样注：如果在列字段中拖入字段，还可以对列进行分类统计Thank you！