编号如何跨年后从1开始编

DIYer

现在写的公式，跨年后需要修改一下单元格范围，如何不用修改用统一公式解决

丢丢表格

=IF(OR(C3:E3=""),"",TEXT(C3,"eL!G")&TEXT(COUNTIFS(D$3:D3,D3,E$3:E3,E3)*10001,"0000"&IF(E3=E3,"!Y","X")&"0000"))

DIYer

丢丢表格 发表于 2018-3-2 22:10
=IF(OR(C3:E3=""),"",TEXT(C3,"eL!G")&TEXT(COUNTIFS(D$3:D3,D3,E$3:E3,E3)*10001,"0000"&IF(E3=E3,"!Y","X ...

是不是要用花括号，后面再加个新年份就不对了

DIYer

OR(C3:E3="")这个公式在我这里显示错误，我修改成了C3="",d3="",e3=""之后不用花括号就能用，但是加新年份之后还是错的

丢丢表格

=IF(OR(C3="",D3="",E3=""),"",TEXT(C3,"eL!G")&TEXT(COUNTIFS(C$3:C3,"<="&C3,C$3:C3,">="&YEAR(C3)&"-1-1",E$3:E3,E3)*10001,"0000"&IF(E3=E3,"!Y","X")&"0000"))

吧123456789

=IF(COUNTA(C8:E8)=3,YEAR(C8)&"lg"&TEXT(SUMPRODUCT((YEAR(C8)=YEAR($C$3:C8))*1),"0000")&IF(E8="男","y","x")&TEXT(SUMPRODUCT((YEAR(C8)=YEAR($C$3:C8))*($E$3:E8=E8)),"0000"),"")

吧123456789

明明最好辅助列  我从没写个那么绕的一条公式

DIYer

日期的流水号没对，有重号，男女的编号是对的

吧123456789

DIYer 发表于 2018-3-2 23:33
日期的流水号没对，有重号，男女的编号是对的

EAR(C8)=YEAR($C$3:C8)
请看好我这里面   我现在的公式位置在第8行，我复制的公式是下拉公式的最后一个，你把公式放第8行即可

DIYer

写了个长的来解决=IF(OR(D3="",E3="",C3=""),"",YEAR(C3)&"LG"&TEXT(SUMPRODUCT(N(YEAR($C$3:C3)=YEAR(C3))),"0000")&IF(E3="男","Y",IF(E3="女","X",”“)))&TEXT(SUMPRODUCT(($E$3:E3=E3)*($N$3:N3=YEAR(C3))),"0000")
发现之前你的公式又对了，但是如果年份混乱的情况下会出现重新编号的情况