excel中按某組數(shù)據(jù)按大小進(jìn)行自動(dòng)排序排名RANK、COUNTIF、LOOKUP、SUMPRODUCT

excel中按某組數(shù)據(jù)按大小進(jìn)行自動(dòng)排序排名(RANKCOUNTIFLOOKUPSUMPRODUCT(2011-03-1616:00:46)如開心網(wǎng)等游戲中效益自動(dòng)排名或者按考試分?jǐn)?shù)高低進(jìn)行自動(dòng)排名等方法一：RANK這個(gè)方法最簡(jiǎn)單，直接使用rank函數(shù)，如有第五名和第六名相等時(shí)，有兩個(gè)第五名，則下一個(gè)顯示第七名(沒有第六名)。在欲顯示排名的列輸入=RANK(D25,$D$25:$D$36)則按降序排歹U,=RANK(D25,$D$25:$D$36,1)則按升序排列。方法二：COUNTIF方法簡(jiǎn)單，直接使用countif函數(shù)：1、在欲顯示排名的列輸入=COUNTIF($N$25:$N$36,">="&N25)則按降序排歹U,=COUNTIF($N$25:$N$36,"<="&N25)則按升序排列。如有第五名和第六名相等時(shí)，有兩個(gè)第六名(沒有第五名)，則下一個(gè)顯示第七名。2、在欲顯示排名的列輸入=COUNTIF($N$25:$N$36,">"&N25)+1則按降序排列，同上，改用小于號(hào)〈即可。如有第五名和第六名相等時(shí)，有兩個(gè)第五名(沒有第六名)，則下一個(gè)顯示第七名。方法三：LOOKUP這個(gè)比較笨的方法，但也好理解，可以使用于顯示排名名次且不局限于數(shù)字形式，如可顯示冠軍、亞軍、第三名等等，如果有第五名和第六名相等時(shí)，可以有兩個(gè)第五名可人工來定下一個(gè)是6還是7,主要看怎么定義，最大優(yōu)點(diǎn)在于顯示結(jié)果靈活，分3步來：1、列F(隨便找一個(gè)臨時(shí)列，如果看著不爽干脆做好后直接隱藏)中輸入=SMALL($D$1:$D$20,ROW(A1)),把D1:D20中的數(shù)據(jù)從小到大排序，其中的絕對(duì)引用是為了使用填充柄方便操作，把排序的范圍定在D1:D20,ROW(A1)取A1的行號(hào)1(可以取任列意行號(hào)，主要偶看A列開心順意用A,如果用該函數(shù)從大到小排序則為把ROW(A1)替換成count($D$1:$D$20)-ROW(A1)+1即可)。2、在旁邊找一空列列H,按大小進(jìn)行排序，這個(gè)就是我們希望排序排名是顯示的(如果用數(shù)字1、2、3、4顯示排名則可以用省略這一步，直接引用行號(hào))。這個(gè)也可用IF來排名：H1中輸入1,然后在H2中輸入=IF(F2=F1,H1,H1+1),如有第五名和第六名相等時(shí)，有兩個(gè)第五名，則下一個(gè)依然顯示第六名，導(dǎo)致最后結(jié)果少一個(gè)。3、在欲顯示排名的列輸入=LOOKUP(D1,$F$1:$F$20,$H$1:$H$20),LOOKUP為查找函數(shù)，該語(yǔ)句作用為在$F$1:$F$20中查找到與D1相等的值，并返回$H$1:$H$20中相應(yīng)行的值，注：$H$1:$H$20的類型不限制，但肝$1:$F$20這一段數(shù)組只支持從小到大遞增排序，否則會(huì)出錯(cuò)。方法四：SUMPRODUCT這個(gè)方法比較簡(jiǎn)單，只要一個(gè)公式即可，但偶不明白他是怎么計(jì)算的，缺點(diǎn)是排名顯示叫單一，如有第五名和第六名相等時(shí)，有兩個(gè)第五名，則下一個(gè)依然顯示第六名，導(dǎo)致最后結(jié)果少一個(gè)，具體：在欲顯示排名的列輸入=SUMPRODUCT(($D$1:$D$20>=$D1)/COUNTIF($D$1:$D$20,$D$1:$D$20)),其中SUMPRODUCT(array1,array2,array3,…)在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和，COUNTIF(range,criteria)計(jì)算區(qū)域range中滿足給定條件criteria的單元格的個(gè)數(shù)。應(yīng)該是計(jì)算滿足大于或等于D1個(gè)數(shù)，如果按數(shù)彳1最低為第1名即升序排列，則($D$1:$D$20>=$D1)替換為($D$1:$D$20<=$D1)即可，比較方便，但是好像只能顯示純數(shù)值的排名。附以上幾個(gè)函數(shù)的幫助信息參考：一、SUMPRODUCT(array1,array2,array3,...)在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。Array1,array2,array3,...為2至U30個(gè)數(shù)組，其相應(yīng)元素需要進(jìn)行相乘并求和。說明數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)SUMPRODUCT將返回錯(cuò)誤值#VALUE!。函數(shù)SUMPRODUCT將非數(shù)值型的數(shù)組元素作為0處理。二、COUNT(value1,value2,…)和COUNTIF(range,criteria)1、COUNT(value1,value2,...)返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個(gè)數(shù)。利用函數(shù)COUNT可以計(jì)算單元格區(qū)域或數(shù)字?jǐn)?shù)組中數(shù)字字段的輸入項(xiàng)個(gè)數(shù)。Value1,value2,…為包含或引用各種類型數(shù)據(jù)的參數(shù)(1至U30個(gè))，但只有數(shù)字類型的數(shù)據(jù)才被計(jì)算。說明函數(shù)COUNT在計(jì)數(shù)時(shí)，將把數(shù)字、日期、或以文本代表的數(shù)字計(jì)算在內(nèi)；但是錯(cuò)誤值或其他無法轉(zhuǎn)換成數(shù)字的文字將被忽略。如果參數(shù)是一個(gè)數(shù)組或引用，那么只統(tǒng)計(jì)數(shù)組或引用中的數(shù)字；數(shù)組或引用中的空白單元格、邏輯值、文字或錯(cuò)誤值都將被忽略。如果要統(tǒng)計(jì)邏輯值、文字或錯(cuò)誤值，請(qǐng)使用函數(shù)COUNTA。2、COUNTIF(range,criteria)計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式、單元格引用或文本。其中數(shù)字可以直接寫入，表達(dá)式和文本必須加引號(hào)。例如，條件可以表示為32、"32"、">32"、"apples"或B4。說明MicrosoftExcel還提供了其他一些函數(shù)，它們可根據(jù)條件來分析數(shù)據(jù)。若要計(jì)算基于一個(gè)文本字符串或某范圍內(nèi)的一個(gè)數(shù)值的總和，可使用SUMIF工作表函數(shù)。若要使公式返回兩個(gè)基于條件的值之一，例如某指定銷售金額的銷售紅利，可使用IF工作表函數(shù)。若要計(jì)算空或非空單元格的個(gè)數(shù)，可使用COUNTA和COUNTBLANK函數(shù)。三、LOOKUP從單行或單列區(qū)域或者從一個(gè)數(shù)組(數(shù)組：用于建立可生成多個(gè)結(jié)果或可對(duì)在行和列中排列的一組參數(shù)進(jìn)行運(yùn)算的單個(gè)公式。數(shù)組區(qū)域共用一個(gè)公式；數(shù)組常量是用作參數(shù)的一組常量。)返回值。LOOKUP函數(shù)具有兩種語(yǔ)法形式：1、向量LOOKUP的向量形式在單行區(qū)域或單列區(qū)域(稱為向量”)中查找值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的值。當(dāng)要查詢的值列表較大或者值可能會(huì)隨時(shí)間而改變時(shí)，使用該向量形式。向量是只含一行或一列的區(qū)域。LOOKUP的向量形式在單行區(qū)域或單列區(qū)域(稱為向量”)中查找值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的值。當(dāng)要指定的區(qū)域包含要匹配的值時(shí)，請(qǐng)使用LOOKUP函數(shù)的這種形式。LOOKUP的另一種形式自動(dòng)在第一行或第一列中查找。LOOKUP(lookup_value,lookup_vector,result_vector)Lookup_valueLOOKUP在第一個(gè)向量中搜索的值。Lookup_value可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊?。Lookup_vector只包含一行或一列的區(qū)域。lookup_vector中的值可以是文本、數(shù)字或邏輯值。要點(diǎn)lookup_vector中的值必須以升序順序放置：...,-2,-1,0,1,2,...;A-Z;FALSE,TRUE。否則，LOOKUP可能無法提供正確的值。大寫文本和小寫文本是等同的。Result_vector只包含一行或一列的區(qū)域。它必須與lookup_vector大小相同。評(píng)論如果LOOKUP找不到lookup_value,則它與lookup_vector中小于或等于lookup_value的最大值匹配。如果lookup_value小于lookup_vector中的最小值，則LOOKUP會(huì)提供#N/A錯(cuò)誤值。如開心網(wǎng)等游戲中效益自動(dòng)排名或者按考試分?jǐn)?shù)高低進(jìn)行自動(dòng)排名等方法一：RANK這個(gè)方法最簡(jiǎn)單，直接使用rank函數(shù)，如有第五名和第六名相等時(shí)，有兩個(gè)第五名，則下一個(gè)顯示第七名(沒有第六名)。在欲顯示排名的列輸入=RANK(D25,$D$25:$D$36)則按降序排歹U,=RANK(D25,$D$25:$D$36,1)則按升序排列。方法二：COUNTIF方法簡(jiǎn)單，直接使用countif函數(shù)：1、在欲顯示排名的列輸入=COUNTIF($N$25:$N$36,">="&N25)則按降序排歹U,=COUNTIF($N$25:$N$36,"<="&N25)則按升序排列。如有第五名和第六名相等時(shí)，有兩個(gè)第六名(沒有第五名)，則下一個(gè)顯示第七名。2、在欲顯示排名的列輸入=COUNTIF($N$25:$N$36,">"&N25)+1則按降序排列，同上，改用小于號(hào)〈即可。如有第五名和第六名相等時(shí)，有兩個(gè)第五名(沒有第六名)，則下一個(gè)顯示第七名。方法三：LOOKUP這個(gè)比較笨的方法，但也好理解，可以使用于顯示排名名次且不局限于數(shù)字形式，如可顯示冠軍、亞軍、第三名等等，如果有第五名和第六名相等時(shí)，可以有兩個(gè)第五名可人工來定下一個(gè)是6還是7,主要看怎么定義，最大優(yōu)點(diǎn)在于顯示結(jié)果靈活，分3步來：1、列F(隨便找一個(gè)臨時(shí)列，如果看著不爽干脆做好后直接隱藏)中輸入=SMALL($D$1:$D$20,ROW(A1)),把D1:D20中的數(shù)據(jù)從小到大排序，其中的絕對(duì)引用是為了使用填充柄方便操作，把排序的范圍定在D1:D20,ROW(A1)取A1的行號(hào)1(可以取任列意行號(hào)，主要偶看A列開心順意用A,如果用該函數(shù)從大到小排序則為把ROW(A1)替換成count($D$1:$D$20)-ROW(A1)+1即可)。2、在旁邊找一空列列H,按大小進(jìn)行排序，這個(gè)就是我們希望排序排名是顯示的(如果用數(shù)字1、2、3、4顯示排名則可以用省略這一步，直接引用行號(hào))。這個(gè)也可用IF來排名：H1中輸入1,然后在H2中輸入=IF(F2=F1,H1,H1+1),如有第五名和第六名相等時(shí)，有兩個(gè)第五名，則下一個(gè)依然顯示第六名，導(dǎo)致最后結(jié)果少一個(gè)。3、在欲顯示排名的列輸入=LOOKUP(D1,$F$1:$F$20,$H$1:$H$20),LOOKUP為查找函數(shù)，該語(yǔ)句作用為在$F$1:$F$20中查找到與D1相等的值，并返回$H$1:$H$20中相應(yīng)行的值，注：$H$1:$H$20的類型不限制，但肝$1:$F$20這一段數(shù)組只支持從小到大遞增排序，否則會(huì)出錯(cuò)。方法四：SUMPRODUCT這個(gè)方法比較簡(jiǎn)單，只要一個(gè)公式即可，但偶不明白他是怎么計(jì)算的，缺點(diǎn)是排名顯示叫單一，如有第五名和第六名相等時(shí)，有兩個(gè)第五名，則下一個(gè)依然顯示第六名，導(dǎo)致最后結(jié)果少一個(gè)，具體：在欲顯示排名的列輸入=SUMPRODUCT(($D$1:$D$20>=$D1)/COUNTIF($D$1:$D$20,$D$1:$D$20)),其中SUMPRODUCT(array1,array2,array3,...)在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和，COUNTIF(range,criteria)計(jì)算區(qū)域range中滿足給定條件criteria的單元格的個(gè)數(shù)。應(yīng)該是計(jì)算滿足大于或等于D1個(gè)數(shù)，如果按數(shù)彳1最低為第1名即升序排列，則($D$1:$D$20>=$D1)替換為($D$1:$D$20<=$D1)即可，比較方便，但是好像只能顯示純數(shù)值的排名。附以上幾個(gè)函數(shù)的幫助信息參考：一、SUMPRODUCT(array1,array2,array3,...)在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。Array1,array2,array3,...為2至U30個(gè)數(shù)組，其相應(yīng)元素需要進(jìn)行相乘并求和。說明數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)SUMPRODUCT將返回錯(cuò)誤值#VALUE!。函數(shù)SUMPRODUCT將非數(shù)值型的數(shù)組元素作為0處理。二、COUNT(value1,value2,...)和COUNTIF(range,criteria)1、COUNT(value1,value2,...)返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個(gè)數(shù)。利用函數(shù)COUNT可以計(jì)算單元格區(qū)域或數(shù)字?jǐn)?shù)組中數(shù)字字段的輸入項(xiàng)個(gè)數(shù)。Value1,value2,...為包含或引用各種類型數(shù)據(jù)的參數(shù)(1至U30個(gè))，但只有數(shù)字類型的數(shù)據(jù)才被計(jì)算。說明函數(shù)COUNT在計(jì)數(shù)時(shí)，將把數(shù)字、日期、或以文本代表的數(shù)字計(jì)算在內(nèi)；但是錯(cuò)誤值或其他無法轉(zhuǎn)換成數(shù)字的文字將被忽略。如果參數(shù)是一個(gè)數(shù)組或引用，那么只統(tǒng)計(jì)數(shù)組或引用中的數(shù)字；數(shù)組或引用中的空白單元格、邏輯值、文字或錯(cuò)誤值都將被忽略。如果要統(tǒng)計(jì)邏輯值、文字或錯(cuò)誤值，請(qǐng)使用函數(shù)COUNTA。2、COUNTIF(range,criteria)計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式、單元格引用或文本。其中數(shù)字可以直接寫入，表達(dá)式和文本必須加引號(hào)。例如，條件可以表示為32、"32"、">32"、"apples"或B4。說明MicrosoftExcel還提供了其他一些函數(shù)，它們可根據(jù)條件來分析數(shù)據(jù)。若要計(jì)算基于一個(gè)文本字符串或某范圍內(nèi)的一個(gè)數(shù)值的總和，可使用SUMIF工作表函數(shù)。若要使公式返回兩個(gè)基于條件的值之一，例如某指定銷售金額的銷售紅利，可使用IF工作表函數(shù)。若要計(jì)算空或非空單元格的個(gè)數(shù)，可使用COUNTA和COUNTBLANK函數(shù)。三、LOOKUP從單行或單列區(qū)域或者從一個(gè)數(shù)組(數(shù)組：用于建立可生成多個(gè)結(jié)果或可對(duì)在行和列中排列的一組參數(shù)進(jìn)行運(yùn)算的單個(gè)公式。數(shù)組區(qū)域共用一個(gè)公式；數(shù)組常量是用作參數(shù)的一組常量。）返回值。LOOKUP函數(shù)具有兩種語(yǔ)法形式：1、向量LOOKUP的向量形式在單行區(qū)域或單列區(qū)域（稱為向量”）中查找值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的值。當(dāng)要查詢的值列表較大或者值可能會(huì)隨時(shí)間而改變時(shí)，使用該向量形式。向量是只含一行或一列的區(qū)域。LOOKUP的向量形式在單行區(qū)域或單列區(qū)域（稱為向量”）中查找值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的值。當(dāng)要指定的區(qū)域包含要匹配的值時(shí)，請(qǐng)使用LOOKUP函數(shù)的這種形式。LOOKUP的另一種形式自動(dòng)在第一行或第一列中查找。LOOKUP（lookup_value,lookup_vector,result_vector）Lookup_valueLOOKUP在第一個(gè)向量中搜索的值。Lookup_value可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊?。Lookup_vector只包含一行或一列的區(qū)域。lookup_vector中的值可以是文本、數(shù)字或邏輯值。要點(diǎn)lookup_vector中的值必須以升序順序放置：…,-2,-1,0,1,2,...;A-Z;FALSE,TRUE。否則，LOOKUP可能無法提供正確的值。大寫文本和小寫文本是等同的。Result_vector只包含一行或一列的區(qū)域。它必須與lookup_vector大小相同。評(píng)論如果LOOKUP找不到lookup_value,則它與lookup_vector中小于或等于lookup_value的最大值匹配。如果lookup_value小于lookup_vector中的最小值，則LOOKUP會(huì)提供#N/A錯(cuò)誤值。2、數(shù)組LOOKUP的數(shù)組形式在數(shù)組的第一行或第一列中查找指定的值，然后返回?cái)?shù)組的最后一行或最后一列中相同位置的值。當(dāng)要查詢的值列表較小或者值在一段時(shí)間內(nèi)保持不變時(shí)，使用該數(shù)組形式。LOOKUP的數(shù)組形式在數(shù)組（數(shù)組：用于建立可生成多個(gè)結(jié)果或可對(duì)在行和列中排列的一組參數(shù)進(jìn)行運(yùn)算的單個(gè)公式。數(shù)組區(qū)域共用一個(gè)公式；數(shù)組常量是用作參數(shù)的一組常量。）的第一行或第一列中查找指定的值，并返回?cái)?shù)組最后一行或最后一列內(nèi)同一位置的值。當(dāng)要匹配的值位于數(shù)組的第一行或第一列中時(shí)，使用LOOKUP的這種形式。當(dāng)要指定列或行的位置時(shí)，使用LOOKUP的另一種形式。提示一般而言，最好使用HLOOKUP或VLOOKUP函數(shù)而不是LOOKUP的數(shù)組形式。LOOKUP的這種形式是為了與其他電子表格程序兼容而提供的。LOOKUP（lookup_value,array）Lookup_valueLOOKUP在數(shù)組中搜索的值。Lookup_value可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊?。如果LOOKUP找不到lookup_value,它會(huì)使用數(shù)組中小于或等于lookup_value的最大值。如果lookup_value小于第一行或第一列中的最小值（取決于數(shù)組維度），LOOKUP會(huì)返回#N/A錯(cuò)誤值。數(shù)組包含要與lookup_value進(jìn)行比較的文本、數(shù)字或邏輯值的單元格區(qū)域。LOOKUP的數(shù)組形式與HLOOKUP和VLOOKUP函數(shù)非常相似。區(qū)別在于：HLOOKUP在第一行中搜索lookup_value,VLOOKUP在第一列中搜索，而LOOKUP根據(jù)數(shù)組維度進(jìn)行搜索。如果數(shù)組包含寬度比高度大的區(qū)域（列數(shù)多于行數(shù)），LOOKUP會(huì)在第一行中搜索lookup_value。如果數(shù)組是正方的或者高度大于寬度（行數(shù)多于列數(shù)），則LOOKUP在第一列中進(jìn)行搜索。使用HLOOKUP和VLOOKUP,可以通過索引以向下或遍歷的方式搜索，但是LOOKUP始終選擇行或列中的最后一個(gè)值。要點(diǎn)數(shù)組中的值必須以升序順序放置：...,-2,-1,0,1,2,...;A-Z;FALSE,TRUE。否則，LOOKUP無法提供正確的值。大寫文本和小寫文本是等同的。注釋對(duì)于詳細(xì)的測(cè)試或七種以上條件的測(cè)試，還可以使用LOOKUP函數(shù)作為IF函數(shù)的替代函數(shù)。請(qǐng)參閱數(shù)組形式的示例。四、SMALL(array,k)返回?cái)?shù)據(jù)集中第k個(gè)最小值。使用此函數(shù)可以返回?cái)?shù)據(jù)集中特定位置上的數(shù)值。Array為需