新浪博客

调休的工作日计算_Excel公式教程

2018-07-24 19:30阅读：

http://blog.sina.cn/dpool/blog/u/5612584991

【导读】
WORKDAY、NETWORKDAYS、WORKDAY.INTL、NETWORKDAYS.INTL这四个工作日函数，当日期计算范围内出现调休的情况，就不能返回正确的结果。本文介绍调休情况下的工作日计算方法，包括：
一、自制上班休假表
二、根据自制的上班休假表，返回指定日期前后的某个工作日
三、根据自制的上班休假表，计算指定日期范围内工作日的天数
WORKDAY和WORKDAY.INTL函数用来返回指定日期之前或之后若干个工作日的日期。语法如下：
【语法】WORKDAY(start_date, days, [holidays])
【语法】WORKDAY.INTL(start_date, days, [weekend], [holidays])
NETWORKDAYS和NETWORKDAYS.INTL函数用来返回指定两个日期之间的工作日数。语法如下：
【语法】NETWORKDAYS(start_date, end_date, [holidays])
【语法】NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
这四个函数都可以通过holidays参数，引用一个单元格区域，由一个列表指定星期六、星期日以外的节假日。
可是，一旦出现调休，某些星期六、星期日需要上班，我们无法在这四个函数中把它们指定为工作日，从而造成函数返回的结果与实际情况不相符。
一、自制上班休假表
节日的放假和调休，一般按照国家发布的节假日安排通知来执行。少数民族习惯的节日，由各少数民族聚居地的地方政府制定放假日期。
此外，个别公司可能根据自身的情况，制定自己公司的上班休假日期。
因此，我们可以根据实际计算的需要，自己制定上班休假表。
（一）制作〔班休〕表
下面，我们一起在电子表格里制作这张表格吧。
首先，在Excel中打开一个新建文档，把一个空白的工作表改名为“班休”。
在
〔班休〕表的A1单元格输入日期2017-12-1，然后按住A1单元格右下角的十字形，向下拖动到A427单元格，生成2017年1月1日至2019年1月31日的日期列表。
在B1单元格输入公式：=IF(WEEKDAY(A1,2)>5,'休','班')
把B1单元格的公式复制到B2:B427。
最后，根据实际情况，把B列部分单元格的值修改为正确的上班休假标记。
适当地设置单元格格式，会让视觉效果非常棒。例如，自定义A列单元格格式为：yyyy-mm-dd aaa；B列设置条件格式，让标记为“休”的字体用红色显示。
〔班休〕表的效果如下图：
自制上班休假表
（二）制作〔班〕表
在〔班休〕表的基础上，我们再制作一个只包含工作日的列表。
把工作表〔班休〕表复制一个，工作表名字改为“班”。
设置数据筛选，然后筛选出B列标记为“休”的行，删掉这些行。
取消数据筛选，现在A列剩下的都是工作日了。
这时候，B列的标记已经是多余的，把整个B列删掉。〔班〕表的效果如下图：
自制工作日表
说明 很多人受holidays参数的影响，模仿原来的函数，制作的是休假日期列表。其实，制作工作日列表对后面的计算更为方便。
二、根据自制的上班休假表，返回指定日期前后的某个工作日
下面，我们分别利用〔班休〕表和〔班〕表，返回指定日期前后的某个工作日。
为了便于计算，我们在〔班休〕表C列先增加一列索引值。
在〔班休〕表C1单元格输入公式：=IF(B1='班',1,0)
把C1单元格的公式复制到C2:C427。
C列的索引值，由上而下标记第几个工作日。休假日的索引值使用上一工作日的索引值。
增加索引值后，〔班休〕表的效果如下图：
自制上班休假表
在下面的表格，根据A列指定的日期，B列指定该日期之前之后的工作日天数（正数为之后的天数，负数为之前的天数），返回工作日的日期：
返回指定日期前后的某个工作日
首先，看看利用〔班休〕表如何写公式。
在C3单元格输入公式：=INDEX(班休!A:A,MATCH(VLOOKUP(A3,班休!A:C,3)+B3,班休!C:C,))
在公式中，首先用VLOOKUP(A3,班休!A:C,3)，从〔班休〕表的C列获取工作日索引值。由于〔班休〕表的A列是排好序的，所以这里VLOOKUP函数使用近似匹配方式，以提高计算速度。
接下来，就是把该索引值，加上B列指定的天数，计算返回日期的工作日索引值，用MATCH函数查找计算后的索引值（注意这里用的是精确匹配方式），用INDEX函数返回索引值对应的日期。
利用〔班〕表，公式简单很多。
在D3单元格输入公式：=INDEX(班!A:A,MATCH(A3,班!A:A)+B3)
由于〔班〕表的A列是排好序的，所以这里MATCH函数使用近似匹配方式。
把C3:D3的公式向下复制就可以了。
为了比较WORKDAY函数的计算结果，我们在E3单元格输入公式：=WORKDAY(A3,B3,班休!A126:A128)
E4单元格的公式为：=WORKDAY(A4,B4,班休!A126:A128)
E5单元格的公式为：=WORKDAY(A5,B5)
下图是输入公式后表格的计算结果：
返回指定日期前后的某个工作日
注意看第5行，A列的日期为休假日，而B列指定的天数为0。C、D列的公式返回A列日期之前最近的工作日，E列的WORKDAY函数直接返回A列的日期，但该日期并不是工作日。
如果想B列为0时，直接返回A列的日期，这也很简单，在原公式外面套个IF函数就可以了。C3公式可改为：
=IF(B3,INDEX(班休!A:A,MATCH(VLOOKUP(A3,班休!A:C,3)+B3,班休!C:C,)),A3)
D3公式可改为：
=IF(B3,INDEX(班!A:A,MATCH(A3,班!A:A)+B3),A3)
三、根据自制的上班休假表，计算指定日期范围内工作日的天数
继续，我们分别利用〔班休〕表和〔班〕表，计算指定日期范围内的工作日天数。
在下面的表格，根据A、B列指定的起始和结束日期，计算该范围内工作日的天数：
计算指定日期范围内工作日的天数
先看看利用〔班休〕表写的公式。
C11单元格可以输入公式：=COUNTIF(INDEX(班休!B:B,MATCH(B12,班休!A:A)):INDEX(班休!B:B,MATCH(A12,班休!A:A)),'班')
公式中，MATCH函数用近似匹配方式确定行范围，用INDEX函数指定列，用COUNTIF统计该列指定行范围内，标记为“班”的次数。
也可以输入用公式：=COUNTIFS(班休!A:A,'>='&A11,班休!A:A,'<='&B11,班休!B:B,'班')
而计算速度最快的公式，应该是：
=INDEX(班休!C:C,MATCH(B11,班休!A:A))-INDEX(班休!C:C,MATCH(A11,班休!A:A))+(INDEX(班休!B:B,MATCH(A11,班休!A:A))='班')
利用〔班〕表，可在D11单元格输入下面其中一个公式：
=COUNTIF(班!A:A,'<='&B11)-COUNTIF(班!A:A,'<'&A11)
=COUNTIFS(班!A:A,'>='&A11,班!A:A,'<='&B11)
=SUMPRODUCT((班!A:A>=A11)*(班!A:A<=B11))
而计算速度最快的公式，应该是：
=MATCH(B11,班!A:A)-MATCH(A11,班!A:A)+(VLOOKUP(A11,班!A:A,1)=A11)
为了比较NETWORKDAYS函数的计算结果，我们在E11单元格输入公式：=NETWORKDAYS(A11,B11,班休!A$77:A$83)
把C11:E11的公式向下复制就可以了。
下图是输入公式后表格的计算结果：
计算指定日期范围内工作日的天数

我的更多文章

下载客户端阅读体验更佳

APP专享