替代数组求和公式 [英] Alternate for Array Sum Formula

问题描述

表格复制为文本

Column1 Column2 Column3 Column4 Column5 Column6
A   AA  AAA 100 95  92
A   AA  AAA 85  83  81
A   AA  BBB 200 199 160
A   BB  AAA 65  55  49
B   AA  AAA 89  88  83
B   AA  BBB 150 149 145
B   BB  AAA 140 135 
B   BB  BBB 190 185 
B   AA  AAA 510     


    AA              
        AAA BBB     
    A   173 160     
    B   593 145     

更多说明

基本上，我想要给定条件的第6列"之和，但是第6列中的数据只能在经过一定的延迟w.r.t后才能输入.第1栏，第2栏和第3栏第4列.

Basically i want the sum of "Column 6" for the given criteria but the data in Column 6 can only be entered after some delay w.r.t. Column 1, Column 2, Column 3 & Column 4.

直到输入第6列数据，我希望excel使用第5列中的可用数字，该数字在经过一定的延迟后也要输入.第1栏，第2栏和第3栏第4列，但在第6列之前.

Till Column 6 data is entered, i want excel to use the number available in Column 5 which is also entered after some delay w.r.t. Column 1, Column 2, Column 3 & Column 4 but before Column 6.

在输入第5列数据之前，我希望excel使用第4列中可用的数字.

And till Column 5 data is entered, i want excel to use the number available in Column 4.

现在，我对下面的帖子中包含的两个SUM/IF安排很熟悉.

Now I am familiar with two SUM/IF arrangements as included below in post.

第一个是数组和/如果排列，写起来很方便，但计算时间却非常长，仅一列就需要1.5秒，而我在一张纸上大约有100列，大约有9张纸.

First one is array sum/if arrangement which is convenient to write but results in terribly long calculation time with 1.5 seconds for just one column and I have over 100 columns in one sheet and about 9 sheets.

第二种方法是使用SUMIFS，它需要花费大量的时间来写，但是相对较好的列计算时间为0.5秒，但是仍然很高.

Second one is using SUMIFS which requires extensive time to write but relatively better calculation time of 0.5 seconds for column but is still quite high.

现在我需要取消数组的安排，但是这样做会花费很多时间，我想知道是否还有更好/其他的安排.

Now I need to do away with the array arrangement but doing so will take quite some time and I want to know if there is any better/other arrangement.

请让我知道其他可以获得所需结果的安排，我将检查该安排的计算时间.如果另一种安排也方便写，那将是一个加分.

Just let me know other arrangement which can get the required result and I will check the arrangement for calculation timing. If the other arrangement is also convenient to write than that is a plus.

这是我的桌子:

我想添加最右边的非空列，即其中有一个数字，但要包含单元格D15中前三列的条件.

And I want to add the right most columns which are not empty i.e. have a number in it, but with the criteria for the first three columns in cell D15.

我只找到添加图像的选项.请让我知道如何上传Excel文件. 在此处输入图片描述

I only found option to add image. Please let me know how to upload excel file. enter image description here

有人可以建议使用此数组公式的替代方法，以便它可以更快地进行计算

Can somebody please suggest an alternate to this array formula so it can calculate way faster

{=SUM(
IF(
($B$2:$B$10=$C15)*
($C$2:$C$10=$C$13)*
($D$2:$D$10=D$14)>0,
IF(
$G$2:$G$10<>"",
$G$2:$G$10,
IF(
$F$2:$F$10<>"",
$F$2:$F$10,
$E$2:$E$10))))}

我在下面尝试过将计算时间减少到1/3，但是对于我正在处理的大数据来说，它的键入太多了

I have tried below which reduces the calculation time to 1/3 but it is too much typing for the large data I am dealing with

=SUMIFS(
$G$2:$G$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14,
$G$2:$G$10,"<>"&"")
+SUMIFS(
$F$2:$F$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14,
$G$2:$G$10,"="&"",
$F$2:$F$10,"<>"&"")
+SUMIFS(
$E$2:$E$10,
$B$2:$B$10,$C15,
$C$2:$C$10,$C$13,
$D$2:$D$10,H$14,
$G$2:$G$10,"="&"",
$F$2:$F$10,"="&"")

推荐答案

如果可以使用帮助列(应该如此)，可以在帮助单元格中使用此公式并向下拖动. (在下面的示例中，该公式位于单元格H2中并向下拖动.)

If you're OK with using a helper column (which you should be), you can use this formula in a helper cell and drag down. (In my example at bottom, this formula is in cell H2 and drag down.)

= INDEX(E2:G2,MATCH(-1E+300,E2:G2,-1))

这会将第4 5或6列中的所有数据全部归为一列.

This gets all of the data in either column 4 5 or 6 all into one column.

然后您可以在单元格D15中使用更简单的SUMIFS公式:

Then you can use a simpler SUMIFS formula in cell D15:

= SUMIFS($H$2:$H$10,         // Sum range (helper column)
         $B$2:$B$10,$C15,    // Criteria 1 (A or B)
         $C$2:$C$10,$C$13,   // Criteria 2 (AA or BB)
         $D$2:$D$10,D$14)    // Criteria 3 (AAA or BBB)

请参见下面的工作示例:

See below, working example:

免责声明

此答案将简化您的公式，但是我不确定这是否有助于解决您遇到的性能问题. SUMIFS本身，我认为这可能不是计算时间长的原因.可能由于电子表格的其他部分使用效率低下的公式和/或涉及易失性单元格的公式而导致计算时间较长，但这只是一个猜测，因为我不知道电子表格的其余部分是什么样.

This answer will simplify your formulas, but I'm not sure if this will help with the performance problems you are experiencing. SUMIFS in itself I don't see being likely the cause of long calculation times. Probably you are experiencing long calculation times because other parts of your spreadsheet are using inefficient formulas and/or formulas involving volatile cells, but that is just a guess because I have no idea what the rest of your spreadsheet looks like.

这篇关于替代数组求和公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！