excel函数公式及技巧(3)

6 赵六 员工

如上的一个表,如何实现当我把赵六这一整行(第6行)插入到上面的表中时,A列的序列号不变?最后的效果如下:

1 赵一 总经理 2 赵二 副经理 3 赵六 员工 4 赵三 副经理 5 赵四 技术员 6 赵五

A1单元格输入公式 =row(),往下拉,然后再插入。 =SUBTOTAL(3,$B$2:$B2)

在A1中输入公式：“=if(b1="","",counta($b$1:b1)”后下拉复制至A列各行即可（“”不必输入）

根据规律的重复的姓名列产生自动序号 姓名 序号 张三 1

张三 1 李四 2 李四 2 赵五 3 赵五 3 赵五 3 王六 4 王六 4

=(A1<>A2)+N(B1) =IF(A3=A2,B2,B2+1)

姓名已排序：

B2=SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2))

姓名未排序：

B2=IF(COUNTIF(A$2:A2,A2)>1,VLOOKUP(A2,A:B,2,0),SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)))

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～ 排名的函数

用排名函数来对成绩进行排名，用起来非常地方便。 =IF(ISERR(RANK(M3,M:M)),"",RANK(M3,M:M))

A列是成绩,B列是排名

=SUMPRODUCT((A$1:A$9>A1)/COUNTIF(A$1:A$9,A$1:A$9))+1

自动排名公式

=RANK(C3,$C$3:$C$12) =RANK(A2,$A$2:$A$11,0)

=RANK(C2,$C$2:$C$65)+COUNTIF($C$2:C2,C2)-1 百分比排名的公式写法为： =PERCENTRANK($C$3:$C$12,C3) 平均分及总分排名 =AVERAGE(B2:E2) =RANK(F2,$F$2:$F$65536) 求名次排名

统计成绩时遇到一个分别求班级和年级总分名次排名的问题，不晓得应该运用什么公式来实现。

班级名次：

=SUMPRODUCT((BJ=A2)*(ZF>E2))+1

年级名次：

=RANK(E2,ZF) 公式下拖。 排名次

根据总分值大小，只将姓名排序后, 降序结果 =INDEX(A$2:A$6,RANK(D2,D$2:D$6))

根据总分值大小，只将姓名排序后, 升序 =INDEX(A$2:A$6,RANK(D2,D$2:D$6,1)) 根据分数进行普通排名