EXCEL 計最高的2個分數問題

J.PNG
2015-4-16 16:55


計分方法如下:
ENG 同 MATH一定要計 +
SUBJECT B 到 G計最好的一科 +
非ENG同MATH的最好2科(可以係SUBJECT B 到 G中的科但不能同之前最好的一科重複)

請問點寫FORMULA?
THX

SUBJECT B 到 G計最好的一科 +
非ENG同MATH的最好2科(可以係SUBJECT B 到 G中的科但不能同之前最好的一科重複)

-> 可不可當成 非ENG同MATH 最好3科 ?
用 sorting ( 降序 )複製去另一地方
=MAX(C3,C4,C5,C6,C7,C8)+CHOOSE(2,C3,C4,C5,C6,C7,C8)+CHOOSE(3
,C3,C4,C5,C6,C7,C8)

TOP

SUBJECT B 到 G計最好的一科 +
非ENG同MATH的最好2科(可以係SUBJECT B 到 G中的科但不能同之前最好的一科重 ...
usei 發表於 2015-4-16 18:06


thx ching
但最好3科有可能冇計到B至G其中一科...
因為一定要計B至G中的最好一科

TOP

本帖最後由 usei 於 2015-4-16 22:02 編輯

回覆 3# sfij

上面 3 個數叫做 X1 , X2 , X3 (非ENG同MATH 最好3科)
X4 = max (B 到 G) <- B至G中的最好一科

X5=IF(OR(X1=X4,X2=X4,X3=X4)=1,X4,MIN(X1,X2,X3))

X5 = X4 <-前 3 個數有包 X4  , 不要 X4
X5 = MIN(X1,X2,X3) <-前 3 個數無 X4 , 要 X4 , 不要前 3 個最低 1 個

X6 = X1 + X2 + X3 + X4 - X5

TOP

本帖最後由 sfij 於 2015-4-17 14:13 編輯
回覆  sfij

上面 3 個數叫做 X1 , X2 , X3 (非ENG同MATH 最好3科)
X4 = max (B 到 G)
usei 發表於 2015-4-16 22:00


AN4=MAX(I4:N4)

AN5=IF(OR(MAX(E4,H4,I4:AA4)=AN4, LARGE((E4, H4, I4:AA4),2 )=AN4, LARGE((E4, H4, I4:AA4),3 )=AN4)=1, AN4, MIN(MAX(E4,H4,I4:AA4), LARGE((E4, H4, I4:AA4),2 ), LARGE((E4, H4, I4:AA4),3 )))

AN6=MAX(E4,H4,I4:AA4)+LARGE((E4, H4, I4:AA4),2 )+LARGE((E4, H4, I4:AA4),3 )+AN4-AO4

係咪咁樣?
但好似計多咗2分...
係咪我打錯咗...
THX CHING

我原先自己的FORMULA:
F4+G4+MAX(I4:O4)+MAX(E4,H4,IFERROR(LARGE((I4:O4),{2,3,4,5,6,7}),0), P4:AA4)+LARGE((E4,H4,IFERROR(LARGE((I4:O4),{2,3,4,5,6,7}),0), P4:AA4), 2)
紅字部分EXCEL話ERROR冇得用兩個LARGE喺一條FORMULA度...

TOP

本帖最後由 usei 於 2015-4-17 14:33 編輯

回覆 5# sfij

AN6=MAX(E4,H4,I4:AA4)+LARGE((E4, H4, I4:AA4),2 )+LARGE((E4, H4, I4:AA4),3 )+AN4-AO4

應該是 AN5

AN5 可以精簡少少
AN5=IF(OR(MAX(E4,H4,I4:AA4)=AN4, LARGE((E4, H4, I4:AA4),2 )=AN4, LARGE((E4, H4, I4:AA4),3 )=AN4)=1, AN4, MIN(MAX(E4,H4,I4:AA4), LARGE((E4, H4, I4:AA4),2 ), LARGE((E4, H4, I4:AA4),3 )))

MAX(E4,H4,I4:AA4) 一定不會是 min

TOP

回覆  sfij

AN6=MAX(E4,H4,I4:AA4)+LARGE((E4, H4, I4:AA4),2 )+LARGE((E4, H4, I4:AA4),3 )+AN4-AO4

應 ...
usei 發表於 2015-4-17 14:29


唔好意思...打錯...
AN4=MAX(I4:N4)

AO5=IF(OR(MAX(E4,H4,I4:AA4)=AN4, LARGE((E4, H4, I4:AA4),2 )=AN4, LARGE((E4, H4, I4:AA4),3 )=AN4)=1, AN4, MIN(MAX(E4,H4,I4:AA4), LARGE((E4, H4, I4:AA4),2 ), LARGE((E4, H4, I4:AA4),3 )))

AO6=MAX(E4,H4,I4:AA4)+LARGE((E4, H4, I4:AA4),2 )+LARGE((E4, H4, I4:AA4),3 )+AN4-AO4

TOP

回覆 7# sfij

AO6=MAX(E4,H4,I4:AA4)+LARGE((E4, H4, I4:AA4),2 )+LARGE((E4, H4, I4:AA4),3 )+AN4-AO4

應該是 AO5

TOP

...如何可以寫一個vba只highlight每一行的某一個range的maximum (不要highlight到duplicate 的maximum)?
thx

TOP

本帖最後由 happy_gemini 於 2015-4-21 15:25 編輯

這兩天想了一下, 樓主看看以下是否做到你所想要的結果.
MarkingScheme.jpg
2015-4-21 15:19

簡述:
1. 先重新排列ENG, MATH及的CHI位置. 因ENG及MATH必需計算, 而CHI須與其他科目作高低分排序.
2. 先從B至G找出最好的一科, 把這科label為"BG_1". (注意: 若最高分多於一科, 會以先出現的一科為"BG_1", 餘下科目會留下跟其他科目再對比排序).
3. 把CHI及B至T(樓主例子中只顯示到T, 不過科目再增加也不是問題, 以下計算方法不變)按高低分排序, 沒有分數的科目不排(留空). 各科目依次label為ALL_RANK1, ALL_RANK2,... (各RANK可以同時多於一科)
4. 計總分, 可分為兩種情況:
情況1: 只有一個最商分 - 把ALL_RANK1科目的分數加一個ALL_RANK2科目的分數 (把所有ALL_RANK2科目分數加起來除以有關科目數量).
情況2: 有多於一個最高分(ALL_RANK1) - 把所有ALL_RANK1科目分數加起來除以有關科目數量, 再乘2.

Formula:
C3:C23儲存格:
=IF(B3="","",IF(ROW(B3)=MATCH(MAX($B$5:$B$10),$B$5:$B$10,0)+4,"BG_1","ALL_RANK"&RANK(B3,$B$4:$B$23,0)))
總分B24儲存格:
=B1+B2+LOOKUP("BG_1",$C$5:$C$10,$B$5:$B$10)+IF(COUNTIF(C3:C23,"ALL_RANK1")>1,SUMIFS(B3:B23,C3:C23,"ALL_RANK1")/COUNTIF(C3:C23,"ALL_RANK1")*2,SUMIFS(B3:B23,C3:C23,"ALL_RANK1")+SUMIFS(B3:B23,C3:C23,"ALL_RANK2")/COUNTIF(C3:C23,"ALL_RANK2"))

TOP