☰

来自不同文件的第二小数字

tag-icon • tag-icon microsoft-excel
来自不同文件的第二小数字

我正在尝试从另一个文件和两个不同的行中获取第二小的数字。我试过这样：

=SMALL('https://test/[file.xlsx]'!)$I$3:$I$27:'https://test/[file.xlsx]'!$I$29:$I$46;2)

由于某种原因，这仅在打开其他文件时才有效。我也尝试使用 min() 函数，该函数在没有打开其他文件的情况下完美运行，但我无法获得第二小的数字。我想要提取数字的 excel 已连接、已更新且状态正常是否有任何解决方法或有人发现错误？谢谢

答案1

第一项是您)的公式中有一个不需要的字符。它位于您指定的第一个范围内，在之后.xlsx]'!和之前$I$3:$I$27。仅此一点就会使事情失败。

这是因为您=SMALL('https://test/[file.xlsx]'!)在其中SMALL没有给出实际地址，只有一个带有额外地址的文件名!，并且没有参数来告诉它返回哪个值，所以即使那个（“有趣的是”如果它确实如此）将整个文件读入函数意味着它仍然不知道要返回“哪个”值。

然而，更大的问题（从某种意义上说，这可能只是一个拼写错误，尽管两者都会破坏公式）是您形成要SMALL检查的范围的方式。您显然不想考虑单元格 I28，不幸的是，它正好在中间。但是，您选择为函数提供一个范围，形式如下：

A1:A5:A7:A10

期望 Excel 理解您想要除 A6 之外的 A1 到 A10。但它不会这样做。它会查看您输入的任何范围并构建一个单元格“框”，其中最左上角的单元格作为起始，最右下角的单元格作为其对角。它将该“框”中的所有内容都包含在它所解释的范围内。因此，上面的代码会为您生成包含 A6 的 A1:A10。以下是：

D3:J9:A2:K3:L1:L3

获取范围 A2:L9，因为这是包含这两个极端的最小单元格“框”，最左边的列是 A 列，最低的行号是第 2 行，因此为 A2，最右边的列是 L 列，最高行号是第 9 行，因此为 L9。

为了包含两个范围，并且不会因它们的大小不同而产生不理想的结果，您可以从 Excel 中最新的新功能构建所需的范围。看起来相当容易。但是，我还不熟悉它们，只是得到了它们，也许你也没有。所以另一种方法是使用公式：

=SMALL(  IFERROR(  INDEX(  ($I$3:$I$27,  $I$29:$I$46),  SEQUENCE(  MAX(  ROWS($I$3:$I$27),  ROWS($I$29:$I$46)  )  ),  1,  {1,2}),  ""),  2)

其中，INDEX用于将两个范围连接在一起。很少使用，但如果需要，可以INDEX赋予多个“区域”并在它们之间切换，或者像上面一样，强制使用每个区域。将几个区域（本例中为两个）放在括号中，以表明INDEX它们与其他参数无关。

要告诉它要使用每个范围的多少行（第二个参数），您必须ROWS查看每个区域有多少行，然后MAX找到这些结果中最大的行。然后将其提供给它，SEQUENCE以便它将使用那么多行。这确保您获得最大范围的所有行，而不是切断其中一些行。但这意味着较短的区域将填充错误，因为它们没有任何行差异。在这种情况下，错误大约有 8 行。

遗憾的是，这会导致公式本身出现问题。因此，IFERROR用于将错误转换为不会使公式出现问题的数值。但是什么数值呢？将它们设为0，它们将争夺最小值……但是将它们设为某个文本值，公式SMALL将忽略它们。因此，公式将它们设为最简单的文本值，，""尽管几乎所有其他文本都可以。没有其他理由，因为这个结果从未被任何人看到过！

因此，现在您有两列 25 行的范围，第二列中的一些单元格看起来是空的，但实际上""里面有内容。SMALL能够处理二维范围，而不仅仅是单个列或行，因此一切都很好，它会为您找到第二最小的值。

回到INDEX刚才的话题。在第二个参数（即行值）之后，我不再谈论它，因为它把我引向了重要的切线并把我带到了最后。但是还有另外两个参数，列参数（第 3 个）和区域参数（第 4 个）。列参数看起来很简单，因为每个 SOURCE 范围只有一列。这是正确的，所以它很容易。但是您可能会想到您需要一个 2 或 来表示它应该使用列 1 和列 2，有些人认为是这样的，因为它生成的虚拟表中有两列。但不要担心输出，这里只有源材料才是重要的，所以 1 是完全正确的。

对于区域参数，您希望INDEX返回两个区域的结果……这实际上是重点。因此，您需要向它指示两个区域，而不仅仅是 1 或 2。我展示了，{1,2}因为这很容易输入，并演示了一种向 Excel 提供信息的另一种方式，而不是使用SEQUENCE。有人可能会说这SEQUENCE对于创建行参数很方便，因为那将是 43 个项目，容易输入错误，而且输入起来肯定很讨厌。但它也可以从外部获取输入来构建序列，这可能会有用。不是在这里，但如果您更喜欢将它用于第 4 个参数，您可以使用SEQUENCE(1,2)。关于这一点的另一件有趣的事情是，它不是考虑源与输出的问题。2 必须放在其列参数中，因为您想要两列INDEX。由于它必须是列参数，因此您也必须输入行参数。那必须是 1，因为任何更高的值都会尝试创建事物的多个版本并在这种情况下导致奇怪的结果，而在其他情况下则根本失败。

因此，要使用上述公式，您需要做两件事：

  1. 'https://test/[file.xlsx]'!您必须在显示范围的四个位置之前输入字符串。我省略了它以使公式更易于阅读和理解，但您绝对必须有它！

  2. 我使用的是英语版本的 Excel，因此我的参数分隔符/定界符是逗号 ( ,)。您需要将这些分隔符改回您版本的分号 ( ;)。

然后一切就都正常了。SMALL是众多读取已关闭文件的函数之一，因此无论文件是否打开，它都应该可以工作，不过打开文件时性能几乎肯定会好得多，因为每次读取已关闭文件时都需要打开文件进行一次读取，然后再打开文件进行下一次读取，依此类推。即使多年来这种情况有所改善，但与已经打开的文件相比，即使通过网络或互联网打开一次文件也可能需要花费明显的时间。

正如我上面提到的，新的文本处理函数将把两个范围构建为一个范围，并允许您使用您选择的任何内容填充“填充”单元格。如果您有的话，值得稍后研究和编辑，但上面的内容应该可以让您立即开始。

最后，如果您想要一个更容易阅读的公式，并且只需要读取一次范围就可以更快地完成，那么您也可以用它包装LET并赋予范围名称。

相关内容