small函数怎么避开零,SMALL函数排除0的公式

题图来自Unsplash，基于CC0协议

导读

首先，SMALL 函数在处理一系列数字时，会按顺序返回最小值、第二小的值、第三小的值等等。然而，在某些情况下，我们可能希望排除那些为零或空的值。

想法是为 SMALL 函数提供一个数组或区域，其中只包含非零值（或者说非空，但不包括零）。为此，我们可以在 SMALL 函数的 array 参数中使用 IF 函数、SUMPRODUCT 函数，或者使用数组公式，来排除零（或其他我们不想包含的值）。

以下是一些不同的方法：

1. 使用 IF 函数排除零值 (简单的数组公式或结合 VLOOKUP/INDEX)

   • 假设数据在 A1:A10。想要获取 A1:A10 中第一个最小但不等于零的值。
   • 一种常见方法是结合 IF 和 SMALL，但这通常结合数组公式来处理。例如 (过去很多版本的 Excel 可能需要，现在可能不再严格要求，但思路类似)： =SMALL(IF(A1:A10<>"",A1:A10),1)
     • 解释： IF(A1:A10<>"",A1:A10) 部分生成一个逻辑测试，如果单元格不是空的（<>""），则返回该单元格的值；如果单元格是空的，则返回 FALSE。IF 函数在这里执行逻辑测试，并返回一个 逻辑值 的数组（TRUE/FALSE）或一个 值数组（如果测试为真）。SMALL 函数是在一个多维上下文中运行的，它忽略 FALSE。因此，我们实际上是指 SMALL 函数只考虑 IF 中返回 TRUE 对应的值。
     • 注意： 在 Excel 365 中，动态数组公式会自动扩展；旧版本需要按 Ctrl+Shift+Enter 完成公式输入（称为数组公式）。建议先检查 Excel 版本。

2. 使用 IF 函数排除零值 (返回值本身而非逻辑值)

   • 更健壮的方法，特别是在处理公式返回的空白或错误值时，是直接返回 0 或一个很大的数来表示"忽略"。
   • 例如，排除零： =SMALL(IF(A1:A10<>0, A1:A10),1)
     • 这里，IF(A1:A10<>0, A1:A10)，当单元格 A1:A10 中的值不等于零时，返回该值；等于零时，返回 FALSE。同样，SMALL 忽略 FALSE。
   • 排除空值和零： =SMALL(IF(A1:A10"". A1:A10), B2) 假设 B2 是你想跳过的第 n 小值。
     • IF(A1:A10<>"" , A1:A10)，非空时返回值，空时返回 FALSE。

3. 使用 SUMPRODUCT 和 SIGN 函数

   • 这种方法通常用于 SUM 或 AVERAGE，但对于 SMALL，我们可以类似地理解。
   • SIGN 函数返回 -1, 0, 或 1。零返回 0，非零返回其符号对应的 1 或 -1。
   • 逻辑在于，我们列出所有可能非零但带符号的值，然后只取非零部分，再排序。
   • 例如，排除零： =SMALL(SIGN(A1:A10)*A1:A10,1)
     • 解释： SIGN(A1:A10) 将为每个单元格生成 -1, 0, 或 1。然后将其乘以原始值。数值乘以 1 不变，0 乘以 1 仍是 0，负数乘以 -1 得正（或保持符号，取决于值），正数不变。SMALL 函数：我们传递给 SMALL 的是这些数值转换后的结果（SIGN(A1:A10)*A1:A10）。只要 A1:A10 中的值非零，SIGN(A1:A10)*A1:A10 就等于 A1:A10 中的值；如果 A1:A10 是零，结果是 0。然后 SMALL 会找到所有非零值对应的小值（包括负数，如果存在的话），或者... 等等，如果数据有负数和正数，SIGN 处理后，SMALL 会按数值顺序排序，包括负数。如果希望只针对正数排序（例如，只取正数列中的最小值），SIGN 方法也很有用。但是，仅仅用 `=SMALL(SIGN(A1:A10)
     • A1:A10,1)` 在包含负数和零的情况下，会找出原始数据中所有非零数值的最小值（可能为负）。如果目的就是要找所有非零中的最小值，这是可行的，前提是完全没问题。

4. 使用数组公式 (比较过时，但仍有用)

   • 在所有可能的 IF 方法中，严格意义上的数组表达公式是： =SMALL(IF((A1:A10>0)|(A1:A10<0), A1:A10), B2)
     • 解释： (A1:A10>0) | (A1:A10<0) 是一个条件测试，检查单元格是否不等于 0（无论是正数还是负数）。如果条件为 TRUE，IF 就返回单元格值；如果为 FALSE，返回 FALSE。
     • 这类似于方法 1 和 2，但用 | 替代了 <>0。逻辑是一样的，并且需要按 Ctrl+Shift+Enter 输入。

5. 条件限制

   • 上述方法通常中立。如果我想选出第 n 小的 正数 或 特定位置 的非零值？
   • 例如，只从正数（和零）中排除元素：=SMALL(IF(A1:A10>0, A1:A10), B2)
   • 或者，选出第 n 小的 非零数，但区分正负：=SMALL(IF(A1:A10<>0, A1:A10), B2)

总结： 选择方法取决于数据中的可能值（是否包含负数、空值、文本、错误值）。常见最安全的选择是使用 SMALL 与 IF 结构结合来排除不符合条件的值，例如，通过 SMALL(IF(条件, 要考虑的值), 位置)。熟练掌握这些方法，可以让你在数据处理时更加灵活高效，特别是在Excel这种高效办公软件中，省时省力真的很重要。