如何簡化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

因為work裡的東西不斷增加

所以公式會愈來愈長

我的excel版本是2003

麻煩各位高手了

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})

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

還有問題?馬上發問,尋求解答。