EXCEL使用技巧(基本应用).ppt

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