I have a table with students' answers to 20 math problems like this:
我有一张表格，上面有20道数学题的答案:
A | B | C | D | E |...
------------+-----+-----+-----+-----+...
problem no | 1 | 2 | 3 | 4 |...
------------+-----+-----+-----+-----+...
right answer| 3 | 2 | A | 15 |...
------------+-----+-----+-----+-----+...
student1 | 3 | 4 | A | 12 |...
student2 | 2 | 2 | C | 15 |...
student3 | 3 | 2 | A | 13 |...
Now a need a column that counts the 'right' answers for each student.
现在需要一个列来计算每个学生的正确答案。
I can do it this so:
我可以这样做:
=(IF(D$3=D5;1;0))+(IF(E$3=E5;1;0))+(IF(F$3=F5;1;0))+......but it's not the nicest way :)
1 个解决方案
#1
1
This is a typical use case for SUMPRODUCT:
这是SUMPRODUCT的典型用例:
A B C D E F G
1 problem no 1 2 3 4
2 right answer 3 2 A 15 right answers per student
3 student1 3 4 A 12 2
4 student2 2 2 C 15 2
5 student3 3 2 A 13 3
Formula in G3:
=SUMPRODUCT($B$2:$E$2=$B3:$E3)
If there are more problem numbers, then the column letters in $E$2 and $E3 have to be increased.
如果有更多的问题编号，则必须增加$E$2和$E3的列字母。
How it works: SUMPRODUCT takes its inner functions as array formulas. So the $B$2:$E$2=$B3:$E3 becomes a matrix of {TRUE, FALSE, TRUE, FALSE} depending of if $B$2=$B3, $C$2=$C3, $D$2=$D3, $E$2=$E3.
它的工作原理:SUMPRODUCT将它的内部函数作为数组公式。所以$B$2 $E$2=$B3 $E3变成了{TRUE, FALSE, TRUE, FALSE}的矩阵取决于$B$2=$B3 $C$2=$C3 $D$2=$D3 $E$2=$E3 $E$2=$E3。
In Libreoffice or Openoffice TRUE is 1 and FALSE is 0. So the SUMPRODUCT sums all TRUEs.
在Libreoffice或Openoffice中，TRUE为1,FALSE为0。所以和积和所有的值。
In Excel you have to get the boolean values in numeric context first. So the Formula in Excel will be =SUMPRODUCT(($B$2:$E$2=$B3:$E3)*1).
在Excel中，必须首先获得数值上下文中的布尔值。Excel中的公式是=SUMPRODUCT($B$2:$E$2=$B3:$E3)*1)
The formula in Row 3 then can be filled down for all student rows. The $ before the row number 2 ensures that thereby the row of the right answers not changes.
第3行中的公式可以为所有的学生行填满。第2行前面的$确保正确答案的行不会改变。
Greetings
问候
Axel
阿克塞尔
#1
1
This is a typical use case for SUMPRODUCT:
这是SUMPRODUCT的典型用例:
A B C D E F G
1 problem no 1 2 3 4
2 right answer 3 2 A 15 right answers per student
3 student1 3 4 A 12 2
4 student2 2 2 C 15 2
5 student3 3 2 A 13 3
Formula in G3:
=SUMPRODUCT($B$2:$E$2=$B3:$E3)
If there are more problem numbers, then the column letters in $E$2 and $E3 have to be increased.
如果有更多的问题编号，则必须增加$E$2和$E3的列字母。
How it works: SUMPRODUCT takes its inner functions as array formulas. So the $B$2:$E$2=$B3:$E3 becomes a matrix of {TRUE, FALSE, TRUE, FALSE} depending of if $B$2=$B3, $C$2=$C3, $D$2=$D3, $E$2=$E3.
它的工作原理:SUMPRODUCT将它的内部函数作为数组公式。所以$B$2 $E$2=$B3 $E3变成了{TRUE, FALSE, TRUE, FALSE}的矩阵取决于$B$2=$B3 $C$2=$C3 $D$2=$D3 $E$2=$E3 $E$2=$E3。
In Libreoffice or Openoffice TRUE is 1 and FALSE is 0. So the SUMPRODUCT sums all TRUEs.
在Libreoffice或Openoffice中，TRUE为1,FALSE为0。所以和积和所有的值。
In Excel you have to get the boolean values in numeric context first. So the Formula in Excel will be =SUMPRODUCT(($B$2:$E$2=$B3:$E3)*1).
在Excel中，必须首先获得数值上下文中的布尔值。Excel中的公式是=SUMPRODUCT($B$2:$E$2=$B3:$E3)*1)
The formula in Row 3 then can be filled down for all student rows. The $ before the row number 2 ensures that thereby the row of the right answers not changes.
第3行中的公式可以为所有的学生行填满。第2行前面的$确保正确答案的行不会改变。
Greetings
问候
Axel
阿克塞尔