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
我想要的是发票的最终总数 (430)，计算方式为每件商品的数量 * 价格 (17*20 + 1*10 + 2*10 + 3*20).以下公式正确执行此操作:
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?
不清楚的部分是，当前两个参数的维度不同(这里，范围是 2 个单元格，而标准是 4 个单元格)时，SUMIF 究竟做了什么(它的迭代模式是什么).另外，SUMIF 的输出"是什么?数组?什么尺寸?
此外，如果我忽略数量，只想在看到桌子时加 20，看到椅子时加 10，我想我会这样做:
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 来查看数组结果
然后使用 SUMPRODUCT 将 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}
(对应桌子;椅子;椅子;桌子)
你应该使用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屋！
