Excel基础知识文本函数编写公式优化

北京中科癜风医院好嘛 https://mip.yyk.99.com.cn/fengtai/68389/jianjie.html

今天要聊的内容是文本函数中有“改”能力的函数SUBSTITUTE，它的功能是将把文本的某些字符或字符串替换成新的内容，默认为4个参数，3必输参数和1选输参数；函数使用的结构图如下：

SUBSTITUTE函数结构图

功能：通过替换规则生成新的文本内容，并不对源文本的任何影响；

源文本：原型为源文本，不过它的参数形式还是很多变的，可以为引用单元格或表达式，不管单元格的内容是原型数据，还是表达式中的数学表达式和函数表达式，其最终结果还是会成文本，数字，逻辑值或错误编码四项之一，若遇到错误编码不会任何处理直接返回错误编码；

原来字符：要替换源文本的字符或字符组合，

新字符：原来字体将会被新字符字符组合替换掉；

N：表示替换原来字符的出现的第几次，并替换成新字符或字符组合；此参数为选输参数，不输默认只要在“源文本”中出现的“原来字符”都会被“新字符”替换掉，输入2，就在第2次出现“原来字符”替换成“新字符”;

说到这基本上文本常用函数就说的差不多了，我们通过一个网上的网友给我发来的问题，来将之前学过的知识融汇一下；

问题：网友A:我在工作中遇到这样的数据：“LTE-罗源霍口徐坪-4LX-V1（）宏站设计图纸”，需要提取“罗源霍口徐坪”，我们就一起随着我的思路一起看看这个公式怎么写吧？

思路1

首先通过find函数或search函数需要确认第一个“-”位置和第二个“-”的位置，通过这两位置计算出“罗源霍口徐坪”的字符数，然后用mid函数从文本内容截取“罗源霍口徐平”；

数据情况：源数据在A列3-9行，B列为输出截取内容的列，示意图如下：

数据示意图

定位公式：哪就开始编写公式吧，我们选用search函数来查找“-”的位置，第一个“-”位置很好写：X=search(-,A3)；第二“-”位置编写起来需要点技巧，需要search函数的第三个参数，从第几个字符开始查找，否则无法定位第二个“-”的位置，我们只需重search(-,A3)+1字符开始就可以定位啦，公式为：Y=search(-,A3,X+1);

复习：search和find区别：search函数支持通配符，不能区分大小写；find区分大小写，不支持通配符，详细了解：Excel基础知识-文本函数之FIND和SEARCH

截取公式：编写截取公式为：=mid(A3,X+1,Y-X-1)带入Y,X后公式为：=MID(A3,SEARCH(-,A3)+1,SEARCH(-,A3,SEARCH(-,A3)+1)-SEARCH(-,A3)-1)；为了公式适应所有单元格，这些位置都需要通过公式计算得到，如果你能写出公式，说明你已经对文本常用的函数基本掌握了，那么我们可以把这个公式简化吗？当然可以啦，哪必须就要用到今天的函数SUBSTITUTE,一起来看看思路2公式是如何写出来的吧！

思路2

我们从上以公式可以看出，定位第一个“-”，公式并不复杂，需要优化的是定位第二“-”的位置的部分，哪我们该如何优化掉Y中X+1？如果我们把第一个“-”替换成其它符号或替换成“”是不是就可以直接用search函数就能查出第二个“-”的位置了啊，而在截取截取函数中第三个参数是需要截取的字符的长度而不是第二个“-”的位置，从数据数据“LTE-”又都是固定的，所以我们干脆直接将这个4个字符整体替换为“”，这样截取的字符数Z=search(-,substitute(A3,LTE-,,1))-1(为了防止删除A1单元格其它部分的“LTE-”,只替换第一次出现的即可）。

优化公式：再将X,Z代入mid(A3,X+1,Z)=mid(A3,search(-,A3)+1,search(-,substitute(A3,LTE-,,1))-1)是不是比上面的公式短了不少呢？再来看看另一种思路，是如何实现该更能的吧！

思路3

既然我们可以通过SUBSTITUTE函数可以将字符串的“LTE-”,去掉，我们又可以获得第二个“-”位置，这样我们就可以直接使用left函数截取也可以获得我们需要的内容啊，先来回顾一下left函数的用法left(文本，长度），在这我要代入的文本并非源文本而是经过处理的字符串：substitute(A1,LTE-,,1)，长度则是在处理后的内容中，找到-的位置减1就是截取字符串的长度，那么公式的长度是不是就有了：长度=search(-,substitute(A1,LTE-,,1)-1,哪整体的公式组合一下即可以了：=left(substitute(A1,LTE-,,1),search(-,substitute(A1,LTE-,,1)-1);对于这三种方法，你更喜欢哪一种呢？你又该如何选择呢？我们把公式修改到什么程度就可以了呢？

三个公式方案的效果图

带着问题我们就一起来看看衡量公式好坏的标准之一：通用性也可以叫兼容性，它是衡量公式对于数据依赖程度的参考线，就拿上面的例子来说，第2个公式和第3个公式依赖都比第1个要大，因为后2个公式的使用的手动输入条件更长，条件中都有手动输入的“LTE-”,而第1个公式只有“-”;如果文本中不存在“LTE-”,后面的两个公式是无法得出结果的。当然你也可以完善后两个公式，也能达到跟公式1一样的通用性；至于怎么完善，其实我在文中已经提到了，看看聪明的你能不能把它完善？

如果你通过自己学的知识也能写出跟我一样的公式，而且也能完善功能，哪你能算什么水平呢？刚及格，其实我们上面写的三个公式表面上看都没有问题，其实存在的问题挺多了，需要优化的方面也很多，这些问题会在以后的文章中逐步介绍，今天的我们就先聊到这里，欢迎你在工作遇到的问题在下方留言，我看到后会第一时间回复，在工作学习的路上，我们一路同行，有缘下文再见！