一、单条件COUNTA函数,I列有数值就开始自动编码,空白值直接跳过,公式如下: =IF(I2='','',COUNTA($I$2:I2)) 二、多条件COUNTIF函数,适用于出纳收支编码,I列只有收付两个文本条件为例 1.纯数字自动编号:收时,以1000四位编码进行自动编号;付时,以2000四位编码进行自行编号,公式如下: =IF(I2='收',1000+COUNTIF(I$2:I2,I2),IF(I2='付',2000+COUNTIF(I$2:I2,I2))) 2.带文本自动编号:收时,以收-0(如收-01)编码进行自动编号;付时,以支-0(如支-01)编码进行自行编号,公式如下: =IF(I2='收','收'&'-'&(COUNTIF(I$2:I2,I2)),IF(I2='付','支'&'-'&(COUNTIF(I$2:I2,I2)))) 备用: 1.=C2&'-L-BBU'&CEILING(SUMPRODUCT(($C$2:C2=C2)*($B$2:B2))/6,1) 2.='AZMTRQGGyqz'&B2&'-'&RIGHT(YEAR(G2),2)&TEXT(MONTH(G2),'00')&'-'&TEXT(SUMPRODUCT((TEXT(G$2:G2,'ymm')=TEXT(G2,'ymm'))*(B$2:B2=B2)),'0000') 3.=IF(D2='优秀',1000+COUNTIF(D$2:D2,D2),IF(D2='良好',2000+COUNTIF(D$2:D2,D2),IF(D2='一般',3000+COUNTIF(D$2:D2,D2),''))) 4.=SUMPRODUCT(--(B$2:B2&C$2:C2=B2&C2)) 5.=IF(F2='','',IF(F2=F1,F2&'-'&COUNTIF($F$1:F2,F2)-1,F2)) |
|