3742Excel 筆數總數不變時各班級筆數的變化－錦子老師

3742

Excel 筆數總數不變時各班級筆數的變化

董源：錦子老師您好，假設今天有總數1000枝的筆，要分給701-704四個班級，正常平均分配時一班分得250枝筆，但是如果有班級得到評比可多得40枝筆，但這40枝筆需從1000枝筆中扣除，此時等於該班得到280枝，而其餘各班就只分得240枝筆，此外得奬者又可多分得20枝，但一樣需從總數扣除，這樣式子該如何設，煩請前輩給予解惑，謝謝您。

錦子老師：董源，關於這個問題可以用公式來解決：

首先先統計評筆欄中含有「有」的筆數(COUNTIF($C$3:$C$6,"有"))並乘以C2儲存格的數值，算出要扣除的數量。

再統計得獎欄中含有「有」的筆數(COUNTIF($D$3:$D$6,"有"))並乘以D2儲存格的數值，算出要扣除的數量。

再將總筆數減掉評筆數及得獎數扣除額($B$2-SUM(COUNTIF($C$3:$C$6,"有")*$C$2,COUNTIF($D$3:$D$6,"有")*$D$2)))，由於有個班級，所以除以4。

接著算出各個班級的評筆數及得獎數增加額(SUMPRODUCT((C3:D3="有")*($C$2:$D$2)))。

最後將二者相加即得到各個班級的額度，所以點取B3儲存格輸入公式：「=($B$2-SUM(COUNTIF($C$3:$C$6,"有")*$C$2,COUNTIF($D$3:$D$6,"有")*$D$2))/4+SUMPRODUCT ((C3:D3="有")*($C$2:$D$2))」，並將公式複製到B4:B6儲存格。