|
在计算工龄、发票报销期限等日期计算应用中，经常要求两个日期的时间差以“0年0个月0天”的样式表现。图13-71为某公司离职员工信息表的部分内容，需要根据入职日期和离职日期计算在职时长，结果以年、月、日样式显示。在E2单元格中输入以下公式，并向下复制到E10单元格。=DATEDIF(B2,D2,'Y')&'年'&DATEDIF(B2,D2,'YM')&'个月'&DATEDIF(B2,D2,'MD')&'天'公式中使用了3个DATEDIF函数。第二参数分别使用“Y”，计算时间段中的整年数；使用“YM”，忽略日和年计算日期相差的月数；使用“MD”，忽略月和年计算日期相差的天数。最后将3个函数的计算结果与字符串“年”“个月”“天”进行连接，最终得到“0年0个月0天”样式的结果。由于月份和天数均不会超过两位数，也可使用以下公式完成计算。=TEXT(SUM(DATEDIF(B2,D2,{'Y','YM','MD'})*{10000,100,1}),'0年00个月00天')以F2单元格公式为例，DATEDIF函数的第二参数使用常量数组{'Y','YM','MD'}，分别计算时间段中的整年数、忽略日和年的相差月数、忽略月和年的相差天数，返回内存数组{0,9,20}。用该内存数组与{10000,100,1}相乘，即年数乘10000，月数乘100，天数乘1。使用SUM函数求和后得到结果为920。TEXT函数的第二参数中包含5个0，如果SUM函数的结果不足5位，则在最左侧以0补齐。本例中，即是将920变成00920处理。使用格式代码“0年00个月00天”，分别在数值右起第一位后面加上字符“天”，右起第三位后面加上字符“个月”，右起第五位后面加上字符“年”，最终得到“0年00个月00天”样式的结果。使用RANDBETWEEN函数，可以生成一组指定范围内的时间。如图13-73所示，需要生成一组9:00至11:00之间、以分钟为单位的随机时间。在A2单元格中输入以下公式，并复制到A2:C10单元格区域。='9:00'+RANDBETWEEN(0,120)/14409:00至11:00间隔为120分钟，因此先使用RAN-DBETWEEN函数生成0~120之间的随机整数，再除以一天的分钟数1 440，得到两小时内的随机分钟的序列值。随机分钟序列值加上起始时间“9:00”，得到9:00~11:00之间以分钟为单位的随机时间。也可以使用以下公式计算，则不必单独计算起始和结束时间的分钟数。=RANDBETWEEN('9:00'*1440,'11:00'*1440)/1440如需生成9:00~11:00之间以秒为单位的随机时间，可使用以下公式完成。='9:00'+RANDBETWEEN(0,7200)/864009:00~11:00间隔为7 200秒，因此先使用RAN-DBETWEEN函数生成0~7 200之间的随机整数，再除以一天的秒数86 400，得到两小时内的随机秒数的序列值。随机秒数序列值加上起始时间“9:00”，得到9:00~11:00之间以秒为单位的随机时间，效果如图13-74所示。除了使用考勤机打卡记录员工的考勤之外，还有部分企业会使用考勤表的形式记录员工出勤。设计合理的考勤表不仅能够直观显示员工的考勤状况，还可以减少统计人员的工作量。图13-75所示的是一份使用窗体工具结合函数与公式和条件格式制作的考勤表，当用户调整微调按钮时，考勤表中的日期会随之调整，并高亮显示周末日期。选中C~AG列的列标，调整列宽。选择A4:A5单元格，在【开始】选项卡下单击【合并后居中】按钮。使用格式刷将格式复制到A6:A21和B4:B21单元格区域。然后在工作表中添加姓名和标题等数据，再选中A4:B21单元格区域添加边框。设置完成后的局部效果如图13-76所示。在【开发工具】选项卡下，单击【插入】下拉按钮，在【表单控件】窗格中选择“数值调节钮(窗体控件)”选项，在工作表中拖动鼠标画出一个数值调节按钮，用于调整月份，如图13-77所示。右击数值调节钮，在弹出的快捷菜单中选择【设置控件格式】命令，打开【设置控件格式】对话框。在【控制】选项卡下，调整【最小值】右侧的微调按钮，将【最小值】设置为1。用同样的方法将【最大值】设置为12，将【步长】设置为1。单击【单元格链接】右侧的折叠按钮，选择E1单元格，单击【确定】按钮，如图13-78所示。右击控件，在弹出的快捷菜单中选择【复制】命令，然后按<Ctrl+V>组合键粘贴，用于调整年份。右击复制后的控件，在弹出的快捷菜单中选择【设置控件格式】命令，打开【设置控件格式】对话框并切换到【控制】选项卡下，调整【最小值】右侧的微调按钮，将【最小值】设置为2018，将【最大值】设置为2022，将【步长】设置为1。单击【单元格链接】右侧的折叠按钮，选择C1单元格，单击【确定】按钮，如图13-79所示。在C3单元格中输入以下公式，并向右复制到AG3单元格，完成日期标题的填充，效果如图13-80所示。=IF(COLUMN(A1)<=DAY(EOMONTH($B1&-$E1,0)),COLUMN(A1),'')设置公式的目的是在C3:AG3单元格区域生成能随着年份、月份动态调整的日数序列值，作为考勤表的参照日期。首先用B1单元格指定的年份值和E1单元格指定的月份值，连接成日期字符串“2018-12”，“EOMONTH($B1&-$E1,0)”部分表示返回日期字符串当月最后一天的日期。再用DAY函数计算出该月份最后一天的天数。COLUMN(A1)返回A1单元格的列号1，参数A1为相对引用，公式向右复制时依次变成B1、C1、D1…COLUMN函数的结果变成2、3、4…得到步长值为1的递增序列。使用IF函数进行判断，如果COLUMN函数生成的序列值小于等于该月份最后一天的日期，返回COLUMN函数结果，否则返回空文本。选中C3:AG21单元格区域，设置条件格式，用来根据每个月的实际天数动态显示边框。依次选择【开始】→【条件格式】→【新建规则】选项，弹出【新建格式规则】对话框。在【新建格式规则】对话框的【选择规则类型】列表框中，选择【使用公式确定要设置格式的单元格】选项。在【为符合此公式的值设置格式】的编辑框中输入以下公式，然后单击【格式】按钮，如图13-81所示。在弹出的【设置单元格格式】对话框中，切换到【边框】选项卡，选取合适的边框颜色，然后单击【外边框】按钮。最后单击【确定】按钮返回【新建格式规则】对话框，在【新建格式规则】对话框中单击【确定】按钮关闭对话框，如图13-82所示。选中C3:AG3单元格区域，设置条件格式，用来高亮显示周末日期。依次选择【开始】→【条件格式】→【新建规则】选项，弹出【新建格式规则】对话框。在【新建格式规则】对话框的【选择规则类型】列表框中，选择【使用公式确定要设置格式的单元格】选项。在【为符合此公式的值设置格式】的编辑框中输入以下公式，然后单击【格式】按钮。=(WEEKDAY(DATE($B1,$E1,C3),2)>5)*(C3<>'')打开【设置单元格格式】对话框。在【填充】选项卡中，选择合适的背景颜色，如“绿色”。最后依次单击【确定】按钮关闭对话框，如图13-83所示。条件 格式公式中的“DATE($B1,$E1,C3)”部分表示使用DATE函数依次生成递增的日期。其中年份值由B1单元格指定，月份值由E1单元格指定，天数值为C3:AG3单元格区域的数字。调整控件时，随着B1和E1单元格中数值的变化，DATE函数即可生成不同年份、不同月份的日期。WEEKDAY函数返回DATE函数生成日期的星期值。如果星期值大于5并且单元格不为空时，单元格将以指定的格式高亮显示。步 骤 10 在B4单元格中输入以下公式，并向下复制到B21单元格，计算每个人的累计出勤天数，如图13-84所示。步 骤 11 在考勤表右侧制作表格，用以记录事假、休班、婚假、丧假、工伤、出差等特殊情况。步 骤 12 在【视图】选项卡下，取消选中【网格线】复选框，如图13-85所示。至此考勤表制 作完毕，每人两行记录分别用于记录上午和下午的出勤情况。根据对应时段的实际出勤情况，出勤时输入1，缺勤时输入0进行记录即可。如图13-86所示，A列是英文简写的月份，需要计算出同样以英文显示的之前一个月的月份，效果如B列所示。在B2单元格中输入 以下公式，并向下复制到B12单元格。=TEXT(EDATE(A2&1,-1),'mmm')公式首先在A2单元格中的英文月份之后连接数值1，使其变成具有日期样式的文本“Aug1”，Excel将其识别为系统当前年份的8月1日。EDATE函数的第一参数使用文本型日期字符串，计算出一个月之前的日期。TEXT函数使用格式代码“mmm”，将EDATE函数得到的日期转换为英文简写形式的月份。 
--------------------------------------------------------------------- 推荐图书 1. 专家云集：多位身处各行各业，并身怀绝技的微软全球有价值专家与您无私分享。多年对 Excel的研究结果进行揭秘。
2. 知识点全覆盖：详尽而又系统地介绍了 Excel 2016函数与公式的所有技术特点和应用方法，全面覆盖相关知识点，完备知识体系无人能及。
3. 解决实际问题：大量源自实际工作的典型案例，通过细致地讲解，生动地展示各种应用技巧，快速提高读者的办公效率，让读者提前完成手头工作，不用加班。
4. 专业级深度剖析：对常常困扰学习者的功能性特性进行深入剖析，可以让读者既能知其然，又能知其所以然。
|
