vba Excel Q - 带有二维数组的 SUMIFS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31505241/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Q - SUMIFS with a 2 dimensional array
提问by Joseph Kadoch
I have a 2-D array: dates on a horizontal axis and identification numbers on a vertical axis.
我有一个二维数组：水平轴上的日期和垂直轴上的标识号。
I want the sums conditioned on a particular date and ID, and I want to know how to do this using SUMIFS.
我想要以特定日期和 ID 为条件的总和，我想知道如何使用 SUMIFS 执行此操作。
For some reason, it seems like I cannot since the array is 2-D while the criteria ranges are 1-D. Can anyone give me any advice on other formulas I can use?
出于某种原因，似乎我不能，因为数组是二维的，而标准范围是一维的。任何人都可以就我可以使用的其他公式给我任何建议吗？
In other words, I would like to add the values that satisfy the ID and date I select; there is one or more data point that satisfies the conditions. This is why the SUMIF function is relevant.
换句话说，我想添加满足我选择的ID和日期的值；有一个或多个数据点满足条件。这就是 SUMIF 函数相关的原因。
回答by rwilson
With this data you will not be able to use a SUMIF forumula. Here's a formula you can use:
有了这些数据，您将无法使用 SUMIF 论坛。这是您可以使用的公式：
=SUM(IF($B:$B=C9,IF($F:$K=B9,$F:$K)))
Change the addresses where appropriate and be sure and enter it by pressing CTRL + SHIFT + ENTER. You can also use the below formula to avoid pressing CTRL + SHIFT + ENTER:
在适当的地方更改地址，并确保按 CTRL + SHIFT + ENTER 输入地址。您还可以使用以下公式来避免按 CTRL + SHIFT + ENTER：
=SUMPRODUCT(($B:$B=C9)*($F:$K=B9)*$F:$K)
回答by nwhaught
Assuming that you're looking for an intersection of an ID and a Date, you can use the following:
假设您正在寻找 ID 和日期的交集，您可以使用以下内容：
=INDIRECT(ADDRESS(MATCH([ID Number],A:A,0),MATCH([Date],1:1,0)))
=INDIRECT(ADDRESS(MATCH([ID Number],A:A,0),MATCH([Date],1:1,0)))
INDIRECTallows you to type in an address as plain text and returns the value
INDIRECT允许您以纯文本形式输入地址并返回值
ADDRESSturns the numbers for rows and columns into a regular address
ADDRESS将行和列的数字转换为常规地址
MATCHfinds where in a row or column a given value is located.
MATCH查找给定值在行或列中的位置。
回答by AndRieYN
I just wanted to add that the array version of the 2D summation in the answer above
我只想在上面的答案中添加二维求和的数组版本
=SUM(IF($B:$B=C9,IF($F:$K=B9,$F:$K)))
will work better if your data table $F$2:$K$6 has blanks (or other non-numeric values) because it will sum only the values that match criteria specified by $B$2:$B$6=C9 $F$1:$K$1=B9 and ignore all others.
如果您的数据表 $F$2:$K$6 有空格（或其他非数字值）会更好，因为它只会对符合 $B$2:$B$6=C9 $F$1:$ 指定条件的值求和K$1=B9 忽略所有其他的。
Generally, you probably will not have blanks or other non-numeric values in your data table but I just wanted to throw this out there in case it helps someone. It certainly helped me, and I had fun playing with both 2D summation examples above. :)
通常，您的数据表中可能不会有空格或其他非数字值，但我只是想把它扔掉，以防它对某人有帮助。它当然对我有帮助，而且我在上面的两个 2D 求和示例中玩得很开心。:)
