是否有功能，将返回“”当输入ref为空时，其内容是否为空？ [英] Is there a function, that would return "" when input ref is empty, and its contents if it is not?

问题描述

IMO Excel对空单元格有奇怪的处理。

我正在构建一个复杂的数组公式。引用的范围之一包含单元格，可以是也可以不是空的，如果不是空的，它们可以包含数值和字符串。

如果单元格不为空，我可以使用什么功能来获取单元格的值，而（或任何其他非数字值，例如＃N / A ）如果单元格是空的？

我想得到这样的工作：

  = MIN（OFFSET（<包含文本，数字和空单元格的列向量> ;;<索引的行向量> -1; 0） ）
  

这种形式的公式返回一个 #ARG 错误，正如为什么这个数组公式不起作用的答案所解释的？ / a>。

但是，当我在 OFFSET 之前加上 N ，它将任何空单元变换为 0 ，所以净结果是 0 （除非有负数在列向量中）

  = MIN（L（OFFSET（< column vector> ;;< -1; 0）））
  

是否有任何公式，只解释引用 OFFSET 返回的引用，保留空单元格的空或者也许有另一种解决问题的方法，如

  = MIN（IF（OFFSET（< column vector& <索引的行向量> -1; 0）=，L（OFFSET（<列向量> ;;<索引的行向量> -1; 0）），））
  

（此示例也与 #ARG 失败，因为我明白，我需要取消引用 = 测试的数组引用。

如果可能的话，我宁愿使用Excel 2007集内置函数。没有VBA。

我将接受任何解决方案，它使用不变数量的单元格，无论每个输入数组的大小。

---

编辑：

无论如何，由 OFFSET 返回的数组是错误的？这个简单的例子很好用：

...而由 OFFSET 返回的数组想要在公式中单独使用。

解决方案

可能还有另一个选项，但目前看不到它.....

您可以使用像这样的IF过滤掉零值

= MIN（IF（N（OFFSET （INDIRECT（$ A $ 2），$ C4：G4-1,0 $））≤;大于0，N（OFFSET（INDIRECT（$ A $ 2），$ C4：$ G4-1,0））））

但是，这不会区分范围内的任何实际零点和N函数遇到空格或文本时产生的零点

修改

此版本应该工作

= MIN（IF（COUNTBLANK（OFFSET（INDIRECT（$ A $ 2），$ C4：$ G4-1,0,1））+ LEN（T（OFFSET（INDIRECT（$ A $ 2） ，$ C4：$ G4-1,0,1））） ，N（OFFSET（INDIRECT（$ A $ 2），$ C4：$ G4-1,0,1））））

这里可能并不完全相关，但问题是找到可以使用这种类型的设置来处理OFFSET返回的引用数组的函数 - N 和 T 工作如下所示，还有 COUNTBLANK 。可以在OFFSET输出上使用的其他功能是 SUBTOTAL 和 COUNTIF 。请注意， COUNTBLANK （以及 SUBTOTAL 和 COUNTIF ）可以在 T 和 N 之间工作，只能使用单个值 - 如果后者的功能适用于他们简单的范围看看范围中的第一个值 - 因为我能够使用 OFFSET 而没有height参数，但是您需要使用 COUNTBLANK （这是一个很好的习惯，所以 OFFSET 应该有最终的1在这里

= OFFSET（INDIRECT（$ A $ 2），$ C4：$ G4-1,0,1）

IMO Excel has weird treatment of empty Cells.

I am building a complex array formula. One of the referenced ranges contain cells, that may, or may not be empty, and if not empty, they can contain both numeric values and strings.

What function can I use, to get the value of the cell if the cell is not empty, and "" (or anything other non-numeric, e.g. #N/A) if the cell is empty?

I want to get something like this working:

=MIN(OFFSET(<column vector that contains text, numbers and empty cells>;<row vector of indices>-1;0))

This form of formula returns an #ARG error, as was explained in the answer to Why this array formula doesn't work?.

But when I prefix the OFFSET with N, it transforms any empty cell into 0, so the net result is 0 (unless there are negative numbers in the column vector).

=MIN(L(OFFSET(<column vector>;<row vector of indices>-1;0)))

Is there any formula, that only dereferences the reference returned by OFFSET preserving the "emptyness" of the empty cell? Or maybe there is an alternate way of solving the problem, like

=MIN(IF(OFFSET(<column vector>;<row vector of indices>-1;0)="",L(OFFSET(<column vector>;<row vector of indices>-1;0)),""))

(This example also fails with #ARG, because, as I understand, I need to dereference the array reference for the = test as well).

If it is at all possible, I prefer to keep with Excel 2007 set of built-in functions. And no VBA.

I would accept any solution, that uses constant number of cells irregardless of the size of each input array.

---

EDIT:

As a side remark I wonder what is wrong with the arrays returned by OFFSET anyway? This simple example works perfectly:

...while the array returned by OFFSET somehow wants to be alone in the formula.

解决方案

There may be another option but I don't see it at the moment.....

You can filter out zeroes by using an IF like this

=MIN(IF(N(OFFSET(INDIRECT($A$2),$C4:$G4-1,0))<>0,N(OFFSET(INDIRECT($A$2),$C4:$G4-1,0))))

but that won't distinguish between any actual zeroes in your range and those produced when the N function encounters blanks or text

Edit

This version should work

=MIN(IF(COUNTBLANK(OFFSET(INDIRECT($A$2),$C4:$G4-1,0,1))+LEN(T(OFFSET(INDIRECT($A$2),$C4:$G4-1,0,1))),"",N(OFFSET(INDIRECT($A$2),$C4:$G4-1,0,1))))

Perhaps not completely relevant here but the problem is finding functions that can deal with the "array of references" returned by OFFSET with this type of setup - N and T work as shown here and also COUNTBLANK. Other functions that can be used on the OFFSET output are SUBTOTAL and COUNTIF. Note that COUNTBLANK (along with SUBTOTAL and COUNTIF) can work on ranges while T and N will only work with single values - if the latter functions are applied to ranges they simply look at the first value in the range - because of that I was able to use OFFSET without the "height" parameter but you need that with COUNTBLANK (and it's a good habit to get in to so OFFSET should have the final 1 as here

=OFFSET(INDIRECT($A$2),$C4:$G4-1,0,1)

这篇关于是否有功能，将返回“”当输入ref为空时，其内容是否为空？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！