川剧有变脸绝活,EXCEL有易容之术

欢迎转发和点一下“在看”，文末留言互动!

置顶公众号或设为星标及时接收更新不迷路

朋友们好，今天要给大家介绍一则数值处理的技巧。在EXCEL中，数值的格式书写遵循数学规则，以“.”号作为整数和小数的分隔符，以“,”号作为千分位符。如果错用了，那么数值型的数据就会变成文本型的数据，给后面的计算带来一系列的问题。

今天的这道题目就是这样的，标点符号混乱导致错误。现在需要通过公式修正错误。

如上图，题目要求将A列中的数据转换为B列中的数据。这是一道简单的文本字符替换类型的题目。可以考虑使用SUBSITITUTE函数，它也是EXCEL中常用的文本数据处理函数之一。但如何将公式写得简单通俗易懂则需要好好思考一番。

在单元格输入下列公式，确认后向下拖曳即可。

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","@",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))),".",""),"@",".")

这条公式的思路是最基本、常规的解题思路。既然要将文本型数据转换为数值，那就要保留最后一个“.”，然后将其它的“.”全部替换掉。

LEN(A2)-LEN(SUBSTITUTE(A2,".",""))

用来计算整个文本字符串中有多少个“.”。它的思路是，用这个文本字符串的长度，减去将分隔符替换为空之后的字符串的长度。差值就是分隔符的个数。

SUBSTITUTE(A2,".","@",LEN(A2)-LEN(SUBSTITUTE(A2,".","")))

将最后一个分隔符替换为“@”。比如，LEN(A2)-LEN(SUBSTITUTE(A2,".",""))部分的结果是3，它作为SUBSTITUTE函数的第4参数，含义是替换第3个“.”，也就是最后一个“.”。

SUBSTITUTE(SUBSTITUTE(A2,".","@",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))),".","")

接下来将字符串中剩余的“.”全部替换为空值。

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".","@",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))),".",""),"@",".")

最后一步，把“@”替换回“.”，完成解答。

这道题目的逻辑思路很简单，就是利用SUBSTITUTE函数来做多次替换，最终使文本数据达到题目要求。这个技巧是SUBSTITUTE函数最基本的应用技巧，尤其是对其第4参数的理解和应用。

在单元格B2中输入下列公式，三键确认后向下拖曳即可。

=SUBSTITUTE(A2,".","")/10^(LEN(A2)-MAX(IFERROR(FIND(".",A2,ROW($1:$18)),0)))

这条公式的思路就比较新颖了。它抛弃了多个SUBSTITUTE函数替换的冗长写法。

FIND(".",A2,ROW($1:$18))

利用FIND函数在源数据中查找“.”，但是今天这个FIND函数也和平常我们常见的用法不一样，它也使用到了第3参数。第3参数是ROW($1:$18)，表示依次从第1、2、..、18位开始，来查找“.”。

这里ROW($1:$18)中的数字18是随意写的，只要保证足够长就行。这部分返回的结果是{2;2;6;6;6;6;10;10;10;10;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。

IFERROR(FIND(".",A2,ROW($1:$18)),0)

接下来利用IFERROR函数将FIND函数返回结果中的错误值都转换为0。

MAX(IFERROR(FIND(".",A2,ROW($1:$18)),0))

MAX函数找出其中的最大值，也就是10。这个10表示的是最后一个分隔符“.”的位置。

LEN(A2)-MAX(IFERROR(FIND(".",A2,ROW($1:$18)),0))

用源数据的总长度减去最后一个分隔符的位置数字，就是最后一个“.”右边剩余的字符串的长度，这里结果是1。

这个1也有另外一层含义，右侧有1个字符长度，意味着最终的结果是源数据替换所有分隔符后，得到的数据的十分之一。

因此上面这部分要作为10的幂。

SUBSTITUTE(A2,".","")/10^(LEN(A2)-MAX(IFERROR(FIND(".",A2,ROW($1:$18)),0)))

最后，SUBSTITUTE函数替换所有的分隔符后再缩小相应的倍数，就得到正确答案。

第二条公式中要充分理解FIND函数第3参数的应用，这个小技巧非常巧妙。

本期内容练习文件提取方式：

链接：https://pan.baidu.com/s/1JnR4z5ArpRB1c_-kklYw2A?pwd=eogn

提取码：eogn

我就知道你“在看”