非相邻像元作为数组函数的输入(MIN和ISBLANK) [英] Non-adjecent cells as input to array function (MIN and ISBLANK)
问题描述
问了这个问题，由于对特定问题的回答是错字，因此将其删除:
This question was asked and as the answer to the specific question was a typo it was deleted:
这是问题:
我正在尝试查找每列最少两个(不相邻)的单元格，并将这些单元格求和成一定数量的列(总共13个).
I'm trying to find the minimum of two (non-adjacent) cells per column and sum these for a number of columns (13 in total).
复杂的是，我希望该函数将空单元格视为零.
What complicates it is that I'd like the function to treat empty cells as zero.
只要单元格相邻，我就可以使它工作，但是当它们不相邻时，excel会弹出此函数的参数过多"的弹出窗口.
I can get it to work as long as the cells are adjacent, but when they are not, excel gives a "too many arguments for this function" pop-up.
我对相邻细胞的计算公式是这样的(不完全漂亮，对不起！):
The formula I have for adjecent cells is this (not exactly pretty, sorry!):
{=SUM(MIN(IF(ISBLANK(P3:P4);0;P3:P4));MIN(IF(ISBLANK(Q3:Q4);0;Q3:Q4));MIN(IF(ISBLANK(R3:R4);0;R3:R4));MIN(IF(ISBLANK(S3:S4);0;S3:S4));MIN(IF(ISBLANK(T3:T4);0;T3:T4));MIN(IF(ISBLANK(U3:U4);0;U3:U4));MIN(IF(ISBLANK(V3:V4);0;V3:V4));MIN(IF(ISBLANK(W3:W4);0;W3:W4));MIN(IF(ISBLANK(X3:X4);0;X3:X4));MIN(IF(ISBLANK(Y3:Y4);0;Y3:Y4));MIN(IF(ISBLANK(Z3:Z4);0;Z3:Z4));MIN(IF(ISBLANK(AA3:AA4);0;AA3:AA4));MIN(IF(ISBLANK(AB3:AB4);0;AB3:AB4)))}
这将在人员月总数"列中提供所需的输出.
This gives the desired output in the column "person months total".
I have tried to use the CHOOSE function for non-adjacent cells as a test (similar to this question), but this gives the "There's something wrong with this formula" pop-up
=SUM(MIN(IF(ISBLANK(CHOOSE{1;2};P16;P18));0;CHOOSE({1;2};P16;P18)))
所以现在我想知道，这完全可以完成吗?我想念什么吗?
So now I'm wondering, can this be done at all? Am I missing something?
我将感谢您的帮助！
亲切的问候， 艾米
Kind regards, Amy
尽管此问题的答案是在第一个CHOOSE之后缺少(，但我开始研究那个丑陋的长公式的简单版本，并希望将其发布在此处.所以问题是，有没有使用vba的简单方法?"
While the answer to this question was that there was a missing ( after the first CHOOSE, I started working on a simpler version to that hideous long formula and wanted to post it here. So the question is, "Is there a simpler method not using vba?"
推荐答案
范围实际上是按行相邻的，但是通过逐列比较，我们可以以数组形式使用MMULT.通过使用MMULT，我们可以创建具有最小数字和0 s的数组并将其求和:
With the ranges actually being adjacent by rows, but comparing column by column we can use MMULT in an array form. By using MMULT we can create an array of the smallest numbers and 0s and sum them:
=SUM(MMULT(N(IF(A1:E1>A2:E2,IF(A2:E2<>"",A2:E2),IF(A1:E1<>"",A1:E1))),TRANSPOSE(COLUMN(A1:E1)^0)))
这是一个数组公式，退出编辑模式时必须使用Ctrl-Shift-Enter而不是Enter进行确认.
This is an array formula and must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
仔细考虑一下，这要简单得多:
way over thought it, this is much simpler:
=SUM(IF(A1:E1>A2:E2,IF(A2:E2<>"",A2:E2),IF(A1:E1<>"",A1:E1)))
仍然是一个数组公式.
这篇关于非相邻像元作为数组函数的输入(MIN和ISBLANK)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
