Excel常用技巧

个人笔记（整理不易，有帮助，收藏+点赞+评论，爱你们！！！你的支持是我写作的动力）

笔记目录：学习笔记目录_pytest和unittest、airtest_weixin_42717928的博客-CSDN博客

个人随笔：工作总结随笔_8、以前工作中都接触过哪些类型的测试文档-CSDN博客

目录

一：绝对引用和相对引用

二：锁定

（1）行锁定

（2）列锁定

三：查找&匹配

（1）列查找：vlookup(查找值,索引范围,列数,匹配规则）

1）用精确查找

2）近似查找

（2）行查找：hlookup(查找值,索引范围,列数,匹配规则）

（3）列引用：column(引用单元格）

（4）列引用：row(引用单元格）

（5）查找匹配：MATCH(查找值,索引行or列,匹配规则)

1）精确查找

2）近似查找

3）vlookup搭配match，返回列数索引

4）hlookup搭配match，返回行数索引

（6）值引用：INDEX(索引范围,匹配行数,匹配列数)

四：计算相关

（1）乘积求和

SUMPRODUCT(数组1,数组2,…）

（2）条件求和

SUMIFS(求和范围，条件1范围，条件1，条件2范围，条件2,...）

（3）条件计数

COUNTIFS(计数范围1，条件1，计数范围2，条件2,...）

（4）矩阵乘积

MMULT(列数组，行数组）

（5）取整相关

1）ROUND(number,num_digits)

2）CEILING(舍入值，舍入倍数）

3)FLOOR(舍入值，舍入倍数）

五：配置相关

（1）SUBSTITUTE(要替换的单元格,需替换旧文本,新文本）

（2）CONCATENATE(文本1,文本2,…）

（3）SUBSTITUTE+CONCATENATE组合使用

---

一：绝对引用和相对引用

（1）excel中$A$1的意思是“绝对引用A1单元格”，即A1单元格的行和列都被绝对引用，不会因向其他位置的填充变化，而改变行列号

在A列往右边插入一行，可以看到这里都是变化的

举个例子，这里对B列和C列进行乘积和，如果这样写$B$2（绝对）*C2（相对），则下拉填充的时候。左边的都是使用$B$2（绝对）

二：锁定

等级	玩家数	钻石数
1	10	100
2	20	200
3	30	300

（1）行锁定

K$61
等级	玩家数	钻石数
1	10	100
1	20	200
1	30	300

（2）列锁定

$B6
等级	玩家数	钻石数
1	1	1
2	20	200
3	30	300

三：查找&匹配

（1）列查找：vlookup(查找值,索引范围,列数,匹配规则）

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

参数	简单说明	输入数据类型
lookup_value	要查找的值	数值、引用或文本字符串
table_array	要查找的区域	数据表区域
col_index_num	返回数据在查找区域的第几列数	正整数
range_lookup	精确匹配/近似匹配	FALSE（0、空格或不填（但是要有','占位)）/TRUE（1或不填(无逗号占位)）

1）用精确查找

备注：这里写错了，不是大本，是等级

其中：

第一个参数，填写需要被查找的数字，我这里就查找1，即K82

第二个参数，这里输入我们要返回数据的区域(绝对引用)，即“$K$76:$M$79”；查找时只会用K82与$K列的内容匹配

第三个参数，“玩家数”是区域的第2列，所以这里输入“2”

（注意：这里的列数不是EXCEL默认的列数，而是查找范围的第几列）

第四个参数，因为我们要精确查找等级，所以输入“FALSE”或者“0”。模糊查找输入“TRUE”或“1”

2）近似查找

如果找不到精确匹配值，则返回小于 lookup_value 的最大数值

比如这里，找不到1.5，就找小于1.5的1

（2）行查找：hlookup(查找值,索引范围,列数,匹配规则）

（3）列引用：column(引用单元格）

可以搭配vlookup使用，返回列数索引

通常在vlookup索引范围很大，数第几列数到你晕的时候，用这个

先用column得到玩家数在哪一列，然后减去10即可

（4）列引用：row(引用单元格）

可以搭配hlookup使用，返回行数索引

通常在hlookup索引范围很大，数第几行数到你晕的时候，用这个

（5）查找匹配：MATCH(查找值,索引行or列,匹配规则)

MATCH(lookup_value, lookup_array, [match_type])

• Match_type：1 或省略，表示MATCH 函数会查找小于或等于 lookup_value 的最大值
• Match_type：0，表示MATCH 函数会查找等于 lookup_value 的第一个值
• Match_type：-1，表示MATCH 函数会查找大于或等于 lookup_value 的最小值

1）精确查找

2）近似查找

3）vlookup搭配match，返回列数索引

通常在vlookup索引范围内你有确切的匹配目标时用（日常配表中最实用的公式）

比如找等级是2的玩家数

4）hlookup搭配match，返回行数索引

通常在hlookup索引范围内你有确切的匹配目标时用

找非R对应的总元宝数

（6）值引用：INDEX(索引范围,匹配行数,匹配列数)

vlookup、hlookup的孪生兄弟，通常在lookup不能用的情况下作他俩的替代

vlookup、hlookup是从左到右、从上到下匹配，而index可以实现从右到左，从下到上的匹配

四：计算相关

（1）乘积求和

SUMPRODUCT(数组1,数组2,…）

序号	玩家数	钻石数	元宝数	乘积
1	10	100	100	100000
2	20	200	200	800000
3	30	300	300	2700000
				3600000

要计算三组序号的乘积和，选择10后，往下拖动到30，然后逗号，以此类推

（2）条件求和

SUMIFS(求和范围，条件1范围，条件1，条件2范围，条件2,...）

钻石数和元宝数都>100的玩家数总和
等级	玩家数	钻石数	元宝数
1	10	100	100
2	20	200	200
3	30	300	300
SUMIFS(K41:K43,L41:L43,">100",M41:M43,">100")
50

这个函数，也可以用于查询

当查找值在索引范围内不是唯一时，是用不了lookup函数的

比如这个表格，当你用等级=1来索引，会有2个匹配项

等级	玩家数	钻石数	元宝数
1	10	100	100
1	20	200	200
3	30	300	300

如果你有额外的可用匹配条件，且条件重合后的值确认唯一，可用sumifs来实现查找

求等级=1且钻石数=100的玩家数
等级	玩家数	钻石数	元宝数
1	10	100	100
1	20	200	200
3	30	300	300
等级	钻石数	玩家数量
1	100	10
SUMIFS(R40:R42,Q40:Q42,Q45,S40:S42,R45)

（3）条件计数

COUNTIFS(计数范围1，条件1，计数范围2，条件2,...）

钻石数和元宝数都>100的数据有几条
等级	玩家数	钻石数	元宝数
1	10	100	100
2	20	200	200
3	30	300	300
COUNTIFS(L41:L43,">100",M41:M43,">100")
2

（4）矩阵乘积

MMULT(列数组，行数组）

当你需要乘积求和,但分别是一行和一列时，是用不了sumproduct函数的，此时用mmult替代

（5）取整相关

1）ROUND(number,num_digits)

round函数就是返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果

Number：需要进行四舍五入的数字。

Num_digits：指定的位数，按此位数进行四舍五入

2）CEILING(舍入值，舍入倍数）

向上舍入：将 17向上舍入到最接近的 4的倍数

舍入数	倍数	舍入后值
17	4	20

3)FLOOR(舍入值，舍入倍数）

向下舍入：将 17向下舍入到最接近的 4的倍数

五：配置相关

（1）SUBSTITUTE(要替换的单元格,需替换旧文本,新文本）

当有固定的配置格式时，用它来做批量替换

替换前	替换后
你哈哈哈	我哈哈哈
你嘿嘿嘿	我嘿嘿嘿
你呵呵呵	我呵呵呵

例子：当你有个配置

配置模板	{"~":#}
	道具id1	数量1	道具id2	数量2
6元	coin	20	speedup1h	10
30元	coin	30	speedup3h	20
68元	coin	60	speedup5h	30

将模板中的~替换成coin

再将#替换成对应的数值

结果

	道具1配置	道具2配置
6元	{"coin":20}	{"speedup1h":10}
30元	{"coin":30}	{"speedup3h":20}
68元	{"coin":60}	{"speedup5h":30}

（2）CONCATENATE(文本1,文本2,…）

“&”的加强版，在有很多项要拼接的时候用

比如现在有一个表格

	道具1配置	道具2配置	道具3配置
6元	{"coin":20}	{"speedup1h":10}	{"hero1":1}
30元	{"coin":30}	{"speedup3h":20}	{"hero2":2}
68元	{"coin":60}	{"speedup5h":30}	{"hero3":3}

将道具拼接起来

	道具1~3配置
6元	{"coin":20}{"speedup1h":10}{"hero1":1}
30元	{"coin":30}{"speedup3h":20}{"hero2":2}
68元	{"coin":60}{"speedup5h":30}{"hero3":3}

（3）SUBSTITUTE+CONCATENATE组合使用

SUBSTITUTE(CONCATENATE(K16,L16,M16),"}{",",")

先拼接，比如这样后{"coin":20}{"speedup1h":10}{"hero1":1}，再替换，将}{替换成，

	道具1~3配置
6元	{"coin":20,"speedup1h":10,"hero1":1}
30元	{"coin":30,"speedup3h":20,"hero2":2}
68元	{"coin":60,"speedup5h":30,"hero3":3}