辽宁11选5开奖结果走势图

電腦入門到精通網-專業的電腦知識和電腦技巧學習網站

用條件格式高亮顯示最高分和最低分

時間:2017-10-30 10:14來源:未知 作者:www.egbtt.icu 點擊:
在競賽活動中如果有多個評委,通常會采用去掉一個最高分和一個最低分,取其余評委打分的平均值為選手的最終得分,在采用Excel表格進行現場評分的情況下,為了方便查看,希望能

 在競賽活動中如果有多個評委,通常會采用去掉一個最高分和一個最低分,取其余評委打分的平均值為選手的最終得分,在采用Excel表格進行現場評分的情況下,為了方便查看,希望能夠用條件格式高亮顯示選手的最高得分和最低得分。下面以下圖的評分表為例,說明Excel 2016中用條件格式高亮顯示一個最高分和一個最低分的方法。 (電腦入門到精通網 www.egbtt.icu)

  圖一、示例數據

(電腦入門到精通網 www.egbtt.icu)

  本例有七個評委,評委打出的分數會被輸入到C4:I23區域,選擇這個區域,單擊“開始→條件格式→新建規則”,在彈出的對話框中選擇“使用公式確定要設置格式的單元格”,在文本框中輸入公式:

(電腦入門到精通網 www.egbtt.icu)

  =AND(COUNTIF($C4:C4,C4)=1,C4=MAX($C4:$I4))

(電腦入門到精通網 www.egbtt.icu)

  然后單擊“格式”按鈕,設置所需的格式后單擊“確定”關閉對話框。

(電腦入門到精通網 www.egbtt.icu)

 

(電腦入門到精通網 www.egbtt.icu)

  說明:公式的原理是選出從左到右第一次出現的最高分,也就是說要高亮顯示的單元格必須具備兩個條件,一是從左到右第一次出現,這個條件用COUNTF函數來實現;二是必須為最高分,這個條件用MAX函數實現。在有多個并列的最高分時,該公式可以避免所有最高分都被高亮顯示。

(電腦入門到精通網 www.egbtt.icu)

  重復上述步驟再次新建一個條件格式規則,為最低分設置公式:

(電腦入門到精通網 www.egbtt.icu)

  =AND(COUNTIF($C4:C4,C4)=1,C4=MIN($C4:$I4))

(電腦入門到精通網 www.egbtt.icu)

  這樣就可以讓每位選手的最高分和最低分高亮顯示了,是不是很醒目呢? (電腦入門到精通網 www.egbtt.icu)

  要去掉的最高分和最低分被高亮顯示出來 (電腦入門到精通網 www.egbtt.icu)

  如果評委更多,需要去掉兩個最高分和兩個最低分,要高亮顯示這些被去掉的得分,該怎樣設置條件格式的公式呢? (電腦入門到精通網 www.egbtt.icu)

  假如有16個評委,評委的打分輸入到C4:R23區域中,高亮顯示兩個最高分的公式可設置為:

(電腦入門到精通網 www.egbtt.icu)

  =OR(AND(COUNTIF($C4:C4,C4)<3,C4=MAX($C4:$R4)),AND(COUNTIF($C4:C4,C4)=1,C4=LARGE($C4:$R4,2))) (電腦入門到精通網 www.egbtt.icu)

  (電腦入門到精通網 www.egbtt.icu)

  說明:OR函數的兩個參數分別對應最高分和次高分,OR函數的第一個參數“AND(COUNTIF($C4:C4,C4)<3,C4=MAX($C4:$R4))”為TRUE時選出唯一的最高分或從左到右第1次和第2次出現的最高分;當最高分唯一時,OR函數的第二個參數“AND(COUNTIF($C4:C4,C4)=1,C4=LARGE($C4:$R4,2))”為TRUE則選出從左到右第1次出現的次高分。 (電腦入門到精通網 www.egbtt.icu)

 

(電腦入門到精通網 www.egbtt.icu)

  高亮顯示兩個最低分的公式可設置為: (電腦入門到精通網 www.egbtt.icu)

  =OR(AND(COUNTIF($C4:C4,C4)<3,C4=MIN($C4:$R4)),AND(COUNTIF($C4:C4,C4)=1,C4=SMALL($C4:$R4,2))) (電腦入門到精通網 www.egbtt.icu)

  最后說明一下去掉一個最高分和一個最低分后計算平均值的公式,上圖中J4單元格中的公式為: (電腦入門到精通網 www.egbtt.icu)

  =IFERROR(TRIMMEAN(C4:I4,0.3),"") (電腦入門到精通網 www.egbtt.icu)

  (電腦入門到精通網 www.egbtt.icu)

  所用到的函數TRIMMEAN,其第二個參數為計算平均值所要排除數量占總數的百分比,如本例選手有7個得分,要去掉一個最高分和一個最低分即2個數值,該參數取值0.3(30%),因為7×0.3=2.1,向下舍入到2的倍數為2,即對稱地去掉一個最高分和一個最低分。 (電腦入門到精通網 www.egbtt.icu)

 

(電腦入門到精通網 www.egbtt.icu)

  當選手有16個得分,要去掉兩個最高分和兩個最低分即4個數值時,該參數取值0.3(30%),因為16×0.3=4.8,向下舍入到2的倍數為4,即對稱地去掉兩個最高分和兩個最低分。由于會自動向下舍入到2的倍數,實際上對于16去4取平均值的情況,該參數取值0.25-0.37都是可以的。

(電腦入門到精通網 www.egbtt.icu)

(責任編輯:電腦入門到精通網 www.egbtt.icu)
頂一下
(0)
0%
踩一下
(2)
100%
------分隔線----------------------------
辽宁11选5开奖结果走势图