学良老师辛苦了,但还是要提一点小小意见,其中有些函数和代码可以精简,举例如下:
1、出身年月
=IF(J3="","",IF(AND(LEN(J3)<>15,LEN(J3)<>18),"错误",IF(ISERROR(1*(TEXT(MID(J3,7,6+(LEN(J3)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(J3,7,6+(LEN(J3)=18)*2),"#-00-00")))<VALUE("1905-01-01"),(1*(TEXT(MID(J3,7,6+(LEN(J3)=18)*2),"#-00-00")))>TODAY()),"错误",VALUE(TEXT(MID(J3,7,6+(LEN(J3)=18)*2),"#年00月00日"))))))
可改为
=--TEXT(MID(K3,7,6+(LEN(K3)=18)*2),"#-00-00")
2、出生距离今天的天数
=IF(ISERROR(1*(TEXT(MID(J3,7,6+(LEN(J3)=18)*2),"#-00-00"))),"错误",IF(OR((1*(TEXT(MID(J3,7,6+(LEN(J3)=18)*2),"#-00-00")))<VALUE("1905-01-01"),(1*(TEXT(MID(J3,7,6+(LEN(J3)=18)*2),"#-00-00")))>TODAY()),"错误",DATEDIF(TEXT(MID(J3,7,6+(LEN(J3)=18)*2),"#-00-00"),TODAY(),"D")))
可改为
=DATEDIF(G3,NOW(),"d")
3、星期几
=CHOOSE(WEEKDAY(D55,2),"星期一","星期二","星期三","星期四","星期五","星期六","星期日")
可改为
=TEXT(D55,"aaaaa")
4、vba中的代码也可精简
Sub 清空表格()
Application.ScreenUpdating = False
Sheets("录入登记表").Cells(2, 2) = ""
Sheets("录入登记表").Cells(3, 3) = ""
Sheets("录入登记表").Cells(3, 6) = ""
Sheets("录入登记表").Cells(3, 8) = ""
Sheets("录入登记表").Cells(3, 10) = ""
Sheets("录入登记表").Cells(4, 7) = ""
Sheets("录入登记表").Cells(4, 10) = ""
Sheets("录入登记表").Cells(5, 3) = ""
Sheets("录入登记表").Cells(5, 10) = ""
Sheets("录入登记表").Cells(6, 3) = ""
Sheets("录入登记表").Cells(6, 10) = ""
Sheets("录入登记表").Cells(7, 3) = ""
Sheets("录入登记表").Cells(7, 10) = ""
Sheets("录入登记表").Cells(8, 10) = ""
Sheets("录入登记表").Cells(9, 3) = ""
Sheets("录入登记表").Cells(9, 5) = ""
Sheets("录入登记表").Cells(9, 8) = ""
Sheets("录入登记表").Cells(9, 11) = ""
Sheets("录入登记表").Cells(11, 3) = ""
Sheets("录入登记表").Cells(12, 3) = ""
Sheets("录入登记表").Cells(13, 3) = ""
Sheets("录入登记表").Cells(14, 3) = ""
Sheets("录入登记表").Cells(15, 3) = ""
Sheets("录入登记表").Cells(16, 3) = ""
Sheets("录入登记表").Cells(18, 3) = ""
Sheets("录入登记表").Cells(18, 5) = ""
Sheets("录入登记表").Cells(18, 7) = ""
Sheets("录入登记表").Cells(18, 10) = ""
Sheets("录入登记表").Cells(18, 11) = ""
Sheets("录入登记表").Cells(19, 3) = ""
Sheets("录入登记表").Cells(19, 5) = ""
Sheets("录入登记表").Cells(19, 7) = ""
Sheets("录入登记表").Cells(19, 10) = ""
Sheets("录入登记表").Cells(19, 11) = ""
Sheets("录入登记表").Cells(20, 3) = ""
Sheets("录入登记表").Cells(20, 5) = ""
Sheets("录入登记表").Cells(20, 7) = ""
Sheets("录入登记表").Cells(20, 10) = ""
Sheets("录入登记表").Cells(20, 11) = ""
Sheets("录入登记表").Cells(21, 3) = ""
Sheets("录入登记表").Cells(21, 5) = ""
Sheets("录入登记表").Cells(21, 7) = ""
Sheets("录入登记表").Cells(21, 10) = ""
Sheets("录入登记表").Cells(21, 11) = ""
End Sub
可改为
Sub 清空表格()
Sheets("录入登记表").Activate
Application.ScreenUpdating = False
Cells(2, 2) = ""
Cells(3, 3) = ""
Cells(3, 6) = ""
Cells(3, 8) = ""
Cells(3, 10) = ""
Cells(4, 7) = ""
Cells(4, 10) = ""
Cells(5, 3) = ""
Cells(5, 10) = ""
Cells(6, 3) = ""
Cells(6, 10) = ""
Cells(7, 3) = ""
Cells(7, 10) = ""
Cells(8, 10) = ""
Cells(9, 3) = ""
Cells(9, 5) = ""
Cells(9, 8) = ""
Cells(9, 11) = ""
Cells(11, 3) = ""
Cells(12, 3) = ""
Cells(13, 3) = ""
Cells(14, 3) = ""
Cells(15, 3) = ""
Cells(16, 3) = ""
Cells(18, 3) = ""
Cells(18, 5) = ""
Cells(18, 7) = ""
Cells(18, 10) = ""
Cells(18, 11) = ""
Cells(19, 3) = ""
Cells(19, 5) = ""
Cells(19, 7) = ""
Cells(19, 10) = ""
Cells(19, 11) = ""
Cells(20, 3) = ""
Cells(20, 5) = ""
Cells(20, 7) = ""
Cells(20, 10) = ""
Cells(20, 11) = ""
Cells(21, 3) = ""
Cells(21, 5) = ""
Cells(21, 7) = ""
Cells(21, 10) = ""
Cells(21, 11) = ""
End Sub
还有可精简的地方,就不一一列举了。
学良老师,在下班门弄斧了! |