数据测试之Excel篇
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
格式：=Mid（字符串，开始位数，截取位数） 实例：=LEFT（A1,4） 从左截取4位 = RIGHT （A1,4） 从右边截取4位 =MID（A1,3,5） 从ID 第三位开始截取5位 =TRIM（A1） 截取所有的空格
7、IF
条件判断
1
实例：=IF（A1=B1,0,1） =IF（OR（A1=0,B1=0）,””,1） =IF（A1-B1>0.1，”绿色”,”红色”） =IF （ A1>B1,”绿色”， IF （ AND （ A1>C1 ， A1<B1 ）,”黄色”， IF （ A1<C1,”红色”,”异 常”）））
1
9、VLOOKUP
多表关联
必须有一列为主键，若没有，可使用&、CONCATENATE将多列进行连接，创建一新主键列。 被关联数据的主键必须在数据的左侧
实例：=VLOOKUP（A:A,B:E,4，0） 若A列与B列值相同，返回该行E列的值，0为精确匹配
13
10、COUNT、 COUNTA、COUNTIF、COUNTIFS
12
8、IS函数：ISBLANK、ISERR、ISERROR
ISBLANK——值为空白单元格； ISERR——值为除#N/A外的任意错误值； ISERROR——值为任意错误值（#N/A、#VALUE！、#REF！、#DIV/0!等）； ISNA ——值为#N/A错误值 ISNUMBER——值为数字 ISTEXT——值为文本
1
3、ROUND
四舍五入 格式：=ROUND（Number，Digits） 实例：=ROUND（Rand（）*100） 返回0到99随机整数
注：英文、中文、数字、符号、半角全角在Excel中 一般 占1位
10
4、INT
将数字向下取整 格式：=INT（Number） 实例：=INT（84.6） 返回84
统计含数字的单元格个数、统计非空单元格个数、带条件统计 格式：=Count(A1:A100) 统计A1到A100中含数字单元格的个数 =Counta（B1:B100） 统计B1到B100中非空单元格的个数 =Countif（C1:C100，”>8”） 统计C1到C100中数值大于55的个数 =Countifs（A：A，”<6”,B：B，”>1”） 统计A列小于6且B列大于1的个数
7、状态栏计算项
工作表状态栏,点击鼠标右键，可添加“计数、求和、平均值”等等，方便快速查看结果
1
8、条件格式
路径：开始/条件格式
只为包含以下内容的单元格设置格式 仅对排名靠前或靠后的数值设置格
4
9、数据有效性
序列、警告、提示
10、拆分合并的单元格
有些页面数据下载后，会对一列值相同的单元格进行了合并。在后期为了方便于手工计算，则需对合并的 单元格进行拆分，可使用if语句进行拆分
11、排序和筛选
自定义排序、条件筛选
1
5
12、表格建议
表格列标题增加底色，字体加粗，居中对齐； 表格添加边框 冻结首行（列少）、冻结窗格（列多,20列以上） 行与行、列与列之间不要有隐藏航或隐藏列 避免太大的数据表操作（上万条数据） 重复性的工作，尽量使用公式，以减少手工误操作
1
Hale Waihona Puke Baidu
4、Ctrl+Shift+*
快速选择整个单元格
4、Sheet快速定位
Excel工作表标签滚动条，点击鼠标右键，点击Sheet名称
5、Sheet复制、移动
可保留Sheet的格式
鼠标右键Sheet，选择“移动或复制”
6、&、 CONCATENATE
字符串连接 实例：=C2&“|”&D2 = CONCATENATE（C2，“|”,D2）
1
15
14、MATCH
搜索指定项的位置 格式：= MATCH （参数，数据组，0） 实例：= MATCH（“中华（大中华）”，E：E，0 ） 精确查找中华（大中华）在E列的位置
15、INDEX
返回区域中的数值
格式：= INDEX （数据组，行号） 实例：=INDEX（B：B，47） 返回B列第47行单元格的值 =INDEX（B：B， MATCH（“中华（大中华）”，E：E，0 ） ） 注：在使用引用函数时，若返回结果不正确，公式输入后同时按下 CTRL+SHIFT+ENTER 键
14
1
12、RANK
排名数 格式：=RANK(Number,Order) 实例：=RANK(B2,$B$2:$B$27) 求B列值的排名
13、LARGE
返回数组中第k个最大值 格式：= LARGE （Array,k） 实例：= LARGE （A1:A99,1） 返回A列第一个最大值 = LARGE （ A1:A99,9） 返回A列第9个最大值
11、SUM、SUMIF、SUMIFS
求和、待条件的求和 实例：=SUM（A：A） 求A列值的和 =SUMIF（A2：A100，”>10000”） 求A2：A100中单元格值大于10000的和值 =SUMIF（A2：A100，”>10000”，B2:B100） 求列中单元格值大于10000的B2：B100 的和值 =SUMIFS（A2：A100，B1：B100，”>0”,C2：C100，”<100”）求满足于B列大于0且C列小 于100条件的A列的和值
5、LEN
字符串中字符的个数 格式：=LEN（text） 实例：=LEN（爱我中华） 返回值为4
1
6、LEFT、RIGHT、MID、TRIM
字符串截取，从左/右/中间截取/截取空格 格式：=LEFT（Text，Number） = RIGHT（Text，Number）
11
6、LEFT、RIGHT、MID、TRIM
数 据 测 试
—Excel篇 2013年10月
1
目录
1 2 3
单元格
数据透视表
逻辑处理
3
2
1、A1、A1:A2， $ ( A1、 $A1、 A$1、 $A$1)
单元格定位
2、Ctrl+ ↑/↓/←/→
快速 跳转 至有数据单元格的第一个单元格/后一单元格
3、Ctrl+Shift+↑/↓/←/→
快速选择有数据的一行/列单元格数据
1
6
目录
1 2 3
单元格
数据透视表
逻辑运算
3
7
作用
对表格进行筛选、排序和分类汇总等操作
限制
1、确保数据源工作表第一行的列名 2、数据不包含空列
1
8
目录
1 2 3
单元格
数据透视表
逻辑运算
3
9
1、去除重复项
通过透视表、数据/删除重复项、 IF方式
2、RAND
大于等于0小于1的随机数 格式：=RAND（） 实例：=RAND（）*100 返回0-99数值
7、IF
条件判断
1
实例：=IF（A1=B1,0,1） =IF（OR（A1=0,B1=0）,””,1） =IF（A1-B1>0.1，”绿色”,”红色”） =IF （ A1>B1,”绿色”， IF （ AND （ A1>C1 ， A1<B1 ）,”黄色”， IF （ A1<C1,”红色”,”异 常”）））
1
9、VLOOKUP
多表关联
必须有一列为主键，若没有，可使用&、CONCATENATE将多列进行连接，创建一新主键列。 被关联数据的主键必须在数据的左侧
实例：=VLOOKUP（A:A,B:E,4，0） 若A列与B列值相同，返回该行E列的值，0为精确匹配
13
10、COUNT、 COUNTA、COUNTIF、COUNTIFS
12
8、IS函数：ISBLANK、ISERR、ISERROR
ISBLANK——值为空白单元格； ISERR——值为除#N/A外的任意错误值； ISERROR——值为任意错误值（#N/A、#VALUE！、#REF！、#DIV/0!等）； ISNA ——值为#N/A错误值 ISNUMBER——值为数字 ISTEXT——值为文本
1
3、ROUND
四舍五入 格式：=ROUND（Number，Digits） 实例：=ROUND（Rand（）*100） 返回0到99随机整数
注：英文、中文、数字、符号、半角全角在Excel中 一般 占1位
10
4、INT
将数字向下取整 格式：=INT（Number） 实例：=INT（84.6） 返回84
统计含数字的单元格个数、统计非空单元格个数、带条件统计 格式：=Count(A1:A100) 统计A1到A100中含数字单元格的个数 =Counta（B1:B100） 统计B1到B100中非空单元格的个数 =Countif（C1:C100，”>8”） 统计C1到C100中数值大于55的个数 =Countifs（A：A，”<6”,B：B，”>1”） 统计A列小于6且B列大于1的个数
7、状态栏计算项
工作表状态栏,点击鼠标右键，可添加“计数、求和、平均值”等等，方便快速查看结果
1
8、条件格式
路径：开始/条件格式
只为包含以下内容的单元格设置格式 仅对排名靠前或靠后的数值设置格
4
9、数据有效性
序列、警告、提示
10、拆分合并的单元格
有些页面数据下载后，会对一列值相同的单元格进行了合并。在后期为了方便于手工计算，则需对合并的 单元格进行拆分，可使用if语句进行拆分
11、排序和筛选
自定义排序、条件筛选
1
5
12、表格建议
表格列标题增加底色，字体加粗，居中对齐； 表格添加边框 冻结首行（列少）、冻结窗格（列多,20列以上） 行与行、列与列之间不要有隐藏航或隐藏列 避免太大的数据表操作（上万条数据） 重复性的工作，尽量使用公式，以减少手工误操作
1
Hale Waihona Puke Baidu
4、Ctrl+Shift+*
快速选择整个单元格
4、Sheet快速定位
Excel工作表标签滚动条，点击鼠标右键，点击Sheet名称
5、Sheet复制、移动
可保留Sheet的格式
鼠标右键Sheet，选择“移动或复制”
6、&、 CONCATENATE
字符串连接 实例：=C2&“|”&D2 = CONCATENATE（C2，“|”,D2）
1
15
14、MATCH
搜索指定项的位置 格式：= MATCH （参数，数据组，0） 实例：= MATCH（“中华（大中华）”，E：E，0 ） 精确查找中华（大中华）在E列的位置
15、INDEX
返回区域中的数值
格式：= INDEX （数据组，行号） 实例：=INDEX（B：B，47） 返回B列第47行单元格的值 =INDEX（B：B， MATCH（“中华（大中华）”，E：E，0 ） ） 注：在使用引用函数时，若返回结果不正确，公式输入后同时按下 CTRL+SHIFT+ENTER 键
14
1
12、RANK
排名数 格式：=RANK(Number,Order) 实例：=RANK(B2,$B$2:$B$27) 求B列值的排名
13、LARGE
返回数组中第k个最大值 格式：= LARGE （Array,k） 实例：= LARGE （A1:A99,1） 返回A列第一个最大值 = LARGE （ A1:A99,9） 返回A列第9个最大值
11、SUM、SUMIF、SUMIFS
求和、待条件的求和 实例：=SUM（A：A） 求A列值的和 =SUMIF（A2：A100，”>10000”） 求A2：A100中单元格值大于10000的和值 =SUMIF（A2：A100，”>10000”，B2:B100） 求列中单元格值大于10000的B2：B100 的和值 =SUMIFS（A2：A100，B1：B100，”>0”,C2：C100，”<100”）求满足于B列大于0且C列小 于100条件的A列的和值
5、LEN
字符串中字符的个数 格式：=LEN（text） 实例：=LEN（爱我中华） 返回值为4
1
6、LEFT、RIGHT、MID、TRIM
字符串截取，从左/右/中间截取/截取空格 格式：=LEFT（Text，Number） = RIGHT（Text，Number）
11
6、LEFT、RIGHT、MID、TRIM
数 据 测 试
—Excel篇 2013年10月
1
目录
1 2 3
单元格
数据透视表
逻辑处理
3
2
1、A1、A1:A2， $ ( A1、 $A1、 A$1、 $A$1)
单元格定位
2、Ctrl+ ↑/↓/←/→
快速 跳转 至有数据单元格的第一个单元格/后一单元格
3、Ctrl+Shift+↑/↓/←/→
快速选择有数据的一行/列单元格数据
1
6
目录
1 2 3
单元格
数据透视表
逻辑运算
3
7
作用
对表格进行筛选、排序和分类汇总等操作
限制
1、确保数据源工作表第一行的列名 2、数据不包含空列
1
8
目录
1 2 3
单元格
数据透视表
逻辑运算
3
9
1、去除重复项
通过透视表、数据/删除重复项、 IF方式
2、RAND
大于等于0小于1的随机数 格式：=RAND（） 实例：=RAND（）*100 返回0-99数值