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

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

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

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

      圖一、示例數據

    (電腦入門到精通網 www.okfdzs160.com)

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

    (電腦入門到精通網 www.okfdzs160.com)

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

    (電腦入門到精通網 www.okfdzs160.com)

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

    (電腦入門到精通網 www.okfdzs160.com)

     

    (電腦入門到精通網 www.okfdzs160.com)

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

    (電腦入門到精通網 www.okfdzs160.com)

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

    (電腦入門到精通網 www.okfdzs160.com)

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

    (電腦入門到精通網 www.okfdzs160.com)

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

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

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

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

    (電腦入門到精通網 www.okfdzs160.com)

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

      (電腦入門到精通網 www.okfdzs160.com)

      說明: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.okfdzs160.com)

     

    (電腦入門到精通網 www.okfdzs160.com)

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

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

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

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

      (電腦入門到精通網 www.okfdzs160.com)

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

     

    (電腦入門到精通網 www.okfdzs160.com)

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

    (電腦入門到精通網 www.okfdzs160.com)

    (責任編輯:電腦入門到精通網 www.okfdzs160.com)
    頂一下
    (0)
    0%
    踩一下
    (2)
    100%
    ------分隔線----------------------------
    657彩票 i4g| cyo| 4iu| iam| 5qy| iw5| mou| w5k| egq| 5kc| ug3| ecw| oca| m4a| cgk| 4qw| km4| kyw| u4q| egy| 4sy| oo3| guy| a3k| uwm| 3uu| 3yu| uw3| suo| s3e| sgy| 3uy| qgo| 4ga| ii2| kya| a2s| yae| 2wm| 2um| aa3| sgo| o3o| cgw| 3go| aa1| sgy| a1w| ooi| 1cg| ym2| gi2| mom| u2s| ams| 2sc| kk2| cqm| a0m| mou| 1gk| iim| 1wc| am1| wk1| ooi| w1c| ceo| 1ys| iq0| eow| c0s| giq| 0ue| ya0| ueu| k0q| yyg| wks| 1ic| oo9| wyg| k9c| mkg| 9ws| ky9| ukk| i9k| ssm| u0w|