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