在一个范围内具有多个条件的求和产品 [英] Sumproduct with multiple criteria on one range

问题描述

在数据集中，我有调查参与者给出的答案.在一个示例中，答案的编号为1到5，其中1表示是，而2到5表示否.

In a dataset I have answers that participants to a survey gave. The answers are in one example numbered 1 to 5, with 1 being yes, and 2 to 5 being variants of no.

有人问了20个左右类似的问题，参与者可以是20个子组中的任何一个.问题分为6类.

20 or so similar questions have been asked, and participants can be in either one of 20 subgroups. Questions were categorized into 6 classes.

现在处理此类数据集的最佳方法通常是使用数据透视表，但是数据集的设置方法不适用于数据透视表，并且由于数据集的绝对大小重塑效率不高.

Now the best way to go about such a dataset would normally be the use of a pivot-table, however the way the data is set up doesn't work with a pivot table, and due to the sheer size of the dataset remodelling isn't efficient.

要提取某个子类别中对某个班级中的问题回答为是的人数，我使用以下函数:

To extract the amount of people in a certain subgroup that answered yes for questions in a certain class I use the following function:

=SuMPRODUCT(--(Test!D$4:$CC$1824=1)*(Test!$C$4:$C$1824=$C3)*(Test!$D$3:$CC$3=D$2))

其中Test!D$4:$CC$1824是给出答案的范围，其他两个分别是子组和类的范围.

In which Test!D$4:$CC$1824 is the range where answers are given, and the other two are ranges for subgroup and classes respectively.

通过--(Test!D$4:$CC$1824=1)，我将所有数据都转换为0，除非参与者回答是(单元格值= 1).

By using --(Test!D$4:$CC$1824=1) I convert all data to 0's except for where participants answered yes (cell value = 1).

现在，我想对他们回答否"的地方做同样的事情，所以值是2或3或4或5.理想的方法是将一些OR逻辑附加到第一个测试中，结果是这个:--(Test!D$4:$CC$1824={2,3,4,5})

Now I would like to do the same thing for where they answered no, so the value is either 2 or 3 or 4 or 5. The ideal way would be to append some OR logic into the first test, coming about something like this: --(Test!D$4:$CC$1824={2,3,4,5})

这当然是行不通的，但是除了将第一部分重新键入4次并将它们加在一起之外，还有什么简单的记法吗?

Ofcourse this doesn't work, but is there any simple notation besides retyping the first part 4 times, and adding them together?

推荐答案

可以通过将所有可能性加在一起来模仿OR操作.

The OR operation can be mimicked by adding all of the possibilities together.

=SuMPRODUCT(((Test!D$4:$CC$1824=2)+(Test!D$4:$CC$1824=3)+(Test!D$4:$CC$1824=4)+(Test!D$4:$CC$1824=5))*
            (Test!$C$4:$C$1824=$C3)*(Test!$D$3:$CC$3=D$2))

如果有可能两个是正确的(在这种情况下没有)，则将总和包装在SIGN函数中，以便只得到零或一.

If there is any possibilitiy that two could be correct (in this case there isn't) wrap the sum in the SIGN function to get only zero or one.

=SuMPRODUCT(SIGN((Test!D$4:$CC$1824=2)+(Test!D$4:$CC$1824=3)+(Test!D$4:$CC$1824=4)+(Test!D$4:$CC$1824=5))*
            (Test!$C$4:$C$1824=$C3)*(Test!$D$3:$CC$3=D$2))

这篇关于在一个范围内具有多个条件的求和产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！