EXCEL使用技巧(基本应用)

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