• <form id="oquu4"><legend id="oquu4"></legend></form>
      <wbr id="oquu4"></wbr><sub id="oquu4"><listing id="oquu4"><small id="oquu4"></small></listing></sub>

      1. <form id="oquu4"><legend id="oquu4"></legend></form>
        <nav id="oquu4"></nav>

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

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

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

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

          圖一、示例數據

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

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

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

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

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

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

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

         

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

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

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

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

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

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

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

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

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

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

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

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

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

          (電腦入門到精通網 www.melindablogs.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.melindablogs.com)

         

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

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

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

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

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

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

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

         

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

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

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

        (責任編輯:電腦入門到精通網 www.melindablogs.com)
        頂一下
        (0)
        0%
        踩一下
        (2)
        100%
        ------分隔線----------------------------
        美女脱光衣服