用日期函数实现自动计算税款所属期

黄其宾

有一些的税务报表中，要核定数据发生的月份，常常在表头用“所属期    年  月  日至    年  月  月”样式来表示，其原意是：前面的“年月日”，表示当月第一天，如2010年1月1日，后面的“年月日”表示当月最后一天，如2010年1月31日。
    在用Excel设计此类表格时，常常将前面的“年月日”放在一个单元格里，在此单元格后面，用一个单元格来存放“至”，“至”后面的一个单元格，存放后一个“年月日”。将前后两个存放“年月日”的单元格，其数字格式设为“日期”，类型为“2001年3月14日”，即设为年月日全部显示的格式，在输入数据时，只要用“年-月-日”形式，即可快速输入日期，输入当年日期还可简化为“月-日”形式，即在单元格中输入“5-1”即可显示为“2010年5月1日”。
    这样的设计比哪些一个字一个字地录入的方法来说也算不错了，关键是我们录入日期时，还是容易出错。如将开始日期输为“2010年5月2日”，或者将终止日期输为“2010年5月30日”等等，我们还得用数据的“有效性”来限制非法的输入。
    首先，我们在工作表闲置的区域，输入当年每月第一天。如G列闲置，将G1-G12设为日期“年月日”全显示格式（同上），在12个单元格中分别输入“2010年1月日”-“2010年12月1日”，将“D1：D12”区域的“名称”定义为“日期”（参照名称定义操作），这样，我们将开始日期单元格（假如为B1）的“有效性”-“允许”设为“序列”，“来源”中输入“=日期”，这样，B1中只能选择录入每月月初第一天。我们在终止日期单元格（假如为D1，其中C1中为“至”）中，输入公式“=DATE（YEAR（B1），MONTH（B1）+1，0）”，这样，当你在B1中选择了月初日期，D1就会生成月末日期，但有一个漏洞，就是当B1为0（无数据）时，D1就会显示为“1900年1月31日”，这不符合空表不显示任何数据的要求，因此，我们在D1的公式中，加上如下条件，就是“当B1<>0时，等于公式计算结果，否则等于0”，即将上面公式更改为“=IF（B1<>0，DATE（YEAR（B1），MONTH（B1）+1，0），0）”。
    以上设计完整了吗？不完整，原因在于“日期”名称中“G1：G12”中的日期是固定的，所以，跨年后，“日期”名称中的数据还得重新录入。
    一张好的表格，就是要修改率低，手工录入数据的单元格少，才能大幅度降低录入数据的错误率。如果跨年就要修改，就不算完整。因此，我们在G1中输入公式：“=DATE（YEAR（NOW（）），1，1）”，如此类推，一直到在G12中输入公式：“=DATE（YEAR（NOW（）），12，1）”，来实现表格的跨年度使用。
    凡做过会计的人都知道，税务报表一般都在征收期上报，即当月税收，在次月1-15日申报，遇节假日顺延，有时延到次月20日，所以我们填制当月的税务报表，是在次月的1-20日。因此，上面的公式，有一个致命的漏洞，就是在每年的元月，无法选取上年12月的日期。
    要解决这个问题，我们只能给上面公式加上如此条件：“如果当前日期大于1月20日，按上面公式计算结果，否则，年度减1”。上面G1中的公式改为：“=IF(NOW()>DATE(YEAR(NOW()),1,20),DATE(YEAR(NOW()),1,1),DATE(YEAR(NOW())-1,1,1))”，如此类推，一直到G12中的公式改为：“=IF(NOW()>DATE(YEAR(NOW()),1,20),DATE(YEAR(NOW()),12,1),DATE(YEAR(NOW())-1,12,1))”。这样，在“月初日期”和“月末日期”两个单元格中，只有“月初日期”单元格能有限制地输入数据，“月末日期”根据“月初日期”来自动生成，大幅度降低了输入数据过程中的错误率。如果你所录入的是一套表，而每张表头都有“所属期：”，而且这个日期张张表都是一样的，你就将这些表放在一个工作簿中，在一个主表中设计以上公式，其余的表取主表中这两个单元格的数据，你只需操作一个单元格，整套表的“所属期”全部搞定。最后，为了打印和美观考虑，将G列隐藏起来。
    当然，还有更先进的方法。

cflood

好长啊！！