![理解复杂的数组公式](https://linux22.com/image/1536607/%E7%90%86%E8%A7%A3%E5%A4%8D%E6%9D%82%E7%9A%84%E6%95%B0%E7%BB%84%E5%85%AC%E5%BC%8F.png)
我想从单元格中删除所有字母,只留下数字。经过一番谷歌搜索,我找到了这个数组公式:=SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
当我单击“评估”时,它会产生数百条真/假语句,这非常令人困惑。有人可以分解并解释每个组件的作用吗?
答案1
让我们一起逐步完成评估过程:
abf5fb6
在我的示例中,单元格中有值A2
,其计算结果为56
。
第一步,A2
用单元格的值替换A2
前:
=SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
后:
=SUM(MID(0&“abf5fb6”,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
注意文本现在被一对引号括起来了。这意味着它是文本,也称为细绳。
现在是简要解释该MID
函数的好时机。此函数将简单地从字符串中提取一些文本。第一个参数是我们将要开始的文本或字符串。第二个参数是我们要开始提取的起始位置。第三个参数是我们要提取的字符数,或最终结果的长度。例如,=MID("wizlog", 1, 3
将返回wiz
而=MID("wizlog", 2, 5)
将返回izlog
因此,下一步将连接0&"abf5fb6"
,因为如上所述,MID
函数中的第一个参数需要文本(再次称为字符串)。但为了在 Excel 中将数字添加到字符串,请使用符号&
。
前:
=SUM(MID(0&"abf5fb6",LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
后:
=SUM(MID(“0abf5fb6”,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
现在,我们将跳转到下一个MID
函数,再次用它的值替换另一个函数A2
,就像以前一样。
后:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID(“abf5fb6”,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
接下来,我们处理第二个MID
函数中的第二个参数ROW($1:$99)
。请记住,函数中的第二个参数MID
只是给我们起始位置。ROW
另一方面,函数只是返回给定的行,因此由于我们向其传递了 1-99 的范围,因此它将返回一个 1-99 的数组或列表。这意味着我们计划使用该MID
函数 99 次,每次从 1 到 99 的不同位置开始。
前:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID("abf5fb6",行($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
后:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID("abf5fb6",{1;2;3;...;99},1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
(为了节省空间,我没有输入 1 到 99 的所有数字,但我相信您明白我的意思。)
现在我们有了第二个函数的所有部分MID
,我们就可以计算出这一部分了。
前:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID("abf5fb6",{1;2;3;...;99},1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
后:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--{“a”;“b”;“f”;“5”;“f”;“b”;“6”;“”;“”;“”;...;“”})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
那么这里到底发生了什么?好吧,该MID
函数只是返回给定字符串的子字符串,从给定位置开始,包含指定数量的字符。所以我们给它字符串,abf5fb6
给它一个起始位置数组,指定我们只想提取 1 个字符。因此,该函数返回字符串中每个字符的数组,从 1 到 99。由于我们的起始字符串只有 7 个字符长,因此位置 8-99 为空,这就是为什么我们在 ( "";"";"";...;"";
) 之后有所有空子字符串的原因。
下一个要计算的函数是ISNUMBER
函数,但首先让我们先来看一下我们首先要做的一个奇怪的功能。请注意,我们的新数组前面有一个双减号。虽然单个减号会反转结果(如果则TRUE
返回FALSE
并且反之亦然),但双减号意味着将字符串答案强制转换为数字。所以通常这会导致答案TRUE
变成一个1
,而答案FALSE
变成一个0
,但在这种情况下,我们将数组中的每个字符转换为数字。因此,输入--"a"
将导致,#VALUE!
而输入--"5"
将导致5
。
因此,当我们执行该ISNUMBER
函数时:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--{"a";"b";"f";"5";"f";"b";"6";"";"";"";...;""})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
我们在幕后实际执行的是:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER({#VALUE!; #VALUE!; #VALUE!; 5; #VALUE!; #VALUE!; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; ...; #VALUE!})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
结果,它变成了一个数组,TRUE
或者FALSE
表明该值是否是数字。因此我们得到:
=SUM(MID("0abf5fb6",LARGE(INDEX({假;假;假;真;假;假;真;假;假;假;...;假})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
时间不够 好吧,我在工作中做着不该做的事情,今天我只能做这么多了,然后才需要真正做一些有成效的事情。希望我稍后能从上次中断的地方继续。
答案2
我们可以“展开”这个公式
=SUM(
MID(
0&A2,
LARGE(
INDEX(
ISNUMBER(
--
MID(
A2,
ROW($1:$99),
1
)
)*ROW($1:$99)
,
),
ROW($1:$99)
)+1,
1
)*10^ROW($1:$99)/10
)
并遵循操作数的所有变换。
首先,表达式ROW($1:$99)
表示一个递增的自然数数组
{1,2,...,99}
。它通常用作构建数组公式的便捷块。
接下来,A2
这里是输入单元格的地址,其中包含混合数字的文本字符串,例如
R824TX01rQ768
。
因此,建筑
MID(
A2,
ROW($1:$99),
1
)
意思是:从输入<1>中创建所有符号的数组(长度为 的字符串1
,根据 中的第三个参数)。MID(...,1)
A2
索引大于字符串长度的元素A2
为空字符串。
然后在这个符号数组前面加上双减号--
,这会将数字符号转换为相应的数字,并将其他符号转换为错误值#VALUE!
<2>。
接下来,一个函数ISNUMBER()
对混合字符/数字数组进行操作,并生成一个布尔true/false
值数组<3>,
它按元素方式乘以一个熟悉的ROW($1:$99)
。当一个true/false
值乘以数字时,
true
被解释为1
,并且false
被解释为0
,因此结果是一个数字数组,对于 中的每个字符位置,如果符号不是数字,则A2
包含0
,如果字符是数字<4>,则包含索引。
功能
INDEX(<the array>,)
或者,扩展一下,
=INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),)
采用这个数字数组和一个空的第二个参数,这基本上会产生相同的数组<5>。
表达
LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))
对从数组中获得的操作数按INDEX(...)
降序排序<6>
表达
MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)
A2
首先在字符串前面加上前缀0
,然后按排序顺序提取符号,从找到的最后一位数字<7>开始。
并将得到的数组中的每个数字乘以10^ROW($1:$99)/10
<8>。
MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10
<9>
最后,
=SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
将数组中的所有数字相加,得到所需的数字 <10>。
不确定为什么INDEX(<array>,)
要使用重复构造,似乎没有它该公式也可以工作:
=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
<11>
也许存在一些兼容性问题?