个人笔记（整理不易，有帮助点个赞）
笔记目录：学习笔记目录_pytest和unittest、airtest_weixin_42717928的博客-CSDN博客
目录
（1）列查找：vlookup(查找值,索引范围,列数,匹配规则）
（2）行查找：hlookup(查找值,索引范围,列数,匹配规则）
（5）查找匹配：MATCH(查找值,索引行or列,匹配规则)
SUMIFS(求和范围，条件1范围，条件1，条件2范围，条件2,...）
COUNTIFS(计数范围1，条件1，计数范围2，条件2,...）
（1）SUBSTITUTE(要替换的单元格,需替换旧文本,新文本）
一：绝对引用和相对引用
（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} | |||
