# 如何簡化EXCEL公式?

COUNTIF(\$D8:\$AH8,WORK!\$A\$1)*7+COUNTIF(\$D8:\$AH8,WORK!\$B\$1)*10.5+COUNTIF(\$D8:\$AH8,WORK!\$C\$1)*7+COUNTIF(\$D8:\$AH8,WORK!\$D\$1)*7+COUNTIF(\$D8:\$AH8,WORK!\$E\$1)*7+COUNTIF(\$D8:\$AH8,WORK!\$F\$1)*7+COUNTIF(\$D8:\$AH8,WORK!\$G\$1)*7+COUNTIF(\$D8:\$AH8,WORK!\$H\$1)*7+COUNTIF(\$D8:\$AH8,WORK!\$I\$1)*7+COUNTIF(\$D8:\$AH8,WORK!\$A\$2)*7+COUNTIF(\$D8:\$AH8,WORK!\$B\$2)*7+COUNTIF(\$D8:\$AH8,WORK!\$C\$2)*7+COUNTIF(\$D8:\$AH8,WORK!\$D\$2)*7+COUNTIF(\$D8:\$AH8,WORK!\$A\$3)*7+COUNTIF(\$D8:\$AH8,WORK!\$B\$3)*7+COUNTIF(\$D8:\$AH8,WORK!\$C\$3)*7+COUNTIF(\$D8:\$AH8,WORK!\$D\$3)*7+COUNTIF(\$D8:\$AH8,WORK!\$E\$3)*7+COUNTIF(\$D8:\$AH8,WORK!\$F\$3)*14+COUNTIF(\$D8:\$AH8,WORK!\$G\$3)*7+COUNTIF(\$D8:\$AH8,WORK!\$H\$3)*7+COUNTIF(\$D8:\$AH8,WORK!\$I\$3)*7+COUNTIF(\$D8:\$AH8,WORK!\$J\$3)*7+COUNTIF(\$D8:\$AH8,WORK!\$A\$5)*5+COUNTIF(\$D8:\$AH8,WORK!\$B\$5)*5+COUNTIF(\$D8:\$AH8,WORK!\$C\$5)*5+COUNTIF(\$D8:\$AH8,WORK!\$D\$5)*5+COUNTIF(\$D8:\$AH8,WORK!\$E\$5)*5+COUNTIF(\$D8:\$AH8,WORK!\$F\$5)*5+COUNTIF(\$D8:\$AH8,WORK!\$G\$5)*5+COUNTIF(\$D8:\$AH8,WORK!\$H\$5)*5.5+COUNTIF(\$D8:\$AH8,WORK!\$I\$5)*4.5+COUNTIF(\$D8:\$AH8,WORK!\$J\$5)*4

### 2 個解答

• 4 年前
最佳解答

=SUMPRODUCT(COUNTIF(D8:AH8,Sheet1!A1:J5)*{7,10.5,7,7,7,7,7,7,7,7;7,7,7,7,7,7,7,7,7,7;7,7,7,7,7,14,7,7,7,7;0,0,0,0,0,0,0,0,0,0;5,5,5,5,5,5,5,5.5,4.5,4})

• Daniel
Lv 7
4 年前

.

=SUMPRODUCT(COUNTIF(D8:AH8,WORK!A1:I1)*{7,10.5,7,7,7,7,7,7,7})

+SUMPRODUCT(COUNTIF(D8:AH8,WORK!A2:D2)*7)

+SUMPRODUCT(COUNTIF(D8:AH8,WORK!A3:J3)*{7,7,7,7,7,14,7,7,7,7})

+SUMPRODUCT(COUNTIF(D8:AH8,WORK!A5:J5)*{5,5,5,5,5,5,5,5.5,4.5,4})

因每一行要計算的格數不同，因此一行一行加，看得不會眼花，也較不易出錯