excel操作总结

一、截取字符串

参考链接：https://baijiahao.baidu.com/s?id=1606854719241330360&wfr=spider&for=pc

 

二、vlookup逆向查找

正常的查找都是知道第A列，去查找对应的其它列，逆向查找是知道第G列，去查找第A列，常规的办法是将两列位置对调，手动将A列剪切并粘贴到D列后面，然后应用vlookup进行查找,这种方法较麻烦。

函数的这一部分IF({1,0},B:B,A:A)通过if函数第一个参数引入数组作为参数，使得每一个数组元素作为参数判断后分别返回不同的数组，返回后的数组分别是B列和A列中单元格对应的内容。

即便是正常的顺序，也可以采用这种方式实现查找匹配：

 

三、替换函数

SUBSTITUTE(text,old_text,new_text,[instance_num])

=substitute（需要替换的文本，旧文本，新文本，替换第几个旧文本）

如下：将A1中的第4个：号替换为*。

 

四、复杂字符串的截取功能

将最后一个:号及中国去掉，只留前面的信息

首先，利用substitue将第四个：号替换为*号

然后，查找*号第一次出现的位置

最后，利用left函数截取字符串。

五、OFFSET函数

offset(参照区域，行数，列数，高度，宽度)：从参照区域开始移动N行N列取数
参照区域：即起始区域
行数：从起始区域向下或向上移动的行数，正值表示向下移动，负值表示向上移动.
列数：从起始区域向左或向右移动的列数，正值表示向右移动，负值表示向左移动。
举例：
offset(A1,3,4)表示从A1格子开始向下移动3行，向右移动4列取数。

特殊应用：
A B C D
城市 象限 占比 第1象限数据
北京 1 3.8% x
北京 2 2.3% x
北京 3 1.8% x
北京 4 2.1% x

基于上述数据固定取第一象限占比，放在最后一列： offset(d2,-mod(row(d2)-2,4),-1,)，从d2开始，mod表示取余数，每隔4行从头开始，都取各自城市的第一象限占比，mod计算的结果就是向上取数的行数，-1表示向左移动一列取数