如何在Excel中将逻辑或应用于数组 [英] How to apply a logical OR to an array in Excel

问题描述

我试图让Excel通过对两个数组进行或运算来使我成为一个数组。例如，假设两个数组 A1：A3 为 7 ， 8 ， -3 和 B1：B3 为 4 ， -8 ， -8 。

I'm trying to have Excel make me an array by ORing two arrays. As an example, let two arrays A1:A3 be 7, 8, -3 and B1:B3 be 4, -8, -8.

此应该很简单，但是看起来

This should be fairly simple but it appears that

OR ( A1:A3 > 0 ; B1:B3 > 0 )

返回 TRUE 而不是我期望的数组（正确，正确，错误）。

returns TRUE instead of the array I'm expecting (TRUE, TRUE, FALSE).

当然我可以使用类似

(((A1:A3 > 0)*1 + (B1:B3 > 0)*1) >= 1) *1

但是谁愿意维护呢？

此外， CSE（ Ctrl + Shift + Enter ）公式（ http://www.mrexcel.com/articles/CSE-array-formulas-excel.php ），但确实看起来像是黑魔法。

Also, there is probably something to search around "CSE (Ctrl+Shift+Enter) Formulas" (http://www.mrexcel.com/articles/CSE-array-formulas-excel.php) but it really looks like black magic.

我错过了吗？容易吗？

对于那些对此感到好奇的人，我尝试构建的实际公式当然要复杂一些。它试图对所有行（通过 SUMPRODUCT ）进行计数，其中（状态== A或状态== B）和日期=一些单元格。我要查找的OR只是我的 SUMPRODUCT 的一个数组，并且未在单元格中定义（这太容易了）。

For those curious about it, the actual formula I'm trying to build is a little more complex of course. It's trying to count (via SUMPRODUCT) all rows where (status == A OR status == B) AND Date = some cell. The OR I'm looking for is just one array of my SUMPRODUCT and is not defined in cells (that would be too easy).

推荐答案

您通常不能在数组公式中使用OR或AND，因为正如您在此处发现的那样，它们仅返回一个结果（只有在所有条件都为TRUE，或者为TRUE是任意为TRUE，正如您所期望的那样，因此在您的示例中，只要6个值中的至少一个为正，您就将获得TRUE。）

You can't typically use OR or AND in "array formulas" because, as you have discovered here, they return only a single result (AND is TRUE only if all conditions are TRUE, OR is TRUE is any are TRUE, as you would expect, so in your example you'll get TRUE as long as at least one of the 6 values is positive).

我会使用与您的建议类似的方法，有效地使用+代替OR，尽管您不需要前两个* 1，因为+可以强制执行，所以就足够了

I'd use something similar to your suggestion, using + in place of OR effectively, although you don't need the first two *1 because the + co-erces, so this would suffice

=（（（A1：A3> 0）+（B1：B3> 0）> 0）* 1

尽管如果您有一个可以是一回事的单列，那么那是互斥的，因此，也许也许只需要

although if you have a single column which can be one thing or another then that's mutually exclusive, surely, so perhaps that needs just

=（A1：A3 = A）+（A1：A3 = B）

这篇关于如何在Excel中将逻辑或应用于数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！