☰

理解复杂的数组公式

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 array
理解复杂的数组公式

我想从单元格中删除所有字母，只留下数字。经过一番谷歌搜索，我找到了这个数组公式：=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>

也许存在一些兼容性问题？

相关内容