SUMPRODUCT(SUMIF()) - 这是如何工作的? [英] SUMPRODUCT( SUMIF() ) - How does this work?
问题描述
我能够构建一个完全符合我想要的公式(从一些例子),但是我无法弄清楚它是如何工作的我有,从单元格A1开始:
I was able to construct a formula that does exactly what I want (from some examples), but yet, I'm unable to figure out how exactly it works. I have, starting with cell A1:
Price $
table 20
chair 10
Invoice Quantity
table 17
chair 1
chair 2
table 3
我想要的是每个项目(17 * 20 + 1 * 10 + 2 * 10 + 3 * 20)以数量*价格计算的发票的最终总数(430)。以下公式正确执行:
What I want is the final total (430) for the invoice which is computed as Quantity*Price for each item (17*20 + 1*10 + 2*10 + 3*20). the following formula correctly does this:
=SUMPRODUCT(B6:B9,SUMIF(A2:A3,A6:A9,B2:B3))
我了解SUMPRODUCT和SUMIF的基础知识。但是在这里,我对SUMIF范围的观点是A2:A3,这使得我认为SUMIF将通过A2和A3进行迭代,而不是通过A8:A11(这是标准)。什么给了?
I understand the basics of SUMPRODUCT and SUMIF. But here, my argument for SUMIF's range is A2:A3, which makes me think the SUMIF would iterate through A2 and A3, and not through A8:A11 (which is the criteria). What gives?
编辑:不清楚的部分是,当前两个参数的尺寸不同时,SUMIF究竟做了什么(它的迭代模式是什么)这里的范围是2个小区,而标准是4个小区)。另外,SUMIF的输出是什么?数组?什么维度?
此外,如果我忽略了数量和只要我看到一张桌子,每当我看到一张椅子,就要加20张,我想我会做的:
In addition, if I ignored the quantity and simply wanted to add 20 whenever I saw a table and 10 whenever I saw a chair, I figured I would do:
=SUMIF(A2:A3,A6:A9,B2:B3)
但这不行,我必须使用一个SUMPRODUCT()函数来使它正常工作并正确地评估为60.将它包含在一个SUM中也不起作用(可能是因为SUMIF不返回数组)为什么?
But that doesn't work, and I have to enclose it with a SUMPRODUCT() for it to work and correctly evaluate to 60. Enclosing it within a SUM doesn't work either (probably because the SUMIF doesn't return an array?) Why?
我已经阅读了一堆教程,仍然无法理解这一点,并且非常感谢对这两种情况的清晰直观的解释。谢谢。
I've read a bunch of tutorials and still can't understand this, and would be most grateful for a clear, intuitive explanation for both these cases. Thank you.
推荐答案
SUMIF
可以产生一系列的结果。如果你拿我的公式
= SUMIF(A6:A9,A2:A3,B6:B9)
它说
SUMIF
can produce an array of results. If you take my formula
=SUMIF(A6:A9,A2:A3,B6:B9)
it says
对于A2(即表)中的条件
- 查看A6:A9
- 其中表匹配,求和相应的值在B6中:B9
- 返回20(即17 +0 +0 +3)
- 存储在数组的第一个位置
For the criteria in A2 (ie table)
- look at A6:A9
- where table is matched, sum the corresponding value in B6:B9
- returns 20 (ie 17 +0 +0 +3)
- this is stored in the first position of the array
然后对于A3中的标准(即主席)
- 查看A6:A9
- 匹配的表,将B6中的相应值相加: B9
- 返回3(即0 +1 +2 +0)
- 这存储在数组的第二个位置
Then for the criteria in A3 (ie chair)
- look at A6:A9
- where table is matched, sum the corresponding value in B6:B9
- returns 3 (ie 0 +1 +2 +0)
- this is stored in the second position of the array
所以从 SUMIF
的结束数组是{20:3}
So the end array from the SUMIF
is {20:3}
你可以通过在Excel的公式栏中突出显示SUMIF公式,然后按F9
You can see the array result by highlighting the SUMIF formula in Excel's formula bar and then pressing F9
然后使用 SUMPRODUCT
以$ code> SUMIF 中的数值乘以B2:B3中的$值,以获得总美元
Then use SUMPRODUCT
to multiple the count in the SUMIF
by the $ values in B2:B3 to get total dollars
= {20; 3} * {20:10}
= 20 * 20 + 3 * 10
= 430
={20;3}*{20:10}
=20*20 + 3*10
= 430
第1部分
而不是
SUMIF(A2:A3,A6:A9,B2:B3)
产生一个四元素数组
= {20; 10; 10; 20}
(对应于table; chair; chair; table)
您应该使用
SUMIF(A6:A9,A2:A3,B6:B9)
,其总和B6中的值:B9针对A2中的两个标准:A3给出期望的结果
= {20; 3}
(对应于表;椅子)
,然后使用 SUMPRODUCT
来重载数组,即
= SUMPRODUCT(SUMIF (A6:A9,A2:A3,B6:B9),B2:B3)
= {20; 3} * {20:10}
= 430
and then use SUMPRODUCT
to weight your array, ie
=SUMPRODUCT(SUMIF(A6:A9,A2:A3,B6:B9),B2:B3)
={20;3}*{20:10}
=430
第2部分
使用 COUNTIF
返回数组的椅子和桌子的数量,然后使用 SUMPRODUCT
= SUMPRODUCT(B2:B3,COUNTIF( A6:A9,A2:A3))
= {20; 10} * {2; 2}
= 60
Part 2
Use COUNTIF
to return an array of the number of chairs and tables and then multiply by the vales using SUMPRODUCT
=SUMPRODUCT(B2:B3,COUNTIF(A6:A9,A2:A3))
={20;10} * {2;2}
=60
这篇关于SUMPRODUCT(SUMIF()) - 这是如何工作的?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!