office excel最常用函數公式技巧搜集大全(13.12.09更新)4

  office excel最常用函數公式技巧搜集大全(13.12.09更新)

導讀:能否用一個公式直接找出所用考生中語文成績中按與考人數的35%切線中位於第35%的,倒數排名函數是什麼,自動產生序列號:在A1輸入以下公式,用以下公式:,一般表格會自動產生以上匯總公式),點「fx」(用「sum」函數)再點擊序列號最末尾數,公式=MOD(A1,2)=1,[DBNUM1]G/通用格式名”)排序後排名{=SUM(IF($B$2:$B$15>=B2,1/COUNTIF(

office excel最常用函數公式技巧搜集大全(13.12.09更新)4

[DBNUM1]G/通用格式名”)

排序後排名

{=SUM(IF($B$2:$B$15>=B2,1/COUNTIF($B$2:$B$15,$B$2:$B$15)))}

=SUMPRODUCT((B$2:B$15>=B2)/COUNTIF(B$2:B$15,B$2:B$15))

位次排名

{=IF($B2:$O2>=0,RANK($B2:$O2,$B2:$O2,0),)}

根據雙列成績進行共同排名

=RANK(C345,($C$345:$C$356,$H$345:$H$356))

在雙列間排名

=RANK(B2,($B$2:$B$26,$E$2:$E$16))

等次排名

由大到小排名

=RANK(B3,$B$3:$B$12)

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16))+1

由小到大排名

=RANK(B3,$B$3:$B$12,1)

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16))+1

不等次排名(行小排先)

由大到小

=RANK(B3,$B$3:$B$12)+COUNTIF($B$3:B3,B3)-1

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000>B16-ROW(B16)/10000))+1

由小到大

=RANK(B3,$B$3:$B$12,1)+COUNTIF($B$3:B3,B3)-1

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000<B16+ROW(B16)/10000))+1

不等次排名(行大排先)

由大到小

=COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12,1)-COUNTIF($B$3:B3,B3)+2

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000>B16+ROW(B16)/10000))+1

由小到大

=COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12)-COUNTIF($B$3:B3,B3)+2

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000<B16-ROW(B16)/10000))+1

順次排名

由大到小

=SUMPRODUCT((B$3:B$12>B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16)/COUNTIF($K$16:$K$25,$K$16:$K$25))+1

由小到大

=SUMPRODUCT((B$3:B$12<B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16)/COUNTIF($K$16:$K$25,$K$16:$K$25))+1

有並列排名

=RANK(B2,$B$2:$B$20)

=SUMPRODUCT(1*($B$3:$B$21>B3))+1

=COUNTIF($B$3:$B$21,”>”&B3)+1

{=SUM(IF($B$3:$B$21>B3,1,0))+1}

=19-FREQUENCY($B$3:$B$21,B3)+1

=SUMPRODUCT(($B$2:$B$20>=B2)/COUNTIF($B$2:$B$20,$B$2:$B$20))

無並列排名

=RANK(B3,$B$3:$B$21)+COUNTIF($B$3:$B3,B3)-1

=SUMPRODUCT((B3-ROW()/1000<$B$3:$B$21-ROW($B$3:$B$21)/1000)*1)+1

=19-FREQUENCY($B$3:$B$21-ROW($B$3:$B$21)/1000,B3-ROW()/1000)+1

{=SUM(IF($B$3:$B$21-ROW($B$3:$B$21)/1000>B3-ROW()/1000,1,0))+1}

有並列分段排名

=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3))+1

=19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21),C3)+1

{=MATCH(C3,LARGE(OFFSET($C$2,IF($A$3:$A$21=A3,ROW($A$3:$A$21)-2),),ROW(INDIRECT(“1:”&COUNTIF($A$3:$A$21,A3)))),0)}

{=MATCH(C3,LARGE(IF($A$3:$A$21=A3,$C$3:$C$21),ROW(INDIRECT(“1:”&COUNTIF($A$3:$A$21,A3)))),0)}

{=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3)/COUNTIF($N$3:$N$21,$N$3:$N$

21))+1}(需輔助列)

無並列分段排名

{=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/10000>C3-ROW(C

3)/10000))+1}

=19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/1000),C3-ROW()/1000)+1

成績排名

100名的成績是多少?

=LARGE(C2:C417,100)

=PERCENTILE(C2:C417,(416-100)/416)

=PERCENTILE($C$2:$C$417,(COUNTA($C$2:$C$417)-100)/COUNTA($C$2:$C$417))

能否用一個公式直接找出所用考生中語文成績中按與考人數的35%切線中位於第35%的成績是多少?

升冪

=SMALL(C2:C417,416*0.35)

=PERCENTILE($C$2:$C$417,0.35)

降冪

=LARGE(C2:C417,416*0.35)

=PERCENTILE($C$2:$C$417,1-0.35)

如何排名

1、對英語進行排名,缺考不計算在內。

b2=IF(A2=”缺考”,””,RANK(A2,$A$2:$A$13)) 然後按照B列排序

缺考計算在內

=IF(A2=”缺考”,COUNTIF($A$2:$A$13,”>=0″)+1,RANK(A2,$A$2:$A$13))

=IF(A2=”缺考”,COUNT($A$2:$A$13)+1,RANK(A2,$A$2:$A$13,0))

數據排名(隔幾行排名)

=IF(A2=””,””,RANK(A2,$A$2:$A$11,0))

如果隔幾行排名,如下表,第五行、第九行和第十二行不參與排名。

單位 數據 排名

A 1 8

A 5 7

A 6 6

小計 12

B 8 4

B 9 3

B 7 5

小計 24

C 18 1

C 11 2

小計 29

=IF(A2=”小計”,””,RANK(B2,(B$2:B$4,B$6:B$8,B$10:B$11))) 下拉

根據分數進行倒排名

=RANK($E3,$E$3:$E$22,1)

=RANK(K60,$K$60:$K$83,1)

=COUNTIF($K$60:$K$83,”<“&K60)+1

倒數排名函數是什麼

1為正排序,0為逆排序。

倒數排名=RANK(A2,$A$2:$A$5,0)

正數排名=RANK(A2,$A$2:$A$5,1)

如何做到每日各車間產量的排名

=RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$1000,0)-1,,,))

=RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$33,0)-1,,,))

分數相同時按照一科的分數進行排名

{=MATCH(K308*100+D308,LARGE($K$308:$K$331*100+$D$308:$D$331,ROW($K$308:$K$331)-307),)}

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 篩選後自動產生序列號並匯總

自動產生序列號:在A1輸入以下公式,往下拖。

=SUBTOTAL(3,$B$2:B2)*1

自動匯總,用以下公式:

=SUBTOTAL(9,$B$2:B2)

說明:匯總時,不要在「全選」狀態下進行,先「篩選」出某一單位,自動求和∑。然後再恢復到「全選」或者選擇任何單位,就能自動匯總了(在「篩選」出某一單位進行求和時,一般表格會自動產生以上匯總公式)。

其它:如同時要在其它單元格顯示人數,在「全選」狀態下,選定單元格,點「fx」(用「sum」函數)再點擊序列號最末尾數,即可。

如何篩選奇數行

公式=MOD(A1,2)=1