中琅软件教程网

首页搜索登录
首页业界网页平面多媒体程序数据库办公工具服务器系统网络安全站长认证壁纸
加入收藏 | 网站地图 | | RSS | WAP
你好,游客 登录 注册 搜索

办公技巧:巧用Office Excel自动工作日计算

[日期:2006-04-05] 作者:佚名 来源:不详 [字体: ]
工作量当然得以“工作日”的数量去安排,但每个月的天数不同,而且周六日也不尽相同。如果想准确地知道一个月有多少个工作日,可以请Excel来帮忙!

小知识

  标准工作日是指法律规定的各企业、事业、机关、团体等单位在正常情况下普遍实行的工作日。通俗的说,就是指除了国家法定节假日(双休日、元旦、五一、国庆、春节等)之外正常工作的日期。

任务分析

  因为每月的总天数和双休日的分布是不同的,而且春节是农历的假期,简单地使用Excel函数去计算是满足不了要求的。我们先使用Excel“分析工具库”中的networkdays()函数计算出“准工作日”,再考虑其中的特殊农历假日。这样就能准确的算出“工作日”的天数。

有请 networkdays()函数

  通常情况下,在Excel中是找不到这个函数的。别着急,跟我来。单击“工具→加载宏”,在弹出的“加载宏”对话框中复选“分析工具库”,再单击“确定”按钮。将office的安装光盘放入光驱,按提示即可安装成功,如图1。


巧用MSOffice Excel自动工作日计算 

外来的和尚念不好经

  networkdays()函数的语法为:networkdays(Start_date,End_date,Holidays)。其中Start_date表示开始日期,End_date为终止日期,Holidays表示一个或者多个特定假日序列,可以采用单元格引用的方式。

  如图2,B8单元格公式“=networkdays(B2,B3,B4:B6)”结果为20。事实上,2004年的10月2日和3日是星期六和星期天,那么按照我们的习惯就会通过调休的方式,最终得到10月份只有18天上班,而不是networkdays()函数计算出的20天。

特殊假日的处理

  虽然说直接应用networkdays()函数并不能得到正确的工作日天数,但我们还是可以通过对此函数的变化来得到。图3是我们的范例,演示了如何一步一步逼近我们的目标。

  第一步:将A列定义为月份。选中A列,在右键菜单中选择“设定单元格格式”,在“数字”标签中选择“分类”为“自定义”,“类型”中输入“yyyy-mm”格式,单击“确定”按钮退出。

  第二步:设计B列公式。先直接用networkdays()函数计算只考虑本月除去双休日后的天数。虽然可直接使用该函数,但需要知道每月的开始日期和终止日期。开始日期当然是每月的1日,用“date(year(A2),month(A2),1)”表示就OK了。结束日期应当是每月的最后一天,这就有些难度了,究竟是30日、31日还是28日、29日?需要一个复杂的判断关系。这里我们采用了一个变通的办法,就是当月的最后一天其实就是下月的第一天再减去1天,所以我们可以用“date(year(A2),month(A2)+1,1)-1”来表示。于是B2的公式就有了:“=networkdays(date(year(A2),month(A2),1),date(year(A2),month(A2)+1,1)-1)”。

巧用MSOffice Excel自动工作日计算 

巧用MSOffice Excel自动工作日计算

  第三步:计算除了春节以外的双休日。我们知道国家规定的公众假日一共10天,除了春节的3天是每年变化的外,其余元旦1天,劳动节3天,国庆节3天都是固定的。因此我们可以用IF函数对月份是否是1月、5月和10月做判断,再来减去相应的公众假日天数,就得到了不考虑春节以外的工作日。于是C2=if(month(A2)=5,B2-3,if(month(A2)=5,B2-3,if(month(A2)=1,B2-1,B2)))或者使用or()函数就是C2=if(or(month(A2)=5,month(A2)=10),B4-3,if(month(A2)=1,B4-1,B4))。

  第四步:考虑春节的问题。根据常识,春节的三天只会在1月份或者2月份出现,也就是说最后的判断只正对1、2月份。通过万年历查询得到2000——2010这十年的春节分布,巧的是正好没有春节三天跨月份的年份,也就是说春节三天不是落在1月份就是落在2月份,如图4所示,这给我们应用IF函数判断带来了方便。通过对年份和月份的判断,减去相应的春节天数,就得到了真正意义上的工作日。因此在D2中应该输入=IF(AND(OR(YEAR(A2)=2001,YEAR(A2)=2004,YEAR(A2)=2006,YEAR(A2)=2009),MONTH(A2)=1),C2-3,IF(AND(OR(YEAR(A2)=2000,YEAR(A2)=2002,YEAR(A2)=2003,YEAR(A2)=2005,YEAR(A2)=2007,YEAR(A2)=2008,YEAR(A2)=2010),MONTH(A2)=2),C2-3,C2))。

巧用MSOffice Excel自动工作日计算 

  第五步:隐藏过渡列。选中“B:C”这两列,在右键菜单中选择“隐藏”,将中间用于计算方便所使用的过渡列B和列C隐藏即可。

  选中区域B2:D2,按住填充柄向下拖动填充后,只要在A列任一单元格输入月份,就可以在D列得到该月相应的天数,是不是很方便?

关键词:

收藏 推荐 打印 | 录入: | 阅读:
本文评论   查看全部评论 (52)
表情: 姓名: 字数
点评:
       
评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事/刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款
第 52 楼
* 匿名 发表于 2017/1/12 10:11:17
http://www.blue1000.com/bkhtml/c14/2009-12/66159.htm 跑步作文700字:http://www.135995.com/details/zs762.html 关于童心的作文:http://www.135995.com/details/zs1488.html
第 51 楼
* 匿名 发表于 2016/12/24 17:27:22
http://www.blue1000.com/bkhtml/c14/2009-12/66159.htm 新员工试用期工作总结(精选3篇) http://www.135995.com/show/21621.html 新员工转正工作总结(精选3篇) http://www.135995.com/show/21620.html
第 50 楼
* 秋天 发表于 2009/9/14 15:49:05
谢谢,受用啦,我试做了
第 49 楼
* jingdian 发表于 2009/2/8 15:44:20
第 48 楼
* 师傅的给 发表于 2008/11/20 14:08:14
不太懂